List:Commits« Previous MessageNext Message »
From:Kristofer Pettersson Date:March 27 2009 4:08pm
Subject:bzr commit into mysql-5.1-bugteam branch (kristofer.pettersson:2842)
Bug#40127
View as plain text  
#At file:///home/thek/Development/cpp/mysqlbzr/51-bug40127/ based on revid:gni@stripped

 2842 Kristofer Pettersson	2009-03-27
      Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation
                on 5.0            
      The server crashes on an assert in net_end_statement indicating that the
      Diagnostics area wasn't set properly during execution.
      This happened on a multi table DELETE operation using the IGNORE keyword.
      The keyword is suppose to allow for execution to continue on a best effort
      despite some non-fatal errors. Instead execution stopped and no client
      response was sent which would have led to a protocol error if it hadn't been
      for the assert.
      This patch corrects this issue by checking for the existence of an IGNORE
      option before setting an error state during row-by-row delete iteration.
     @ mysql-test/r/innodb_mysql.result
        * Added test case for bug40127
     @ mysql-test/t/innodb_mysql.test
        * Added test case for bug40127
     @ sql/sql_delete.cc
        * IGNORE option wasn't implemented in multi_delete::send_data
          and multi_delete::do_deletes

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
      sql/sql_delete.cc
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2009-02-20 09:50:50 +0000
+++ b/mysql-test/r/innodb_mysql.result	2009-03-27 16:08:14 +0000
@@ -1846,4 +1846,149 @@ id
 TRUNCATE TABLE t2;
 DROP TABLE t2;
 DROP TABLE t1;
+#
+# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
+#
+CREATE TABLE t1 (
+id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (id)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+aid INT UNSIGNED NOT NULL,
+PRIMARY KEY (id),
+FOREIGN KEY (aid) REFERENCES t1 (id)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+bid INT UNSIGNED NOT NULL,
+FOREIGN KEY (bid) REFERENCES t2 (id)
+) ENGINE=InnoDB;
+CREATE TABLE t4 (
+a INT
+) ENGINE=InnoDB;
+CREATE TABLE t5 (
+a INT
+) ENGINE=InnoDB;
+INSERT INTO t1 (id) VALUES (1);
+INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1);
+INSERT INTO t3 (bid) VALUES (1);
+INSERT INTO t4 VALUES (1),(2),(3),(4),(5);
+INSERT INTO t5 VALUES (1);
+DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a;
+DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`))
+DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`))
+DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP TABLES t4,t5;
+# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
+# Testing for any side effects of IGNORE on AFTER DELETE triggers used with
+# transactional tables.
+#
+CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB;
+CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT, 
+FOREIGN KEY (t1i) REFERENCES t1(i))
+ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+SET @b:='EXECUTED TRIGGER';
+INSERT INTO t2 VALUES (@b);
+SET @a:= error_happens_here;
+END||
+SET @b:="";
+SET @a:="";
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 SELECT * FROM t1;
+** An error in a trigger causes rollback of the statement.
+DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+ERROR 42S22: Unknown column 'error_happens_here' in 'field list'
+SELECT @a,@b;
+@a	@b
+	EXECUTED TRIGGER
+SELECT * FROM t2;
+a
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+i	i
+1	1
+2	2
+3	3
+4	4
+** Same happens with the IGNORE option
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+ERROR 42S22: Unknown column 'error_happens_here' in 'field list'
+SELECT * FROM t2;
+a
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+i	i
+1	1
+2	2
+3	3
+4	4
+**
+** The following is an attempt to demonstrate
+** error handling inside a row iteration.
+**
+DROP TRIGGER trg;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+TRUNCATE TABLE t3;
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+INSERT INTO t4 VALUES (3,3),(4,4);
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR));
+INSERT INTO t2 VALUES (@b);
+END||
+** DELETE is prevented by foreign key constrains but errors are silenced.
+** The AFTER trigger isn't fired.
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+** Tables are modified by best effort:
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+i	i
+3	3
+4	4
+** The AFTER trigger was only executed on successful rows:
+SELECT * FROM t2;
+a
+EXECUTED TRIGGER FOR ROW 1
+EXECUTED TRIGGER FOR ROW 2
+DROP TRIGGER trg;
+**
+** Induce an error midway through an AFTER-trigger
+**
+TRUNCATE TABLE t4;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t3;
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+SET @a:= @a+1;
+IF @a > 2 THEN
+INSERT INTO t4 VALUES (5,5);
+END IF;
+END||
+SET @a:=0;
+** Errors in the trigger causes the statement to abort.
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`t1i`) REFERENCES `t1` (`i`))
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+i	i
+1	1
+2	2
+3	3
+4	4
+SELECT * FROM t4;
+i	t1i
+DROP TRIGGER trg;
+DROP TABLE t4;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
 End of 5.1 tests

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2009-02-20 09:50:50 +0000
+++ b/mysql-test/t/innodb_mysql.test	2009-03-27 16:08:14 +0000
@@ -185,4 +185,151 @@ TRUNCATE TABLE t2;
 DROP TABLE t2;
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
+--echo #
+CREATE TABLE t1 (
+        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+        PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+        aid INT UNSIGNED NOT NULL,
+        PRIMARY KEY (id),
+        FOREIGN KEY (aid) REFERENCES t1 (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE t3 (
+        bid INT UNSIGNED NOT NULL,
+        FOREIGN KEY (bid) REFERENCES t2 (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE t4 (
+  a INT
+) ENGINE=InnoDB;
+
+CREATE TABLE t5 (
+  a INT
+) ENGINE=InnoDB;
+
+INSERT INTO t1 (id) VALUES (1);
+INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1);
+INSERT INTO t3 (bid) VALUES (1);
+
+INSERT INTO t4 VALUES (1),(2),(3),(4),(5);
+INSERT INTO t5 VALUES (1);
+
+DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a;
+
+--error ER_ROW_IS_REFERENCED_2
+DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+--error ER_ROW_IS_REFERENCED_2
+DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+
+DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP TABLES t4,t5;
+
+--echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
+--echo # Testing for any side effects of IGNORE on AFTER DELETE triggers used with
+--echo # transactional tables.
+--echo #
+CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB;
+CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT, 
+  FOREIGN KEY (t1i) REFERENCES t1(i))
+  ENGINE=InnoDB;
+delimiter ||;
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  SET @b:='EXECUTED TRIGGER';
+  INSERT INTO t2 VALUES (@b);
+  SET @a:= error_happens_here;
+END||
+delimiter ;||
+
+SET @b:="";
+SET @a:="";
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 SELECT * FROM t1;
+--echo ** An error in a trigger causes rollback of the statement.
+--error ER_BAD_FIELD_ERROR
+DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+SELECT @a,@b;
+SELECT * FROM t2;
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+
+--echo ** Same happens with the IGNORE option
+--error ER_BAD_FIELD_ERROR
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+SELECT * FROM t2;
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+
+--echo **
+--echo ** The following is an attempt to demonstrate
+--echo ** error handling inside a row iteration.
+--echo **
+DROP TRIGGER trg;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+TRUNCATE TABLE t3;
+
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+INSERT INTO t4 VALUES (3,3),(4,4);
+
+delimiter ||;
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR));
+  INSERT INTO t2 VALUES (@b);
+END||
+delimiter ;||
+
+--echo ** DELETE is prevented by foreign key constrains but errors are silenced.
+--echo ** The AFTER trigger isn't fired.
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+--echo ** Tables are modified by best effort:
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+--echo ** The AFTER trigger was only executed on successful rows:
+SELECT * FROM t2;
+
+DROP TRIGGER trg;
+
+--echo **
+--echo ** Induce an error midway through an AFTER-trigger
+--echo **
+TRUNCATE TABLE t4;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t3;
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+delimiter ||;
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+  SET @a:= @a+1;
+  IF @a > 2 THEN
+    INSERT INTO t4 VALUES (5,5);
+  END IF;
+END||
+delimiter ;||
+
+SET @a:=0;
+--echo ** Errors in the trigger causes the statement to abort.
+--error ER_NO_REFERENCED_ROW_2
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+SELECT * FROM t4;
+
+DROP TRIGGER trg;
+DROP TABLE t4;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2009-02-09 22:51:59 +0000
+++ b/sql/sql_delete.cc	2009-03-27 16:08:14 +0000
@@ -709,6 +709,8 @@ bool multi_delete::send_data(List<Item> 
   TABLE_LIST *del_table;
   DBUG_ENTER("multi_delete::send_data");
 
+  bool ignore= thd->lex->current_select->no_error;
+
   for (del_table= delete_tables;
        del_table;
        del_table= del_table->next_local, secure_counter++)
@@ -741,8 +743,12 @@ bool multi_delete::send_data(List<Item> 
                                               TRG_ACTION_AFTER, FALSE))
           DBUG_RETURN(1);
       }
-      else
+      else if (!ignore)
       {
+        /*
+          If the IGNORE option is used errors caused by ha_delete_row don't
+          have to stop the iteration.
+        */
         table->file->print_error(error,MYF(0));
         DBUG_RETURN(1);
       }
@@ -834,6 +840,11 @@ int multi_delete::do_deletes()
 {
   int local_error= 0, counter= 0, tmp_error;
   bool will_batch;
+  /*
+    If the IGNORE option is used all non fatal errors will be translated
+    to warnings and we should not break the row-by-row iteration
+  */
+  bool ignore= thd->lex->current_select->no_error;
   DBUG_ENTER("do_deletes");
   DBUG_ASSERT(do_delete);
 
@@ -872,18 +883,29 @@ int multi_delete::do_deletes()
         local_error= 1;
         break;
       }
-      if ((local_error=table->file->ha_delete_row(table->record[0])))
+
+      local_error= table->file->ha_delete_row(table->record[0]);
+      if (local_error && !ignore)
       {
-	table->file->print_error(local_error,MYF(0));
-	break;
+        table->file->print_error(local_error,MYF(0));
+        break;
       }
-      deleted++;
-      if (table->triggers &&
-          table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
-                                            TRG_ACTION_AFTER, FALSE))
+
+      /*
+        Increase the reported number of deleted rows only if no error occurred
+        during ha_delete_row.
+        Also, don't execute the AFTER trigger if the row operation failed.
+      */
+      if (!local_error)
       {
-        local_error= 1;
-        break;
+        deleted++;
+        if (table->triggers &&
+            table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
+                                              TRG_ACTION_AFTER, FALSE))
+        {
+          local_error= 1;
+          break;
+        }
       }
     }
     if (will_batch && (tmp_error= table->file->end_bulk_delete()))


Attachment: [text/bzr-bundle] bzr/kristofer.pettersson@sun.com-20090327160814-orn2jj66rx0gpgci.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (kristofer.pettersson:2842)Bug#40127Kristofer Pettersson27 Mar
  • Re: bzr commit into mysql-5.1-bugteam branch(kristofer.pettersson:2842) Bug#40127Davi Arnaut28 Mar