Hi Federico,
It seems to be a tricky issue.
On one hand, the current behavior looks logical me. You specifically instruct your function to ignore the duplicate key condition by adding an empty continue handler:
DECLARE CONTINUE HANDLER
FOR 1062
DO NULL;
If you hadn't done that, the result would have been as you expected.
On the other hand, it's not as obvious as a case with two subsequent updates would have been:
DECLARE CONTINUE HANDLER
FOR 1062
DO NULL;
UPDATE t1 SET id = 10 WHERE id = 1;
UPDATE t1 SET id = 10 WHERE id = 2;
Here one could totally expect the result of the first statement to be preserved; but should it be so inside a single statement? I can't say for certain one way or another. My bet is on "it's by design (of error handlers in stored procedures)", but if you have doubts, I can get a second opinion. Alternatively, you can try to file a bug at MySQL and see what they think about it (it works the same way in MySQL).
One thing is obvious though, the behavior of InnoDB error handling together with stored procedures error handling should be documented somewhere; maybe it is, but I couldn't find it. So, at least the doc request is due.
Hi Elena,
Thanks for your answer.
I don't have an opinion about what behavior is correct. What really confuses me is that I can't find this information in the docs. So I am not sure if this behaviour is intended and, most importantly for me, if I can expect it to remain the same in future versions.
Hi again,
I tried to repeat a very similar test, but using a Stoed Procedure instead of a Function. In this case, the transaction IS rolled back. There may be some reason I don't understand... but still, I wasn't able to find any information in the docs which warns users about this difference:
MariaDB [test]> DELIMITER ||
|
MariaDB [test]> DROP TABLE IF EXISTS t1; |
-> DROP PROCEDURE IF EXISTS sp1; |
-> CREATE TABLE t1 ( |
-> id TINYINT UNSIGNED PRIMARY KEY |
-> ) ENGINE=InnoDB;
|
-> INSERT INTO t1 |
-> VALUES |
-> (1),
|
-> (2);
|
-> CREATE PROCEDURE sp1() |
-> BEGIN |
-> DECLARE CONTINUE HANDLER |
-> FOR 1062 |
-> DO NULL; |
-> UPDATE t1 SET id = 10 ORDER BY id ASC; |
-> END; |
-> ||
|
Query OK, 0 rows affected (0.11 sec) |
|
|
Query OK, 0 rows affected (0.35 sec) |
|
|
Query OK, 0 rows affected (0.70 sec) |
|
|
Query OK, 2 rows affected (0.94 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
Query OK, 0 rows affected (1.33 sec) |
|
|
MariaDB [test]> DELIMITER ;
|
MariaDB [test]> SET autocommit = 0; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> START TRANSACTION; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> CALL sp1();
|
Query OK, 0 rows affected, 1 warning (0.00 sec) |
|
|
MariaDB [test]> COMMIT; |
Query OK, 0 rows affected (0.08 sec) |
|
|
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+----------------------------------------+ |
| Level | Code | Message | |
+-------+------+----------------------------------------+ |
| Error | 1062 | Duplicate entry '10' for key 'PRIMARY' | |
+-------+------+----------------------------------------+ |
1 row in set (0.01 sec) |
|
|
MariaDB [test]> SELECT * FROM t1; |
+----+ |
| id |
|
+----+ |
| 1 |
|
| 2 |
|
+----+ |
2 rows in set (0.00 sec) |
Yes, I agree, that's weird. I think it's worth filing for upstream and seeing what they say – if nothing else, this way we'll get it documented.
Are you willing to file a bug at bugs.mysql.com, or should I do it on your behalf?
I'm sorry, I dont want to register on Oracle site because they ask too many personal information.
No problem, I understand your point. Filed as http://bugs.mysql.com/bug.php?id=69872
It turned out to be a duplicate of http://bugs.mysql.com/bug.php?id=51006. I pressed the "Affect me" button on your behalf 