From: Date: March 27 2009 9:18am
Subject: bzr commit into mysql-5.1-bugteam branch (kristofer.pettersson:2842)
 Bug#40127
List-Archive: http://lists.mysql.com/commits/70634
X-Bug: 40127
Message-Id: <0KH500MCOOBOID40@fe-emea-09.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="Boundary_(ID_TbN33+Kd50kMQnOzkHc5mw)"

--Boundary_(ID_TbN33+Kd50kMQnOzkHc5mw)
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-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 08:18:14 +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-27 08:18:14 +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-27 08:18: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++)
@@ -731,7 +733,25 @@ bool multi_delete::send_data(List<Item> 
                                             TRG_ACTION_BEFORE, FALSE))
         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 the IGNORE option is used errors caused by ha_delete_row don't
+        have to stop the iteration.
+      */
+      if (!ignore && error)
+      {
+        table->file->print_error(error,MYF(0));
+        DBUG_RETURN(1);
+      }
+
+      /*
+        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 (!error)
       {
         deleted++;
         if (!table->file->has_transactions())
@@ -741,11 +761,6 @@ bool multi_delete::send_data(List<Item> 
                                               TRG_ACTION_AFTER, FALSE))
           DBUG_RETURN(1);
       }
-      else
-      {
-        table->file->print_error(error,MYF(0));
-        DBUG_RETURN(1);
-      }
     }
     else
     {
@@ -834,6 +849,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 +892,34 @@ 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 the IGNORE option is used errors caused by ha_delete_row don't
+        have to stop the iteration.
+      */
+      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()))


--Boundary_(ID_TbN33+Kd50kMQnOzkHc5mw)
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\
#   a4e5fkbmyh5exfi0
# target_branch: file:///home/thek/Development/cpp/mysqlbzr/51-\
#   bug40127/
# testament_sha1: c4c855d755411df3c4bc9d2e60a05758a1997ccc
# timestamp: 2009-03-27 09:18:45 +0100
# base_revision_id: gni@stripped
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWY1AuPYABm1fgFVQef///3//
36r////6YA2t33ba3Y1HX3UFO2AdTaKq7e5xAOZt7ukudwssNLroSSTSGEJvVNTaZpoNJNmk1MaE
NAAAANGg0OaNGhphANMCaaAMhoYgDRiNDBGQASpMg0SfpTTNQ00yNBmoaBoaDEPUwgxND1DE0EiS
mJoNMqbCR7UJpptEaNlDIGho0AaADQBFIQBNEZqNGVPUemUBp6T1NND1GygaaNAeo0DQEkQJoCZD
Sm0TAE0EZT09SNkjIGnqD1PUDQ9Iv2UgWUOynLG/CyDI/HoH3XDb0RL9ev59VsuUe7ME6M4NMQxj
gmoLBpG3ogP0UOtj6RMu4uCNZ6xzeGfVhRINJlty5XkylWdbIMLjjsu/gpJ6W223cqHKTQ2uerm0
i6GZOXd7G0g0kjYyDyXLhiayeJsfRqk+nolq0IEgmQCwMOZmFRoXXVvwnvQo3GZ73/ReoR7jnMBA
UDooiIZBBFHMJ2LSHyWNMbbTYsX1DU4St8pJ4DsHK/6Em35nwOfhZO9+iBipRV7Ft72XOJI7MusF
iPWGw1EDpIwrEZh+UgKc3eBWgmcZBeoMgPSI8OInOoXWGpqvuAkBsFsNu4WR8BjGMYMyUAXiidtI
0Fw65l1x5jevGSNE30uzb42uWNWy8KiHVihZ/aT6UCZV53iESaY9MchfIORg2OD3bLKEhqwRdLYS
X5/pKEizitL75jGBOIxY9qpniYtFDXNjSN5VPCwupBXjsSHhcK4uREncWqd8JEVcw5g0i0pCDKzY
XyuNT4m0XSL6RSCRI+IpAEDCCCRMXAZImL601FYq5iuclYrDoIkDhnUtloy7ipNRuW6cN9kp8fqO
ZvX7PZZ9fd3e/dpiePFr7LuBXpa0u9nQQYMskI48Yx2rQoXQ+ok0L3jDbYt0SYLKxJsWgezJWcFo
vBBiEcZcAN6DISNIcQy0ZnPwQXm2LYORGpcoaxf9V3k4m7JSk3fLxstLOQFaEmIEDAMdpHbWlLMA
c+EsLEuH/FIqykg2WFAjHtFapWr2I3aL1Mwr5q75ElYZJUt4SCUXaeh16NxMkbR0paGLAkx1joUo
J2pz+AGCONmlq+CItkrYPd6z9SuAxYGZBGKJeJmSi08kWbjtbpjaQIVJN2DvZPQQyFpc2EPOTFGy
UCSqIiSUQB2LVQctCFafiTPKWniY3EvaS2Ct2idO4LQgesHEwy0iKNdNFVMujM0/pmbi1fZGwWE+
LUeswiYd4hwg8xlQJEIKRK2A/bPjERgpos6HtN7LuUyUkbhjAP9lo5/w1PtQtaULU+y0F5BhMMCc
wSQxlkauZ2OmYgYGdGm1lLj6HhML29hrTSJGDKIwmOQWnG5FxvP+9dYK660ML83w2hgoGCxvdZlU
4jSUFkp8zFnIQ61iVJXmQtg/U64dPFYZWGdKwDXDcEDBWCLNYl56k0YSEkpETSK8r6gUjipXd4g6
xQq+4UBhGIGEloEoXeqGL9ue0zNUh3HKWMziLy4r+75OVqzMXlOgdVPy5MTBbs1lAssVwbCp7Kkz
qEcBHCXYts8dGg6xW1MrC+iinXE8OzZ4cCw0GcJJcuRobzKPsyVqrqvOutdKxtsMEyvTCMeVSft/
nHM7nLU2pmbNtq+/9e2CK5TOYiHMuU4HFbiI5yPK3WGXBiOCYRFlVOMI6evflMrDYeWFhIeQ7M2b
0HMywcopYFHMrAkARy5VPUlKC5LsCQ4yqSWlakyZic5AvNNPE5Cw1MDBoGwajaLPMw2qKcKzgiKp
Bwi8pQFSnk+s28AwgzvKTr37JkwpKpyEkSilxPOtMXA8zWcqYjATmll0ZfJ3DewzdUII8YiYz8gc
fDKbwayLx6wIMkeClLwHEe8QfVoN7z7vmI/AMxE4viYjXG2me4R9hA589B6QGTiICeRMBWIpkpxs
VTAmHViSTBsOYUQ2xigRB6IwEoJVID0kwKyByRgG8TUHBYNz/I7QLi8RYfMRgLBJX5fMR8imYDGl
zM4EDctQn+IN5o/0ax0gWqVB3gZB6hKDiJAOJAmyo7tId4lShz7APfAJkGlHgAwUyA4QoHoLGg6h
qGRBwVgSFoBsGYkxmkTAcRUCg4lIGENECGYj8gKgRA2lkxL1OQYAkWBaBw5xF0zBCokpIbxN5IUR
QCIEZH+QxvA5wLyZQFoDAjdkCaiArEtNAExchCxLRIAqcqhMQ2nyspLMMBGsAJAaXg4ijCwHEYh0
HoPsPE3mGed0oZCBFMS+lFZtFw8r2GHDHQa1xKNOEecRKfV56ho9cJjFXynkKBeVH3hvDzkicznI
XDs7by6KVeJoQjQVDwO4QTtQ1R5EhASEQTBGiIEMHT779pTgQR94ue4XjycTicAoi4yNy/gi6h5U
SdUZZB+Yg/VHQF+8tLykCR+FjG7Iq5f1tBXQtGGRTI8kXLAuFB8LY40FRnlDMWBGgzrxe26pTAoD
TmgpILNmzB9zKCx7REx71ed/GK2TIlSW/pZSmAymPUTrTulZaMhxIumBiYoNaIytTVnyhHkNc9nA
nNsVzwXYtJwi+0u2ICKUhi0nA2lj4nwHQ6ZSBDCj6nPVYQgA/hMBQxcY1UCnO+5T1KociWzRoQJU
KFBFhEcPOfd2jm8RYdJ7jMkaqxCY8H3Z4XhnH02Bi1RuK7Vw2sagweUbnd4KQ5Buv081JEhWdUKp
iqqdj1pNCmhlDXp+JuhcbiMhHqPPrG46sDm4sb0TOomxTNKInFY29w8x3qECY3xRNzXnYOEVkxqW
OxVzrJ7uS7088O1RR6MTAEYK9t7x7c+/yWkmQ/SnQcj8hkQSMON9yYOq6GHE2g582dvFD6YmGTeD
cXDG6esqy4eXYlZrgSUUS438YJ5tClKhKGSeISLV3rfpT3WHd5mR1CaintHOZr5UVF1GSQiHmcLg
K2E+9oEdS5A1carV0LhhEXzTEspGrLW2A3K1LlVa3pAK0ZGWsqZB20R76LGRuuHCeF1A/Hxqa867
WTHrEPoBuOIygwSjrJvG0F7oSOGnhu4+uuyJNbVlU+ByaO9ShmJFxbSyKRy28Jw2Snto4hbAsxUd
aTfcEyFA4JhSCdCed45ZDmCWlG4F1FKOceasAg3DGxMRhpE23ujdJk/Lw75r5O6e1FDhZ6Lr+lRL
2AGQMuZRB4huKQE7Ywc+2RUdNZi0qJbFzRX3ObaRYwU9tKngFa4+ugpQyhDZbGHXkA96ZGiJESIo
1xnJWHEsScHBgZI8htciEARIztkxZ1+Zvd6M6HuKB7W0JOLuvxXN1+c+rVATIrsqR2oqRXHz5nVh
Z7V3W1Cl76FqVKRYOxcAjYT3mmZTsmYZkF9EQ4CpiogcxY8LuZKGpPiww7CHAZkVyrmJSIcXRssL
RKxKyx3CdJAki5IkbZw3XWXSTC8hhiBihqGk6RrMtNNa2TC2NS1jtlq1J1lAjMzbRiRtiIeCgC9K
69DNOgMGd5neygFXToLgg4iSQweqR6LBdRhgSTpP7IBBbUwMOOCiLQoXzZMB+JCYQtVE0w2dE66u
tzIUjNC34i83m7kQZX9hMMSojxSMruCoqQ5yws6Zk+tYZZJiJqEVb+THMqgFnW5iuqVqovKxjYxj
LIRJpaKKaYPhaqmcAZJbG+i0QuqKGs5HCtK0mQus5uROKU3TDPEu0JmGs7+lSvJzy7Zll18l1Kj3
5DqIGMYT4QvgKExsE2MY2jgUK70nHWBzlIrqIbGyZTCZAZjy9HY6NOxVDHfmjadm5qjmDdCiEgrG
gbBEV3/bmi+w1K4AvaHO2Du7u+Rx7klCMFKJIVTgC2QErdmEI7WBSV4pIwmj6BKMv/FtLoW1GGci
WvOFrEKOGyknTrmMLMomIKeMYA7CBgIBP0PY7G858dJlpTGCwZMbQ+R7zFfORNMFVu5x0rWFKV20
io4SfmqDApYnoDmLDeQiKFU+TJB3KP0crmGJMwrk3awyY0IlAStbrE/aV6Ja7ZJU9XrRVVBfokme
aPW0DLbPZ6PSvQc6mvGy0jjXxUpEJXzkTiGJXQvhYeMPTTTpwbX4Bsb3Z1u41/Hi8gnM63x5oy3S
KwwY+kjWTyLUROVLGsJGiADcTSyq2ETYZEFhBKBwvXeATieUeWaBcS2PJrfbeYM69Ns3Xj3PvQR/
4u5IpwoSEagXHsA=


--Boundary_(ID_TbN33+Kd50kMQnOzkHc5mw)--