mysqli signal to php error

Question

I have Mysql stored procedure, that in some cases give a signal error like this:

    CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertAction`(IN `EmployerID` INT, IN `StoreFromID` INT, IN `StoreToID` INT, IN `StoreID` INT, IN `ProductID` INT, IN `Quantity` DECIMAL(10,2), IN `DualOperation` TINYINT, IN `inOrOut` TINYINT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    START TRANSACTION;
        SELECT @lastActionID;
        SELECT @lastTransferID;
        SELECT @retval;
        SELECT SUM(ad.Quantity) INTO @retVal FROM productin pri JOIN actiondetails ad ON ad.ID=pri.ID;
        IF DualOperation = 1
            THEN
                IF @retVal>Quantity
                    THEN
                        INSERT INTO Actions (EmployerID, StorehouseID, `Date`)
                            VALUES (EmployerID, StoreFromID, CURDATE());
                        SET @lastActionID = (SELECT ID FROM Actions ORDER BY ID DESC LIMIT 1);
                        INSERT INTO ProductTransfer (ID, TransferType)
                            VALUES (@lastActionID, 0);

                        INSERT INTO ActionDetails (ID,ProductID, Quantity)
                            VALUES (@lastActionID, ProductID, Quantity);

                        SET @lastTransferID = (SELECT ID FROM ProductTransfer ORDER BY ID DESC LIMIT 1);
                        INSERT INTO Actions (EmployerID, StorehouseID, `Date`) VALUES (EmployerID, StoreToID, CURDATE());
                        SET @lastActionID = (SELECT ID FROM Actions ORDER BY ID DESC LIMIT 1);
                        INSERT INTO ProductTransfer (ID, TransferType, ParentID) VALUES (@lastActionID, 1, @lastTransferID);

                        INSERT INTO ActionDetails (ID,ProductID, Quantity)
                            VALUES (@lastActionID, ProductID, Quantity);
                    ELSE
                        SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Not enough materials';
            END IF;
        ELSE
                INSERT INTO Actions (EmployerID, StorehouseID, `Date`)
                    VALUES (EmployerID, StoreID, CURDATE());
                SET @lastActionID = (SELECT ID FROM Actions ORDER BY ID DESC LIMIT 1);
                INSERT INTO ActionDetails (ID, ProductID, Quantity)
                    VALUES (@lastActionID, ProductID, Quantity);
                IF InOrOut = 0
                    THEN
                        INSERT INTO ProductIn (ID, OrganizationID) values (@lastActionID, NULL);
                    ELSE
                        IF @retVal>Quantity
                            THEN
                                INSERT INTO ProductOut (ID, OrganizationID) values (@lastActionID, NULL);
                            ELSE
                                SIGNAL SQLSTATE '45000'
                            SET MESSAGE_TEXT = 'Not enough materials';
                    END IF;
                END IF;
        END IF;
    COMMIT;
END

When i run this code through Mysql query everything seems to be working just fine. it gives a signal of "not enough materials" IF @retVal<=Quantity and no records are inserted(works as it should be)... But when i call this procedure from PHP it simply doesn't give any error. none of rows are inserted but i cant get notification that the oppreration failed... here is php code:

$mysqli->query("CALL `InsertAction`('6', '1', '2', '0', '13', '431243241', '1', '0')");

the $mysqli->sqlstate gives 0000. how should i understand the procedure was done or got signal?

so what i really want is, if @retVal<=Quantity then give php exception. and this code prints "string" out:

try {
$mysqli->query("CALL `InsertAction`(6, 1, 2, 0, 13, 431243241, 1, 0)");
}
catch (Exception $e){
    echo "string";
}

Try passing INT parameters as INT and not strings i.e. remove the single quotes around numbers that make then text fields — RiggsFolly, Jul 11 '16 at 14:07
Whoever wrote this has the signal state for those 2 conditions. PHP can handle it like any try / catch exception. — Drew, Jul 11 '16 at 14:24
@RiggsFolly , thanks for your comment. sure the numbers should be passed as numbers, my bad. but this doesnt solve the problem. — M.G., Jul 13 '16 at 16:18
@Drew , thanks for comment, but it is another topic. also PHP can't handle it with try catch. as i mentioned it doesn't give any exception... — M.G., Jul 13 '16 at 16:22
Point being is that you have the code (we don't). So tweak it. — Drew, Jul 13 '16 at 16:23

RiggsFolly · Answer 1 · 2016-07-13T16:47:57.493

MYSQLI does not throw exceptions you have to do the old fashioned way

$result = $mysqli->query("CALL InsertAction(6, 1, 2, 0, 13, 431243241, 1, 0)");
if ($result === false) {
    echo $mysqli->error;
    exit;
}

Also the 6th param is a decimal (10,2) not an int the number you are passing does not fit that 10,2 which if I rememebr correctly mean 8 digits before the decimal place and 2 after, so try a number that fits like

 $result = $mysqli->query("CALL InsertAction(6, 1, 2, 0, 13, 4312432.41, 1, 0)");

score 0 · Accepted Answer · answered Jul 13 '16 at 17:55

The real problem in the code above is that variables are not Declared. so i changed this Select statements to Declare statements.

BEGIN
DECLARE lastActionID INT unsigned;
DECLARE lastTransferID INT unsigned;
DECLARE retval INT unsigned;
    START TRANSACTION;
        SELECT SUM(ad.Quantity) INTO retVal FROM productin pri JOIN actiondetails ad ON ad.ID=pri.ID;
        IF DualOperation = 1
            THEN
                IF retVal>Quantity
                    THEN
                        INSERT INTO Actions (EmployerID, StorehouseID, `Date`)
                            VALUES (EmployerID, StoreFromID, CURDATE());
                        SET lastActionID = (SELECT ID FROM Actions ORDER BY ID DESC LIMIT 1);
                        INSERT INTO ProductTransfer (ID, TransferType)
                            VALUES (lastActionID, 0);

                        INSERT INTO ActionDetails (ID, ProductID, Quantity)
                            VALUES (lastActionID, ProductID, Quantity);

                        SET lastTransferID = (SELECT ID FROM ProductTransfer ORDER BY ID DESC LIMIT 1);
                        INSERT INTO Actions (EmployerID, StorehouseID, `Date`) VALUES (EmployerID, StoreToID, CURDATE());
                        SET lastActionID = (SELECT ID FROM Actions ORDER BY ID DESC LIMIT 1);
                        INSERT INTO ProductTransfer (ID, TransferType, ParentID) VALUES (lastActionID, 1, lastTransferID);

                        INSERT INTO ActionDetails (ID,ProductID, Quantity)
                            VALUES (lastActionID, ProductID, Quantity);
                    ELSE
                        SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Not enough materials';
            END IF;
        ELSE
                INSERT INTO Actions (EmployerID, StorehouseID, `Date`)
                    VALUES (EmployerID, StoreID, CURDATE());
                SET @lastActionID = (SELECT ID FROM Actions ORDER BY ID DESC LIMIT 1);
                INSERT INTO ActionDetails (ID, ProductID, Quantity)
                    VALUES (lastActionID, ProductID, Quantity);
                IF InOrOut = 0
                    THEN
                        INSERT INTO ProductIn (ID, OrganizationID) values (lastActionID, NULL);
                    ELSE
                        IF retVal>Quantity
                            THEN
                                INSERT INTO ProductOut (ID, OrganizationID) values (lastActionID, NULL);
                            ELSE
                                SIGNAL SQLSTATE '45000'
                            SET MESSAGE_TEXT = 'Not enough materials';
                    END IF;
                END IF;
        END IF;
    COMMIT;
    select true;
END

now i am able to get $mysqli->sqlstate 45000 and check whether there was an error in procedure

score 0 · Answer 3 · answered Nov 24 '18 at 23:57

0

Actualy you'll need to make some preparation to catch exceptions from MySql in PHP.

Use:

$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ALL ^ MYSQLI_REPORT_INDEX;

answered Nov 24 '18 at 23:57

TARiK

16
1

score -1 · Answer 4 · answered Mar 25 '23 at 13:07

1. You can use try/catch statement:

PHP side:

$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
$dbh = new mysqli(...);

try {
    $result = $dbh->query("CALL proc_test_2(2)");
} catch (Throwable $err) {
    print_r([
        "code" => $err->getCode(),
        "msg" => $err->getMessage(),
        "trace" => $err->getTraceAsString()
    ]);
}

MySQL routine (in this i show "shortcut" for SQLSTATE - ERR_CUSTOM):

DROP PROCEDURE IF EXISTS proc_test_2;
CREATE PROCEDURE proc_test_2 (IN p_id INT)
BEGIN
    DECLARE ERR_CUSTOM CONDITION FOR SQLSTATE '45000';

    IF (p_id > 10) THEN
        SIGNAL ERR_CUSTOM SET MESSAGE_TEXT = 'ERR #1', MYSQL_ERRNO = 1;
    END IF;

    IF (p_id < 5) THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'ERR #2', MYSQL_ERRNO = 222;
    END IF;

    SELECT p_id;
END;

2. Another solution

DROP PROCEDURE IF EXISTS proc_test;
CREATE
    PROCEDURE proc_test (
    IN p_id INT
)
BEGIN
    DECLARE E_STATE CHAR(5) DEFAULT '00000';
    DECLARE E_MSG TEXT DEFAULT 'SUCCESS';
    DECLARE E_NUM, DIAG_CNT INT DEFAULT 0;

    DECLARE ERR_CUSTOM CONDITION FOR SQLSTATE '45000';

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS DIAG_CNT = NUMBER;
            GET DIAGNOSTICS CONDITION DIAG_CNT E_STATE = RETURNED_SQLSTATE, E_MSG = MESSAGE_TEXT, E_NUM = MYSQL_ERRNO;
            SELECT CAST(JSON_OBJECT('e_state', E_STATE, 'e_msg', E_MSG, 'e_num', E_NUM) AS JSON) app_debug2;
        END;

    IF (p_id > 10) THEN
        SIGNAL ERR_CUSTOM SET MESSAGE_TEXT = 'Invalid p_id', MYSQL_ERRNO = 1;
    END IF;

    IF (p_id < 5) THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'INVALID #2', MYSQL_ERRNO = 2;
    END IF;

    SELECT p_id;
END;

and next:

CALL proc_test(5);
CALL proc_test(11);

3. You can mix them all (ex. for logging purpose)

MySQL log table:

DROP TABLE IF EXISTS error_logs;
CREATE TABLE error_logs (
    error_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    error_log JSON
);

MySQL routine:

DROP PROCEDURE IF EXISTS proc_test_3;
CREATE PROCEDURE proc_test_3(
    IN p_id INT
)
BEGIN
    DECLARE E_STATE CHAR(5) DEFAULT '00000';
    DECLARE E_MSG TEXT DEFAULT 'SUCCESS';
    DECLARE E_NUM, DIAG_CNT INT DEFAULT 0;

    DECLARE ERR_CUSTOM CONDITION FOR SQLSTATE '45000';

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS DIAG_CNT = NUMBER;
            GET DIAGNOSTICS CONDITION DIAG_CNT E_STATE = RETURNED_SQLSTATE, E_MSG = MESSAGE_TEXT, E_NUM = MYSQL_ERRNO;
            INSERT INTO error_logs (error_date, error_log)
                SELECT CURRENT_TIMESTAMP, CAST(JSON_OBJECT('e_state', E_STATE, 'e_msg', E_MSG, 'e_num', E_NUM) AS JSON);
            RESIGNAL;
        END;

    IF (p_id > 10) THEN
        SIGNAL ERR_CUSTOM SET MESSAGE_TEXT = 'Invalid p_id', MYSQL_ERRNO = 111;
    END IF;

    IF (p_id < 5) THEN
        SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'INVALID #2', MYSQL_ERRNO = 222;
    END IF;

    SELECT p_id;
END;

PHP side:

$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
$dbh = new mysqli(...);

try {
    $result = $dbh->query("CALL proc_test_3(2)");
} catch (Throwable $err) {
    print_r([
        "code" => $err->getCode(),
        "msg" => $err->getMessage(),
        "trace" => $err->getTraceAsString()
    ]);
}

Also if you want to handle transaction commit/rollback, you should add ROLLBACK before INSERT... RESIGNAL in last example. — Marek Lisiecki, Mar 25 '23 at 13:13

mysqli signal to php error

4 Answers4

1. You can use try/catch statement:

2. Another solution

3. You can mix them all (ex. for logging purpose)