| 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: | |
| 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
[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]
MySQL Verification Team
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.
