| Bug #11472 | Triggers not executed following foreign key updates/deletes | ||
|---|---|---|---|
| Submitted: | 21 Jun 2005 2:08 | Modified: | 17 Oct 2008 0:03 |
| Reporter: | Omer BarNir | ||
| Status: | Verified | ||
| Category: | Server: DML | Severity: | S2 (Serious) |
| Version: | 5.0.8 | OS: | Any |
| Assigned to: | Dmitri Lenev | Target Version: | |
| Triage: | Triaged: D2 (Serious) | ||
[21 Jun 2005 9:30]
Heikki Tuuri
Omer, this is a known problem. Dmitri, PEM, or someone else has to give us a mechanism to fire the trigger if a cascading foreign key clause causes a row to be changed. I will document this shortcoming until this is fixed. Regards, Heikki
[22 Jun 2005 17:10]
Heikki Tuuri
I have now documented that currently TRIGGERs are not activated by cascading FOREIGN KEY operations. We should fix in 5.1, at the latest. Regards, Heikki
[22 Jun 2005 18:25]
Heikki Tuuri
Lowering priority to P3 now that this shortcoming is noted in the manual. --Heikki
[30 Jun 2005 21:04]
Dmitri Lenev
We will fix this in 5.1
[1 Dec 2006 11:06]
Dmitri Lenev
Bug #13102 was marked as duplicate of this bug. Please note the comment about emitting warning in cases when one tries to create foreign key with cascading action for table with triggers.
[21 Feb 2007 13:56]
Heikki Tuuri
MySQL's own FOREIGN KEY implementation will eventually fix this, but it will still take some time.
[4 Jun 2007 22:00]
Christopher Lee
In addition, it should generate a warning when an ON DELETE trigger is placed on a table with a foreign key which could cause a cascading delete. The order in which the foreign key and trigger are added should not affect whether or not a warning is shown. This issue is still present as of version 5.1.11.
[30 Sep 2008 12:49]
Konstantin Osipov
WL#148
[28 May 2009 4:44]
Mulyadi Pasaribu
IT has been fixed? http://itmagazines.info
[30 Jun 2009 16:55]
Andrew Holland
It's 2009 and the bug is still present as of MySQL 5.1.34 and still causing problems.
[2 Jul 2009 17:42]
Konstantin Osipov
This bug will not be fixed in 5.1. You may want to try lp:6.1-fk tree, where it is fixed. But this tree is still in alpha. See also http://forge.mysql.com/worklog/task.php?id=148
[5 Jan 10:43]
tomas pokorny
Additionally, when having a cascade foreign-key in table `table_b` (table_b.a = table_a.a) and a delete trigger in table `table_a`, only the trigger fires - the foreign-key has no effect at all
Description: 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