Bug #38821 Assert table->auto_increment_field_not_null failed in open_table()
Submitted: 15 Aug 2008 12:00 Modified: 17 Oct 2008 18:15
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1-bzr/5.0/6.0 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: regression
Triage: D1 (Critical)

[15 Aug 2008 12:00] Philip Stoev
Description:
When executing a non-concurrent workload involving creating partitioned tables and stored procedures and triggers that operate on them, mysqld asserted as follows:

#8  0x003df57e in __assert_fail () from /lib/libc.so.6
#9  0x082a91bb in open_table (thd=0xa33d808, table_list=0xa383848,
    mem_root=0xb2a4c14c, refresh=0xb2a4c18f, flags=0) at sql_base.cc:2964
#10 0x082a9cc3 in open_tables (thd=0xa33d808, start=0xb2a4c204,
    counter=0xb2a4c1f0, flags=0) at sql_base.cc:4515
#11 0x082aa4b4 in open_and_lock_tables_derived (thd=0xa33d808,
    tables=0xa383848, derived=true) at sql_base.cc:4915
#12 0x08266e2b in open_and_lock_tables (thd=0xa33d808, tables=0xa383848)
    at mysql_priv.h:1531
#13 0x08259702 in execute_sqlcom_select (thd=0xa33d808, all_tables=0xa383848)
    at sql_parse.cc:4730
#14 0x0825b655 in mysql_execute_command (thd=0xa33d808) at sql_parse.cc:2073
#15 0x083fcc47 in sp_instr_stmt::exec_core (this=0xa383a30, thd=0xa33d808,
    nextp=0xb2a4d1dc) at sp_head.cc:2892
#16 0x083fce9b in sp_lex_keeper::reset_lex_and_exec_core (this=0xa383a58,
---Type <return> to continue, or q <return> to quit---
    thd=0xa33d808, nextp=0xb2a4d1dc, open_tables=false, instr=0xa383a30)
    at sp_head.cc:2721
#17 0x08402d35 in sp_instr_stmt::execute (this=0xa383a30, thd=0xa33d808,
    nextp=0xb2a4d1dc) at sp_head.cc:2835
#18 0x083ff0cf in sp_head::execute (this=0xa382888, thd=0xa33d808)
    at sp_head.cc:1245
#19 0x083ffd71 in sp_head::execute_procedure (this=0xa382888, thd=0xa33d808,
    args=0xa33ee3c) at sp_head.cc:1970
#20 0x08262198 in mysql_execute_command (thd=0xa33d808) at sql_parse.cc:4119
#21 0x08264375 in mysql_parse (thd=0xa33d808, inBuf=0xa33f660 "CALL r",
    length=6, found_semicolon=0xb2a4e25c) at sql_parse.cc:5656
#22 0x08264f70 in dispatch_command (command=COM_QUERY, thd=0xa33d808,
    packet=0xa34f749 "CALL r", packet_length=6) at sql_parse.cc:1137
#23 0x0826612e in do_command (thd=0xa33d808) at sql_parse.cc:794
#24 0x082535ae in handle_one_connection (arg=0xa33d808) at sql_connect.cc:1115
#25 0x0057d32f in start_thread () from /lib/libpthread.so.0
#26 0x0049a27e in clone () from /lib/libc.so.6

The assertion is here:

2959      table->status=STATUS_NO_RECORD;
2960      table->insert_values= 0;
2961      table->fulltext_searched= 0;
2962      table->file->ft_handler= 0;
2963      /* Catch wrong handling of the auto_increment_field_not_null. */
2964      DBUG_ASSERT(!table->auto_increment_field_not_null); <<<<<<<<< HERE
2965      table->auto_increment_field_not_null= FALSE;
2966      if (table->timestamp_field)
2967        table->timestamp_field_type= table->timestamp_field->get_auto_set_type();
2968      table->pos_in_table_list= table_list;

(gdb) print table->auto_increment_field_not_null
$2 = 1 '\001'

How to repeat:
A simplifed test case will follow shortly.
[15 Aug 2008 12:44] Philip Stoev
The only queries that are needed are three CREATE IF NOT EXISTS ... SELECT statements using AUTO_INCREMENT:

CREATE TABLE t1 (
        pk INTEGER AUTO_INCREMENT ,
        int_nokey INTEGER NOT NULL,
        int_key INTEGER NOT NULL,
        PRIMARY KEY (pk),
        KEY (int_key)
);

INSERT IGNORE INTO t1 ( int_key, int_nokey ) VALUES ( '5', '8' );

CREATE TABLE IF NOT EXISTS t2 (
        `pk` INTEGER NOT NULL AUTO_INCREMENT,
        PRIMARY KEY ( `pk` )
) SELECT `pk` FROM t1;

CREATE TABLE IF NOT EXISTS t2 (
        `pk` INTEGER NOT NULL AUTO_INCREMENT,
        PRIMARY KEY ( `pk` )
) SELECT `pk` FROM t1;

CREATE TABLE IF NOT EXISTS t2 (
        `pk` INTEGER NOT NULL AUTO_INCREMENT,
        PRIMARY KEY ( `pk` )
) SELECT `pk` FROM t1;
[15 Aug 2008 13:05] Miguel Solorzano
Thank you for the bug report. Repeatable on bzr 5.0/5.1/6.0. Not repeatable on 5.0.22 debug released version.

terribly wrong...
009B9434    mysqld.exe!_NMSG_WRITE()[crt0msg.c:195]
009A79BA    mysqld.exe!abort()[abort.c:44]
009A5122    mysqld.exe!_assert()[assert.c:306]
00609B1B    mysqld.exe!open_table()[sql_base.cc:2964]
0060CABF    mysqld.exe!open_tables()[sql_base.cc:4515]
0060DB98    mysqld.exe!open_and_lock_tables_derived()[sql_base.cc:4915]
005D8FB2    mysqld.exe!open_and_lock_tables()[mysql_priv.h:1531]
0066C707    mysqld.exe!mysql_execute_command()[sql_parse.cc:2452]
00675041    mysqld.exe!mysql_parse()[sql_parse.cc:5656]
00669EDE    mysqld.exe!dispatch_command()[sql_parse.cc:1137]
006695C7    mysqld.exe!do_command()[sql_parse.cc:794]
00778B64    mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
0083E4B6    mysqld.exe!pthread_start()[my_winthread.c:85]
009AD567    mysqld.exe!_threadstart()[thread.c:196]
7C80B713    kernel32.dll!GetModuleFileNameA()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 01EEE730=CREATE TABLE IF NOT EXISTS t2 (
        `pk` INTEGER NOT NULL AUTO_INCREMENT,
        PRIMARY KEY ( `pk` )
) SELECT `pk` FROM t1
thd->thread_id=1
thd->killed=NOT_KILLED
[26 Aug 2008 11:46] 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/52548

2721 Ramil Kalimullin	2008-08-26
      Fix for bug#38821: Assert table->auto_increment_field_not_null failed 
      in open_table()
      
      Problem: repeating "CREATE... ( AUTOINCREMENT) ... SELECT" may lead to
      an assertion failure.
      
      Fix: reset table->auto_increment_field_not_null after each record 
      writing.
[3 Sep 2008 10:17] 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/53147

2681 Ramil Kalimullin	2008-09-03
      Fix for bug#38821: Assert table->auto_increment_field_not_null failed 
      in open_table()
      
      Problem: repeating "CREATE... ( AUTOINCREMENT) ... SELECT" may lead to
      an assertion failure.
      
      Fix: reset table->auto_increment_field_not_null after each record 
      writing.
[15 Sep 2008 8:11] Bugs System
Pushed into 5.0.70  (revid:ramil@mysql.com-20080903101719-64hgxtnfm157j1n6) (version source revid:kgeorge@mysql.com-20080910094058-fygie2nur8py7y8j) (pib:3)
[15 Sep 2008 8:31] Bugs System
Pushed into 5.1.29  (revid:ramil@mysql.com-20080903101719-64hgxtnfm157j1n6) (version source revid:kgeorge@mysql.com-20080910094421-1i1kxv3n1bxskiqa) (pib:3)
[16 Sep 2008 14:24] Paul Dubois
Noted in 5.0.70, 5.1.29 changelogs.

Repeated CREATE TABLE ... SELECT statements, where the created table
contained an AUTO_INCREMENT column, could lead to an assertion
failure.

Setting to NDI pending push into 6.0.x
[1 Oct 2008 16:00] Bugs System
Pushed into 5.1.29  (revid:ramil@mysql.com-20080903101719-64hgxtnfm157j1n6) (version source revid:kgeorge@mysql.com-20080910094421-1i1kxv3n1bxskiqa) (pib:4)
[1 Oct 2008 17:16] Paul Dubois
Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:45] Bugs System
Pushed into 6.0.8-alpha  (revid:ramil@mysql.com-20080903101719-64hgxtnfm157j1n6) (version source revid:kpettersson@mysql.com-20080911114255-81pt7q1uvl1fkojq) (pib:5)
[17 Oct 2008 18:15] Paul Dubois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:05] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:ramil@mysql.com-20080903101719-64hgxtnfm157j1n6) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:23] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:ramil@mysql.com-20080903101719-64hgxtnfm157j1n6) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:49] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:ramil@mysql.com-20080903101719-64hgxtnfm157j1n6) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)