From: Jon Olav Hauglid Date: January 12 2011 10:24am Subject: bzr commit into mysql-5.5 branch (jon.hauglid:3237) Bug#42230 List-Archive: http://lists.mysql.com/commits/128494 X-Bug: 42230 Message-Id: <201101121025.p0C8Xk0a014523@acsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============5215397303848668170==" --===============5215397303848668170== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/x/mysql-5.5-bug42230/ based on revid:dmitry.shulga@stripped 3237 Jon Olav Hauglid 2011-01-12 Bug #42230 during add index, cannot do queries on storage engines that implement add_index The problem was that ALTER TABLE blocked reads on an InnoDB table while adding a secondary index, even if this was not needed. It is only needed for the final step where the .frm file is updated. The reason queries were blocked, was that ALTER TABLE upgraded the metadata lock from MDL_SHARED_NO_WRITE (which blocks writes) to MDL_EXCLUSIVE (which blocks all accesses) before index creation. The way the server handles index creation, is that storage engines publish their capabilities to the server and the server determines which of the following three ways this can be handled: 1) build a new version of the table; 2) change the existing table but with exclusive metadata lock; 3) change the existing table but without metadata lock upgrade. For InnoDB and secondary index creation, option 3) should have been selected. However this failed for two reasons. First, InnoDB did not publish this capability properly. Second, the ALTER TABLE code failed to made proper use of the information supplied by the storage engine. A variable need_lock_for_indexes was set accordingly, but was not later used. This patch fixes this problem by only doing metadata lock upgrade before index creation/deletion if this variable has been set. This patch also changes some of the related terminology used in the code. Specifically the use of "fast" and "online" with respect to ALTER TABLE. "Fast" was used to indicate that an ALTER TABLE operation could be done without involving a temporary table. "Fast" has been renamed "in-place" to more accurately describe the behavior. "Online" meant that the operation could be done without taking a table lock. However, in the current implementation writes are always prohibited during ALTER TABLE and an exclusive metadata lock is held while updating the .frm, so ALTER TABLE is not completely online. This patch replaces "online" with "in-place", with additional comments indicating if concurrent reads are allowed during index creation/deletion or not. An important part of this update of terminology is renaming of the handler flags used by handlers to indicate if index creation/deletion can be done in-place and if concurrent reads are allowed. For example, the HA_ONLINE_ADD_INDEX_NO_WRITES flag has been renamed HA_INPLACE_ADD_INDEX_NO_READ_WRITE, while HA_ONLINE_ADD_INDEX is now HA_INPLACE_ADD_INDEX_NO_WRITE. Note that this is a rename to clarify current behavior, the flag values have not changed and no flags have been removed or added. Test case added to innodb_mysql_sync.test. modified: mysql-test/r/innodb_mysql_sync.result mysql-test/t/innodb_mysql_sync.test sql/ha_partition.cc sql/handler.h sql/sql_table.cc storage/innobase/handler/ha_innodb.cc === modified file 'mysql-test/r/innodb_mysql_sync.result' --- a/mysql-test/r/innodb_mysql_sync.result 2011-01-10 13:12:23 +0000 +++ b/mysql-test/r/innodb_mysql_sync.result 2011-01-12 10:24:03 +0000 @@ -90,3 +90,68 @@ test.t1 optimize status Operation failed # Connection default DROP TABLE t1; SET DEBUG_SYNC= 'RESET'; +# +# Bug#42230 during add index, cannot do queries on storage engines +# that implement add_index +# +DROP DATABASE IF EXISTS db1; +DROP TABLE IF EXISTS t1; +# Test 1: Secondary index, should not block reads (original test case). +# Connection default +CREATE DATABASE db1; +CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb; +INSERT INTO db1.t1(value) VALUES (1), (2); +SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +# Sending: +ALTER TABLE db1.t1 ADD INDEX(value); +# Connection con1 +SET DEBUG_SYNC= "now WAIT_FOR manage"; +USE db1; +SELECT * FROM t1; +id value +1 1 +2 2 +SET DEBUG_SYNC= "now SIGNAL query"; +# Connection default +# Reaping: ALTER TABLE db1.t1 ADD INDEX(value) +DROP DATABASE db1; +# Test 2: Primary index (implicit), should block reads. +CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; +SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +# Sending: +ALTER TABLE t1 ADD UNIQUE INDEX(a); +# Connection con1 +SET DEBUG_SYNC= "now WAIT_FOR manage"; +USE test; +# Sending: +SELECT * FROM t1; +# Connection con2 +# Waiting for SELECT to be blocked by the metadata lock on t1 +SET DEBUG_SYNC= "now SIGNAL query"; +# Connection default +# Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a) +# Connection con1 +# Reaping: SELECT * FROM t1 +a b +# Test 3: Primary index (explicit), should block reads. +# Connection default +ALTER TABLE t1 DROP INDEX a; +SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +# Sending: +ALTER TABLE t1 ADD PRIMARY KEY (a); +# Connection con1 +SET DEBUG_SYNC= "now WAIT_FOR manage"; +# Sending: +SELECT * FROM t1; +# Connection con2 +# Waiting for SELECT to be blocked by the metadata lock on t1 +SET DEBUG_SYNC= "now SIGNAL query"; +# Connection default +# Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a) +# Connection con1 +# Reaping: SELECT * FROM t1 +a b +# Test 4: Secondary unique index, should not block reads. +# Connection default +SET DEBUG_SYNC= "RESET"; +DROP TABLE t1; === modified file 'mysql-test/t/innodb_mysql_sync.test' --- a/mysql-test/t/innodb_mysql_sync.test 2011-01-10 13:12:23 +0000 +++ b/mysql-test/t/innodb_mysql_sync.test 2011-01-12 10:24:03 +0000 @@ -147,6 +147,139 @@ SET DEBUG_SYNC= 'RESET'; disconnect con1; +--echo # +--echo # Bug#42230 during add index, cannot do queries on storage engines +--echo # that implement add_index +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +DROP TABLE IF EXISTS t1; +--enable_warnings + +connect(con1,localhost,root); +connect(con2,localhost,root); + +--echo # Test 1: Secondary index, should not block reads (original test case). + +--echo # Connection default +connection default; +CREATE DATABASE db1; +CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb; +INSERT INTO db1.t1(value) VALUES (1), (2); +SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +--echo # Sending: +--send ALTER TABLE db1.t1 ADD INDEX(value) + +--echo # Connection con1 +connection con1; +SET DEBUG_SYNC= "now WAIT_FOR manage"; +# Neither of these two statements should be blocked +USE db1; +SELECT * FROM t1; +SET DEBUG_SYNC= "now SIGNAL query"; + +--echo # Connection default +connection default; +--echo # Reaping: ALTER TABLE db1.t1 ADD INDEX(value) +--reap +DROP DATABASE db1; + +--echo # Test 2: Primary index (implicit), should block reads. + +CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; +SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +--echo # Sending: +--send ALTER TABLE t1 ADD UNIQUE INDEX(a) + +--echo # Connection con1 +connection con1; +SET DEBUG_SYNC= "now WAIT_FOR manage"; +USE test; +--echo # Sending: +--send SELECT * FROM t1 + +--echo # Connection con2 +connection con2; +--echo # Waiting for SELECT to be blocked by the metadata lock on t1 +let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist + WHERE state= 'Waiting for table metadata lock' + AND info='SELECT * FROM t1'; +--source include/wait_condition.inc +SET DEBUG_SYNC= "now SIGNAL query"; + +--echo # Connection default +connection default; +--echo # Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a) +--reap + +--echo # Connection con1 +connection con1; +--echo # Reaping: SELECT * FROM t1 +--reap + +--echo # Test 3: Primary index (explicit), should block reads. + +--echo # Connection default +connection default; +ALTER TABLE t1 DROP INDEX a; +SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +--echo # Sending: +--send ALTER TABLE t1 ADD PRIMARY KEY (a) + +--echo # Connection con1 +connection con1; +SET DEBUG_SYNC= "now WAIT_FOR manage"; +--echo # Sending: +--send SELECT * FROM t1 + +--echo # Connection con2 +connection con2; +--echo # Waiting for SELECT to be blocked by the metadata lock on t1 +let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist + WHERE state= 'Waiting for table metadata lock' + AND info='SELECT * FROM t1'; +--source include/wait_condition.inc +SET DEBUG_SYNC= "now SIGNAL query"; + +--echo # Connection default +connection default; +--echo # Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a) +--reap + +--echo # Connection con1 +connection con1; +--echo # Reaping: SELECT * FROM t1 +--reap + +--echo # Test 4: Secondary unique index, should not block reads. +# This requires HA_INPLACE_ADD_UNIQUE_INDEX_NO_WRITE to be supported +# by InnoDB. Adding this flag currently introduces a regression so +# this test is disabled until the regression has been fixed. + +--echo # Connection default +connection default; +#SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +#--echo # Sending: +#--send ALTER TABLE t1 ADD UNIQUE (b) + +#--echo # Connection con1 +#connection con1; +#SET DEBUG_SYNC= "now WAIT_FOR manage"; +#SELECT * FROM t1; +#SET DEBUG_SYNC= "now SIGNAL query"; + +#--echo # Connection default +#connection default; +#--echo # Reaping: ALTER TABLE t1 ADD UNIQUE (b) +#--reap + +disconnect con1; +disconnect con2; +SET DEBUG_SYNC= "RESET"; +DROP TABLE t1; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc === modified file 'sql/ha_partition.cc' --- a/sql/ha_partition.cc 2010-12-17 20:58:40 +0000 +++ b/sql/ha_partition.cc 2011-01-12 10:24:03 +0000 @@ -6432,28 +6432,28 @@ uint ha_partition::alter_table_flags(uin already altered, partitions. So both ADD and DROP can only be supported in pairs. */ - flags_to_check= HA_ONLINE_ADD_INDEX_NO_WRITES; - flags_to_check|= HA_ONLINE_DROP_INDEX_NO_WRITES; + flags_to_check= HA_INPLACE_ADD_INDEX_NO_READ_WRITE; + flags_to_check|= HA_INPLACE_DROP_INDEX_NO_READ_WRITE; if ((flags_to_return & flags_to_check) != flags_to_check) flags_to_return&= ~flags_to_check; - flags_to_check= HA_ONLINE_ADD_UNIQUE_INDEX_NO_WRITES; - flags_to_check|= HA_ONLINE_DROP_UNIQUE_INDEX_NO_WRITES; + flags_to_check= HA_INPLACE_ADD_UNIQUE_INDEX_NO_READ_WRITE; + flags_to_check|= HA_INPLACE_DROP_UNIQUE_INDEX_NO_READ_WRITE; if ((flags_to_return & flags_to_check) != flags_to_check) flags_to_return&= ~flags_to_check; - flags_to_check= HA_ONLINE_ADD_PK_INDEX_NO_WRITES; - flags_to_check|= HA_ONLINE_DROP_PK_INDEX_NO_WRITES; + flags_to_check= HA_INPLACE_ADD_PK_INDEX_NO_READ_WRITE; + flags_to_check|= HA_INPLACE_DROP_PK_INDEX_NO_READ_WRITE; if ((flags_to_return & flags_to_check) != flags_to_check) flags_to_return&= ~flags_to_check; - flags_to_check= HA_ONLINE_ADD_INDEX; - flags_to_check|= HA_ONLINE_DROP_INDEX; + flags_to_check= HA_INPLACE_ADD_INDEX_NO_WRITE; + flags_to_check|= HA_INPLACE_DROP_INDEX_NO_WRITE; if ((flags_to_return & flags_to_check) != flags_to_check) flags_to_return&= ~flags_to_check; - flags_to_check= HA_ONLINE_ADD_UNIQUE_INDEX; - flags_to_check|= HA_ONLINE_DROP_UNIQUE_INDEX; + flags_to_check= HA_INPLACE_ADD_UNIQUE_INDEX_NO_WRITE; + flags_to_check|= HA_INPLACE_DROP_UNIQUE_INDEX_NO_WRITE; if ((flags_to_return & flags_to_check) != flags_to_check) flags_to_return&= ~flags_to_check; - flags_to_check= HA_ONLINE_ADD_PK_INDEX; - flags_to_check|= HA_ONLINE_DROP_PK_INDEX; + flags_to_check= HA_INPLACE_ADD_PK_INDEX_NO_WRITE; + flags_to_check|= HA_INPLACE_DROP_PK_INDEX_NO_WRITE; if ((flags_to_return & flags_to_check) != flags_to_check) flags_to_return&= ~flags_to_check; DBUG_RETURN(flags_to_return); === modified file 'sql/handler.h' --- a/sql/handler.h 2010-11-05 11:01:10 +0000 +++ b/sql/handler.h 2011-01-12 10:24:03 +0000 @@ -172,28 +172,31 @@ bits in alter_table_flags: */ /* - These bits are set if different kinds of indexes can be created - off-line without re-create of the table (but with a table lock). + These bits are set if different kinds of indexes can be created or dropped + in-place without re-creating the table using a temporary table. + NO_READ_WRITE indicates that the handler needs concurrent reads and writes + of table data to be blocked. Partitioning needs both ADD and DROP to be supported by its underlying handlers, due to error handling, see bug#57778. */ -#define HA_ONLINE_ADD_INDEX_NO_WRITES (1L << 0) /*add index w/lock*/ -#define HA_ONLINE_DROP_INDEX_NO_WRITES (1L << 1) /*drop index w/lock*/ -#define HA_ONLINE_ADD_UNIQUE_INDEX_NO_WRITES (1L << 2) /*add unique w/lock*/ -#define HA_ONLINE_DROP_UNIQUE_INDEX_NO_WRITES (1L << 3) /*drop uniq. w/lock*/ -#define HA_ONLINE_ADD_PK_INDEX_NO_WRITES (1L << 4) /*add prim. w/lock*/ -#define HA_ONLINE_DROP_PK_INDEX_NO_WRITES (1L << 5) /*drop prim. w/lock*/ +#define HA_INPLACE_ADD_INDEX_NO_READ_WRITE (1L << 0) +#define HA_INPLACE_DROP_INDEX_NO_READ_WRITE (1L << 1) +#define HA_INPLACE_ADD_UNIQUE_INDEX_NO_READ_WRITE (1L << 2) +#define HA_INPLACE_DROP_UNIQUE_INDEX_NO_READ_WRITE (1L << 3) +#define HA_INPLACE_ADD_PK_INDEX_NO_READ_WRITE (1L << 4) +#define HA_INPLACE_DROP_PK_INDEX_NO_READ_WRITE (1L << 5) /* - These are set if different kinds of indexes can be created on-line - (without a table lock). If a handler is capable of one or more of - these, it should also set the corresponding *_NO_WRITES bit(s). + These are set if different kinds of indexes can be created or dropped + in-place while still allowing concurrent reads (but not writes) of table + data. If a handler is capable of one or more of these, it should also set + the corresponding *_NO_READ_WRITE bit(s). */ -#define HA_ONLINE_ADD_INDEX (1L << 6) /*add index online*/ -#define HA_ONLINE_DROP_INDEX (1L << 7) /*drop index online*/ -#define HA_ONLINE_ADD_UNIQUE_INDEX (1L << 8) /*add unique online*/ -#define HA_ONLINE_DROP_UNIQUE_INDEX (1L << 9) /*drop uniq. online*/ -#define HA_ONLINE_ADD_PK_INDEX (1L << 10)/*add prim. online*/ -#define HA_ONLINE_DROP_PK_INDEX (1L << 11)/*drop prim. online*/ +#define HA_INPLACE_ADD_INDEX_NO_WRITE (1L << 6) +#define HA_INPLACE_DROP_INDEX_NO_WRITE (1L << 7) +#define HA_INPLACE_ADD_UNIQUE_INDEX_NO_WRITE (1L << 8) +#define HA_INPLACE_DROP_UNIQUE_INDEX_NO_WRITE (1L << 9) +#define HA_INPLACE_ADD_PK_INDEX_NO_WRITE (1L << 10) +#define HA_INPLACE_DROP_PK_INDEX_NO_WRITE (1L << 11) /* HA_PARTITION_FUNCTION_SUPPORTED indicates that the function is supported at all. === modified file 'sql/sql_table.cc' --- a/sql/sql_table.cc 2010-11-22 14:08:51 +0000 +++ b/sql/sql_table.cc 2011-01-12 10:24:03 +0000 @@ -4745,7 +4745,7 @@ err: @details Checks if any index is being modified (present as both DROP INDEX and ADD INDEX) in the current ALTER TABLE statement. Needed for disabling - online ALTER TABLE. + in-place ALTER TABLE. @param table The table being altered @param alter_info The ALTER TABLE structure @@ -4861,7 +4861,7 @@ mysql_compare_tables(TABLE *table, like to keep mysql_compare_tables() idempotent (not altering any of the arguments) we create a copy of alter_info here and pass it to mysql_prepare_create_table, then use the result - to evaluate possibility of fast ALTER TABLE, and then + to evaluate possibility of in-place ALTER TABLE, and then destroy the copy. */ Alter_info tmp_alter_info(*alter_info, thd->mem_root); @@ -4902,9 +4902,9 @@ mysql_compare_tables(TABLE *table, There was a bug prior to mysql-4.0.25. Number of null fields was calculated incorrectly. As a result frm and data files gets out of - sync after fast alter table. There is no way to determine by which + sync after in-place alter table. There is no way to determine by which mysql version (in 4.0 and 4.1 branches) table was created, thus we - disable fast alter table for all tables created by mysql versions + disable in-place alter table for all tables created by mysql versions prior to 5.0 branch. See BUG#6236. */ @@ -4927,7 +4927,7 @@ mysql_compare_tables(TABLE *table, } /* - Use transformed info to evaluate possibility of fast ALTER TABLE + Use transformed info to evaluate possibility of in-place ALTER TABLE but use the preserved field to persist modifications. */ new_field_it.init(alter_info->create_list); @@ -5207,7 +5207,7 @@ blob_length_by_type(enum_field_types typ semantic checks. This function is invoked when we know that we're going to - perform ALTER TABLE via a temporary table -- i.e. fast ALTER TABLE + perform ALTER TABLE via a temporary table -- i.e. in-place ALTER TABLE is not possible, perhaps because the ALTER statement contains instructions that require change in table data, not only in table definition or indexes. @@ -6102,7 +6102,7 @@ bool mysql_alter_table(THD *thd,char *ne } /* - If there are index changes only, try to do them online. "Index + If there are index changes only, try to do them in-place. "Index changes only" means also that the handler for the table does not change. The table is open and locked. The handler can be accessed. */ @@ -6110,8 +6110,8 @@ bool mysql_alter_table(THD *thd,char *ne { int pk_changed= 0; ulong alter_flags= 0; - ulong needed_online_flags= 0; - ulong needed_fast_flags= 0; + ulong needed_inplace_with_read_flags= 0; + ulong needed_inplace_flags= 0; KEY *key; uint *idx_p; uint *idx_end_p; @@ -6135,8 +6135,8 @@ bool mysql_alter_table(THD *thd,char *ne { DBUG_PRINT("info", ("Dropping primary key")); /* Primary key. */ - needed_online_flags|= HA_ONLINE_DROP_PK_INDEX; - needed_fast_flags|= HA_ONLINE_DROP_PK_INDEX_NO_WRITES; + needed_inplace_with_read_flags|= HA_INPLACE_DROP_PK_INDEX_NO_WRITE; + needed_inplace_flags|= HA_INPLACE_DROP_PK_INDEX_NO_READ_WRITE; pk_changed++; candidate_key_count--; } @@ -6146,8 +6146,9 @@ bool mysql_alter_table(THD *thd,char *ne bool is_candidate_key= true; /* Non-primary unique key. */ - needed_online_flags|= HA_ONLINE_DROP_UNIQUE_INDEX; - needed_fast_flags|= HA_ONLINE_DROP_UNIQUE_INDEX_NO_WRITES; + needed_inplace_with_read_flags|= + HA_INPLACE_DROP_UNIQUE_INDEX_NO_WRITE; + needed_inplace_flags|= HA_INPLACE_DROP_UNIQUE_INDEX_NO_READ_WRITE; /* Check if all fields in key are declared @@ -6166,12 +6167,13 @@ bool mysql_alter_table(THD *thd,char *ne else { /* Non-unique key. */ - needed_online_flags|= HA_ONLINE_DROP_INDEX; - needed_fast_flags|= HA_ONLINE_DROP_INDEX_NO_WRITES; + needed_inplace_with_read_flags|= HA_INPLACE_DROP_INDEX_NO_WRITE; + needed_inplace_flags|= HA_INPLACE_DROP_INDEX_NO_READ_WRITE; } } no_pk= ((table->s->primary_key == MAX_KEY) || - (needed_online_flags & HA_ONLINE_DROP_PK_INDEX)); + (needed_inplace_with_read_flags & + HA_INPLACE_DROP_PK_INDEX_NO_WRITE)); /* Check added indexes. */ for (idx_p= index_add_buffer, idx_end_p= idx_p + index_add_count; idx_p < idx_end_p; @@ -6209,57 +6211,59 @@ bool mysql_alter_table(THD *thd,char *ne { DBUG_PRINT("info", ("Adding primary key")); /* Primary key. */ - needed_online_flags|= HA_ONLINE_ADD_PK_INDEX; - needed_fast_flags|= HA_ONLINE_ADD_PK_INDEX_NO_WRITES; + needed_inplace_with_read_flags|= HA_INPLACE_ADD_PK_INDEX_NO_WRITE; + needed_inplace_flags|= HA_INPLACE_ADD_PK_INDEX_NO_READ_WRITE; pk_changed++; no_pk= false; } else { /* Non-primary unique key. */ - needed_online_flags|= HA_ONLINE_ADD_UNIQUE_INDEX; - needed_fast_flags|= HA_ONLINE_ADD_UNIQUE_INDEX_NO_WRITES; + needed_inplace_with_read_flags|= HA_INPLACE_ADD_UNIQUE_INDEX_NO_WRITE; + needed_inplace_flags|= HA_INPLACE_ADD_UNIQUE_INDEX_NO_READ_WRITE; } } else { /* Non-unique key. */ - needed_online_flags|= HA_ONLINE_ADD_INDEX; - needed_fast_flags|= HA_ONLINE_ADD_INDEX_NO_WRITES; + needed_inplace_with_read_flags|= HA_INPLACE_ADD_INDEX_NO_WRITE; + needed_inplace_flags|= HA_INPLACE_ADD_INDEX_NO_READ_WRITE; } } - if ((candidate_key_count > 0) && - (needed_online_flags & HA_ONLINE_DROP_PK_INDEX)) + if ((candidate_key_count > 0) && + (needed_inplace_with_read_flags & HA_INPLACE_DROP_PK_INDEX_NO_WRITE)) { /* Dropped primary key when there is some other unique not null key that should be converted to primary key */ - needed_online_flags|= HA_ONLINE_ADD_PK_INDEX; - needed_fast_flags|= HA_ONLINE_ADD_PK_INDEX_NO_WRITES; + needed_inplace_with_read_flags|= HA_INPLACE_ADD_PK_INDEX_NO_WRITE; + needed_inplace_flags|= HA_INPLACE_ADD_PK_INDEX_NO_READ_WRITE; pk_changed= 2; } - DBUG_PRINT("info", ("needed_online_flags: 0x%lx, needed_fast_flags: 0x%lx", - needed_online_flags, needed_fast_flags)); + DBUG_PRINT("info", + ("needed_inplace_with_read_flags: 0x%lx, needed_inplace_flags: 0x%lx", + needed_inplace_with_read_flags, needed_inplace_flags)); /* - Online or fast add/drop index is possible only if + In-place add/drop index is possible only if the primary key is not added and dropped in the same statement. Otherwise we have to recreate the table. need_copy_table is no-zero at this place. */ if ( pk_changed < 2 ) { - if ((alter_flags & needed_online_flags) == needed_online_flags) + if ((alter_flags & needed_inplace_with_read_flags) == + needed_inplace_with_read_flags) { - /* All required online flags are present. */ + /* All required in-place flags to allow concurrent reads are present. */ need_copy_table= ALTER_TABLE_METADATA_ONLY; need_lock_for_indexes= FALSE; } - else if ((alter_flags & needed_fast_flags) == needed_fast_flags) + else if ((alter_flags & needed_inplace_flags) == needed_inplace_flags) { - /* All required fast flags are present. */ + /* All required in-place flags are present. */ need_copy_table= ALTER_TABLE_METADATA_ONLY; } } @@ -6413,10 +6417,18 @@ bool mysql_alter_table(THD *thd,char *ne } else { - if (!table->s->tmp_table && + /* + Ensure that we will upgrade the metadata lock if + handler::enable/disable_indexes() will be called. + */ + if (alter_info->keys_onoff != LEAVE_AS_IS || + table->file->indexes_are_disabled()) + need_lock_for_indexes= true; + if (!table->s->tmp_table && need_lock_for_indexes && wait_while_table_is_used(thd, table, HA_EXTRA_FORCE_REOPEN)) goto err_new_table_cleanup; thd_proc_info(thd, "manage keys"); + DEBUG_SYNC(thd, "alter_table_manage_keys"); alter_table_manage_keys(table, table->file->indexes_are_disabled(), alter_info->keys_onoff); error= trans_commit_stmt(thd); === modified file 'storage/innobase/handler/ha_innodb.cc' --- a/storage/innobase/handler/ha_innodb.cc 2011-01-07 11:54:07 +0000 +++ b/storage/innobase/handler/ha_innodb.cc 2011-01-12 10:24:03 +0000 @@ -2574,11 +2574,12 @@ innobase_alter_table_flags( /*=======================*/ uint flags) { - return(HA_ONLINE_ADD_INDEX_NO_WRITES - | HA_ONLINE_DROP_INDEX_NO_WRITES - | HA_ONLINE_ADD_UNIQUE_INDEX_NO_WRITES - | HA_ONLINE_DROP_UNIQUE_INDEX_NO_WRITES - | HA_ONLINE_ADD_PK_INDEX_NO_WRITES); + return(HA_INPLACE_ADD_INDEX_NO_READ_WRITE + | HA_INPLACE_ADD_INDEX_NO_WRITE + | HA_INPLACE_DROP_INDEX_NO_READ_WRITE + | HA_INPLACE_ADD_UNIQUE_INDEX_NO_READ_WRITE + | HA_INPLACE_DROP_UNIQUE_INDEX_NO_READ_WRITE + | HA_INPLACE_ADD_PK_INDEX_NO_READ_WRITE); } /*****************************************************************//** --===============5215397303848668170== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jon.hauglid@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jon.hauglid@stripped # target_branch: file:///export/home/x/mysql-5.5-bug42230/ # testament_sha1: a4cc8c6a63fdf48b9440d3b93db5787a42c9bfd0 # timestamp: 2011-01-12 11:24:06 +0100 # base_revision_id: dmitry.shulga@stripped\ # jezu2dbglg05g6ff # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWctzWe0ADE7fgH/wf/f//3// /+C////1YBmfe5q9d3uy5fex99qqO+e93vfe+u3q6S1kUJtTO63qbZRXTcEB72D0KOm2Dp0b0x3Y Ub24p1vdnIu9xtnTp0eGKKn6p5T1PUeoB6mj1Gm0ntUDQAMnqAAAAAACUEAmgTRGgaGqanlD02qP Ueo9QwgxA0bUaDIMgSQKaImmKe1T0nlT8lPU8psp6gA9QeUPUDQekeoAAAJNRIhGoyZTI2obFPEm 1A08U09TQADQaaNNDQyARKgmapjQ1T2qZGDSnqep5NTQaDENAA0AAaaNPUCRIIAgBMQBGmjSnimZ Cj0jI9J6TZT1PKeU0B6gwOaYYsSE0OOmP4k/5aER8EeEHl2dMcP0JLIpRMTBVIeZnV48+O32zLUu mvJ4vLtmvMmlNKeKuVePBEKf4XM/17tTPb179vW1Q0QyrpBHf33Nzyi26I459vk4fkwikNmhTyM/ Wf7PpngAcy2UvDzezlLnvLAZItUsMl+ZPIy7Mypd9MBdf6SgjTZo4adM0K8QwasLx4IOboAP3qYx mYmGhZZSTWd8e+kgjkxrTEdLKi7THSSbY7hWbJKZ+mH/Wgi1CRWqLjAW2gimHjctWL9+Wds8HZ07 ouY52aMlrcNWirjniJMjXYRMxg+ffWVnrtMpmx+fR0cCm7V2WUtzEkqu5iSSvBGKYDEBVgKb4lKJ JO3vw3pxwOTGqyR18MEDduHNUFS2lCqB3GmeJ/YIuV7w6GHPcgm40xNkGuDOCgkkAkkCCkQtzQwL 8PHoSA99ZVqJsd15lgy3ClWzwY4jF+LneVEZXemzOjo7ri91uLDKcST3wwzxsWI42otnbFbqlB/P S0SyLGDNRjJUGWa1Wy4LBQnFiVmTJRqsy4RaSzwdnZYwwrCsI+4F4d5o43CXQeHKkrN42Ntkv+Zl fAwL/eeo7BhJ+YcJckNo8AJnCaDZ2t85r89lXUplVQ8lF3Qoo89F3Qr59J1A6oh94bJ0HMaTrGnn 7vc/zHwFo52i5MXG3QEjqCaRpIC0yDelwSmFUjL93Ii5ERhUVJYBZgSrSO4ou1cxaGG7wYDJV7j7 rtMTChsakODQ4Q28bTDUbqyub1xVYw5BJgdGRHepDlWra3YywcFiY4RJmRS+RsqdTRIuMHVZQYK4 fyyMrs6XoBsT3Gy/hZUlBejtir6MBf0GH0jLgRZnp35mWhUrirDZ5py2Awqba4zL3uaUcUxZrvOR 0IxBT4QZjMf3oaTjaMM26+r8y+y/JWVFp7yFhgV93DxWWSxKm9yNwbJmL/BRIJAkbzqpLmf0KW6i uca8PDss2TdVwSJO/3pelLjRG1qmV9Bcov5vcqyz21UMGSkGeow3d1Mdy9s2pMOAdViMOps8Ui+/ ULC1ZVIY23fPWrdqW1Ka0DMYFbY9zthl+vvDZIlZTSkWKWfQNlCCvuJ4UJJRW6gYUUkCgYM7m2bu 7bwJcNLL6ERjjQrqmaMjWCgtZaxrSj3gNp0qxjMo+QNjDdhEwg2+MYJjC06w80khwjH7obbZ5GUs k2aPZv0ehOQ/HZcHnOQz0NPdHz2rrA50ztospc8px03tOpFnSPF69u9DrE+j2+34D9HssY+q+MT9 cJCnW2qTV0aQbxUYTIZLlCSaw2UAzEsRSSKeEbiod5IZxY1vXINgFQG1FUWMERixIqIsRIwVVFiI DyFECjcO2sp6hKTMjaiTaYErN3OBpxkg7u7y2k7u7T9XlmjRoI+gbY95APmQQiZC+/OCn5WKo5n8 On4nfX3dByIY4QT8sFh+dzMoz4hIxXRDrsNCigjyBSHNWHrJMgEoFXVobH4s4rcl8x86cUEuKfyW p6MCl9D1ekPf567oPUKDpz2dGn7C9nqbvX2j6etyw+RkUBYoAsWHRO95CeIgawxOaCnQeR6WVPPm PFOLvVUdiod47WsPWX9k6b14iDM5m2diSwBdTKLEsOslq7co1XUL6RdxvdaWUzVQzjdtUdPoPQat erYpzMMMgWw6+Q8CohGWJ1LYuAEB616vcejAV6z2TJXzFJM9u12JI3d9qCnV69Au+yZpM0DJzlUr itGrQCTRUhCuEwkMkjQmQ7AFG4uAICcOspyghteJM8cBmCRVLyHrKgylAiFhIARTEUv0nSZzCr+x o5U5ABYwLl0chmvKBa1J1Qk5CUVBg0UcVJPto+7KYBM4YDgYwPUXml/ACx52W7AYNpAz/CCiAkGR KO826zvorqmLATooIRKEdqVknSegtX7mAA0m1LhnCcMI1zOUWvYqM+eItRSRDkBENhSJ8euRSlST 4anLrDeU/JUAKSHcRdDbvf8bQ0El5eV0jrBqNB12MG+pOBOAe4XWCoq8iCPQcEcTluLjroK4uJGB 7ufX2dpsIdmx2Yac4G8JTsboVawlptM4FXE5cUREnvtupsOQ5bICM1ne9iZNsNDsZwfxFWUMDDzf INTaW8AsMHjvaypiKyqMMGSRYzLYq8ClMDONNyzyJnXJBtWr5n+SNhHo7LJmo9K/XlqMT3nkf+HH augjhuG21c1yjTPxiimagCznJtcqlAvJx1lm9JJTsatyjlXLHGvImYUo/d5rYRzkdaKPMW/ZAgQC 2go4oxcqYOmySMjYidJ1wRzgn3/t43LyfHo00YWGkKKq6cWANRSM0ClBfBYQCPsTO8rY3copnAZd M699bC6WdmlZkSOietlwxWjILFzSZdibHBIkhUzJ1FMe1GAPvEZxuX0i20mssFzn6crUnM8xCGEK VU8kj7yTbFjoLKaOKAZeIBfjrJZo7lqSRzoGlqNoFy3PpTqFLixHTtHKjilyydPzlqGfadKcMyrl 7i6LQ0VozMiuw0AmFWA4whAcxhY1s7CiKkzJo7h3mPX33GJ4YS6Dz5rMoacgx2rnrAwLTTua0Fm0 0D4LwJKoB8stGh8Mq8dYz2hyLWHiwTUTBI5HFkLL21jReZFiaFY03F3gKPiDTK0A4HWcBSRMcp1n qPDOF81mtHIx3wq1eeooOAwOIAgyVecVjaMSr3LeLNeOayZxJOo9SFYnDeOXKnQdffulpPTJqvni sVOKUDQ7ZhuDaYwaVswSOyhiVRVUoE58mDY0mWIbzMzLxmJaZb9yR85whKwyIDEyJPBca0kNm961 2va7QjRqmlKswHa6pYIKXO9TK8g+UDt+Shgj2bqTngyGJkZgcVDQ0MSgrWX4yygk4hyrtsu7yFKX MuT7gW8F7STeT7e1JFjeZCcFRRVWQ/XoGGnPwQ3NRwG1SqMZSukkII8zMgNX0Cl4eSOC8b/cYHFN AW/vEI/egFAvYi8ij2C/4LYhxkZEjE7CHn/MfZGn5ivFUdUgUK2YcIY0MTP9BCJVQL31MQG1AfLW XDYZlCsFuFrKh4Q+G9RJWqWIrgXhWQNIpeDSIF6jimLAewxqeMwYCwpQLJVeQQCy6GKqkJcAIsAW uEglRIGZFQS2EyLShJArUjHMKhB/0dfWJIgYQUor+PoCgtFec3JVlLRbkHT5bBQ+yL+UunIRcwuI 6CskAggW8aAVSQpsNSET6QmhC4iVteevXJHUx8yB1A4FBYFaA7gQt5VDUZDcK8HCeZXuDlSh6Bf1 AbShFV3kS8uHfUkQSxBbyWkCxacpvq3EADzi6h1FpUiKzdiItQE0ApiiQkQF6EYglISZNIbx0wvM V7qI6FcQAvLhzqVOkNFg6wtF7ppO9iBaR0QyZgGYMDRQlbAIAWF1AGtW5UxN4cRhYFhUlw5gQoIV DBA7YLLDOcIVi5gM4RhDsG4ivAg3jYLNJZFgWYHTUvlABYhCYajMSA0iZmBEITVuEms3THYeIq7L pq6q6Wlu6LLu7KRurW6ou9cAmntEP2SQwkDBlYsRYi+pqkJs0BMYERGKswxEQRRiLIgKqoqdHLIT nDw+z7R9Y0oqoqIqq9+ZVOaoVMTIpPdlkJZAYgfHFfNV+8SwETi4mC+KT/OFgBVCC8A7TqPgffFw JqbImA/qPuyEtBp96ZjEDDE1mB/NWi/xP5H4fUQbjTsaTzhSaDe5MCIUaTE27xCLN6BWZH27Y2CW AK8C9lu9qiS0HHlXNaUrENLNgQgkhAfefgHyFnUOZuvIYNh0r6AP3gpVEWfhIsB7BIzgCXSIGpV2 pSTSOVd40e/mksjbsJBkHYGSZmFBbM1cZm76i+1IdwlgrY/EbcPVMmyg8aLGUiEnEYgv6rIgXBD6 LOhgaB7fjMTaaFmSNvbykAff5g5cVoHObUYnjO9c5hDJDMZvkSGsDSJdUj8T1E0u5lparbR+opJy SUMBeY+RMQbPtjoaFt8TID9zBBEg9eRASSa0B+IZHM/E9+jT0z5HXSp1LAuT3Gzew3NBAYrmcz8k HJa0L1DRrGIWy0iN2RuKmPqY2+EaEeaQuRxQgwdAxwSCQhyhEMTQmKyaBWntKzYgzuQHKKSAXLoD QcR9EWQcczbHlimyu21ALO3OowemgNkoYRBB6xyhJpDiFwTiQQEMYwiLJeuUdaGgr1HoM87rBt9J 1noM2E8QO0uAmSyqAFRLJSQkyYpAtJ51Z+nSpgHhzymkYEHguKwS1jX1MsafaWy0kJHQCcYP3Ntb iq5O04CsLJtWo3FecinR059dndyLAtyPgfOcKIUzirgO49h785m5LhcQl2ciiPaX83v1+hbJhB6R sxBRYfFKfprcSG3K0sjxRN1LooMcoVqJF8xRcOKQRb7ItqXLAcyxDMCjUa8oSrIgPgojEWwUiQHu SpUpRV1x2uyiqO5LNFo3biZEKMYLLZSjqhICR5eWHEg1HtPiVq9fD9hInxLz0jNJ6zdp2naLiLcG JxaoyOF4K1bcECoSsXaWi4wXeg/IpAestOhEOmaIJ8XcKGhBS6oggxl+GWJm0IB1N38rbtS3hMqt l7tU/hKkp0vnSbELcsBoSw2+WoAOvoZwFmrwRJgpFoyu/7On15eDv/WDvwu3ZL+e43UbGEValadD Dz7KheLsSPJBLqTQTGKHaoIAqXwEzBygwgl2xNSaz75yaRQr1s5kvDJhFQsvJUWyolysY2dnboiC +r7l3ltJNu+RBqnoC8NmEDPsQXoJnDKMEAnwGUpHhWodBnoBScrpOdReheAi/UfotRw9sCPtYQNp ENQe/FeQEMqdB5HpO9MmV9h53HQbT5XAi08g9xKXofYMRr+cMDvm1ZDoZ+w4d/lGpdIMaj2wqCCQ T/kgLuR8kTEuIeAqCJK09Ej3UXq+JP2/tKs3Gt2ne0FWJtIcHSda8pfE717LRIqrQUDAnphASWBi e8gkg8VgCIn2r4dYbyhekheYxLwNZtVjmJ1AwthsT9B1jGJcJTcJfCdJpN4Qog6pM4IaJDZkCpul gTmGA9bf0SExm0My5kc0wA6ITMwcdIQMZ0iA0Gyolc4CHWRjQcOAA9/Gqa89lt3pBATeG/gqOgy+ NChIJi0NYN5nqfREIbSsQaq9oz5rdR36jWkxoXeeAd5ZyJF4SMjMNozcw1NLsHjqOwkdiDoUAhsa 9kBIqC3dCtbAjCt90EmW0y0c44SWzWlHZAW2RTJScRpf0Dx6yQ1YFojv1+tz9qvBVNkkiCwFGRju HUOHtTj0G+IiupqKoqmtLiXIGfIp2dwdwRzDkIM2FtBalgjerXRazrPCBS9USXV8ug6jqSYo5+Gv o3CDpTQl7PM2kgNi7UHp/bQ9c0HUc7ugvLDh7TxJiorGNszjkiDWGkAuQUvmiojiIRzAQXzz/hh6 UKe465sFiRiwR1NMmmBGiKRkMqKNeujMISIfoJVXyPYsF6dh8ACwKAh5hgbDMLGNn1kRlCN+Mfqb UrgGLkoEVKmxHpNlx9FEIhC4lfm0kzoGIZQ8sJ/7HTjuKuMOEvPeSi+sMcALXsAOuu0yVyAoF49W RyF+K3wzUDdL6d26RFVGeRNSqU2TkAYHilixe3C6I0AmpFFumRYv1HyAMcNSUy/9mUYIxymxgxJD nA+yFANBgc8yVLDvpCgJPgEJmaSQrEvWRlHSBlRlCsEZKuAkdDAC7xIIVCXJKBgb0kho9H36k8v9 20neHHm96ryzopvY2IamElDBLBGKCFs1HFHE9tokLWFglcsDWIKTJEuJrZtkmCE4Zrmg0vh2uqyY QUhSGgSCHf7hwPB0QgVQCw22HFOnDd55JxxKDEENR0kIIFYvWVm1TKJZaWU1FLTmtog6ZNv/tmiB MNRQQO5ICTKQiBhCgndQxqz45nImtLqH5pDEwY0JpeV+GOaLBgzs8CHHYcLjOYwvIgTiULLroboH AiVSZbDfR5CfNV4LC4iE2FQZ25Xtv1kNw04816kscfcxMB8E13KiX2Wai9VZpVgd5pMUjyANQhaM heTbxy/qJBIzWZkpqQfuGtc6d53UsMlx8DXmgOc5dqZGOA2CBuzeNW1JeTUQVew0KqR3miJ2RqMY DAmsEk6dqp6GQlGmWNFNBKW8yneJQjawOImdQjM0SA8FeMLkmwJBTaXU0mOIj7eVbksDAeVm+QT1 H3BrR5hSfgNKA5qiRYf2LYHAdv1mWU0y0DDBb8JEoUXhIKuQtLJLuOvr9rIK1VpZYNx70jjixMtW wGZcqd1Tu1OqGpSDosxNTEDPLXbHkinIL6zeQKi+hfjwEvaASJ3dBArgh6eeu4gVLKc9j6ZTHzJh 09wpnXRYtGvAmFWsRUSO1WLAwjodyCJFguzx3JcHabzgZG0eVHpPcj+5tt7S2aYEoILZnUmG7YQC uG9lCQsUUGjcglBFnj9m5LSJaS/Mha74OGgIch07ZglMaxlRSq7qtBIho7uwaTd2SrspGZZSJNCA 6Xo+EHNBDiDAGDQZnz0wQ2ZYBaKftBOYlaYtB9P/EKSbAeQJ11NXWgaNBBg6vFhpd7vOrDx0lOmF DQaDUZJU1iZCEeHBAufDGHIyAWwiD+tW6sKadejpJiskM9GnYkiQwRBCKIaYx2q+7xyQupi4IQW4 iVVhSB5J4BIwYEWYpFGrcUtZyEn04gHsKykxDt5zklHMMLTUCCPNPuXzbAQ6LmhfMtfMqhcmlpPa cDQroAqq85ktaOtImBVLUVNDGvFEi7yKHSfNad4mu02HZcNaU8E3IxQ8jHk7UIoECcp0AI/IZ/Ke R/485kBk+LvGxmw84bCQwqJMOIwyALciAhh2G4l6rKLe02J9QBI4E0lyOd1xvlEAOcosHETUBY5x JUnAlMbYcAxu3/OdZvAmlgMuM1NJ8/V7RdYYrjfQeN/vmTSJjJIQhwD/4u5IpwoSGW5rPaA= --===============5215397303848668170==--