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
– troelskn, Commented May 2, 2009 at 20:34

Graviton · Accepted Answer · 2017-07-11 01:09:10Z

4

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

?>

edited Jul 11, 2017 at 1:09

answered May 2, 2009 at 12:21

Graviton

83.2k150 gold badges443 silver badges614 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Emil H · Accepted Answer · 2009-05-02 11:51:02Z

2

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.

answered May 2, 2009 at 11:51

Emil H

40.3k11 gold badges81 silver badges97 bronze badges

Comments

mylesmg · Accepted Answer · 2009-05-02 12:19:29Z

2

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.

answered May 2, 2009 at 12:19

mylesmg

5323 silver badges8 bronze badges

Comments

enam · Accepted Answer · 2012-02-12 18:18:35Z

2

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

edited Feb 12, 2012 at 18:18

enam

1,1771 gold badge10 silver badges24 bronze badges

answered Feb 12, 2012 at 17:27

Tiago Sintra

312 bronze badges

Comments

Francis · Accepted Answer · 2009-05-02 10:14:29Z

1

there should be no space between -p and mypass.

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

answered May 2, 2009 at 10:14

Francis

12.2k2 gold badges35 silver badges37 bronze badges

1 Comment

Graviton Over a year ago

Hello, I tried your suggestion. The direct calling to MySQL is working, but the calling from PHP using exec still doesn't work

Bob Fanger · Accepted Answer · 2009-05-02 11:12:06Z

0

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

answered May 2, 2009 at 11:12

Bob Fanger

30k7 gold badges68 silver badges83 bronze badges

Collectives™ on Stack Overflow

Calling MySQL exe using PHP exec doesn't work

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related