Below is the list of changes that have just been committed into a local
5.1 repository of andrey. When andrey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2006-10-30 11:32:31+01:00, andrey@stripped +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.
mysql-test/r/alter_table.result@stripped, 2006-10-30 11:32:22+01:00, andrey@stripped +35 -0
update result
mysql-test/t/alter_table.test@stripped, 2006-10-30 11:32:22+01:00, andrey@stripped +44 -9
Error to bug number
Added test case for #22369: Alter table rename combined
with other alterations causes lost tables
sql/sql_table.cc@stripped, 2006-10-30 11:32:22+01:00, andrey@stripped +56 -8
When doing ALTER TABLE RENAME and there is not only rename
clause, copy the table. This is the safe choice.
There were few options explored:
1)Add the second name to a TABLE_LIST and pass it to mysql_alter_table()
then do the rename and if there are more clauses continue. Doesn't work
as the table is opened beforehand.
2)Let create_info->frm_only not to be 1 when there is RENAME. This works
without error from the storage engine and the statement. But all the data
gets lost, because the new table is empty and then it "overwrites" the
original table
Thus safest is to mark tables not compatible if there is RENAME clause. In 5.0
and 4.1 it's already the case. 5.1 differs a lot compared to 5.0's codebase.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: andrey
# Host: example.com
# Root: /work/bug22369/my51
--- 1.370/sql/sql_table.cc 2006-10-30 11:32:44 +01:00
+++ 1.371/sql/sql_table.cc 2006-10-30 11:32:44 +01:00
@@ -4971,7 +4971,8 @@ static uint compare_tables(TABLE *table,
create_info->used_fields & HA_CREATE_USED_ENGINE ||
create_info->used_fields & HA_CREATE_USED_CHARSET ||
create_info->used_fields & HA_CREATE_USED_DEFAULT_CHARSET ||
- (alter_info->flags & (ALTER_RECREATE | ALTER_FOREIGN_KEY)) ||
+ (alter_info->flags & (ALTER_RECREATE | ALTER_FOREIGN_KEY |
+ ALTER_RENAME)) ||
order_num ||
!table->s->mysql_version ||
(table->s->frm_version < FRM_VER_TRUE_VARCHAR && varchar))
@@ -5132,10 +5133,60 @@ static uint compare_tables(TABLE *table,
/*
- Alter table
+ SYNOPSIS
+ mysql_alter_table()
+ thd Thread handler
+ new_db If there is a RENAME clause
+ new_name If there is a RENAME clause
+ lex_create_info Information from the parsing phase. Since some
+ clauses are common to CREATE and ALTER TABLE, the
+ data is stored in lex->create_info. The non-common
+ is stored in lex->alter_info.
+ table_list The table to change.
+ fields lex->create_list - List of fields to be changed,
+ added or dropped.
+ keys lex->key_list - List of keys to be changed, added or
+ dropped.
+ order_num How many ORDER BY fields has been specified.
+ order List of fields to ORDER BY.
+ ignore Whether we have ALTER IGNORE TABLE
+ alter_info Information from the parsing phase specific to ALTER
+ TABLE and not shared with CREATE TABLE.
+ do_send_ok Whether to call send_ok() on success.
+
+ DESCRIPTION
+ This is a veery long function and is everything but the kitchen sink :)
+ It is used to alter a table and not only by ALTER TABLE but also
+ CREATE|DROP INDEX are mapped on this function, as well as RENAME TABLE.
+ When the ALTER TABLE statement just does a RENAME (or it is a
+ RENAME TABLE) or ENABLE|DISABLE KEYS, then this function short cuts its
+ operation by renaming the table and/or enabling/disabling the keys.
+ In this case the FRM is not changed. However, if there is a RENAME +
+ change of a field, or an index the short cut is not used.
+ `fields` is used to generate the new FRM regarding the structure of the
+ fields. The same is done for the indices of the table.
+
+ Important is the fact that this function tries to do as less work as
+ possible by finding out whether a temporary table is needed to copy data
+ into and when finishing the altering to use it as the original table.
+ For this reason the function compare_tables() is called, which decides
+ based on all kind of data how similar are the new and the original
+ tables.
+
+ NOTES
+ Combined ALTER TABLE that does change the name of the table as well,
+ as fields and indices could be slower than when broken down in two
+ statements one of which renames the table and the other changes its
+ structure. The reason is because in the former case the data is
+ copied and then changed. OTOH, if the tables are `compatible` then
+ the two statements won't do any copy but combined ALTER TABLE will.
+
+ RETURN VALUES
+ FALSE OK
+ TRUE Error
*/
-bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
+bool mysql_alter_table(THD *thd, char *new_db, char *new_name,
HA_CREATE_INFO *lex_create_info,
TABLE_LIST *table_list,
List<create_field> &fields, List<Key> &keys,
@@ -5460,7 +5511,7 @@ view_err:
DBUG_RETURN(error);
}
- /* Full alter table */
+ /* Ok. We have to do full alter table */
/* Let new create options override the old ones */
if (!(used_fields & HA_CREATE_USED_MIN_ROWS))
@@ -6419,10 +6470,7 @@ view_err:
DBUG_ASSERT(!(mysql_bin_log.is_open() && thd->current_stmt_binlog_row_based &&
(create_info->options & HA_LEX_CREATE_TMP_TABLE)));
write_bin_log(thd, TRUE, thd->query, thd->query_length);
- /*
- TODO RONM: This problem needs to handled for Berkeley DB partitions
- as well
- */
+
if (ha_check_storage_engine_flag(old_db_type,HTON_FLUSH_AFTER_RENAME))
{
/*
--- 1.66/mysql-test/r/alter_table.result 2006-10-30 11:32:44 +01:00
+++ 1.67/mysql-test/r/alter_table.result 2006-10-30 11:32:44 +01:00
@@ -722,3 +722,38 @@ Table Create Table
`c1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE `#sql2`, `@0023sql1`;
+DROP TABLE IF EXISTS bug_22369;
+DROP TABLE IF EXISTS fibonacci;
+CREATE TABLE bug_22369 (
+int_field int UNSIGNED NOT NULL,
+char_field CHAR(1),
+INDEX(`int_field`)
+);
+ALTER TABLE bug_22369 DISABLE KEYS;
+INSERT INTO bug_22369 VALUES (1,"a"), (1,"b"), (2,"c"),
+(3,"d"), (5,"e"), (8,"f"),
+(13,"g"),(21,"h"),(34,"i");
+EXPLAIN SELECT char_field FROM bug_22369 WHERE int_field BETWEEN 4 AND 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE bug_22369 ALL NULL NULL NULL NULL 9 Using where
+ALTER TABLE bug_22369
+CHANGE int_field unsigned_int_field int unsigned not null,
+RENAME fibonacci,
+ENABLE KEYS;
+SELECT * FROM bug_22369 ORDER BY int_field;
+ERROR 42S02: Table 'test.bug_22369' doesn't exist
+EXPLAIN SELECT char_field FROM fibonacci WHERE unsigned_int_field BETWEEN 4 AND 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE fibonacci range int_field int_field 4 NULL 1 Using where
+SELECT * FROM fibonacci ORDER BY unsigned_int_field;
+unsigned_int_field char_field
+1 a
+1 b
+2 c
+3 d
+5 e
+8 f
+13 g
+21 h
+34 i
+DROP TABLE fibonacci;
--- 1.51/mysql-test/t/alter_table.test 2006-10-30 11:32:44 +01:00
+++ 1.52/mysql-test/t/alter_table.test 2006-10-30 11:32:44 +01:00
@@ -101,7 +101,7 @@ create table mysqltest.t1 (name char(15)
insert into mysqltest.t1 (name) values ("mysqltest");
select * from t1;
select * from mysqltest.t1;
---error 1050
+--error ER_TABLE_EXISTS_ERROR
alter table t1 rename mysqltest.t1;
select * from t1;
select * from mysqltest.t1;
@@ -231,9 +231,9 @@ DROP TABLE t1;
# BUG#4717 - check for valid table names
#
create table t1 (a int);
---error 1103
+--error ER_WRONG_TABLE_NAME
alter table t1 rename to ``;
---error 1103
+--error ER_WRONG_TABLE_NAME
rename table t1 to ``;
drop table t1;
@@ -325,14 +325,14 @@ drop table t1;
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);
ALTER TABLE t1 DROP PRIMARY KEY;
SHOW CREATE TABLE t1;
---error 1091
+--error ER_CANT_DROP_FIELD_OR_KEY
ALTER TABLE t1 DROP PRIMARY KEY;
DROP TABLE t1;
# BUG#3899
create table t1 (a int, b int, key(a));
insert into t1 values (1,1), (2,2);
---error 1091
+--error ER_CANT_DROP_FIELD_OR_KEY
alter table t1 drop key no_such_key;
alter table t1 drop key a;
drop table t1;
@@ -343,7 +343,7 @@ drop table t1;
# Some platforms (Mac OS X, Windows) will send the error message using small letters.
CREATE TABLE T12207(a int) ENGINE=MYISAM;
--replace_result t12207 T12207
---error 1031
+--error ER_ILLEGAL_HA
ALTER TABLE T12207 DISCARD TABLESPACE;
DROP TABLE T12207;
@@ -367,7 +367,7 @@ drop table t1;
# shorter than packed field length.
#
create table t1 ( a timestamp );
---error 1089
+--error ER_WRONG_SUB_KEY
alter table t1 add unique ( a(1) );
drop table t1;
@@ -380,7 +380,7 @@ create table t1 (c1 int);
# Move table to other database.
alter table t1 rename mysqltest.t1;
# Assure that it has moved.
---error 1051
+--error ER_BAD_TABLE_ERROR
drop table t1;
# Move table back.
alter table mysqltest.t1 rename t1;
@@ -394,7 +394,7 @@ use mysqltest;
# Drop the current db. This de-selects any db.
drop database mysqltest;
# Now test for correct message.
---error 1046
+--error ER_NO_DB_ERROR
alter table test.t1 rename t1;
# Check that explicit qualifying works even with no selected db.
alter table test.t1 rename test.t1;
@@ -536,3 +536,38 @@ SHOW CREATE TABLE `#sql2`;
SHOW CREATE TABLE `@0023sql1`;
DROP TABLE `#sql2`, `@0023sql1`;
+#
+# Bug #22369: Alter table rename combined with other alterations causes lost tables
+#
+# This problem happens if the data change is compatible.
+# Changing to the same type is compatible for example.
+#
+--disable_warnings
+DROP TABLE IF EXISTS bug_22369;
+DROP TABLE IF EXISTS fibonacci;
+--enable_warnings
+CREATE TABLE bug_22369 (
+ int_field int UNSIGNED NOT NULL,
+ char_field CHAR(1),
+ INDEX(`int_field`)
+);
+
+ALTER TABLE bug_22369 DISABLE KEYS;
+INSERT INTO bug_22369 VALUES (1,"a"), (1,"b"), (2,"c"),
+ (3,"d"), (5,"e"), (8,"f"),
+ (13,"g"),(21,"h"),(34,"i");
+
+EXPLAIN SELECT char_field FROM bug_22369 WHERE int_field BETWEEN 4 AND 9;
+
+ALTER TABLE bug_22369
+ CHANGE int_field unsigned_int_field int unsigned not null,
+ RENAME fibonacci,
+ ENABLE KEYS;
+
+--error ER_NO_SUCH_TABLE
+SELECT * FROM bug_22369 ORDER BY int_field;
+
+EXPLAIN SELECT char_field FROM fibonacci WHERE unsigned_int_field BETWEEN 4 AND 9;
+
+SELECT * FROM fibonacci ORDER BY unsigned_int_field;
+DROP TABLE fibonacci;
| Thread |
|---|
| • bk commit into 5.1 tree (andrey:1.2337) BUG#22369 | ahristov | 30 Oct |
