Why is My MySQL Stored Procedure Failing After Implementing Transactions?

Transaction Implementation in MySQL Stored Procedure

In an effort to enhance data reliability, a user has attempted to implement transactions within their MySQL stored procedure. However, the changes have rendered the procedure non-functional. Despite consulting documentation and searching online, the user has been unable to identify the error.

Upon examining the provided code, it is evident that there are two syntax errors:

1. Missing Comma in Exit Handler: The exit handler for SQL exceptions and warnings should have commas separating the conditions. Proper syntax dictates: DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING instead of DECLARE EXIT HANDLER FOR SQLEXCEPTION SQLWARNING.
2. Missing Semicolon for Exit Handler: The END statement that terminates the exit handler must also be followed by a semicolon, just like any other statement. This would look like: END;

Once these syntax errors are corrected, the procedure should function as intended, allowing the user to implement transactions seamlessly. The corrected code should resemble this:

<code class="sql">BEGIN

DECLARE poid INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
    ROLLBACK;
END;

START TRANSACTION;

    -- ADD option 5
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0);
    SET poid = (SELECT LAST_INSERT_ID());
    INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+');

    -- ADD option 12
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1);

    -- ADD option 13
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0);

COMMIT;

END</code>

The above is the detailed content of Why is My MySQL Stored Procedure Failing After Implementing Transactions?. For more information, please follow other related articles on the PHP Chinese website!