Bug #22060 ALTER TABLE x AUTO_INCREMENT=y in SP crashes server
Submitted: 6 Sep 2006 17:34 Modified: 4 Jun 2007 18:05
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1 OS:Any (*)
Assigned to: Konstantin Osipov CPU Architecture:Any
Tags: ALTER TABLE, auto_increment, rt_q1_2007, stored procedure

[6 Sep 2006 17:34] Shane Bester
Description:
When calling an SP that resets the auto_increment value of a table, in many threads, the server can crash.

mysql-pro-5.0.24a-linux-i686
-----------------------------
0x809f9d2 handle_segfault + 430
0x82f4918 pthread_sighandler + 184
0x82bf15d sm_00 + 0
0x811debf rea_create_table__FP3THDPcPCcT2P24st_ha_create_informationRt4List1Z12create_fieldUiP6st_key + 51
0x8145a96 mysql_create_table__FP3THDPCcT1P24st_ha_create_informationRt4List1Z12create_fieldRt4List1Z3KeybUi + 842
0x81497e3 mysql_alter_table__FP3THDPcT1P24st_ha_create_informationP13st_table_listRt4List1Z12create_fieldRt4List1Z3KeyUiP8st_order15enum_ + 5615
0x80b27ae mysql_execute_command__FP3THD + 5434
0x8183c5a exec_core__13sp_instr_stmtP3THDPUi + 26
0x8183a0b reset_lex_and_exec_core__13sp_lex_keeperP3THDPUibP8sp_instr + 223
0x8183b3d execute__13sp_instr_stmtP3THDPUi + 145
0x8181947 execute__7sp_headP3THD + 875
0x818254d execute_procedure__7sp_headP3THDPt4List1Z4Item + 753
0x80b5b03 mysql_execute_command__FP3THD + 18575
0x80b814d mysql_parse__FP3THDPcUi + 281
0x80afcd4 dispatch_command__F19enum_server_commandP3THDPcUi + 1876
0x80af573 do_command__FP3THD + 195
0x80aeb04 handle_one_connection + 764
0x82f20cc pthread_start_thread + 220
0x833a77a thread_start + 4

mysql-5.0.24-win32 (mysqld-debug.exe)
-------------------------------------
00 mysqld_debug!strmake
01 mysqld_debug!mysql_create_frm
02 mysqld_debug!rea_create_table
03 mysqld_debug!mysql_create_table
04 mysqld_debug!mysql_alter_table
05 mysqld_debug!mysql_execute_command
06 mysqld_debug!sp_instr_stmt::exec_core
07 mysqld_debug!sp_lex_keeper::reset_lex_and_exec_core
08 mysqld_debug!sp_instr_stmt::execute
09 mysqld_debug!sp_head::execute
0a mysqld_debug!sp_head::execute_procedure
0b mysqld_debug!mysql_execute_command
0c mysqld_debug!mysql_parse
0d mysqld_debug!dispatch_command
0e mysqld_debug!do_command
0f mysqld_debug!handle_one_connection
10 mysqld_debug!pthread_start
11 mysqld_debug!_threadstart
12 KERNEL32!BaseThreadStart

How to repeat:
DELIMITER //
DROP TABLE IF EXISTS `t1`//
CREATE TABLE `t1`(`a` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY)ENGINE=MyISAM//
DROP PROCEDURE IF EXISTS `p1`//
CREATE PROCEDURE `p1`()
BEGIN
	ALTER TABLE `t1` AUTO_INCREMENT=1;
	ALTER TABLE `t1` AUTO_INCREMENT=10;
	ALTER TABLE `t1` AUTO_INCREMENT=100;
END//
DELIMITER ;
INSERT INTO `t1` VALUES (),(),(),(),(),(),();

now, "call p1()" in many threads simultaneously and leave running.

Suggested fix:
.
[6 Sep 2006 17:34] Shane Bester
I found it hard to reproduce initially.  Please leave the threads running for some hours and a crash will happen.  Debug version is hard to crash.  Optimized binary is easier, maybe due to increased speed.  I'm now building 5.0BK in a optimized way and will post those results here.
[7 Sep 2006 13:33] Shane Bester
crashed my debug build of 5.0bk too.  But only after many tens of thousands of repetitions.
[4 Oct 2006 15:25] Shane Bester
i'm preparing a .c app to reproduce this crash. will upload shortly, after I see it crashes the server reliably
[4 Oct 2006 16:25] Shane Bester
See header for building instructions. Leave running....

Attachment: testcase.c (text/x-csrc), 3.34 KiB.

[4 Oct 2006 19:16] Shane Bester
the above testcase crashed after 2 hours of running
sbester@linux:~/code> ./testcase 
running initializationscompleted spawning new database worker threads

waiting for worker threads to finish
query failed 'call `p1`()' : 2013 (Lost connection to MySQL server during query)
query failed 'call `p1`()' : 2013 (Lost connection to MySQL server during query)
query failed 'call `p1`()' : 2013 (Lost connection to MySQL server during query)
query failed 'call `p1`()' : 2013 (Lost connection to MySQL server during query)
query failed 'call `p1`()' : 2013 (Lost connection to MySQL server during query)
sbester@linux:~/code>
[10 Oct 2006 14:43] Shane Bester
i can't seem to reproduce this crash anymore on mysql-standard-5.0.26-linux-i686-glibc23 or on 5.0BK, or on windows 5.0.26.  
I left it running for >8 hours already.

Not sure where this was fixed, but it seems ok now.
[13 Oct 2006 12:36] Dmitry Lenev
After investigation I believe that crash disappeared from last versions of 5.0 as side-effect of fixing bug #13934 "Silent truncation of table comments" (it was fixed in 5.0.25).
Still code which were causing it has some problems which can be  exposed by the following simple test-case:

--disable_warnings
drop table if exists t1, t2;
--enable_warnings
create table t1 (i int primary key auto_increment) comment='comment for table t1';
create table t2 (i int, j int, k int);
prepare stmt from "alter table t1 auto_increment=100";
execute stmt;
show create table t1;
# Let us trash table-cache's mem
flush tables;
select * from t2;
execute stmt;
# Oops we have some trash in the comment
show create table t1;
deallocate prepare stmt;
drop table t1, t2;
[25 Oct 2006 15:41] Dmitry Lenev
Hi, Shane!

As discussed on IRC current version of server still exposes some bad behavior which is related to original problem and which still have to be fixed (see example  above).

So I am marking this bug as verified again.
[31 Oct 2006 13:22] 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/14605

ChangeSet@1.2321, 2006-10-31 16:22:23+03:00, dlenev@mockturtle.local +7 -0
  Proposed fix for bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP
  crashes server".
  
  Re-execution of CREATE DATABASE, CREATE TABLE and ALTER TABLE 
  statements in stored routines or as prepared statements caused
  incorrect results (and crashes in versions before 5.0.25).
  (in 5.1 problem occured only for CREATE DATABASE, CREATE TABLE
  SELECT and CREATE TABLE with INDEX/DATA DIRECTOY options).
  
  The problem stemmed from the fact that code implementing these
  statements modified HA_CREATE_INFO structure in LEX (e.g. making
  it to point to areas in current memory root).
  
  Proposed patch solves this problem by creating and using on-stack
  copy of this structure (note that code in 5.1 already created and
  used copies this structure in mysql_create_table()/alter_table()
  routines but this approach didn't work well for CREATE TABLE
  SELECT statement).
  
  Note that this patch does not make CREATE/ALTER TABLE statements
  totally safe for re-execution. Their implementation still has some
  problems which are to be fixed during work on bugs 4968 and 19182.
[17 Jan 2007 17:55] Konstantin Osipov
Pushed into 4.1.23 and 5.0.36, NULL-merged into 5.1. 5.1 requires a separate patch.
[19 Jan 2007 4:08] Paul Dubois
Noted in 4.1.23, 5.0.36, 5.1.15 changelogs.
[19 Jan 2007 21:52] Konstantin Osipov
This bug is still present in 5.1
[28 May 2007 11:44] Konstantin Osipov
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/27449

ChangeSet@1.2515, 2007-05-28 15:30:01+04:00, kostja@vajra.(none) +17 -0
  5.1 version of a fix and test cases for bugs:
  Bug#4968 ""Stored procedure crash if cursor opened on altered table"
  Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing"
  Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from 
  stored procedure."
  Bug#19733 "Repeated alter, or repeated create/drop, fails"
  Bug#22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
  Bug#24879 "Prepared Statements: CREATE TABLE (UTF8 KEY) produces a 
  growing key length" (this bug is not fixed in 5.0)
  
  Re-execution of CREATE DATABASE, CREATE TABLE and ALTER TABLE 
  statements in stored routines or as prepared statements caused
  incorrect results (and crashes in versions prior to 5.0.25).
  
  In 5.1 the problem occured only for CREATE DATABASE, CREATE TABLE
  SELECT and CREATE TABLE with INDEX/DATA DIRECTOY options).
    
  The problem of bugs 4968, 19733, 19282 and 6895 was that functions
  mysql_prepare_table, mysql_create_table and mysql_alter_table are not
  re-execution friendly: during their operation they modify contents
  of LEX (members create_info, alter_info, key_list, create_list),
  thus making the LEX unusable for the next execution.
  In particular, these functions removed processed columns and keys from
  create_list, key_list and drop_list. Search the code in sql_table.cc 
  for drop_it.remove() and similar patterns to find evidence.
    
  The fix is to supply to these functions a usable copy of each of the
  above structures at every re-execution of an SQL statement. 
    
  To simplify memory management, LEX::key_list and LEX::create_list
  were added to LEX::alter_info, a fresh copy of which is created for
  every execution.
    
  The problem of crashing bug 22060 stemmed from the fact that the above 
  metnioned functions were not only modifying HA_CREATE_INFO structure 
  in LEX, but also were changing it to point to areas in volatile memory
  of the execution memory root.
     
  The patch solves this problem by creating and using an on-stack
  copy of HA_CREATE_INFO in mysql_execute_command.
  
  Additionally, this patch splits the part of mysql_alter_table
  that analizes and rewrites information from the parser into
  a separate function - mysql_prepare_alter_table, in analogy with
  mysql_prepare_table, which is renamed to mysql_prepare_create_table.
[28 May 2007 11:44] Konstantin Osipov
Queued in 5.1-runtime
[1 Jun 2007 19:24] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 18:05] Paul Dubois
Noted in 5.1.20 changelog.