Description:
I have a procedure which has a loop which uses CREATE/DROP to add and drop an index.
It fails after a few iterations saying "Duplicate column name".
I have a procedure which has a loop which uses ALTER to add and drop a column.
It fails after a few iterations saying "Duplicate column name".

How to repeat:
mysql> delimiter //
mysql> create table tp (s1 int)//
Query OK, 0 rows affected (0.06 sec)

mysql> create procedure p1 () begin declare v int default 0; while v < 100 do select v; create index i on tp (s1); drop index i on tp; set v = v + 1; end while; end//
Query OK, 0 rows affected (0.02 sec)

mysql> call p1()//
+------+
| v    |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

+------+
| v    |
+------+
|    1 |
+------+
1 row in set (0.20 sec)

+------+
| v    |
+------+
|    2 |
+------+
1 row in set (0.34 sec)

ERROR 1061 (42000): Duplicate key name 'i'
mysql>
mysql> create procedure p2 () begin declare v int default 0; while v < 100 do select v; alter table tp add column s2 int; alter table tp drop column s2; set v = v + 1; end while; end//
Query OK, 0 rows affected (0.05 sec)

mysql> call p2()//
+------+
| v    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

+------+
| v    |
+------+
|    1 |
+------+
1 row in set (0.40 sec)

+------+
| v    |
+------+
|    2 |
+------+
1 row in set (0.69 sec)

ERROR 1060 (42S21): Duplicate column name 's2'

Thank you for the bug report.

ERROR 1061 (42000): Duplicate key name 'i'
mysql> select version();
    -> //
+-------------------+
| version()         |
+-------------------+
| 5.1.10-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

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.

Code reviews were sent 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.

Queued in 5.1-runtime

Pushed into 5.1.20-beta

Moved 5.1 changelog entry from 5.1.15 to 5.1.20.