Debian Codegraph @

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

raw
1
2 SET NAMES utf8;
3
4 CREATE TABLE t1 (
5 c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT,
6 INDEX(c2))
7 ENGINE=InnoDB;
8
9 INSERT INTO t1 SET c1=1;
10
11 CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES
12 WHERE NAME LIKE 'test/t%';
13 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
14 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
15
16 CREATE TABLE t1p LIKE t1;
17
18 CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
19 CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES t1(c2),
20 CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2))
21 ENGINE=InnoDB;
22
23 CREATE TABLE sys_foreign SELECT i.*
24 FROM INFORMATION_SCHEMA.INNODB_FOREIGN i
25 WHERE FOR_NAME LIKE 'test/t%';
26
27 --sorted_result
28 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
29 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
30
31 -- source suite/innodb/include/innodb_dict.inc
32
33 SHOW CREATE TABLE t1;
34 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
35 SHOW CREATE TABLE t1;
36
37 -- source suite/innodb/include/innodb_dict.inc
38
39 # These should be no-op.
40 ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1;
41 ALTER TABLE t1 CHANGE c1 c1 INT FIRST;
42
43 -- source suite/innodb/include/innodb_dict.inc
44
45 ALTER TABLE t1 CHANGE C2 c3 INT;
46
47 -- source suite/innodb/include/innodb_dict.inc
48
49 ALTER TABLE t1 CHANGE c3 C INT;
50
51 -- source suite/innodb/include/innodb_dict.inc
52
53 ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT;
54
55 --sorted_result
56 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
57 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
58
59 -- source suite/innodb/include/innodb_dict.inc
60
61 -- error ER_BAD_FIELD_ERROR
62 ALTER TABLE t1 CHANGE cöĿǖmň_two c3 INT;
63
64 ALTER TABLE t1 CHANGE cÖĿUMŇ_two c3 INT, RENAME TO t3;
65
66 SELECT st.NAME, i.NAME
67 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_TABLES i
68 ON i.TABLE_ID=st.TABLE_ID;
69
70 SHOW CREATE TABLE t3;
71 SHOW CREATE TABLE t1c;
72
73 # The maximum column name length should be 64 characters.
74 --error ER_TOO_LONG_IDENT
75 ALTER TABLE t3 CHANGE c3
76 `12345678901234567890123456789012345678901234567890123456789012345` INT;
77 ALTER TABLE t3 CHANGE c3
78 `1234567890123456789012345678901234567890123456789012345678901234` INT;
79 SHOW CREATE TABLE t3;
80
81 # Test the length limit with non-ASCII utf-8 characters.
82 --error ER_TOO_LONG_IDENT
83 ALTER TABLE t3 CHANGE
84 `1234567890123456789012345678901234567890123456789012345678901234`
85 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿偀` INT;
86 --error ER_TOO_LONG_IDENT
87 ALTER TABLE t3 CHANGE
88 `1234567890123456789012345678901234567890123456789012345678901234`
89 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿ä` INT;
90 ALTER TABLE t3 CHANGE
91 `1234567890123456789012345678901234567890123456789012345678901234`
92 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾ä` INT;
93
94 # check that the rename is case-insensitive (note the upper-case ä at end)
95 ALTER TABLE t3 CHANGE
96 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾Ä`
97 c3 INT;
98
99 # test with 4-byte UTF-8 (should be disallowed)
100 --error ER_INVALID_CHARACTER_STRING
101 ALTER TABLE t3 CHANGE c3 𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜 INT;
102 --error ER_INVALID_CHARACTER_STRING
103 ALTER TABLE t3 CHANGE c3 😲 INT;
104
105 ALTER TABLE t3 RENAME TO t2;
106
107 SELECT st.NAME, i.NAME
108 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_TABLES i
109 ON i.TABLE_ID=st.TABLE_ID;
110
111 SHOW CREATE TABLE t2;
112
113 RENAME TABLE t2 TO t1;
114
115 SELECT st.NAME, i.NAME
116 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_TABLES i
117 ON i.TABLE_ID=st.TABLE_ID;
118
119 -- source suite/innodb/include/innodb_dict.inc
120
121 --error ER_DROP_INDEX_FK
122 ALTER TABLE t1 DROP INDEX c2;
123
124 --error ER_CANT_DROP_FIELD_OR_KEY
125 ALTER TABLE t1 DROP INDEX c4;
126
127 --error ER_CANT_DROP_FIELD_OR_KEY
128 ALTER TABLE t1c DROP FOREIGN KEY c2;
129
130 --error ER_CANT_DROP_FIELD_OR_KEY
131 ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2;
132
133 --error ER_CANT_DROP_FIELD_OR_KEY
134 ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2, DROP INDEX c2;
135
136 --error ER_DROP_INDEX_FK
137 ALTER TABLE t1c DROP INDEX c2;
138
139 --error ER_CANT_DROP_FIELD_OR_KEY
140 ALTER TABLE t1c DROP FOREIGN KEY ẗ1C2;
141
142 SHOW CREATE TABLE t1c;
143
144 SET foreign_key_checks=0;
145 DROP TABLE t1p;
146 SET foreign_key_checks=1;
147
148 SHOW CREATE TABLE t1c;
149
150 -- source suite/innodb/include/innodb_dict.inc
151
152 CREATE TABLE t1p (c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE=InnoDB;
153
154 --error ER_DROP_INDEX_FK
155 ALTER TABLE t1c DROP INDEX C2, DROP INDEX C3;
156 --error ER_DROP_INDEX_FK
157 ALTER TABLE t1c DROP INDEX C3;
158
159 SHOW CREATE TABLE t1c;
160 -- source suite/innodb/include/innodb_dict.inc
161
162 ALTER TABLE t1c DROP FOREIGN KEY t1C3;
163
164 SHOW CREATE TABLE t1c;
165 -- source suite/innodb/include/innodb_dict.inc
166
167 ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2;
168
169 SHOW CREATE TABLE t1c;
170
171 -- source suite/innodb/include/innodb_dict.inc
172
173 ALTER TABLE t1 DROP INDEX c2, CHANGE c3 c2 INT;
174
175 -- source suite/innodb/include/innodb_dict.inc
176
177 CREATE TABLE t1o LIKE t1;
178
179 # This will implicitly add a FTS_DOC_ID column, which cannot be done online.
180 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
181 ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
182 CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
183 ALGORITHM=INPLACE, LOCK=NONE;
184
185 ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
186 CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
187 ALGORITHM=INPLACE, LOCK=SHARED;
188
189 # The output should be empty, because index->id was reassigned.
190 -- source suite/innodb/include/innodb_dict.inc
191
192 SHOW CREATE TABLE tt;
193
194 # DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR are reserved InnoDB system column names.
195 --error ER_WRONG_COLUMN_NAME
196 ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=COPY;
197 --error ER_WRONG_COLUMN_NAME
198 ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=INPLACE;
199 --error ER_WRONG_COLUMN_NAME
200 ALTER TABLE t1o CHANGE c1 DB_TRX_ID INT;
201 --error ER_WRONG_COLUMN_NAME
202 ALTER TABLE t1o CHANGE c1 db_roll_ptr INT;
203 --error ER_WRONG_COLUMN_NAME
204 ALTER TABLE t1o ADD COLUMN DB_TRX_ID INT;
205 --error ER_WRONG_COLUMN_NAME
206 ALTER TABLE t1o ADD COLUMN db_roll_ptr INT;
207
208 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
209 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT;
210 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
211 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;
212
213 ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
214 ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
215
216 --error ER_DUP_FIELDNAME
217 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID INT;
218
219 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
220 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;
221
222 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, DROP INDEX ct, ALGORITHM=INPLACE;
223
224 # This creates a hidden FTS_DOC_ID column.
225 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN cu TEXT;
226
227 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
228 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT,
229 ALGORITHM=INPLACE;
230 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
231 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT;
232 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
233 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED,
234 ALGORITHM=INPLACE;
235 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
236 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;
237
238 # This would drop the hidden FTS_DOC_ID column and create
239 # a fulltext index on ct and another fulltext index on cu.
240 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
241 ALTER TABLE t1o ADD FULLTEXT INDEX(cu),
242 ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
243
244 # Replace the hidden FTS_DOC_ID column with a user-visible one.
245 # This used to work if there is at most one fulltext index.
246 # Currently, we disallow native ALTER TABLE if the table
247 # contains any FULLTEXT indexes.
248 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
249 ALTER TABLE t1o ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
250 ALGORITHM=INPLACE;
251 ALTER TABLE t1o ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
252 # Replace the user-visible FTS_DOC_ID column with a hidden one.
253 # We do not support this in-place.
254 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
255 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;
256 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID;
257
258 # FTS_DOC_ID is the internal row identifier for full-text search.
259 # It should be of type BIGINT UNSIGNED NOT NULL.
260 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
261 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
262 ALGORITHM=COPY;
263
264 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
265 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
266 ALGORITHM=INPLACE;
267 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
268 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_Doc_ID INT,
269 ALGORITHM=INPLACE;
270 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
271 ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
272 CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
273 ALGORITHM=INPLACE;
274
275 CREATE TABLE t1n LIKE t1o;
276
277 ALTER TABLE t1n ADD FULLTEXT INDEX(ct);
278 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
279 ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=INPLACE;
280 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
281 ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=COPY;
282 --error ER_BAD_FIELD_ERROR
283 ALTER TABLE t1n CHANGE FTS_DOC_ID c11 INT, ALGORITHM=INPLACE;
284 ALTER TABLE t1n CHANGE c1 FTS_DOC_ïD INT, ALGORITHM=INPLACE;
285
286 ALTER TABLE t1n CHANGE FTS_DOC_ÏD c1 INT, ALGORITHM=INPLACE;
287 ALTER TABLE t1n CHANGE c1 c2 INT, CHANGE c2 ct INT, CHANGE ct c1 TEXT,
288 ALGORITHM=INPLACE;
289 SHOW CREATE TABLE t1n;
290 ALTER TABLE t1n CHANGE c2 c1 INT, CHANGE ct c2 INT, CHANGE c1 ct TEXT,
291 ALGORITHM=COPY;
292 SHOW CREATE TABLE t1n;
293
294 --error ER_KEY_COLUMN_DOES_NOT_EXITS
295 ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
296 --error ER_KEY_COLUMN_DOES_NOT_EXITS
297 ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY;
298 ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
299 SHOW CREATE TABLE t1n;
300 ALTER TABLE t1n DROP INDEX c4;
301 --error ER_DUP_FIELDNAME
302 ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE;
303 ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE;
304
305 SHOW CREATE TABLE t1n;
306 DROP TABLE t1n;
307
308 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
309 ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct,
310 ALGORITHM=INPLACE;
311
312 # This will copy the table, removing the hidden FTS_DOC_ID column.
313 ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct;
314
315 ALTER TABLE t1o CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
316 ALGORITHM=INPLACE;
317
318 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE;
319
320 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
321 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
322 ALGORITHM=INPLACE;
323
324 # This should not show duplicates.
325 SELECT sc.pos FROM information_schema.innodb_columns sc
326 INNER JOIN information_schema.innodb_tables st
327 ON sc.TABLE_ID=st.TABLE_ID
328 WHERE st.NAME='test/t1o' AND sc.NAME='FTS_DOC_ID';
329
330 SHOW CREATE TABLE t1o;
331
332 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
333 DROP INDEX ct, ALGORITHM=INPLACE;
334
335 SHOW CREATE TABLE t1o;
336
337 DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign;
338
339 # Check the internal schemata of tt, t1o.
340
341 CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES
342 WHERE NAME='test/t1o';
343 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
344 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
345 CREATE TABLE sys_foreign SELECT i.*
346 FROM INFORMATION_SCHEMA.INNODB_FOREIGN i WHERE FOR_NAME='test/t1o';
347
348 -- source suite/innodb/include/innodb_dict.inc
349
350 # Ensure that there exists no hidden FTS_DOC_ID_INDEX on foo_id.
351
352 ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID),
353 ADD FULLTEXT INDEX(ct),
354 CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
355
356 ALTER TABLE t1o DROP INDEX ct, DROP INDEX FTS_DOC_ID_INDEX,
357 CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL;
358
359 ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id);
360
361 ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
362 ADD FULLTEXT INDEX(ct);
363
364 --error ER_INNODB_FT_WRONG_DOCID_INDEX
365 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
366 ALGORITHM=INPLACE;
367
368 DROP TABLE sys_indexes;
369 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
370 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
371
372 -- source suite/innodb/include/innodb_dict.inc
373
374 DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign;
375
376 CREATE TABLE t (t TEXT, FULLTEXT(t)) ENGINE=InnoDB;
377 DROP INDEX t ON t;
378 LET $regexp=/fts_([0-9a-f_]+)([A-Z_]+)/fts_aux\2/;
379 --replace_regex $regexp
380 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
381 FROM INFORMATION_SCHEMA.INNODB_TABLES
382 WHERE name LIKE '%FTS_%' ORDER BY 1, 2;
383
384 SELECT sc.pos, sc.NAME FROM information_schema.innodb_columns sc
385 INNER JOIN information_schema.innodb_tables st
386 ON sc.TABLE_ID=st.TABLE_ID
387 WHERE st.NAME='test/t' ORDER BY sc.pos, sc.NAME;
388
389 ALTER TABLE t ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;
390
391 --replace_regex $regexp
392 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
393 FROM INFORMATION_SCHEMA.INNODB_TABLES
394 WHERE name LIKE '%FTS_%' ORDER BY 1, 2;
395
396 ALTER TABLE t ADD FULLTEXT INDEX(t);
397
398 SELECT sc.pos, sc.NAME FROM information_schema.innodb_columns sc
399 INNER JOIN information_schema.innodb_tables st
400 ON sc.TABLE_ID=st.TABLE_ID
401 WHERE st.NAME='test/t' ORDER BY sc.pos, sc.NAME;
402
403 DROP TABLE t;
404
405 --disable_query_log
406 call mtr.add_suppression("deleting orphaned .ibd file");
407 --enable_query_log
408
409 --echo #
410 --echo # Bug #19465984 INNODB DATA DICTIONARY IS NOT UPDATED WHILE
411 --echo # RENAMING THE COLUMN
412 --echo #
413 CREATE TABLE t1(c1 INT NOT NULL, PRIMARY KEY(c1))ENGINE=INNODB;
414 CREATE TABLE t2(c2 INT NOT NULL, FOREIGN KEY(c2) REFERENCES t1(c1))ENGINE=INNODB;
415 SHOW CREATE TABLE t1;
416 SHOW CREATE TABLE t2;
417 ALTER TABLE t1 CHANGE COLUMN c1 C1 INT;
418 SHOW CREATE TABLE t1;
419 SHOW CREATE TABLE t2;
420 ALTER TABLE t1 CHANGE COLUMN C1 c5 INT;
421 SHOW CREATE TABLE t1;
422 SHOW CREATE TABLE t2;
423 DROP TABLE t2, t1;
424
425 --echo #
426 --echo #BUG#21514135 SCHEMA MISMATCH ERROR WHEN IMPORTING TABLESPACE AFTER
427 --echo #DROPPING AN INDEX
428 --echo #
429 let $source_db = source_db;
430 let $dest_db = dest_db;
431
432 eval CREATE DATABASE $source_db;
433 eval CREATE DATABASE $dest_db;
434
435 eval CREATE TABLE $source_db.t1 (
436 id int(11) NOT NULL,
437 age int(11) DEFAULT NULL,
438 name varchar(20),
439 PRIMARY KEY (id),
440 KEY index1 (age)
441 ) ENGINE=InnoDB;
442
443 eval ALTER TABLE $source_db.t1 DROP INDEX index1, ADD INDEX index2(name, age), algorithm=inplace;
444
445 --source suite/innodb/include/import.inc
446
447 eval ALTER TABLE $source_db.t1 DROP INDEX index2, algorithm=inplace;
448
449 --source suite/innodb/include/import.inc
450
451 eval DROP TABLE $source_db.t1;
452 eval DROP DATABASE $source_db;
453 eval DROP DATABASE $dest_db;
454
455 --echo #
456 --echo # Bug#22005726 ASSERT COL->ORD_PART IN ROW_BUILD_INDEX_ENTRY_LOW AT
457 --echo # ROW0ROW.CC:299
458 --echo #
459 USE test;
460 CREATE TABLE t1 (col1 INT, col2 TEXT) ENGINE = InnoDB;
461 INSERT INTO t1 (col1, col2) VALUES (2 , 'A');
462 ALTER TABLE t1 ADD KEY idx (col2(10));
463 INSERT INTO t1 (col1, col2) VALUES (2 , 'A');
464 --error ER_DUP_ENTRY
465 ALTER TABLE t1 DROP KEY idx, ADD UNIQUE KEY uidx (col1);
466 DELETE FROM t1 WHERE col1 = 2;
467 DROP TABLE t1;