Debian Codegraph @

mysql-test/suite/innodb/t/instant_alter_index_rename.test

raw
1 --source include/have_innodb.inc
2 --source include/have_debug.inc
3 --source include/have_sequence.inc
4
5 delimiter |;
6 create function get_index_id(tbl_id int, index_name char(100))
7 returns int
8 begin
9 declare res int;
10 select index_id into res from information_schema.innodb_sys_indexes where
11 name=index_name and table_id = tbl_id;
12 return res;
13 end|
14
15 delimiter ;|
16
17 create table t (
18 pk int primary key,
19 a int,
20 b int,
21 c int,
22 unique index a_key (a),
23 key c_key (c)
24 ) engine=innodb stats_persistent=1;
25
26 insert into t values (1, 1, 1, 1);
27
28 set @table_id = (select table_id from information_schema.innodb_sys_tables where name='test/t');
29
30 set @a_key_id = get_index_id(@table_id, 'a_key');
31 set @c_key_id = get_index_id(@table_id, 'c_key');
32 set @primary_id = get_index_id(@table_id, 'primary');
33
34 select distinct(index_name) from mysql.innodb_index_stats where table_name = 't';
35 alter table t
36 drop index a_key,
37 add unique index a_key_strikes_back (a);
38 select distinct(index_name) from mysql.innodb_index_stats where table_name = 't';
39
40 check table t;
41 select @a_key_id = get_index_id(@table_id, 'a_key_strikes_back'),
42 @c_key_id = get_index_id(@table_id, 'c_key'),
43 @primary_id = get_index_id(@table_id, 'primary');
44
45 set @a_key_strikes_back_id = get_index_id(@table_id, 'a_key_strikes_back');
46 set @c_key_id = get_index_id(@table_id, 'c_key');
47 set @primary_id = get_index_id(@table_id, 'primary');
48
49 alter table t
50 drop index a_key_strikes_back,
51 add unique index a_key_returns (a),
52 drop primary key,
53 add primary key (pk),
54 add unique index b_key (b);
55
56 check table t;
57 select @a_key_strikes_back_id = get_index_id(@table_id, 'a_key_returns'),
58 @c_key_id = get_index_id(@table_id, 'c_key'),
59 @primary_id = get_index_id(@table_id, 'primary');
60
61 set @a_key_returns_id = get_index_id(@table_id, 'a_key_returns');
62 set @b_key_id = get_index_id(@table_id, 'b_key');
63 set @c_key_id = get_index_id(@table_id, 'c_key');
64 set @primary_id = get_index_id(@table_id, 'primary');
65
66 alter table t
67 drop key c_key,
68 add key c_key2 (c);
69
70 check table t;
71 select @a_key_returns_id = get_index_id(@table_id, 'a_key_returns'),
72 @b_key_id = get_index_id(@table_id, 'b_key'),
73 @c_key_id = get_index_id(@table_id, 'c_key2'),
74 @primary_id = get_index_id(@table_id, 'primary');
75
76 drop table t;
77
78 create table errors (
79 a int,
80 unique key a_key (a),
81 b int
82 ) engine=innodb;
83
84 --error ER_CANT_DROP_FIELD_OR_KEY
85 alter table errors
86 drop key a_key,
87 drop key a_key,
88 add unique key a_key2 (a);
89
90 --error ER_CANT_DROP_FIELD_OR_KEY
91 alter table errors
92 drop key a_key,
93 drop key a_key2,
94 add unique key a_key2 (a);
95
96 --error ER_CANT_DROP_FIELD_OR_KEY
97 alter table errors
98 add key b_key (b),
99 drop key b_key,
100 add key bb_key (b);
101
102 --error ER_CANT_DROP_FIELD_OR_KEY
103 alter table errors
104 drop key a_key,
105 add key a_key2 (a),
106 drop key a_key,
107 add key a_key2 (a);
108
109 drop table errors;
110
111 --disable_query_log
112 call mtr.add_suppression("Flagged corruption of `a_key` in table `test`.`corrupted` in dict_set_index_corrupted");
113 --enable_query_log
114
115 create table corrupted (
116 a int,
117 key a_key (a)
118 ) engine=innodb;
119
120 insert into corrupted values (1);
121
122 select * from corrupted;
123
124 SET @save_dbug = @@SESSION.debug_dbug;
125 SET debug_dbug = '+d,dict_set_index_corrupted';
126 check table corrupted;
127 SET debug_dbug = @save_dbug;
128
129 --error ER_INDEX_CORRUPT
130 select * from corrupted;
131
132 --error ER_INDEX_CORRUPT
133 alter table corrupted
134 drop key a_key,
135 add key a_key2 (a);
136
137 alter table corrupted
138 drop key a_key;
139
140 select * from corrupted;
141
142 check table corrupted;
143
144 drop table corrupted;
145
146 create table t (
147 a int,
148 unique key a_key (a)
149 ) engine=innodb stats_persistent=1;
150
151 SET @save_dbug = @@SESSION.debug_dbug;
152 SET debug_dbug = '+d,ib_rename_index_fail1';
153 -- error ER_LOCK_DEADLOCK
154 alter table t
155 drop key a_key,
156 add unique key a_key2 (a),
157 algorithm=instant;
158 SET debug_dbug = @save_dbug;
159
160 --error ER_WRONG_NAME_FOR_INDEX
161 alter table t
162 drop key a_key,
163 add unique key `GEN_CLUST_INDEX` (a),
164 algorithm=instant;
165
166 show create table t;
167
168 drop table t;
169
170
171 create table rename_column_and_index (
172 a int,
173 unique index a_key(a)
174 ) engine=innodb;
175
176 insert into rename_column_and_index values (1), (3);
177
178 alter table rename_column_and_index
179 change a aa int,
180 drop key a_key,
181 add unique key aa_key(aa),
182 algorithm=instant;
183
184 show create table rename_column_and_index;
185 check table rename_column_and_index;
186 drop table rename_column_and_index;
187
188
189 --echo #
190 --echo # MDEV-19189: ASAN memcpy-param-overlap in fill_alter_inplace_info upon adding indexes
191 --echo #
192
193 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
194 ALTER TABLE t1 ADD FOREIGN KEY f (f2) REFERENCES xx(f2);
195 ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1(f2), ADD KEY (f3), ADD KEY (f1);
196 DROP TABLE t1;
197
198 --echo #
199 --echo # MDEV-21669 InnoDB: Table ... contains <n> indexes inside InnoDB, which is different from the number of indexes <n> defined in the MariaDB
200 --echo #
201 CREATE TABLE t1 (col_int INTEGER, col_char CHAR(20), col_varchar VARCHAR(500)) ENGINE=InnoDB;
202 SET @table_id = (SELECT table_id FROM information_schema.innodb_sys_tables WHERE name='test/t1');
203 ALTER TABLE t1 ADD KEY idx3 (col_varchar(9)), ADD KEY idX2 (col_char(9));
204 SET @idx3_key_id = get_index_id(@table_id, 'iDx3');
205 ALTER TABLE t1 DROP KEY iDx3, ADD KEY Idx3 (col_varchar(9));
206 SELECT @idx3_key_id = get_index_id(@table_id, 'Idx3');
207 CHECK TABLE t1 EXTENDED ;
208 DROP TABLE t1;
209
210 DROP FUNCTION get_index_id;
211
212 --echo #
213 --echo # MDEV-23356 InnoDB: Failing assertion: field->col->mtype == type, crash or ASAN failures in row_sel_convert_mysql_key_to_innobase, InnoDB indexes are inconsistent after INDEX changes
214 --echo #
215
216 CREATE TABLE t1 (a INT, b INT, c CHAR(8),
217 KEY ind1(c), KEY ind2(b)) ENGINE=InnoDB STATS_PERSISTENT=1;
218
219 INSERT INTO t1 SELECT 1, 1, 'a' FROM seq_1_to_100;
220
221 SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
222
223 ALTER TABLE t1 DROP INDEX ind2, ADD INDEX ind3(b),
224 DROP INDEX ind1, ADD INDEX ind2(c);
225
226 SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
227
228 ALTER TABLE t1 DROP b, FORCE;
229 SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
230
231 UPDATE t1 SET a = 1 WHERE c = 'foo';
232 DROP TABLE t1;
233 SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;