Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

PetroGromovo's avatar

How to catch mysql SIGNAL error in a stored procedure ?

On laravel 10 site with mysql 8 I have a stored procedure, which raise error SIGNAL in case of invalid operation

create procedure completeTask(IN in_taskId bigint unsigned,
                                                      IN completeSubtasks tinyint unsigned,
                                                      IN in_userId bigint unsigned, OUT retCount bigint unsigned)
BEGIN
    DECLARE taskCreatorId bigint unsigned;

    SELECT concat('in_taskId : ', in_taskId);
    SELECT concat('in_userId : ', in_userId);

    SELECT creator_id INTO taskCreatorId from tasks WHERE id = in_taskId;
    SELECT concat('taskCreatorId : ', taskCreatorId);

    IF (in_userId != taskCreatorId) THEN
        SIGNAL SQLSTATE '45000'  -- I RAISE ERROR
            SET MESSAGE_TEXT = 'Only creator can complete task !';
        SET retCount := -1;  -- NOT SHUR IF THERE IS A SENSE TO SET NEGATIVE CODE - ASS ERROR OPERATION
    END IF;

    IF (completeSubtasks = 1) THEN
        UPDATE tasks SET status = 'D', completed_at = NOW() WHERE tasks.parent_id = in_taskId;
    END IF;

    UPDATE tasks SET status = 'D', completed_at = NOW() WHERE tasks.id = in_taskId;

    SET retCount := in_taskId;
    SELECT concat('retCount is ', retCount); -- SET retCount FOR SUCCESS
END;

From laravel I call procedure:

    DB::select("call completeTask('" . $id . "', " . "1, " . Auth::user()->id . ", @returnCode)");
    $data = DB::select('SELECT @returnCode as returnCode');

In case of successfull operation returned data looks like :

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

In case of error operation(SIGNAL triggered) I just got stdClass Object with empty returnCode.

How to catch this error on laravel side ? Which SQLSTATE for error better to use ? Has block with SIGNAL return some code ?

Thanks in advance!

0 likes
4 replies
tisuchi's avatar

@petrogromovo Have you tried with try-catch block?


try {
    DB::select("call completeTask(...)");
    // Your code to fetch the result
} catch (\Illuminate\Database\QueryException $ex) {
    // Handle the exception
    $errorCode = $ex->getCode();
    $errorMessage = $ex->getMessage();
    // Do something with the error
}
PetroGromovo's avatar

@tisuchi I tried, but no exception is generated in this case. Have I to use some other mysql functionality , not SIGNAL ?

Tray2's avatar

I would not do those things in a stored procedure, I would do those checks on the php side instead.

1 like
PetroGromovo's avatar

@Tray2 That make sense, but also I would like to know how can I do it in stored proc

Please or to participate in this conversation.