From: Date: November 28 2006 6:47pm Subject: bk commit into 5.0 tree (andrey:1.2334) BUG#24395 List-Archive: http://lists.mysql.com/commits/15999 X-Bug: 24395 Message-Id: <20061128174756.715293311E@andrey.hristov.com> Below is the list of changes that have just been committed into a local 5.0 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-28 18:47:47+01:00, andrey@stripped +4 -0 Merge example.com:/work/bug24395-v2/my41 into example.com:/work/bug24395-v2/my50 fix for bug#24395 (5.0 merge) MERGE: 1.1616.2847.4 myisam/mi_open.c@stripped, 2006-11-28 18:47:44+01:00, andrey@stripped +2 -3 manual merge - use macroses from 5.0 MERGE: 1.80.2.7 mysql-test/r/alter_table.result@stripped, 2006-11-28 18:47:44+01:00, andrey@stripped +0 -0 manual merge MERGE: 1.40.1.11 mysql-test/t/alter_table.test@stripped, 2006-11-28 18:27:55+01:00, andrey@stripped +0 -0 Auto merged MERGE: 1.29.1.12 sql/sql_table.cc@stripped, 2006-11-28 18:47:44+01:00, andrey@stripped +12 -6 manual merge MERGE: 1.157.2.154 # 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-v2/my50/RESYNC --- 1.96/myisam/mi_open.c 2006-11-28 18:47:56 +01:00 +++ 1.97/myisam/mi_open.c 2006-11-28 18:47:56 +01:00 @@ -1262,13 +1262,30 @@ int mi_enable_indexes(MI_INFO *info) RETURN 0 indexes are not disabled 1 all indexes are disabled - [2 non-unique indexes are disabled - NOT YET IMPLEMENTED] + 2 non-unique indexes are disabled */ int mi_indexes_are_disabled(MI_INFO *info) { MYISAM_SHARE *share= info->s; - return (! mi_is_any_key_active(share->state.key_map) && share->base.keys); + /* + No keys or all are enabled. keys is the number of keys. Left shifted + gives us only one bit set. When decreased by one, gives us all all bits + up to this one set and it gets unset. + */ + if (!share->base.keys || + (mi_is_all_keys_active(share->state.key_map, share->base.keys))) + return 0; + + /* All are disabled */ + if (mi_is_any_key_active(share->state.key_map)) + return 1; + + /* + We have keys. Some enabled, some disabled. + Don't check for any non-unique disabled but return directly 2 + */ + return 2; } --- 1.326/sql/sql_table.cc 2006-11-28 18:47:56 +01:00 +++ 1.327/sql/sql_table.cc 2006-11-28 18:47:56 +01:00 @@ -37,7 +37,8 @@ static char *make_unique_key_name(const static int copy_data_between_tables(TABLE *from,TABLE *to, List &create, bool ignore, uint order_num, ORDER *order, - ha_rows *copied,ha_rows *deleted); + ha_rows *copied,ha_rows *deleted, + enum enum_enable_or_disable keys_onoff); static bool prepare_blob_field(THD *thd, create_field *sql_field); static bool check_engine(THD *thd, const char *table_name, enum db_type *new_engine); @@ -2935,6 +2936,54 @@ err: /* + Manages enabling/disabling of indexes for ALTER TABLE + + SYNOPSIS + alter_table_manage_keys() + table Target table + indexes_were_disabled Whether the indexes of the from table + were disabled + keys_onoff ENABLE | DISABLE | LEAVE_AS_IS + + RETURN VALUES + FALSE OK + TRUE Error +*/ + +static +bool alter_table_manage_keys(TABLE *table, int indexes_were_disabled, + enum enum_enable_or_disable keys_onoff) +{ + int error= 0; + DBUG_ENTER("alter_table_manage_keys"); + DBUG_PRINT("enter", ("table=%p were_disabled=%d on_off=%d", + table, indexes_were_disabled, keys_onoff)); + + switch (keys_onoff) { + case ENABLE: + error= table->file->enable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE); + break; + case LEAVE_AS_IS: + if (!indexes_were_disabled) + break; + /* fall-through: disabled indexes */ + case DISABLE: + error= table->file->disable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE); + } + + if (error == HA_ERR_WRONG_COMMAND) + { + push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_NOTE, + ER_ILLEGAL_HA, ER(ER_ILLEGAL_HA), table->table_name); + error= 0; + } else if (error) + table->file->print_error(error, MYF(0)); + + DBUG_RETURN(error); +} + + +/* Alter table */ @@ -3587,8 +3636,24 @@ view_err: new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; new_table->next_number_field=new_table->found_next_number_field; error=copy_data_between_tables(table, new_table, create_list, ignore, - order_num, order, &copied, &deleted); + order_num, order, &copied, &deleted, + alter_info->keys_onoff); } + else if (!new_table) + { + table->file->external_lock(thd, F_WRLCK); + alter_table_manage_keys(table, table->file->indexes_are_disabled(), + alter_info->keys_onoff); + table->file->external_lock(thd, F_UNLCK); + } + /* + No need to have call to alter_table_manage_keys() in the else because + in 4.1 we always copy data, except for views. In 5.0 it could happen + that no data is copied and only frm is modified. Then we have to handle + alter_info->keys_onoff outside of copy_data_between_tables + */ + + thd->last_insert_id=next_insert_id; // Needed for correct log thd->count_cuted_fields= CHECK_FIELD_IGNORE; @@ -3816,7 +3881,8 @@ copy_data_between_tables(TABLE *from,TAB bool ignore, uint order_num, ORDER *order, ha_rows *copied, - ha_rows *deleted) + ha_rows *deleted, + enum enum_enable_or_disable keys_onoff) { int error; Copy_field *copy,*copy_end; @@ -3848,6 +3914,9 @@ copy_data_between_tables(TABLE *from,TAB if (to->file->external_lock(thd, F_WRLCK)) DBUG_RETURN(-1); + + /* We need external lock before we can disable/enable keys */ + alter_table_manage_keys(to, from->file->indexes_are_disabled(), keys_onoff); /* We can abort alter table for any table type */ thd->no_trans_update= 0; --- 1.58/mysql-test/r/alter_table.result 2006-11-28 18:47:56 +01:00 +++ 1.59/mysql-test/r/alter_table.result 2006-11-28 18:47:56 +01:00 @@ -541,6 +541,127 @@ 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 t1; +create table t1 (a int, key(a)); +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE +"this used not to disable the index" +alter table t1 modify a int, disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +alter table t1 enable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE +alter table t1 modify a bigint, disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +alter table t1 enable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE +alter table t1 add b char(10), disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +alter table t1 add c decimal(10,2), enable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE +"this however did" +alter table t1 disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +desc t1; +Field Type Null Key Default Extra +a bigint(20) YES MUL NULL +b char(10) YES NULL +c decimal(10,2) YES NULL +alter table t1 add d decimal(15,5); +"The key should still be disabled" +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled +drop table t1; +"Now will test with one unique index" +create table t1(a int, b char(10), unique(a)); +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +alter table t1 disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +alter table t1 enable keys; +"If no copy on noop change, this won't touch the data file" +"Unique index, no change" +alter table t1 modify a int, disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +"Change the type implying data copy" +"Unique index, no change" +alter table t1 modify a bigint, disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +alter table t1 modify a bigint; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +alter table t1 modify a int; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +drop table t1; +"Now will test with one unique and one non-unique index" +create table t1(a int, b char(10), unique(a), key(b)); +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE +alter table t1 disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +alter table t1 enable keys; +"If no copy on noop change, this won't touch the data file" +"The non-unique index will be disabled" +alter table t1 modify a int, disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +alter table t1 enable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE +"Change the type implying data copy" +"The non-unique index will be disabled" +alter table t1 modify a bigint, disable keys; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +"Change again the type, but leave the indexes as_is" +alter table t1 modify a int; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +"Try the same. When data is no copied on similar tables, this is noop" +alter table t1 modify a int; +show indexes from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +t1 0 a 1 a A NULL NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE disabled +drop table t1; create database mysqltest; create table t1 (c1 int); alter table t1 rename mysqltest.t1; --- 1.45/mysql-test/t/alter_table.test 2006-11-28 18:47:56 +01:00 +++ 1.46/mysql-test/t/alter_table.test 2006-11-28 18:47:56 +01:00 @@ -372,6 +372,103 @@ 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 t1; +--enable_warnings +create table t1 (a int, key(a)); +show indexes from t1; +--echo "this used not to disable the index" +alter table t1 modify a int, disable keys; +show indexes from t1; + +alter table t1 enable keys; +show indexes from t1; + +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +alter table t1 enable keys; +show indexes from t1; + +alter table t1 add b char(10), disable keys; +show indexes from t1; + +alter table t1 add c decimal(10,2), enable keys; +show indexes from t1; + +--echo "this however did" +alter table t1 disable keys; +show indexes from t1; + +desc t1; + +alter table t1 add d decimal(15,5); +--echo "The key should still be disabled" +show indexes from t1; + +drop table t1; + +--echo "Now will test with one unique index" +create table t1(a int, b char(10), unique(a)); +show indexes from t1; +alter table t1 disable keys; +show indexes from t1; +alter table t1 enable keys; + +--echo "If no copy on noop change, this won't touch the data file" +--echo "Unique index, no change" +alter table t1 modify a int, disable keys; +show indexes from t1; + +--echo "Change the type implying data copy" +--echo "Unique index, no change" +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +alter table t1 modify a bigint; +show indexes from t1; + +alter table t1 modify a int; +show indexes from t1; + +drop table t1; + +--echo "Now will test with one unique and one non-unique index" +create table t1(a int, b char(10), unique(a), key(b)); +show indexes from t1; +alter table t1 disable keys; +show indexes from t1; +alter table t1 enable keys; + + +--echo "If no copy on noop change, this won't touch the data file" +--echo "The non-unique index will be disabled" +alter table t1 modify a int, disable keys; +show indexes from t1; +alter table t1 enable keys; +show indexes from t1; + +--echo "Change the type implying data copy" +--echo "The non-unique index will be disabled" +alter table t1 modify a bigint, disable keys; +show indexes from t1; + +--echo "Change again the type, but leave the indexes as_is" +alter table t1 modify a int; +show indexes from t1; +--echo "Try the same. When data is no copied on similar tables, this is noop" +alter table t1 modify a int; +show indexes from t1; + +drop table t1; + + +# # Bug#11493 - Alter table rename to default database does not work without # db name qualifying #