Bug #22369 Alter table rename combined with other alterations causes lost tables
Submitted: 14 Sep 2006 21:50 Modified: 14 Dec 2006 3:42
Reporter: Robert Nice Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.11-beta-log OS:Linux (FC5)
Assigned to: Andrey Hristov CPU Architecture:Any

[14 Sep 2006 21:50] Robert Nice
Description:
Table engine is innodb;

Simple enough table, thought I could get away with a table rename and some table alterations in the same statement. It seems to lose track of the table 1/2 way through the operation. After the error there is a suspicious looking temporary table left lying around and the original table is missing.

I can't drop the database afterwards either, it says:
mysql> drop database BBrniceOld;
ERROR 1051 (42S02): Unknown table 'Notes'

How to repeat:
mysql> show fields from BrokerNotes;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| BrokerNoteID | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | 
| BrokerID     | bigint(20) unsigned | NO   | MUL |         |                | 
| Origin       | varchar(64)         | NO   |     |         |                | 
| Created      | datetime            | NO   |     |         |                | 
| Note         | varchar(1024)       | NO   |     |         |                | 
+--------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> ALTER TABLE BrokerNotes RENAME Notes, CHANGE BrokerNoteID NoteID bigint unsigned not null auto_increment, CHANGE BrokerID GenericUserID bigint unsigned not null;
ERROR 1146 (42S02): Table 'BBrniceOld.BrokerNotes' doesn't exist

mysql> show tables;
+---------------------------+
| Tables_in_BBrniceOld      |
+---------------------------+
| #sql2-5514-8              |
[15 Sep 2006 9:24] Sveta Smirnova
Thank you for the report.

Is BrokerNotes temporary table? If so could you please explain how you create and use it before the error occurs? Also, please, provide output of SHOW VARIABLES LIKE 'lower_case_table_names'; statement?
[15 Sep 2006 11:49] Shane Bester
testcase for creating orphaned innodb tables

Attachment: testcase.sql (text/x-delimtext), 639 bytes.

[15 Sep 2006 12:24] Sveta Smirnova
Verified using test case provided by Shane Bester and BK sources on Linux:

mysql> create database bug22369_2;
Query OK, 1 row affected (0.03 sec)

mysql> use bug22369_2;
Database changed
mysql> DROP TABLE IF EXISTS `Notes`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP TABLE IF EXISTS `BrokerNotes`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `BrokerNotes`(
    -> `BrokerNoteID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `BrokerID` BIGINT(20) UNSIGNED NOT NULL,
    -> `Origin` VARCHAR(64) NOT NULL,
    -> `Created` DATETIME NOT NULL,
    -> `Note` VARCHAR(1024) NOT NULL,
    -> PRIMARY KEY(`BrokerNoteID`),
    -> INDEX(`BrokerID`) )ENGINE=InnoDB;

Query OK, 0 rows affected (0.16 sec)

mysql>
mysql> INSERT INTO `BrokerNotes` (`BrokerID`, `Origin`, `Created`, `Note`) VALUES (1,'1','20060606123333','note');
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> ALTER TABLE BrokerNotes RENAME Notes,CHANGE BrokerNoteID NoteID bigint unsigned not null auto_increment,CHANGE BrokerID GenericUserID bigint unsigned not null;
ERROR 1146 (42S02): Table 'bug22369_2.BrokerNotes' doesn't exist
mysql>  SHOW TABLES;
+----------------------+
| Tables_in_bug22369_2 |
+----------------------+
| Notes                |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM  Notes;
ERROR 1146 (42S02): Table 'bug22369_2.Notes' doesn't exist
mysql> \q
Bye

ssmirnova@shella ~/mysql5.1b
$ls -la data/bug22369_2
total 36
drwx------  2 ssmirnova ssmirnova 4096 Sep 15 14:25 .
drwx------  9 ssmirnova ssmirnova 4096 Sep 15 14:25 ..
-rw-rw----  1 ssmirnova ssmirnova   65 Sep 15 14:25 db.opt
-rw-rw----  1 ssmirnova ssmirnova 8712 Sep 15 14:25 Notes.frm
-rw-rw----  1 ssmirnova ssmirnova 8714 Sep 15 14:25 #sql2-74ba-3.frm
[25 Oct 2006 9:59] Andrey Hristov
Minimized the test case. On InnoDB there is an error, no error with MyISAM but it leads to kind of data loss, which can be resolved only on FS level.
-----------------------------------------------------
InnoDB:
drop database bug22369_34;
create database bug22369_34;
use bug22369_34;
CREATE TABLE `BrokerNotes`(
  `BrokerID` int UNSIGNED NOT NULL, INDEX(`BrokerID`)
) ENGINE=InnoDB;

ALTER TABLE BrokerNotes
  CHANGE BrokerID GenericUserID int unsigned not null,
  RENAME Notes;

-----------------------------------------------------
MyISAM (does not fail but the end result is bad):
drop database bug22369_36;
create database bug22369_36;
use bug22369_36;
CREATE TABLE `BrokerNotes`(
  `BrokerID` int UNSIGNED NOT NULL,
  INDEX(`BrokerID`)
)ENGINE=myisam;

ALTER TABLE BrokerNotes
  CHANGE BrokerID GenericUserID int unsigned not null,
  RENAME Notes;

andrey@lmy004:/work/bug22909/my51/sql> ls -al /work/var/bug22369_36/
total 22
drwx------   2 andrey users  168 2006-10-25 11:53 .
drwxr-xr-x  55 andrey users 1832 2006-10-25 11:43 ..
-rw-rw----   1 andrey users    0 2006-10-25 11:43 BrokerNotes.MYD
-rw-rw----   1 andrey users 1024 2006-10-25 11:43 BrokerNotes.MYI
-rw-rw----   1 andrey users   65 2006-10-25 11:43 db.opt
-rw-rw----   1 andrey users 8578 2006-10-25 11:45 Notes.frm

So, Notes.frm is renamed, but the data and the index files are not migrated!
[25 Oct 2006 10:00] Andrey Hristov
Here is the output with MyISAM:
mysql> use bug22369_36;
Database changed
mysql> CREATE TABLE `BrokerNotes`(
    -> `BrokerID` int UNSIGNED NOT NULL,
    -> INDEX(`BrokerID`) )ENGINE=myisam;
Query OK, 0 rows affected (0.07 sec)

mysql>
mysql> ALTER TABLE BrokerNotes CHANGE BrokerID GenericUserID int unsigned not null, RENAME Notes;
Query OK, 0 rows affected (9 min 48.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from Notes;
ERROR 1017 (HY000): Can't find file: 'Notes' (errno: 2)
[27 Oct 2006 9:11] Andrey Hristov
A patch like the following should resolve the issue. I will commit it using the standard procedure.
===== sql/mysql_priv.h 1.450 vs edited =====
--- 1.450/sql/mysql_priv.h	2006-10-27 11:07:50 +02:00
+++ edited/sql/mysql_priv.h	2006-10-26 17:46:47 +02:00
@@ -918,6 +918,7 @@ bool mysql_create_table(THD *thd,const c
 bool mysql_alter_table(THD *thd, char *new_db, char *new_name,
                        HA_CREATE_INFO *create_info,
                        TABLE_LIST *table_list,
+                       TABLE_LIST *rename_table_list,
                        List<create_field> &fields,
                        List<Key> &keys,
                        uint order_num, ORDER *order, bool ignore,
===== sql/sql_parse.cc 1.589 vs edited =====
--- 1.589/sql/sql_parse.cc	2006-10-27 11:07:54 +02:00
+++ edited/sql/sql_parse.cc	2006-10-26 17:47:43 +02:00
@@ -3141,7 +3141,8 @@ end_with_restore_list:
       thd->enable_slow_log= opt_log_slow_admin_statements;
       res= mysql_alter_table(thd, select_lex->db, lex->name,
                              &lex->create_info,
-                             first_table, lex->create_list,
+                             first_table, first_table->next_local,
+                             lex->create_list,
                              lex->key_list,
                              select_lex->order_list.elements,
                              (ORDER *) select_lex->order_list.first,
@@ -7199,7 +7200,7 @@ bool mysql_create_index(THD *thd, TABLE_
   create_info.db_type= 0;
   create_info.default_table_charset= thd->variables.collation_database;
   DBUG_RETURN(mysql_alter_table(thd,table_list->db,table_list->table_name,
-				&create_info, table_list,
+				&create_info, table_list, NULL,
 				fields, keys, 0, (ORDER*)0,
                                 0, &alter_info, 1));
 }
@@ -7217,7 +7218,7 @@ bool mysql_drop_index(THD *thd, TABLE_LI
   alter_info->clear();
   alter_info->flags= ALTER_DROP_INDEX;
   DBUG_RETURN(mysql_alter_table(thd,table_list->db,table_list->table_name,
-				&create_info, table_list,
+				&create_info, table_list, NULL,
 				fields, keys, 0, (ORDER*)0,
                                 0, alter_info, 1));
 }
===== sql/sql_table.cc 1.369 vs edited =====
--- 1.369/sql/sql_table.cc	2006-10-27 11:07:55 +02:00
+++ edited/sql/sql_table.cc	2006-10-26 18:05:03 +02:00
@@ -5172,6 +5172,7 @@ static uint compare_tables(TABLE *table,
 bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
                        HA_CREATE_INFO *lex_create_info,
                        TABLE_LIST *table_list,
+                       TABLE_LIST *rename_table_list,
                        List<create_field> &fields, List<Key> &keys,
                        uint order_num, ORDER *order, bool ignore,
                        ALTER_INFO *alter_info, bool do_send_ok)
@@ -5372,6 +5373,11 @@ bool mysql_alter_table(THD *thd,char *ne
   }
   
   thd->proc_info="setup";
+  /*
+    better have a negative test here, instead of positive, like
+    alter_info->flags & ALTER_ADD_COLUMN|ALTER_ADD_INDEX|...
+    so that ALTER TABLE won't break when somebody will add new flag
+  */
   if (!(alter_info->flags & ~(ALTER_RENAME | ALTER_KEYS_ONOFF)) &&
       !table->s->tmp_table) // no need to touch frm
   {
@@ -5931,12 +5937,7 @@ bool mysql_alter_table(THD *thd,char *ne
                         need_copy_table, need_lock_for_indexes));
   }
 
-  /*
-    better have a negative test here, instead of positive, like
-    alter_info->flags & ALTER_ADD_COLUMN|ALTER_ADD_INDEX|...
-    so that ALTER TABLE won't break when somebody will add new flag
-  */
-  if (!need_copy_table)
+  if (!need_copy_table && (!(alter_info->flags & ALTER_RENAME)))
     create_info->frm_only= 1;
 
 #ifdef WITH_PARTITION_STORAGE_ENGINE
@@ -6287,8 +6288,11 @@ bool mysql_alter_table(THD *thd,char *ne
 
 
   error=0;
-  if (!need_copy_table)
-    new_db_type=old_db_type= NULL; // this type cannot happen in regular ALTER
+  if (new_name)
+
+//  if (!need_copy_table)
+//    new_db_type=old_db_type= NULL; // this type cannot happen in regular ALTER
+
   if (mysql_rename_table(old_db_type, db, table_name, db, old_name,
                          FN_TO_IS_TMP))
   {
@@ -6308,6 +6312,7 @@ bool mysql_alter_table(THD *thd,char *ne
     VOID(mysql_rename_table(old_db_type, db, old_name, db, alias,
                             FN_FROM_IS_TMP));
   }
+  
   if (error)
   {
     /*
@@ -6326,8 +6331,9 @@ bool mysql_alter_table(THD *thd,char *ne
   {
     if (! table)
     {
+      TABLE_LIST *tmp= rename_table_list? rename_table_list : table_list;
       VOID(pthread_mutex_unlock(&LOCK_open));
-      if (! (table= open_ltable(thd, table_list, TL_WRITE_ALLOW_READ)))
+      if (! (table= open_ltable(thd, tmp, TL_WRITE_ALLOW_READ)))
         goto err;
       VOID(pthread_mutex_lock(&LOCK_open));
     }
@@ -6676,7 +6682,7 @@ bool mysql_recreate_table(THD *thd, TABL
   /* Force alter table to recreate table */
   lex->alter_info.flags= (ALTER_CHANGE_COLUMN | ALTER_RECREATE);
   DBUG_RETURN(mysql_alter_table(thd, NullS, NullS, &create_info,
-                                table_list, lex->create_list,
+                                table_list, NULL, lex->create_list,
                                 lex->key_list, 0, (ORDER *) 0,
                                 0, &lex->alter_info, do_send_ok));
 }
===== sql/sql_yacc.yy 1.509 vs edited =====
--- 1.509/sql/sql_yacc.yy	2006-10-27 11:07:55 +02:00
+++ edited/sql/sql_yacc.yy	2006-10-26 17:59:30 +02:00
@@ -5099,6 +5099,9 @@ alter_list_item:
             }
 	    lex->name= $3->table.str;
 	    lex->alter_info.flags|= ALTER_RENAME;
+	    if (!lex->select_lex.add_table_to_list(thd, $3, NULL,
+						   TL_OPTION_UPDATING))
+	      YYABORT;
 	  }
 	| CONVERT_SYM TO_SYM charset charset_name_or_default opt_collate
 	  {
[30 Oct 2006 10:34] 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/14543

ChangeSet@1.2337, 2006-10-30 11:32:31+01:00, andrey@example.com +3 -0
  Fix for bug#22369: Alter table rename combined
  with other alterations causes lost tables
  
  To the documentor: Using RENAME clause combined with other
  clauses of ALTER TABLE leads to data loss (the data is there but
  not accessible). This could happen if the changes do not change the table
  much. Adding and droppping fields and indices is safe. Renaming a column with
  MODIFY or CHANGE is unsafe operation.
  
  Depending on the storage the behavior is different:
  1)MyISAM (Memory probably too) - the ALTER TABLE statement completes
    without any error but next SELECT against the new table fails.
  2)InnoDB (and every transactional table) - The ALTER TABLE statement
    fails. There is are the the following files in the db dir -
    `new_table_name.frm` and a temporary table's frm. If the SE is file
    based, then the data and index files will be present but with the old
    names. What happens is that for InnoDB the table is not renamed in the
    internal DDIC.
[22 Nov 2006 17:28] 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/15727

ChangeSet@1.2387, 2006-11-22 18:25:36+01:00, andrey@example.com +4 -0
  Fix for bug#22369: Alter table rename combined
  with other alterations causes lost tables
    
  Using RENAME clause combined with other clauses of ALTER TABLE leaded to
  data loss (the data was there but not accessible). This could happen if the
  changes do not change the table much. Adding and droppping fields and
  indices was safe. Renaming a column with MODIFY or CHANGE is unsafe operation,
  if the actual column didn't change (changing from int to int, which is noop)
    
  Depending on the storage the behavior is different:
  1)MyISAM/MEMORY - the ALTER TABLE statement completes
    without any error but next SELECT against the new table fails.
  2)InnoDB (and every other transactional table) - The ALTER TABLE statement
    fails. There is are the the following files in the db dir -
    `new_table_name.frm` and a temporary table's frm. If the SE is file
    based, then the data and index files will be present but with the old
    names. What happens is that for InnoDB the table is not renamed in the
    internal DDIC.
  
  Fixed by adding additional call to SE's rename method, which should not include
  FRM file rename, because it firstly done during file names juggling.
[29 Nov 2006 12:16] 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/16089

ChangeSet@1.2386, 2006-11-29 11:46:28+01:00, andrey@example.com +5 -0
  Update mysql_fix_privilege_tables.sql to handle upgrade from 5.0 while retaining
  old upgrade behaviour.
  Add test for upgrade from 5.0.30
  ---
  Fix for bug#22369: Alter table rename combined
  with other alterations causes lost tables
  
  SE - Storage Engine
  
  Using RENAME clause combined with other clauses of ALTER TABLE leaded to
  data loss (the data was there but not accessible). This could happen if the
  changes do not change the table much. Adding and droppping fields and
  indices was safe. Renaming a column with MODIFY or CHANGE was unsafe operation,
  if the actual column didn't change (changing from int to int, which is a noop)
    
  Depending on the storage the behavior is different:
  1)MyISAM/MEMORY - the ALTER TABLE statement completes
    without any error but next SELECT against the new table fails.
  2)InnoDB (and every other transactional table) - The ALTER TABLE statement
    fails. There are the the following files in the db dir -
    `new_table_name.frm` and a temporary table's frm. If the SE is file
    based, then the data and index files will be present but with the old
    names. What happens is that for InnoDB the table is not renamed in the
    internal DDIC.
  
  Fixed by adding additional call to SE's rename method, which should not include
  FRM file rename, because it firstly done during file names juggling.
[29 Nov 2006 13:26] 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/16104

ChangeSet@1.2390, 2006-11-29 14:03:43+01:00, andrey@example.com +5 -0
  Fix for bug#22369: Alter table rename combined
  with other alterations causes lost tables
  
  SE - Storage Engine
  
  Using RENAME clause combined with other clauses of ALTER TABLE leaded to
  data loss (the data was there but not accessible). This could happen if the
  changes do not change the table much. Adding and droppping fields and
  indices was safe. Renaming a column with MODIFY or CHANGE was unsafe operation,
  if the actual column didn't change (changing from int to int, which is a noop)
    
  Depending on the storage the behavior is different:
  1)MyISAM/MEMORY - the ALTER TABLE statement completes
    without any error but next SELECT against the new table fails.
  2)InnoDB (and every other transactional table) - The ALTER TABLE statement
    fails. There are the the following files in the db dir -
    `new_table_name.frm` and a temporary table's frm. If the SE is file
    based, then the data and index files will be present but with the old
    names. What happens is that for InnoDB the table is not renamed in the
    internal DDIC.
  
  Fixed by adding additional call to SE's rename method, which should not include
  FRM file rename, because it firstly done during file names juggling.
[30 Nov 2006 14:16] 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/16216

ChangeSet@1.2390, 2006-11-30 15:14:23+01:00, andrey@example.com +7 -0
  Fix for bug#22369: Alter table rename combined
  with other alterations causes lost tables
  
  Using RENAME clause combined with other clauses of ALTER TABLE leaded to
  data loss (the data was there but not accessible). This could happen if the
  changes do not change the table much. Adding and droppping fields and
  indices was safe. Renaming a column with MODIFY or CHANGE was unsafe operation,
  if the actual column didn't change (changing from int to int, which is a noop)
    
  Depending on the storage engine (SE) the behavior is different:
  1)MyISAM/MEMORY - the ALTER TABLE statement completes
    without any error but next SELECT against the new table fails.
  2)InnoDB (and every other transactional table) - The ALTER TABLE statement
    fails. There are the the following files in the db dir -
    `new_table_name.frm` and a temporary table's frm. If the SE is file
    based, then the data and index files will be present but with the old
    names. What happens is that for InnoDB the table is not renamed in the
    internal DDIC.
  
  Fixed by adding additional call to mysql_rename_table() method, which should
  not include FRM file rename, because it has been already done during file
  names juggling.
[4 Dec 2006 17:24] 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/16402

ChangeSet@1.2390, 2006-12-04 18:22:38+01:00, andrey@example.com +7 -0
  Fix for bug#22369: Alter table rename combined
  with other alterations causes lost tables
  
  Using RENAME clause combined with other clauses of ALTER TABLE led to
  data loss (the data was there but not accessible). This could happen if the
  changes do not change the table much. Adding and droppping of fields and
  indices was safe. Renaming a column with MODIFY or CHANGE was unsafe operation,
  if the actual column didn't change (changing from int to int, which is a noop)
    
  Depending on the storage engine (SE) the behavior is different:
  1)MyISAM/MEMORY - the ALTER TABLE statement completes
    without any error but next SELECT against the new table fails.
  2)InnoDB (and every other transactional table) - The ALTER TABLE statement
    fails. There are the the following files in the db dir -
    `new_table_name.frm` and a temporary table's frm. If the SE is file
    based, then the data and index files will be present but with the old
    names. What happens is that for InnoDB the table is not renamed in the
    internal DDIC.
  
  Fixed by adding additional call to mysql_rename_table() method, which should
  not include FRM file rename, because it has been already done during file
  names juggling.
[7 Dec 2006 13:01] 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/16580

ChangeSet@1.2355, 2006-12-07 13:57:39+01:00, andrey@example.com +3 -0
  Additional fix for bug#22369
[8 Dec 2006 16:39] Andrey Hristov
The fix will be part of 5.1.15
[14 Dec 2006 3:42] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.15 changelog.