From: Date: March 26 2009 5:51pm
Subject: bzr commit into mysql-5.1-bugteam branch (kristofer.pettersson:2842)
 Bug#40127
List-Archive: http://lists.mysql.com/commits/70569
X-Bug: 40127
Message-Id: <0KH400IZ3HE4GO30@fe-emea-09.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="Boundary_(ID_Dp5R/YhJW3Rx0KbDs/yCXQ)"

--Boundary_(ID_Dp5R/YhJW3Rx0KbDs/yCXQ)
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 16:51:13 +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 16:51:13 +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 16:51:13 +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,19 @@ 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 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())
@@ -741,11 +755,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 +843,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);
 
@@ -874,8 +888,11 @@ int multi_delete::do_deletes()
       }
       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 &&


--Boundary_(ID_Dp5R/YhJW3Rx0KbDs/yCXQ)
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\
#   cnm25krbgh72msy4
# target_branch: file:///home/thek/Development/cpp/mysqlbzr/51-\
#   bug40127/
# testament_sha1: 68ecaddf9ffa2689c1620ea09d0245017b0d40b8
# timestamp: 2009-03-26 17:51:24 +0100
# base_revision_id: gni@stripped
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWXuMrScABdHfgFVQeff//3//
36r////6YAyHafLxd5t4JKCjAxtW3Rth7rHXs1PdlWyi1t9nhJIRTam01Mp4KbNBD1T09FPJlH6j
UAyAeoAaNBoJKTBNNBqNT00qeU/VP1PNVPUaGh6DSekNAxD1DagyeSBpNMmiT1NRkBoNA0aaAAAA
AAGgAJERBTaaR6psmTVPTUeo8U9I3qm1GR6g0eoHqGag9QAI1U9QNNBo0AyaAADQaMgAAAAACSIQ
ATTQGgp6NTTaE00FD1HtTUH6k9QAANC3vAljXa3XwuQT5MnIQ4HDXnyfbs+n54yjFu4pbTcc0xxF
ZhzcS73bHEdb68UZupr356JV1DJjDCE1ouE2FZwYIM7S82F5Ze2226YjmWhteq+DSUhDagzc7PES
3pFGAe2azvNj3UdrxqsI4+SfDQEItSFJiXBZYX46PHnbpSnevsEdx6jpIC0dtsRDIII+sW+bg71k
mNtptGj9Q4TTW/aOCrDZ8Yp0cPcexFtWckLOoxVTGyt7mppidwL7w7z2GB8ziHaI9pxMwsDP1c1Q
WOcReAZgO8R25Cc6hdYbjavqBIDAWBjwi0HcMYxjBlgF5ZVtI6ChzmrWPUcWw0xbW6frWoYXA1Eb
b8Vg+IgY/ZsZA7sPyAVjahw4jFWNLPYNFCw3ChqRAtvUEolWqTqhXhwhN8mr1VQg6h02SkSI5JUY
5moc9QJE8LIRJZveU3CllEDNxNQkhqPWaReyLcKoVKnrFIBAwggkqUBklSxfRqMBYPMKqUMo0rKr
NTID2yW17cYvht8S6Ojnj5NNOrXasFRfl7laRWh7rF8B1Ut2Akc9Aw2BUO7GwJkLcMGRhcgkwK8y
EzCzOLWAXRoJhFRAAYtjjyh8IzjGcR4Qrxi2nzG5fTlZ2dbcRDdxoMTEVgkwvLzEYakcVolzAHRk
QWxeb3HvBgSKlq5TBRXucR+tHKymmYVR4/iHgsxonpJM/l6ljdtIFxsFeikLEwiAsRWpERH0OHFg
jfRnqX2RFrFVDN5kWqAuYGvcCXxIEIfFpYnuWMAFFDqoWSdeIydCjQqhlyICjRKS9UCKkAOxWpxy
oIU1/43lZ7z4TlXkYCq0CdO4LEge0HEwyxiLPgrUy5YEBF3XiWrpbWKHMYjISoIiDwZIzVYZjWq9
IwhpDlorkb5lzF8KeOeHIMMA9h7TtTS0qEZqEClSoBbBhMMCcrSQxbaa3WJU5AfGbVBSvJ1CGvmp
+8RtWYRYrDORVKOBY8lcUDQFgGxTGoJkjt0kkNUJYTmDW7dgWpwiTy1XUHNWJBVCNE0OGZTqngRv
5rAqmwF1mbORHIULLqxV3YVEzBqgjUECtaXIq6o9E2kKS8ehZ1rNSpGMj1q7YZx5t0y8VSK41Dz/
gyagoy0j4Di3GsmUC/EnJvtSI+WpW5y1ZG44EF/3EwL1z2UGaJRgrDE+HIJDPmEetZ5asWZVqZMq
CSgIjZogp2L13TiM0C68wIpbKyRfrHLwvUVnJfIt63KyJMyqVC5X7VF43nEcW9c285uqustNmzGF
q+766YIpucXBazib1rIjm66DU7L72ItGxTOtixrgPnpkom8kOxmeciaiZVk8xMEQC3JR8Y3EFTYR
RwIqU8CJoNBcVngbCY0lZg0MRnK9S38jC5UUBwWSiitmApFayK23X6MuIM8uIrWDq34ssC2bzYVR
WiXGdSyxgDpa2gsxU3MtGb2hwx8jOhQgjtEV6flDd8k2RLupFOYUtLyKteogR2CDuP8EfcGIjhFg
aRtjb7hH7CB112HaAywRAQUAziO+VUbEqDwzpJobO0UQ2xigRBcIkRSAPAkCoxZgTBViLwgL2C9I
EjrEQO4RMUxKzuEeswAiUsYyoBpcI7QFP3CcK4CAiYdgHpDuEOFMQwFtGCHF33dghyQcMQPAANGF
4KgFgi0DAIh+0nURXikJxjNMA4lEDEUBEikRYBAi8C0gSqDEUA2iP9AvAkDYOIqEbRMA5MFIGOlm
ogVikOIcVIjSVFQUhQCngGOAHIBgVLAWQMEYgihhUEBFZaA0SuEEUViGJNETDaNoGQjTMBQBnpBw
CooEWBwO75/I0E4cDMbaSj0RSbKNag4qKVoUScI6hDn6fZFA1sKVtpUPDfIH7AyD0HIQOoZKo8/Q
Z7QKT6l+BemIFoTf+tA4n0prJsCs1SR9BbbBd+jSaTSTotNmBoX2soJIk6J2RgHkQffHQC9Comci
ffOxqvKHK9LQVUFUp/1S8Go/XEtN0z0DRHqLD4F1eq5aGUyJUvQcDibjWshGXLlD7mQCeU3K27+M
e5mRIJYMzKQ4TlxFJKuW2Xj1Hfea8juRHoqeh/DPxDA7q59advBXaFzWuaEXxJxGbBASSmYmcxKH
v+w6dNIEMo2+EDrgQgnZu2YQrz2KfjOtBrNS5dvaVyLUOUF1SNZAJSEWOOHQ+vYObRF5zPmaSRkq
EGu+ysL4420HBdi259AGMgOyTN5KFRLWnZioUQzHyKWa6xJlAECDiRMHL7xpDZ5MyH6HHOg6871D
dzMNiIHAgyLORJwm1vKtjTiEGcSIwd4E1jISwl5hCN+KFFwWhHErjAKe6oykskpC3XMwLMrm0vHs
0duVRJkPzTpYOyRXvqpY1c6o7jELj8H90F/aBUCoKBM9WByuwzomNbCHaCKDjqBeliRGQiJaidLc
lTau70fJ+oReEHGN/E4nI5FynkIyMyBmJgUwS3fwuZU6lnXAEw2kzPWMgo2rsBf0VPFdFppF0RwZ
aVyZBG4RPpFnrHCa2ugP8+F517VzNM6BEZAbNMsKuFqUpdrfBT3aXat1tHCsUViJZFsUFOIgnrGr
iedM+o02Xxz71QsQtpYMr2bYJhycM4iE8JUKBtNch4DfnshOjz1EDPmQGLArJB0E2PbHXJn37Oky
/m7p6kTmyjwrs6lEsYQyBliIMxAQ3RICc7U459kin8mgw0p65lU+45zVD5yy1Lh2Co96R/rccEMs
9efxcYfBARimR64IhfGZeR7+8Pask6dMyRsNEQcFIz1zMUdd8SAfFryLi4V2Km/xakB8QiA5b3YI
rGrZz0N1tfhXCyQRm7qEa1zrjtP+XtCOoz5PBrO02NNGlEkC5eUgNgk3Rb06QT5kfKusVYnGmeUA
IsymVmjIR+URMImJzeI6hhDlKGcygypsnpdFlQyZMwwoahpMNi9+7gWblGyfIeNeVPEsEa2c2V8i
KzILotOBmToDJneY82UCl06TsBFIYO/pOLqMLmlhukleJNMghBQWRbhY0wP3JsJ3rU7Aw3wlsqxY
LUhZXi7/f50NYrdCAXl4HSkYTzUVFs5PykS6yy65MPKdW92jGStAWS530KG5T1HWyZlgyIML2RSL
2PJSkQZK5FDb4rmIkDsFUxoyLJIehoxLfyozqzVGJsyWw5DswSH3LK75mmNkkqIBuhC4JrVWgHGB
MwMyPBOn5GWviowT3tc1xKAzHHnxRhzVJbozZzrxakctbiohIKYzhmERXZ54IsoKLAXkHBrHd3d7
jb60lCMFKJJFBqBXtErMLWu1sBPtE9F0EfOJOfmIsjP/p2DBjiboTmZNsBttrChbTG+/fqZwSPMJ
gdgxMGCP4N/SpneuoYURawiKpOsD1+b6cHkEwJLWxKTHOsqrg++cYFNYdwbygxIRFClOkeSh45OW
bCYsoubsGe2OGRcr7V+kbyQXLXCKzLLs0IyyQvMqBh1nt1s4yksHKBVTgU4bQYgESVBiRiBAoAbM
wyMJZ4OCpKjOtHktpj+OxbRG9Yr4aRPucmCtN5jYptK8h7loVo/N0dSZLUt7olZgRHyEQHgvUta3
C3aUBUPdWC2qFjC7HZrPVyXppZv+LuSKcKEg9xlaTg==


--Boundary_(ID_Dp5R/YhJW3Rx0KbDs/yCXQ)--