MySQL Bugs: #47107: assert in notify_shared_lock on incorrect CREATE TABLE , HANDLER

Bug #47107	assert in notify_shared_lock on incorrect CREATE TABLE , HANDLER
Submitted:	3 Sep 2009 17:58	Modified:	7 Mar 2010 1:55
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.4	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	handler, mdl

Description:
CREATE TABLE t1  ( f1 integer );
HANDLER t1 OPEN AS A;
CREATE TABLE t1 AS SELECT 1 FROM t1_not_exists;

backtrace on mysql-next-bugfixing 2009-09-03:
---------------------------------------------
Thread 1 (process 20166):
#0  0x00007f4aed27ace6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b57fec in my_write_core (sig=6) at stacktrace.c:309
#2  0x00000000006efb31 in handle_segfault (sig=6) at mysqld.cc:2738
#3  <signal handler called>
#4  0x00007f4aec1765c5 in raise () from /lib64/libc.so.6
#5  0x00007f4aec177bb3 in abort () from /lib64/libc.so.6
#6  0x00007f4aec16f1e9 in __assert_fail () from /lib64/libc.so.6
#7  0x00000000009222bd in notify_shared_lock (thd=0x166e808, conflicting_ticket=0x16173d0) at mdl.cc:788
#8  0x0000000000922abc in MDL_context::acquire_exclusive_locks (this=0x166e8e0, mdl_requests=0x40162b60) at mdl.cc:901
#9  0x0000000000922f6f in MDL_context::acquire_exclusive_lock (this=0x166e8e0, mdl_request=0x160be00) at mdl.cc:804
#10 0x00000000007581a8 in open_table_get_mdl_lock (thd=0x166e808, table_list=0x160baa8, mdl_request=0x160be00, ot_ctx=0x401631f0, flags=32) at sql_base.cc:2313
#11 0x000000000075c71e in open_table (thd=0x166e808, table_list=0x160baa8, mem_root=0x40163160, ot_ctx=0x401631f0, flags=32) at sql_base.cc:2588
#12 0x000000000075db23 in open_tables (thd=0x166e808, start=0x40163290, counter=0x401632d0, flags=32, prelocking_strategy=0x40163310) at sql_base.cc:3954
#13 0x000000000075e42f in open_and_lock_tables_derived (thd=0x166e808, tables=0x160baa8, derived=true, flags=32, prelocking_strategy=0x40163310) at sql_base.cc:4667
#14 0x000000000070d34c in open_and_lock_tables_derived (thd=0x166e808, tables=0x160baa8, derived=true, flags=32) at ../mysql_priv.h:1525
#15 0x0000000000702f50 in mysql_execute_command (thd=0x166e808) at sql_parse.cc:2633
#16 0x000000000070a36f in mysql_parse (thd=0x166e808, inBuf=0x160b9a0 "CREATE TABLE t1 AS SELECT 1 FROM t1_not_exists", length=46, found_semicolon=0x40164f20) at sql_parse.cc:5941
#17 0x000000000070af8d in dispatch_command (command=COM_QUERY, thd=0x166e808, packet=0x1607949 "CREATE TABLE t1 AS SELECT 1 FROM t1_not_exists", packet_length=46) at sql_parse.cc:1062
#18 0x000000000070c431 in do_command (thd=0x166e808) at sql_parse.cc:744
#19 0x00000000006f99bf in handle_one_connection (arg=0x166e808) at sql_connect.cc:1163
#20 0x00007f4aed276040 in start_thread () from /lib64/libpthread.so.0
#21 0x00007f4aec21708d in clone () from /lib64/libc.so.6
#22 0x0000000000000000 in ?? ()

The assert disappears if I remove the:
   HANDLER t1 OPEN AS A;
and I get a correct
   query 'CREATE TABLE t1 AS SELECT 1 FROM t1_not_exists' failed:
   1146: Table 'test.t1_not_exists' doesn't exist

My environment:
- mysql-next-bugfixing 2009-09-03
- ./BUILD/compile-pentiun64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
See above

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/82906

2814 Jon Olav Hauglid	2009-09-10
      Bug #47107 assert in notify_shared_lock on incorrect CREATE TABLE , HANDLER
      
      The problem here was that the HANDLER was not closed by CREATE TABLE
      before CREATE tried to open and lock the table. Acquire exclusive MDL
      lock on the table to be created would therefore fail since HANDLER
      already had a shared MDL lock. This triggered an assert since the 
      HANDLER and CREATE statements came from the same thread (self-deadlock).
      
      This patch resolves the issue by closing any open HANDLERs on tables
      to be created by CREATE TABLE, similar to what is already done for 
      DROP and ALTER TABLE.
      
      Test case added to create.test.

Approved with minor comments sent by e-mail.

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/83136

2815 Jon Olav Hauglid	2009-09-14
      Bug #47107 assert in notify_shared_lock on incorrect CREATE TABLE , HANDLER
      
      Attempts to create a table (using CREATE TABLE, CREATE TABLE LIKE or
      CREATE TABLE SELECT statements) which already existed and was opened
      by the same connection through HANDLER statement, led to a stalled
      connection (for production builds of the server) or to the server being
      aborted due to an assertion failure (for debug builds of the server).
      
      This problem was introduced by the new implementation of a metadata
      locking subsystem and didn't affect earlier versions of the server.
      
      The cause of the problem was that the HANDLER was not closed by CREATE TABLE
      before CREATE tried to open and lock the table. Acquiring an exclusive MDL
      lock on the table to be created would therefore fail since HANDLER
      already had a shared MDL lock. This triggered an assert as the 
      HANDLER and CREATE statements came from the same thread (self-deadlock).
      
      This patch resolves the issue by closing any open HANDLERs on tables
      to be created by CREATE TABLE, similar to what is already done for 
      DROP and ALTER TABLE.
      
      Test case added to create.test.

Pushed to mysql-next-bugfixing (5.4.4-alpha)

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/83155

2816 Tor Didriksen	2009-09-14
      Bug#47107
      
      Add missing line in previous change set.
     @ mysql-test/r/create.result
        Bug#47107
        
        Add missing line in previous change set.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090915134838-5nj3ycjfsqc2vr2f) (version source revid:alik@sun.com-20090914161222-1gvwajrhv7zv39mz) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.4.4 changelog.

A CREATE TABLE attempt for a table that had been opened with HANDLER
caused an assertion failure because CREATE TABLE did not close any
open handlers for the table.

Pushed to mysql-next-4284 (5.6.0-beta).

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100216101445-2ofzkh48aq2e0e8o) (version source revid:kostja@sun.com-20091211154405-c9yhiewr9o5d20rq) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100216101208-33qkfwdr0tep3pf2) (version source revid:kostja@sun.com-20091210084103-l4f8u62u4evoy3dc) (pib:16)

Since this bug is not repeatable in any publicly available tree with version < 6.0 there is nothing to document. So I am simply closing this bug.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100216221947-luyhph0txl2c5tc8) (merge vers: 5.5.99-m3) (pib:16)

No changelog entry needed.