PHP mysqli is NOT trapping some errors when calling stored procedure

Question

We're building a production PHP-MySQL application, and want MySQL stored procedures to be the central bullet-proof gateway to the database. Duplicate keys, table not found, server instance going down, etc all and any kind of error needs to be trapped and conveyed to the calling PHP web-based UI, and transaction rolled back in the stored proc upon such errors.

I am using PHP mysqli and calling a stored procedure as follows:

$stmt = mysqli_prepare($db, "call my_stored_proc(?, ?, ?, @ptid)");
if ($stmt && mysqli_stmt_bind_param($stmt, "sss", 'p1', 'p2', 'p3') &&
             mysqli_stmt_execute($stmt) && mysqli_stmt_close($stmt)) {
  echo "All fine!"
} else {
  echo mysqli_error($db);
  db_disconnect($db);
  exit;
}

The stored procedure does some basic validation and signals a user-defined condition if the validation fails. And sure enough, my PHP code is able to catch and see those non-database (eg. formatting) validation errors arising from the stored procedure. After the non-database validations pass, the stored procedure goes on to do a database-related validation and if those pass it inserts a row in a table, and passes the ID in the last OUT parameter.

My problem is that if this insert fails (say, bcoz duplicate key error, or table not found error), my PHP code is simply not catching the error! It prints "All fine"!

Why is that? What am I missing?

I want my invocation of the stored proc to be bullet-proof, and all errors raised by the stored proc should be trappable in PHP.

FYI: If I call the stored proc from a mysql client (like MySQL Workbench or the mysql client on Linux), the errors are correctly reported.

LATER EDITS: FYI, the stored procedure code is simply:

delimiter $$

drop procedure if exists my_stored_proc $$
create procedure my_stored_proc
(
    in    p_name                VARCHAR(31),
    in    p_notes               VARCHAR(510),
    in    p_created_by          VARCHAR(31),
    out   p_pt_id               INT
)
begin
    declare custom_exception condition for sqlstate '45000';
    declare l_retval boolean;
    declare l_right_now datetime default now();

    select p_name regexp '^[[:space:]]*$' into l_retval;
    if l_retval then
        signal custom_exception set message_text = 'NAME cannot be blank.';
    end if;

    select p_name regexp '[^0-9_]' into l_retval;
    if l_retval then
        signal custom_exception set message_text = 'Invalid NAME.';
    end if;

    call validate_user_in_db(p_created_by, true, l_retval);
    if not l_retval then
        signal custom_exception set message_text = 'Invalid CREATED_BY user.';
    end if;

    insert into some_table
    (
        NAME, NOTES,
        CREATED_BY, CREATED_ON
    ) values
    (
        p_name, p_notes,
        p_created_by, l_right_now
    );

    set p_pt_id = last_insert_id();
end $$

delimiter ;

EVEN LATER UPDATE: The weird thing is, if I comment out the call to validate_user_in_db in the above stored proc, things work fine and errors are correctly trapped (eg. duplicate key, etc) in PHP.

FYI: validate_user_in_db does the following:

create procedure validate_user_in_db (in p_user VARCHAR(127),
                in p_active_only boolean, out p_retval boolean)
begin
    set p_retval = false;

    if p_active_only then
        select sql_calc_found_rows 'x'
        from SOME_USERS_TABLE
        where username = p_user
        and   active = true
        limit 1;
    else
        select sql_calc_found_rows 'x'
        from SOME_USERS_TABLE
        where username = p_user
        limit 1;
    end if;
    set @l_num_rows = found_rows() ;
    if @l_num_rows = 1 then
        set p_retval = true;
    end if;
end $$

Sorry for the long post. But I thought I'd give the full picture.

What am I missing? Why is my PHP code not getting back errors if the call to validate_user_in_db is enabled? Is validate_user_in_db changing some state permanently? Is the sql_calc_found_rows keyword messing things up?

FYI: This is PHP 7.3 and MySQL 5.6

Answer 1

Aah, after breaking my head against it for long and a lot googling, I found the problem! It is closely related to How to call a stored procedure within another stored procedure (PHP and mysqli)

Basically I had a case of PHP calling SP1, which in turn called SP2, and everything working fine in a mysql client but breaking when called by PHP!

It turns out the problem is that SP2 was SELECTing a result set (ie. SELECT without an INTO clause).

I re-wrote SP2 to necessarily do a SELECT INTO and that fixed the problem.

I think the ability to SELECT a result set without doing a SELECT INTO is a crappy feature in MySQL. Come to think of it, quite a few things crappy about MySQL (stored functions, exception propagation up the stack, poor compilation and syntax error pinpointing in stored procedures, bad concept of transaction boundaries, etc).

I think SELECTing a result set in a stored routine should be avoided at all costs.

Answer 2

PHP reports errors from Stored Procedures. The problem here is that calling Stored Procedures through mysqli is not an easy task. It's best to avoid Stored Procedures and mysqli if you can.

All errors can be reported by mysqli if you enable mysqli error reporting. Simply add this line before opening a new connection:

 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Note: until recently mysqli had plenty of bugs that either crashed PHP or didn't report errors properly. Keep your PHP up-to-date to avoid such problems.

Your main problem is that your stored procedure is producing results, which you are not reading in PHP. Results from MySQL are fetched sequentially, even if the queries are executed asynchronously. When an error happens after SELECT in your stored procedure then PHP will not throw the error immediately. You must fetch each result, even if you don't need them in PHP.

You can utilize a simple do-while loop to fetch all results.

$stmt = $mysqli->prepare('CALL SP1()');
echo $stmt->execute();
do {
    $stmt->get_result();
} while ($y = $stmt->next_result());