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 Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: 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