MySQL Bugs: #18819: DELETE IGNORE hangs on foreign key parent delete

Bug #18819	DELETE IGNORE hangs on foreign key parent delete
Submitted:	5 Apr 2006 18:00	Modified:	13 Nov 2006 4:26
Reporter:	Jeremy Papp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.16-nt/5.0BK, 5.1.12	OS:	Windows (Windows 2000/Linux)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
The thread hangs (goes to sleep, does not wake up, no reply) when attempting delete a row that is protected by an ON DELETE NO ACTION foreign key with DELETE IGNORE.  Using DELETE alone works.  I have observed this in the mysql command line client, and mysqli in PHP.  SHOW PROCESSES lists the non-responsive thread as sleeping.  I don't know if this is a problem in the server (IE, not sending confirmation that the command was completed) or the client.

How to repeat:
CREATE TABLE `table1` (
  `x` int(11) NOT NULL,
  PRIMARY KEY  (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table1` VALUES (1);

CREATE TABLE `table2` (
  `test` int(11) NOT NULL,
  PRIMARY KEY  (`test`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `table2`
  ADD CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`test`) REFERENCES `table1` (`x`) ON DELETE NO ACTION ON UPDATE CASCADE;

INSERT INTO `table2` VALUES (1);

DELETE IGNORE FROM table1 WHERE x=1; -- Causes the connection to hang.

Thank you for the bug report. Server 5.1 version is not affected:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 3
   User: root
   Host: localhost
     db: test
Command: Sleep
   Time: 110
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 4
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
2 rows in set (0.01 sec)

mysql>

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.9-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `table1` (
    ->   `x` int(11) NOT NULL,
    ->   PRIMARY KEY  (`x`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table1` VALUES (1);

CQuery OK, 0 rows affected (0.03 sec)

REAmysql>
mysql> INSERT INTO `table1` VALUES (1);
 TQuery OK, 1 row affected (0.00 sec)

ABmysql>
mysql> CREATE TABLE `table2` (
    ->   `test` int(11) NOT NULL,
    ->   PRIMARY KEY  (`test`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `taQuery OK, 0 rows affected (0.01 sec)

blemysql>
mysql> ALTER TABLE `table2`
     ->   ADD CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`test`) REFERENCES `table1` (`x`)
    -> ON DELETE NO ACTION ON UPDATE CASCADE;

INSERT INTO `Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

table2` Vmysql>
mysql> INSERT INTO `table2` VALUES (1);

DELETE Query OK, 1 row affected (0.00 sec)

IGmysql>
mysql> DELETE IGNORE FROM table1 WHERE x=1; -- Causes the connection to hang.

Query OK, 1 row affected (0.01 sec)

mysql>

Similar result obtained with 5.0.21 and Linux. Here is what show innodb status prints:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
060406  8:42:10 Transaction:
TRANSACTION 0 782, ACTIVE 0 sec, process no 7103, OS thread id 2720164784 updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 1, query id 9 localhost jplindst updating
DELETE IGNORE FROM table1 WHERE x=1
Foreign key constraint fails for table `test/table2`:
,
  CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`test`) REFERENCES `table1` (`x`) ON DELETE NO ACTION ON UPDATE CASCADE
Trying to delete in parent table, in index `PRIMARY` tuple:
DATA TUPLE: 3 fields;
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 00000000030e; asc       ;; 2: len 7; hex 00000000320629; asc     2 );;

But in child table `test/table2`, in index `PRIMARY`, there is a record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 00000000030d; asc       ;; 2: len 7; hex 800000002d0110; asc     -  ;;

Regards,
    Jan

gdb output while running DELETE INGNORE

Attachment: gdb_test.txt (text/plain), 20.06 KiB.

Tested provided test case using a gdb with a breakpoint inside InnoDB (see gdb output obove). However, hang happens on  handle_one_connection (arg=0x8c99688) at sql_parse.cc at line 1159          if (do_command(thd)). and if you see where there it is in line 1481  if ((packet_length=my_net_read(net)) == packet_error).

Regards,
    Jan

Jan,

does 5.0.21 hang? It did not hang in Miguel's printout?

If there really is a hang, the bug looks like MySQL fails to send to the client a confirmation that the query has ended.

Regards,

Heikki

MySQL 5.0.21 does hang in my Linux:

plindst@innobase06:~$ mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.21-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `table1` (
    ->   `x` int(11) NOT NULL,
    ->   PRIMARY KEY  (`x`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0,07 sec)

mysql>
mysql> INSERT INTO `table1` VALUES (1);
Query OK, 1 row affected (0,00 sec)

mysql>
mysql> CREATE TABLE `table2` (
    ->   `test` int(11) NOT NULL,
    ->   PRIMARY KEY  (`test`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0,06 sec)

mysql>
mysql> ALTER TABLE `table2`
    ->   ADD CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`test`) REFERENCES
    -> `table1` (`x`) ON DELETE NO ACTION ON UPDATE CASCADE;
Query OK, 0 rows affected (0,08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO `table2` VALUES (1);
Query OK, 1 row affected (0,00 sec)

mysql>
mysql> DELETE IGNORE FROM table1 WHERE x=1; /* HANGS */

And no error messages are printed to mysqld log:

jplindst@innobase06:~/svn/5.0/sql$ ./mysqld
InnoDB: The first specified data file /home/jplindst/sqldata/ibdata1 did not exist:
InnoDB: a new database to be created!
060406  9:18:48  InnoDB: Setting file /home/jplindst/sqldata/ibdata1 size to 20 MB
InnoDB: Database physically writes the file full: wait...
060406  9:18:50  InnoDB: Log file /home/jplindst/sqldata/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /home/jplindst/sqldata/ib_logfile0 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
060406  9:18:56  InnoDB: Log file /home/jplindst/sqldata/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /home/jplindst/sqldata/ib_logfile1 size to 128 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
060406  9:19:03  InnoDB: Started; log sequence number 0 0
060406  9:19:03 [Note] ./mysqld: ready for connections.
Version: '5.0.21-debug-log'  socket: '/home/jplindst/bugsocket'  port: 3306  Source distribution

show innodb status says:

=====================================
060406  9:22:18 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 8, signal count 8
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 17, OS waits 8; RW-excl spins 0, OS waits 0
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060406  9:19:18 Transaction:
TRANSACTION 0 782, ACTIVE 0 sec, process no 7408, OS thread id 2719763376 updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 1, query id 8 localhost jplindst updating
DELETE IGNORE FROM table1 WHERE x=1
Foreign key constraint fails for table `test/table2`:
,
  CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`test`) REFERENCES `table1` (`x`) ON DELETE NO ACTION ON UPDATE CASCADE
Trying to delete in parent table, in index `PRIMARY` tuple:
DATA TUPLE: 3 fields;
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 00000000030e; asc       ;; 2: len 7; hex 00000000320629; asc     2 );;

But in child table `test/table2`, in index `PRIMARY`, there is a record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 00000000030d; asc       ;; 2: len 7; hex 800000002d0110; asc     -  ;;

------------
TRANSACTIONS
------------
Trx id counter 0 785
Purge done for trx's n:o < 0 784 undo n:o < 0 0
History list length 4
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 7408, OS thread id 2719562672
MySQL thread id 2, query id 14 localhost jplindst
show innodb status
---TRANSACTION 0 782, not started, process no 7408, OS thread id 2719763376
MySQL thread id 1, query id 8 localhost jplindst
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
0 OS file reads, 336 OS file writes, 53 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1106407, used cells 0, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 59157
Log flushed up to   0 59157
Last checkpoint at  0 59157
0 pending log writes, 0 pending chkp writes
27 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 303243934; in additional pool allocated 1419008
Buffer pool size   16384
Free buffers       16192
Database pages     191
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 0, created 195, written 229
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 7408, id 2736892848, state: waiting for server activity
Number of rows inserted 2, updated 0, deleted 0, read 1
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Regards,
    Jan

Heikki,

It hangs to me on 5.0.21 on Linux. Which not hangs is 5.1 server.

This bug does not seem to be inside of InnoDB. InnoDB correctly returns a error to the server which then
hangs. See e.g. gdb output provided. Changing category to server and unassigning this bug from me.

Regards,
    Jan

I am not sure if this is the same bug that I am experiencing but it seems so. I use DELETE IGNORE ... query on a table that has ON DELETE RESTRICT constraints set to it by other tables and then I would (can't really test as server hangs here) check AffectedRows to see whether I was able to delete a record or not and inform the user accordingly. Is there a workaround for this? Maybe a way to see how many rows would get affected if the query was executed?

Adding 5.1.12, as it hangs too.

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/14074

ChangeSet@1.2353, 2006-10-20 18:39:03+04:00, kroki@moonlight.intranet +3 -0
  BUG#18819: DELETE IGNORE hangs on foreign key parent delete
  
  If the error happens during DELETE IGNORE, nothing could be send to the
  client, thus leaving it frozen expecting the reply.
  
  The problem was that if some error occurred, it wouldn't be reported to
  the client because of IGNORE, but neither success would be reported.
  
  MySQL 4.1 would not freeze the client, but will report
  
    ERROR 1105 (HY000): Unknown error
  
  instead, which is also a bug.
  
  The solution is to report success if we are in DELETE IGNORE and some
  non-fatal error has happened.

Reviewed via email and skype.

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/14381

ChangeSet@1.2554, 2006-10-25 19:53:26+04:00, kroki@moonlight.intranet +3 -0
  BUG#18819: DELETE IGNORE hangs on foreign key parent delete
  
  If the error happens during DELETE IGNORE, nothing could be send to the
  client, thus leaving it frozen expecting the reply.
  
  The problem was that if some error occurred, it wouldn't be reported to
  the client because of IGNORE, but neither success would be reported.
  
  MySQL 4.1 would not freeze the client, but will report
  
    ERROR 1105 (HY000): Unknown error
  
  instead, which is also a bug.
  
  The solution is to report success if we are in DELETE IGNORE and some
  non-fatal error has happened.

Queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.

Noted in 4.1.22, 5.0.30 (not 5.0.29), 5.1.13 changelogs.

6.0.3-alpha-community has this bug also

I want to delete ignore all session numbers in the session table that are not referenced by a user in the user table.

6.0.4-alpha-community also (windows)