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: CPU Architecture:Any

[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] MySQL Verification Team
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] MySQL Verification Team
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 2017 18:47] FIlip Bekic
This bug is just annoying, can you please fix this already?
[28 Apr 2017 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 2017 12:18] Sameera Viraj Amarasinghe
Please don't fix this. Thankz...
[23 Sep 2017 21:58] Romain Loth
lol 2013-08-08: "The guys over at Oracle probably aren't getting any of these messages because their triggers aren't firing ;)"

Imagine you're programming a digital clock. When a minute passes, it triggers a reset of the seconds count. When an hour passes, you do this for the minutes and seconds separately or do you prefer to trigger one action with logical consequences that will trickle down other trigger. The latter would seem more elegant, but can't be implemented in InnoDB because of this bug.

2017-09-22: 12 years 3 months...
[25 Oct 2017 11:14] Mauro Molin
Will this bug ever be fixed?
[30 Jan 2018 14:22] Maciej Partyka
2018 still going strong!!!
[21 Jun 2018 18:02] Ash Meu
Happy 13th birthday, 11472! They grow up so fast :')
[22 Jun 2018 4:23] Jishnu Viswanath
Can we apply for the world record for the longest standing bug on opensource projects?
[10 Oct 2018 8:53] amal wick
shall we celebrate 14th birthday together friends
#feeling happy with oracle :D  

මේක හදලා දාපියවූ යකුනේ.......
[21 Jan 2019 10:30] Yusup Bugacheeev
Hello from 2019. Still have this bug
[21 Jan 2019 10:31] Yusup Bugacheeev
.
[30 Apr 2019 13:08] Daniel Lindholm
PLEASE fix this!! Just fell over this bug today. I can't beleive that the DELETE trigger can't be trusted to execute in all cases.
[21 Jun 2019 13:07] Adam Spofford
Happy 14th birthday! 🎂
[21 Jun 2019 14:56] Henry Ayala
Soon this bug is going to be old enough to take driving lessons.
[9 Nov 2019 7:17] Owen Gallagher
This bug is still bugging me after over 14 years and 4 months.
[11 Nov 2019 9:12] Fabio Napodano
would be nice to know how is life of the bug report opener. Is he still alive? Is he actually still using MySQL?
[12 Nov 2019 14:29] Omer Barnir
Thanks for asking.
I'm alive and well, and using MySQL.
[11 Jun 2020 21:36] Jacob Bingham
This bug is older than me
[11 Jun 2020 23:17] Hugues Gauthier
Lol ! 🤣
My first reply on this bug was in 2011,
Guess my age !
[14 Jul 2020 18:43] Awk Dev
Don't mind me, just trying to be in the screenshot.
[14 Jul 2020 18:52] John Stef
I started using MySQL a month ago for a project of mine. Today I discovered this bug, now I feel sorry for my self.
[15 Jul 2020 8:41] Giga Chad
I was in kindergarten when this bug was reported. Now I'm in 2nd year of my bachelor's degree in computer science.
[15 Jul 2020 9:29] Fermin Cortell Ferri
Can I join you?
[15 Jul 2020 12:28] Jay Godara
Guys my girlfriend says that she will marry me once this bug is resolved. Do we have any update on this?

P.S: We've been waiting since 2017 and she's now consider to Gary.

P.S 2: Gary you're a prick!
[20 Jul 2020 7:35] Daniël van Eeden
Hi, I do agree with all of you that this is an important bug.

However there are few things I would like you to consider:

1. What would a fix look like?

If this bug would be fixed in the next minor version update then
triggers that were previously not being executed would suddenly be used. While it would be a very bad practice to rely on this, people may have got into this situation by accident. If people are using statement based replication (also not advised: row based has many advantages) then these triggers may only be executed on replica's and not on the source (with different minor versions) causing data to drift.
This *could* be fixed by adding a new setting which allows you to switch between the current behaviour or the new behaviour.
Another "solution" would be to add a flag for triggers that sets this behaviour, but that probably deviates from the ISO SQL standards. And then you would need to change all your triggers.
Removing and/or restricting the use of statement based replication fixes some of this but not all of it.

So what should a fix for this bug look like? A simple fix which then causes issues during upgrades? A complex fix that requires you to modify all triggers after upgrading? Should it be fixed in 8.0 or in 8.1 or 9.0?

2. MySQL is open source

There is nothing stopping you from creating a patch to fix this and submitting it to Oracle for inclusion. You could do this yourself or in cooperation with other people. You could also pay a third party company to do this for you. Just make sure the copyright on the patch is clear and in line with the Oracle Contribution Agreement.

This can be a multi stage process:
* Find out where/how triggers are executed
* Find out where/how foreign keys are processed.
* Try to make the foreign key code call out to the code that deals with triggers.
* Testing, Testing, Testing

3. MySQL is a commercial product

If you are a "MySQL Enterprise Edition" customer, then you should talk to your sales person and/or account manager to let them know this bug is important to you.

P.S. I'm not working for Oracle, just so you know.
[12 Aug 2020 11:22] Dhilip R
I am happy to help. Any volunteers?.
ഇവിടെ ഏതെങ്കിലും മലയാളിയുണ്ടോ?
[22 Dec 2020 5:44] Prasad Ajayakumar
Lol. 15 years and still open bug :)

മലയാളി ഉണ്ട് ദിലീപ് സാർ . മലയാളി ഉണ്ട്.
[18 Jan 2021 0:49] Alex D
Was just checking to see if our favourite bug made it through the covid-19 pandemic.  Glad to see it's doing well.
[8 Jan 2022 20:34] Ayaka K.
Hello from 2022, this was reported when I was 3 and still here xD.
[8 Jan 2022 20:35] Ayaka K.
Hello from 2022, this was reported when I was 3 and still here xD.
[21 Jun 2022 4:39] Vladimir Pakhomchik
As you turn 17-years-old today, I want to wish you luck, happiness, and success for your adult years that are about to come your way!
[11 Oct 2022 22:52] Antonio Goncalves
Well its 2022, this bug was reported when I was 2 years old and this is still here? No one is going to address this? This is a major issue in my setup where I have triggers that archive stuff and because of this bug CASCADING is not activating triggers, and the work arounds are not ideal too.
[11 Oct 2022 22:52] Antonio Goncalves
Well its 2022, this bug was reported when I was 2 years old and this is still here? No one is going to address this? This is a major issue in my setup where I have triggers that archive stuff and because of this bug CASCADING is not activating triggers, and the work arounds are not ideal too.
[7 Feb 2023 19:01] Daniel Armstrong
Maybe we should do something special for this bug's 18th birthday?

We, however, moved away from MySQL years ago - this bug was the last straw.  We needed data integrity for a custom account platform.

To whom it may be concerned: you're losing users over this bug.
[7 Feb 2023 20:04] Hugues Gauthier
Does anyone know if this bug also occurs in version 8 of mysql?
[22 Mar 2023 13:39] Nicolas Vazquez
spoiler: this will never be fixed
[22 Mar 2023 19:52] Perry Harrington
The way that bugs get fixed at Oracle is for a paying customer to request it.  Then the bug gets added to an open BPS window.  The sustaining team is then tasks with fixing the bug or involving the dev group proper.

This bug will probably involve weeks of discussion before a solution is decided on.

Daniel (sorry no i18n keyboard) is correct about sweeping changes like this.  MySQL 8 is GA but in a state of perpetual change, so this bug could be fixed there.  There would probably be an argument against changing the behavior in 5.7, since the fix would certainly introduce a new SQL mode or a backwards compatibility flag like "avoid_temporal_upgrade".  Making it an SQL mode makes it more visible and less hacky.

So, if you are an Oracle support customer then open a support ticket referencing this community bug.  I'm certain it's been imported into the Oracle bug repository by now; they can add the internal bug ID to the next available BPS window and get actual work moving forward.

It's also possible it'll get punted to a WL (very likely), in that case the bug will most likely never be mentioned in the Release notes, since WLs that fix bugs almost never mark the bug as fixed or mention it in the notes.

The wheels only move if lubricated properly and the correct sequence of levers thrown.
[1 Jun 2023 16:18] rajneesh ojha
wow never seen a bug that old ,i wonder what could be th real reason they are not intrested in this fix?
[21 Jun 2023 6:20] Vladimir Pakhomchik
Dear MySQL Bug,

Happy 18th birthday to you, dear bug!
It's remarkable how you've managed to persist,
For eighteen years, you've caused quite a fuss,
Yet remained a part of our programming list.

Through countless queries and intricate codes,
You've challenged developers far and wide,
But your quirks and flaws, they never erode,
The admiration we hold deep inside.

Your existence has shaped the database world,
Unveiling the mysteries of data at large,
From humble beginnings, you steadily unfurled,
A bug that has become a legendary charge.

So here's to you, MySQL Bug, on this special day,
May you continue to baffle and perplex,
We celebrate your persistence, come what may,
And cherish the lessons you continue to teach.

Happy 18th birthday, dear MySQL Bug!
May your impact endure and your legend live on,
Cheers to the moments you've made us shrug,
And the knowledge you've bestowed upon us, dawn to dawn.

With gratitude,
The developers and users
[25 Mar 4:00] Robert Rothkopf
I've only been working with MySQL for 1.5 years now and just came across this bug... couldn't believe that my auditing trigger didn't fire when a Cascaded update reset a value to NULL.

The whole point of RI rules is to maintain data integrity, and triggers are often there to make sure the business logic is enforced and audits are logged.  

For the record:
- Not fixed as of version 8.0.36
- It's been 6,851 days since reported... that's 18 years, 9 months and 3 days (excluding the end date)

*sigh*
[9 Apr 10:03] Lukas Pirch
*I was here*

I've just created an oracle account to mention that the existence of this bug jeopardizes my whole research in the field of software defect detection. What's the point of detecting bugs if nobody fixes them?

On the bright side, this leaves some interesting future work on automatic program repair. In any case, stay safe and bug-free y'all!