I have the following stored procedure in Mysql 5.1.73-cll:
BEGIN
DECLARE finished1 INTEGER DEFAULT 0;
DECLARE cursor1 CURSOR FOR SELECT ... FROM table1 WHERE Id= 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished1 = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
CALL Debug('SQLEXCEPTION');
ROLLBACK;
CALL Debug('Rollback executed');
END;
START TRANSACTION;
INSERT INTO table2(...) VALUES (...); /* Statement 1*/
OPEN cursor1;
label1: LOOP
FETCH cursor1 INTO var1, var2;
IF finished1 = 1 THEN
INSERT INTO table1(...) VALUES (...); /* Statement 2*/
LEAVE label1;
END IF;
UPDATE table1 SET ... WHERE Id=1; /* Statement 3*/
LEAVE label1;
END LOOP label1;
CLOSE cursor1;
COMMIT;
END
When I (on purpose) introduce a SQL error in Statement 3, and ensure that finished1 = 0, I expect Statement 1 to be rolled back, but it is not. By my Debug procedure, I know that the Rollback statement is executed. All tables are InnoDb. What am I doing wrong?