MySQL Stored Procedure not throwing error if it contains a query that succeeds

error handlingMySQLPHPstored-procedures

I am writing a simple MySQL Stored Procedure, and calling it from PHP using PDO.

This is just simple enough to illustrate the problem I'm having.

This procedure test_procedure_2, does not throw any error.

CREATE PROCEDURE `test_procedure_1`()
BEGIN    
    select 'gary';

    CALL raise_error;
END

It returns the result set:

[0] => Array
    (
        [0] => Array
            (
                [gary] => gary
            )

    )

However… 

CREATE PROCEDURE `test_procedure_2`()
BEGIN            
    CALL raise_error;

    select 'gary';
END

…this procedure test_procedure_2, when called, will actually throw an PDOException with the message "SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE vjs_admin_dev.raise_error does not exist". And the select 'gary' query never gets executed. I like that.

Why is it that the first procedure did NOT throw an error when it clearly contained an error? I would like to come up with a stored procedure that, whenever there is an error anywhere in it, it will throw an error. Thanks for your help!

I'm using

  • Ubuntu: 16.04, and
  • PHP: 7.0.15
  • mysql: Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper

EDIT:
I have tried this with mysqli, and with PDO, and I get the same results. Here are some simple code examples that show my issue:

The output of the Mysqli version is:

Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN  select 'gary';  CALL raise_error;END
RESULTS:Array ( [gary] => gary ) 
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN  CALL raise_error;   select 'gary';END
CALL failed: (1305) PROCEDURE vjs_admin_dev.raise_error does not exist
----------

The output of the PDO version is:

Running test_procedure_1
CREATE PROCEDURE `test_procedure_1`()BEGIN  select 'gary';  CALL raise_error;END
RESULTS:Array ( [gary] => gary [0] => gary ) 
----------
Running test_procedure_2
CREATE PROCEDURE `test_procedure_2`()BEGIN  CALL raise_error;   select 'gary';END
CALL failed: (42000) Array ( [0] => 42000 [1] => 1305 [2] => PROCEDURE vjs_admin_dev.raise_error does not exist ) 
----------

Best Answer

Wow, that's a relevant problem, because you can't identify an error in a procedure, an this passes hidden next to some valid result! I think your question should be more popular.

After many tries and tests I found a way to solve it. You'd expect to loop through the procedure result set, until no more results are available (the PHP Manual way). However, I found an alternate control flow to catch the errors after previous valid results:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
    $sql="CALL procedureWithErrors()";      
    $conn=new mysqli($host,$user,$pass,$db);

    /*The usual way (does not work)*/
    $conn->multi_query($sql);
    do{
        $result=$conn->store_result();      
        if($result){
            while($row=$result->fetch_assoc()){
                print_r($row);
            }
            $result->free();
        }                           
    }while($conn->more_results() && $conn->next_result());

    /*This way works*/
    $conn->multi_query($sql);
    while(($result=$conn->store_result())!==FALSE){
        if($result){
            while($row=$result->fetch_assoc()){
                print_r($row);
            }
            $result->free();
        }                           
        $conn->next_result();
    }

    /*Just closing and error handling, not relevant code from here*/
    $conn->close();         

}catch(mysqli_sql_exception | Exception $e){    
    $error="Error #".$e->getCode()." ".$e->getMessage().PHP_EOL;    
    if(isset($conn) && get_class($e)=="mysqli_sql_exception")
        $error.="SQLSTATE #".$conn->sqlstate." Statement: $sql".PHP_EOL;
    $error.=$e->getTraceAsString();
    echo(nl2br($error));    
}finally{
    if(isset($result) && $result instanceof mysql_result) $result->free();          
    if(isset($conn) && is_resource($conn) && get_resource_type($conn)==='mysql link') $conn->close();
}
?>

The problems seems to be that mysqli::more_results and mysql::next_result return FALSE if an error happens, so they can't distinguish between an error and the lack of further results.

Related Question