Bug #4968 Stored procedure crash if cursor opened on altered table
Submitted: 10 Aug 2004 1:52 Modified: 4 Jun 2007 18:03
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.1 OS:Linux (SuSE 8.2/FC4/Windows)
Assigned to: Konstantin Osipov CPU Architecture:Any
Tags: ALTER TABLE, prepared statement, rt_q1_2007, stored procedure

[10 Aug 2004 1:52] Peter Gulutzan 
Description:
If I declare a cursor for selecting from a table, then open and close the cursor, then alter 
the table so that the column to be fetched is different, then open the same cursor again, 
I get a crash. 
 

How to repeat:
mysql> delimiter // 
mysql> create procedure pw () 
    ->  begin 
    ->   declare xx cursor for select * from tw; 
    ->   create table tw (s1 char(20)); 
    ->   open xx; 
    ->   close xx; 
    ->   alter table tw modify s1 int; 
    ->   open xx; 
    ->   end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call pw()// 
ERROR 2013 (HY000): Lost connection to MySQL server during query
[19 Apr 2005 14:10] Per-Erik Martin 
Since the new locking scheme was introduced, this is no longer repeatable as it is.
Moving the "create table" outside the procedure doesn't help, "alter" doesn't
get the right lock (the select makes it a read lock).
It's possible that this is still a problem, should the locking issue be resolved.
[5 Mar 2006 10:00] Konstantin Osipov 
This test case for the bug still produces quite worrying results in the latest 5.0:

mysql> drop procedure if exists bug4968|
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t5|
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure bug4968()
    -> begin
    ->   declare xx cursor for select * from t5;
    ->   create table t5 (s1 char(20));
    ->   open xx;
    ->   close xx;
    ->   alter table t5 modify s1 int;
    ->   open xx;
    ->   close xx;
    ->   drop table t5;
    -> end|
Query OK, 0 rows affected (0.01 sec)

mysql> call bug4968()|
ERROR 1054 (42S22): Unknown column 'tesàè0@.s1' in 'field list'
mysql> drop procedure bug4968|
Query OK, 0 rows affected (0.00 sec)

drop procedure if exists bug4968|
drop table if exists t5|
create procedure bug4968()
begin
  declare xx cursor for select * from t5;
  create table t5 (s1 char(20));
  open xx;
  close xx;
  alter table t5 modify s1 int;
  open xx;
  close xx;
  drop table t5;
end|
call bug4968()|
drop procedure bug4968|

This could lead to a crash in production.
[25 May 2006 10:54] MySQL Verification Team 
Hi, load the attached testcase into 5.0.22 and it crashes

mysql -uroot test < bug.sql

Then, 
In one query window run: CALL `test`.`p1`();
In another query window run: CALL `test`.`p2`();

Stack trace of crash:
!Field_enum::val_str(String * val_buffer=0x046edf2c, String * val_ptr=0x046edf2c)
!Field::val_str(String * str=0x046edf2c)
!field_conv(Field * to=0x03629f98, Field * from=0x03702ce8)
!Item_field::save_in_field(Field * to=0x03629f98, int no_conversions=0x00000000)
!sp_eval_expr(THD * thd=0x0362c9b0, Field * result_field=0x03629f98, Item * * expr_item_ptr=0x046ee2e4)
!sp_rcontext::set_variable(THD * thd=0x0362c9b0, Field * field=0x03629f98, Item * * value=0x046ee2e4)
!sp_rcontext::set_variable(THD * thd=0x0362c9b0, unsigned int var_idx=0x00000001, Item * * value=0x046ee2e4)
!Select_fetch_into_spvars::send_data(List<Item> & items={...})
!Materialized_cursor::fetch(unsigned long num_rows=0x00000001)
!sp_cursor::fetch(THD * thd=0x0362c9b0, List<sp_variable> * vars=0x03634024)
!sp_instr_cfetch::execute(THD * thd=0x0362c9b0, unsigned int * nextp=0x046ee3c0)
!sp_head::execute(THD * thd=0x0362c9b0)
!sp_head::execute_procedure(THD * thd=0x0362c9b0, List<Item> * args=0x0362cef4)
!mysql_execute_command(THD * thd=0x0362c9b0)
!mysql_parse(THD * thd=0x0362c9b0, char * inBuf=0x03628238, unsigned int length=0x00000022)
!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x0362c9b0, char * packet=0x0362dfc1, unsigned int packet_length=0x00000023)
!do_command(THD * thd=0x0362c9b0)
!handle_one_connection(void * arg=0x0362c9b0)
!pthread_start(void * param=0x035baff0)
!_threadstart(void * ptd=0x03631ff8)
[25 May 2006 10:59] MySQL Verification Team 
the original testcase still produces suspicious results on 5.0.22-bk

Current database: test

mysql> delimiter |
mysql>
mysql> drop procedure if exists bug4968|
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t5|
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create procedure bug4968()
    -> begin
    ->   declare xx cursor for select * from t5;
    ->   create table t5 (s1 char(20));
    ->   open xx;
    ->   close xx;
    ->   alter table t5 modify s1 int;
    ->   open xx;
    ->   close xx;
    ->   drop table t5;
    -> end|
Query OK, 0 rows affected (0.00 sec)

mysql> call bug4968()|
ERROR 1054 (42S22): Unknown column 'ÑÑÑÑÑÑÑÑÑÑÑÑÑÑÑ
mysql> drop procedure bug4968|
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> delimiter ;
mysql>
[16 Jun 2006 2:42] Konstantin Osipov 
The test case for this bug does not crash the server if it contains the patch for Bug#15217.

However, it causes valgrind warnings, as well as a simplier test case:

mysql> create procedure bug4968()
    -> begin
    ->   create table t5 (s1 char(20));
    ->   alter table t5 modify s1 int;
    ->   drop table t5;
    -> end|
Query OK, 0 rows affected (0.48 sec)

mysql> call bug4968()|
Query OK, 0 rows affected (0.82 sec)

mysql> call bug4968()|
Query OK, 0 rows affected (0.07 sec)

mysql> drop procedure bug4968|
==6228==  Address 0x4D0B078 is 16 bytes inside a block of size 17 free'd
==6228==    at 0x401CFCF: free (vg_replace_malloc.c:235)
==6228==    by 0x85E048F: my_no_flags_free (my_malloc.c:60)
==6228==    by 0x85E105F: free_root (my_alloc.c:347)
==6228==    by 0x823FDCC: closefrm(st_table*) (table.cc:992)
==6228==    by 0x8233BEA: intern_close_table(st_table*) (sql_base.cc:209)
==6228==    by 0x8233C7F: free_cache_entry(st_table*) (sql_base.cc:228)
==6228==    by 0x85EAC17: hash_delete (hash.c:520)
==6228==    by 0x8234C54: unlink_open_table(THD*, st_table*, st_table*) (sql_base.cc:1028)
==6228==    by 0x8313DB4: close_cached_table(THD*, st_table*) (sql_table.cc:1923)
==6228==    by 0x8319184: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, st_table_list*, List<create_field>&, List<Key>&, unsigned, st_order*, enum_duplicates, bool, st_alter_info*, bool) (sql_table.cc:3853)
==6228==    by 0x820BD49: mysql_execute_command(THD*) (sql_parse.cc:3050)
==6228==    by 0x8379E88: sp_instr_stmt::exec_core(THD*, unsigned*) (sp_head.cc:2311)
==6228==    by 0x8379C66: sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned*, bool, sp_instr*) (sp_head.cc:2187)
==6228==    by 0x837CD2A: sp_instr_stmt::execute(THD*, unsigned*) (sp_head.cc:2264)
==6228==    by 0x8376AEC: sp_head::execute(THD*) (sp_head.cc:1068)
==6228==    by 0x8377CE1: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:1520)
==6228==    by 0x8210D48: mysql_execute_command(THD*) (sql_parse.cc:4436)
==6228==    by 0x8212EAC: mysql_parse(THD*, char*, unsigned) (sql_parse.cc:5731)
==6228==    by 0x8213982: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1745)
==6228==    by 0x8214EE6: do_command(THD*) (sql_parse.cc:1531)
==6228==    by 0x82152E0: handle_one_connection (sql_parse.cc:1174)
==6228==    by 0x41B9340: start_thread (in /lib/tls/i686/cmov/libpthread-2.3.6.so)
==6228==    by 0x42F34ED: clone (in /lib/tls/i686/cmov/libc-2.3.6.so)
[16 Jun 2006 2:47] Konstantin Osipov 
The latter problem can be repeated with a significantly simplier test case:

create table t5 (s1 char(20));
prepare stmt from "alter table t5 modify s1 int";
execute stmt;
execute stmt;
drop table t5;

 -- the second execution of the prepared statement causes the very same warning
[16 Jun 2006 2:49] Konstantin Osipov 
This suggests that this bug should be revisited when Bug#19733 is fixed.
[31 Oct 2006 18:49] 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/14631

ChangeSet@1.2298, 2006-10-31 21:45:59+03:00, kostja@bodhi.local +13 -0
  A fix and test cases for
  Bug#4968 "Stored procedure crash if cursor opened on altered table"
  Bug#19733 "Repeated alter, or repeated create/drop, fails"
  Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from 
  stored procedure."
  
  Bug#19182 affects 4.1 and this fix will be ported to 4.1 when (if) it's 
  approved.
  
  The problem that caused all of the above bugs is 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 function remove processed columns and keys from
  create_list, key_list and drop_list. Search the code for drop_it.remove()
  and similar 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.
[9 Nov 2006 21:33] Konstantin Osipov 
Two reviews were sent by email and IRC.
QA test request will  be filed when the patch is pushed.
[7 Dec 2006 23:21] 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/16626

ChangeSet@1.2561, 2006-12-08 02:20:09+03:00, kostja@bodhi.local +12 -0
  A fix and test cases for
  Bug#4968 "Stored procedure crash if cursor opened on altered table"
  Bug#19733 "Repeated alter, or repeated create/drop, fails"
  Bug#19182 "CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work from 
  stored procedure."
  Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing"
  Bug#22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
  
  Test cases for bugs 4968, 19733, 6895 will be added 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 were not
  re-execution friendly: during their operation they used to 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 (note that code in 5.1 already creates and
  uses a copy of this structure in mysql_create_table()/alter_table(),
  but this approach didn't work well for CREATE TABLE SELECT statement).
[11 Dec 2006 22:50] 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/16805

ChangeSet@1.2317, 2006-12-12 01:50:12+03:00, kostja@bodhi.local +9 -0
  Post-merge fixes for Bug#4968 "Stored procedure crash if cursor opened 
  on altered table" and Bug#19733 "Repeated alter, or repeated 
  create/drop, fails"
[17 Jan 2007 12: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/18250

ChangeSet@1.2600, 2007-01-17 15:46:30+03:00, kostja@bodhi.local +1 -0
  Fix a failure of lowercase_tables2 test on powermacg5, introduced
  by the patch for Bug#4968
[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 3:57] Paul DuBois 
Noted in 4.1.23, 5.0.36, 5.1.15 changelogs.
[19 Jan 2007 15:16] Konstantin Osipov 
This bug is not fixed yet in 5.1 tree.
[15 May 2007 15:18] Konstantin Osipov 
Working on 5.1 port
[28 May 2007 11:30] 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/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:40] Konstantin Osipov 
Queued in 5.1-runtime
[1 Jun 2007 19:23] Bugs System 
Pushed into 5.1.20-beta
[4 Jun 2007 18:03] Paul DuBois 
Moved 5.1 changelog entry from 5.1.15 to 5.1.20.
[4 Jun 2007 21:48] Konstantin Osipov 
Bug#26650 Can't CREATE TEMPORARY TABLE in iterations in a stored procedure
was marked a duplicate of this bug.