From: Jon Olav Hauglid Date: January 26 2011 1:23pm Subject: bzr commit into mysql-5.5 branch (jon.hauglid:3278) Bug#42230 List-Archive: http://lists.mysql.com/commits/129664 X-Bug: 42230 Message-Id: <201101261323.p0Q6juU5004461@rcsinet13.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============6810774784653488254==" --===============6810774784653488254== 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:alfranio.correia@stripped 3278 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 13:23:29 +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 13:23:29 +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 13:23:29 +0000 @@ -1,4 +1,4 @@ -/* Copyright 2005-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc. +/* Copyright (c) 2005, 2011, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -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 13:23:29 +0000 @@ -1,7 +1,7 @@ #ifndef HANDLER_INCLUDED #define HANDLER_INCLUDED -/* Copyright 2000-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc. +/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -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*/ -/* - 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). -*/ -#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_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 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_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 13:23:29 +0000 @@ -1,4 +1,4 @@ -/* Copyright 2000-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc. +/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -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 13:23:29 +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); } /*****************************************************************//** --===============6810774784653488254== 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: 8d2661772dd27339f3af6876eaf114450e2f9fe8 # timestamp: 2011-01-26 14:23:32 +0100 # base_revision_id: alfranio.correia@stripped\ # ldahnb0h3ezgbhuz # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWUXVulEADIrfgH/wf/f//3// /+C////1YBnumfPvW4ve83PVFRvOa7u41yKVrUVUIi29pJoAsM6O297xVcm893uwHdtraG9td2Hr uVlY9uigF6zvYMJIk0KehlG0TKeahqb0p5HqKafk1R6ah6QGj9UB6amgGmgEoQNAhGhT1J6NNMpP 1NTNTTTQeggB6gNGgAAxwZBo0AZNMQ00GQYhhA0BoxMRoAABJqKI0CI9UN6Qj1M1PU9TajahtEA9 TQNANNDIaGjQIpCBDRok8CTNFMTZTI2poHqA2oDI9TTRkNBiBIkEAgaCZDRqaYmiMqfkygmEG0h+ lA9TRoD0heFeYSEyGz2P6iTfUh9iLXOjmsfJ+hBYCMUwxIVSHmZvY9rj5sRdC5UeTxeXpGvMBllR 4q5V48DEMv2ao/r10wK/f2bSvHsmDiRVkwyKEPPAxQli+KJAgf7sgHw3oqTG8k+wq3HtPZNeANJR TLz6svNLbiKAKU3AqB0v5Ecme7QmV9jBW3iSENqa+TbaKCWEFBUoWFuhAgnFQIdymZU2XQxAtOac KYz3UmElOFMEEqqJ0MNJJtjtFZnJTP9dn/eki0SRWqLjAW2gimHnVZ7Y/xwnynjX065VZ45Scqt2 eU61lSw8pSmFLbpzldGntqvu57S+hwR3tmzWVacN9lXbuzCrZGKBVcYJewAiAqwFOwJSiSTk7EOv PXgasNFkjr7KIaNBMzpDiwtLkOIwzXnoCqZdjd4aplDzI7DVoX6BeAcQYNobbAB/s9+7aeR/ESPH x8n08d+E5pYzdR7ynBTwuzRbC+KxuOIZt9b0J50rWlMQyplZ3KGzyScKYxw0sWMWo1qxWDarhUch 4vaRZFlFmdjRUGWi3W6pBQndidmTRRq1aF6Mj3a6pKFl07px+ALw2GnlcJcTw66Ss4DY22S/6mV8 DAv+A1rNAwJQXrDnJEkVG8GIoUxevaDbD3QHg6dlVUPJRd0KKPPRd0Kn+ZGwN4Q+8Ood07haaC3w 8/N66dA5uG0eGB3W3OYKHEFSmBILi8NS61oFamX93SQukhGCRJKAUzESvHkLl7CUpEKdG2kfI38n utgqHTHbMuxsfliTy34KfNDdkjD8aLMaJtjcFMUY3qQ5Vq2t2MsHBYmOESZkUvkZ1O1okXGDqsoM FcP6ZGV2il6AbE9xnfwsqSYsh3NNetxP6ih7BSoIU026aGdUlPCUN/gRYbgwqba4zL3uaUuE5WOv RHmjaCnuBsyPy1OZzcq58zyfKfQ9pXQpTkRsMCw48/Ss9FkVOUCWAbRmMf1WqamVNpxtr6j+JS3W V0Rsw8O6zObsVi4JE3f8EvFLjRG1qlt9Bcov65Yl0OzPWwwhWziVxBHbxg7X2WRbYG4ON5SfGNOS i+/WLC1ZVIY23fPYrdqW1Ka1FMMI9F5q7KW4eAwaIjJWdEebD2hsG6hFYbCy+0qrXHEIGMqCgYM6 Ns3dNvAlw1MvoQ2MSJ8ImuTiCMOo7HB3hiI23kVjNB4aA2Y8byLxblKUUzG1Kx92aR5yefqDffXQ 0nRN2l2ctoOUmQ67reFKTGg5t9MvG1eETvfW2yzn36R25NNyrQSXWFfNyQzC8PR3dp07qDB138ET 6JJTnc7u935mvTirja9LU3mKc10McJ1pKYqx4xmvdPmSGYWNcFyDYBUBtRVFjBEYsSKiLESMFVRt MYD8CBBBmetYHuJpNo4omXlxNY/WANWyZI+v1/CyZ9frr/u/CdDVpI+Y2x7yEnyIIRMhfhS5HxmU o0h8ufsOiXrymZDxJtXqmu1/ZvfXvoKnbAiNy0kx4W5dD9RST4/NuLDqC6Tie0+WqjxK/vP7xcUL EU+hal/Vg37Fvsk+J8PrD7Hhy5xhgGD2T0o/tIbdNNT8u/sw+VkUBYoAsWHenD0D8Ahvh0ziSQ1H euhM6slPQ2flfgXEkg6SvhDpKOWSN9ahjMV5ZklMAwBdjKrEmdpLX3ZRruoX0hVfR1sm1OdKScD/ bQKHp7IoGeW/isqJjQLqXE/T3maqhGjI7VwAYei8/eepUvFctGcyXGYpqag2/Z3RaIO6cEd8QwUz 7qBc7DBMyTMhTauZqcF2uRWN3bPFWg0uN0wkqBY4TYkMM00kYjxLDAkHymSosPAtShEjdGuQCZn3 vbIHJXfIyipCwSDAixAmmxmRGJ4Gx75XAsqfO/ikDknMydfdNrDcUBcVQhfRpQSTGiA3YQTGiXVZ Le61AIN95VKYGuh7yFhhqAsFgcbbPLcEA00g5MrJGglHrN2w9d9EaGgUy0nlZbVWdg4v2lyUkg00 aqUf3NfMtygxYA0uwyYJku8CaJI/DrOb5HWfZ1h+KnQyVsSecN/fAdRJens6jGZzDbEhj0nO5g32 pwJwD3i5gqLM5HH1nFFNJuMzm6CtJjkjBuAfD353EMcDlfr3xOgTo5xjRuJOZyH7YLqQNMSHWTss 7T1RfxtlLTwJnfNzhmkIvKlbFhGah2gFnMAY69IYahqM6ueszOIaUZjID7ysGiwrg7JAGvJSwKHG SDatHyf4o0keMG00GxxP+anfIwcz1H/Tp3gDp1OZ1VVCysp2Q6RzJIl3OGyGqnLaF5NdpussNwxB joLACRSL+GBwqSQC3C7yyT11LHnRSxLxznTiTJhTYgWJwRbkE3TJUoUMjdEiKde1NzonvuAf+66m vRtFYaO0SW67Y2JvxRQJ73KnokDfcTaXp1GJ+ji4RPguk6cCK7FqIocrKLeVsJC2jIJLikx7jgoJ NaC2RjtT640q2MarmLdVJL59KzSJyIQrHL7eUYn60dlOnQwZnMfAwK6qPJQNMoRY8QhXFdYBo6SO oxFTsJDHM6pnyftLOV7DomcptwiaaCxVzmsqG5XkP3zqFoQkEAdUQgMxha1wCYUJJd46nSnt0Gs4 K41mN4PlyAoaVkXBUu5G7oQvpdXF8JRdrc5RSCMTeE/Ek0KciZBMm9j9WgtV59ykZ1wMVmUVpfU1 GihQmsILS+4DYbeQypp5SOJ9h512aM8V4VGbaE59tEcfPgkZhuKWhPqdgzVICkTKsU8uXGJlZRJc yEIQeJ0UueQ9HdtTTfGK6QldZKeVNkqbACjPwKhALTYHONr0IFSiPApUqDPNhxHJSIyZitgywxLu 8020Z25pH3G+EtBoIDQYl04hHVrpmm59jntdgRo2TSlSYDtdUr0FLnipleIfSB2/RQwY90oS+KGk TVCoHhA4GwqKMDYIfxIDBE7RiU+yLM0QkkWL2+kE84TnkfB4u/JDOeoaTDQqKKqyHj5Rhlm+2Gzb pO8lDY2xqG3aKEEe00EBr+gpeHmjgvO/3mBtYQLfEIniQGQvsReNR9gv+i0Q4iFYMYecT+v0J75N HjJpJmHNIEomWAO5WMEDH7CJOtB8FZkAakB8kywaFahNVsFmSHhD8LVEiapRFcpaEyAvFLQdDAMp CbYbYhPOban6mBgFhSgWSq8ggFl0IIqQlwArOBNtSBeMgJsMwLwFCVpUTQbVMmUJCD6xw9wSIQMs Clyv6eoLhble0ciS0xULYg3+SgofjC+aHjGlQS2CxJDYDEC3jQCwJCmw1oRPsCiI7Rba+HRomnFA +lA5gcpcLATQHkBCreoZjSNgrs4T0q7R03XHqF84DUXEKriRLSwdakQglEFtIqICi3bzWrYQAH9i 5hzFRJEbNMOQS1AogMxlMUkGJEyAsxYKKNo3wWmhdyI5FdAAWlg41JN4ZKDgFQ/IZzywTHOSScMQ BEBcYVC2XBMBurqAMFbFTQYh0DBQKEksGsELhCQwIGoFihjOEJi1gYwhgg9g2EK7EG0aDlVvLAsx nZrMU5AFiITDWaBIDUJmgBMEK7YJYmZPe/AiUi6auqulpbuiy7uykbq1uqLvXAJlwkP2SQwSBgY2 LEWIvp6JCaqAmGAiIxVmDERBFGIsiAqqip3eWQnOHh9rtngGlFVFRFVX4pjU7lQqYTElB0MxGYhD AH0Lio19YuMSkrigPwrHnk4wK0QxAHIcR0HXK4GGkEqAR0mQXL7yfpAPyXxxJG5g0Swlfafu9hyN qKiLhkgqBzWCgSkywMZwaRE8ZbqBbfJpI0i4wcQGKCzVDUrnNu6sMrgtiQt8ASQmiBqes8ofEOaw Ol/M15CUBEBxvSB1AzsEt8sywI0imElJ7xBJQnXelJNI7K9Q0fHsSWRv2kgyDeGSZoCQm+iVNDp8 xZ0QWoiXR2/eNxIVTRtIwllKtOlClkxkUj68aheCdrHNMhgHT1HYNxg3vXuZAcwwDeLngWDkxqw4 joPBRJUKBfOplUJZ3HS0MZvzmzMPaMbbklIE5h4DAgcn3VuYTf3GwDvsEESD2uqyICaTWw1HcEtB M4GmZtNv2r38DsWO828GGxoIDktIz8UamFqi81oXkNHwGIWbeegkWHMZl5sbfCNSPckLkexCDF0C /BIJEG6hSRhEmfAJMjtlm2BHsITqMgoFiWIYnM+9pNi2XKxVa6NUZZ1XIBaLtFRg9LEFLpBE5Rup GQaqdWNWFBSIwiOVpP0nHehgrJLsNGi647zrOvuNLLwO8vAkSyqAC4W9miwYNQ0Bznesg9HE0AMh 4OKRRTTkNm1yLQ7ZJ6YLIY5TRO4ko3gm8HrapvAklevXASeaUHQleNI8fZfrs8OssC3E+J8zpRCm WgdpqP5spmxLbaQlz4FEfaX9W7X5LZQIPMbMQl3nOaTH2MdBYtSryM9duGDiaKxEbqZQs04CNUVp MM/rZ5lUuLEogpcdnbE4zrMiQuojEmupIiQwTeo7qvDO+GUVRaomiDiq+DIyNhyaeEHsdICBcazN bpLqmMRfMJJ8E7YvJRRcOTYFnOZpPMgtO87+W7iaxdguIadrCjI5bwVqVNiAkJMXZULqgXFA9SkA 9tEPbqUfp3kBmIqXVEEGM/FO+jRiHmb091uGpbvoVWkbM6S+KE6EVpW+lZsQtoAX9i89YAdOLPAW hXAiTBSLRlYW/93dhjzb/KDwttzxX1zM6NjCKtStNzD4c6heLmkeiCXWmgmMUO1QQBUvgJmDlBhB Luiak1o4zk0ihXrZ6iXhkwioWXkqCzqJcbGNnd3aYgvq+9dC2km3fJEGuekL0s8IGfuQWoJzuwGE gUXDU0jcsyEGmgCmeqEoPmXoXgI9FhsNZw+yBH72EDaRDUHTFeoXtGSZYM4nRZSbXdcbvNnMhDgB F+dUELqtMhKgw8ZGtBzq1P7By8/GMhxMZQYZnvhIIEgT/kgHkT4EoK4B6hUESVp4SONF48l8imfY WM3m13HNoKsTaQzlhpfsNUokcqmBOMBZxOgIK8XxhdQxNDECRPc9/jDUVFyo90gXlM5wNhylNAQP kMqfGAdhkXcOYycSlnEu81FZYEFCBtkJUMFiRkkELcSAXvGgefOxIU6uBqnvZBnICSTYiA5NbWiE jBSgZzhqntHne6ok2+DM5AGyLoXeqZKMj4Pu9wSCdA78qS9hj5IUkgnKyTWDek9j7IhDaViDbXaM D4W6zx1mxJjQuoa4BwLORIvCRkaA2jNzDW0u4eGs7iR3IOpQCGxr3QEioLd1K1sCMK33QSZbTLT2 Rwkq8KT8zhGRkjGkNYYF4h28IzFVJEQskPPbXChUySHTVUFgKMjHaWdnhnHnPVERXQ1G2xts2JcS 5Az5FO7oHQI6w5CDQwtoLWryOBT8cqwnKWuKGt+T9cpyHIkwnz215eMQWJkJdf2m4kBmu9B4++h6 TQcTru4F5Yb/eeRMVFY0Nsyy2pI0BahS6aVCbRE4QELqX9WLuI04nUcMMTKVKIxYI6WmS4EaIpGQ zUUZZQVCEiH6yVV8jWvWy5UD2gh4heZlAqxs/nREYQjdrj8jalcAxcVAipU2I2my4/ZRCIQuor8d RM4DEMoeeE/9zrx3FXGHCXt3kovrDHAC1ekTNyS3AWhYLz2naW60rXV3B63bdysbQ2jQoUUpxSp3 I45Z97IjRCikkWyZO0t7he2yJAu92MXowxmxgxJDnA+cKAaCY61FCkH6IEkAvzHcAJA2FwpKpneM PMzPjArkVpWJGSmXAojoYAXeEghUJckoGB15JDLd3qk8v920ndHDzc9XjmopvTphoYSUIRxpkQk6 eAo7k3HftFHQFotbjNAhVQmM4mtnSJMCE4prmcyfDv7zJggpCkM4kEPz+h0fTkhBEIG0amjwX0m5 SrRlq9agkFEINJ5jGICQl7SRtSNzROhO/SXUPVS5AtDWz/tVECYNRQQO1ICTGQiBghQT3EMas+Wg 5E1qdQ/gkMTBjQml6X4Y6EWDB2KOnQkiXSyJhaYGmb9yDIWbfrqI5iQEwo1MO42I7yPsl4aFhCEc BIMbYrrfGQco3cddqkUdHRAwBG1h9pqX7bM5ia4MGwO0eEAzl6lwBhELhpFczNgp+IgECpVFKkUA +QZVyR0lsYKwyXLxNmhAf1Hu0qhPoYCDNdZdklKjhMGDb1GhVSPXaDcAJR1hqMYHNKgTeBEv09Fs kuFgyDBSHBDgJS4mqeQlCODA0CZ3iNRrkeaYmrxhck2BIKbyFdTWY4iP7OytiWBgPKzqkE/mqrYf gGaPgFk940oDtVEi4/MtocA5dBv7+ETVNADqm7VZdStIXJVyFpZJZjv75hF0WtSByT2JOdy8aKsr wqVVIhUiHSFQdFMIRYyio5ykOJuvGJkqQSLbrJVBp+j/bgKaxvoEizGSEF2CPh4a9hMuL2eGZ/CU x8iYdnQUzm1RYtGzAoFWsRUSKq8vjg7UESKi5+e0DfabjeYmY8qPSfFHwbbeZbNMCUEFszsTDbmQ CuG86EhYooNG1BKCLPP+vclrEtZbkhb7ZHktNQJwThQ9ATTOA1QoqWfmskVJ65SlAa7JShXShlLD BlCxEySytlhORrEeYMAYMDfPex1QU2agyJhzBHAkyNbA+H+alxQHIIs+CpVwNdRi6zXChstlsdt/ e2JdrIhQoVMUZU1iYiEeLEgXPkjDkZALYRB6Jlrwpq2ae0mKyQzx1ZpIkMEQQiiGmMdqvu88hLmx cEILcRKqwpA8k8AlzTxYErckipt5CKyHlJDfFOVhu1GiRFowqowEEOY+9beIENF3I5tpUjshcx3j sGVtkBW17aE86dlSgFa6ipoY14okclf5lTkfG46Ca5m07bhu3YruahQWn2mj5yi0ogmWVstAKf2m n7z3n/x4mgDQ+TvGxmZ7lASGFRJhyGF4BkAXZEBDDuNxLytot7TYnBvJJLkddtpulEAOcosHETUB Y5xJUnAlMbe8MLsvuOZ8wDeB5qqWQzA6JGk+rq3E0h5jGJmx0KhloXt0oqEY2ZmGGM4v/F3JFOFC QRdW6UQ= --===============6810774784653488254==--