MySQL Bugs: #47919: assert in open_table during ALTER temporary table

Bug #47919	assert in open_table during ALTER temporary table
Submitted:	8 Oct 2009 15:09	Modified:	18 Dec 2009 13:11
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1,6.0.14	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	auto_increment, temporary

Description:
The assertion happens in sql_base.cc:2891 :
DBUG_ASSERT(!table->auto_increment_field_not_null);

My script:
----------
CREATE TABLE t1 (f1 INTEGER AUTO_INCREMENT, PRIMARY KEY (f1));
INSERT INTO t1 VALUES  (1) ;
CREATE TEMPORARY TABLE t2 LIKE t1;
INSERT INTO t2 (f1) SELECT f1 FROM t1; 
ALTER TABLE t2 COMMENT = 'ABC';
UPDATE t2 AS A , t1 B SET A.f1 = 2 , B.f1 = 9;
ALTER TABLE t2 COMMENT = 'DEF';
   <--- Here I get the assertion
The two disabled "SET SESSION ..." can be used to reveal
that the optimizer is not involved.

Result on mysql-6.0-codebase-bugfixing revno: 3647 2009-10-08
-------------------------------------------------------------
Thread 1 (process 23582):
#0  0x00007f8a5d32ace6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b75864 in my_write_core (sig=6) at stacktrace.c:309
#2  0x00000000006f3e78 in handle_segfault (sig=6) at mysqld.cc:2754
#3  <signal handler called>
#4  0x00007f8a5c2265c5 in raise () from /lib64/libc.so.6
#5  0x00007f8a5c227bb3 in abort () from /lib64/libc.so.6
#6  0x00007f8a5c21f1e9 in __assert_fail () from /lib64/libc.so.6
#7  0x0000000000762c5e in open_table (thd=0x1618448, table_list=0x16734b8, mem_root=0x40e080f0, ot_ctx=0x40e08160, flags=32) at sql_base.cc:2891
#8  0x0000000000763669 in open_and_process_table (thd=0x1618448, lex=0x1619d18, tables=0x16734b8, counter=0x40e08230, flags=32, prelocking_strategy=0x40e09210, has_prelocking_list=false, ot_ctx=0x40e08160, new_frm_mem=0x40e080f0) at sql_base.cc:3936
#9  0x0000000000763d40 in open_tables (thd=0x1618448, start=0x40e081f0, counter=0x40e08230, flags=32, prelocking_strategy=0x40e09210) at sql_base.cc:4174
#10 0x000000000076422c in open_and_lock_tables_derived (thd=0x1618448, tables=0x16734b8, derived=false, flags=32, prelocking_strategy=0x40e09210) at sql_base.cc:4759
#11 0x00000000008835f8 in mysql_alter_table (thd=0x1618448, new_db=0x1673a50 "test", new_name=0x0, create_info=0x40e09630, table_list=0x16734b8, alter_info=0x40e0a1b0, order_num=0, order=0x0, ignore=false) at sql_table.cc:6927
#12 0x0000000000708784 in mysql_execute_command (thd=0x1618448) at sql_parse.cc:2888
#13 0x000000000070f12e in mysql_parse (thd=0x1618448, inBuf=0x16733d0 "ALTER TABLE t2 COMMENT = 'DEF'", length=30, found_semicolon=0x40e0af20) at sql_parse.cc:5991
#14 0x000000000070fd6a in dispatch_command (command=COM_QUERY, thd=0x1618448, packet=0x16635b9 "ALTER TABLE t2 COMMENT = 'DEF'", packet_length=30) at sql_parse.cc:1074
#15 0x0000000000711231 in do_command (thd=0x1618448) at sql_parse.cc:756
#16 0x00000000006fe038 in handle_one_connection (arg=0x1618448) at sql_connect.cc:1164
#17 0x00007f8a5d326040 in start_thread () from /lib64/libpthread.so.0
#18 0x00007f8a5c2c708d in clone () from /lib64/libc.so.6
#19 0x0000000000000000 in ?? ()

Result on mysql-5.1-bugteam revno: 3146 2009-10-06
--------------------------------------------------
Thread 1 (process 23834):
#0  0x00007f3acec58ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b14343 in my_write_core (sig=6) at stacktrace.c:310
#2  0x00000000006b9f13 in handle_segfault (sig=6) at mysqld.cc:2570
#3  <signal handler called>
#4  0x00007f3acde615c5 in raise () from /lib64/libc.so.6
#5  0x00007f3acde62bb3 in abort () from /lib64/libc.so.6
#6  0x00007f3acde5a1e9 in __assert_fail () from /lib64/libc.so.6
#7  0x000000000072176c in open_table (thd=0x11e26a0, table_list=0x1201b60, mem_root=0x41cecf90, refresh=0x41ced01f, flags=0) at sql_base.cc:2973
#8  0x0000000000722351 in open_tables (thd=0x11e26a0, start=0x41ced090, counter=0x41ced0d4, flags=0) at sql_base.cc:4577
#9  0x0000000000722c8d in open_and_lock_tables_derived (thd=0x11e26a0, tables=0x1201b60, derived=false) at sql_base.cc:4983
#10 0x00000000006d7e5d in simple_open_n_lock_tables (thd=0x11e26a0, tables=0x1201b60) at mysql_priv.h:1557
#11 0x0000000000722eb3 in open_n_lock_single_table (thd=0x11e26a0, table_l=0x1201b60, lock_type=TL_WRITE_ALLOW_READ) at sql_base.cc:4861
#12 0x000000000082b292 in mysql_alter_table (thd=0x11e26a0, new_db=0x11f35f0 "test", new_name=0x0, create_info=0x41cee710, table_list=0x1201b60, alter_info=0x41cef010, order_num=0, order=0x0, ignore=false) at sql_table.cc:6549
#13 0x00000000006cdc2b in mysql_execute_command (thd=0x11e26a0) at sql_parse.cc:2890
#14 0x00000000006d4d44 in mysql_parse (thd=0x11e26a0, inBuf=0x11fd530 "ALTER TABLE t2 COMMENT = 'DEF'", length=30, found_semicolon=0x41cefef0) at sql_parse.cc:5963
#15 0x00000000006d5b58 in dispatch_command (command=COM_QUERY, thd=0x11e26a0, packet=0x11f3a11 "ALTER TABLE t2 COMMENT = 'DEF'", packet_length=30) at sql_parse.cc:1224
#16 0x00000000006d6f4a in do_command (thd=0x11e26a0) at sql_parse.cc:865
#17 0x00000000006c3439 in handle_one_connection (arg=0x11e26a0) at sql_connect.cc:1127
#18 0x00007f3acec54040 in start_thread () from /lib64/libpthread.so.0
#19 0x00007f3acdf0208d in clone () from /lib64/libc.so.6
#20 0x0000000000000000 in ?? ()

My environment:
---------------
- mysql-6.0-codebase-bugfixing , ./BUILD/compile-pentium64-debug-max
- mysql-5.1-bugteam , ./BUILD/compile-pentium64-valgrind-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/86756

2899 Jon Olav Hauglid	2009-10-14
      Bug #47919 assert in open_table during ALTER temporary table
      
      This assertion would occur if UPDATE was used to update multiple
      tables containing an AUTO_INCREMENT column and if the inserted
      row had a user-supplied value for that column. The assertion 
      could then be triggered by the next statement.
      
      The problem was only noticeable on debug builds of the server.
      
      The cause of the problem was that the code for multi update did
      not properly reset the TABLE->auto_increment_if_null flag after update.
      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 in this case by ALTER TABLE.
      
      This patch fixes the problem by resetting the auto_increment_if_null
      field to FALSE once a row has been updated.
      
      This bug is similar to Bug#47274, but for multi update rather
      than INSERT DELAYED.
      
      Test case added to update.test.

Approved by email. Please push into 5.1+

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

3180 Jon Olav Hauglid	2009-10-23
      Bug #47919 assert in open_table during ALTER temporary table
      
      This assertion would occur if UPDATE was used to update multiple
      tables containing an AUTO_INCREMENT column and if the inserted
      row had a user-supplied value for that column. The assertion 
      could then be triggered by the next statement.
      
      The problem was only noticeable on debug builds of the server.
      
      The cause of the problem was that the code for multi update did
      not properly reset the TABLE->auto_increment_if_null flag after update.
      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 in this case by ALTER TABLE.
      
      This patch fixes the problem by resetting the auto_increment_if_null
      field to FALSE once a row has been updated.
      
      This bug is similar to Bug#47274, but for multi update rather
      than INSERT DELAYED.
      
      Test case added to update.test.

Pushed to 5.1-bugteam (5.1.41) and mysql-pe (6.0.14).

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)

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)

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)

Noted in 5.1.41, 5.5.0, 6.0.14 changelogs.

For debug builds, an assertion could fail during the next statement
executed for a temporary table after a multiple-table UPDATE 
involving that table and modified an AUTO_INCREMENT column with a 
user-supplied value.

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)

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)

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)

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)

Already documented in 5.1.41