Debian Codegraph @

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

raw
1 --source include/innodb_row_format.inc
2
3 --let $row_format= `SELECT @@GLOBAL.innodb_default_row_format`
4 set names utf8;
5
6 create table no_rebuild (
7 a char(150) charset utf8mb3 collate utf8mb3_general_ci
8 ) engine=innodb;
9 create table rebuild (
10 a varchar(150) charset ascii
11 ) engine=innodb;
12
13 set @id = (select table_id from information_schema.innodb_sys_tables
14 where name = 'test/no_rebuild');
15 select name, prtype, len from information_schema.innodb_sys_columns
16 where table_id = @id;
17 select c.prtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id
18 where t.name = 'test/rebuild' and c.name = 'a';
19 alter table no_rebuild
20 change a a char(150) charset utf8mb3 collate utf8mb3_spanish_ci,
21 algorithm=inplace;
22 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
23 alter table rebuild
24 change a a varchar(150) charset latin1 not null default 'asdf',
25 algorithm=inplace;
26 alter table rebuild
27 change a a varchar(150) charset latin1 not null default 'asdf',
28 algorithm=copy;
29 select name, prtype, len from information_schema.innodb_sys_columns
30 where table_id = @id;
31 select c.prtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id
32 where t.name = 'test/rebuild' and c.name = 'a';
33
34 drop table no_rebuild, rebuild;
35
36 create table supported_types (
37 id int primary key auto_increment,
38 a varchar(150) charset ascii,
39 b text(150) charset ascii,
40 c text charset ascii,
41 d tinytext charset ascii,
42 e mediumtext charset ascii,
43 f longtext charset ascii
44 ) engine=innodb;
45
46 alter table supported_types
47 convert to charset ascii collate ascii_bin,
48 algorithm=instant;
49
50 drop table supported_types;
51
52 create table various_cases (
53 a char(150) charset ascii,
54 b varchar(150) as (a) virtual,
55 c char(150) as (a) persistent
56 ) engine=innodb;
57
58 alter table various_cases
59 change a a char(150) charset ascii collate ascii_bin,
60 algorithm=inplace;
61
62 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
63 alter table various_cases
64 change a a varchar(222),
65 algorithm=inplace;
66
67 alter table various_cases
68 change b b varchar(150) as (a) virtual,
69 algorithm=inplace;
70
71 --error ER_ALTER_OPERATION_NOT_SUPPORTED
72 alter table various_cases
73 change c c char(150) as (a) persistent,
74 algorithm=inplace;
75
76 # Can not grow storage in bytes from CHAR
77 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
78 alter table various_cases
79 modify a char(150) charset utf8mb4,
80 algorithm=instant;
81
82 drop table various_cases;
83
84
85 create table all_texts (
86 a tinytext charset ascii,
87 b text charset ascii,
88 c mediumtext charset ascii,
89 d longtext charset ascii,
90 footer int
91 ) engine=innodb;
92
93 alter table all_texts
94 convert to charset ascii collate ascii_bin,
95 algorithm=instant;
96
97 drop table all_texts;
98
99
100 create table all_binaries (
101 a tinyblob,
102 b blob,
103 c mediumblob,
104 d longblob,
105 e varbinary(150),
106 f binary(150)
107 ) engine=innodb;
108
109 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
110 alter table all_binaries modify a tinytext, algorithm=instant;
111 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
112 alter table all_binaries modify b text, algorithm=instant;
113 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
114 alter table all_binaries modify c mediumtext, algorithm=instant;
115 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
116 alter table all_binaries modify d longtext, algorithm=instant;
117 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
118 alter table all_binaries modify e varchar(150), algorithm=instant;
119 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
120 alter table all_binaries modify f char(150), algorithm=instant;
121
122 drop table all_binaries;
123
124 create table all_strings (
125 a tinytext,
126 b text,
127 c mediumtext,
128 d longtext,
129 e varchar(150),
130 f char(150)
131 ) engine=innodb;
132
133 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
134 alter table all_strings modify a tinyblob, algorithm=instant;
135 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
136 alter table all_strings modify b blob, algorithm=instant;
137 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
138 alter table all_strings modify c mediumblob, algorithm=instant;
139 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
140 alter table all_strings modify d longblob, algorithm=instant;
141 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
142 alter table all_strings modify e varbinary(150), algorithm=instant;
143 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
144 alter table all_strings modify f binary(150), algorithm=instant;
145
146 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
147 alter table all_strings modify a tinytext charset binary, algorithm=instant;
148 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
149 alter table all_strings modify b text charset binary, algorithm=instant;
150 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
151 alter table all_strings modify c mediumtext charset binary, algorithm=instant;
152 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
153 alter table all_strings modify d longtext charset binary, algorithm=instant;
154 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
155 alter table all_strings modify e varchar(150) charset binary, algorithm=instant;
156 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
157 alter table all_strings modify f char(150) charset binary, algorithm=instant;
158
159 drop table all_strings;
160
161 create table key_part_change (
162 a char(150) charset ascii,
163 b char(150) charset ascii,
164 c char(150) charset ascii,
165 unique key ab (a,b)
166 ) engine=innodb;
167
168 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
169 alter table key_part_change
170 modify a char(150) charset utf8mb4,
171 drop index ab,
172 add unique key ab(a,c),
173 algorithm=instant;
174
175 drop table key_part_change;
176
177 create table key_part_change_and_rename (
178 a char(100) charset ascii,
179 b char(100) charset ascii,
180 unique key ab (a,b)
181 ) engine=innodb;
182
183 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
184 alter table key_part_change_and_rename
185 change a b char(100) charset utf8mb4,
186 change b a char(100) charset utf8mb4,
187 drop index ab,
188 add unique key ab(a,b),
189 algorithm=instant;
190
191 drop table key_part_change_and_rename;
192
193 create table enum_and_set (
194 a enum('one', 'two') charset utf8mb3,
195 b set('three', 'four') charset utf8mb3
196 ) engine=innodb;
197
198 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
199 alter table enum_and_set
200 modify a enum('one', 'two') charset utf8mb4,
201 algorithm=instant;
202
203 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
204 alter table enum_and_set
205 modify b enum('three', 'four') charset utf8mb4,
206 algorithm=instant;
207
208 drop table enum_and_set;
209
210 create table compressed (
211 a varchar(255) compressed charset utf8mb3
212 ) engine=innodb;
213
214 insert into compressed values ('AAA'), ('bbb'), ('CCC');
215
216 alter table compressed
217 modify a varchar(255) compressed charset utf8mb4,
218 algorithm=instant;
219
220 select * from compressed;
221 check table compresed;
222
223 drop table compressed;
224
225 create table key_part_bug (
226 id int primary key auto_increment,
227 a varchar(150) charset utf8mb3 unique key
228 ) engine=innodb;
229
230 alter table key_part_bug
231 modify a varchar(150) charset utf8mb4,
232 algorithm=instant;
233
234 drop table key_part_bug;
235
236
237 create table latin1_swedish_special_case (
238 copy1 varchar(150) charset ascii collate ascii_general_ci,
239 copy2 char(150) charset ascii collate ascii_general_ci,
240 instant1 varchar(150) charset ascii collate ascii_general_ci,
241 instant2 char(150) charset ascii collate ascii_general_ci
242 ) engine=innodb;
243
244 select c.name, c.prtype, c.mtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id
245 where t.name = 'test/latin1_swedish_special_case';
246 alter table latin1_swedish_special_case
247 modify copy1 varchar(150) charset latin1 collate latin1_swedish_ci,
248 modify copy2 char(150) charset latin1 collate latin1_swedish_ci,
249 algorithm=copy;
250 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
251 alter table latin1_swedish_special_case
252 modify instant1 varchar(150) charset latin1 collate latin1_swedish_ci,
253 modify instant2 char(150) charset latin1 collate latin1_swedish_ci,
254 algorithm=instant;
255 alter table latin1_swedish_special_case
256 modify instant1 varchar(150) charset latin1 collate latin1_swedish_ci,
257 modify instant2 char(150) charset latin1 collate latin1_swedish_ci,
258 algorithm=copy;
259 select c.name, c.prtype, c.mtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id
260 where t.name = 'test/latin1_swedish_special_case';
261 alter table latin1_swedish_special_case
262 modify copy1 varchar(150) charset latin1 collate latin1_general_ci,
263 modify copy2 char(150) charset latin1 collate latin1_general_ci,
264 algorithm=copy;
265 alter table latin1_swedish_special_case
266 modify instant1 varchar(150) charset latin1 collate latin1_general_ci,
267 modify instant2 char(150) charset latin1 collate latin1_general_ci,
268 algorithm=instant;
269 select c.name, c.prtype, c.mtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id
270 where t.name = 'test/latin1_swedish_special_case';
271
272 drop table latin1_swedish_special_case;
273
274 create table regression (a varchar(100) charset utf8mb3 primary key, b int) engine=innodb;
275 alter table regression convert to character set utf8mb4;
276 drop table regression;
277
278 # ROW_FORMAT=DYNAMIC limitation:
279 # size in bytes cannot be increased from less of equal that 255 to more than 255
280 create table boundary_255 (
281 a varchar(50) charset ascii,
282 b varchar(200) charset ascii,
283 c varchar(300) charset ascii
284 ) engine=innodb;
285
286 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
287 alter table boundary_255
288 modify a varchar(50) charset utf8mb3,
289 algorithm=instant;
290 alter table boundary_255
291 modify a varchar(50) charset utf8mb3,
292 algorithm=copy;
293
294 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
295 alter table boundary_255
296 modify b varchar(200) charset utf8mb3,
297 algorithm=instant;
298
299 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
300 alter table boundary_255
301 modify c varchar(300) charset utf8mb3,
302 algorithm=instant;
303
304 drop table boundary_255;
305
306 create table boundary_255 (
307 a varchar(70) charset utf8mb3
308 ) engine=innodb;
309
310 if ($row_format == 'redundant') {
311 alter table boundary_255
312 modify a varchar(70) charset utf8mb4,
313 algorithm=instant;
314 }
315 if ($row_format != 'redundant') {
316 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
317 alter table boundary_255
318 modify a varchar(70) charset utf8mb4,
319 algorithm=instant;
320 }
321
322 drop table boundary_255;
323
324
325 create table t (
326 a char(10) collate utf8mb3_general_ci,
327 b char(70) collate utf8mb3_general_ci,
328 c char(100) collate utf8mb3_general_ci,
329
330 aa char(10) collate utf8mb3_general_ci unique,
331 bb char(70) collate utf8mb3_general_ci unique,
332 cc char(100) collate utf8mb3_general_ci unique,
333
334 d char(10) collate utf8mb3_general_ci,
335 dd char(10) collate utf8mb3_general_ci unique
336 ) engine=innodb;
337 insert into t values
338 (repeat('a', 10), repeat('a', 70), repeat('a', 100),
339 repeat('a', 10), repeat('a', 70), repeat('a', 100),
340 repeat('a', 10), repeat('a', 10)
341 );
342 if ($row_format != 'redundant') {
343 alter table t modify a char(10) collate utf8mb4_general_ci, algorithm=instant;
344 check table t;
345 alter table t modify b char(70) collate utf8mb4_general_ci, algorithm=instant;
346 check table t;
347 alter table t modify c char(100) collate utf8mb4_general_ci, algorithm=instant;
348 check table t;
349
350 alter table t modify aa char(10) collate utf8mb4_general_ci, algorithm=instant;
351 check table t;
352 alter table t modify bb char(70) collate utf8mb4_general_ci, algorithm=instant;
353 check table t;
354 alter table t modify cc char(100) collate utf8mb4_general_ci, algorithm=instant;
355 check table t;
356
357 alter table t modify d char(10) collate utf8mb4_spanish_ci, algorithm=instant;
358 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
359 alter table t modify dd char(10) collate utf8mb4_spanish_ci, algorithm=instant;
360 }
361 if ($row_format == 'redundant') {
362 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
363 alter table t modify a char(10) collate utf8mb4_general_ci, algorithm=instant;
364 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
365 alter table t modify b char(70) collate utf8mb4_general_ci, algorithm=instant;
366 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
367 alter table t modify c char(100) collate utf8mb4_general_ci, algorithm=instant;
368
369 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
370 alter table t modify aa char(10) collate utf8mb4_general_ci, algorithm=instant;
371 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
372 alter table t modify bb char(70) collate utf8mb4_general_ci, algorithm=instant;
373 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
374 alter table t modify cc char(100) collate utf8mb4_general_ci, algorithm=instant;
375
376 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
377 alter table t modify d char(10) collate utf8mb4_spanish_ci, algorithm=instant;
378 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
379 alter table t modify dd char(10) collate utf8mb4_spanish_ci, algorithm=instant;
380 }
381 select * from t;
382 drop table t;
383
384
385 create table fully_compatible (
386 id int auto_increment unique key,
387 from_charset char(255),
388 from_collate char(255),
389 to_charset char(255),
390 to_collate char(255)
391 );
392
393 insert into fully_compatible (from_charset, from_collate, to_charset, to_collate) values
394 ('utf8mb3', 'utf8mb3_general_ci', 'utf8mb4', 'utf8mb4_general_ci'),
395 ('utf8mb3', 'utf8mb3_bin', 'utf8mb4', 'utf8mb4_bin'),
396 ('utf8mb3', 'utf8mb3_unicode_ci', 'utf8mb4', 'utf8mb4_unicode_ci'),
397 ('utf8mb3', 'utf8mb3_icelandic_ci', 'utf8mb4', 'utf8mb4_icelandic_ci'),
398 ('utf8mb3', 'utf8mb3_latvian_ci', 'utf8mb4', 'utf8mb4_latvian_ci'),
399 ('utf8mb3', 'utf8mb3_romanian_ci', 'utf8mb4', 'utf8mb4_romanian_ci'),
400 ('utf8mb3', 'utf8mb3_slovenian_ci', 'utf8mb4', 'utf8mb4_slovenian_ci'),
401 ('utf8mb3', 'utf8mb3_polish_ci', 'utf8mb4', 'utf8mb4_polish_ci'),
402 ('utf8mb3', 'utf8mb3_estonian_ci', 'utf8mb4', 'utf8mb4_estonian_ci'),
403 ('utf8mb3', 'utf8mb3_spanish_ci', 'utf8mb4', 'utf8mb4_spanish_ci'),
404 ('utf8mb3', 'utf8mb3_swedish_ci', 'utf8mb4', 'utf8mb4_swedish_ci'),
405 ('utf8mb3', 'utf8mb3_turkish_ci', 'utf8mb4', 'utf8mb4_turkish_ci'),
406 ('utf8mb3', 'utf8mb3_czech_ci', 'utf8mb4', 'utf8mb4_czech_ci'),
407 ('utf8mb3', 'utf8mb3_danish_ci', 'utf8mb4', 'utf8mb4_danish_ci'),
408 ('utf8mb3', 'utf8mb3_lithuanian_ci', 'utf8mb4', 'utf8mb4_lithuanian_ci'),
409 ('utf8mb3', 'utf8mb3_slovak_ci', 'utf8mb4', 'utf8mb4_slovak_ci'),
410 ('utf8mb3', 'utf8mb3_spanish2_ci', 'utf8mb4', 'utf8mb4_spanish2_ci'),
411 ('utf8mb3', 'utf8mb3_roman_ci', 'utf8mb4', 'utf8mb4_roman_ci'),
412 ('utf8mb3', 'utf8mb3_persian_ci', 'utf8mb4', 'utf8mb4_persian_ci'),
413 ('utf8mb3', 'utf8mb3_esperanto_ci', 'utf8mb4', 'utf8mb4_esperanto_ci'),
414 ('utf8mb3', 'utf8mb3_hungarian_ci', 'utf8mb4', 'utf8mb4_hungarian_ci'),
415 ('utf8mb3', 'utf8mb3_sinhala_ci', 'utf8mb4', 'utf8mb4_sinhala_ci'),
416 ('utf8mb3', 'utf8mb3_german2_ci', 'utf8mb4', 'utf8mb4_german2_ci'),
417 ('utf8mb3', 'utf8mb3_croatian_mysql561_ci', 'utf8mb4', 'utf8mb4_croatian_mysql561_ci'),
418 ('utf8mb3', 'utf8mb3_unicode_520_ci', 'utf8mb4', 'utf8mb4_unicode_520_ci'),
419 ('utf8mb3', 'utf8mb3_vietnamese_ci', 'utf8mb4', 'utf8mb4_vietnamese_ci'),
420 ('utf8mb3', 'utf8mb3_croatian_ci', 'utf8mb4', 'utf8mb4_croatian_ci'),
421 ('utf8mb3', 'utf8mb3_myanmar_ci', 'utf8mb4', 'utf8mb4_myanmar_ci'),
422 ('utf8mb3', 'utf8mb3_thai_520_w2', 'utf8mb4', 'utf8mb4_thai_520_w2'),
423 ('utf8mb3', 'utf8mb3_general_nopad_ci', 'utf8mb4', 'utf8mb4_general_nopad_ci'),
424 ('utf8mb3', 'utf8mb3_nopad_bin', 'utf8mb4', 'utf8mb4_nopad_bin'),
425 ('utf8mb3', 'utf8mb3_unicode_nopad_ci', 'utf8mb4', 'utf8mb4_unicode_nopad_ci'),
426 ('utf8mb3', 'utf8mb3_unicode_520_nopad_ci', 'utf8mb4', 'utf8mb4_unicode_520_nopad_ci')
427 ;
428
429 let $data_size = `select count(*) from fully_compatible`;
430 let $counter = 1;
431
432 while ($counter <= $data_size) {
433 let $from_charset = `select from_charset from fully_compatible where id = $counter`;
434 let $from_collate = `select from_collate from fully_compatible where id = $counter`;
435 let $to_charset = `select to_charset from fully_compatible where id = $counter`;
436 let $to_collate = `select to_collate from fully_compatible where id = $counter`;
437
438 eval create table tmp (
439 a varchar(50) charset $from_charset collate $from_collate,
440 b varchar(50) charset $from_charset collate $from_collate primary key
441 ) engine=innodb;
442
443 insert into tmp values ('AAA', 'AAA'), ('bbb', 'bbb');
444
445 eval alter table tmp
446 change a a varchar(50) charset $to_charset collate $to_collate,
447 modify b varchar(50) charset $to_charset collate $to_collate,
448 algorithm=instant;
449
450 check table tmp;
451
452 drop table tmp;
453
454 inc $counter;
455 }
456
457 drop table fully_compatible;
458
459
460 create table compatible_without_index (
461 id int auto_increment unique key,
462 from_charset char(255),
463 from_collate char(255),
464 to_charset char(255),
465 to_collate char(255)
466 );
467
468 insert into compatible_without_index (from_charset, from_collate, to_charset, to_collate) values
469
470 ('utf8mb3', 'utf8mb3_general_ci', 'utf8mb4', 'utf8mb4_vietnamese_ci'),
471 ('utf8mb3', 'utf8mb3_bin', 'utf8mb4', 'utf8mb4_vietnamese_ci'),
472 ('utf8mb3', 'utf8mb3_general_nopad_ci', 'utf8mb4', 'utf8mb4_vietnamese_ci'),
473 ('utf8mb3', 'utf8mb3_nopad_bin', 'utf8mb4', 'utf8mb4_vietnamese_ci'),
474
475 ('ascii', 'ascii_general_ci', 'ascii', 'ascii_bin'),
476 ('utf8mb3', 'utf8mb3_roman_ci', 'utf8mb3', 'utf8mb3_lithuanian_ci'),
477 ('utf8mb4', 'utf8mb4_thai_520_w2', 'utf8mb4', 'utf8mb4_persian_ci'),
478 ('utf8mb3', 'utf8mb3_myanmar_ci', 'utf8mb4', 'utf8mb4_german2_ci'),
479 ('utf8mb3', 'utf8mb3_general_ci', 'utf8mb3', 'utf8mb3_unicode_ci'),
480 ('latin1', 'latin1_general_cs', 'latin1', 'latin1_general_ci'),
481
482 ('utf16', 'utf16_general_ci', 'utf16', 'utf16_german2_ci')
483 ;
484
485 let $data_size = `select count(*) from compatible_without_index`;
486 let $counter = 1;
487
488 while ($counter <= $data_size) {
489 let $from_charset = `select from_charset from compatible_without_index where id = $counter`;
490 let $from_collate = `select from_collate from compatible_without_index where id = $counter`;
491 let $to_charset = `select to_charset from compatible_without_index where id = $counter`;
492 let $to_collate = `select to_collate from compatible_without_index where id = $counter`;
493
494 eval create table tmp (
495 a varchar(50) charset $from_charset collate $from_collate,
496 b varchar(50) charset $from_charset collate $from_collate unique key,
497 c varchar(50) charset $from_charset collate $from_collate primary key
498 ) engine=innodb;
499
500 eval alter table tmp
501 change a a varchar(50) charset $to_charset collate $to_collate,
502 algorithm=instant;
503
504 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
505 eval alter table tmp
506 modify b varchar(50) charset $to_charset collate $to_collate,
507 algorithm=instant;
508
509 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
510 eval alter table tmp
511 modify c varchar(50) charset $to_charset collate $to_collate,
512 algorithm=instant;
513
514 drop table tmp;
515
516 inc $counter;
517 }
518
519 drop table compatible_without_index;
520
521
522 create table fully_incompatible (
523 id int auto_increment unique key,
524 from_charset char(255),
525 from_collate char(255),
526 to_charset char(255),
527 to_collate char(255)
528 );
529
530 insert into fully_incompatible (from_charset, from_collate, to_charset, to_collate) values
531 ('ascii', 'ascii_general_ci', 'utf8mb3', 'utf8mb3_general_ci'),
532 ('ascii', 'ascii_general_ci', 'utf8mb4', 'utf8mb4_general_ci'),
533 ('ascii', 'ascii_general_ci', 'latin1', 'latin1_general_ci'),
534 ('ascii', 'ascii_bin', 'latin1', 'latin1_bin'),
535 ('ascii', 'ascii_nopad_bin', 'latin1', 'latin1_nopad_bin'),
536 ('ascii', 'ascii_general_ci', 'latin2', 'latin2_general_ci'),
537 ('ascii', 'ascii_general_ci', 'latin7', 'latin7_general_ci'),
538 ('ascii', 'ascii_bin', 'koi8u', 'koi8u_bin'),
539 ('ascii', 'ascii_bin', 'ujis', 'ujis_bin'),
540 ('ascii', 'ascii_bin', 'big5', 'big5_bin'),
541 ('ascii', 'ascii_bin', 'gbk', 'gbk_bin'),
542
543 ('ascii', 'ascii_general_ci', 'utf8mb3', 'utf8mb3_swedish_ci'),
544 ('ascii', 'ascii_bin', 'latin1', 'latin1_swedish_ci'),
545 ('ascii', 'ascii_general_nopad_ci', 'latin1', 'latin1_swedish_ci'),
546 ('ascii', 'ascii_nopad_bin', 'latin1', 'latin1_swedish_ci'),
547
548 ('ascii', 'ascii_general_ci', 'koi8u', 'koi8u_bin'),
549 ('ascii', 'ascii_general_nopad_ci', 'koi8u', 'koi8u_bin'),
550 ('ascii', 'ascii_nopad_bin', 'koi8u', 'koi8u_bin'),
551
552 ('ascii', 'ascii_general_ci', 'latin1', 'latin1_swedish_ci'),
553 ('ascii', 'ascii_bin', 'utf8mb3', 'utf8mb3_swedish_ci'),
554 ('ascii', 'ascii_general_nopad_ci', 'utf8mb3', 'utf8mb3_swedish_ci'),
555 ('ascii', 'ascii_nopad_bin', 'utf8mb3', 'utf8mb3_swedish_ci'),
556
557 ('ascii', 'ascii_general_ci', 'utf8mb4', 'utf8mb4_danish_ci'),
558 ('ascii', 'ascii_bin', 'utf8mb4', 'utf8mb4_danish_ci'),
559 ('ascii', 'ascii_general_nopad_ci', 'utf8mb4', 'utf8mb4_danish_ci'),
560 ('ascii', 'ascii_nopad_bin', 'utf8mb4', 'utf8mb4_danish_ci'),
561
562 ('ascii', 'ascii_general_ci', 'gbk', 'gbk_chinese_ci'),
563 ('ascii', 'ascii_general_ci', 'gbk', 'gbk_chinese_nopad_ci'),
564
565 ('ascii', 'ascii_general_ci', 'ujis', 'ujis_japanese_ci'),
566 ('ascii', 'ascii_general_ci', 'big5', 'big5_chinese_ci'),
567 ('ascii', 'ascii_general_ci', 'latin2', 'latin2_croatian_ci'),
568 ('ascii', 'ascii_general_ci', 'latin7', 'latin7_estonian_cs'),
569
570 ('ucs2', 'ucs2_general_ci', 'utf16', 'utf16_general_ci'),
571 ('ucs2', 'ucs2_unicode_ci', 'utf16', 'utf16_unicode_ci'),
572 ('ucs2', 'ucs2_icelandic_ci', 'utf16', 'utf16_icelandic_ci'),
573 ('ucs2', 'ucs2_latvian_ci', 'utf16', 'utf16_latvian_ci'),
574 ('ucs2', 'ucs2_romanian_ci', 'utf16', 'utf16_romanian_ci'),
575 ('ucs2', 'ucs2_slovenian_ci', 'utf16', 'utf16_slovenian_ci'),
576 ('ucs2', 'ucs2_polish_ci', 'utf16', 'utf16_polish_ci'),
577 ('ucs2', 'ucs2_estonian_ci', 'utf16', 'utf16_estonian_ci'),
578 ('ucs2', 'ucs2_spanish_ci', 'utf16', 'utf16_spanish_ci'),
579 ('ucs2', 'ucs2_general_ci', 'utf16', 'utf16_general_ci'),
580 ('ucs2', 'ucs2_myanmar_ci', 'utf16', 'utf16_thai_520_w2'),
581 ('ucs2', 'ucs2_general_ci', 'utf16', 'utf16_unicode_nopad_ci'),
582 ('ucs2', 'ucs2_general_mysql500_ci', 'utf16', 'utf16_spanish2_ci'),
583
584 ('utf8mb4', 'utf8mb4_general_ci', 'utf8mb3', 'utf8mb3_general_ci'),
585 ('utf8mb4', 'utf8mb4_general_ci', 'ascii', 'ascii_general_ci'),
586 ('utf8mb3', 'utf8mb3_general_ci', 'ascii', 'ascii_general_ci'),
587 ('utf8mb3', 'utf8mb3_general_ci', 'latin1', 'latin1_general_ci'),
588 ('utf16', 'utf16_general_ci', 'utf32', 'utf32_general_ci'),
589 ('latin1', 'latin1_general_ci', 'ascii', 'ascii_general_ci'),
590 ('ascii', 'ascii_general_ci', 'swe7', 'swe7_swedish_ci'),
591 ('eucjpms', 'eucjpms_japanese_nopad_ci', 'geostd8', 'geostd8_general_ci'),
592 ('latin1', 'latin1_general_ci', 'utf16', 'utf16_general_ci')
593 ;
594
595 let $data_size = `select count(*) from fully_incompatible`;
596 let $counter = 1;
597
598 while ($counter <= $data_size) {
599 let $from_charset = `select from_charset from fully_incompatible where id = $counter`;
600 let $from_collate = `select from_collate from fully_incompatible where id = $counter`;
601 let $to_charset = `select to_charset from fully_incompatible where id = $counter`;
602 let $to_collate = `select to_collate from fully_incompatible where id = $counter`;
603
604 eval create table tmp (
605 a varchar(150) charset $from_charset collate $from_collate,
606 b text(150) charset $from_charset collate $from_collate,
607 unique key b_idx (b(150))
608 ) engine=innodb;
609
610 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
611 eval alter table tmp
612 change a a varchar(150) charset $to_charset collate $to_collate,
613 algorithm=instant;
614
615 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
616 eval alter table tmp
617 modify b text charset $to_charset collate $to_collate,
618 algorithm=instant;
619
620 drop table tmp;
621
622 inc $counter;
623 }
624
625 drop table fully_incompatible;
626
627 --echo #
628 --echo # MDEV-19284 INSTANT ALTER with ucs2-to-utf16 conversion produces bad data
629 --echo #
630
631 CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ucs2, PRIMARY KEY(a)) ENGINE=InnoDB;
632 INSERT INTO t1 VALUES ('a'),(0xD800);
633 --error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
634 ALTER TABLE t1 ALGORITHM=COPY, MODIFY a VARCHAR(10) CHARACTER SET utf16;
635 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
636 ALTER TABLE t1 ALGORITHM=INSTANT, MODIFY a VARCHAR(10) CHARACTER SET utf16;
637 --enable_info ONCE
638 ALTER IGNORE TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16;
639 SELECT HEX(a) FROM t1;
640 DROP TABLE t1;
641
642 --echo #
643 --echo # MDEV-19285 INSTANT ALTER from ascii_general_ci to latin1_general_ci produces corrupt data
644 --echo #
645
646 CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_ci, PRIMARY KEY(a)) ENGINE=InnoDB;
647 INSERT INTO t1 VALUES ('a'),(0xC0),('b');
648 --error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
649 ALTER TABLE t1 ALGORITHM=COPY, MODIFY a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci;
650 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
651 ALTER TABLE t1 ALGORITHM=INSTANT, MODIFY a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci;
652 --enable_info ONCE
653 ALTER IGNORE TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci;
654 SELECT HEX(a) FROM t1;
655 DROP TABLE t1;
656
657
658
659 --echo #
660 --echo # MDEV-19524 Server crashes in Bitmap<64u>::is_clear_all / Field_longstr::csinfo_change_allows_instant_alter
661 --echo #
662
663 CREATE TABLE t1 (a VARCHAR(1), UNIQUE(a)) ENGINE=InnoDB;
664 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
665 ALTER TABLE t1 MODIFY a INT, ADD b INT, ADD UNIQUE (b), ALGORITHM=INSTANT;
666 DROP TABLE t1;
667
668
669 --echo #
670 --echo # MDEV-17301 Change of COLLATE unnecessarily requires ALGORITHM=COPY
671 --echo #
672
673 create table t (
674 a char(10) collate latin1_general_ci primary key,
675 b char(10) collate latin1_general_ci,
676 c char(10) collate latin1_general_ci,
677 unique key b_key(b)
678 ) engine=innodb;
679
680 insert into t values
681 ('aaa', 'aaa', 'aaa'), ('ccc', 'ccc', 'ccc'), ('bbb', 'bbb', 'bbb');
682
683 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
684 alter table t modify a char(10) collate latin1_general_cs, algorithm=inplace;
685
686 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
687 alter table t modify b char(10) collate latin1_general_cs, algorithm=instant;
688 alter table t modify b char(10) collate latin1_general_cs, algorithm=nocopy;
689 check table t;
690
691 alter table t modify c char(10) collate latin1_general_cs, algorithm=instant;
692 check table t;
693
694 drop table t;
695
696 create table t (
697 a varchar(10) collate latin1_general_ci primary key,
698 b varchar(10) collate latin1_general_ci,
699 c varchar(10) collate latin1_general_ci,
700 unique key b_key(b)
701 ) engine=innodb;
702
703 insert into t values
704 ('aaa', 'aaa', 'aaa'), ('ccc', 'ccc', 'ccc'), ('bbb', 'bbb', 'bbb');
705
706 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
707 alter table t modify a varchar(10) collate latin1_general_cs, algorithm=inplace;
708
709 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
710 alter table t modify b varchar(10) collate latin1_general_cs, algorithm=instant;
711 alter table t modify b varchar(10) collate latin1_general_cs, algorithm=nocopy;
712 check table t;
713
714 alter table t modify c varchar(10) collate latin1_general_cs, algorithm=instant;
715 check table t;
716
717 drop table t;
718
719 --echo #
720 --echo # MDEV-20726: InnoDB: Assertion failure in file data0type.cc line 67
721 --echo #
722
723 CREATE TABLE t (
724 id int(10) unsigned NOT NULL PRIMARY KEY,
725 a text CHARSET utf8mb3,
726 KEY a_idx(a(1))
727 ) ENGINE=InnoDB;
728
729 INSERT INTO t VALUES (1, 'something in the air');
730 ALTER TABLE t MODIFY a text CHARSET utf8mb4;
731
732 DROP TABLE t;
733
734
735 --echo #
736 --echo # MDEV-22899: Assertion `field->col->is_binary() || field->prefix_len % field->col->mbmaxlen == 0' failed in dict_index_add_to_cache
737 --echo #
738
739 CREATE TABLE t1 (
740 a text CHARACTER SET utf8 DEFAULT NULL,
741 KEY a_key (a(1))
742 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
743 INSERT INTO t1 VALUES ();
744 ALTER TABLE t1 MODIFY a text DEFAULT NULL;
745 DROP TABLE t1;
746
747 CREATE TABLE t1 (
748 a text CHARACTER SET utf8 DEFAULT NULL,
749 b int,
750 KEY a_key (b, a(1))
751 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
752 INSERT INTO t1 VALUES ();
753 ALTER TABLE t1 MODIFY a text DEFAULT NULL;
754 DROP TABLE t1;
755
756 CREATE TABLE t1 (
757 a char(200) CHARACTER SET utf8 DEFAULT NULL,
758 KEY a_key (a(1))
759 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
760 INSERT INTO t1 VALUES ();
761 ALTER TABLE t1 MODIFY a text DEFAULT NULL;
762 DROP TABLE t1;
763
764 CREATE TABLE t1 (
765 a char(200) CHARACTER SET utf8 DEFAULT NULL,
766 b int,
767 KEY a_key (b, a(1))
768 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
769 INSERT INTO t1 VALUES ();
770 ALTER TABLE t1 MODIFY a text DEFAULT NULL;
771 DROP TABLE t1;
772
773 CREATE TABLE t1 (
774 a varchar(200) CHARACTER SET utf8 DEFAULT NULL,
775 KEY a_key (a(1))
776 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
777 INSERT INTO t1 VALUES ();
778 ALTER TABLE t1 MODIFY a text DEFAULT NULL;
779 DROP TABLE t1;
780
781 CREATE TABLE t1 (
782 a varchar(200) CHARACTER SET utf8 DEFAULT NULL,
783 b int,
784 KEY a_key (b, a(1))
785 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
786 INSERT INTO t1 VALUES ();
787 ALTER TABLE t1 MODIFY a text DEFAULT NULL;
788 DROP TABLE t1;
789
790 CREATE TABLE t1 (
791 a varchar(2000) CHARACTER SET utf8 DEFAULT NULL,
792 KEY a_key (a(1))
793 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
794 INSERT INTO t1 VALUES ();
795 ALTER TABLE t1 MODIFY a text DEFAULT NULL;
796 DROP TABLE t1;
797
798 CREATE TABLE t1 (
799 a varchar(2000) CHARACTER SET utf8 DEFAULT NULL,
800 b int,
801 KEY a_key (b, a(1))
802 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
803 INSERT INTO t1 VALUES ();
804 ALTER TABLE t1 MODIFY a text DEFAULT NULL;
805 DROP TABLE t1;
806
807 --echo #
808 --echo # MDEV-23245 Still getting assertion failure in file data0type.cc line 67
809 --echo #
810
811 CREATE TABLE Foo
812 (
813 Bar char(2) CHARACTER SET utf8,
814 KEY Bar (Bar(1))
815 ) ENGINE = InnoDB;
816 ALTER TABLE Foo MODIFY Bar char(2) CHARACTER SET utf8mb4;
817 INSERT INTO Foo VALUES ('a');
818 DROP TABLE Foo;
819
820 CREATE TABLE Foo
821 (
822 Bar varchar(2) CHARACTER SET utf8,
823 KEY Bar (Bar(1))
824 ) ENGINE = InnoDB;
825 ALTER TABLE Foo MODIFY Bar varchar(2) CHARACTER SET utf8mb4;
826 INSERT INTO Foo VALUES ('a');
827 DROP TABLE Foo;
828
829 CREATE TABLE Foo
830 (
831 Bar text CHARACTER SET utf8,
832 KEY Bar (Bar(1))
833 ) ENGINE = InnoDB;
834 ALTER TABLE Foo MODIFY Bar text CHARACTER SET utf8mb4;
835 INSERT INTO Foo VALUES ('a');
836 DROP TABLE Foo;
837
838 CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
839 PRIMARY KEY (a(1)))
840 ENGINE=InnoDB;
841 SHOW CREATE TABLE t1;
842 ALTER TABLE t1 MODIFY a VARCHAR(2)
843 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
844 INSERT INTO t1 VALUES ('a');
845 DROP TABLE t1;
846
847
848 --echo #
849 --echo # MDEV-22775 [HY000][1553] Changing name of primary key column with foreign key constraint fails.
850 --echo #
851
852 create table t1 (id int primary key) engine=innodb default charset=utf8;
853 create table t2 (input_id int primary key, id int not null,
854 key a (id),
855 constraint a foreign key (id) references t1 (id)
856 )engine=innodb default charset=utf8;
857 alter table t1 change id id2 int;
858 drop table t2;
859 drop table t1;
860
861
862 --echo #
863 --echo # MDEV-25951 MariaDB crash after ALTER TABLE convert to utf8mb4
864 --echo #
865
866 CREATE TABLE t1 (id INT PRIMARY KEY, a VARCHAR(32), KEY (a(7))) ENGINE=INNODB DEFAULT CHARSET=UTF8;
867 INSERT INTO t1 VALUES (1, 'a1'), (2, 'a1');
868
869 --error ER_DUP_ENTRY
870 ALTER TABLE t1
871 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
872 ADD UNIQUE INDEX test_key (a);
873
874 ALTER TABLE t1 CONVERT TO CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_520_CI;
875 CHECK TABLE t1;
876 SELECT * FROM t1;
877 DROP TABLE t1;
878
879 CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(32), KEY (a(7))) ENGINE=INNODB DEFAULT CHARSET=UTF8;
880 INSERT INTO t1 VALUES (1, 'a1'), (2, 'a1');
881
882 --error ER_DUP_ENTRY
883 ALTER TABLE t1
884 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
885 ADD UNIQUE INDEX test_key (a);
886
887 ALTER TABLE t1 CONVERT TO CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_520_CI;
888 CHECK TABLE t1;
889 SELECT * FROM t1;
890 DROP TABLE t1;