diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/instant_alter_index_rename.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/instant_alter_index_rename.result | 178 |
1 files changed, 178 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/instant_alter_index_rename.result b/mysql-test/suite/innodb/r/instant_alter_index_rename.result new file mode 100644 index 00000000000..93bbf6ee193 --- /dev/null +++ b/mysql-test/suite/innodb/r/instant_alter_index_rename.result @@ -0,0 +1,178 @@ +create function get_index_id(tbl_id int, index_name char(100)) +returns int +begin +declare res int; +select index_id into res from information_schema.innodb_sys_indexes where +name=index_name and table_id = tbl_id; +return res; +end| +create table t ( +pk int primary key, +a int, +b int, +c int, +unique index a_key (a), +key c_key (c) +) engine=innodb stats_persistent=1; +insert into t values (1, 1, 1, 1); +set @table_id = (select table_id from information_schema.innodb_sys_tables where name='test/t'); +set @a_key_id = get_index_id(@table_id, 'a_key'); +set @c_key_id = get_index_id(@table_id, 'c_key'); +set @primary_id = get_index_id(@table_id, 'primary'); +select distinct(index_name) from mysql.innodb_index_stats where table_name = 't'; +index_name +PRIMARY +a_key +c_key +alter table t +drop index a_key, +add unique index a_key_strikes_back (a); +select distinct(index_name) from mysql.innodb_index_stats where table_name = 't'; +index_name +PRIMARY +a_key_strikes_back +c_key +check table t; +Table Op Msg_type Msg_text +test.t check status OK +select @a_key_id = get_index_id(@table_id, 'a_key_strikes_back'), +@c_key_id = get_index_id(@table_id, 'c_key'), +@primary_id = get_index_id(@table_id, 'primary'); +@a_key_id = get_index_id(@table_id, 'a_key_strikes_back') @c_key_id = get_index_id(@table_id, 'c_key') @primary_id = get_index_id(@table_id, 'primary') +1 1 1 +set @a_key_strikes_back_id = get_index_id(@table_id, 'a_key_strikes_back'); +set @c_key_id = get_index_id(@table_id, 'c_key'); +set @primary_id = get_index_id(@table_id, 'primary'); +alter table t +drop index a_key_strikes_back, +add unique index a_key_returns (a), +drop primary key, +add primary key (pk), +add unique index b_key (b); +check table t; +Table Op Msg_type Msg_text +test.t check status OK +select @a_key_strikes_back_id = get_index_id(@table_id, 'a_key_returns'), +@c_key_id = get_index_id(@table_id, 'c_key'), +@primary_id = get_index_id(@table_id, 'primary'); +@a_key_strikes_back_id = get_index_id(@table_id, 'a_key_returns') @c_key_id = get_index_id(@table_id, 'c_key') @primary_id = get_index_id(@table_id, 'primary') +1 1 1 +set @a_key_returns_id = get_index_id(@table_id, 'a_key_returns'); +set @b_key_id = get_index_id(@table_id, 'b_key'); +set @c_key_id = get_index_id(@table_id, 'c_key'); +set @primary_id = get_index_id(@table_id, 'primary'); +alter table t +drop key c_key, +add key c_key2 (c); +check table t; +Table Op Msg_type Msg_text +test.t check status OK +select @a_key_returns_id = get_index_id(@table_id, 'a_key_returns'), +@b_key_id = get_index_id(@table_id, 'b_key'), +@c_key_id = get_index_id(@table_id, 'c_key2'), +@primary_id = get_index_id(@table_id, 'primary'); +@a_key_returns_id = get_index_id(@table_id, 'a_key_returns') @b_key_id = get_index_id(@table_id, 'b_key') @c_key_id = get_index_id(@table_id, 'c_key2') @primary_id = get_index_id(@table_id, 'primary') +1 1 1 1 +drop table t; +drop function get_index_id; +create table errors ( +a int, +unique key a_key (a), +b int +) engine=innodb; +alter table errors +drop key a_key, +drop key a_key, +add unique key a_key2 (a); +ERROR 42000: Can't DROP INDEX `a_key`; check that it exists +alter table errors +drop key a_key, +drop key a_key2, +add unique key a_key2 (a); +ERROR 42000: Can't DROP INDEX `a_key2`; check that it exists +alter table errors +add key b_key (b), +drop key b_key, +add key bb_key (b); +ERROR 42000: Can't DROP INDEX `b_key`; check that it exists +alter table errors +drop key a_key, +add key a_key2 (a), +drop key a_key, +add key a_key2 (a); +ERROR 42000: Can't DROP INDEX `a_key`; check that it exists +drop table errors; +create table corrupted ( +a int, +key a_key (a) +) engine=innodb; +insert into corrupted values (1); +select * from corrupted; +a +1 +SET @save_dbug = @@SESSION.debug_dbug; +SET debug_dbug = '+d,dict_set_index_corrupted'; +check table corrupted; +Table Op Msg_type Msg_text +test.corrupted check Warning InnoDB: Index a_key is marked as corrupted +test.corrupted check error Corrupt +SET debug_dbug = @save_dbug; +select * from corrupted; +ERROR HY000: Index corrupted is corrupted +alter table corrupted +drop key a_key, +add key a_key2 (a); +ERROR HY000: Index a_key is corrupted +alter table corrupted +drop key a_key; +select * from corrupted; +a +1 +check table corrupted; +Table Op Msg_type Msg_text +test.corrupted check status OK +drop table corrupted; +create table t ( +a int, +unique key a_key (a) +) engine=innodb stats_persistent=1; +SET @save_dbug = @@SESSION.debug_dbug; +SET debug_dbug = '+d,ib_rename_index_fail1'; +alter table t +drop key a_key, +add unique key a_key2 (a), +algorithm=instant; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +SET debug_dbug = @save_dbug; +alter table t +drop key a_key, +add unique key `GEN_CLUST_INDEX` (a), +algorithm=instant; +ERROR 42000: Incorrect index name 'GEN_CLUST_INDEX' +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + UNIQUE KEY `a_key` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1 +drop table t; +create table rename_column_and_index ( +a int, +unique index a_key(a) +) engine=innodb; +insert into rename_column_and_index values (1), (3); +alter table rename_column_and_index +change a aa int, +drop key a_key, +add unique key aa_key(aa), +algorithm=instant; +show create table rename_column_and_index; +Table Create Table +rename_column_and_index CREATE TABLE `rename_column_and_index` ( + `aa` int(11) DEFAULT NULL, + UNIQUE KEY `aa_key` (`aa`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +check table rename_column_and_index; +Table Op Msg_type Msg_text +test.rename_column_and_index check status OK +drop table rename_column_and_index; |