Bug #47274 assert in open_table on CREATE TABLE <already existing>
Submitted: 11 Sep 2009 16:43 Modified: 18 Dec 2009 13:23
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: locking
Triage: Triaged: D1 (Critical)

[11 Sep 2009 16:43] Matthias Leich
Description:
My script:
----------
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 ( f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1));
REPLACE DELAYED t1 (f1) VALUES (5);
# <--- The next statement gets an assertion in open_table
--error ER_TABLE_EXISTS_ERROR
CREATE TABLE t1 AS SELECT 1 AS f1;

DROP TABLE t1;

Backtrace on mysql-next-bugfixing:
----------------------------------
Thread 1 (process 974):
#0  0x00007fdd25766ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b58068 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  0x00007fdd246625c5 in raise () from /lib64/libc.so.6
#5  0x00007fdd24663bb3 in abort () from /lib64/libc.so.6
#6  0x00007fdd2465b1e9 in __assert_fail () from /lib64/libc.so.6
#7  0x000000000075cfae in open_table (thd=0x166e808, table_list=0x160ba90, mem_root=0x40f73160, ot_ctx=0x40f731f0, flags=32) at sql_base.cc:2864
#8  0x000000000075db23 in open_tables (thd=0x166e808, start=0x40f73290, counter=0x40f732d0, flags=32, prelocking_strategy=0x40f73310) at sql_base.cc:3954
#9  0x000000000075e42f in open_and_lock_tables_derived (thd=0x166e808, tables=0x160ba90, derived=true, flags=32, prelocking_strategy=0x40f73310) at sql_base.cc:4667
#10 0x000000000070d34c in open_and_lock_tables_derived (thd=0x166e808, tables=0x160ba90, derived=true, flags=32) at ../mysql_priv.h:1525
#11 0x0000000000702f50 in mysql_execute_command (thd=0x166e808) at sql_parse.cc:2633
#12 0x000000000070a36f in mysql_parse (thd=0x166e808, inBuf=0x160b9a0 "CREATE TABLE t1 AS SELECT 1 AS f1", length=33, found_semicolon=0x40f74f20) at sql_parse.cc:5941
#13 0x000000000070af8d in dispatch_command (command=COM_QUERY, thd=0x166e808, packet=0x1607949 "CREATE TABLE t1 AS SELECT 1 AS f1", packet_length=33) at sql_parse.cc:1062
#14 0x000000000070c431 in do_command (thd=0x166e808) at sql_parse.cc:744
#15 0x00000000006f99bf in handle_one_connection (arg=0x166e808) at sql_connect.cc:1163
#16 0x00007fdd25762040 in start_thread () from /lib64/libpthread.so.0
#17 0x00007fdd2470308d in clone () from /lib64/libc.so.6
#18 0x0000000000000000 in ?? ()

Observation:
------------
mysql-5.1-bugteam late August 2009 shows no assertion

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

How to repeat:
See above
[14 Sep 2009 13:34] Bugs System
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/83191

2817 Jon Olav Hauglid	2009-09-14
      Bug #47274 assert in open_table on CREATE TABLE <already existing>
      
      The problem was an assertion during execution of CREATE TABLES. 
      This assertion would occur if INSERT DELAYED or REPLACE DELAYED
      were used to update a table containing an AUTO_INCREMENT column
      and if the inserted row had a user-supplied value for that column.
      Any CREATE TABLE statement (including CREATE TABLE SELECT and
      CREATE TABLE LIKE) trying to create the same table and 
      which followed the INSERT/REPLACED would cause the assertion.
      
      The problem was only noticeable on debug builds of the server
      and not present in the mysql-5.1 tree.
      
      The cause of the problem was that the code for delayed insert did
      not properly reset the TABLE->auto_increment_if_null flag after insert.
      The flag is used to indicate that a non-null value of an auto_increment field
      has been provided by the user or retrieved from a current record.
      Open_tables() contains an assertion that tests this flag, and this
      was triggered by CREATE TABLE.
      
      This patch fixes the problem by resetting the auto_increment_if_null
      field to FALSE once INSERT/REPLACE DELAYED has updated the table, 
      similar to what is done already for regular INSERT statements.
      
      Test case added to delayed.test.
[25 Sep 2009 11:03] Bugs System
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/84614

2841 Jon Olav Hauglid	2009-09-25
      Bug #47274 assert in open_table on CREATE TABLE <already existing>
      
      The problem was an assertion during execution of CREATE TABLES. 
      This assertion would occur if INSERT DELAYED or REPLACE DELAYED
      were used to update a table containing an AUTO_INCREMENT column
      and if the inserted row had a user-supplied value for that column.
      Any CREATE TABLE statement (including CREATE TABLE SELECT and
      CREATE TABLE LIKE) trying to create the same table and 
      which followed the INSERT/REPLACED would cause the assertion.
      
      The problem was only noticeable on debug builds of the server
      and not present in the mysql-5.1 tree.
      
      The cause of the problem was that the code for delayed insert did
      not properly reset the TABLE->auto_increment_if_null flag after insert.
      The flag is used to indicate that a non-null value of an auto_increment field
      has been provided by the user or retrieved from a current record.
      Open_tables() contains an assertion that tests this flag, and this
      was triggered by CREATE TABLE.
      
      This patch fixes the problem by resetting the auto_increment_if_null
      field to FALSE once INSERT/REPLACE DELAYED has updated the table, 
      similar to what is done already for regular INSERT statements.
      
      Test case added to delayed.test.
[25 Sep 2009 11:04] Jon Olav Hauglid
Pushed to mysql-6.0-codebase-bugfixing (version 6.0.14-alpha)
[30 Sep 2009 8:17] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090927203924-087s36mrs0uxepwb) (merge vers: 6.0.14-alpha) (pib:11)
[1 Oct 2009 17:28] Paul Dubois
Noted in 6.0.14 changelog.

An assertion could be raised for CREATE TABLE if there was a pending
INSERT DELAYED or REPLACE DELAYED for the same table.

Setting NDI pending push into 5.4.x.
[14 Oct 2009 12:52] Jon Olav Hauglid
Pushed to mysql-next-mr-runtime (version 5.5.0)
[14 Oct 2009 17:02] Paul Dubois
Noted in 5.5.0 changelog.
[3 Nov 2009 7:16] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)
[4 Nov 2009 9:26] Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)
[5 Nov 2009 1:59] Paul Dubois
Noted in 5.1.41 changelog.
[11 Nov 2009 6:54] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 2009 7:02] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105092041-sp6eyod7sdlfuj3b) (merge vers: 5.5.0-beta) (pib:13)
[12 Nov 2009 8:21] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)
[18 Dec 2009 10:38] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:54] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:09] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:23] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[18 Dec 2009 13:23] MC Brown
Already noted in earlier changelogs.