From: Date: November 17 2006 9:10pm Subject: bk commit into 4.1 tree (andrey:1.2611) BUG#24395 List-Archive: http://lists.mysql.com/commits/15522 X-Bug: 24395 Message-Id: <20061117201052.048623312D@andrey.hristov.com> Below is the list of changes that have just been committed into a local 4.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-11-17 21:10:46+01:00, andrey@stripped +3 -0 Fix for bug#24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table To the documentor: ENABLE|DISABLE KEYS combined with another ALTER TABLE option, different than RENAME TO did nothing. Also, if the table had disabled keys and was modified then the end table was with enabled keys. mysql-test/r/alter_table.result@stripped, 2006-11-17 21:10:42+01:00, andrey@stripped +41 -0 update test mysql-test/t/alter_table.test@stripped, 2006-11-17 21:10:42+01:00, andrey@stripped +38 -0 add test for bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table sql/sql_table.cc@stripped, 2006-11-17 21:10:42+01:00, andrey@stripped +37 -0 When ENABLE|DISABLE index is combined with another option different than RENAME TO, we should ENABLE|DISABLE the keys of the modified table. Also when modifying we should preserve the previous state of the indices. (This problem exists in 5.0 and 5.1 but since the codebase has diverged, this fix won't automerge, but the fix will be quite similar). # 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/bug24395/my41 --- 1.309/sql/sql_table.cc 2006-11-17 21:10:52 +01:00 +++ 1.310/sql/sql_table.cc 2006-11-17 21:10:52 +01:00 @@ -2849,6 +2849,7 @@ int mysql_alter_table(THD *thd,char *new ALTER_INFO *alter_info, bool do_send_ok) { TABLE *table,*new_table; + bool indexes_were_disabled; int error; char tmp_name[80],old_name[32],new_name_buff[FN_REFLEN]; char new_alias_buff[FN_REFLEN], *table_name, *db, *new_alias, *alias; @@ -3022,6 +3023,7 @@ int mysql_alter_table(THD *thd,char *new } /* Full alter table */ + indexes_were_disabled= table->file->indexes_are_disabled(); /* let new create options override the old ones */ if (!(used_fields & HA_CREATE_USED_MIN_ROWS)) @@ -3355,7 +3357,41 @@ int mysql_alter_table(THD *thd,char *new VOID(quick_rm_table(new_db_type,new_db,tmp_name)); goto err; } + + { + /* We need to lock, if we have a temp table*/ + if (!(alter_info->keys_onoff == LEAVE_AS_IS && !indexes_were_disabled)) + new_table->file->external_lock(thd, F_WRLCK); + + error= 0; + switch (alter_info->keys_onoff) { + case ENABLE: + error= new_table->file->enable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE); + /* COND_refresh will be signaled in close_thread_tables() */ + break; + case LEAVE_AS_IS: + if (!indexes_were_disabled) + break; + /* Fall-through */ + case DISABLE: + error= new_table->file->disable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE); + /* COND_refresh will be signaled in close_thread_tables() */ + break; + } + if (error == HA_ERR_WRONG_COMMAND) + { + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_ILLEGAL_HA, ER(ER_ILLEGAL_HA), + table->table_name); + error= 0; + } + if (error) + { + new_table->file->print_error(error, MYF(0)); + goto skip_copy; + } + } /* We don't want update TIMESTAMP fields during ALTER TABLE. */ new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; @@ -3372,6 +3408,7 @@ int mysql_alter_table(THD *thd,char *new thd->last_insert_id=next_insert_id; // Needed for correct log thd->count_cuted_fields= CHECK_FIELD_IGNORE; +skip_copy: if (table->tmp_table) { /* We changed a temporary table */ --- 1.49/mysql-test/r/alter_table.result 2006-11-17 21:10:52 +01:00 +++ 1.50/mysql-test/r/alter_table.result 2006-11-17 21:10:52 +01:00 @@ -528,6 +528,47 @@ create table t1 ( a timestamp ); alter table t1 add unique ( a(1) ); ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys drop table t1; +drop table if exists bug_24395; +create table bug_24395 (a int, index(a)); +show indexes from bug_24395; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +bug_24395 1 a 1 a A NULL NULL NULL YES BTREE +"this used not to disable the index" +alter table bug_24395 modify a int, disable keys; +show indexes from bug_24395; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +bug_24395 1 a 1 a A NULL NULL NULL YES BTREE disabled +alter table bug_24395 enable keys; +show indexes from bug_24395; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +bug_24395 1 a 1 a A NULL NULL NULL YES BTREE +alter table bug_24395 modify a bigint, disable keys; +show indexes from bug_24395; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +bug_24395 1 a 1 a A NULL NULL NULL YES BTREE disabled +alter table bug_24395 enable keys; +show indexes from bug_24395; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +bug_24395 1 a 1 a A NULL NULL NULL YES BTREE +alter table bug_24395 add b char(10), disable keys; +show indexes from bug_24395; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +bug_24395 1 a 1 a A NULL NULL NULL YES BTREE disabled +alter table bug_24395 add c decimal(10,2), enable keys; +show indexes from bug_24395; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +bug_24395 1 a 1 a A NULL NULL NULL YES BTREE +"this however did" +alter table bug_24395 disable keys; +show indexes from bug_24395; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +bug_24395 1 a 1 a A NULL NULL NULL YES BTREE disabled +desc bug_24395; +Field Type Null Key Default Extra +a bigint(20) YES MUL NULL +b char(10) YES NULL +c decimal(10,2) YES NULL +drop table bug_24395; create database mysqltest1; create table t1 (c1 int); alter table t1 rename mysqltest1.t1; --- 1.39/mysql-test/t/alter_table.test 2006-11-17 21:10:52 +01:00 +++ 1.40/mysql-test/t/alter_table.test 2006-11-17 21:10:52 +01:00 @@ -362,6 +362,44 @@ alter table t1 add unique ( a(1) ); drop table t1; # +# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table +# +# 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_24395; +--enable_warnings +create table bug_24395 (a int, index(a)); +show indexes from bug_24395; +--echo "this used not to disable the index" +alter table bug_24395 modify a int, disable keys; +show indexes from bug_24395; + +alter table bug_24395 enable keys; +show indexes from bug_24395; + +alter table bug_24395 modify a bigint, disable keys; +show indexes from bug_24395; + +alter table bug_24395 enable keys; +show indexes from bug_24395; + +alter table bug_24395 add b char(10), disable keys; +show indexes from bug_24395; + +alter table bug_24395 add c decimal(10,2), enable keys; +show indexes from bug_24395; + +--echo "this however did" +alter table bug_24395 disable keys; +show indexes from bug_24395; + +desc bug_24395; + +drop table bug_24395; + +# # Bug#11493 - Alter table rename to default database does not work without # db name qualifying #