From: Date: March 26 2009 10:20am
Subject: bzr commit into mysql-5.1-bugteam branch (kristofer.pettersson:2842)
 Bug#40127
List-Archive: http://lists.mysql.com/commits/70491
X-Bug: 40127
Message-Id: <0KH300AFXWIRVV20@fe-emea-09.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="Boundary_(ID_HEPbNIqCpZJZDVtxvGszWw)"

--Boundary_(ID_HEPbNIqCpZJZDVtxvGszWw)
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
Content-disposition: inline

#At file:///home/thek/Development/cpp/mysqlbzr/51-bug40127/ based on revid:gni@stripped

 2842 Kristofer Pettersson	2009-03-26
      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-26 09:20:23 +0000
@@ -1846,4 +1846,42 @@ 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;
 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-26 09:20:23 +0000
@@ -185,4 +185,53 @@ 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 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-26 09:20:23 +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++)
@@ -729,9 +731,28 @@ bool multi_delete::send_data(List<Item> 
       if (table->triggers &&
           table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
                                             TRG_ACTION_BEFORE, FALSE))
-        DBUG_RETURN(1);
+      {
+        /*
+          If the IGNORE option is used the errors will be translated into
+          warnings and we should not interrupt the loop.
+        */
+        if (!ignore)
+          DBUG_RETURN(1);
+      }
       table->status|= STATUS_DELETED;
-      if (!(error=table->file->ha_delete_row(table->record[0])))
+      error=table->file->ha_delete_row(table->record[0]);
+
+      /*
+        If an error occurred and there is no IGNORE flag;
+        Signal an error and exit.
+      */
+      if (!ignore && error)
+      {
+        table->file->print_error(error,MYF(0));
+        DBUG_RETURN(1);
+      }
+
+      if (!error)
       {
         deleted++;
         if (!table->file->has_transactions())
@@ -739,12 +760,14 @@ bool multi_delete::send_data(List<Item> 
         if (table->triggers &&
             table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
                                               TRG_ACTION_AFTER, FALSE))
-          DBUG_RETURN(1);
-      }
-      else
-      {
-        table->file->print_error(error,MYF(0));
-        DBUG_RETURN(1);
+        {
+          /*
+            If the IGNORE option is used the errors will be translated into
+            warning messages and we should not stop.
+          */
+          if (!ignore)
+            DBUG_RETURN(1);
+        }
       }
     }
     else
@@ -834,6 +857,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);
 
@@ -869,21 +897,30 @@ int multi_delete::do_deletes()
           table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
                                             TRG_ACTION_BEFORE, FALSE))
       {
-        local_error= 1;
-        break;
+        if (!ignore)
+        {
+          local_error= 1;
+          break;
+        }
       }
       if ((local_error=table->file->ha_delete_row(table->record[0])))
       {
-	table->file->print_error(local_error,MYF(0));
-	break;
+        if (!ignore)
+        {
+          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))
       {
-        local_error= 1;
-        break;
+        if (!ignore)
+        {
+          local_error= 1;
+          break;
+        }
       }
     }
     if (will_batch && (tmp_error= table->file->end_bulk_delete()))


--Boundary_(ID_HEPbNIqCpZJZDVtxvGszWw)
MIME-version: 1.0
Content-type: text/bzr-bundle;
 name="bzr/kristofer.pettersson@stripped"; charset=us-ascii
Content-transfer-encoding: 7BIT
Content-disposition: inline;
 filename="bzr/kristofer.pettersson@stripped"

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: kristofer.pettersson@stripped\
#   jvms6ovdlebyv1rq
# target_branch: file:///home/thek/Development/cpp/mysqlbzr/51-\
#   bug40127/
# testament_sha1: 0a9a3b1f0e159b08ea0478a17bbca42f56324339
# timestamp: 2009-03-26 10:20:35 +0100
# base_revision_id: gni@stripped
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWfIYt2wABlrfgFVQeff//3//
36r////6YA0t3L4uG9xj72+u7Dr7YfU+66+zee4vu+6819t7rWt3br0b0zu28OGUTVPySbKep+oy
n6SD0myg2kAZBoaAAeoGgAJJE1DZNCaGpppPU8iemoAANA00AGmgAASU0ARqUyaPU9TyhgCaGQAB
oAA0AGIJEiBJhonqTaTxT1NJ4I9ARiDRMaBpA2iBgRU0gSeiT9U/SZTPVPJGmjQDTJ6gADQADQGg
JIgQnqYIEGibJKfjUT01TBiIyA81RkDTQ9QssBIMx/kRuSQM6Zk4VlJCH5EKQfP5qx4xRn9zYJ0X
NOKIlytsFnDO2FuKsq1UwrB9YKUD8u1JSadbHwz2xzbOC9Y0zNOduTYHmXADCHGJ3tasprbbbc8A
5SaGy5aXF5Q5DMnF2xjIPaCSPzFZ5lkLB9tGvRlywqMEO9PFwCARUIFSwjQs1lNlvWmzUUUcyXpE
e05T8kBUOqqIhkEEdYrTkD+VkTG202Bi7QuK9k36RnJMHT7gIYHmb9u6xpv1Oi9SjqdyrEGpZQhg
wW6NMeQL+IcjuMz3nWHeI8TrNQtZs4dqsCrm4XrDED1CO/pE5wFxDqOpfeBIDIWRq1iwPMYxjGDK
QLCmhtI3iZuncyj7hs7RsRv0faO3HaZvBO1Fuv0TH0JMcmM2Q36lxyIzadnHC0B16ZIPmTpBZnFp
5CS8HAVotPDG5F45OJYtVT5sLwQ1nF70jkLJrQxoyu0Rz4hZDyNSijd71dxjOJJmcibWhUynEc8W
sLgFEIkTiE8AYcDDDyJAGIEjQvLIwFhIvfFYzGjshjYPdkyoSUKLmlDVRZCzLuk7OxtMwUUYbDYR
fciBDMJhbZev2gAnBC5QJG9qBAedb0f686aA7Bhtpi3RJgtloTYuCDGjOLJqMIjoJgDNcg4w5xmY
ZpPtAWNsWc6g5F6/Z7bq/TztxEN3jGWloqRJlpabAzXRuLeS0AH07ia5l6v+nSGBaOst5gqL9PwR
xrbbIVBFx5e0eKxNd1vyHm0Po4rXXoIGBzixRaGlMIgLWK9IiI+8Ovhw4QCeV2yq80Rb2KsNWX7D
JqgOwMyR90vaTKJ6pVcwrc0wGhIc47bQJN4STMWmx1iT0TdoFGkpJh6gAPEPYwVqGqQHhC2P8kVO
/XuGklZqE+hiEAWZA8gcBhllEWWKqmXHEyl+OkqvsfiFK3e4puxVYggDF8jcUQePerCHRhi0Y1JG
uRey8FkTJTFsGMQ/2Zn/D1KwYR9ojVttsJq4WDuOBIZEgacpcqSMBK7zaqmkFDmgSqlpxsNp7ajn
WQRRWHqYmUJjNaArhmVyp0Dj2aPVOZa4IXzbdqCqghVWmbrAwLVGIis43qXVJy3qIsdq2E5Yiv4m
A4+a3cOhX2GdaQDbsBzSpuWnwTU1hdaXkLFkqmtRzHOcodquw2DUmvH5zcGAwxtOkgrO9k1oYb9e
e4iZuLpOcsUDLcXE/P22q+hoWkxHL+5CslHcrXDQ4xWJ1BUWmE7uEma39BXQr3rmZmztWtaZMIuK
KVYpim45QOOOgzIJToXFbjMJk/pipgRWMbDSdE2zXfEMaGPFspiWbz71FapYcNJJaJLYgFFlMlyd
XK9FNzgkbqvNCcMbsx99Nlg95JQcsyw6cpE36iUyI4zLF6G82DGZRXltDCwJAF0OhShERJn1EVfU
tmiKkYwtcqaCISNB1F5aTPacxYbCRoaGAxxbasdJXUopwtnBEVSDhF5SgKlOb3Z7g0wZ3lJ1ybFT
diYUhac5JEopdByWd7gd7LMYRiL5ScbKx6zpghvQzdUII6xFHB1Bz+GVLwayLx4igyXJSl4DiPtE
H8j5CPiF4jWKpsGbTOwR2iBqjLSdYDKREBBMDAI9Xq/ehVDYlSPDiSTQ2HWKIbYxQIgvCJCJwB9y
YFJIcgMgbBLgqH8j7QMR8hKjkEtFISiv4I+hYBhLoszgHkxH7EKS/oqBTAqBtDjA9ocglA2iSBwJ
CUGTz7W1xiVKHbsA9gBAYxBZgVEYAagiH8yiiLIUhQSL2oCBaAMwzEmMwagIExAVkCxBwiAHTEcg
FQHgZxhFgWgTgGJBUDZuZrIGgUhwTitEbiQoigEQIr8wvVQPQBUkTBZgwIvBGJg4TEWFQICWkQVp
YJJZiWhgTbgzCZmoCwC9xBABeKxF8NJq8/QXFQaS02lYV+5HiPHvcynAzoPEuFUKBHtERLj/aia5
mi+/MdBILxM7A7ofEglI2xpR4yoCfGWlagewY5CYYv9MDUcdUzBcew3II8wm6ai+bZtNp8SxGkOn
I3Laq2EkSdFGSyD5yCfrjrBfALibkT40Y24FjmjYmgroK5U32+OR+DGg93nwqeZIYYR1HoVKdpVM
SNa4DL4HE3mC7OzsD8jrA3onwWN3ImPazI2CIoDPt0OUjKZaHHRJrbkIHSHDQD4CheOWHXBSQq6a
90VWJhTkrlKUaxhtGcyreUmQRWoCaVDKILiqMPxIUJ0AhqeDVINUiSkiG1uyELIbinq84o5SylUZ
h5EwmjUHkoiKsMHVOPYOMheIrN84jJqGwmdRQX4+AsrC7gEYOpbE0raWjLmBg1h2uzJ6iRhDB2/q
0iQJbKfVV206kkQAkhlDbzG9fgQNbaDWRqOeGzsJaR7zfzsa0TOJNimaURPY2twiI7lCEBvxUtu+
O6Kv1kyy2RwP0FnjfLi1P4VQjTMrBVqbZnUbLtueJBkO305LdcyRLRGljbwuj0GoLz5P7II/KAXI
VhYJuvRHMhflmKRuYREppYac4PFjUopEoMiVrguLIvT4owh5wlxaayGnWPJrRgNRzwmCmEcv2XYX
D1ltUwqG6hsetJFTbQwF/zK3qXctVqXcjey5LsYRGewWdw4Eq3VI/zmsOXOvA0x2gZdeTCbhXKSR
7GO2e/Ou7jqm4amlx0u28DQ4C0M3S5WZ5sOwdK6dXNr1DoHvGQ3JN8gmQoHBQKQX0KZ0nFYcpeDp
0Yw0I9PkFM5dyA61mVJB4CbX3x5pczw6oHhNf2d09yKFy57fXWfgUy+wTQNd4m2ExD6bAURxQeTp
sWjz6jN4VJ2BN91UZoiWom6DJrrp3hs4P3oNKSctuV34JEXoE5po3JIksU1SuUObUHEsyhQm0i8I
2i6gIBTM60TFvbyLcZkg+hsiLi7bqrPh79SAkQXZDFGEer9ttppqp7i35RDDVwiIRTlowH+WsRsK
sbSZVMykSZLlSZA6dsgOYLLpuDChXcmq20C0XGm8oIIsMpqupe8RIRImesRwGEOWIGcZlk1XrVKy
Ea14aabGJwyE2kw2Vw194xrA4zEPUb14pryYjcxwWdsBEoQBeC45GKdAdDM0jacnlWTIGcBBIcG9
uzFrl74Xwek2+8HrUmTDjpERZlt82SYD8yEwhWiaYrvRO3avERC9hTM0LbgLw8O5EGWn0kwwLQPq
SMdHMqKkKFOEiXaVvvTDyKIwEX8tmuavAXSvTjYob1TQdrJmsjRJi35pFrNpUUBJpYUVtZ5qYNgW
UBdcSgiCHjJc/dRe69WMnyKcxkPpsSITWaa8rzJGlK1CHwEsIdQirCr4kQDBNg2Fb4VJbZmJ5dKi
Si14mrAo0iHMbI3NIsm4rDBsWrXP0udhBpb0KISC2KoGIiK7tuSKqwsqC8g6mq7u7vec/pSUIwUo
khWG0FhASuwrCObApFRSRpmjxEoRrK3Lao4dAtCT42Yg+eWAaKzZMtBkww0olwSO0JgPCMJgYEcx
q76ka54yy1FTGETlREP6/Z5YSJpgtWbJWtGNzD6HffQk4KdTkG8tNRCIn2TAHZT9/OxfMSMaXdnB
xsDmcNRViTX3fYEodmEpZzkWWwl96PA0Bj4davVxPoNTbyg69idl97At5vsF92y3Gw1s2UPQtcR1
hJb464luw4+04Gx3ddwEtE0uZ4cgxog+1m9J12T5CWdk7LmI5bnbiOlCbrJb3bhKceUoIglDuOy6
B0Z0CkTEPvW9ctq1hlwWYnrJPkOmDDB/xdyRThQkPIYt2wA=


--Boundary_(ID_HEPbNIqCpZJZDVtxvGszWw)--