mysql transaction error handling

Question

 DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND 
BEGIN 
    ROLLBACK; 
END;
START TRANSACTION;      

    UPDATE tbl_order SET TransactionID="abc" WHERE OrderID=1;
    UPDATE tbl_order SET TransactionID="xyz" WHERE OrderID=;
    UPDATE tbl_order SET TransactionID="zzz" WHERE OrderID=13;


COMMIT;

for some reason order 1 and 13 are filled without rollback and i get syntax error for the exit hadler.

Query:  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN ROLLBACK

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND 
BEGIN 
    ROLLBACK' at line 1

can someone help me figure out what i'm doing wrong?

Thanks in advance

EDIT

UPDATE tbl_order SET TransactionID="xyz" WHERE OrderID=;

is intentional

Shot in the dark: is the semicolon after `rollback` prematurely ending the exit handler? — sarnold, Feb 02 '12 at 00:51
so anyway i went ahead and did it with code instead of using mysql query to handle errors . Assuming an open connection MySqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.Transaction = conn.BeginTransaction(); try{ cmd.CommandText = strSQL; cmd.ExecuteNonQuery(); cmd.Transaction.Commit(); }catch (Exception ex){ cmd.Transaction.Rollback(); dbLog.ErrorFormat("*** Transaction Rollback: {0}\n Params: {1}\n Ex: {2}\n", strSQL, ex.InnerException); } — robert, Feb 02 '12 at 04:54
also it doesn't seem to give me any errors if the sql is formatted correctly and the code is in a stored procedure. — robert, Feb 02 '12 at 04:57

score 2 · Accepted Answer · answered Feb 09 '12 at 17:19

2

I believe exit handlers can only be used in stored procedures. The documentation doesn't explicitly state this, but alludes to

Conditions may arise during stored program execution that require special handling

http://dev.mysql.com/doc/refman/5.1/en/condition-handling.html

answered Feb 09 '12 at 17:19

sreimer

4,913
2
33
43

any example for handling transaction error and rollback – Harikrushna Patel Mar 10 '22 at 10:03

score 0 · Answer 2 · answered Nov 11 '22 at 18:39

I know this is quite outdated topic, but I encountered the same error when declaring my own procedure with transaction and error handling. The above procedure looks well, something OP didn't copy as it seems. The order is important and it should look like this:

Procedure declaration
Declaration of variables
ERROR HANDLING
Start transaction
Statements
Commit
End

In my case, it looks like this:

DELIMITER //
CREATE PROCEDURE pAddUser(IN _nick VARCHAR(30))
BEGIN
    DECLARE doesUserExist BOOLEAN;
    DECLARE accountCreationDate DATE;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;

    SET doesUserExist = (SELECT EXISTS(SELECT * FROM tblUser WHERE nick=_nick));
    IF NOT doesUserExist THEN
        SET accountCreationDate = CURDATE();
        INSERT INTO tblUser (nick, accountCreation) VALUES (_nick, CURDATE());
    ELSE
        SELECT CONCAT("Nick '", _nick, "' is in use!") AS 'Console output';
    END IF;

    COMMIT;

END;
//
DELIMITER ;

mysql transaction error handling

2 Answers2

Linked