MySQL Bugs: #43520: Foreign keys: ignoring triggered action which precedes constraint check

Bug #43520	Foreign keys: ignoring triggered action which precedes constraint check
Submitted:	9 Mar 2009 23:35	Modified:	27 Mar 2009 19:23
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	6.1.0-alpha-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk-stage.
I start mysqld with --foreign-key-all-engines=1.

I create two Falcon tables, a parent and a child referencing the parent.
I create an UPDATE trigger on the child which will insert '4'  in the parent.
I update the child in a way that the last child row will become '4'.
I get an integrity-violation error.

I believe there are two acceptable actions. Quoting WL#148 specification:
(1) "Error if: all data-change operations are illegal if
    there is a trigger that changes one of the tables in
    table_list, or a subquery that changes one of the tables
    in table_list. ... ER_FK_CHANGE_BY_TRIGGER"
(2) "If a table has both triggers and foreign keys, or
    if a table has multiple foreign keys, the order
    of processing may not be the same as it is now for
    InnoDB tables. For SQL:2003 requirement details see 
    (email reference)."

But neither of these things happens. We get integrity-violation instead.

How to repeat:
How to repeat:

SET @@storage_engine=falcon;
DROP TABLE IF EXISTS t2,t1;
CREATE TABLE t1 (s1 INT NOT NULL, unique key (s1) using hash);
CREATE TABLE t2 (s1 INT, key(s1) using hash,  FOREIGN KEY (s1) REFERENCES t1 (s1));
DELIMITER //
CREATE TRIGGER t2_au AFTER UPDATE ON t2 FOR EACH ROW
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
  INSERT INTO t1 VALUES (4);
  END//
DELIMITER ;
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (1),(2),(3);
UPDATE t2 SET s1 = s1 + 1 ORDER BY s1;
DROP TABLE IF EXISTS t2,t1;

Sample run:

mysql> SET @@storage_engine=falcon;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t2,t1;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t1 (s1 INT NOT NULL, unique key (s1) using hash);
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t2 (s1 INT, key(s1) using hash,  FOREIGN KEY (s1) REFERENCES t1 (s1));
Query OK, 0 rows affected (0.19 sec)

mysql> DELIMITER //
mysql> CREATE TRIGGER t2_au AFTER UPDATE ON t2 FOR EACH ROW
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
    ->   INSERT INTO t1 VALUES (4);
    ->   END//
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER ;
mysql> INSERT INTO t1 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE t2 SET s1 = s1 + 1 ORDER BY s1;
ERROR 1790 (23000): Foreign key error: constraint 'fk_t2_ujlz3': no matching key for value '4', it is not in parent table
mysql> DROP TABLE IF EXISTS t2,t1;
Query OK, 0 rows affected (0.01 sec)

Thank you for the bug report. Verified as described:

c:\dbs\6.1>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 6.1.0-alpha-Win x64 revno 2705 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET @@storage_engine=falcon;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t2,t1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> CREATE TABLE t1 (s1 INT NOT NULL, unique key (s1) using hash);
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE TABLE t2 (s1 INT, key(s1) using hash,  FOREIGN KEY (s1) REFERENCES t1 (s1));
Query OK, 0 rows affected (0.42 sec)

mysql> DELIMITER //
mysql> CREATE TRIGGER t2_au AFTER UPDATE ON t2 FOR EACH ROW
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
    ->   INSERT INTO t1 VALUES (4);
    ->   END//
Query OK, 0 rows affected (0.22 sec)

mysql> DELIMITER ;
mysql> INSERT INTO t1 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (1),(2),(3);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE t2 SET s1 = s1 + 1 ORDER BY s1;
ERROR 1790 (23000): Foreign key error: constraint 'fk_t2_mzmon': no matching key for value '4', it is not in parent table
mysql> DROP TABLE IF EXISTS t2,t1;
Query OK, 0 rows affected (0.19 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70744

2712 Dmitry Lenev	2009-03-27
      Fix for bug #43520 "Foreign keys: ignoring triggered action which
      precedes constraint check".
      
      Because we were reporting foreign key violation before executing
      AFTER trigger for a row (and even before executing BEFORE triggers 
      for other rows) users were able to see that we perform foreign key
      validation in row-by-row fashion and not at the end of statement 
      as prescribed by spec. 
      
      This fix solves the problem by enabling usage of EOS buffer for 
      foreign  keys checks caused by operations which also fire triggers
      (or when triggers are fired by operations which caused this
      operation).
     @ mysql-test/r/foreign_key_all_engines.result
        Added test for bug #43520 "Foreign keys: ignoring triggered action which
        precedes constraint check".
     @ mysql-test/t/foreign_key_all_engines.test
        Added test for bug #43520 "Foreign keys: ignoring triggered action which
        precedes constraint check".
     @ sql/fk.cc
        To preserve illusion that foreign key validation happens at the end of 
        statement, after execution of triggers we have to turn on EOS checking 
        if there are some triggers for the operation which caused this foreign 
        key check (or one of operations which caused this operation).
     @ sql/fk.h
        Added Fk_constraint_list member to store pointer to TABLE instance on 
        which operation which triggered foreign keys is performed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70747

2713 Dmitry Lenev	2009-03-27
      Fix for bug #43520 "Foreign keys: ignoring triggered action which
      precedes constraint check".
      
      Because we were reporting foreign key violation before executing
      AFTER trigger for a row (and even before executing BEFORE triggers
      for other rows) users were able to see that we perform foreign key
      validation in row-by-row fashion and not at the end of statement
      as prescribed by spec.
      
      This fix solves the problem by enabling usage of EOS buffer for
      foreign  keys checks caused by operations which also fire triggers
      (or when triggers are fired by operations which caused this
      operation).
     @ mysql-test/r/foreign_key_all_engines.result
        Added test for bug #43520 "Foreign keys: ignoring triggered action which
        precedes constraint check".
     @ mysql-test/t/foreign_key_all_engines.test
        Added test for bug #43520 "Foreign keys: ignoring triggered action which
        precedes constraint check".
     @ sql/fk.cc
        To preserve illusion that foreign key validation happens at the end of
        statement, after execution of triggers we have to turn on EOS checking
        if there are some triggers for the operation which caused this foreign
        key check (or one of operations which caused this operation).
     @ sql/fk.h
        Added Fk_constraint_list member to store pointer to TABLE instance on
        which operation which triggered foreign keys is performed.

Fix for this bug was pushed into mysql-6.1-fk tree. Since this bug was reported against tree which is not publicly available yet I am simply closing this bug.