From: Jon Olav Hauglid Date: January 26 2011 12:08pm Subject: bzr commit into mysql-5.5 branch (jon.hauglid:3277) Bug#42230 List-Archive: http://lists.mysql.com/commits/129650 X-Bug: 42230 Message-Id: <201101261210.p0QC6FmA024423@rcsinet13.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============5503086226342593983==" --===============5503086226342593983== 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:anders.song@stripped 3277 Jon Olav Hauglid 2011-01-26 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 to 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-26 12:08:51 +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-26 12:08:51 +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-26 12:08:51 +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-26 12:08:51 +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-26 12:08:51 +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-26 12:08:51 +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); } /*****************************************************************//** --===============5503086226342593983== 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: 46bda9127067cd19750b03583269d7221b5e09e3 # timestamp: 2011-01-26 13:08:56 +0100 # base_revision_id: anders.song@stripped\ # rhsv61qa4oarwxoo # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWU3nn8AADC3fgH/wf/f//3// /+C////1YBme9u+Pr3m6d77vRyqN8+d6buW3VTdubDVRBHX2k2AosMaO9yXXrOedufbj2b33Nxu0 zaBHLbbLub1vu71dg329HCSJNJiT0NMQmCmyYJqm9NSbRHpMhpkADIAABKE0NEAggnoieRG1TbUy jyamQaAPSP0poGgGIDQBJ6p6g1TTQZAyHqGgAAxAAAAAAEmooQjQT1J+qep6noxE8ieo0abJPUyD QGgDQyGho0CKQgTI1NqNMRpMptQeo0NpDTTQ0aNDE9R6hkNBpkEiQIBACE2jSYmqn7Siep+qPeqT 0T9SYEZB6mnpNNDRpl43wKMJHHul9xTx4pLpTnkdHLulr85NzlVTBBQqkPQzb5MuS33DHQuevN5P PumnIDPno8lcy8mBiGf92vn8ey9+xZ47a/G84YEXsVUihDlArhLF64kCB/vOAe9dij8GePUaes/4 fXXAA8pfOnZ8Pt8lPLwLgZotkuMifWPmZ9NCpf9kBfh8CgjXbVy165oWAhg1YwHiiRtmBLozOltb 0MpGFarZbLPvtUKM2WyJK9peBjqJNsdwrbpKZ+7b/DURchIrVF5iLfQRTH0qt2+P9sjazX29rhLB jSdGczmeFeRpVWFHGCsy4Jn4b5Y2jGWRuf3urq5FOGrvtTyv0EkrPBiSSxBGaYDGAqwFOMSlEkna 44cE5YGzhoskdPYhA1aiBUhlRgmJ0GgwX6T8wjAn8AYnIHtQsJgpXmF1i1iYZmYGZmEJmQ3xc7Kz cfqkG7d1Nu25+i2gpxZdjSsmeLHKMn8HPAsIyv87WpTSWLMXW8saXUIWarPehQe9WM0TKdzKQumc HGBG7IlkGYyCHJ6PFqTCuMFcIWjIviHjWFbGMGqSxvnfOP9AXj7zV13iXUePZSVuI2Ntkv4TK+Ji YfUeh3jBL/cPAlJDcPtBMoTMbOzxHev/LKupTKqh5aLuhRRfWxKTCqp9sGQZih4BzHsPwEG4j1+r u+5/viacoHjgeK3EJnIFFN4kFpnDhXWtArUz/w6iF4DQmQQiYE9BxmLoXpb0qlA06fPQjk//D+Pi pUhGOx1Lauw4tF1K3BXq7919lcW4cwZoHuQvRKDWKtrhlLFwWTHCJMzKYSN1Tm0SLzF1WcGKvH7Z md+lMEA2J8DdhytUlBgjpFX1Yi/eMP7Rl4Itpr46GepUrkrG75Jy3Awqb65TMHwaUuU5WdexDzoc AR+IKpkfdqrnjWVsuqJxkn1ReeCFa8B7jAst3HsWmazLHCRPEGzZjH8kI6OUOB0xTtP8E42K6Rtx 8e+26bquSRJ4fUl5pddEb2qZ4UF2Rh7pZF8O2/ZQwZIdpWGG6+ljrXqq0VDiHTcm/S2mSNe+wmFy zqQxtvCe1Xb0t6U1qGZQK6z4O6GYbPEjRK1KpE2IvLUN6g6y3FcIKJCt0gwopQEYUFOxVU49nDnJ c+ql5jNjjMps5pkbgRiFIY2iJYuNrzqxlUiWYNk+7CZg7cJzdMnvWz+aiPxnFO4N+/PmGaJLUR9f e6FsJgLwNQ8izMBFsNHvfTWGLDZtnrQcRrvSYLHlmJ3go5IT57EZRez3+/vPN5KCnl0UxPVJKNrb 0N514OfqFg9G7G8+DoYwaOUMgLQk7JmZk28ZU5V6WESpmNcFyDYBUBtRVFjBEYsSKiLESMFVRYiA 9oogUdU7c2T3TCIqockHORYdMvBgNuLkjw8PfVzw8Nv3+95mug3zCqx8SAfuIIRMhf66QU+6yqOZ +bt9jxr9XUdiIoW9/01+Tz9/7m7vTc0ZckJ5khb1x+A/KTkvR7VVqnOFpKz7z2aTXEp7j+CdCCYi n1LYw8+JaWp6fQHf27bnlYGD56YZs/1l7XV17h9PU5oeJkUBYoAsWHfnT8g+AQvhUamEjafCt6Z1 1yne2bsedVJIOwrsAKg7DHupX4Gh2G/CySxFkmLtZVZlxzJbO/SNl9DGkV8IrabU50pJwP8lKHx/ oigb+O3rU82MaBdZ+LobVRCKeg0J1QA4di39p8FAp1TVIQxyCgmOVbXEI0vRXG1BTb10C8bDBMkm TCm1cmpVK3VvhYGK0ghVExIYbEwFEzmSD8RgYBJxOlCZ2WGYHLrMWJ4kJ7pgE3LBUOfASYFloboG fI1mOFLvpHkpFxefH3mlSYLqUz8EkoJJioMG1mGcZG48dKUAKHG5IDNz1GGCAR2gYH37XbgYNNIO plJB56E5eRw2nlXCpo0CoTH31iidB3FrY3AGpjsOEkQNFq+HK+p41ywLDS1zMhkoP1gTDgLM/Puz Ii1Vq+G405B/RKloZV4svj1yw3TCc3Ns2b3CFZ0w3Th0IL140RoHeJwgoqvMObTKjMSRHuGmIolw gWqg568ubCIy1GK7ftc4hStjc9WilDgR19CIiUnyfpZwC9nO15PVmlI6pOUMzCLcStlNMGQ7rBsl eMPB1a6DhsggqmwB9lIMzORa8xjdqssMihzkg2rV/Y/uRkH2OVH+NfUx4jgcS2pWjlRazMp2XS+G TY8VIS42VRlRzn2C4/I665QkljEYqxBx6hAwLvWt0v23w4000MTqTJidxdOhykyYVMyQqMQWLpJN 6RU12H3mogcx0TTEAc4p8f/efU0pLftuUWWld6wy7tiRekIF7nXIKfKmc/JaMxxoLAHMVL81T0RG hhbYpaiUzroqjFwGQVWKTMjacFMoMNC4sYdSap3FIxNdV3u2CnNT49Mq0PKSkXQB6xdLcHM471nj JnEzZBlasmyu2ikcCLbCNQYF8/DiahmRUknQOUHcYxOPvLQaeI3pxzNNXMshXWDVWnUzL9xdIKFW QAXBAGi4zJCMWl1jDBrmYV4J3FEu8fb0uWB4XR1nr7liUNMQefuA5ZFjcsjaZjy8CS9urPR4PLU6 SikEZcAnEoJEEt+BVeydFtqQWDcapU8VDGb9NBiaZmCysYQBG48QwpQqSOk7z4tcsdV1gZrScpTx yRkrw8qO4ZEGsi84pzKdIo6KRXggGUDGY74DVJWHtM4byRiZXOJ5Pg2rttpe0tc1mpzpAbFA3Boo aRaUxzqMSqLIUmGFZqhsZuOWJ7jImQKYE4OW9EPYcWRKmIwWMjCcQuWullOGb30ptdwRprmlKswH c6pYoKXvBTK9Qe0Du9lDBj5ShL5oaRNUKge+BwOBpkBGRL7iRATPeQUr2TiImFFODHD9gLrBOeR+ ryeKSGY65sGGhUUVVkPLzjDPl9kNt50NUNtjTUNu4UII9TQgNnsKXj6I5L4eax+EwULfrET60BkL 6kXlUfUL/otEOQhWDGHpE/r9CdJNHlJsEyHKQJUz4A7yjCBj9hEnWg9NZgA1ID5Jlg0K1CYLYLMk PGH4WqJE1SiK6C0JkBnFLQbogGeQmqGqIT0mqp+pgYBYUoFkqvMIBZdCCKkJcAKzATVUgXjICbRk EW4mRcUJIFckZaBAgX8Ra/UQ2gNGJF6vX6QuFuV5zmSWqKhbEHP5KCh5YX9IewQtQuQ4EyIAgQeE hAbyY0gNKJTcFER2i218eWU05IH0IHQDoLhYCaA8wIVcqhiahsFdnGehXaOq649IvmAai4hVcyJa WDrUiEEogtpFRAUW7lNathAAf2LiOJUSRGzVgJagUQGgymKSDMiYAsxYKKNo54LTJeJEcFcgAtLC xSIWYZTFrCgvsNh9rJC2EIk02A2F5vVC2XBMBurqAN5WxUyMw5DBQKEksGsELhCQwIHCCxQvOMJi 1gXhDBB6hsIV2INo0HQrnLAsvN1ZmnIAsRFhsNBIDWJmgCYIV+0SyNxPi/EiUiUOLqrpaW7osu7s pG6tbqi70wCZ+Eh+ySGCQMDGxYixF62iQmzQEwwERGKswYiIIoxFkQFVUVOjmkJzh+Pt9w8Y0oqo qIqq+GY1PeqFTCYlJ4GYjMQhgDwLmo19Qt4lJXFAe6seaTeBWiGYA5jkOk6pXAw0glQCO+V4C6D7 O2HkyP1fLjMqC41GgvGw8h198kcJp4IWM8mcIa4nAEQo2mJw5CEW5CRbM1iV4K4C5leLVElmbeKv B0LYkLhAEkJogdR1h3R0Vh+Zv5iUBEByPeA8IM6xLOuZYEb4pokB9DTjEIaWalmwpts1kJ3OJXQa uAmHO4h2A0MGIVjqxS5oc/4y8IgthExSG/kNvJWTRs3lOFnSg9JCMKpH+ShIOgEbaIIumAO/0lpn MCpSM+XcQA35xyprGbTceZ9q+0shSIIP2QrzUJc5Ez0JpeDOstYfoTk5JKGAvAfWTEG/9UcmhbvM yA+jBA8A4UjhBJleD2BdpMp2l/IZ5e05K5QMrDDawWMgcNCzGU9qDOsKFvGQyrHEKuYd7aS0lMvR jb5RqR8khdh1oQYugZYpBIQ5QiGJowNlBS098maUGNsA6oUiALFwDA3HiaTYttVRrvamyu+5ALS7 SoweugNkoYMTmG6kZBqpvRqwoKRGFVmvmuuGDAy8uYzTS+x2nceRownkB0MMAO6pTS4AMRLRSRYK DMHSdquDz8jQA5901MxibXMukh70FcMcxqtpvElHAE5AepqyaMKut4DWTCkcCudRC6MkbN92u5qz FwJqTxHccIoUhMBrOJzpkLEsUw6XVlIo8JRmtq2rDEHNwzHQChC4fYlHdGcgKqygq3nQdaFmmKLJ khCRdxGsK02Gj0NFCyYCuVQUwIaGxo9LUHJYKIxNsFJjksSkWIub2Z7soY2O9LRFw3dkZkKMoLXS lHbAE0wFIVaruMcqOBDp2eQ4zT29PIiTS5igjtHKjgZK8Z1DuHYGg3Q1QS4swNq1YoEhJi7dhWLw QLfA+FSFdhUeyiHt2KPg5SA0lIM8KJIx6Yy6EZgNdNjGt5iK2ZGBy1JYn6SwrPTOxggWqWQGQlds 31ABo6mMYryoBEOLgpCcYuY/Xp8d7i13+3PLG/jmv8uJxo2MIq1K47GHz6VDAXRI+8g8FPvTQUGK HeoIAsYwEzJygygldE1JrTynJpFCvezyJeebCKha9YE6rdYS7LmNnh79UQY1fivIiclVbyGMn0C4 b8GFP1oEIDvbAUJAjWFUiRaqx0F+ICkNjpOeUoQtgiiowGLa4j5mBxmSHZOarq3gOxsuGQ3FwA7T WmIriUGM3HtYEVPRfUS8e4og2/IozE0QeKf7RZ+vMUJXAwyPjCQQJAnrSAeVO6lBeEPYGrQARAwm g7NncynL3zD2ugyTYNTnOJgZJFkGjgO1XkOEqBSgmGA1X3QEFQXTkOQQTghoaF2oDOHerSUoSQuI wlqKitTGokwgwshkT+U7iDgKxaVi/Se4ZG4Js5nyUK0gLFL1CTvkwF8xoHs7rJCnVwNU+bINkgIR JNsPlRCBmpIkGs7KS5r4AIhYs2Gp2qpu8/rfP1CQTzDnlSXoY+aFJIJytJrFvQ9H1RCG0v3AFyDD c1DHdPhNmErSYZC6zmHWXMxAoCBSXwtGMSGpk4x09Q4yzjgb8oIKM7lBZkE178zqBGNcL4JMupnq 7Y5SW7alHSApNpHBIfMzX3B+HWKbm2YkJvX3eTq3tySHUVUFgKMjHUbR2eGcuY4hEV0NRVFU1Sb5 ogJ0mPF2A7APmDKILzBNEVSuouYorCaTY4ocH7ADTt6TSaUmE+fZX04hBoTIS9zwlpACxakG73Yn GRBpM8+QwLHLkfAmKisxtmkdaINoXIJ3yRQR1iEcAEDRhtfNscsJhrOGGAxIxYI6GmTOSDgTYmkU ggyygqEJEPyJVXseWwmHqCHkF5tMgqxs+hEYwjfrj/43JXgMXUoEVKm1HmAeZuwPwVQiELrLfPYT OoYhlDDGf7z3ZcCrjHlL14kowrDHAC1/GJnBJcALgsL03nMu2JXOrvDyd1/ZZtDaiVizLbVedFcX GF27BKJhZlE8Fmdxh2GOGtKRf8sowRjlNjBiSHOB90KAYGA5ZEqWHwpCgJPCITIzkhWEvSRlHSBj RjCsCMlXASVKzMAKvCQQqEuSUDA35JDNr79Sef+7aToHD0c9XjlRTexsQ0KLIgFhvTBCbv6TYmw9 60UcgsFubzIQqoTGbBhDvC0EeZwbC3Qy/KWiFpARAlIZhIIfB2zieLvQgVUBYbrDsz38G7yxT2Ey AmhB9FrPQYxATEvUkbVwSOLRSpTHYX1HZqzIOMOXrvkINIkxAQB0KEDUowBghQTuoack6NszTgMZ uuYPLIJEEYRi+ONsui1LaAPingMGfhmiCGQFxsKyX4xIgWbhtqI6CQE6NTDuNiPER9su5QsIQjJJ Be2K63xkFoB2RjsV2qRRy7cDAEbGHnal8VthgKrNasHkazJI+IBsELVmL3NvLP7CQSNFeKVIoB5x lhkjrNUbUlMXl5llk2FmBAbzjUokmo0kDXNBo3ZLxaiCrvNCqkeBomyAUeuNxjASQsEk8G5U14jC jPLGimgu94zngJQjgwOwTO4RktDVMDyWIwwSbAkE+BecGOIh7uVLImBmmI2cc0gfY8A3Id4TfzCo oDtVEix9FvDkO76Gec0y4DHEXLGRKFGASC5bIWlklmn1eLAK0VnZYNx6ZHDkwmOxsBkIJ0QnRDdC cG5MAhCTDOyqDRjjWRgroFFrVlQFRe5fowExvgkwqRjJBAvih39u24csYKdu8/bKY+0mHPmKZ3UW TRuxK0SyFRIqsDCNzuQPAlFp32AYpi0xHQVjUx1J8BUuaPSzMzWFEUwEoIMJnNMLbyAV43voSFmi g0cEEoIt5Ld6saVYlWT3kLJPAvxAg5B07XgkTGQZRIqMvXlRAbFVVAMZVULBUMPIow4kgwUr2XIy jEEcQYAwaDQ+umKGzPELhT++CcxK4yaD7P+4Uk2A8wTrsavuA1aiDF1eTDW8Hgd2PprKd0JFixeU ZCyGUGCfvmIJL+5NHwaQEmhMH9Fdsxpr26uZMVpDPPXuSRIYIghFENMY7l3gGOHpoJdzF1IQXhmJ VWVIHonlIyYB7lKREs0EVjNRIfRFOVh8e46pEdYwr8YCCfKfi9zSCRU7UcdhUjrhcTtHAaG2QFbX sok9iO1ImBVLWVNGNeaJF/MwVTrPnedBNczcdtxViOtNyMQfIZ/LJoJoDlaVgAn9Jr7T0n/jzGYG j3vAbGbj1UBIYVEmHWMNFoAX6EBDDuOBLzuouLTYnBxPrU0l2HuvvMpRADnKLhxE1AXOcSVJwJTG 3yDK/j9Z3HECaWIy870i0AvHr49xNIWJVz0LSv51ovCK2ZmGGMov/F3JFOFCQTeefwA= --===============5503086226342593983==--