MySQL Bugs: #45691: InnoDB hangs getting foreign key info

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:	None
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

Description:
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 |

SHOW INNODB STATUS:
---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

It looks like the cause is the first critical bug listed here as
part of the PHCO_38050 patch set:
http://www13.itrc.hp.com/service/patch/patchDetail.do?BC=main|patchDetail{PHCO_38050,{hpux...

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:
PHCO_38050: ABORT HANG
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.

I would recommend that ALL of the following patchsets from HP be 
installed:
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