MySQL Bugs: #25164: create table `a` as select * from `A` hangs

Bug #25164	create table `a` as select * from `A` hangs
Submitted:	19 Dec 2006 8:04	Modified:	9 Oct 2007 17:24
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	4.1.21, 4.1BK	OS:	Linux (suse 9.3 x86)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	bfsm_2006_12_21

Description:
when issueing a command in the form:

create table `b` as select * from `A`; #works
create table `a` as select * from `A`; #hangs

the second command hangs, and the query remains locked indefinately on 4.1

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 6
   User: root
   Host: 192.168.250.3:6702
     db: test
Command: Query
   Time: 413
  State: Locked
   Info: create table `a` as select * from `A`

5.0 immediately errors out, with:

mysql> create table `a` as select * from `A`;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.  

How to repeat:
set storage_engine=innodb;
drop table if exists `a`;
drop table if exists `A`;
drop table if exists `b`;
create table `A`(`c` int);
insert into `A` (`c`) values (0);
create table `b` as select * from `A`; #works
create table `a` as select * from `A`; #hangs

Suggested fix:
either error immediately, or don't hang.

I changed the category to InnoDB, because MyISAM tables aren't affected.

It looks like MySQL is trying to lock the same table twice, or something like that:

(gdb) bt
#0  0xb7e86b64 in __pthread_sigsuspend () from /lib/libpthread.so.0
#1  0xb7e85728 in __pthread_wait_for_restart_signal ()
   from /lib/libpthread.so.0
#2  0xb7e830eb in pthread_cond_wait@GLIBC_2.0 () from /lib/libpthread.so.0
#3  0x083c1cbb in safe_cond_wait (cond=0x86f61fc, mp=0x86f68f4, 
    file=0x8485f2f "thr_lock.c", line=389) at thr_mutex.c:202
#4  0x083bffa9 in wait_for_lock (wait=0x86f6944, data=0x86f70d8, 
    in_wait_list=0 '\0') at thr_lock.c:389
#5  0x083c0839 in thr_lock (data=0x86f70d8, lock_type=TL_WRITE)
    at thr_lock.c:604
#6  0x083c16de in thr_multi_lock (data=0x86f74b4, count=1) at thr_lock.c:844
#7  0x08140c15 in mysql_lock_tables (thd=0x86c0508, tables=0xb60b93c8, 
    count=1, flags=2) at lock.cc:155
#8  0x081f1fc8 in create_table_from_items (thd=0x86c0508, 
    create_info=0x86c08d8, db=0x86c1b08 "test", name=<value optimized out>, 
    extra_fields=0x86c0814, keys=0x86c0808, items=0x86c069c, lock=0x86fa5d0)
    at sql_table.cc:1653
#9  0x0819dd96 in select_create::prepare (this=0x86fa568, values=@0x86c069c, 
    u=0x86c0550) at sql_insert.cc:1811
#10 0x0818c6f1 in JOIN::prepare (this=0x86fa5d8, 
    rref_pointer_array=0x86c0720, tables_init=0x86fa500, wild_num=1, 
    conds_init=0x0, og_num=0, order_init=0x0, group_init=0x0, 
    having_init=0x0, proc_param_init=0x0, select_lex_arg=0x86c0630, 
    unit_arg=0x86c0550) at sql_select.cc:409
#11 0x0819cd34 in mysql_select (thd=0x86c0508, rref_pointer_array=0x86c0720, 
    tables=0x86fa500, wild_num=1, fields=@0x86c069c, conds=0x0, og_num=0, 
    order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2424588800, result=0x86fa568, unit=0x86c0550, 
    select_lex=0x86c0630) at sql_select.cc:1694
#12 0x0819d2c2 in handle_select (thd=0x86c0508, lex=0x86c0544, 
    result=0x86fa568) at sql_select.cc:192
#13 0x0815b972 in mysql_execute_command (thd=0x86c0508) at sql_parse.cc:2558
#14 0x0815f4f6 in mysql_parse (thd=0x86c0508, 
    inBuf=0x86fa380 "create table `a` as select * from `A`", length=37)
    at sql_parse.cc:4374

(gdb) frame 8
#8  0x081f1fc8 in create_table_from_items (thd=0x86c0508, 
    create_info=0x86c08d8, db=0x86c1b08 "test", name=<value optimized out>, 
    extra_fields=0x86c0814, keys=0x86c0808, items=0x86c069c, lock=0x86fa5d0)
    at sql_table.cc:1653
(gdb) p table->table_name
$1 = 0x86cea90 "a"
(gdb) frame 9
#9  0x0819dd96 in select_create::prepare (this=0x86fa568, values=@0x86c069c, 
    u=0x86c0550) at sql_insert.cc:1811
(gdb) p name
$2 = 0x86fa3e0 "a"

I suppose that $2 should be "A".  Because I don't know this part of the MySQL code, I have no idea where the lower-case "a" comes from.

This is probably a MySQL server bug. InnoDB does not take care of MySQL table locks.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32789

ChangeSet@1.2498, 2007-08-20 23:11:08-03:00, davi@moksha.local +1 -0
  Bug#25164 create table `a` as select * from `A` hangs
  
  If the thr_multi_lock() detects a deadlock (or timeouts), the table handler (storage engine) lock must be unlocked. As the function comment indicates: each call to external_lock(F_[RD|WR]LOCK) is followed by a call to external_lock(F_UNLCK) and if it is not, it is a bug in MySQL.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33048

ChangeSet@1.2502, 2007-08-24 15:22:41-03:00, davi@moksha.local +3 -0
  Bug#25164 create table `a` as select * from `A` hangs
  
  The problem from a user's perspective: user creates table A, and then tries to CREATE TABLE a SELECT from A - and this causes a deadlock error, a hang, or fails with a debug assert, but only if the storage engine is InnoDB.
  
  The origin of the problem: InnoDB uses case-insensitive collation (system_charset_info) when looking up the internal table share, thus returning the same share for 'a' and 'A'.
  
  Cause of the user-visible behavior: since the same share is returned to SQL locking subsystem, it assumes that the same table is first locked (within the same session) for WRITE, and then for READ, and returns a deadlock error. However, the code is wrong in not properly cleaning up upon an error, leaving external locks in place, which leads to assertion failures and hangs.
  
  Fix that has been implemented: the SQL layer should properly propagate the deadlock error, cleaning up and freeing all resources.
  
  Further work towards a more complete solution: InnoDB should not use case-insensitive collation for table share hash if table names on disk honor the case.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33150

ChangeSet@1.2502, 2007-08-27 10:13:54-03:00, davi@moksha.local +3 -0
  Bug#25164 create table `a` as select * from `A` hangs
  
  The problem from a user's perspective: user creates table A, and then tries
  to CREATE TABLE a SELECT from A - and this causes a deadlock error, a hang,
  or fails with a debug assert, but only if the storage engine is InnoDB.
  
  The origin of the problem: InnoDB uses case-insensitive collation
  (system_charset_info) when looking up the internal table share, thus returning
  the same share for 'a' and 'A'.
  
  Cause of the user-visible behavior: since the same share is returned to SQL
  locking subsystem, it assumes that the same table is first locked (within the
  same session) for WRITE, and then for READ, and returns a deadlock error.
  However, the code is wrong in not properly cleaning up upon an error, leaving
  external locks in place, which leads to assertion failures and hangs.
  
  Fix that has been implemented: the SQL layer should properly propagate the
  deadlock error, cleaning up and freeing all resources.
  
  Further work towards a more complete solution: InnoDB should not use case
  insensitive collation for table share hash if table names on disk honor the case.

Approved over email.

Pushed in 5.0-runtime and 5.1-runtime

Pushed into 5.1.23-beta

Pushed into 5.0.50

Noted in 5.0.50, 5.1.23 changelog.

For InnoDB tables, CREATE TABLE a AS SELECT * FROM A would fail.