| Bug #78144 | Error handler not being triggered for multi-table update | ||
|---|---|---|---|
| Submitted: | 19 Aug 2015 22:34 | Modified: | 21 Aug 2015 10:12 |
| Reporter: | Mark Ogilvie | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.6.19-log | OS: | Linux (RDS) |
| Assigned to: | CPU Architecture: | Any | |
[20 Aug 2015 14:46]
Valeriy Kravchuk
Same problem with 5.6.24.
[20 Aug 2015 19:12]
MySQL Verification Team
mysql 5.7 > create database test_handler_bug;
Query OK, 1 row affected (0.13 sec)
mysql 5.7 > use test_handler_bug;
Database changed
mysql 5.7 > drop table if exists test_handler_bug.t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql 5.7 > CREATE TABLE test_handler_bug.t1 (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.39 sec)
mysql 5.7 > Insert into test_handler_bug.t1 (s1) Values (1);
Query OK, 1 row affected (0.11 sec)
mysql 5.7 > Insert into test_handler_bug.t1 (s1) Values (2);
Query OK, 1 row affected (0.09 sec)
mysql 5.7 >
mysql 5.7 > drop table if exists test_handler_bug.t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.7 > create table test_handler_bug.t2 (s1 int, s2 int);
Query OK, 0 rows affected (0.22 sec)
mysql 5.7 > Insert into test_handler_bug.t2 (s1, s2) Values (1,2);
Query OK, 1 row affected (0.04 sec)
mysql 5.7 >
mysql 5.7 > drop procedure if exists handlerdemo;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql 5.7 > delimiter //
mysql 5.7 > CREATE PROCEDURE handlerdemo ()
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' set @message = 'The handler has been triggered, no problems found';
->
-> set @message = 'The handler has NOT been triggered, something is wrong';
->
->
-> -- This doesn't trigger the error handler
-> update
-> test_handler_bug.t1
-> inner join
-> test_handler_bug.t2
-> on t1.s1 = t2.s1
-> set t1.s1 = t2.s2
-> ;
-> END//
Query OK, 0 rows affected (0.03 sec)
mysql 5.7 > delimiter ;
mysql 5.7 >
mysql 5.7 > CALL handlerdemo();
Query OK, 0 rows affected (0.08 sec)
mysql 5.7 > select @message;
+---------------------------------------------------+
| @message |
+---------------------------------------------------+
| The handler has been triggered, no problems found |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql 5.7 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+-------------------------------------+
| Variable_name | Value |
+-------------------------+-------------------------------------+
| innodb_version | 5.7.9 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.9 |
| version_comment | Source distribution PULL 2015/08/14 |
| version_compile_machine | x86_64 |
| version_compile_os | Win64 |
+-------------------------+-------------------------------------+
7 rows in set, 1 warning (0.09 sec)
[20 Aug 2015 19:15]
MySQL Verification Team
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.27 Source distribution PULL: 2015/08/14
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.6 > create database test_handler_bug;
Query OK, 1 row affected (0.08 sec)
mysql 5.6 > use test_handler_bug;
Database changed
mysql 5.6 > drop table if exists test_handler_bug.t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.6 > CREATE TABLE test_handler_bug.t1 (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.28 sec)
mysql 5.6 > Insert into test_handler_bug.t1 (s1) Values (1);
Query OK, 1 row affected (0.08 sec)
mysql 5.6 > Insert into test_handler_bug.t1 (s1) Values (2);
Query OK, 1 row affected (0.06 sec)
mysql 5.6 >
mysql 5.6 > drop table if exists test_handler_bug.t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.6 > create table test_handler_bug.t2 (s1 int, s2 int);
Query OK, 0 rows affected (0.25 sec)
mysql 5.6 > Insert into test_handler_bug.t2 (s1, s2) Values (1,2);
Query OK, 1 row affected (0.05 sec)
mysql 5.6 >
mysql 5.6 > drop procedure if exists handlerdemo;
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql 5.6 > delimiter //
mysql 5.6 > CREATE PROCEDURE handlerdemo ()
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' set @message = 'The handler has been triggered, no problems found';
->
-> set @message = 'The handler has NOT been triggered, something is wrong';
->
->
-> -- This doesn't trigger the error handler
-> update
-> test_handler_bug.t1
-> inner join
-> test_handler_bug.t2
-> on t1.s1 = t2.s1
-> set t1.s1 = t2.s2
-> ;
-> END//
Query OK, 0 rows affected (0.01 sec)
mysql 5.6 > delimiter ;
mysql 5.6 >
mysql 5.6 > CALL handlerdemo();
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql 5.6 > select @message;
+--------------------------------------------------------+
| @message |
+--------------------------------------------------------+
| The handler has NOT been triggered, something is wrong |
+--------------------------------------------------------+
1 row in set (0.00 sec)
[21 Aug 2015 10:12]
MySQL Verification Team
Thank you for the bug report. Only 5.6 is affected.
C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.46-log Source distribution PULL: 2015/08/14
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.5 > use test
Database changed
mysql 5.5 > create database test_handler_bug;
Query OK, 1 row affected (0.06 sec)
mysql 5.5 > use test_handler_bug;
Database changed
mysql 5.5 > drop table if exists test_handler_bug.t1;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql 5.5 > CREATE TABLE test_handler_bug.t1 (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.14 sec)
mysql 5.5 > Insert into test_handler_bug.t1 (s1) Values (1);
Query OK, 1 row affected (0.08 sec)
mysql 5.5 > Insert into test_handler_bug.t1 (s1) Values (2);
Query OK, 1 row affected (0.05 sec)
mysql 5.5 >
mysql 5.5 > drop table if exists test_handler_bug.t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.5 > create table test_handler_bug.t2 (s1 int, s2 int);
Query OK, 0 rows affected (0.06 sec)
mysql 5.5 > Insert into test_handler_bug.t2 (s1, s2) Values (1,2);
Query OK, 1 row affected (0.06 sec)
mysql 5.5 >
mysql 5.5 > drop procedure if exists handlerdemo;
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql 5.5 > delimiter //
mysql 5.5 > CREATE PROCEDURE handlerdemo ()
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' set @message = 'The handler has been triggered, no problems found';
->
-> set @message = 'The handler has NOT been triggered, something is wrong';
->
->
-> -- This doesn't trigger the error handler
-> update
-> test_handler_bug.t1
-> inner join
-> test_handler_bug.t2
-> on t1.s1 = t2.s1
-> set t1.s1 = t2.s2
-> ;
-> END//
Query OK, 0 rows affected (0.02 sec)
mysql 5.5 > delimiter ;
mysql 5.5 >
mysql 5.5 > CALL handlerdemo();
Query OK, 0 rows affected, 3 warnings (0.06 sec)
mysql 5.5 > select @message;
+---------------------------------------------------+
| @message |
+---------------------------------------------------+
| The handler has been triggered, no problems found |
+---------------------------------------------------+
1 row in set (0.00 sec)
C:\dbs>5.6\bin\mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test_handler_bug;
Query OK, 1 row affected (0.01 sec)
mysql> use test_handler_bug;
Database changed
mysql> drop table if exists test_handler_bug.t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE test_handler_bug.t1 (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.20 sec)
mysql> Insert into test_handler_bug.t1 (s1) Values (1);
Query OK, 1 row affected (0.02 sec)
mysql> Insert into test_handler_bug.t1 (s1) Values (2);
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> drop table if exists test_handler_bug.t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table test_handler_bug.t2 (s1 int, s2 int);
Query OK, 0 rows affected (0.18 sec)
mysql> Insert into test_handler_bug.t2 (s1, s2) Values (1,2);
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> drop procedure if exists handlerdemo;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE handlerdemo ()
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' set @message = 'The handler has been triggered, no problems found';
->
-> set @message = 'The handler has NOT been triggered, something is wrong';
->
->
-> -- This doesn't trigger the error handler
-> update
-> test_handler_bug.t1
-> inner join
-> test_handler_bug.t2
-> on t1.s1 = t2.s1
-> set t1.s1 = t2.s2
-> ;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> CALL handlerdemo();
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> select @message;
+--------------------------------------------------------+
| @message |
+--------------------------------------------------------+
| The handler has NOT been triggered, something is wrong |
+--------------------------------------------------------+
1 row in set (0.00 sec)
[24 Nov 2015 8:59]
Ilya Zvyagin
I have same problem but with simple update, not multi-table (without joins). Server version: 5.6.27-0ubuntu0.14.04.1-log (Ubuntu)
[24 Nov 2015 9:07]
Ilya Zvyagin
Why marked non-critical ? This can cause data loss or damage. This can lead to inconsistent transactions being committed.
Description: I have stored procedures with error handlers, and update statements that cause duplicate-key errors. I expect the duplicate key errors to activate the error handlers, but it is not happening, instead the procedures are terminating as if the error handler was not there. This happens for Update statements, but not for Insert statements. It happens for Update statements with multi-table joins, but not "single-table" update statments. It happens for all configurations of error handler: continue and exit; for SQLEXCEPTION and FOR SQLSTATE '23000'. I have only tested this on MySQL on AWS, but I have seen the problem several separate AWS instances. One respondent on the MySQL forum has been unable to replicate the problem on his local MySQL server so I believe the problem does not happen on all platforms. How to repeat: -- create database test_handler_bug; use test_handler_bug; drop table if exists test_handler_bug.t1; CREATE TABLE test_handler_bug.t1 (s1 INT, PRIMARY KEY (s1)); Insert into test_handler_bug.t1 (s1) Values (1); Insert into test_handler_bug.t1 (s1) Values (2); drop table if exists test_handler_bug.t2; create table test_handler_bug.t2 (s1 int, s2 int); Insert into test_handler_bug.t2 (s1, s2) Values (1,2); drop procedure if exists handlerdemo; delimiter // CREATE PROCEDURE handlerdemo () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' set @message = 'The handler has been triggered, no problems found'; set @message = 'The handler has NOT been triggered, something is wrong'; -- This doesn't trigger the error handler update test_handler_bug.t1 inner join test_handler_bug.t2 on t1.s1 = t2.s1 set t1.s1 = t2.s2 ; END// delimiter ; CALL handlerdemo(); select @message;