MySQL Bugs: #40127: Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0

Bug #40127	Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
Submitted:	18 Oct 2008 15:27	Modified:	13 May 2009 1:50
Reporter:	Aleksi Torhamo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.67, 5.0, 5.1, 6.0 bzr	OS:	Linux
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any

Description:
Multiple table DELETE IGNORE hangs on foreign key constraint violation.
Hang observed on 5.0.32, 5.0.44, 5.0.67.
Version 5.1.28 seemed to be unaffected.

This is very similiar to bug #18819, however i could only get this to happen with multiple table deletes. (Obviously, since that bug is fixed)

How to repeat:
CREATE TABLE a (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE b (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        aid INT UNSIGNED NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY (aid) REFERENCES a (id)
) ENGINE=InnoDB;

CREATE TABLE c (
        bid INT UNSIGNED NOT NULL,
        FOREIGN KEY (bid) REFERENCES b (id)
) ENGINE=InnoDB;

INSERT INTO a (id) VALUES (1);
INSERT INTO b (id, aid) VALUES (1, 1);
INSERT INTO c (bid) VALUES (1);

mysql> DELETE b, a FROM b INNER JOIN a ON (b.aid = a.id) WHERE b.id = 1; /* Works as expected */
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`my_test/c`, CONSTRAINT `c_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `b` (`id`))

mysql> DELETE IGNORE b, a FROM b INNER JOIN a ON (b.aid = a.id) WHERE b.id = 1; /* Hangs */

/* Same queries with version 5.1.28 */
mysql> DELETE b, a FROM b INNER JOIN a ON (b.aid = a.id) WHERE b.id = 1; /* Works as expected */
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`my_test`.`c`, CONSTRAINT `c_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `b` (`id`))

mysql> DELETE IGNORE b, a FROM b INNER JOIN a ON (b.aid = a.id) WHERE b.id = 1; /* Works as expected */
Query OK, 0 rows affected, 1 warning (0.10 sec)

Confirmed on Windows. (Omitted success messages from the transcript below)

Server version: 5.0.51a-community-nt MySQL Community Edition (GPL)

mysql> create database test40127;
mysql> use test40127;
mysql> CREATE TABLE a ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB;
mysql> CREATE TABLE b ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, aid INT UNSIGNED NOT NULL, PRIMARY KEY (id), FOREIGN KEY (aid) REFERENCES a (id) ) ENGINE=InnoDB;
mysql> CREATE TABLE c ( bid INT UNSIGNED NOT NULL, FOREIGN KEY (bid) REFERENCES b (id) ) ENGINE=InnoDB;
mysql> INSERT INTO a (id) VALUES (1);
mysql> INSERT INTO b (id, aid) VALUES (1, 1);
mysql> INSERT INTO c (bid) VALUES (1);
mysql> DELETE b, a FROM b INNER JOIN a ON (b.aid = a.id) WHERE b.id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test40127/c`, CONSTRAINT `c_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `b` (`id`))
mysql> DELETE IGNORE b, a FROM b INNER JOIN a ON (b.aid = a.id) WHERE b.id = 1;
(hangs)

Thank you for the report.

Verified as described.

Version 5.1 and 6.0 crash in my case. Trace for 5.1:

Version: '5.1.30-debug-log'  socket: '/users/ssmirnova/src/mysql-5.1/mysql-test/var/tmp/master.sock'  port: 9306  Source distribution
mysqld: protocol.cc:416: void net_end_statement(THD*): Assertion `0' failed.
081019 11:18:33 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=1048576
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 59974 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xa770548
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xb2b25448 thread_stack 0x30000
/users/ssmirnova/src/mysql-5.1/sql/mysqld(my_print_stacktrace+0x26)[0x86c7601]
/users/ssmirnova/src/mysql-5.1/sql/mysqld(handle_segfault+0x2d8)[0x8289ec0]
[0x2ce420]
/lib/libc.so.6(abort+0xf8)[0x316678]
/lib/libc.so.6(__assert_fail+0xfd)[0x30e269]
/users/ssmirnova/src/mysql-5.1/sql/mysqld(_Z17net_end_statementP3THD+0x1b9)[0x827feb7]
/users/ssmirnova/src/mysql-5.1/sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x18be)[0x82a9f88]
/users/ssmirnova/src/mysql-5.1/sql/mysqld(_Z10do_commandP3THD+0x260)[0x82aa34a]
/users/ssmirnova/src/mysql-5.1/sql/mysqld(handle_one_connection+0x115)[0x82971a1]
/lib/libpthread.so.0[0x45fbd4]
/lib/libc.so.6(__clone+0x5e)[0x3b74fe]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa7c3fe8 = DELETE IGNORE b, a FROM b INNER JOIN a ON (b.aid = a.id) WHERE b.id = 1
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file

triage: setting tag to SR51MRU (embarrassment factor)

Progress report:

This bug won't be fixed for 5.0.

In 5.1 the following happens:
In end_send() the multi_delete::send_data method is called
This method then calls table->file->delete_row(table->record[0]) which causes
the error 152 (table->file->print_error (which calls my_error)).

multi_delete::send_data() returns TRUE which indicated that an error has
occurred. Back in end_send() there is an immediate test for an error state and
then end_send() returns NESTED_LOOP_ERROR to do_select().

There is no explicit response to this error but it is implicitly translated to
a local variable rc=-1 which causes the debug output: Error: do_select() failed.

The function returns -1 if either join->thd->is_error() =1 or if rc=-1, in our
case rc==-1 and join->thd->is_error() == FALSE. THD::is_error is wrapper around
main_da.is_error() { return m_status == DA_ERROR; }. (the difference between 
INSERT without IGNORE is that m_status is DA_ERROR here). In either case
the return value is -1.

do_select returns to JOIN::exec with value -1 indicating a failure. This 
error is totally ignored and JOIN::exec returns "void" to mysql_select.
(The only remnant is put in thd->limit_found_rows= curr_join->send_records == 0.
 thd->examined_row_count+= curr_join->examined_rows (0 -> 1).
 JOIN::error was also set to -1.)

In mysql_select the process info goes to "end".
Then follows:
err|= select_lex->cleanup() which calls join->destory() which
returns the value of join->error==-1 which is interpreted as a bool and thus
indicate an error: DBUG_RETURN(err||thd->is_error) => mysql_select returns true.
(note thd->is_error() is still false)

In mysql_execute_command[SQLCOM_DELETE_MULTI]: This value is again combined with
 thd->is_error(): res= mysql_select(...)|thd->is_error()

The error state cases an abort on the result set: del_result->abort()

mysql_execute_command returns: DBUG_RETURN(res||thd->is_error()) == true. This
return value is ignored.

We enter dispatch_command.
The error state in thd->is_error() causes a ha_rollback_trans(thd, 0) if IGNORE
isn't used; in this case nothing happens.
thd->main_da.can_overwrite_status= FALSE.
Ending statement and closing tables.

Assert in net_end_statement() because Diagnostics_area is empty.

SOLUTION:

It is possible to add error handlers which captures errors and rewrite them to warnings. This handler must be placed at two places:
multi_delete::send_data()
multi_delete::do_deletes() (because if we ignore the error in the former we will create new errors in the latter)

Another way is to consider current_select->no_error variable which is set if lex->ignore is set. This variable governs the behavior of my_message_sql and also causes errors to become warnings. At this point in time it isn't obvious to me where to check if the no_error variable is set.

It is still an open question whether triggers causing errors during row-by-row execution should be ignored or converted to warnings.

It can be that converting errors to warnings will cause many duplicate warnings.

An attempt to define warning is found in WL#4103: Define IGNORE.

Similar bugs exist like bug#30191.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69876

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.
     @ sql/sql_delete.cc
        * IGNORE option wasn't implemented in multi_delete::send_data
          and multi_delete::do_deletes

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/69877

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

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70491

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

Update: Review feedback: IGNORE is defined as a best effort to complete execution despite errors but it isn't implemented that way. If we consider INSERT and UPDATE as well, could we achieve a more consistent behaviour of IGNORE regarding error handling of triggers?

Dmitri pointed out that the WL4103 says:
14:22 < dlenev_> TheK:  ugh... look into the WL that you have mentioned
14:22 < dlenev_> it says:
14:22 < dlenev_> If a row is skipped, then effects of FOR EACH ROW ... BEFORE
14:22 < dlenev_> triggers are rolled back if possible, and FOR EACH ROW ... AFTER
14:22 < dlenev_> triggers are not activated.

We have different dimension of the error handling here: If an error occurs while a row of data is accessed, it will affect the triggers. IGNORE behaviour hasn't been specified on what should happen for errors caused inside the actual trigger associated with an action before or after the row operation.

My interpretation of IGNORE is that we do our best to continue execution no matter what non fatal state a trigger reports. It is however out of the scope of the current bug report.

The current situation is that how we deal with an error is almost more significant than if we trigger an error at all. If we use non-transactional tables parts of the trigger and parts of the row-iteration are completed, but if we use transactional tables then the statement is rolled back regardless.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70569

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

Review sent by email.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70634

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

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70726

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

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/70728

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

Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)

Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:kristofer.pettersson@sun.com-20090330111749-sfvydel26k7p3n23) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 5.1.35, 6.0.12 changelogs.

A multiple-table DELETE IGNORE statement involving a foreign key
constraint caused an assertion failure.

Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)

Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)

Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)