Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12302

Execute triggers for foreign key updates/deletes

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              jagermesh Sergiy Lavryk
              Votes:
              3 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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