The infamous 20 year old MySQL Bug #11472 has been fixed

MySQL Bugs: #11472: Triggers not executed following foreign key updates/deletes

Developer Zone

Bugs Home

Report a bug

Advanced search

Saved searches

Tags

Bug #11472 Triggers not executed following foreign key updates/deletes

Submitted: 21 Jun 2005 0:08 Modified: 17 Mar 10:14

Reporter:

Omer Barnir (OCA) Email Updates:

Status:

Closed 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

View

Add Comment

Files

Developer

Edit Submission

View Progress Log

Contributions

[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...

The infamous 20 year old MySQL Bug #11472 has been fixed

Related Articles

Amazon, Facebook, FBI have access to a private intelligence-sharing network

SpaceX not the behemoth everyone thought

The Mirror Is Part of the Machine

Elevated error rates on requests to multiple models

Donald Trump and sons to be 'forever' exempt from tax audits