Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.0.3, 5.5.32, 5.1.67, 5.2.14, 5.3.12, 10.0.10
-
None
Description
This happened to me in 10.0 on WinXP; don't know about other platforms/versions.
First, I create a 2-rows table with a primary key. Then I start a transaction and invoke a function which tries to set both values to 10. The first value is updated, the second value fails (duplicate value), but the first operation is not rollback, so I get a row with id=10.
Here's the code:
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; |
As far as I understand from this page, this is a bug:
http://dev.mysql.com/doc/refman/5.6/en/innodb-error-handling.html
"A duplicate-key error rolls back the SQL statement, if you have not specified the IGNORE option in your statement."
In my test a DECLARE CONTINUE HANDLER is used, but I couldn't find transaction information in the DECLARE HANDLER page or similar pages.