Bug #64274 MySQL thread was suspended when execute UPDATE query

• Submitted: 9 Feb 2012 9:42
• Modified: 9 Apr 2012 16:29
• Reporter: Dariusz Jagodzik
• Status: Closed
• Category: MySQL Server: DML
• Severity: S1 (Critical)
• Version: 5.6.4m7
• OS: Any
• CPU Architecture: Any
• Tags: fk, foreign keys, regression, suspended, UPDATE

[9 Feb 2012 9:42] Dariusz Jagodzik

Description:
I have DB and when I try update row in table, mysql thread is suspended. This is OK in: MySQL-5.5.20 and MySQL-5.1.xx

I try 5.6.4m7 Linux(debian) and Windows version

How to repeat:
load sql dump from:
http://wklej.org/hash/07e1efaea79/

and try query:

UPDATE `offer` SET `status`='101' WHERE `idOffer`=1 LIMIT 1;

[9 Feb 2012 10:12] Valeriy Kravchuk

Thank you for the bug report. Verified just as described on Windows XP. UPDATE hangs like this:

mysql> show table status like 'offer'\G
*************************** 1. row ***************************
           Name: offer
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: 2
    Create_time: 2012-02-09 12:08:35
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> show processlist;
+----+------+----------------+-----------+---------+------+----------+----------
---------------------------------------------------+
| Id | User | Host           | db        | Command | Time | State    | Info
                                                   |
+----+------+----------------+-----------+---------+------+----------+----------
---------------------------------------------------+
|  1 | root | localhost:3594 | exampledb | Query   |  185 | updating | UPDATE `o
ffer` SET `status`='101' WHERE `idOffer`=1 LIMIT 1 |
|  2 | root | localhost:3618 | exampledb | Query   |    0 | init     | show proc
esslist                                            |
+----+------+----------------+-----------+---------+------+----------+----------
---------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.4-m7  |
+-----------+
1 row in set (0.05 sec)

[9 Apr 2012 16:29] Paul DuBois

Noted in 5.6.6 changelog.

Full-text search in InnoDB tried to follow foreign key references
without keeping track of which ones it had already seen. With
circular and other complex setups, this could loop forever or a very
long time, leading to the appearance of the query thread hanging.

[24 Dec 2012 9:20] Erlend Dahl

Bug#65015 was marked as a duplicate.