Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/default_charset.inc
3 --source include/test_db_charset_latin1.inc
4
5 SET NAMES utf8mb4;
6
7 CREATE TABLE t1 (
8 c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT,
9 INDEX(c2))
10 ENGINE=InnoDB;
11
12 INSERT INTO t1 SET c1=1;
13
14 CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
15 WHERE NAME LIKE 'test/t%';
16 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
17 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
18
19 CREATE TABLE t1p LIKE t1;
20
21 CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
22 CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES t1(c2),
23 CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2))
24 ENGINE=InnoDB;
25
26 CREATE TABLE sys_foreign SELECT i.*
27 FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i
28 WHERE FOR_NAME LIKE 'test/t%';
29
30 SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i
31 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
32
33 -- source suite/innodb/include/innodb_dict.inc
34
35 SHOW CREATE TABLE t1;
36 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
37 SHOW CREATE TABLE t1;
38
39 -- source suite/innodb/include/innodb_dict.inc
40
41 # These should be no-op.
42 ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1;
43 ALTER TABLE t1 CHANGE c1 c1 INT FIRST;
44
45 -- source suite/innodb/include/innodb_dict.inc
46
47 ALTER TABLE t1 CHANGE C2 c3 INT;
48
49 -- source suite/innodb/include/innodb_dict.inc
50
51 ALTER TABLE t1 CHANGE c3 C INT;
52
53 -- source suite/innodb/include/innodb_dict.inc
54
55 ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT;
56
57 SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i
58 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
59
60 -- source suite/innodb/include/innodb_dict.inc
61
62 -- error ER_BAD_FIELD_ERROR
63 ALTER TABLE t1 CHANGE cöĿǖmň_two c3 INT;
64
65 ALTER TABLE t1 CHANGE cÖĿUMŇ_two c3 INT, RENAME TO t3;
66
67 SELECT st.NAME, i.NAME
68 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES i
69 ON i.TABLE_ID=st.TABLE_ID;
70
71 SHOW CREATE TABLE t3;
72 SHOW CREATE TABLE t1c;
73
74 # The maximum column name length should be 64 characters.
75 --error ER_TOO_LONG_IDENT
76 ALTER TABLE t3 CHANGE c3
77 `12345678901234567890123456789012345678901234567890123456789012345` INT;
78 ALTER TABLE t3 CHANGE c3
79 `1234567890123456789012345678901234567890123456789012345678901234` INT;
80 SHOW CREATE TABLE t3;
81
82 # Test the length limit with non-ASCII utf-8 characters.
83 --error ER_TOO_LONG_IDENT
84 ALTER TABLE t3 CHANGE
85 `1234567890123456789012345678901234567890123456789012345678901234`
86 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿偀` INT;
87 --error ER_TOO_LONG_IDENT
88 ALTER TABLE t3 CHANGE
89 `1234567890123456789012345678901234567890123456789012345678901234`
90 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿ä` INT;
91 ALTER TABLE t3 CHANGE
92 `1234567890123456789012345678901234567890123456789012345678901234`
93 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾ä` INT;
94
95 # check that the rename is case-insensitive (note the upper-case ä at end)
96 ALTER TABLE t3 CHANGE
97 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾Ä`
98 c3 INT;
99
100 # test with 4-byte UTF-8 (should be disallowed)
101 --error ER_INVALID_CHARACTER_STRING
102 ALTER TABLE t3 CHANGE c3 𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜 INT;
103 --error ER_INVALID_CHARACTER_STRING
104 ALTER TABLE t3 CHANGE c3 😲 INT;
105
106 ALTER TABLE t3 RENAME TO t2;
107
108 SELECT st.NAME, i.NAME
109 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES i
110 ON i.TABLE_ID=st.TABLE_ID;
111
112 SHOW CREATE TABLE t2;
113
114 RENAME TABLE t2 TO t1;
115
116 SELECT st.NAME, i.NAME
117 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES i
118 ON i.TABLE_ID=st.TABLE_ID;
119
120 -- source suite/innodb/include/innodb_dict.inc
121
122 --error ER_DROP_INDEX_FK
123 ALTER TABLE t1 DROP INDEX c2;
124
125 --error ER_CANT_DROP_FIELD_OR_KEY
126 ALTER TABLE t1 DROP INDEX c4;
127
128 --error ER_CANT_DROP_FIELD_OR_KEY
129 ALTER TABLE t1c DROP FOREIGN KEY c2;
130
131 --error ER_CANT_DROP_FIELD_OR_KEY
132 ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2;
133
134 --error ER_CANT_DROP_FIELD_OR_KEY
135 ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2, DROP INDEX c2;
136
137 --error ER_DROP_INDEX_FK
138 ALTER TABLE t1c DROP INDEX c2;
139
140 --error ER_CANT_DROP_FIELD_OR_KEY
141 ALTER TABLE t1c DROP FOREIGN KEY ẗ1C2;
142
143 SHOW CREATE TABLE t1c;
144
145 SET foreign_key_checks=0;
146 DROP TABLE t1p;
147 SET foreign_key_checks=1;
148
149 SHOW CREATE TABLE t1c;
150
151 -- source suite/innodb/include/innodb_dict.inc
152
153 CREATE TABLE t1p (c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE=InnoDB;
154
155 --error ER_DROP_INDEX_FK
156 ALTER TABLE t1c DROP INDEX C2, DROP INDEX C3;
157 --error ER_DROP_INDEX_FK
158 ALTER TABLE t1c DROP INDEX C3;
159
160 SET foreign_key_checks=0;
161 ALTER TABLE t1c DROP INDEX C3;
162 SET foreign_key_checks=1;
163
164 SHOW CREATE TABLE t1c;
165 -- source suite/innodb/include/innodb_dict.inc
166
167 ALTER TABLE t1c DROP FOREIGN KEY t1C3;
168
169 SHOW CREATE TABLE t1c;
170 -- source suite/innodb/include/innodb_dict.inc
171
172 ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2;
173
174 SHOW CREATE TABLE t1c;
175
176 -- source suite/innodb/include/innodb_dict.inc
177
178 ALTER TABLE t1 DROP INDEX c2, CHANGE c3 c2 INT;
179
180 -- source suite/innodb/include/innodb_dict.inc
181
182 CREATE TABLE t1o LIKE t1;
183
184 # This will implicitly add a FTS_DOC_ID column, which cannot be done online.
185 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
186 ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
187 CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
188 ALGORITHM=INPLACE, LOCK=NONE;
189
190 ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
191 CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
192 ALGORITHM=INPLACE, LOCK=SHARED;
193
194 # The output should be empty, because index->id was reassigned.
195 -- source suite/innodb/include/innodb_dict.inc
196
197 SHOW CREATE TABLE tt;
198 # Non-instant ADD COLUMN would require the table to be rebuilt.
199 --error ER_ALTER_OPERATION_NOT_SUPPORTED
200 ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT;
201 # This is still non-instant ADD COLUMN, because FTS_DOC_ID is hidden.
202 --error ER_ALTER_OPERATION_NOT_SUPPORTED
203 ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT;
204
205 CREATE TABLE tu (
206 pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT,
207 FULLTEXT INDEX(t)
208 ) ENGINE=InnoDB;
209 --error ER_ALTER_OPERATION_NOT_SUPPORTED
210 ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT;
211 --error ER_ALTER_OPERATION_NOT_SUPPORTED
212 ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT;
213 DROP TABLE tu;
214
215 CREATE TABLE tv (
216 pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT,
217 UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID),
218 FULLTEXT INDEX(t)
219 ) ENGINE=InnoDB;
220 --error ER_ALTER_OPERATION_NOT_SUPPORTED
221 ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT;
222 --error ER_ALTER_OPERATION_NOT_SUPPORTED
223 ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT;
224 DROP TABLE tv;
225
226 # DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR are reserved InnoDB system column names.
227 --error ER_WRONG_COLUMN_NAME
228 ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=COPY;
229 --error ER_WRONG_COLUMN_NAME
230 ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=INPLACE;
231 --error ER_WRONG_COLUMN_NAME
232 ALTER TABLE t1o CHANGE c1 DB_TRX_ID INT;
233 --error ER_WRONG_COLUMN_NAME
234 ALTER TABLE t1o CHANGE c1 db_roll_ptr INT;
235 --error ER_WRONG_COLUMN_NAME
236 ALTER TABLE t1o ADD COLUMN DB_TRX_ID INT;
237 --error ER_WRONG_COLUMN_NAME
238 ALTER TABLE t1o ADD COLUMN db_roll_ptr INT;
239
240 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
241 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT;
242 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
243 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;
244
245 ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
246 ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
247
248 --error ER_DUP_FIELDNAME
249 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID INT;
250
251 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
252 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;
253
254 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, DROP INDEX ct, ALGORITHM=INPLACE;
255
256 # This creates a hidden FTS_DOC_ID column.
257 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN cu TEXT;
258
259 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
260 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT,
261 ALGORITHM=INPLACE;
262 --error ER_WRONG_COLUMN_NAME
263 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT;
264 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
265 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED,
266 ALGORITHM=INPLACE;
267 --error ER_WRONG_COLUMN_NAME
268 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;
269
270 # This would drop the hidden FTS_DOC_ID column and create
271 # a fulltext index on ct and another fulltext index on cu.
272 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
273 ALTER TABLE t1o ADD FULLTEXT INDEX(cu),
274 ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
275
276 # Replace the hidden FTS_DOC_ID column with a user-visible one.
277 ALTER TABLE t1o ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
278 ALGORITHM=INPLACE;
279 # Replace the user-visible FTS_DOC_ID column with a hidden one.
280 # We do not support this in-place.
281 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
282 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;
283 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID;
284
285 # FTS_DOC_ID is the internal row identifier for full-text search.
286 # It should be of type BIGINT UNSIGNED NOT NULL.
287 --error ER_WRONG_COLUMN_NAME
288 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
289 ALGORITHM=COPY;
290
291 --error ER_WRONG_COLUMN_NAME
292 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
293 ALGORITHM=INPLACE;
294 --error ER_WRONG_COLUMN_NAME
295 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_Doc_ID INT,
296 ALGORITHM=INPLACE;
297 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
298 ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
299 CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
300 ALGORITHM=INPLACE;
301
302 CREATE TABLE t1n LIKE t1o;
303
304 ALTER TABLE t1n ADD FULLTEXT INDEX(ct);
305 --error ER_WRONG_COLUMN_NAME
306 ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=INPLACE;
307 --error ER_WRONG_COLUMN_NAME
308 ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=COPY;
309 --error ER_BAD_FIELD_ERROR
310 ALTER TABLE t1n CHANGE FTS_DOC_ID c11 INT, ALGORITHM=INPLACE;
311 ALTER TABLE t1n CHANGE c1 FTS_DOC_ïD INT, ALGORITHM=INPLACE;
312
313 ALTER TABLE t1n CHANGE FTS_DOC_ÏD c1 INT, ALGORITHM=INPLACE;
314 ALTER TABLE t1n CHANGE c1 c2 INT, CHANGE c2 ct INT, CHANGE ct c1 TEXT,
315 ALGORITHM=INPLACE;
316 SHOW CREATE TABLE t1n;
317 ALTER TABLE t1n CHANGE c2 c1 INT, CHANGE ct c2 INT, CHANGE c1 ct TEXT,
318 ALGORITHM=COPY;
319 SHOW CREATE TABLE t1n;
320
321 --error ER_KEY_COLUMN_DOES_NOT_EXIST
322 ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
323 --error ER_KEY_COLUMN_DOES_NOT_EXIST
324 ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY;
325 ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
326 SHOW CREATE TABLE t1n;
327 ALTER TABLE t1n DROP INDEX c4;
328 --error ER_DUP_FIELDNAME
329 ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE;
330 ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE;
331
332 SHOW CREATE TABLE t1n;
333 DROP TABLE t1n;
334
335 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
336 ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct,
337 ALGORITHM=INPLACE;
338
339 # This will copy the table, removing the hidden FTS_DOC_ID column.
340 ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct;
341
342 ALTER TABLE t1o CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
343 ALGORITHM=INPLACE;
344
345 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE;
346
347 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
348 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
349 ALGORITHM=INPLACE;
350
351 # This should not show duplicates.
352 SELECT sc.pos FROM information_schema.innodb_sys_columns sc
353 INNER JOIN information_schema.innodb_sys_tables st
354 ON sc.TABLE_ID=st.TABLE_ID
355 WHERE st.NAME='test/t1o' AND sc.NAME='FTS_DOC_ID';
356
357 SHOW CREATE TABLE t1o;
358
359 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
360 DROP INDEX ct, ALGORITHM=INPLACE;
361
362 SHOW CREATE TABLE t1o;
363
364 DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign;
365
366 # Check the internal schemata of tt, t1o.
367
368 CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
369 WHERE NAME='test/t1o';
370 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
371 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
372 CREATE TABLE sys_foreign SELECT i.*
373 FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN i WHERE FOR_NAME='test/t1o';
374
375 -- source suite/innodb/include/innodb_dict.inc
376
377 # Ensure that there exists no hidden FTS_DOC_ID_INDEX on foo_id.
378
379 ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID),
380 ADD FULLTEXT INDEX(ct),
381 CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
382
383 ALTER TABLE t1o DROP INDEX ct, DROP INDEX FTS_DOC_ID_INDEX,
384 CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL;
385
386 ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id);
387
388 ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
389 ADD FULLTEXT INDEX(ct);
390
391 DROP TABLE sys_indexes;
392 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
393 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
394
395 -- source suite/innodb/include/innodb_dict.inc
396
397 DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign;
398
399 CREATE TABLE t (t TEXT, FULLTEXT(t)) ENGINE=InnoDB;
400 DROP INDEX t ON t;
401
402 LET $regexp=/FTS_([0-9a-f_]+)([A-Z_]+)/FTS_AUX_\2/;
403 --replace_regex $regexp
404 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
405 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
406 WHERE name LIKE '%FTS_%' ORDER BY 1, 2;
407
408 SELECT sc.pos, sc.NAME FROM information_schema.innodb_sys_columns sc
409 INNER JOIN information_schema.innodb_sys_tables st
410 ON sc.TABLE_ID=st.TABLE_ID
411 WHERE st.NAME='test/t';
412
413 ALTER TABLE t ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;
414
415 --replace_regex $regexp
416 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
417 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
418 WHERE name LIKE '%FTS_%' ORDER BY 1, 2;
419
420 ALTER TABLE t ADD FULLTEXT INDEX(t);
421
422 SELECT sc.pos, sc.NAME FROM information_schema.innodb_sys_columns sc
423 INNER JOIN information_schema.innodb_sys_tables st
424 ON sc.TABLE_ID=st.TABLE_ID
425 WHERE st.NAME='test/t';
426
427 DROP TABLE t;
428
429 --disable_query_log
430 call mtr.add_suppression("deleting orphaned .ibd file");
431 --enable_query_log
432
433 --echo #
434 --echo # Bug #19465984 INNODB DATA DICTIONARY IS NOT UPDATED WHILE
435 --echo # RENAMING THE COLUMN
436 --echo #
437 CREATE TABLE t1(c1 INT NOT NULL, PRIMARY KEY(c1))ENGINE=INNODB;
438 CREATE TABLE t2(c2 INT NOT NULL, FOREIGN KEY(c2) REFERENCES t1(c1))ENGINE=INNODB;
439 SHOW CREATE TABLE t1;
440 SHOW CREATE TABLE t2;
441 ALTER TABLE t1 CHANGE COLUMN c1 C1 INT;
442 ALTER TABLE t2 CHANGE COLUMN c2 C2 INT;
443 SHOW CREATE TABLE t1;
444 SHOW CREATE TABLE t2;
445 ALTER TABLE t1 CHANGE COLUMN C1 c5 INT;
446 ALTER TABLE t2 CHANGE COLUMN C2 c6 INT;
447 SHOW CREATE TABLE t1;
448 SHOW CREATE TABLE t2;
449
450 SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN
451 INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON C.TABLE_ID=T.TABLE_ID
452 WHERE T.NAME='test/t1';
453
454 SELECT F.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS F INNER JOIN
455 INFORMATION_SCHEMA.INNODB_SYS_INDEXES I ON F.INDEX_ID=I.INDEX_ID INNER JOIN
456 INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON I.TABLE_ID=T.TABLE_ID
457 WHERE T.NAME='test/t1' AND I.NAME='PRIMARY';
458
459 SELECT C.REF_COL_NAME, C.FOR_COL_NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS C INNER JOIN
460 INFORMATION_SCHEMA.INNODB_SYS_FOREIGN F ON C.ID=F.ID
461 WHERE F.FOR_NAME='test/t2';
462
463 DROP TABLE t2, t1;
464 --echo # virtual columns case too
465 CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB;
466 ALTER TABLE t1 CHANGE COLUMN a A INT;
467 SHOW CREATE TABLE t1;
468 SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN
469 INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON C.TABLE_ID=T.TABLE_ID
470 WHERE T.NAME='test/t1';
471 DROP TABLE t1;
472
473
474 --echo # different FOREIGN KEY cases
475 CREATE TABLE t1 (
476 a INT UNIQUE KEY,
477 b INT UNIQUE KEY,
478 c INT UNIQUE KEY,
479 d INT UNIQUE KEY
480 ) ENGINE=INNODB;
481 CREATE TABLE t2 (
482 aa INT,
483 bb INT,
484 cc INT,
485 dd INT
486 ) ENGINE=INNODB;
487
488 INSERT INTO t1 VALUES (1, 1, 1, 1);
489 INSERT INTO t2 VALUES (1, 1, 1, 1);
490
491 ALTER TABLE t1 CHANGE a A INT, ALGORITHM=INPLACE;
492 ALTER TABLE t1 CHANGE c C INT, ALGORITHM=INPLACE;
493 ALTER TABLE t2 CHANGE cc CC INT, ALGORITHM=INPLACE;
494 ALTER TABLE t2 CHANGE dd DD INT, ALGORITHM=INPLACE;
495
496 SET foreign_key_checks=0;
497 ALTER TABLE t2
498 ADD FOREIGN KEY(aa) REFERENCES t1(a),
499 ADD FOREIGN KEY(bb) REFERENCES t1(b),
500 ADD FOREIGN KEY(cc) REFERENCES t1(c),
501 ADD FOREIGN KEY(dd) REFERENCES t1(d),
502 ALGORITHM=INPLACE;
503
504 ALTER TABLE t1 CHANGE b B INT, ALGORITHM=INPLACE;
505 ALTER TABLE t2 CHANGE aa AA INT, ALGORITHM=INPLACE;
506
507 --source include/restart_mysqld.inc
508
509 ALTER TABLE t1 CHANGE d D INT, ALGORITHM=INPLACE;
510 ALTER TABLE t2 CHANGE bb BB INT, ALGORITHM=INPLACE;
511 SHOW CREATE TABLE t1;
512 SHOW CREATE TABLE t2;
513
514 --error ER_ROW_IS_REFERENCED_2
515 DELETE FROM t1 WHERE a=1;
516 --error ER_ROW_IS_REFERENCED_2
517 DELETE FROM t1 WHERE A=1;
518
519 --error ER_ROW_IS_REFERENCED_2
520 DELETE FROM t1 WHERE b=1;
521 --error ER_ROW_IS_REFERENCED_2
522 DELETE FROM t1 WHERE B=1;
523
524 --error ER_ROW_IS_REFERENCED_2
525 DELETE FROM t1 WHERE c=1;
526 --error ER_ROW_IS_REFERENCED_2
527 DELETE FROM t1 WHERE C=1;
528
529 --error ER_ROW_IS_REFERENCED_2
530 DELETE FROM t1 WHERE d=1;
531 --error ER_ROW_IS_REFERENCED_2
532 DELETE FROM t1 WHERE D=1;
533
534 DROP TABLE t2, t1;
535
536 --echo # virtual columns case too
537 CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL) ENGINE = InnoDB;
538 ALTER TABLE t1 CHANGE COLUMN a A INT;
539 SHOW CREATE TABLE t1;
540 SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN
541 INFORMATION_SCHEMA.INNODB_SYS_TABLES T ON C.TABLE_ID=T.TABLE_ID
542 WHERE T.NAME='test/t1';
543 DROP TABLE t1;
544
545 --echo # and an MDEV-18041 regression related to indexes prefixes
546 create table `test` (
547 `test_old` varchar(255) NOT NULL,
548 `other` varchar(255) NOT NULL,
549 PRIMARY KEY (`test_old`,`other`),
550 UNIQUE KEY uk (`test_old`(100), `other`)
551 ) ENGINE=InnoDB;
552
553 select name, pos from information_schema.innodb_SYS_FIELDS where name in ('test_old', 'other', 'test_new');
554 alter table `test` CHANGE COLUMN `test_old` `test_new` varchar(255) NOT NULL;
555 select name, pos from information_schema.innodb_SYS_FIELDS where name in ('test_old', 'other', 'test_new');
556 drop table `test`;
557
558
559 --echo #
560 --echo # BUG 20029625 - HANDLE_FATAL_SIGNAL (SIG=11) IN
561 --echo # DICT_MEM_TABLE_COL_RENAME_LOW
562 --echo #
563 CREATE TABLE parent(a INT, b INT, KEY(a, b)) ENGINE = InnoDB;
564 CREATE TABLE t1(a1 INT, a2 INT) ENGINE = InnoDB;
565
566 set foreign_key_checks=0;
567 ALTER TABLE t1 ADD CONSTRAINT fk_a FOREIGN KEY(a1, a2) REFERENCES parent(a, b) ON DELETE SET NULL ON UPDATE CASCADE;
568
569 ALTER TABLE t1 CHANGE a2 a3 INT,ADD CONSTRAINT fk_1 FOREIGN KEY(a1, a3) REFERENCES parent(a, b) ON DELETE SET NULL ON UPDATE CASCADE;
570 SHOW CREATE TABLE t1;
571 CHECK TABLE t1;
572
573 ALTER TABLE t1 CHANGE a3 a4 INT;
574 SHOW CREATE TABLE t1;
575 CHECK TABLE t1;
576
577 ALTER TABLE parent CHANGE b c INT;
578 SHOW CREATE TABLE t1;
579 CHECK TABLE t1;
580
581 DROP TABLE t1, parent;
582
583 --echo #
584 --echo #BUG#21514135 SCHEMA MISMATCH ERROR WHEN IMPORTING TABLESPACE AFTER
585 --echo #DROPPING AN INDEX
586 --echo #
587
588 --disable_query_log
589 call mtr.add_suppression("\\[Warning\\] InnoDB: Unknown index id .* on page");
590 --enable_query_log
591
592 let $source_db = source_db;
593 let $dest_db = dest_db;
594
595 SET NAMES utf8mb4;
596
597 eval CREATE DATABASE $source_db;
598 eval CREATE DATABASE $dest_db;
599
600 eval CREATE TABLE $source_db.t1 (
601 id int(11) NOT NULL,
602 age int(11) DEFAULT NULL,
603 name varchar(20),
604 PRIMARY KEY (id),
605 KEY index1 (age)
606 ) ENGINE=InnoDB;
607
608 eval ALTER TABLE $source_db.t1 DROP INDEX index1, ADD INDEX index2(name, age), algorithm=inplace;
609
610 --source suite/innodb/include/import.inc
611
612 eval ALTER TABLE $source_db.t1 DROP INDEX index2, algorithm=inplace;
613
614 --source suite/innodb/include/import.inc
615
616 eval DROP TABLE $source_db.t1;
617 eval DROP DATABASE $source_db;
618 eval DROP DATABASE $dest_db;
619
620 --echo #
621 --echo # BUG #26334149 MYSQL CRASHES WHEN FULL TEXT INDEXES IBD FILES ARE
622 --echo # ORPHANED DUE TO RENAME TABLE
623 --echo #
624 CREATE DATABASE db1; USE db1;
625 --disable_query_log
626 SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
627 SET GLOBAL innodb_read_only_compressed=OFF;
628 --enable_query_log
629 CREATE TABLE notes (
630 id int(11) NOT NULL AUTO_INCREMENT,
631 body text COLLATE utf8_unicode_ci,
632 PRIMARY KEY (id)
633 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
634 COLLATE=utf8_unicode_ci
635 ROW_FORMAT=COMPRESSED;
636
637 ALTER TABLE notes ADD FULLTEXT INDEX index_ft_body (body(255));
638 DROP INDEX index_ft_body ON notes;
639 --disable_query_log
640 SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
641 --enable_query_log
642
643 CREATE DATABASE db2;
644 RENAME TABLE db1.notes TO db2.notes;
645 DROP DATABASE db1;
646 DROP DATABASE db2;
647
648 USE test;
649
650 #
651 # End of 10.1 tests
652 #
653
654 --echo #
655 --echo # MDEV-14038 ALTER TABLE does not exit on error with InnoDB + bad default function
656 --echo #
657
658 CREATE TABLE t1 (a INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
659 iNSERT INTO t1 VALUES (10);
660 --error ER_WARN_DATA_OUT_OF_RANGE
661 ALTER TABLE t1 ADD b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0);
662 SELECT * FROM t1;
663 DROP TABLE t1;
664
665 # DATETIME-to-DATE truncation is OK
666 CREATE TABLE t1 (a INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
667 INSERT INTO t1 VALUES (10);
668 --enable_info
669 ALTER TABLE t1 ADD b DATE NOT NULL DEFAULT if(unix_timestamp()>1,TIMESTAMP'2001-01-01 10:20:30',0), algorithm=copy;
670 --disable_info
671 SELECT * FROM t1;
672 DROP TABLE t1;
673
674 # DATETIME-to-TIME truncation is OK
675 CREATE TABLE t1 (a INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
676 iNSERT INTO t1 VALUES (10);
677 --enable_info
678 ALTER TABLE t1 ADD b TIME NOT NULL DEFAULT if(unix_timestamp()>1,TIMESTAMP'2001-01-01 10:20:30',0);
679 --disable_info
680 SELECT * FROM t1;
681 DROP TABLE t1;
682
683 --echo #
684 --echo # MDEV-18042 Server crashes in mysql_alter_table upon adding a non-null
685 --echo # date column under NO_ZERO_DATE with ALGORITHM=INPLACE
686 --echo #
687
688 SET @OLD_SQL_MODE= @@SQL_MODE;
689 SET @@SQL_MODE= 'NO_ZERO_DATE';
690 CREATE OR REPLACE TABLE t1 (i INT) ENGINE=MyISAM;
691 --error ER_ALTER_OPERATION_NOT_SUPPORTED
692 ALTER TABLE t1 ADD COLUMN d DATE NOT NULL, ALGORITHM=INPLACE;
693
694 CREATE OR REPLACE TABLE t1 (i INT) ENGINE=InnoDB;
695 --error ER_BAD_FIELD_ERROR
696 ALTER TABLE t1 ADD d DATETIME NOT NULL CHECK (f <= 0), ALGORITHM=COPY;
697
698 CREATE OR REPLACE TABLE t1 (a int) ENGINE=InnoDB;
699 ALTER TABLE t1 ADD COLUMN b DATETIME NOT NULL, LOCK=NONE;
700
701 --echo # Cleanup
702 SET @@SQL_MODE= @OLD_SQL_MODE;
703 DROP TABLE t1;
704
705 --echo #
706 --echo # Bug#20977779 CANNOT IMPORT TABLES CONTAINING PREFIX INDEXES
707 --echo #
708
709 CREATE TABLE t1 (c1 VARCHAR(32), c2 VARCHAR(32), c3 VARCHAR(32),
710 PRIMARY KEY (c1, c2, c3))
711 ENGINE=InnoDB;
712
713 ALTER TABLE t1 ADD INDEX ind1(c1(5), c2, c3);
714 ALTER TABLE t1 ADD INDEX ind2(c3, c1(10), c2);
715 ALTER TABLE t1 ADD INDEX ind3(c2, c3, c1(20));
716
717 INSERT INTO t1 VALUES ('Test Data -1', 'Test Data -2', 'Test Data -3');
718
719 let $source_db = test;
720 let $dest_db = test;
721
722 --echo # Test with 2ndary index having prefix
723 --source suite/innodb/include/import.inc
724
725 --echo # Test with PK & 2ndary index with prefix
726 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c1(5), c2(10), c3(20));
727 --source suite/innodb/include/import.inc
728
729 DROP TABLE t1;
730
731 #
732 # End of 10.2 tests
733 #
734
735 --source include/test_db_charset_restore.inc