[MDEV-12302] Execute triggers for foreign key updates/deletes - Jira

XML

Word

Printable

Details

Type: New Feature
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Fix Version/s: None
Component/s: Triggers
Labels:

Description

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

Attachments

Issue Links

blocks

MDEV-29181 Potential corruption on Foreign key update on a table with vcol index

Stalled

MDEV-30674 Implement CHECK constraints validation on cascading updates

Closed

is blocked by

MDEV-29181 Potential corruption on Foreign key update on a table with vcol index

Stalled

is duplicated by

MDEV-19402 Invoke triggers for foreign key cascade actions

Closed

relates to

MDEV-31942 Online alter: support cascade foreign keys

Open

MDEV-22880 Honor constraints on UPDATE CASCADE

Open

MDEV-31163 In some scenarios triggers limitations may lead to cascade data posioning.

Open

MDEV-31769 Adding a check constraint in "create table" doesn't work if it uses a foreign key with "update cascade"

Closed

links to

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

(3 relates to, 1 links to)

Activity

People

Assignee:: Nikita Malyavin

Reporter:: Sergiy Lavryk

Votes:: 4 Vote for this issue

Watchers:: 12 Start watching this issue

Dates

Created:: 2017-03-20 03:06

Updated:: 3 days ago 17:15

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.