|
copied from https://bugs.mysql.com/bug.php?id=11472
When rows of a table are updated/deleted indirectly as a result of a foregin key definition on the table, triggers on that table are not executed as required, as follows:
omer@linux:~/source/src50_0620/client> ./mysql --socket=/home/omer/source/src50_0620/mysql-test/var/tmp/master.sock --user=root
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 1 to server version: 5.0.8-beta-log
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
|
|
mysql> USE test;
|
Database changed
|
mysql>
|
mysql> DROP TABLE IF EXISTS t1,t2;
|
Query OK, 0 rows affected, 2 warnings (0.00 sec)
|
|
mysql>
|
mysql> CREATE TABLE t1 (id INT NOT NULL, col1 char(50), PRIMARY KEY (id)) ENGINE=INNODB;
|
Query OK, 0 rows affected (0.01 sec)
|
|
mysql> CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, INDEX par_ind (f_id), col1 char(50),
|
-> FOREIGN KEY (f_id) REFERENCES t1(id) ON DELETE SET NULL) ENGINE=INNODB;
|
Query OK, 0 rows affected (0.01 sec)
|
|
mysql>
|
mysql> create trigger tr_t2 after update on t2
|
-> for each row set @counter=@counter+1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql>
|
mysql> insert into t1 values (1,'Department A');
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> insert into t1 values (2,'Department B');
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> insert into t1 values (3,'Department C');
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> insert into t2 values (1,2,'Emp 1');
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> insert into t2 values (2,2,'Emp 2');
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> insert into t2 values (3,2,'Emp 3');
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> insert into t2 values (4,2,'Emp 4');
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> insert into t2 values (5,2,'Emp 5');
|
Query OK, 1 row affected (0.00 sec)
|
|
mysql> set @counter=0;
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> select * from t1;
|
+----+--------------+
|
| id | col1 |
|
+----+--------------+
|
| 1 | Department A |
|
| 2 | Department B |
|
| 3 | Department C |
|
+----+--------------+
|
3 rows in set (0.00 sec)
|
|
mysql> select * from t2;
|
+----+------+-------+
|
| id | f_id | col1 |
|
+----+------+-------+
|
| 1 | 2 | Emp 1 |
|
| 2 | 2 | Emp 2 |
|
| 3 | 2 | Emp 3 |
|
| 4 | 2 | Emp 4 |
|
| 5 | 2 | Emp 5 |
|
+----+------+-------+
|
5 rows in set (0.00 sec)
|
|
mysql> select @counter;
|
+----------+
|
| @counter |
|
+----------+
|
| 0 |
|
+----------+
|
1 row in set (0.00 sec)
|
|
mysql> delete from t1 where id=2;
|
Query OK, 1 row affected (0.05 sec)
|
|
mysql>
|
mysql> select * from t1;
|
+----+--------------+
|
| id | col1 |
|
+----+--------------+
|
| 1 | Department A |
|
| 3 | Department C |
|
+----+--------------+
|
2 rows in set (0.00 sec)
|
|
mysql> select * from t2;
|
+----+------+-------+
|
| id | f_id | col1 |
|
+----+------+-------+
|
| 1 | NULL | Emp 1 |
|
| 2 | NULL | Emp 2 |
|
| 3 | NULL | Emp 3 |
|
| 4 | NULL | Emp 4 |
|
| 5 | NULL | Emp 5 |
|
+----+------+-------+
|
5 rows in set (0.00 sec)
|
|
mysql> select @counter;
|
+----------+
|
| @counter |
|
+----------+
|
| 0 |
|
+----------+
|
1 row in set (0.00 sec)
|
Note At this point 5 rows were updated in table t2, the value of @count is expected to be '5' (each activation of the trigger increases it by 1, and yet the value remained zero, indicating the trigger was not executed.
The following shows that the trigger it self is executed when table 't2' is updated directly:
mysql> update t2 set col1='Emp 5a' where id=5;
|
Query OK, 1 row affected (0.00 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
|
mysql> select * from t2;
|
+----+------+--------+
|
| id | f_id | col1 |
|
+----+------+--------+
|
| 1 | NULL | Emp 1 |
|
| 2 | NULL | Emp 2 |
|
| 3 | NULL | Emp 3 |
|
| 4 | NULL | Emp 4 |
|
| 5 | NULL | Emp 5a |
|
+----+------+--------+
|
5 rows in set (0.00 sec)
|
|
mysql> select @counter;
|
+----------+
|
| @counter |
|
+----------+
|
| 1 |
|
+----------+
|
1 row in set (0.00 sec)
|
|
***** In this case the trigger wasexecuted (@count set to '1')
|
|
mysql>
|
mysql> drop table t2,t1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> quit
|
Bye
|
omer@linux:~/source/src50_0620/client>
|
|
How to repeat:
|
Run the following in the mysql client:
|
|
USE test;
|
|
DROP TABLE IF EXISTS t1,t2;
|
|
CREATE TABLE t1 (id INT NOT NULL, col1 char(50), PRIMARY KEY (id)) ENGINE=INNODB;
|
CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, INDEX par_ind (f_id), col1 char(50),
|
FOREIGN KEY (f_id) REFERENCES t1(id) ON DELETE SET NULL) ENGINE=INNODB;
|
|
create trigger tr_t2 after update on t2
|
for each row set @counter=@counter+1;
|
|
insert into t1 values (1,'Department A');
|
insert into t1 values (2,'Department B');
|
insert into t1 values (3,'Department C');
|
insert into t2 values (1,2,'Emp 1');
|
insert into t2 values (2,2,'Emp 2');
|
insert into t2 values (3,2,'Emp 3');
|
insert into t2 values (4,2,'Emp 4');
|
insert into t2 values (5,2,'Emp 5');
|
set @counter=0;
|
|
select * from t1;
|
select * from t2;
|
select @counter;
|
|
delete from t1 where id=2;
|
|
select * from t1;
|
select * from t2;
|
select @counter;
|
update t2 set col1='Emp 5a' where id=5;
|
select * from t2;
|
select @counter;
|
|
drop table t2,t1;
|
Suggested fix:
Have triggers executed in the above scenario
|