Debian Codegraph @

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

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