| Bug #74751 | MySQL Cluster trigger ON DELETE false positive | ||
|---|---|---|---|
| Submitted: | 8 Nov 2014 19:39 | Modified: | 10 Dec 2014 17:47 |
| Reporter: | Odis Yuck | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S1 (Critical) |
| Version: | 5.6.19-ndb-7.3.6-cluster-gpl, 5.6.21-ndb-7.3.7 | OS: | Linux (Centos 7 (didn't check other systems)) |
| Assigned to: | CPU Architecture: | Any | |
[8 Nov 2014 19:47]
Odis Yuck
have fixed synopsis and OS description.
[9 Nov 2014 7:24]
MySQL Verification Team
Hello Odis, Thank you for the bug report and test case. Thanks, Umesh
[9 Nov 2014 7:25]
MySQL Verification Team
// MySQL Cluster Server - 7.3.7
mysql> SHOW VARIABLES LIKE '%VERSION%';
+-------------------------+------------------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------------------+
| innodb_version | 5.6.21 |
| ndb_version | 459527 |
| ndb_version_string | ndb-7.3.7 |
| ndbinfo_version | 459527 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.21-ndb-7.3.7-cluster-commercial-advanced-log |
| version_comment | MySQL Cluster Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+------------------------------------------------------+
10 rows in set (0.00 sec)
mysql> DROP TABLE IF EXISTS `bug_helper`;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> DROP TABLE IF EXISTS `cluster_bug`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> CREATE TABLE `bug_helper`
-> (
-> `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY
-> );
CREATE TABLE `cluster_bug`
(
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`bug_text` varchar(500) NOT NULL
);
DELIMITER ;;
CREATE TRIGGER `bug_helper_ad` AFTER DELETE ON `bug_helper` FOR EACH ROW
INSERT INTO cluster_bug (bug_text) VALUES ("AFTER DELETE TRIGGER WAS EXECUTED ON bug_helper, BUT WHAT WAS DELETED???");;
DELIMITER ;
DELETE FROM `bug_helper` WHERE id = 1;
SELECT * FROM `cluster_bug`;Query OK, 0 rows affected (0.84 sec)
mysql> CREATE TABLE `cluster_bug`
-> (
-> `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> `bug_text` varchar(500) NOT NULL
-> );
Query OK, 0 rows affected (0.93 sec)
mysql>
mysql> DELIMITER ;;
mysql>
mysql> CREATE TRIGGER `bug_helper_ad` AFTER DELETE ON `bug_helper` FOR EACH ROW
-> INSERT INTO cluster_bug (bug_text) VALUES ("AFTER DELETE TRIGGER WAS EXECUTED ON bug_helper, BUT WHAT WAS DELETED???");;
Query OK, 0 rows affected (0.16 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> DELETE FROM `bug_helper` WHERE id = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM `cluster_bug`;
+----+--------------------------------------------------------------------------+
| id | bug_text |
+----+--------------------------------------------------------------------------+
| 1 | AFTER DELETE TRIGGER WAS EXECUTED ON bug_helper, BUT WHAT WAS DELETED??? |
+----+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@cluster-repo mysql-7.3.7]# more docs/INFO_SRC
revision-id: mauritz.sundell@oracle.com-20141009130427-cwuq3yrz4eu63xn6
date: 2014-10-09 15:04:27 +0200
build-date: 2014-10-09 15:29:27 +0200
revno: 4444
branch-nick: mysql-cluster-7.3.7-release
MySQL source 5.6.21
[9 Nov 2014 7:26]
MySQL Verification Team
// Non-Cluster MySQL server
mysql> SHOW VARIABLES LIKE '%VERSION%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.21 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.21-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql> DROP TABLE IF EXISTS `bug_helper`;
Query OK, 0 rows affected (0.02 sec)
mysql> DROP TABLE IF EXISTS `cluster_bug`;
CREATE TABLE `bug_helper`
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE `bug_helper`
-> (
-> `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY
-> );
CREATE TABLE `cluster_bug`
(
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`bug_text` varchar(500) NOT NULL
);
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `cluster_bug`
-> (
-> `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> `bug_text` varchar(500) NOT NULL
-> );
DELIMITER ;;
CREATE TRIGGER `bug_helper_ad` AFTER DELETE ON `bug_helper` FOR EACH ROW
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> DELIMITER ;;
mysql>
mysql> CREATE TRIGGER `bug_helper_ad` AFTER DELETE ON `bug_helper` FOR EACH ROW
-> INSERT INTO cluster_bug (bug_text) VALUES ("AFTER DELETE TRIGGER WAS EXECUTED ON bug_helper, BUT WHAT WAS DELETED???");;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql> DELETE FROM `bug_helper` WHERE id = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM `cluster_bug`;
Empty set (0.00 sec)
[10 Dec 2014 17:47]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
Documented fix in the NDB 7.3.8 changelog as follows:
In some cases, when run against a table having an AFTER DELETE
trigger, a DELETE statement that matched no rows still caused
the trigger to execute.
Closed.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
[11 Dec 2014 6:18]
MySQL Verification Team
Bug #67972 marked as duplicate of this.
[11 Dec 2014 6:18]
MySQL Verification Team
Bug #67451 marked as duplicate of this.
Description: Trigger make work even if there wasn't deletion. How to repeat: DROP TABLE IF EXISTS `bug_helper`; DROP TABLE IF EXISTS `cluster_bug`; CREATE TABLE `bug_helper` ( `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY ); DELIMITER ;; CREATE TABLE `cluster_bug` ( `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `bug_text` varchar(500) NOT NULL ); DELIMITER ;; CREATE TRIGGER `bug_helper_ad` AFTER DELETE ON `bug_helper` FOR EACH ROW INSERT INTO cluster_bug (bug_text) VALUES ("AFTER DELETE TRIGGER WAS EXECUTED ON bug_helper, BUT WHAT WAS DELETED???");; DELIMITER ; DELETE FROM `bug_helper` WHERE id = 1; SELECT * FROM `cluster_bug`;