MySQL Bugs: #6895: Prepared Statements: ALTER TABLE DROP COLUMN does nothing

Bug #6895	Prepared Statements: ALTER TABLE DROP COLUMN does nothing
Submitted:	30 Nov 2004 17:26	Modified:	4 Jun 2007 18:02
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Windows (Windows XP, Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	ALTER TABLE, prepared statement, rt_q1_2007, stored procedure

Description:
I have a stored procedure which adds a column, then drops the same column, 
inside a WHILE loop. For a few iterations of the loop, this is okay. But eventually 
the "drop column" doesn't happen. I see a "Column count doesn't match value count" 
error message. It happens for INSERT, because the column that I added has not 
been dropped. 
  

How to repeat:
mysql> create procedure pq () begin declare v3 int default 0; create table if not exists tq (s1 
int,s2 char); while v3 < 3 do select v3; insert into tq values (1,'a'); alter table tq add column 
s3 char; alter table tq drop column s3; set v3 = v3 + 1; end while; end;// 
Query OK, 0 rows affected (0.38 sec) 
 
mysql> call pq()// 
+----+ 
| v3 | 
+----+ 
|  0 | 
+----+ 
1 row in set (0.31 sec) 
 
+----+ 
| v3 | 
+----+ 
|  1 | 
+----+ 
1 row in set (0.45 sec) 
 
+----+ 
| v3 | 
+----+ 
|  2 | 
+----+ 
1 row in set (0.59 sec) 
 
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Thank you for the bug report.

The problem still exists as described:

mysql> use test
Database changed
mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.04 sec)

mysql> delimiter //
mysql> create procedure pq () begin declare v3 int default 0; create table if
    -> not exists tq (s1
    -> int,s2 char); while v3 < 3 do select v3; insert into tq values (1,'a'); a
lter
    -> table tq add column
    -> s3 char; alter table tq drop column s3; set v3 = v3 + 1; end while; end;/
/
Query OK, 0 rows affected (0.42 sec)

mysql> call pq()//
+----+
| v3 |
+----+
|  0 |
+----+
1 row in set (0.57 sec)

+----+
| v3 |
+----+
|  1 |
+----+
1 row in set (1.27 sec)

+----+
| v3 |
+----+
|  2 |
+----+
1 row in set (1.78 sec)

ERROR 1136 (21S01): Column count doesn't match value count at row 1

The same on Fedora Core and 5.0.13-BK build:

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0,00 sec)

mysql> delimiter //
mysql> create procedure pq () begin declare v3 int default 0; create table if
    -> not exists tq (s1
    -> int,s2 char); while v3 < 3 do select v3; insert into tq values (1,'a'); a
lter
    -> table tq add column
    -> s3 char; alter table tq drop column s3; set v3 = v3 + 1; end while; end;/
/
Query OK, 0 rows affected (1,69 sec)

mysql> call pq()//
+----+
| v3 |
+----+
|  0 |
+----+
1 row in set (0,15 sec)

+----+
| v3 |
+----+
|  1 |
+----+
1 row in set (0,33 sec)

+----+
| v3 |
+----+
|  2 |
+----+
1 row in set (0,55 sec)

ERROR 1136 (21S01): Column count doesn't match value count at row 1

Test case:

create procedure p1()
begin 
  declare v3 int default 0; 

  create table if not exists t1 (s1 int,s2 char); 

  while v3 < 3 do 
    select v3; 
    insert into t1 values (1,'a'); 
    alter table t1 add column s3 char; 
    alter table t1 drop column s3; 
    set v3 = v3 + 1; 
  end while;
end|

call p1()

The bug is in ALTER TABLE code, which is not reexecution-friendly.
A test case using SQL syntax for prepared statements that demonstrates the problem:
mysql> create table t1 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from "alter table t1 drop column b";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute stmt;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> drop table t1; create table t1 (a int, b int);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
| b     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> execute stmt;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
| b     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

SQL code:
create table t1 (a int, b int);
prepare stmt from "alter table t1 drop column b";
execute stmt;
show columns from t1;
drop table t1; create table t1 (a int, b int);
execute stmt;
show columns from t1;
execute stmt;
show columns from t1;

The bug is present in 5.0 and up, as in 4.1 ALTER is not supported in prepared statements.

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

ChangeSet@1.2298, 2006-11-01 15:56:12+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#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing"
  
  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.

Two code reviews done by email and IRC.

Pushed into 4.1.23 and 5.0.36, NULL-merged into 5.1. 5.1 requires a separate patch.

Noted in 4.1.23, 5.0.36, 5.1.15 changelogs.

This bug is still present in 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/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.

Pushed into 5.1.20-beta

Moved 5.1 changelog entry from 5.1.15 to 5.1.20.