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: | |
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
[6 Sep 2006 17:34]
MySQL Verification Team
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]
MySQL Verification Team
crashed my debug build of 5.0bk too. But only after many tens of thousands of repetitions.
[4 Oct 2006 15:25]
MySQL Verification Team
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]
MySQL Verification Team
See header for building instructions. Leave running....
Attachment: testcase.c (text/x-csrc), 3.34 KiB.
[4 Oct 2006 19:16]
MySQL Verification Team
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]
MySQL Verification Team
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.