Debian Codegraph @

mysql-test/suite/innodb/r/instant_alter_index_rename.result

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