Bug #69872 UPDATE in stored function is not rolled back

• Submitted: 30 Jul 2013 15:53
• Modified: 30 Jul 2013 16:54
• Reporter: Elena Stepanova
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Any
• CPU Architecture: Any

[30 Jul 2013 15:53] Elena Stepanova

Description:
Filing on behalf of our user, initially reported as https://mariadb.atlassian.net/browse/MDEV-4826

If a stored function containing an error handler for ER_DUP_ENTRY updates an InnoDB table and the update hits the duplicate key error, rows previously modified by this update remain modified -- the update is not rolled back. 

When the same is done in a stored procedure, it works as expected. 

Also, we could not find any documentation in regard to how InnoDB error handling is supposed to interact with SP error handling. 

Test case 1 final output:
+----+
| id |
+----+
|  2 |
| 10 |
+----+

Test case 2 final output:
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

How to repeat:
# Test case 1 - stored function, UPDATE is not rolled back

DELIMITER ||
USE test;
DROP TABLE IF EXISTS t1, t2;
DROP FUNCTION IF EXISTS f1;
CREATE TABLE t1 (
  id TINYINT UNSIGNED PRIMARY KEY
) ENGINE=InnoDB;
INSERT INTO t1
  VALUES
    (1),
    (2);
CREATE FUNCTION f1() RETURNS TEXT
BEGIN
  DECLARE CONTINUE HANDLER
    FOR 1062
    DO NULL;
  UPDATE t1 SET id = 10 ORDER BY id ASC;
  RETURN TRUE;
END;
||
DELIMITER ;
SET autocommit = 0;
START TRANSACTION;
SELECT f1();
COMMIT;
SHOW WARNINGS;
SELECT * FROM t1;

# Test case 2 - stored procedure, UPDATE is rolled back

DELIMITER ||
USE test;
DROP TABLE IF EXISTS t1, t2;
DROP FUNCTION IF EXISTS p1;
CREATE TABLE t1 (
id TINYINT UNSIGNED PRIMARY KEY
) ENGINE=InnoDB;
INSERT INTO t1
VALUES
(1),
(2);
CREATE PROCEDURE p1() 
BEGIN
DECLARE CONTINUE HANDLER
FOR 1062
DO NULL;
UPDATE t1 SET id = 10 ORDER BY id ASC;
END;
||
DELIMITER ;
SET autocommit = 0;
START TRANSACTION;
CALL p1();
COMMIT;
SHOW WARNINGS;
SELECT * FROM t1;

[30 Jul 2013 16:53] Dmitry Lenev

Hello Elena!

Thanks for reporting this issue!!!

The behaviour that you observe is caused by the same problem as already reported bug #51006 "InnoDB does not correctly rollback statement on error inside stored function". Therefore I am closing this report as a duplicate of #51006.

Please follow bug#51006 to track progress on this issue (you are also welcome to use "Affects me" button).

Best regards,
Dmitry