From: Date: March 20 2009 12:20am Subject: bzr commit into mysql-5.1-bugteam branch (kristofer.pettersson:2842) Bug#40127 List-Archive: http://lists.mysql.com/commits/69877 X-Bug: 40127 Message-Id: <0KGS00F9W0R7KWD0@fe-emea-10.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Boundary_(ID_urKbQK0EMm9SaCZFgDpRiw)" --Boundary_(ID_urKbQK0EMm9SaCZFgDpRiw) 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-20 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 existance 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-19 23:20:25 +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-19 23:20:25 +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 1451 +DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1; +--error 1451 +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-19 23:20:25 +0000 @@ -709,6 +709,8 @@ bool multi_delete::send_data(List TABLE_LIST *del_table; DBUG_ENTER("multi_delete::send_data"); + bool no_error= 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 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 (!no_error) + 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 (!no_error && 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 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 (!no_error) + 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 no_errors= 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 (!no_errors) + { + 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 (!no_errors) + { + 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 (!no_errors) + { + local_error= 1; + break; + } } } if (will_batch && (tmp_error= table->file->end_bulk_delete())) --Boundary_(ID_urKbQK0EMm9SaCZFgDpRiw) 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\ # hqwkosjs90jx5j55 # target_branch: file:///home/thek/Development/cpp/mysqlbzr/51-\ # bug40127/ # testament_sha1: 14d8c547296aca8d12f5f5c27eca75aa3e6bc633 # timestamp: 2009-03-20 00:20:36 +0100 # base_revision_id: gni@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWVYiuwUABnlfgFVweff//3// 36r////6YA093z7tpwnJx2FCgilt2d23XTWQHBVVXYYjosbjmjRoaYQDTAmmgDIaGIA0YjQwRkAO aNGhphANMCaaAMhoYgDRiNDBGQASRGhNBTInpkUHlPSep6n6oPUBpoNqANPUNNNNA9EEiVNJtKea jSZqPaRsoZT1PU9E9R6Rgg0MjGoeo9QwmgRSmQmmSbRokemk9Sfomp6abSeUE9TRiGmhoADQASRC GgmEACNGphBoAieiZpPUyeoekDRkyNeUEKUp72v8/5TCC5TefkJK8DluORrRn7evATUZta1Nqbo9 rVqKd5+gxYduhW0VLYMVDCcsJlZnn4qUeuFzt6q7cOdi+4ZMeSMcMi7cvGDBBr4FdlRRUzMzM0ZD QgyGZfOpzSRdDMnK+po5EWdJH55LVmL5ZiG7bLp0aKbimr4smLmBULlF6TW9++y/N2ZIic5/kvtE 9x+BiICsOtWiIZBBHMK0egM6uJjbabA1NAWyrVQ/AZyWgcOH0gRwPkcdeypqH6HRepR0O5cxBq8V ZK+AstyT6wfqDrPCYHoPCHUJsM44Gjb4GsLXNQvMF4HlEd+4TnULrDgcF9oEgMRYnToFefIYYYYY GKAJlEmZI7SJ2GrYNqNe00v3S/J/NrvU3hW9l1S9SpJrFJHoyq6H89O6hFXrdjseY2dtJyqVtNbm L10KLw9ZhZa8cpuMmIxeypw5RNDYMZZWjMunvPRWdZvA0oC6JEYFlGcpLNjKsFHdkVe88DjPIbBc 4usUBBB5BSAHGBxyRBEGIEixejbkYCwoL3xWNA0dUMah7MmVJJQpW2UNFKlQtvsS8/p5U+7rh78c LT2Vtr81QYHjejYA99mmIke7cMd1YI7HOmgOoYUpiviTBW2hNiwwXkzYFwYzTEb5IAM1iDzhOM8Y ygl/dQIaU57Q8F9Xw+7RV7/WzO7s1ZiYGAqBJjAwNAxnR4l2JcQD1ayhbF9f7HgF5MdY8S9Ur0fg jnbPoEV0CsPR7h5OY04j+y0qjntdEchBxGocqVhcwJIdA5FJieoNm3btkFWfHoufck4yt0s3rb7Q IgIhTfM85iJy7/z0V7DE6ZAMzYQye4MmYkngQyRrIqxLG5RyS75AqYQoXqk1ABDGCqOOYg2GX9uc x7f0Kj3Fdoj2GcWI1eoT3sQgC0kD0g4DDKiIqrFoTKeiJkYmdQ/EoLl75it1V5zhCmMwkDQGuJVm xTCJEjFSzndLfgq0crCmIh13qMhaRjMH6G8meNcAYRlTapiodxZbhxMMClgZkkMUEV47PRkSNhuG mOIqPG05VYnLvrARyW1lVNa7SCsFaoFw0BXhsVRM/710wVJhOYQuobVloDcoki5aC+CghTTyEWyj mUuG4opWmMzEjxOtU5CiXyzrby3q+ot00wC7SDlqoc+9VrQFc5mYjMiTWC0KRiM4x1qurQVU7t69 FJuCIxqKR5/qybArC62rj2EyjivyEkbTERdNQmqGmJbYaz8/BZXmZi/a45FrAXLA6KsijAwpKSyK kOUmJceuReV4VHUcqUjmqZNlvxZmbFXJlQnCPdiUHXIxrNBnJI0bBi6Q/rmoLPUu9cl0q2vHO1Na fbksKCLZFWsJddFR87SZzYtLH2ZDDwL68xcvt+GiCJ2OTVhtRruI41aLyEK1jsILBUTgbsiEDUOO yweZtKxytFc6CISIALZ7l4BFZE4aSF0rCZwDLQhSw7xkUHHNZ8xuINgxru+Yy57LS5w2U04aVkia rJwm8RIVfDbkfZtDOTO8Q63tqVFQKzwOQhETS5TeuHRwO5ltMwwi0ff4VExi2TDs3FjxJ0D/ERLu +sPbZKjya03nzikyXUojqHEfSIO07hHjDQR3hZGxmGTHaI/kQMo3UPvExQQ4OTAzEdnZ5YVRmSWa 9JMhmD4id2ZhhOIcrEQERcBxmQNwMCVCZQpH3j9Y4j7xP8E942i43uEfwcQszhuzOBIzEf8IWK9A pizAoCxDzAdAf0RIKwIYK6MRNyzz6BFVIL9sDIASILwc4FomUDAJh9xWzG8aRoIM7UNAswNIyEkM gagKBLAKygWkIEmB3hH7ATAgBuHEVgt4mAoCYGnUzVQLBSHBOLARMUhOEhbV/QaLEDmAzKAuEGEO IE4oCYlRaBIW4QqSsSFkJYHIQV4aRGlUAVAYqYOAUiLQ6Ty+/3F4bFH4oxm/hytjJGMaOAxreVVQ I+AiKmT61ZEZWrL4z3EArIn8w1h8xyEDoGSr1fZUM9wH3l+JoMF8C8KuYnp4AMTxYxca32nEbqI7 BNy6C38e02n5FkZhvyNS+StqJIk6GpdLEPQQT546AX4BWUORP5UsabypyzSmgq4KtU6p+T8DI+cj lcWECggI6Tivp+VqiL5ri+hdNRUUUB+jZAnrmZTRyts6G0V0B4sDUzQds8+w7wuWR4mNpM8iwNUz uRHsW4r/HLsnHUnbyrLeuZbaSm/umnAgJpQxDnQVfPxjp00AhlPPfIN8iSkh2ZdNBC0OJV2HNIuY dKtdDEjGqOEiQitxw2n7+ZgaRGcuPaZyo0qaDG+60L48rqjYua16AYN43B3UFE1Rjyby66pESjpU JqfUmcCCGWnA2/1KjPKRE4lRx3bGMkUHgSYouSiJ8O0fKAc070DfNSz8I64q/IoKpyKPTrn7IHec Zc1QjG0uBXK1tEevHxa6yTIfpTpbHZIs22zY09NcbdpnBz9XdeDI/1BOmatCqKhM/GzIfNjkKg1s IdoIqOGoF91qRGQiJmRSlwSncl2fF9zrqEXnSeBLoY5ERzI69gXA3BTh+z2O/c7m0LyOs8EnlJtU WwBxZmzW9DnsXoTkh6nZAk6tI58ZTdjrT3+ew4dbthgi8Du4SgJxQ6GSnrgjLPpni0b65xQ1NLde JU7Hg0vILNk950Mj28nWeLNKrlr8w8o9pkNsojeMFDQRQUjILkZnadlhwMfbDszBsT2eHoQNjmLT EHSJs/GOuWt4cIHdQv7u6etFJWtk/JbHqUS1gTIGXEnEBMSO2wGijdQeXx2PL8m4TrJia+ppaxOx 4T0u7eoguJdmS6O0VV/SI/PgdCGWVuheDjD50BGKZOeSSb5tTvDh4g3OtoaGIU5DTMKAaTC2pgq5 +MnhQSD1NgRcXOu1Y9X0ZICRBdUMEUkFq7cMDZdZ5HmtxhXdOx6HVUf87ROouzRbDdDEEMLgkhxd HQOGsSbvW5OkFOCPXWgrFYNQ8oIIsMqm7B7RKRKSo3Ceh5iVDEhILIoINUobLsTjhFmwNNNjE4ZC bSYXFklpPCpoGypvF5Yk2YoEbGEdaynARKEAXjXPEwToDezv2UogTdOgdgIpDB5O+kXWXXWXWQSf zwCAZ0yYccSiLInJB+Q8gIWUJtQjAVnKmvWnzMKRkharxd/f2ogyt8RQF5MD2pF9mxUqmFJTykS5 lubMmHpK0ydWnClyAOt58uJlUJysY3mhiHNCSgDpmplg3tNKSheJMUPemzYiykMNRbSlKSIXv6k4 6ONywytLNJgfpsUpyOnI+qRhjsW9FjqJcQVZGwSTgMCZgZgpbzKCfkbh9fQngnvbMymlzENREZjp j0Cx5qZdowWR152mOXN0KISCcVSGAiKrEdn1YouUydwL0hxa53d3e82+JJQjBSiSFY4wWqQk19WU 9UAoMhQjOiOsSnGFL+m+Aw0SuNEJVuRhTqiG+dLp1vM1dmEi4Ej1CYD9wwmBgR8zp+hWHK7EvwRa xSJ1GsPx975okgYKlgyVTRjNh6330knBUTO8N5UZEIihNOAedQ821zPaUMrZtyAx7AzsPZYQVXll cETW10QpSYtbQmfOjvZhd5Mdfm+l9h5W3KHixJufsaBc52NBnv7reaGtkxQ7bWx7BswcfjdRh57n UJyuDw0jRroPzr7YcjD6yNLDWJ3HgSveNylHGJ0STnYWTsoSm7ORHcZ11LYuAuGpATHxW/Jdq5Bo ovqgs9vf8Bkxh/xdyRThQkFYiuwU --Boundary_(ID_urKbQK0EMm9SaCZFgDpRiw)--