Debian Codegraph @

mysql-test/suite/innodb/r/choose_tbsp_location-alter.result

raw
1 #
2 # This is a copy of innodb-alter.test except using remote tablespaces
3 # and showing those files.
4 #
5 SET default_storage_engine=InnoDB;
6 SET GLOBAL innodb_file_per_table=ON;
7 SET NAMES utf8;
8 Warnings:
9 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
10 CREATE TABLE t1 (
11 c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT,
12 INDEX(c2))
13 ENGINE=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
14 INSERT INTO t1 SET c1=1;
15 CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES
16 WHERE NAME LIKE 'test/t%';
17 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
18 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
19 CREATE TABLE t1p LIKE t1;
20 CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
21 CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES t1(c2),
22 CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2))
23 ENGINE=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
24 CREATE TABLE sys_foreign SELECT i.*
25 FROM INFORMATION_SCHEMA.INNODB_FOREIGN i
26 WHERE FOR_NAME LIKE 'test/t%';
27 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
28 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
29 FOR_COL_NAME REF_COL_NAME POS
30 c2 c2 1
31 c3 c2 1
32 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
33 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
34 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
35 NAME POS MTYPE PRTYPE LEN
36 c1 0 6 1283 4
37 c2 1 6 1027 4
38 ct 2 5 16711932 10
39 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
40 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
41 NAME POS NAME
42 c2 0 c2
43 PRIMARY 0 c1
44 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
45 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
46 FOR_COL_NAME REF_COL_NAME POS
47 c2 c2 1
48 c3 c2 1
49 SHOW CREATE TABLE t1;
50 Table Create Table
51 t1 CREATE TABLE `t1` (
52 `c1` int NOT NULL,
53 `c2` int DEFAULT '1',
54 `ct` text,
55 PRIMARY KEY (`c1`),
56 KEY `c2` (`c2`)
57 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
58 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
59 SHOW CREATE TABLE t1;
60 Table Create Table
61 t1 CREATE TABLE `t1` (
62 `c1` int NOT NULL,
63 `c2` int,
64 `ct` text,
65 PRIMARY KEY (`c1`),
66 KEY `c2` (`c2`)
67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
68 ### files in MYSQL_DATA_DIR/test
69 sys_foreign.ibd
70 sys_indexes.ibd
71 sys_tables.ibd
72 t1p.ibd
73 ### files in MYSQL_TMP_DIR/alt_dir/test
74 t1.ibd
75 t1c.ibd
76 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
77 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
78 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
79 NAME POS MTYPE PRTYPE LEN
80 c1 0 6 1283 4
81 c2 1 6 1027 4
82 ct 2 5 16711932 10
83 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
84 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
85 NAME POS NAME
86 c2 0 c2
87 PRIMARY 0 c1
88 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
89 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
90 FOR_COL_NAME REF_COL_NAME POS
91 c2 c2 1
92 c3 c2 1
93 ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1;
94 ALTER TABLE t1 CHANGE c1 c1 INT FIRST;
95 ### files in MYSQL_DATA_DIR/test
96 sys_foreign.ibd
97 sys_indexes.ibd
98 sys_tables.ibd
99 t1p.ibd
100 ### files in MYSQL_TMP_DIR/alt_dir/test
101 t1.ibd
102 t1c.ibd
103 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
104 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
105 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
106 NAME POS MTYPE PRTYPE LEN
107 c1 0 6 1283 4
108 c2 1 6 1027 4
109 ct 2 5 16711932 10
110 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
111 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
112 NAME POS NAME
113 c2 0 c2
114 PRIMARY 0 c1
115 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
116 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
117 FOR_COL_NAME REF_COL_NAME POS
118 c2 c2 1
119 c3 c2 1
120 ALTER TABLE t1 CHANGE C2 c3 INT;
121 ### files in MYSQL_DATA_DIR/test
122 sys_foreign.ibd
123 sys_indexes.ibd
124 sys_tables.ibd
125 t1p.ibd
126 ### files in MYSQL_TMP_DIR/alt_dir/test
127 t1.ibd
128 t1c.ibd
129 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
130 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
131 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
132 NAME POS MTYPE PRTYPE LEN
133 c1 0 6 1283 4
134 c3 1 6 1027 4
135 ct 2 5 16711932 10
136 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
137 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
138 NAME POS NAME
139 c2 0 c3
140 PRIMARY 0 c1
141 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
142 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
143 FOR_COL_NAME REF_COL_NAME POS
144 c2 c3 1
145 c3 c2 1
146 ALTER TABLE t1 CHANGE c3 C INT;
147 ### files in MYSQL_DATA_DIR/test
148 sys_foreign.ibd
149 sys_indexes.ibd
150 sys_tables.ibd
151 t1p.ibd
152 ### files in MYSQL_TMP_DIR/alt_dir/test
153 t1.ibd
154 t1c.ibd
155 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
156 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
157 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
158 NAME POS MTYPE PRTYPE LEN
159 C 1 6 1027 4
160 c1 0 6 1283 4
161 ct 2 5 16711932 10
162 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
163 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
164 NAME POS NAME
165 c2 0 C
166 PRIMARY 0 c1
167 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
168 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
169 FOR_COL_NAME REF_COL_NAME POS
170 c2 C 1
171 c3 c2 1
172 ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT;
173 ### files in MYSQL_DATA_DIR/test
174 sys_foreign.ibd
175 sys_indexes.ibd
176 sys_tables.ibd
177 t1p.ibd
178 ### files in MYSQL_TMP_DIR/alt_dir/test
179 t1.ibd
180 t1c.ibd
181 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
182 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
183 FOR_COL_NAME REF_COL_NAME POS
184 c2 Cöŀumň_TWO 1
185 c3 c2 1
186 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
187 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
188 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
189 NAME POS MTYPE PRTYPE LEN
190 c1 0 6 1283 4
191 Cöŀumň_TWO 1 6 1027 4
192 ct 2 5 16711932 10
193 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
194 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
195 NAME POS NAME
196 c2 0 Cöŀumň_TWO
197 PRIMARY 0 c1
198 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
199 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
200 FOR_COL_NAME REF_COL_NAME POS
201 c2 Cöŀumň_TWO 1
202 c3 c2 1
203 ALTER TABLE t1 CHANGE cöĿǖmň_two c3 INT;
204 ERROR 42S22: Unknown column 'cöĿǖmň_two' in 't1'
205 ALTER TABLE t1 CHANGE cÖĿUMŇ_two c3 INT, RENAME TO t3;
206 ### files in MYSQL_DATA_DIR/test
207 sys_foreign.ibd
208 sys_indexes.ibd
209 sys_tables.ibd
210 t1p.ibd
211 ### files in MYSQL_TMP_DIR/alt_dir/test
212 t1c.ibd
213 t3.ibd
214 SELECT st.NAME, i.NAME
215 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_TABLES i
216 ON i.TABLE_ID=st.TABLE_ID;
217 NAME NAME
218 test/t1 test/t3
219 SHOW CREATE TABLE t3;
220 Table Create Table
221 t3 CREATE TABLE `t3` (
222 `c1` int NOT NULL,
223 `c3` int DEFAULT NULL,
224 `ct` text,
225 PRIMARY KEY (`c1`),
226 KEY `c2` (`c3`)
227 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
228 SHOW CREATE TABLE t1c;
229 Table Create Table
230 t1c CREATE TABLE `t1c` (
231 `c1` int NOT NULL,
232 `c2` int DEFAULT NULL,
233 `c3` int DEFAULT NULL,
234 PRIMARY KEY (`c1`),
235 KEY `c2` (`c2`),
236 KEY `c3` (`c3`),
237 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t3` (`c3`),
238 CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`)
239 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
240 ALTER TABLE t3 CHANGE c3
241 `12345678901234567890123456789012345678901234567890123456789012345` INT;
242 ERROR 42000: Identifier name '12345678901234567890123456789012345678901234567890123456789012345' is too long
243 ALTER TABLE t3 CHANGE c3
244 `1234567890123456789012345678901234567890123456789012345678901234` INT;
245 ### files in MYSQL_DATA_DIR/test
246 sys_foreign.ibd
247 sys_indexes.ibd
248 sys_tables.ibd
249 t1p.ibd
250 ### files in MYSQL_TMP_DIR/alt_dir/test
251 t1c.ibd
252 t3.ibd
253 SHOW CREATE TABLE t3;
254 Table Create Table
255 t3 CREATE TABLE `t3` (
256 `c1` int NOT NULL,
257 `1234567890123456789012345678901234567890123456789012345678901234` int DEFAULT NULL,
258 `ct` text,
259 PRIMARY KEY (`c1`),
260 KEY `c2` (`1234567890123456789012345678901234567890123456789012345678901234`)
261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
262 ALTER TABLE t3 CHANGE
263 `1234567890123456789012345678901234567890123456789012345678901234`
264 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿偀` INT;
265 ERROR 42000: Identifier name '倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠?' is too long
266 ALTER TABLE t3 CHANGE
267 `1234567890123456789012345678901234567890123456789012345678901234`
268 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿ä` INT;
269 ERROR 42000: Identifier name '倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠?' is too long
270 ALTER TABLE t3 CHANGE
271 `1234567890123456789012345678901234567890123456789012345678901234`
272 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾ä` INT;
273 ### files in MYSQL_DATA_DIR/test
274 sys_foreign.ibd
275 sys_indexes.ibd
276 sys_tables.ibd
277 t1p.ibd
278 ### files in MYSQL_TMP_DIR/alt_dir/test
279 t1c.ibd
280 t3.ibd
281 ALTER TABLE t3 CHANGE
282 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾Ä`
283 c3 INT;
284 ### files in MYSQL_DATA_DIR/test
285 sys_foreign.ibd
286 sys_indexes.ibd
287 sys_tables.ibd
288 t1p.ibd
289 ### files in MYSQL_TMP_DIR/alt_dir/test
290 t1c.ibd
291 t3.ibd
292 ALTER TABLE t3 CHANGE c3 𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜 INT;
293 ERROR HY000: Invalid utf8mb3 character string: '\xF0\x90\x8C\x80\xF0\x90\x8C\x81\xF0\x90\x8C\x82\xF0\x90\x8C\x83'
294 ALTER TABLE t3 CHANGE c3 😲 INT;
295 ERROR HY000: Invalid utf8mb3 character string: '\xF0\x9F\x98\xB2'
296 ALTER TABLE t3 RENAME TO t2;
297 ### files in MYSQL_DATA_DIR/test
298 sys_foreign.ibd
299 sys_indexes.ibd
300 sys_tables.ibd
301 t1p.ibd
302 ### files in MYSQL_TMP_DIR/alt_dir/test
303 t1c.ibd
304 t2.ibd
305 SELECT st.NAME, i.NAME
306 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_TABLES i
307 ON i.TABLE_ID=st.TABLE_ID;
308 NAME NAME
309 test/t1 test/t2
310 SHOW CREATE TABLE t2;
311 Table Create Table
312 t2 CREATE TABLE `t2` (
313 `c1` int NOT NULL,
314 `c3` int DEFAULT NULL,
315 `ct` text,
316 PRIMARY KEY (`c1`),
317 KEY `c2` (`c3`)
318 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
319 RENAME TABLE t2 TO t1;
320 SELECT st.NAME, i.NAME
321 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_TABLES i
322 ON i.TABLE_ID=st.TABLE_ID;
323 NAME NAME
324 test/t1 test/t1
325 ### files in MYSQL_DATA_DIR/test
326 sys_foreign.ibd
327 sys_indexes.ibd
328 sys_tables.ibd
329 t1p.ibd
330 ### files in MYSQL_TMP_DIR/alt_dir/test
331 t1.ibd
332 t1c.ibd
333 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
334 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
335 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
336 NAME POS MTYPE PRTYPE LEN
337 c1 0 6 1283 4
338 c3 1 6 1027 4
339 ct 2 5 16711932 10
340 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
341 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
342 NAME POS NAME
343 c2 0 c3
344 PRIMARY 0 c1
345 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
346 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
347 FOR_COL_NAME REF_COL_NAME POS
348 c2 c3 1
349 c3 c2 1
350 ALTER TABLE t1 DROP INDEX c2;
351 ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint
352 ALTER TABLE t1 DROP INDEX c4;
353 ERROR 42000: Can't DROP 'c4'; check that column/key exists
354 ALTER TABLE t1c DROP FOREIGN KEY c2;
355 ERROR 42000: Can't DROP 'c2'; check that column/key exists
356 ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2;
357 ERROR 42000: Can't DROP 'c2'; check that column/key exists
358 ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2, DROP INDEX c2;
359 ERROR 42000: Can't DROP 'c2'; check that column/key exists
360 ALTER TABLE t1c DROP INDEX c2;
361 ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint
362 ALTER TABLE t1c DROP FOREIGN KEY ẗ1C2;
363 ERROR 42000: Can't DROP 'ẗ1C2'; check that column/key exists
364 SHOW CREATE TABLE t1c;
365 Table Create Table
366 t1c CREATE TABLE `t1c` (
367 `c1` int NOT NULL,
368 `c2` int DEFAULT NULL,
369 `c3` int DEFAULT NULL,
370 PRIMARY KEY (`c1`),
371 KEY `c2` (`c2`),
372 KEY `c3` (`c3`),
373 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`),
374 CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`)
375 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
376 SET foreign_key_checks=0;
377 DROP TABLE t1p;
378 SET foreign_key_checks=1;
379 SHOW CREATE TABLE t1c;
380 Table Create Table
381 t1c CREATE TABLE `t1c` (
382 `c1` int NOT NULL,
383 `c2` int DEFAULT NULL,
384 `c3` int DEFAULT NULL,
385 PRIMARY KEY (`c1`),
386 KEY `c2` (`c2`),
387 KEY `c3` (`c3`),
388 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`),
389 CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`)
390 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
391 ### files in MYSQL_DATA_DIR/test
392 sys_foreign.ibd
393 sys_indexes.ibd
394 sys_tables.ibd
395 ### files in MYSQL_TMP_DIR/alt_dir/test
396 t1.ibd
397 t1c.ibd
398 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
399 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
400 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
401 NAME POS MTYPE PRTYPE LEN
402 c1 0 6 1283 4
403 c3 1 6 1027 4
404 ct 2 5 16711932 10
405 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
406 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
407 NAME POS NAME
408 c2 0 c3
409 PRIMARY 0 c1
410 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
411 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
412 FOR_COL_NAME REF_COL_NAME POS
413 c2 c3 1
414 c3 c2 1
415 CREATE TABLE t1p (c1 INT PRIMARY KEY, c2 INT, INDEX(c2))
416 ENGINE=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir';
417 ALTER TABLE t1c DROP INDEX C2, DROP INDEX C3;
418 ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint
419 ALTER TABLE t1c DROP INDEX C3;
420 ERROR HY000: Cannot drop index 'c3': needed in a foreign key constraint
421 SET foreign_key_checks=0;
422 ALTER TABLE t1c DROP INDEX C3;
423 ERROR HY000: Cannot drop index 'c3': needed in a foreign key constraint
424 ### files in MYSQL_DATA_DIR/test
425 sys_foreign.ibd
426 sys_indexes.ibd
427 sys_tables.ibd
428 ### files in MYSQL_TMP_DIR/alt_dir/test
429 t1.ibd
430 t1c.ibd
431 t1p.ibd
432 SET foreign_key_checks=1;
433 SHOW CREATE TABLE t1c;
434 Table Create Table
435 t1c CREATE TABLE `t1c` (
436 `c1` int NOT NULL,
437 `c2` int DEFAULT NULL,
438 `c3` int DEFAULT NULL,
439 PRIMARY KEY (`c1`),
440 KEY `c2` (`c2`),
441 KEY `c3` (`c3`),
442 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`),
443 CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`)
444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
445 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
446 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
447 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
448 NAME POS MTYPE PRTYPE LEN
449 c1 0 6 1283 4
450 c3 1 6 1027 4
451 ct 2 5 16711932 10
452 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
453 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
454 NAME POS NAME
455 c2 0 c3
456 PRIMARY 0 c1
457 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
458 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
459 FOR_COL_NAME REF_COL_NAME POS
460 c2 c3 1
461 c3 c2 1
462 ALTER TABLE t1c DROP FOREIGN KEY t1C3;
463 ### files in MYSQL_DATA_DIR/test
464 sys_foreign.ibd
465 sys_indexes.ibd
466 sys_tables.ibd
467 ### files in MYSQL_TMP_DIR/alt_dir/test
468 t1.ibd
469 t1c.ibd
470 t1p.ibd
471 SHOW CREATE TABLE t1c;
472 Table Create Table
473 t1c CREATE TABLE `t1c` (
474 `c1` int NOT NULL,
475 `c2` int DEFAULT NULL,
476 `c3` int DEFAULT NULL,
477 PRIMARY KEY (`c1`),
478 KEY `c2` (`c2`),
479 KEY `c3` (`c3`),
480 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`)
481 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
482 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
483 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
484 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
485 NAME POS MTYPE PRTYPE LEN
486 c1 0 6 1283 4
487 c3 1 6 1027 4
488 ct 2 5 16711932 10
489 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
490 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
491 NAME POS NAME
492 c2 0 c3
493 PRIMARY 0 c1
494 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
495 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
496 FOR_COL_NAME REF_COL_NAME POS
497 c2 c3 1
498 ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2;
499 ### files in MYSQL_DATA_DIR/test
500 sys_foreign.ibd
501 sys_indexes.ibd
502 sys_tables.ibd
503 ### files in MYSQL_TMP_DIR/alt_dir/test
504 t1.ibd
505 t1c.ibd
506 t1p.ibd
507 SHOW CREATE TABLE t1c;
508 Table Create Table
509 t1c CREATE TABLE `t1c` (
510 `c1` int NOT NULL,
511 `c2` int DEFAULT NULL,
512 `c3` int DEFAULT NULL,
513 PRIMARY KEY (`c1`),
514 KEY `c3` (`c3`)
515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
516 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
517 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
518 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
519 NAME POS MTYPE PRTYPE LEN
520 c1 0 6 1283 4
521 c3 1 6 1027 4
522 ct 2 5 16711932 10
523 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
524 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
525 NAME POS NAME
526 c2 0 c3
527 PRIMARY 0 c1
528 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
529 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
530 FOR_COL_NAME REF_COL_NAME POS
531 ALTER TABLE t1 DROP INDEX c2, CHANGE c3 c2 INT;
532 ### files in MYSQL_DATA_DIR/test
533 sys_foreign.ibd
534 sys_indexes.ibd
535 sys_tables.ibd
536 ### files in MYSQL_TMP_DIR/alt_dir/test
537 t1.ibd
538 t1c.ibd
539 t1p.ibd
540 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
541 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
542 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
543 NAME POS MTYPE PRTYPE LEN
544 c1 0 6 1283 4
545 c2 1 6 1027 4
546 ct 2 5 16711932 10
547 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
548 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
549 NAME POS NAME
550 PRIMARY 0 c1
551 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
552 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
553 FOR_COL_NAME REF_COL_NAME POS
554 CREATE TABLE t1o LIKE t1;
555 ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
556 CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
557 ALGORITHM=INPLACE, LOCK=NONE;
558 ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED.
559 ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
560 CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
561 ALGORITHM=INPLACE, LOCK=SHARED;
562 Warnings:
563 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
564 ### files in MYSQL_DATA_DIR/test
565 sys_foreign.ibd
566 sys_indexes.ibd
567 sys_tables.ibd
568 t1o.ibd
569 ### files in MYSQL_TMP_DIR/alt_dir/test
570 fts_aux_index_1.ibd
571 fts_aux_index_2.ibd
572 fts_aux_index_3.ibd
573 fts_aux_index_4.ibd
574 fts_aux_index_5.ibd
575 fts_aux_index_6.ibd
576 fts_aux_being_deleted.ibd
577 fts_aux_being_deleted_cache.ibd
578 fts_aux_config.ibd
579 fts_aux_deleted.ibd
580 fts_aux_deleted_cache.ibd
581 t1c.ibd
582 t1p.ibd
583 tt.ibd
584 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
585 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
586 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
587 NAME POS MTYPE PRTYPE LEN
588 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
589 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
590 NAME POS NAME
591 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
592 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
593 FOR_COL_NAME REF_COL_NAME POS
594 SHOW CREATE TABLE tt;
595 Table Create Table
596 tt CREATE TABLE `tt` (
597 `pk` int NOT NULL,
598 `c2` int DEFAULT '42',
599 `ct` text,
600 PRIMARY KEY (`pk`),
601 FULLTEXT KEY `ct` (`ct`)
602 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/'
603 ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=COPY;
604 ERROR 42000: Incorrect column name 'dB_row_Id'
605 ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=INPLACE;
606 ERROR 42000: Incorrect column name 'DB_ROW_ID'
607 ALTER TABLE t1o CHANGE c1 DB_TRX_ID INT;
608 ERROR 42000: Incorrect column name 'DB_TRX_ID'
609 ALTER TABLE t1o CHANGE c1 db_roll_ptr INT;
610 ERROR 42000: Incorrect column name 'db_roll_ptr'
611 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
612 ALGORITHM=COPY;
613 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
614 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
615 ALGORITHM=INPLACE;
616 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
617 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_Doc_ID INT,
618 ALGORITHM=INPLACE;
619 ERROR HY000: Column 'FTS_Doc_ID' is of wrong type for an InnoDB FULLTEXT index
620 ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
621 CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
622 ALGORITHM=INPLACE;
623 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
624 CREATE TABLE t1n LIKE t1o;
625 ALTER TABLE t1n ADD FULLTEXT INDEX(ct);
626 Warnings:
627 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
628 ### files in MYSQL_DATA_DIR/test
629 fts_aux_index_1.ibd
630 fts_aux_index_2.ibd
631 fts_aux_index_3.ibd
632 fts_aux_index_4.ibd
633 fts_aux_index_5.ibd
634 fts_aux_index_6.ibd
635 fts_aux_being_deleted.ibd
636 fts_aux_being_deleted_cache.ibd
637 fts_aux_config.ibd
638 fts_aux_deleted.ibd
639 fts_aux_deleted_cache.ibd
640 sys_foreign.ibd
641 sys_indexes.ibd
642 sys_tables.ibd
643 t1n.ibd
644 t1o.ibd
645 ### files in MYSQL_TMP_DIR/alt_dir/test
646 fts_aux_index_1.ibd
647 fts_aux_index_2.ibd
648 fts_aux_index_3.ibd
649 fts_aux_index_4.ibd
650 fts_aux_index_5.ibd
651 fts_aux_index_6.ibd
652 fts_aux_being_deleted.ibd
653 fts_aux_being_deleted_cache.ibd
654 fts_aux_config.ibd
655 fts_aux_deleted.ibd
656 fts_aux_deleted_cache.ibd
657 t1c.ibd
658 t1p.ibd
659 tt.ibd
660 ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=INPLACE;
661 ERROR HY000: Column 'Fts_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
662 ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=COPY;
663 ERROR HY000: Column 'Fts_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
664 ALTER TABLE t1n CHANGE FTS_DOC_ID c11 INT, ALGORITHM=INPLACE;
665 ERROR 42S22: Unknown column 'FTS_DOC_ID' in 't1n'
666 ALTER TABLE t1n CHANGE c1 FTS_DOC_ïD INT, ALGORITHM=INPLACE;
667 ### files in MYSQL_DATA_DIR/test
668 fts_aux_index_1.ibd
669 fts_aux_index_2.ibd
670 fts_aux_index_3.ibd
671 fts_aux_index_4.ibd
672 fts_aux_index_5.ibd
673 fts_aux_index_6.ibd
674 fts_aux_being_deleted.ibd
675 fts_aux_being_deleted_cache.ibd
676 fts_aux_config.ibd
677 fts_aux_deleted.ibd
678 fts_aux_deleted_cache.ibd
679 sys_foreign.ibd
680 sys_indexes.ibd
681 sys_tables.ibd
682 t1n.ibd
683 t1o.ibd
684 ### files in MYSQL_TMP_DIR/alt_dir/test
685 fts_aux_index_1.ibd
686 fts_aux_index_2.ibd
687 fts_aux_index_3.ibd
688 fts_aux_index_4.ibd
689 fts_aux_index_5.ibd
690 fts_aux_index_6.ibd
691 fts_aux_being_deleted.ibd
692 fts_aux_being_deleted_cache.ibd
693 fts_aux_config.ibd
694 fts_aux_deleted.ibd
695 fts_aux_deleted_cache.ibd
696 t1c.ibd
697 t1p.ibd
698 tt.ibd
699 ALTER TABLE t1n CHANGE FTS_DOC_ÏD c1 INT, ALGORITHM=INPLACE;
700 ### files in MYSQL_DATA_DIR/test
701 fts_aux_index_1.ibd
702 fts_aux_index_2.ibd
703 fts_aux_index_3.ibd
704 fts_aux_index_4.ibd
705 fts_aux_index_5.ibd
706 fts_aux_index_6.ibd
707 fts_aux_being_deleted.ibd
708 fts_aux_being_deleted_cache.ibd
709 fts_aux_config.ibd
710 fts_aux_deleted.ibd
711 fts_aux_deleted_cache.ibd
712 sys_foreign.ibd
713 sys_indexes.ibd
714 sys_tables.ibd
715 t1n.ibd
716 t1o.ibd
717 ### files in MYSQL_TMP_DIR/alt_dir/test
718 fts_aux_index_1.ibd
719 fts_aux_index_2.ibd
720 fts_aux_index_3.ibd
721 fts_aux_index_4.ibd
722 fts_aux_index_5.ibd
723 fts_aux_index_6.ibd
724 fts_aux_being_deleted.ibd
725 fts_aux_being_deleted_cache.ibd
726 fts_aux_config.ibd
727 fts_aux_deleted.ibd
728 fts_aux_deleted_cache.ibd
729 t1c.ibd
730 t1p.ibd
731 tt.ibd
732 ALTER TABLE t1n CHANGE c1 c2 INT, CHANGE c2 ct INT, CHANGE ct c1 TEXT,
733 ALGORITHM=INPLACE;
734 ### files in MYSQL_DATA_DIR/test
735 fts_aux_index_1.ibd
736 fts_aux_index_2.ibd
737 fts_aux_index_3.ibd
738 fts_aux_index_4.ibd
739 fts_aux_index_5.ibd
740 fts_aux_index_6.ibd
741 fts_aux_being_deleted.ibd
742 fts_aux_being_deleted_cache.ibd
743 fts_aux_config.ibd
744 fts_aux_deleted.ibd
745 fts_aux_deleted_cache.ibd
746 sys_foreign.ibd
747 sys_indexes.ibd
748 sys_tables.ibd
749 t1n.ibd
750 t1o.ibd
751 ### files in MYSQL_TMP_DIR/alt_dir/test
752 fts_aux_index_1.ibd
753 fts_aux_index_2.ibd
754 fts_aux_index_3.ibd
755 fts_aux_index_4.ibd
756 fts_aux_index_5.ibd
757 fts_aux_index_6.ibd
758 fts_aux_being_deleted.ibd
759 fts_aux_being_deleted_cache.ibd
760 fts_aux_config.ibd
761 fts_aux_deleted.ibd
762 fts_aux_deleted_cache.ibd
763 t1c.ibd
764 t1p.ibd
765 tt.ibd
766 SHOW CREATE TABLE t1n;
767 Table Create Table
768 t1n CREATE TABLE `t1n` (
769 `c2` int NOT NULL,
770 `ct` int DEFAULT NULL,
771 `c1` text,
772 PRIMARY KEY (`c2`),
773 FULLTEXT KEY `ct` (`c1`)
774 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
775 ALTER TABLE t1n CHANGE c2 c1 INT, CHANGE ct c2 INT, CHANGE c1 ct TEXT,
776 ALGORITHM=COPY;
777 ### files in MYSQL_DATA_DIR/test
778 fts_aux_index_1.ibd
779 fts_aux_index_2.ibd
780 fts_aux_index_3.ibd
781 fts_aux_index_4.ibd
782 fts_aux_index_5.ibd
783 fts_aux_index_6.ibd
784 fts_aux_being_deleted.ibd
785 fts_aux_being_deleted_cache.ibd
786 fts_aux_config.ibd
787 fts_aux_deleted.ibd
788 fts_aux_deleted_cache.ibd
789 sys_foreign.ibd
790 sys_indexes.ibd
791 sys_tables.ibd
792 t1n.ibd
793 t1o.ibd
794 ### files in MYSQL_TMP_DIR/alt_dir/test
795 fts_aux_index_1.ibd
796 fts_aux_index_2.ibd
797 fts_aux_index_3.ibd
798 fts_aux_index_4.ibd
799 fts_aux_index_5.ibd
800 fts_aux_index_6.ibd
801 fts_aux_being_deleted.ibd
802 fts_aux_being_deleted_cache.ibd
803 fts_aux_config.ibd
804 fts_aux_deleted.ibd
805 fts_aux_deleted_cache.ibd
806 t1c.ibd
807 t1p.ibd
808 tt.ibd
809 SHOW CREATE TABLE t1n;
810 Table Create Table
811 t1n CREATE TABLE `t1n` (
812 `c1` int NOT NULL,
813 `c2` int DEFAULT NULL,
814 `ct` text,
815 PRIMARY KEY (`c1`),
816 FULLTEXT KEY `ct` (`ct`)
817 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
818 ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
819 ERROR 42000: Key column 'c2' doesn't exist in table
820 ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY;
821 ERROR 42000: Key column 'c2' doesn't exist in table
822 ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
823 ### files in MYSQL_DATA_DIR/test
824 fts_aux_index_1.ibd
825 fts_aux_index_2.ibd
826 fts_aux_index_3.ibd
827 fts_aux_index_4.ibd
828 fts_aux_index_5.ibd
829 fts_aux_index_6.ibd
830 fts_aux_being_deleted.ibd
831 fts_aux_being_deleted_cache.ibd
832 fts_aux_config.ibd
833 fts_aux_deleted.ibd
834 fts_aux_deleted_cache.ibd
835 sys_foreign.ibd
836 sys_indexes.ibd
837 sys_tables.ibd
838 t1n.ibd
839 t1o.ibd
840 ### files in MYSQL_TMP_DIR/alt_dir/test
841 fts_aux_index_1.ibd
842 fts_aux_index_2.ibd
843 fts_aux_index_3.ibd
844 fts_aux_index_4.ibd
845 fts_aux_index_5.ibd
846 fts_aux_index_6.ibd
847 fts_aux_being_deleted.ibd
848 fts_aux_being_deleted_cache.ibd
849 fts_aux_config.ibd
850 fts_aux_deleted.ibd
851 fts_aux_deleted_cache.ibd
852 t1c.ibd
853 t1p.ibd
854 tt.ibd
855 SHOW CREATE TABLE t1n;
856 Table Create Table
857 t1n CREATE TABLE `t1n` (
858 `c1` int NOT NULL,
859 `c4` int DEFAULT NULL,
860 `ct` text,
861 PRIMARY KEY (`c1`),
862 KEY `c4` (`c4`),
863 FULLTEXT KEY `ct` (`ct`)
864 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
865 ALTER TABLE t1n DROP INDEX c4;
866 ### files in MYSQL_DATA_DIR/test
867 fts_aux_index_1.ibd
868 fts_aux_index_2.ibd
869 fts_aux_index_3.ibd
870 fts_aux_index_4.ibd
871 fts_aux_index_5.ibd
872 fts_aux_index_6.ibd
873 fts_aux_being_deleted.ibd
874 fts_aux_being_deleted_cache.ibd
875 fts_aux_config.ibd
876 fts_aux_deleted.ibd
877 fts_aux_deleted_cache.ibd
878 sys_foreign.ibd
879 sys_indexes.ibd
880 sys_tables.ibd
881 t1n.ibd
882 t1o.ibd
883 ### files in MYSQL_TMP_DIR/alt_dir/test
884 fts_aux_index_1.ibd
885 fts_aux_index_2.ibd
886 fts_aux_index_3.ibd
887 fts_aux_index_4.ibd
888 fts_aux_index_5.ibd
889 fts_aux_index_6.ibd
890 fts_aux_being_deleted.ibd
891 fts_aux_being_deleted_cache.ibd
892 fts_aux_config.ibd
893 fts_aux_deleted.ibd
894 fts_aux_deleted_cache.ibd
895 t1c.ibd
896 t1p.ibd
897 tt.ibd
898 ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE;
899 ERROR 42S21: Duplicate column name 'c1'
900 ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE;
901 ### files in MYSQL_DATA_DIR/test
902 fts_aux_index_1.ibd
903 fts_aux_index_2.ibd
904 fts_aux_index_3.ibd
905 fts_aux_index_4.ibd
906 fts_aux_index_5.ibd
907 fts_aux_index_6.ibd
908 fts_aux_being_deleted.ibd
909 fts_aux_being_deleted_cache.ibd
910 fts_aux_config.ibd
911 fts_aux_deleted.ibd
912 fts_aux_deleted_cache.ibd
913 sys_foreign.ibd
914 sys_indexes.ibd
915 sys_tables.ibd
916 t1n.ibd
917 t1o.ibd
918 ### files in MYSQL_TMP_DIR/alt_dir/test
919 fts_aux_index_1.ibd
920 fts_aux_index_2.ibd
921 fts_aux_index_3.ibd
922 fts_aux_index_4.ibd
923 fts_aux_index_5.ibd
924 fts_aux_index_6.ibd
925 fts_aux_being_deleted.ibd
926 fts_aux_being_deleted_cache.ibd
927 fts_aux_config.ibd
928 fts_aux_deleted.ibd
929 fts_aux_deleted_cache.ibd
930 t1c.ibd
931 t1p.ibd
932 tt.ibd
933 SHOW CREATE TABLE t1n;
934 Table Create Table
935 t1n CREATE TABLE `t1n` (
936 `c1` int NOT NULL,
937 `c11` int DEFAULT NULL,
938 `ct` text,
939 PRIMARY KEY (`c1`),
940 KEY `c11` (`c11`),
941 FULLTEXT KEY `ct` (`ct`)
942 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
943 DROP TABLE t1n;
944 ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL;
945 ### files in MYSQL_DATA_DIR/test
946 sys_foreign.ibd
947 sys_indexes.ibd
948 sys_tables.ibd
949 t1o.ibd
950 ### files in MYSQL_TMP_DIR/alt_dir/test
951 fts_aux_index_1.ibd
952 fts_aux_index_2.ibd
953 fts_aux_index_3.ibd
954 fts_aux_index_4.ibd
955 fts_aux_index_5.ibd
956 fts_aux_index_6.ibd
957 fts_aux_being_deleted.ibd
958 fts_aux_being_deleted_cache.ibd
959 fts_aux_config.ibd
960 fts_aux_deleted.ibd
961 fts_aux_deleted_cache.ibd
962 t1c.ibd
963 t1p.ibd
964 tt.ibd
965 ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
966 CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
967 ALGORITHM=INPLACE;
968 ### files in MYSQL_DATA_DIR/test
969 fts_aux_index_1.ibd
970 fts_aux_index_2.ibd
971 fts_aux_index_3.ibd
972 fts_aux_index_4.ibd
973 fts_aux_index_5.ibd
974 fts_aux_index_6.ibd
975 fts_aux_being_deleted.ibd
976 fts_aux_being_deleted_cache.ibd
977 fts_aux_config.ibd
978 fts_aux_deleted.ibd
979 fts_aux_deleted_cache.ibd
980 sys_foreign.ibd
981 sys_indexes.ibd
982 sys_tables.ibd
983 t1o.ibd
984 ### files in MYSQL_TMP_DIR/alt_dir/test
985 fts_aux_index_1.ibd
986 fts_aux_index_2.ibd
987 fts_aux_index_3.ibd
988 fts_aux_index_4.ibd
989 fts_aux_index_5.ibd
990 fts_aux_index_6.ibd
991 fts_aux_being_deleted.ibd
992 fts_aux_being_deleted_cache.ibd
993 fts_aux_config.ibd
994 fts_aux_deleted.ibd
995 fts_aux_deleted_cache.ibd
996 t1c.ibd
997 t1p.ibd
998 tt.ibd
999 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
1000 LOCK=NONE;
1001 ERROR 0A000: LOCK=NONE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try LOCK=SHARED.
1002 SELECT sc.pos FROM information_schema.innodb_columns sc
1003 INNER JOIN information_schema.innodb_tables st
1004 ON sc.TABLE_ID=st.TABLE_ID
1005 WHERE st.NAME='test/t1o' AND sc.NAME='FTS_DOC_ID';
1006 pos
1007 0
1008 SHOW CREATE TABLE t1o;
1009 Table Create Table
1010 t1o CREATE TABLE `t1o` (
1011 `FTS_DOC_ID` bigint unsigned NOT NULL,
1012 `c2` int DEFAULT NULL,
1013 `ct` text,
1014 PRIMARY KEY (`FTS_DOC_ID`),
1015 FULLTEXT KEY `ct` (`ct`)
1016 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1017 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
1018 DROP INDEX ct, LOCK=NONE;
1019 ### files in MYSQL_DATA_DIR/test
1020 sys_foreign.ibd
1021 sys_indexes.ibd
1022 sys_tables.ibd
1023 t1o.ibd
1024 ### files in MYSQL_TMP_DIR/alt_dir/test
1025 fts_aux_index_1.ibd
1026 fts_aux_index_2.ibd
1027 fts_aux_index_3.ibd
1028 fts_aux_index_4.ibd
1029 fts_aux_index_5.ibd
1030 fts_aux_index_6.ibd
1031 fts_aux_being_deleted.ibd
1032 fts_aux_being_deleted_cache.ibd
1033 fts_aux_config.ibd
1034 fts_aux_deleted.ibd
1035 fts_aux_deleted_cache.ibd
1036 t1c.ibd
1037 t1p.ibd
1038 tt.ibd
1039 SHOW CREATE TABLE t1o;
1040 Table Create Table
1041 t1o CREATE TABLE `t1o` (
1042 `foo_id` bigint unsigned NOT NULL,
1043 `c2` int DEFAULT NULL,
1044 `ct` text,
1045 PRIMARY KEY (`foo_id`)
1046 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1047 DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign;
1048 CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES
1049 WHERE NAME='test/t1o';
1050 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
1051 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
1052 CREATE TABLE sys_foreign SELECT i.*
1053 FROM INFORMATION_SCHEMA.INNODB_FOREIGN i WHERE FOR_NAME='test/t1o';
1054 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
1055 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
1056 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
1057 NAME POS MTYPE PRTYPE LEN
1058 c2 1 6 1027 4
1059 ct 2 5 16711932 10
1060 foo_id 0 6 1800 8
1061 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
1062 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
1063 NAME POS NAME
1064 PRIMARY 0 foo_id
1065 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
1066 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
1067 FOR_COL_NAME REF_COL_NAME POS
1068 ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id);
1069 ### files in MYSQL_DATA_DIR/test
1070 sys_foreign.ibd
1071 sys_indexes.ibd
1072 sys_tables.ibd
1073 t1o.ibd
1074 ### files in MYSQL_TMP_DIR/alt_dir/test
1075 fts_aux_index_1.ibd
1076 fts_aux_index_2.ibd
1077 fts_aux_index_3.ibd
1078 fts_aux_index_4.ibd
1079 fts_aux_index_5.ibd
1080 fts_aux_index_6.ibd
1081 fts_aux_being_deleted.ibd
1082 fts_aux_being_deleted_cache.ibd
1083 fts_aux_config.ibd
1084 fts_aux_deleted.ibd
1085 fts_aux_deleted_cache.ibd
1086 tt.ibd
1087 ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
1088 ADD FULLTEXT INDEX(ct);
1089 ### files in MYSQL_DATA_DIR/test
1090 fts_aux_index_1.ibd
1091 fts_aux_index_2.ibd
1092 fts_aux_index_3.ibd
1093 fts_aux_index_4.ibd
1094 fts_aux_index_5.ibd
1095 fts_aux_index_6.ibd
1096 fts_aux_being_deleted.ibd
1097 fts_aux_being_deleted_cache.ibd
1098 fts_aux_config.ibd
1099 fts_aux_deleted.ibd
1100 fts_aux_deleted_cache.ibd
1101 sys_foreign.ibd
1102 sys_indexes.ibd
1103 sys_tables.ibd
1104 t1o.ibd
1105 ### files in MYSQL_TMP_DIR/alt_dir/test
1106 fts_aux_index_1.ibd
1107 fts_aux_index_2.ibd
1108 fts_aux_index_3.ibd
1109 fts_aux_index_4.ibd
1110 fts_aux_index_5.ibd
1111 fts_aux_index_6.ibd
1112 fts_aux_being_deleted.ibd
1113 fts_aux_being_deleted_cache.ibd
1114 fts_aux_config.ibd
1115 fts_aux_deleted.ibd
1116 fts_aux_deleted_cache.ibd
1117 tt.ibd
1118 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL;
1119 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
1120 DROP TABLE sys_indexes;
1121 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
1122 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
1123 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
1124 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
1125 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
1126 NAME POS MTYPE PRTYPE LEN
1127 c2 1 6 1027 4
1128 ct 2 5 16711932 10
1129 FTS_DOC_ID 0 6 1800 8
1130 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
1131 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
1132 NAME POS NAME
1133 ct 0 ct
1134 FTS_DOC_ID_INDEX 0 FTS_DOC_ID
1135 PRIMARY 0 FTS_DOC_ID
1136 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
1137 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
1138 FOR_COL_NAME REF_COL_NAME POS
1139 #
1140 # Cleanup
1141 #
1142 DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign;
1143 ### files in MYSQL_DATA_DIR/test
1144 ### files in MYSQL_TMP_DIR/alt_dir/test