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

Answer 1

there should be no space between -p and mypass.

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

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

I found the solution, guys.

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);

?>