Bug #45691 InnoDB hangs getting foreign key info
Submitted: 23 Jun 2009 20:40 Modified: 24 Jun 2009 17:04
Reporter: Matthew Lord Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.1.36 OS:HP/UX (11.31 on IA64)
Assigned to: CPU Architecture:Any
Tags: hang, hpux, IA64

[23 Jun 2009 20:40] Matthew Lord
InnoDB hangs when getting the foreign key info for a table.

mysql> show create table t1;
... # hang

Here's the related part of SHOW PROCESSLIST:
| 2 | root | localhost | mlord | Query | 315 | NULL | show create table t1 |

---TRANSACTION 0 2347, not started, OS thread id 11 getting info on foreign keys
MySQL thread id 2, query id 155 localhost root
show create table t1

gdb stack unwind for this thread:
Thread 11 (system thread 4503974):
#0 0xc00000000042d6d0:0 in __ksleep+0x30 () from /usr/lib/hpux64/libc.so.1
#1 0xc0000000001a85a0:0 in __mxn_sleep+0x1080 ()
from /usr/lib/hpux64/libpthread.so.1
#2 0xc0000000000e2130:0 in <unknown_procedure> + 0x1210 ()
from /usr/lib/hpux64/libpthread.so.1
#3 0xc0000000000df2a0:0 in pthread_cond_wait+0xe0 ()
from /usr/lib/hpux64/libpthread.so.1
#4 0x4000000000f41240:0 in os_event_wait_low () at os/os0sync.c:422
#5 0x4000000000faa5d0:0 in sync_array_wait_event () at sync/sync0arr.c:448
#6 0x4000000000fafa60:0 in mutex_spin_wait () at sync/sync0sync.c:594
#7 0x4000000000faf5f0:0 in mutex_enter_func ()
at ../../storage/innobase/include/sync0sync.ic:259
#8 0x4000000000fafad0:0 in mutex_enter_noninline () at sync/sync0sync.c:211
#9 0x4000000000e55fb0:0 in ha_innobase::get_foreign_key_create_info ()
at handler/ha_innodb.cc:6359
#10 0x4000000000c84850:0 in store_create_info () at sql_show.cc:1304
#11 0x4000000000c813a0:0 in mysqld_show_create () at sql_show.cc:625
#12 0x400000000087a770:0 in mysql_execute_command () at sql_parse.cc:2880
#13 0x400000000086dc90:0 in mysql_parse () at sql_parse.cc:5809
#14 0x40000000008685b0:0 in dispatch_command () at sql_parse.cc:1216
#15 0x4000000000870620:0 in do_command () at sql_parse.cc:857
#16 0x4000000000864d00:0 in handle_one_connection () at sql_connect.cc:1115
#17 0xc0000000000fb220:0 in __pthread_bound_body+0x190 ()
from /usr/lib/hpux64/libpthread.so.1

How to repeat:
mysql> create table t1 (id int, index (id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id int, t1_id int, index (t1_id), foreign key (t1_id) references t1(id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1;
... # hang
[24 Jun 2009 17:04] Matthew Lord
It looks like the cause is the first critical bug listed here as
part of the PHCO_38050 patch set:

Based on the debugging that I've done with gdb I can see that the
hangs are occurring in the pthread library when InnoDB is doing an
ABORT which fits perfectly with HP's description of the bug:
Pthread cancel signal is getting consumed by other
routine. This will prevent the delivery of the
signal to the target thread and the target thread
does not exit, subsequently the application hangs.
Multithreaded applications that access TLS data
types that are 16 bytes in size sometimes get
alignment trap.
[24 Jun 2009 17:13] Matthew Lord
I would recommend that ALL of the following patchsets from HP be 
PHKL_37465 1.0 thread level enhancements patch
PHKL_37457 1.0 thread level signal enhancements patch
PHCO_38050 1.0 pthread library cumulative patch

PHCO_38048 1.0 libc cumulative patch
PHCO_38044 1.0 libc cumulative header file patch
PHCO_37128 1.0 libc manpage cumulative patch