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 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.6.4m7 OS:Any
Assigned to: 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.