Debian Codegraph @

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

raw
1 SET NAMES utf8;
2 Warnings:
3 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.
4 CREATE TABLE t1 (
5 c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT,
6 INDEX(c2))
7 ENGINE=InnoDB;
8 INSERT INTO t1 SET c1=1;
9 CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES
10 WHERE NAME LIKE 'test/t%';
11 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
12 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
13 CREATE TABLE t1p LIKE t1;
14 CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2), INDEX(c3),
15 CONSTRAINT t1c2 FOREIGN KEY (c2) REFERENCES t1(c2),
16 CONSTRAINT t1c3 FOREIGN KEY (c3) REFERENCES t1p(c2))
17 ENGINE=InnoDB;
18 CREATE TABLE sys_foreign SELECT i.*
19 FROM INFORMATION_SCHEMA.INNODB_FOREIGN i
20 WHERE FOR_NAME LIKE 'test/t%';
21 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
22 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
23 FOR_COL_NAME REF_COL_NAME POS
24 c2 c2 1
25 c3 c2 1
26 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
27 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
28 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
29 NAME POS MTYPE PRTYPE LEN
30 c1 0 6 1283 4
31 c2 1 6 1027 4
32 ct 2 5 16711932 10
33 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
34 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
35 NAME POS NAME
36 c2 0 c2
37 PRIMARY 0 c1
38 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
39 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
40 FOR_COL_NAME REF_COL_NAME POS
41 c2 c2 1
42 c3 c2 1
43 SHOW CREATE TABLE t1;
44 Table Create Table
45 t1 CREATE TABLE `t1` (
46 `c1` int NOT NULL,
47 `c2` int DEFAULT '1',
48 `ct` text,
49 PRIMARY KEY (`c1`),
50 KEY `c2` (`c2`)
51 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
52 ALTER TABLE t1 ALTER c2 DROP DEFAULT;
53 SHOW CREATE TABLE t1;
54 Table Create Table
55 t1 CREATE TABLE `t1` (
56 `c1` int NOT NULL,
57 `c2` int,
58 `ct` text,
59 PRIMARY KEY (`c1`),
60 KEY `c2` (`c2`)
61 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
62 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
63 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
64 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
65 NAME POS MTYPE PRTYPE LEN
66 c1 0 6 1283 4
67 c2 1 6 1027 4
68 ct 2 5 16711932 10
69 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
70 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
71 NAME POS NAME
72 c2 0 c2
73 PRIMARY 0 c1
74 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
75 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
76 FOR_COL_NAME REF_COL_NAME POS
77 c2 c2 1
78 c3 c2 1
79 ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1;
80 ALTER TABLE t1 CHANGE c1 c1 INT FIRST;
81 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
82 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
83 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
84 NAME POS MTYPE PRTYPE LEN
85 c1 0 6 1283 4
86 c2 1 6 1027 4
87 ct 2 5 16711932 10
88 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
89 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
90 NAME POS NAME
91 c2 0 c2
92 PRIMARY 0 c1
93 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
94 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
95 FOR_COL_NAME REF_COL_NAME POS
96 c2 c2 1
97 c3 c2 1
98 ALTER TABLE t1 CHANGE C2 c3 INT;
99 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
100 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
101 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
102 NAME POS MTYPE PRTYPE LEN
103 c1 0 6 1283 4
104 c3 1 6 1027 4
105 ct 2 5 16711932 10
106 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
107 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
108 NAME POS NAME
109 c2 0 c3
110 PRIMARY 0 c1
111 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
112 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
113 FOR_COL_NAME REF_COL_NAME POS
114 c2 c3 1
115 c3 c2 1
116 ALTER TABLE t1 CHANGE c3 C INT;
117 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
118 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
119 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
120 NAME POS MTYPE PRTYPE LEN
121 C 1 6 1027 4
122 c1 0 6 1283 4
123 ct 2 5 16711932 10
124 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
125 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
126 NAME POS NAME
127 c2 0 C
128 PRIMARY 0 c1
129 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
130 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
131 FOR_COL_NAME REF_COL_NAME POS
132 c2 C 1
133 c3 c2 1
134 ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT;
135 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
136 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
137 FOR_COL_NAME REF_COL_NAME POS
138 c2 Cöŀumň_TWO 1
139 c3 c2 1
140 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
141 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
142 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
143 NAME POS MTYPE PRTYPE LEN
144 c1 0 6 1283 4
145 Cöŀumň_TWO 1 6 1027 4
146 ct 2 5 16711932 10
147 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
148 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
149 NAME POS NAME
150 c2 0 Cöŀumň_TWO
151 PRIMARY 0 c1
152 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
153 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
154 FOR_COL_NAME REF_COL_NAME POS
155 c2 Cöŀumň_TWO 1
156 c3 c2 1
157 ALTER TABLE t1 CHANGE cöĿǖmň_two c3 INT;
158 ERROR 42S22: Unknown column 'cöĿǖmň_two' in 't1'
159 ALTER TABLE t1 CHANGE cÖĿUMŇ_two c3 INT, RENAME TO t3;
160 SELECT st.NAME, i.NAME
161 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_TABLES i
162 ON i.TABLE_ID=st.TABLE_ID;
163 NAME NAME
164 test/t1 test/t3
165 SHOW CREATE TABLE t3;
166 Table Create Table
167 t3 CREATE TABLE `t3` (
168 `c1` int NOT NULL,
169 `c3` int DEFAULT NULL,
170 `ct` text,
171 PRIMARY KEY (`c1`),
172 KEY `c2` (`c3`)
173 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
174 SHOW CREATE TABLE t1c;
175 Table Create Table
176 t1c CREATE TABLE `t1c` (
177 `c1` int NOT NULL,
178 `c2` int DEFAULT NULL,
179 `c3` int DEFAULT NULL,
180 PRIMARY KEY (`c1`),
181 KEY `c2` (`c2`),
182 KEY `c3` (`c3`),
183 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t3` (`c3`),
184 CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`)
185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
186 ALTER TABLE t3 CHANGE c3
187 `12345678901234567890123456789012345678901234567890123456789012345` INT;
188 ERROR 42000: Identifier name '12345678901234567890123456789012345678901234567890123456789012345' is too long
189 ALTER TABLE t3 CHANGE c3
190 `1234567890123456789012345678901234567890123456789012345678901234` INT;
191 SHOW CREATE TABLE t3;
192 Table Create Table
193 t3 CREATE TABLE `t3` (
194 `c1` int NOT NULL,
195 `1234567890123456789012345678901234567890123456789012345678901234` int DEFAULT NULL,
196 `ct` text,
197 PRIMARY KEY (`c1`),
198 KEY `c2` (`1234567890123456789012345678901234567890123456789012345678901234`)
199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
200 ALTER TABLE t3 CHANGE
201 `1234567890123456789012345678901234567890123456789012345678901234`
202 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿偀` INT;
203 ERROR 42000: Identifier name '倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠?' is too long
204 ALTER TABLE t3 CHANGE
205 `1234567890123456789012345678901234567890123456789012345678901234`
206 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾倿ä` INT;
207 ERROR 42000: Identifier name '倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠?' is too long
208 ALTER TABLE t3 CHANGE
209 `1234567890123456789012345678901234567890123456789012345678901234`
210 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾ä` INT;
211 ALTER TABLE t3 CHANGE
212 `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾Ä`
213 c3 INT;
214 ALTER TABLE t3 CHANGE c3 𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜 INT;
215 ERROR HY000: Invalid utf8mb3 character string: '\xF0\x90\x8C\x80\xF0\x90\x8C\x81\xF0\x90\x8C\x82\xF0\x90\x8C\x83'
216 ALTER TABLE t3 CHANGE c3 😲 INT;
217 ERROR HY000: Invalid utf8mb3 character string: '\xF0\x9F\x98\xB2'
218 ALTER TABLE t3 RENAME TO t2;
219 SELECT st.NAME, i.NAME
220 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_TABLES i
221 ON i.TABLE_ID=st.TABLE_ID;
222 NAME NAME
223 test/t1 test/t2
224 SHOW CREATE TABLE t2;
225 Table Create Table
226 t2 CREATE TABLE `t2` (
227 `c1` int NOT NULL,
228 `c3` int DEFAULT NULL,
229 `ct` text,
230 PRIMARY KEY (`c1`),
231 KEY `c2` (`c3`)
232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
233 RENAME TABLE t2 TO t1;
234 SELECT st.NAME, i.NAME
235 FROM sys_tables st INNER JOIN INFORMATION_SCHEMA.INNODB_TABLES i
236 ON i.TABLE_ID=st.TABLE_ID;
237 NAME NAME
238 test/t1 test/t1
239 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
240 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
241 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
242 NAME POS MTYPE PRTYPE LEN
243 c1 0 6 1283 4
244 c3 1 6 1027 4
245 ct 2 5 16711932 10
246 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
247 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
248 NAME POS NAME
249 c2 0 c3
250 PRIMARY 0 c1
251 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
252 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
253 FOR_COL_NAME REF_COL_NAME POS
254 c2 c3 1
255 c3 c2 1
256 ALTER TABLE t1 DROP INDEX c2;
257 ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint
258 ALTER TABLE t1 DROP INDEX c4;
259 ERROR 42000: Can't DROP 'c4'; check that column/key exists
260 ALTER TABLE t1c DROP FOREIGN KEY c2;
261 ERROR 42000: Can't DROP 'c2'; check that column/key exists
262 ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2;
263 ERROR 42000: Can't DROP 'c2'; check that column/key exists
264 ALTER TABLE t1c DROP FOREIGN KEY t1c2, DROP FOREIGN KEY c2, DROP INDEX c2;
265 ERROR 42000: Can't DROP 'c2'; check that column/key exists
266 ALTER TABLE t1c DROP INDEX c2;
267 ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint
268 ALTER TABLE t1c DROP FOREIGN KEY ẗ1C2;
269 ERROR 42000: Can't DROP 'ẗ1C2'; check that column/key exists
270 SHOW CREATE TABLE t1c;
271 Table Create Table
272 t1c CREATE TABLE `t1c` (
273 `c1` int NOT NULL,
274 `c2` int DEFAULT NULL,
275 `c3` int DEFAULT NULL,
276 PRIMARY KEY (`c1`),
277 KEY `c2` (`c2`),
278 KEY `c3` (`c3`),
279 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`),
280 CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`)
281 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
282 SET foreign_key_checks=0;
283 DROP TABLE t1p;
284 SET foreign_key_checks=1;
285 SHOW CREATE TABLE t1c;
286 Table Create Table
287 t1c CREATE TABLE `t1c` (
288 `c1` int NOT NULL,
289 `c2` int DEFAULT NULL,
290 `c3` int DEFAULT NULL,
291 PRIMARY KEY (`c1`),
292 KEY `c2` (`c2`),
293 KEY `c3` (`c3`),
294 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`),
295 CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`)
296 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
297 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
298 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
299 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
300 NAME POS MTYPE PRTYPE LEN
301 c1 0 6 1283 4
302 c3 1 6 1027 4
303 ct 2 5 16711932 10
304 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
305 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
306 NAME POS NAME
307 c2 0 c3
308 PRIMARY 0 c1
309 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
310 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
311 FOR_COL_NAME REF_COL_NAME POS
312 c2 c3 1
313 c3 c2 1
314 CREATE TABLE t1p (c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE=InnoDB;
315 ALTER TABLE t1c DROP INDEX C2, DROP INDEX C3;
316 ERROR HY000: Cannot drop index 'c2': needed in a foreign key constraint
317 ALTER TABLE t1c DROP INDEX C3;
318 ERROR HY000: Cannot drop index 'c3': needed in a foreign key constraint
319 SHOW CREATE TABLE t1c;
320 Table Create Table
321 t1c CREATE TABLE `t1c` (
322 `c1` int NOT NULL,
323 `c2` int DEFAULT NULL,
324 `c3` int DEFAULT NULL,
325 PRIMARY KEY (`c1`),
326 KEY `c2` (`c2`),
327 KEY `c3` (`c3`),
328 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`),
329 CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`)
330 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
331 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
332 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
333 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
334 NAME POS MTYPE PRTYPE LEN
335 c1 0 6 1283 4
336 c3 1 6 1027 4
337 ct 2 5 16711932 10
338 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
339 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
340 NAME POS NAME
341 c2 0 c3
342 PRIMARY 0 c1
343 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
344 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
345 FOR_COL_NAME REF_COL_NAME POS
346 c2 c3 1
347 c3 c2 1
348 ALTER TABLE t1c DROP FOREIGN KEY t1C3;
349 SHOW CREATE TABLE t1c;
350 Table Create Table
351 t1c CREATE TABLE `t1c` (
352 `c1` int NOT NULL,
353 `c2` int DEFAULT NULL,
354 `c3` int DEFAULT NULL,
355 PRIMARY KEY (`c1`),
356 KEY `c2` (`c2`),
357 KEY `c3` (`c3`),
358 CONSTRAINT `t1c2` FOREIGN KEY (`c2`) REFERENCES `t1` (`c3`)
359 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
360 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
361 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
362 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
363 NAME POS MTYPE PRTYPE LEN
364 c1 0 6 1283 4
365 c3 1 6 1027 4
366 ct 2 5 16711932 10
367 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
368 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
369 NAME POS NAME
370 c2 0 c3
371 PRIMARY 0 c1
372 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
373 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
374 FOR_COL_NAME REF_COL_NAME POS
375 c2 c3 1
376 ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2;
377 SHOW CREATE TABLE t1c;
378 Table Create Table
379 t1c CREATE TABLE `t1c` (
380 `c1` int NOT NULL,
381 `c2` int DEFAULT NULL,
382 `c3` int DEFAULT NULL,
383 PRIMARY KEY (`c1`),
384 KEY `c3` (`c3`)
385 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
386 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
387 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
388 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
389 NAME POS MTYPE PRTYPE LEN
390 c1 0 6 1283 4
391 c3 1 6 1027 4
392 ct 2 5 16711932 10
393 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
394 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
395 NAME POS NAME
396 c2 0 c3
397 PRIMARY 0 c1
398 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
399 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
400 FOR_COL_NAME REF_COL_NAME POS
401 ALTER TABLE t1 DROP INDEX c2, CHANGE c3 c2 INT;
402 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
403 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
404 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
405 NAME POS MTYPE PRTYPE LEN
406 c1 0 6 1283 4
407 c2 1 6 1027 4
408 ct 2 5 16711932 10
409 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
410 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
411 NAME POS NAME
412 PRIMARY 0 c1
413 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
414 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
415 FOR_COL_NAME REF_COL_NAME POS
416 CREATE TABLE t1o LIKE t1;
417 ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
418 CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
419 ALGORITHM=INPLACE, LOCK=NONE;
420 ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED.
421 ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
422 CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
423 ALGORITHM=INPLACE, LOCK=SHARED;
424 Warnings:
425 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
426 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
427 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
428 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
429 NAME POS MTYPE PRTYPE LEN
430 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
431 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
432 NAME POS NAME
433 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
434 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
435 FOR_COL_NAME REF_COL_NAME POS
436 SHOW CREATE TABLE tt;
437 Table Create Table
438 tt CREATE TABLE `tt` (
439 `pk` int NOT NULL,
440 `c2` int DEFAULT '42',
441 `ct` text,
442 PRIMARY KEY (`pk`),
443 FULLTEXT KEY `ct` (`ct`)
444 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
445 ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=COPY;
446 ERROR 42000: Incorrect column name 'dB_row_Id'
447 ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=INPLACE;
448 ERROR 42000: Incorrect column name 'DB_ROW_ID'
449 ALTER TABLE t1o CHANGE c1 DB_TRX_ID INT;
450 ERROR 42000: Incorrect column name 'DB_TRX_ID'
451 ALTER TABLE t1o CHANGE c1 db_roll_ptr INT;
452 ERROR 42000: Incorrect column name 'db_roll_ptr'
453 ALTER TABLE t1o ADD COLUMN DB_TRX_ID INT;
454 ERROR 42000: Incorrect column name 'DB_TRX_ID'
455 ALTER TABLE t1o ADD COLUMN db_roll_ptr INT;
456 ERROR 42000: Incorrect column name 'db_roll_ptr'
457 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT;
458 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
459 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;
460 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
461 ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
462 ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
463 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN FTS_DOC_ID INT;
464 ERROR 42S21: Duplicate column name 'FTS_DOC_ID'
465 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;
466 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY.
467 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, DROP INDEX ct, ALGORITHM=INPLACE;
468 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN cu TEXT;
469 Warnings:
470 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
471 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT,
472 ALGORITHM=INPLACE;
473 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
474 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT;
475 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
476 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED,
477 ALGORITHM=INPLACE;
478 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
479 ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED;
480 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
481 ALTER TABLE t1o ADD FULLTEXT INDEX(cu),
482 ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE;
483 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY.
484 ALTER TABLE t1o ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
485 ALGORITHM=INPLACE;
486 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY.
487 ALTER TABLE t1o ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
488 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE;
489 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY.
490 ALTER TABLE t1o DROP COLUMN FTS_DOC_ID;
491 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
492 ALGORITHM=COPY;
493 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
494 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_DOC_ID INT,
495 ALGORITHM=INPLACE;
496 ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
497 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), CHANGE c1 FTS_Doc_ID INT,
498 ALGORITHM=INPLACE;
499 ERROR HY000: Column 'FTS_Doc_ID' is of wrong type for an InnoDB FULLTEXT index
500 ALTER TABLE t1o ADD FULLTEXT INDEX(ct),
501 CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
502 ALGORITHM=INPLACE;
503 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
504 CREATE TABLE t1n LIKE t1o;
505 ALTER TABLE t1n ADD FULLTEXT INDEX(ct);
506 Warnings:
507 Warning 1831 Duplicate index 'ct_2' defined on the table 'test.t1n'. This is deprecated and will be disallowed in a future release.
508 ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=INPLACE;
509 ERROR HY000: Column 'Fts_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
510 ALTER TABLE t1n CHANGE c1 Fts_DOC_ID INT, ALGORITHM=COPY;
511 ERROR HY000: Column 'Fts_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
512 ALTER TABLE t1n CHANGE FTS_DOC_ID c11 INT, ALGORITHM=INPLACE;
513 ERROR 42S22: Unknown column 'FTS_DOC_ID' in 't1n'
514 ALTER TABLE t1n CHANGE c1 FTS_DOC_ïD INT, ALGORITHM=INPLACE;
515 ALTER TABLE t1n CHANGE FTS_DOC_ÏD c1 INT, ALGORITHM=INPLACE;
516 ALTER TABLE t1n CHANGE c1 c2 INT, CHANGE c2 ct INT, CHANGE ct c1 TEXT,
517 ALGORITHM=INPLACE;
518 SHOW CREATE TABLE t1n;
519 Table Create Table
520 t1n CREATE TABLE `t1n` (
521 `c2` int NOT NULL,
522 `ct` int DEFAULT NULL,
523 `c1` text,
524 `cu` text,
525 PRIMARY KEY (`c2`),
526 FULLTEXT KEY `ct` (`c1`),
527 FULLTEXT KEY `ct_2` (`c1`)
528 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
529 ALTER TABLE t1n CHANGE c2 c1 INT, CHANGE ct c2 INT, CHANGE c1 ct TEXT,
530 ALGORITHM=COPY;
531 SHOW CREATE TABLE t1n;
532 Table Create Table
533 t1n CREATE TABLE `t1n` (
534 `c1` int NOT NULL,
535 `c2` int DEFAULT NULL,
536 `ct` text,
537 `cu` text,
538 PRIMARY KEY (`c1`),
539 FULLTEXT KEY `ct` (`ct`),
540 FULLTEXT KEY `ct_2` (`ct`)
541 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
542 ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
543 ERROR 42000: Key column 'c2' doesn't exist in table
544 ALTER TABLE t1n ADD INDEX(c2), CHANGE c2 c4 INT, ALGORITHM=COPY;
545 ERROR 42000: Key column 'c2' doesn't exist in table
546 ALTER TABLE t1n ADD INDEX(c4), CHANGE c2 c4 INT, ALGORITHM=INPLACE;
547 SHOW CREATE TABLE t1n;
548 Table Create Table
549 t1n CREATE TABLE `t1n` (
550 `c1` int NOT NULL,
551 `c4` int DEFAULT NULL,
552 `ct` text,
553 `cu` text,
554 PRIMARY KEY (`c1`),
555 KEY `c4` (`c4`),
556 FULLTEXT KEY `ct` (`ct`),
557 FULLTEXT KEY `ct_2` (`ct`)
558 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
559 ALTER TABLE t1n DROP INDEX c4;
560 ALTER TABLE t1n CHANGE c4 c1 INT, ADD INDEX(c1), ALGORITHM=INPLACE;
561 ERROR 42S21: Duplicate column name 'c1'
562 ALTER TABLE t1n CHANGE c4 c11 INT, ADD INDEX(c11), ALGORITHM=INPLACE;
563 SHOW CREATE TABLE t1n;
564 Table Create Table
565 t1n CREATE TABLE `t1n` (
566 `c1` int NOT NULL,
567 `c11` int DEFAULT NULL,
568 `ct` text,
569 `cu` text,
570 PRIMARY KEY (`c1`),
571 KEY `c11` (`c11`),
572 FULLTEXT KEY `ct` (`ct`),
573 FULLTEXT KEY `ct_2` (`ct`)
574 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
575 DROP TABLE t1n;
576 ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct,
577 ALGORITHM=INPLACE;
578 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
579 ALTER TABLE t1o MODIFY c1 BIGINT UNSIGNED NOT NULL, DROP INDEX ct;
580 ALTER TABLE t1o CHANGE c1 FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
581 ALGORITHM=INPLACE;
582 ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ALGORITHM=INPLACE;
583 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
584 ALGORITHM=INPLACE;
585 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY.
586 SELECT sc.pos FROM information_schema.innodb_columns sc
587 INNER JOIN information_schema.innodb_tables st
588 ON sc.TABLE_ID=st.TABLE_ID
589 WHERE st.NAME='test/t1o' AND sc.NAME='FTS_DOC_ID';
590 pos
591 0
592 SHOW CREATE TABLE t1o;
593 Table Create Table
594 t1o CREATE TABLE `t1o` (
595 `FTS_DOC_ID` bigint unsigned NOT NULL,
596 `c2` int DEFAULT NULL,
597 `ct` text,
598 `cu` text,
599 PRIMARY KEY (`FTS_DOC_ID`),
600 FULLTEXT KEY `ct` (`ct`)
601 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
602 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
603 DROP INDEX ct, ALGORITHM=INPLACE;
604 SHOW CREATE TABLE t1o;
605 Table Create Table
606 t1o CREATE TABLE `t1o` (
607 `foo_id` bigint unsigned NOT NULL,
608 `c2` int DEFAULT NULL,
609 `ct` text,
610 `cu` text,
611 PRIMARY KEY (`foo_id`)
612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
613 DROP TABLE t1c, t1p, sys_tables, sys_indexes, sys_foreign;
614 CREATE TABLE sys_tables SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES
615 WHERE NAME='test/t1o';
616 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
617 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
618 CREATE TABLE sys_foreign SELECT i.*
619 FROM INFORMATION_SCHEMA.INNODB_FOREIGN i WHERE FOR_NAME='test/t1o';
620 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
621 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
622 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
623 NAME POS MTYPE PRTYPE LEN
624 c2 1 6 1027 4
625 ct 2 5 16711932 10
626 cu 3 5 16711932 10
627 foo_id 0 6 1800 8
628 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
629 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
630 NAME POS NAME
631 PRIMARY 0 foo_id
632 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
633 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
634 FOR_COL_NAME REF_COL_NAME POS
635 ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID),
636 ADD FULLTEXT INDEX(ct),
637 CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL;
638 ALTER TABLE t1o DROP INDEX ct, DROP INDEX FTS_DOC_ID_INDEX,
639 CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL;
640 ALTER TABLE t1o ADD UNIQUE INDEX FTS_DOC_ID_INDEX(foo_id);
641 ALTER TABLE t1o CHANGE foo_id FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
642 ADD FULLTEXT INDEX(ct);
643 ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL,
644 ALGORITHM=INPLACE;
645 ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
646 DROP TABLE sys_indexes;
647 CREATE TABLE sys_indexes SELECT i.* FROM INFORMATION_SCHEMA.INNODB_INDEXES i
648 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID;
649 SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
650 FROM INFORMATION_SCHEMA.INNODB_COLUMNS i
651 INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID ORDER BY i.NAME,i.POS;
652 NAME POS MTYPE PRTYPE LEN
653 c2 1 6 1027 4
654 ct 2 5 16711932 10
655 cu 3 5 16711932 10
656 FTS_DOC_ID 0 6 1800 8
657 SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_FIELDS i
658 INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID ORDER BY si.NAME, i.POS;
659 NAME POS NAME
660 ct 0 ct
661 FTS_DOC_ID_INDEX 0 FTS_DOC_ID
662 PRIMARY 0 FTS_DOC_ID
663 SELECT i.FOR_COL_NAME, i.REF_COL_NAME, i.POS FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS i
664 INNER JOIN sys_foreign sf ON i.ID = sf.ID;
665 FOR_COL_NAME REF_COL_NAME POS
666 DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign;
667 CREATE TABLE t (t TEXT, FULLTEXT(t)) ENGINE=InnoDB;
668 DROP INDEX t ON t;
669 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
670 FROM INFORMATION_SCHEMA.INNODB_TABLES
671 WHERE name LIKE '%FTS_%' ORDER BY 1, 2;
672 prefix name
673 fts_0 test/fts_aux_being_deleted
674 fts_0 test/fts_aux_being_deleted_cache
675 fts_0 test/fts_aux_config
676 fts_0 test/fts_aux_deleted
677 fts_0 test/fts_aux_deleted_cache
678 SELECT sc.pos, sc.NAME FROM information_schema.innodb_columns sc
679 INNER JOIN information_schema.innodb_tables st
680 ON sc.TABLE_ID=st.TABLE_ID
681 WHERE st.NAME='test/t' ORDER BY sc.pos, sc.NAME;
682 pos NAME
683 0 t
684 ALTER TABLE t ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE;
685 SELECT SUBSTRING(name, LOCATE('_', name) - 3, 5) AS prefix, name
686 FROM INFORMATION_SCHEMA.INNODB_TABLES
687 WHERE name LIKE '%FTS_%' ORDER BY 1, 2;
688 prefix name
689 ALTER TABLE t ADD FULLTEXT INDEX(t);
690 Warnings:
691 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
692 SELECT sc.pos, sc.NAME FROM information_schema.innodb_columns sc
693 INNER JOIN information_schema.innodb_tables st
694 ON sc.TABLE_ID=st.TABLE_ID
695 WHERE st.NAME='test/t' ORDER BY sc.pos, sc.NAME;
696 pos NAME
697 0 t
698 DROP TABLE t;
699 #
700 # Bug #19465984 INNODB DATA DICTIONARY IS NOT UPDATED WHILE
701 # RENAMING THE COLUMN
702 #
703 CREATE TABLE t1(c1 INT NOT NULL, PRIMARY KEY(c1))ENGINE=INNODB;
704 CREATE TABLE t2(c2 INT NOT NULL, FOREIGN KEY(c2) REFERENCES t1(c1))ENGINE=INNODB;
705 SHOW CREATE TABLE t1;
706 Table Create Table
707 t1 CREATE TABLE `t1` (
708 `c1` int NOT NULL,
709 PRIMARY KEY (`c1`)
710 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
711 SHOW CREATE TABLE t2;
712 Table Create Table
713 t2 CREATE TABLE `t2` (
714 `c2` int NOT NULL,
715 KEY `c2` (`c2`),
716 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c1`)
717 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
718 ALTER TABLE t1 CHANGE COLUMN c1 C1 INT;
719 SHOW CREATE TABLE t1;
720 Table Create Table
721 t1 CREATE TABLE `t1` (
722 `C1` int NOT NULL,
723 PRIMARY KEY (`C1`)
724 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
725 SHOW CREATE TABLE t2;
726 Table Create Table
727 t2 CREATE TABLE `t2` (
728 `c2` int NOT NULL,
729 KEY `c2` (`c2`),
730 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t1` (`C1`)
731 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
732 ALTER TABLE t1 CHANGE COLUMN C1 c5 INT;
733 SHOW CREATE TABLE t1;
734 Table Create Table
735 t1 CREATE TABLE `t1` (
736 `c5` int NOT NULL,
737 PRIMARY KEY (`c5`)
738 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
739 SHOW CREATE TABLE t2;
740 Table Create Table
741 t2 CREATE TABLE `t2` (
742 `c2` int NOT NULL,
743 KEY `c2` (`c2`),
744 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c5`)
745 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
746 DROP TABLE t2, t1;
747 #
748 #BUG#21514135 SCHEMA MISMATCH ERROR WHEN IMPORTING TABLESPACE AFTER
749 #DROPPING AN INDEX
750 #
751 CREATE DATABASE source_db;
752 CREATE DATABASE dest_db;
753 CREATE TABLE source_db.t1 (
754 id int(11) NOT NULL,
755 age int(11) DEFAULT NULL,
756 name varchar(20),
757 PRIMARY KEY (id),
758 KEY index1 (age)
759 ) ENGINE=InnoDB;
760 Warnings:
761 Warning 1681 Integer display width is deprecated and will be removed in a future release.
762 Warning 1681 Integer display width is deprecated and will be removed in a future release.
763 ALTER TABLE source_db.t1 DROP INDEX index1, ADD INDEX index2(name, age), algorithm=inplace;
764 FLUSH TABLES source_db.t1 FOR EXPORT;
765 UNLOCK TABLES;
766 USE dest_db;
767 CREATE TABLE `t1` (
768 `id` int NOT NULL,
769 `age` int DEFAULT NULL,
770 `name` varchar(20) DEFAULT NULL,
771 PRIMARY KEY (`id`),
772 KEY `index2` (`name`,`age`)
773 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
774 ALTER TABLE dest_db.t1 DISCARD TABLESPACE;
775 ALTER TABLE dest_db.t1 IMPORT TABLESPACE;
776 CHECK TABLE dest_db.t1;
777 Table Op Msg_type Msg_text
778 dest_db.t1 check status OK
779 SHOW CREATE TABLE dest_db.t1;
780 Table Create Table
781 t1 CREATE TABLE `t1` (
782 `id` int NOT NULL,
783 `age` int DEFAULT NULL,
784 `name` varchar(20) DEFAULT NULL,
785 PRIMARY KEY (`id`),
786 KEY `index2` (`name`,`age`)
787 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
788 SELECT * FROM dest_db.t1;
789 id age name
790 DROP TABLE dest_db.t1;
791 ALTER TABLE source_db.t1 DROP INDEX index2, algorithm=inplace;
792 FLUSH TABLES source_db.t1 FOR EXPORT;
793 UNLOCK TABLES;
794 USE dest_db;
795 CREATE TABLE `t1` (
796 `id` int NOT NULL,
797 `age` int DEFAULT NULL,
798 `name` varchar(20) DEFAULT NULL,
799 PRIMARY KEY (`id`)
800 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
801 ALTER TABLE dest_db.t1 DISCARD TABLESPACE;
802 ALTER TABLE dest_db.t1 IMPORT TABLESPACE;
803 CHECK TABLE dest_db.t1;
804 Table Op Msg_type Msg_text
805 dest_db.t1 check status OK
806 SHOW CREATE TABLE dest_db.t1;
807 Table Create Table
808 t1 CREATE TABLE `t1` (
809 `id` int NOT NULL,
810 `age` int DEFAULT NULL,
811 `name` varchar(20) DEFAULT NULL,
812 PRIMARY KEY (`id`)
813 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
814 SELECT * FROM dest_db.t1;
815 id age name
816 DROP TABLE dest_db.t1;
817 DROP TABLE source_db.t1;
818 DROP DATABASE source_db;
819 DROP DATABASE dest_db;
820 #
821 # Bug#22005726 ASSERT COL->ORD_PART IN ROW_BUILD_INDEX_ENTRY_LOW AT
822 # ROW0ROW.CC:299
823 #
824 USE test;
825 CREATE TABLE t1 (col1 INT, col2 TEXT) ENGINE = InnoDB;
826 INSERT INTO t1 (col1, col2) VALUES (2 , 'A');
827 ALTER TABLE t1 ADD KEY idx (col2(10));
828 INSERT INTO t1 (col1, col2) VALUES (2 , 'A');
829 ALTER TABLE t1 DROP KEY idx, ADD UNIQUE KEY uidx (col1);
830 ERROR 23000: Duplicate entry '2' for key 't1.uidx'
831 DELETE FROM t1 WHERE col1 = 2;
832 DROP TABLE t1;