Bug #39483 InnoDB hang on adaptive hash because of out of order ::open() call by MySQL

• Submitted: 16 Sep 2008 15:29
• Modified: 19 Jun 2010 0:16
• Reporter: Inaam Rana
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0, 5.1
• OS: Any
• Assigned to: Inaam Rana
• CPU Architecture: Any

[16 Sep 2008 15:29] Inaam Rana

Description:
This is an offshoot of Bug#20358 which is a bundle of hangs for various reason. This bug deals with ::open() bug. The symptoms would be following:

1) InnoDB hanging on adaptive hash index with non-zero (typically 1) reader count.

<snip>
--Thread 1151424864 has waited at btr0sea.c line 995 for 265.00 seconds the semaphore:
X-lock on RW-latch at 0x2aaaaf3970b8 created in file btr0sea.c line 139
a writer (thread id 1151424864) has reserved it in mode  wait exclusive
number of readers 1, waiters flag 1
</snip>

2) Find the thread holding the hash index latch by searching for "holds adaptive hash latch" in .err

<snip>
---TRANSACTION 0 11972017, ACTIVE 329 sec, process no 32401, OS thread id 1164941664, thread declared inside InnoDB 500
mysql tables in use 6, locked 0
, holds adaptive hash latch
MySQL thread id 54162, query id 45172395 172.16.12.6 ofmg Copying to tmp table
SELECT  federation.name,  federation.abbreviation,  federation.type,  federation.colour_1,  federation.colour_2,  federation.colour_3,  federation.date_created,  federation.membership_fee,  federation.number_of_teams_joining,  federation.number_of_teams_joined,  IF(current_federation_member.organizer=1,federation.description,''),  federation.media_money_negotiated,  federation.reputation,  federation.locked,  founder_user.id,  founder_user.name,  founder_team.name,  IF(current_federation_member.user_id IS NULL,0,current_federation_member.organizer),  IF(current_federation_member.user_id IS NUL
Trx read view will not see trx with id >= 0 11972018, sees < 0 11970906
</snip>

3)Note the OS thread id (1164941664) above and see if the same thread is waiting for dictionary mutex.

<snip>
--Thread 1164941664 has waited at dict0dict.c line 781 for 334.00 seconds the semaphore:
Mutex at 0x2aaaaf4858b8 created file dict0dict.c line 709, lock var 1
waiters flag 1
</snip>

If the above is true then we have hit ::open() bug.

Though MySQL should look into the real cause of calling ::open() while holding hash index latch, we can fix the hang by releasing the adaptive hash latch unconditionally in ::open()

How to repeat:
see above.

[12 Mar 2009 21:48] Paul DuBois

Noted in 5.0.76, 5.1.31, 6.0.10 changelogs.

InnoDB could hang trying to open an adaptive hash index.

[5 May 2010 15:11] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 16:02] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 6:10] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:38] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:06] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 15:35] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[17 Jun 2010 12:15] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:02] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:43] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)