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:Microsoft Windows (Windows 2000/Linux)
Assigned to: Tomash Brechko CPU Architecture:Any

[5 Apr 2006 18:00] Jeremy Papp
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.
[5 Apr 2006 18:31] Miguel Solorzano
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>
[6 Apr 2006 5:45] Jan Lindström
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
[6 Apr 2006 5:55] Jan Lindström
gdb output while running DELETE INGNORE

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

[6 Apr 2006 6:03] Jan Lindström
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
[6 Apr 2006 6:12] Heikki Tuuri
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
[6 Apr 2006 6:23] Jan Lindström
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
[6 Apr 2006 12:02] Miguel Solorzano
Heikki,

It hangs to me on 5.0.21 on Linux. Which not hangs is 5.1 server.
[10 Apr 2006 5:52] Jan Lindström
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
[25 Apr 2006 9:08] Donatas Maciunas
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?
[20 Oct 2006 9:30] Tomash Brechko
Adding 5.1.12, as it hangs too.
[20 Oct 2006 14:41] Bugs System
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.
[25 Oct 2006 13:54] Konstantin Osipov
Reviewed via email and skype.
[25 Oct 2006 16:51] Bugs System
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.
[26 Oct 2006 9:21] Tomash Brechko
Queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.
[13 Nov 2006 4:26] Paul Dubois
Noted in 4.1.22, 5.0.30 (not 5.0.29), 5.1.13 changelogs.
[28 Mar 2008 1:52] gert cuykens
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.
[28 Mar 2008 5:05] gert cuykens
6.0.4-alpha-community also (windows)