How to catch mysql SIGNAL error in a stored procedure

Question

I have a MySQL stored procedure, which calls a SIGNAL statement in case of invalid input:

my_proc(IN in_val bigint unsigned, OUT out_val bigint unsigned)
BEGIN
    IF (in_val <= 0) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Error!';
    END IF;
    SET out_val := in_val * 5;
END;

From Laravel I call this procedure like this:

DB::select("call my_proc('" . $num . "', @returnVal)");
$data = DB::select('SELECT @returnVal as returnVal');

In case of successful operation returned data looks like:

[0] => stdClass Object
     (
         [returnCode] => 100
     )

But in case of an error operation where SIGNAL is triggered, I just get:

[0] => stdClass Object
     (
         [returnCode] =>
     )

How to catch this error on the Laravel side?

Answer 1

In PHP 7.x, PDO's default error mode was silent, so you have to check for the return value false every time you execute a query. In PHP 8.0 and later, PDO's default error mode throws exceptions.

Here's a demo I tested with PHP 8.3.0 and MySQL 8.2.0:

<?php

$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '...');

$stmt = $pdo->prepare("CALL my_proc(?, @returnVal)");

$ok = $stmt->execute( [ 1 ] );

if ($ok === false) {
  print_r($stmt->errorInfo());
}

$result = $pdo->query("SELECT @returnVal")->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

When I call the procedure with value 1, this is the output, as expected:

Array
(
    [0] => Array
        (
            [@returnVal] => 5
        )

)

Then I changed the code to call the procedure with value 0, which should trigger the SIGNAL condition in your procedure:

$ok = $stmt->execute( [ 0 ] );

This is the output:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[45000]: <<Unknown error>>: 1644 Error! in /Users/billkarwin/work/SO/p.php:9
Stack trace:
#0 /Users/billkarwin/work/SO/p.php(9): PDOStatement->execute(Array)
#1 {main}
  thrown in /Users/billkarwin/work/SO/p.php on line 9

This is an exception being thrown by PDO when the stored procedure returns an error.

I tested setting the error mode to silent, which simulates the behavior of PDO on PHP 7.x.

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);

Then I call the procedure with value 0 again. Here is the output:

Array
(
    [0] => 45000
    [1] => 1644
    [2] => Error!
)
Array
(
    [0] => Array
        (
            [@returnVal] => 
        )

)

Note the error info is output only because I wrote code to check for it. The error is in fact returned by PDO, but this doesn't interrupt execution if exceptions are not enabled. If you don't check for the error returned explicitly, then it's ignored, and your script goes on to the next query.

The next query shows a blank value of @returnVal because it was never set by the procedure. The procedure returned because of the SIGNAL before it set that value. The default value of any user variable in MySQL is NULL.

It's up to you if you want to enable exceptions in PDO. Read https://www.php.net/manual/en/pdo.error-handling.php for details.

If you don't want to enable exceptions, then you must write more code to check for error return status.