Calling MySQL exe using PHP exec doesn't work

Question

I have an extremely simple script with PHP exec, calling mysql command:

  $dbhost = 'localhost';
  $dbuser = 'root';
  $dbpass = 'mypass';
  $db = 'job';
  $file ='job_create.sql';
  $mySQLDir='"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql"';



    if ($dbpass != '') {
        $cmd = $mySQLDir.' -h '.$dbhost.' --user='.$dbuser.' --password='.$dbpass.' < "'.dirname(__FILE__).'\\'.$file.'"';
    } else {
        $cmd = $mySQLDir.' -h '.$dbhost.' --user='.$dbuser.' < "'.dirname(__FILE__).'\\'.$file.'"';
    }

    // echo $cmd;
    exec($cmd,$out,$retval);

I would expect that the above script calls mysql command, pass in the user authentication information and run the job_create.sql on the fly.

The only thing is that it doesn't work, in the sense that the job_create.sql is not run properly. . I tried to call mysql command directly from command line using the below script,

bin\mysql.exe -h localhost --user=root --password=mypass < "job_create.sql"

and it works.

Any idea how to fix this?

Edit: I call this script from PHP command line. i.e., PHP.exe installdb.php

Why don't you use the mysql-bindings instead of calling over the shell? — troelskn, May 02 '09 at 20:34

Graviton · Accepted Answer · 2017-07-11T01:09:10.143

I found the solution

The problem is that you need to explicitly enclosed the $cmd in "".i.e.,

exec('"'.$cmd.'"',$out ,$retval);

This is the full code that works:

<?php

  $dbhost = 'localhost';
  $dbuser = 'root';
  $dbpass = 'password';
  $db = 'job';
  $file =dirname(__FILE__).'\\'.'job_create.sql';
  $mySQLDir='"C:\\Program Files\\MySQL\\MySQL Server 5.1\\bin\\mysql.exe"';



    if ($dbpass != '') {
        $cmd = $mySQLDir.' -h '.$dbhost.' --user='.$dbuser.' --password='.$dbpass.' < "'.$file.'"';

    } else {
        $cmd = $mySQLDir.' -h '.$dbhost.' --user='.$dbuser.' < "'.$file.'"';
    }

     echo $cmd;  

   exec('"'.$cmd.'"',$out ,$retval);
   echo "\n";
    echo ($retval);

?>

score 2 · Answer 2 · answered May 02 '09 at 11:51

The problem here is probably that

mysql < file.sql

depends on a pipe operation that is facilitated by the shell. If php executes the command directly using something like the exec*() family of api calls rather than through a shell this won't work. I know that the shell actually is used on linux, but this might not be the case on windows. The manual doesn't specify exactly how the command is executed.

Try using the -e flag and the source command instead:

bin\mysql.exe -h localhost --user=root --password=mypass -e 'source job_create.sql'

This should work regardless of the way that the command is invoked.

score 2 · Answer 3 · answered May 02 '09 at 12:19

Your script should work, but definitely try using the -e flag and the 'source' command instead of the < char as a matter of best practice.

Chances are, the script is running as a different user than you are, and doesn't have command line permission to run your command. Check user permissions.

score 2 · Answer 4 · edited Feb 12 '12 at 18:18

This was happening to me too even on Ubuntu. The problem was that i was doing the following code:

$mysql = shell_exec("sudo which mysql");
$cmd = "$mysql --host=$host --user=$user --password=$pass -D $dbname -e 'source $base_sql_file'";
exec($cmd, $output, $retvar);

Only a slight detail was missing... the shell_exec returns the output with trailling characters. When executing the command i was getting a MySQL error 127 (Record is crashed).

The solution was simple: add a trim function to shell_exec :)

$mysql = trim(shell_exec("sudo which mysql"));
$cmd = "$mysql --host=$host --user=$user --password=$pass -D $dbname -e 'source $base_sql_file'";
exec($cmd, $output, $retvar);

score 1 · Answer 5 · answered May 02 '09 at 10:14

1

there should be no space between -p and mypass.

BTW I suggest you use the form --password=mypass

answered May 02 '09 at 10:14

Francis

11,388
2
33
37

Hello, I tried your suggestion. The direct calling to MySQL is working, but the calling from PHP using exec still doesn't work – Graviton May 02 '09 at 10:40

score 0 · Answer 6 · answered May 02 '09 at 11:12

0

Try it using proc_open() instead of exec().
proc_open allows you to display the errors a commandline tool gives.

answered May 02 '09 at 11:12

Bob Fanger

28,949
7
62
78

Calling MySQL exe using PHP exec doesn't work

6 Answers6

Linked