Bug #11472 Triggers not executed following foreign key updates/deletes
Submitted: 21 Jun 2005 0:08 Modified: 16 Oct 2008 22:03
Reporter: Omer Barnir (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.0.8/5.5/5.6/5.7 OS:Any
Assigned to:
Triage: Triaged: D2 (Serious)

[21 Jun 2005 0:08] Omer Barnir
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
[21 Jun 2005 7: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 15: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 16:25] Heikki Tuuri
Lowering priority to P3 now that this shortcoming is noted in the manual.
--Heikki
[30 Jun 2005 19:04] Dmitry Lenev
We will fix this in 5.1
[1 Dec 2006 10:06] Dmitry 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 12:56] Heikki Tuuri
MySQL's own FOREIGN KEY implementation will eventually fix this, but it will still take some time.
[4 Jun 2007 20: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 10:49] Konstantin Osipov
WL#148
[30 Jun 2009 14: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 15: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 2010 9: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
[9 Nov 2010 10:54] poorani mu
even i am facing this problem with foreign keys... is it solved or yet to be solved... otherwise any other way to maintain foreign key integrity?other wise i have to suggest a different databse to my organisation.
[13 Mar 2011 14:37] Hugues Gauthier
5 years after, and this bug isn't fixed.

can you tell us why ? and what is the targeted version ?
[17 Jun 2011 22:39] Miguel Solorzano
See http://bugs.mysql.com/bug.php?id=61555.
[29 Jun 2011 16:24] Matteo Melli
The "ON DELETE NULL" constraint dose not update columns where "ON UPDATE CURRENT_TIMESTAMP" has been specified.

This is a real problem when you come in need to integrate with some external index like Solr that uses TIMESTAMP columns like that to generate delta updates.
[20 Aug 2011 11:32] Morg. What?
This implies that MySQL InnoDB HAS NOT BEEN ACID COMPLIANT FOR THE LAST 5 YEARS.

Anyone official care to comment on that ?
[9 May 2012 1:18] Anthony Galano
Any update on this? 7 years and this hasn't been fixed?
A warning in the manual is NOT a fix.
[21 Jun 2012 2:44] Abraham Guyt
Please, could anyone from MySQL tell us whether there is a plan/intention to fix this bug? This is threatening ACID/integrity big time ....
[25 Sep 2012 15:00] Ryan Brothers
I am running into this problem too.  Is there any idea of when it will be fixed?
[26 Sep 2012 7:49] Tom Bradley
More than 7 Years and counting... its a joke! I've now moved over to PostgreSQL, got cascade delete triggers working and its a little faster too ;)
[24 Jan 2013 20:23] Jon Taylor
Is there any plan to fix this or is this how it's going to be?  I haven't looked at this in 4 years and thought it would have been fixed by now also...
[8 May 2013 12:29] Alex Prokop
Coming up to 8 years now... Any movement?
[8 May 2013 13:25] Tom Bradley
The guys over at Oracle probably aren't getting any of these messages because their triggers aren't firing ;)
[8 May 2013 13:34] Hugues Gauthier
Lol !!
[8 May 2013 13:52] Ståle Deraas
Note that there is major development work needed to fix this issue. It will not be a mere bugfix.
[8 May 2013 13:58] Tom Bradley
I agree... it's not going to be a simple fix, so please take your time
[8 May 2013 14:01] Hugues Gauthier
Lol !
[19 Sep 2013 19:16] Miguel Solorzano
http://bugs.mysql.com/bug.php?id=70380 marked as duplicate of this one.
[19 Sep 2013 19:24] Wayne Chan
It's been 8 years since this issue has been reported; obviously many people are affected by it; and no, there is no good workaround. When can this bug be fixed?
[5 Apr 2014 7:16] Ananda Theerthan
Is this fixed? I am still having this issue.
[28 Feb 2015 15:30] Petr Levtonov
Soon 10 years after initial bug submission and that problem still exists. Over the years I had to suggest several times to use some other rdbms like (postgres) that supports basic things like that. That is very disappointing...
[20 Apr 2015 15:02] Ariel Schiera
We just started suffering this issue after trying to implement triggers on cascade delete. Please a fix would be really appreciated. Looks like the bug is 10 years old now. Thanks.
[21 Jun 2015 8:38] Jarosław Strzelecki
Happy anniversary! 
It's 10 years already...
[24 Jun 2015 21:42] v f
This defect is going to be attending middle school in the fall. She's a little nervous and angry at us because most of her friends are going to Valley MS instead of Lakeview.
[28 Jun 2016 17:54] Kostadin Taneski
MySQL v.5.7.8 is also affected from this issue. Was this bug ever resolved? Cascading actions (update/delete/set null) are not firing the defined triggers.
[6 Aug 2016 15:27] Fernando Lopes
This defect also happens on Windows 10 x64 with 5.6.17. Pretty annoying!
[21 Dec 2016 17:32] Daniel Cohen Gindi
This is one of the most restrictive bugs regarding Triggers, and in mysql overall.

For MySql 5.7 I expected it to be fixed, but no...
And now there's 8.0 in beta. 

Is anyone going to take this seriously???
[21 Dec 2016 17:35] Daniel Cohen Gindi
It's funny - the bug is referenced in the source code, as something that prevents certain unit tests from running. So the development team is clearly aware of this bug.
[10 Jan 18:47] FIlip Bekic
This bug is just annoying, can you please fix this already?
[28 Apr 19:13] R Chinoy
Nearly 12 years and this hasn't been fixed. Please add another vote for getting it done as it's been a major point of pain for a LONG time.
[10 Jul 12:18] Sameera Viraj Amarasinghe
Please don't fix this. Thankz...