Error Reporting? Import .sql file from PHP and show errors

Question

I am building a way of importing .SQL files into a MySQL database from PHP. This is used for executing batches of queries. The issue I am having is error reporting.

$command = "mysql -u $dbuser --password='$dbpassword' --host='$sqlhost' $dbname < $file";
exec($command, $output);

This is essentially how I am importing my .sql file into my database. The issue is that I have no way of knowing if any errors occurred within the PHP script executing this command. Successful imports are entirely indistinguishable from a failure.

I have tried:

Using PHP's sql error reporting functions.
Adding the verbose argument to the command and examining the output. It simply returns the contents of the .sql file and that is all.
Setting errors to a user variable within the .sql file and querying it from the PHP script.

I hope I am not forced to write the errors into a temporary table. Is there a better way?

UPDATE: If possible, it would be very preferable if I could determine WHAT errors occurred, not simply IF one occurred.

Output of the script should be stored in $output. Is it not? — Explosion Pills
– Explosion Pills, Commented Sep 20, 2012 at 22:08
See second bullet. "Adding the verbose argument to the command and examining the output. It simply returns the contents of the .sql file and that is all." — Tanoro
– Tanoro, Commented Sep 20, 2012 at 22:20
you can't use php's sql error reporting functions - you don't have a connection to the database in php. live by a command-line import, die by it. The best you can probably hope to achieve is capturing error messages from the exec call, either using output buffering, or a return variable. — Tim G
– Tim G, Commented Sep 20, 2012 at 22:27
just edited my answer to what I think will be a nice solution for you. please take a look if you have time. — Tim G
– Tim G, Commented Sep 21, 2012 at 2:09

Frank Farmer · Accepted Answer · 2012-09-20 22:32:37Z

2

$command = "mysql -u $dbuser --password='$dbpassword' --host='$sqlhost' $dbname"
  . " < $file 2>&1";
exec($command, $output);

The error message you're looking for is probably printed to stderr rather than stdout. 2>&1 causes stderr to be included in stdout, and as a result, also included in $output.

Even better, use proc_open instead of exec, which gives you far more control over the process, including separate stdout and stderr pipes.

answered Sep 20, 2012 at 22:32

Frank Farmer

39.6k12 gold badges73 silver badges90 bronze badges

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

Comments

Tim G · Accepted Answer · 2012-09-21 02:07:53Z

1

Try using shell_exec

$output = shell_exec( "mysql -u $dbuser --password='$dbpassword' --host='$sqlhost' $dbname < $file" );
// parse $output here for errors

From the manual:

shell_exec — Execute command via shell and return the complete output as a string

Note:

This function is disabled when PHP is running in safe mode.

EDIT: Full solution:

what you need to do is grab STDERR and discard STDOUT. Do this by adding '2>&1 1> /dev/null' to the end of your command.

$output = shell_exec( "mysql -u $dbuser --password='$dbpassword' --host='$sqlhost' $dbname < $file 2>&1 1> /dev/null" );
$lines = explode( PHP_EOL, $output );

$errors = array();

foreach( $lines as $line )
{
    if ( strtolower( substr( $line, 0, 5 ) ) == 'error' )
    {
        $errors[] = $line;
    }
}

if ( count( $errors ) )
{
    echo PHP_EOL . 'Errors occurred during import.';
    echo implode( PHP_EOL, $errors );
}
else
{
    echo 'No Errors' . PHP_EOL;
}

edited Sep 21, 2012 at 2:07

answered Sep 20, 2012 at 22:31

Tim G

1,82212 silver badges25 bronze badges

2 Comments

Tanoro Over a year ago

Tim, this pointed me in the right direction, but there are some caveats. Shell_exec is disabled on my server, but using exec() with the output variable set gave me the same effect. Exec returns output as an array already, so I didn't need the explode. Other than these gotchas, your solution gave me what I needed. Thanks.

Svetoslav Marinov Over a year ago

How is this working when you redirect all output to /dev/null ?

JvdBerg · Accepted Answer · 2012-09-20 22:07:36Z

1

When issuing a exec, the shell will return a 0 on succes, or a number indicating a failure.

$result = exec( $command, $output );

should do the trick. Check result and handle appropiate.

answered Sep 20, 2012 at 22:07

JvdBerg

21.8k8 gold badges40 silver badges55 bronze badges

1 Comment

Tanoro Over a year ago

Thank you. That does tell me IF an error happened. Is there a way of determining WHAT error happened?

AlexP · Accepted Answer · 2012-09-20 22:12:42Z

-1

You have done everything but look at the PHP manual! There is an additional parameter for the exec to return a result

http://php.net/manual/en/function.exec.php

"If the return_var argument is present along with the output argument, then the return status of the executed command will be written to this variable."

exec($command,$output,$result);
if ($result === 0) {
 // success
} else {
 // failure
}

answered Sep 20, 2012 at 22:12

AlexP

9,8571 gold badge28 silver badges44 bronze badges

3 Comments

Tanoro Over a year ago

No, I saw it. This solution provides the same thing JvdBerg suggested. I realize I didn't ask for this, specifically, but it would be handy if I knew what error happened.

AlexP Over a year ago

Perhaps passthru? php.net/manual/en/function.passthru.php this should give you the raw output

Tanoro Over a year ago

Passthru disabled on the server.

Collectives™ on Stack Overflow

Error Reporting? Import .sql file from PHP and show errors

4 Answers 4

Comments

2 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related