1# InnoDB page size 16k is required 2 3# 4# Test of alter table 5# 6 7SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 8 9SET SESSION information_schema_stats_expiry=0; 10 11create table t1 ( 12col1 int not null auto_increment primary key, 13col2 varchar(30) not null, 14col3 varchar (20) not null, 15col4 varchar(4) not null, 16col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null, 17col6 int not null, to_be_deleted int); 18insert into t1 values (2,4,3,5,"PENDING",1,7); 19alter table t1 20add column col4_5 varchar(20) not null after col4, 21add column col7 varchar(30) not null after col5, 22add column col8 datetime not null, drop column to_be_deleted, 23change column col2 fourth varchar(30) not null after col3, 24modify column col6 int not null first; 25select * from t1; 26drop table t1; 27 28create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL) engine=myisam; 29insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12); 30alter table t1 add column new_col int, order by payoutid,bandid; 31select * from t1; 32alter table t1 order by bandid,payoutid; 33select * from t1; 34drop table t1; 35 36# ORDER BY does not make sence for InnoDB tables,because InnoDB always orders table rows based on the clustered index 37create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL) engine=InnoDB; 38insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12); 39alter table t1 add column new_col int, order by payoutid,bandid; 40select * from t1; 41alter table t1 order by bandid,payoutid; 42select * from t1; 43drop table t1; 44 45# Check that pack_keys and dynamic length rows are not forced. 46 47CREATE TABLE t1 ( 48GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL, 49LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL, 50NAME varchar(80) DEFAULT '' NOT NULL, 51PRIMARY KEY (GROUP_ID,LANG_ID), 52KEY NAME (NAME)); 53#show table status like "t1"; 54ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null; 55--replace_column 8 # 56SHOW FULL COLUMNS FROM t1; 57DROP TABLE t1; 58 59# 60# Test of ALTER TABLE ... ORDER BY 61# 62 63create table t1 (n int); 64insert into t1 values(9),(3),(12),(10); 65alter table t1 order by n; 66select * from t1; 67drop table t1; 68 69CREATE TABLE t1 ( 70 id int(11) unsigned NOT NULL default '0', 71 category_id tinyint(4) unsigned NOT NULL default '0', 72 type_id tinyint(4) unsigned NOT NULL default '0', 73 body text NOT NULL, 74 user_id int(11) unsigned NOT NULL default '0', 75 status enum('new','old') NOT NULL default 'new', 76 PRIMARY KEY (id) 77) ENGINE=MyISAM; 78 79ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body; 80DROP TABLE t1; 81 82# 83# The following combination found a hang-bug in MyISAM 84# 85 86CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) engine=myisam; 87insert into t1 values (null,"hello"); 88LOCK TABLES t1 WRITE; 89ALTER TABLE t1 ADD Column new_col int not null; 90UNLOCK TABLES; 91OPTIMIZE TABLE t1; 92DROP TABLE t1; 93 94# 95# Drop and add an auto_increment column 96# 97 98create table t1 (i int unsigned not null auto_increment primary key); 99insert into t1 values (null),(null),(null),(null); 100alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i); 101select * from t1; 102drop table t1; 103 104# 105# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1 106# if it exists 107# 108create table t1 (name char(15)); 109insert into t1 (name) values ("current"); 110create database mysqltest; 111create table mysqltest.t1 (name char(15)); 112insert into mysqltest.t1 (name) values ("mysqltest"); 113select * from t1; 114select * from mysqltest.t1; 115--error ER_TABLE_EXISTS_ERROR 116alter table t1 rename mysqltest.t1; 117select * from t1; 118select * from mysqltest.t1; 119drop table t1; 120drop database mysqltest; 121 122# Disable/Enable keys supported by Myisam only 123# ALTER TABLE ... ENABLE/DISABLE KEYS 124 125create table t1 (n1 int not null, n2 int, n3 int, n4 float, 126 unique(n1), 127 key (n1, n2, n3, n4), 128 key (n2, n3, n4, n1), 129 key (n3, n4, n1, n2), 130 key (n4, n1, n2, n3) ) engine=Myisam; 131alter table t1 disable keys; 132show keys from t1; 133#let $1=10000; 134let $1=10; 135while ($1) 136{ 137 eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND()); 138 dec $1; 139} 140alter table t1 enable keys; 141show keys from t1; 142drop table t1; 143 144# 145# Alter table and rename 146# 147 148create table t1 (i int unsigned not null auto_increment primary key); 149alter table t1 rename t2; 150alter table t2 rename t1, add c char(10) comment "no comment"; 151show columns from t1; 152drop table t1; 153 154# implicit analyze 155 156create table t1 (a int, b int); 157let $1=100; 158while ($1) 159{ 160 eval insert into t1 values(1,$1), (2,$1), (3, $1); 161 dec $1; 162} 163alter table t1 add unique (a,b), add key (b); 164show keys from t1; 165analyze table t1; 166show keys from t1; 167drop table t1; 168 169# 170# Test ALTER TABLE ENABLE/DISABLE keys when things are locked 171# 172 173CREATE TABLE t1 ( 174 Host varchar(16) binary NOT NULL default '', 175 User varchar(16) binary NOT NULL default '', 176 PRIMARY KEY (Host,User) 177) ENGINE=MyISAM; 178 179ALTER TABLE t1 DISABLE KEYS; 180LOCK TABLES t1 WRITE; 181INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); 182SHOW INDEX FROM t1; 183ALTER TABLE t1 ENABLE KEYS; 184UNLOCK TABLES; 185CHECK TABLES t1; 186DROP TABLE t1; 187 188# 189# Test with two keys 190# 191 192CREATE TABLE t1 ( 193 Host varchar(16) binary NOT NULL default '', 194 User varchar(16) binary NOT NULL default '', 195 PRIMARY KEY (Host,User), 196 KEY (Host) 197) ENGINE=MyISAM; 198 199ALTER TABLE t1 DISABLE KEYS; 200SHOW INDEX FROM t1; 201LOCK TABLES t1 WRITE; 202INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); 203SHOW INDEX FROM t1; 204ALTER TABLE t1 ENABLE KEYS; 205SHOW INDEX FROM t1; 206UNLOCK TABLES; 207CHECK TABLES t1; 208 209# Test RENAME with LOCK TABLES 210LOCK TABLES t1 WRITE; 211ALTER TABLE t1 RENAME t2; 212UNLOCK TABLES; 213select * from t2; 214DROP TABLE t2; 215 216# 217# Test disable keys with locking 218# 219CREATE TABLE t1 ( 220 Host varchar(16) binary NOT NULL default '', 221 User varchar(16) binary NOT NULL default '', 222 PRIMARY KEY (Host,User), 223 KEY (Host) 224) ENGINE=MyISAM; 225 226LOCK TABLES t1 WRITE; 227ALTER TABLE t1 DISABLE KEYS; 228SHOW INDEX FROM t1; 229DROP TABLE t1; 230 231# 232# BUG#4717 - check for valid table names 233# 234create table t1 (a int); 235--error ER_WRONG_TABLE_NAME 236alter table t1 rename to ``; 237--error ER_WRONG_TABLE_NAME 238rename table t1 to ``; 239drop table t1; 240 241# 242# BUG#6236 - ALTER TABLE MODIFY should set implicit NOT NULL on PK columns 243# 244drop table if exists t1, t2; 245create table t1 ( a varchar(10) not null primary key ) engine=myisam; 246create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1); 247flush tables; 248alter table t1 modify a varchar(10); 249show create table t2; 250flush tables; 251alter table t1 modify a varchar(10) not null; 252show create table t2; 253drop table if exists t1, t2; 254 255# The following is also part of bug #6236 (CREATE TABLE didn't properly count 256# not null columns for primary keys) 257 258create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM; 259insert into t1 (a) values(1); 260--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 261show table status like 't1'; 262alter table t1 modify a int; 263--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 264show table status like 't1'; 265drop table t1; 266create table t1 (a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, g int not null, h int not null,i int not null, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM; 267insert into t1 (a) values(1); 268--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 269show table status like 't1'; 270drop table t1; 271 272# 273# Test that data get converted when character set is changed 274# Test that data doesn't get converted when src or dst is BINARY/BLOB 275# 276set names koi8r; 277create table t1 (a char(10) character set koi8r); 278insert into t1 values ('����'); 279select a,hex(a) from t1; 280alter table t1 change a a char(10) character set cp1251; 281select a,hex(a) from t1; 282alter table t1 change a a binary(4); 283select a,hex(a) from t1; 284alter table t1 change a a char(10) character set cp1251; 285select a,hex(a) from t1; 286alter table t1 change a a char(10) character set koi8r; 287select a,hex(a) from t1; 288alter table t1 change a a varchar(10) character set cp1251; 289select a,hex(a) from t1; 290alter table t1 change a a char(10) character set koi8r; 291select a,hex(a) from t1; 292alter table t1 change a a text character set cp1251; 293select a,hex(a) from t1; 294alter table t1 change a a char(10) character set koi8r; 295select a,hex(a) from t1; 296delete from t1; 297 298# 299# Test ALTER TABLE .. CHARACTER SET .. 300# 301show create table t1; 302alter table t1 DEFAULT CHARACTER SET latin1; 303show create table t1; 304alter table t1 CONVERT TO CHARACTER SET latin1; 305show create table t1; 306alter table t1 DEFAULT CHARACTER SET cp1251; 307show create table t1; 308 309drop table t1; 310 311# 312# Bug#2821 313# Test that table CHARACTER SET does not affect blobs 314# 315create table t1 (myblob longblob,mytext longtext) 316default charset latin1 collate latin1_general_cs; 317show create table t1; 318alter table t1 character set latin2; 319show create table t1; 320drop table t1; 321 322# 323# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY) 324# 325 326CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE); 327ALTER TABLE t1 DROP PRIMARY KEY; 328SHOW CREATE TABLE t1; 329--error ER_CANT_DROP_FIELD_OR_KEY 330ALTER TABLE t1 DROP PRIMARY KEY; 331DROP TABLE t1; 332 333# BUG#3899 334create table t1 (a int, b int, key(a)); 335insert into t1 values (1,1), (2,2); 336--error ER_CANT_DROP_FIELD_OR_KEY 337alter table t1 drop key no_such_key; 338alter table t1 drop key a; 339drop table t1; 340 341# 342# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000) 343# 344# Some platforms (Mac OS X, Windows) will send the error message using small letters. 345CREATE TABLE T12207(a int) ENGINE=MYISAM; 346--replace_result t12207 T12207 347--error ER_ILLEGAL_HA 348ALTER TABLE T12207 DISCARD TABLESPACE; 349DROP TABLE T12207; 350 351# 352# Bug #6479 ALTER TABLE ... changing charset fails for TEXT columns 353# 354# The column's character set was changed but the actual data was not 355# modified. In other words, the values were reinterpreted 356# as UTF8 instead of being converted. 357create table t1 (a text) character set koi8r; 358insert into t1 values (_koi8r'����'); 359select hex(a) from t1; 360alter table t1 convert to character set cp1251; 361select hex(a) from t1; 362drop table t1; 363 364# 365# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix" 366# MySQL should not think that packed field with non-zero decimals is 367# geometry field and allow to create prefix index which is 368# shorter than packed field length. 369# 370create table t1 ( a timestamp ); 371--error ER_WRONG_SUB_KEY 372alter table t1 add unique ( a(1) ); 373drop table t1; 374 375# 376# Disable/Enable keys supported by Myisam only 377# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table 378# 379# This problem happens if the data change is compatible. 380# Changing to the same type is compatible for example. 381# 382create table t1 (a int, key(a)) engine=myisam; 383show indexes from t1; 384--echo "this used not to disable the index" 385alter table t1 modify a int, disable keys; 386show indexes from t1; 387 388alter table t1 enable keys; 389show indexes from t1; 390 391alter table t1 modify a bigint, disable keys; 392show indexes from t1; 393 394alter table t1 enable keys; 395show indexes from t1; 396 397alter table t1 add b char(10), disable keys; 398show indexes from t1; 399 400alter table t1 add c decimal(10,2), enable keys; 401show indexes from t1; 402 403--echo "this however did" 404alter table t1 disable keys; 405show indexes from t1; 406 407desc t1; 408 409alter table t1 add d decimal(15,5); 410--echo "The key should still be disabled" 411show indexes from t1; 412 413drop table t1; 414 415--echo "Now will test with one unique index" 416create table t1(a int, b char(10), unique(a)) engine=myisam; 417show indexes from t1; 418alter table t1 disable keys; 419show indexes from t1; 420alter table t1 enable keys; 421 422--echo "If no copy on noop change, this won't touch the data file" 423--echo "Unique index, no change" 424alter table t1 modify a int, disable keys; 425show indexes from t1; 426 427--echo "Change the type implying data copy" 428--echo "Unique index, no change" 429alter table t1 modify a bigint, disable keys; 430show indexes from t1; 431 432alter table t1 modify a bigint; 433show indexes from t1; 434 435alter table t1 modify a int; 436show indexes from t1; 437 438drop table t1; 439 440--echo "Now will test with one unique and one non-unique index" 441create table t1(a int, b char(10), unique(a), key(b)) engine=myisam; 442show indexes from t1; 443alter table t1 disable keys; 444show indexes from t1; 445alter table t1 enable keys; 446 447 448--echo "If no copy on noop change, this won't touch the data file" 449--echo "The non-unique index will be disabled" 450alter table t1 modify a int, disable keys; 451show indexes from t1; 452alter table t1 enable keys; 453show indexes from t1; 454 455--echo "Change the type implying data copy" 456--echo "The non-unique index will be disabled" 457alter table t1 modify a bigint, disable keys; 458show indexes from t1; 459 460--echo "Change again the type, but leave the indexes as_is" 461alter table t1 modify a int; 462show indexes from t1; 463--echo "Try the same. When data is no copied on similar tables, this is noop" 464alter table t1 modify a int; 465show indexes from t1; 466 467drop table t1; 468 469 470# 471# Bug#11493 - Alter table rename to default database does not work without 472# db name qualifying 473# 474create database mysqltest; 475create table t1 (c1 int); 476# Move table to other database. 477alter table t1 rename mysqltest.t1; 478# Assure that it has moved. 479--error ER_BAD_TABLE_ERROR 480drop table t1; 481# Move table back. 482alter table mysqltest.t1 rename t1; 483# Assure that it is back. 484drop table t1; 485# Now test for correct message if no database is selected. 486# Create t1 in 'test'. 487create table t1 (c1 int); 488# Change to other db. 489use mysqltest; 490# Drop the current db. This de-selects any db. 491drop database mysqltest; 492# Now test for correct message. 493--error ER_NO_DB_ERROR 494alter table test.t1 rename t1; 495# Check that explicit qualifying works even with no selected db. 496alter table test.t1 rename test.t1; 497# Go back to standard 'test' db. 498use test; 499drop table t1; 500 501# 502# ROW_FORMAT=FIXED supported by Myisam only 503# BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the 504# table 505# 506CREATE TABLE t1(a INT) ENGINE=MyISAM ROW_FORMAT=FIXED; 507CREATE INDEX i1 ON t1(a); 508SHOW CREATE TABLE t1; 509DROP INDEX i1 ON t1; 510SHOW CREATE TABLE t1; 511DROP TABLE t1; 512 513# 514# Disable/Enable keys supported by Myisam only 515# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash 516# 517 518CREATE TABLE bug24219 (a INT, INDEX(a)) ENGINE=MyISAM; 519 520SHOW INDEX FROM bug24219; 521 522ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; 523 524SHOW INDEX FROM bug24219_2; 525 526DROP TABLE bug24219_2; 527 528# 529# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts) 530# 531 532create table table_24562( 533 section int, 534 subsection int, 535 title varchar(50)); 536 537insert into table_24562 values 538(1, 0, "Introduction"), 539(1, 1, "Authors"), 540(1, 2, "Acknowledgements"), 541(2, 0, "Basics"), 542(2, 1, "Syntax"), 543(2, 2, "Client"), 544(2, 3, "Server"), 545(3, 0, "Intermediate"), 546(3, 1, "Complex queries"), 547(3, 2, "Stored Procedures"), 548(3, 3, "Stored Functions"), 549(4, 0, "Advanced"), 550(4, 1, "Replication"), 551(4, 2, "Load balancing"), 552(4, 3, "High availability"), 553(5, 0, "Conclusion"); 554 555select * from table_24562; 556 557alter table table_24562 add column reviewer varchar(20), 558order by title; 559 560select * from table_24562; 561 562update table_24562 set reviewer="Me" where section=2; 563update table_24562 set reviewer="You" where section=3; 564 565alter table table_24562 566order by section ASC, subsection DESC; 567 568select * from table_24562; 569 570alter table table_24562 571order by table_24562.subsection ASC, table_24562.section DESC; 572 573select * from table_24562; 574 575--error ER_PARSE_ERROR 576alter table table_24562 order by 12; 577--error ER_PARSE_ERROR 578alter table table_24562 order by (section + 12); 579--error ER_PARSE_ERROR 580alter table table_24562 order by length(title); 581--error ER_PARSE_ERROR 582alter table table_24562 order by (select 12 from dual); 583 584--error ER_BAD_FIELD_ERROR 585alter table table_24562 order by no_such_col; 586 587drop table table_24562; 588 589# End of 4.1 tests 590 591# 592# Bug #14693 (ALTER SET DEFAULT doesn't work) 593# 594 595create table t1 (mycol int(10) not null); 596alter table t1 alter column mycol set default 0; 597desc t1; 598drop table t1; 599 600# 601# Bug#25262 Auto Increment lost when changing Engine type 602# 603 604create table t1(id int(8) primary key auto_increment) engine=heap; 605 606insert into t1 values (null); 607insert into t1 values (null); 608 609select * from t1; 610 611# Set auto increment to 50 612alter table t1 auto_increment = 50; 613 614# Alter to myisam 615alter table t1 engine = myisam; 616 617# This insert should get id 50 618insert into t1 values (null); 619select * from t1; 620 621# Alter to heap again 622alter table t1 engine = heap; 623insert into t1 values (null); 624select * from t1; 625 626drop table t1; 627 628# 629# Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the 630# NO_ZERO_DATE mode. 631# 632set sql_mode= default; 633create table t1(f1 int) engine=myisam; 634alter table t1 add column f2 datetime not null, add column f21 date not null; 635insert into t1 values(1,'2000-01-01','2000-01-01'); 636--error 1292 637alter table t1 add column f3 datetime not null; 638--error 1292 639alter table t1 add column f3 date not null; 640--error 1292 641alter table t1 add column f4 datetime not null default '2002-02-02', 642 add column f41 date not null; 643alter table t1 add column f4 datetime not null default '2002-02-02', 644 add column f41 date not null default '2002-02-02'; 645select * from t1; 646drop table t1; 647 648# 649# Some additional tests for new, faster alter table. Note that most of the 650# whole alter table code is being tested all around the test suite already. 651# 652 653create table t1 (v varchar(32)); 654insert into t1 values ('def'),('abc'),('hij'),('3r4f'); 655select * from t1; 656# Fast alter, no copy performed 657alter table t1 change v v2 varchar(32); 658select * from t1; 659# Fast alter, no copy performed 660alter table t1 change v2 v varchar(64); 661select * from t1; 662update t1 set v = 'lmn' where v = 'hij'; 663select * from t1; 664# Regular alter table 665alter table t1 add i int auto_increment not null primary key first; 666select * from t1; 667update t1 set i=5 where i=3; 668select * from t1; 669alter table t1 change i i bigint; 670select * from t1; 671alter table t1 add unique key (i, v); 672select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn'); 673drop table t1; 674 675# 676# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index 677# without # prefix is not allowed for TEXT columns, while index 678# is defined with prefix. 679# 680create table t1 (t varchar(255) default null, key t (t(80))) 681engine=myisam default charset=latin1; 682alter table t1 change t t text; 683drop table t1; 684 685# 686# Bug #26794: Adding an index with a prefix on a SPATIAL type breaks ALTER 687# TABLE 688# 689CREATE TABLE t1 (a varchar(500)); 690 691ALTER TABLE t1 ADD b GEOMETRY NOT NULL SRID 0, ADD SPATIAL INDEX(b); 692SHOW CREATE TABLE t1; 693--error ER_WRONG_SUB_KEY 694ALTER TABLE t1 ADD KEY(b(50)); 695SHOW CREATE TABLE t1; 696 697ALTER TABLE t1 ADD c POINT; 698SHOW CREATE TABLE t1; 699 700--error ER_WRONG_SUB_KEY 701CREATE TABLE t2 (a INT, KEY (a(20))); 702 703ALTER TABLE t1 ADD d INT; 704--error ER_WRONG_SUB_KEY 705ALTER TABLE t1 ADD KEY (d(20)); 706 707# the 5.1 part of the test 708--error ER_WRONG_SUB_KEY 709ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30)); 710 711DROP TABLE t1; 712 713# 714# Bug#18038 MySQL server corrupts binary columns data 715# 716 717CREATE TABLE t1 (s CHAR(8) BINARY); 718INSERT INTO t1 VALUES ('test'); 719SELECT LENGTH(s) FROM t1; 720ALTER TABLE t1 MODIFY s CHAR(10) BINARY; 721SELECT LENGTH(s) FROM t1; 722DROP TABLE t1; 723 724CREATE TABLE t1 (s BINARY(8)); 725INSERT INTO t1 VALUES ('test'); 726SELECT LENGTH(s) FROM t1; 727SELECT HEX(s) FROM t1; 728ALTER TABLE t1 MODIFY s BINARY(10); 729SELECT HEX(s) FROM t1; 730SELECT LENGTH(s) FROM t1; 731DROP TABLE t1; 732 733# 734# Bug#19386: Multiple alter causes crashed table 735# The trailing column would get corrupted data, or server could not even read 736# it. 737# 738 739CREATE TABLE t1 (v VARCHAR(3), b INT); 740INSERT INTO t1 VALUES ('abc', 5); 741SELECT * FROM t1; 742ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4); 743SELECT * FROM t1; 744DROP TABLE t1; 745 746 747# 748# Bug#31291 ALTER TABLE CONVERT TO CHARACTER SET does not change some data types 749# 750create table t1 (a tinytext character set latin1); 751alter table t1 convert to character set utf8; 752show create table t1; 753drop table t1; 754create table t1 (a mediumtext character set latin1); 755alter table t1 convert to character set utf8; 756show create table t1; 757drop table t1; 758 759--echo End of 5.0 tests 760 761# 762# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES 763# It should be consistent across all platforms and for all engines 764# (Before 5.1 this was not true as behavior was different between 765# Unix/Windows and transactional/non-transactional tables). 766# See also innodb_mysql.test. 767# See alter_table-big.test for extended full ALTER TABLE RENAME coverage. 768# 769create table t1 (i int); 770create table t3 (j int); 771insert into t1 values (); 772insert into t3 values (); 773# Table which is altered under LOCK TABLES it should stay in list of locked 774# tables and be available after alter takes place unless ALTER contains RENAME 775# clause. We should see the new definition of table, of course. 776lock table t1 write, t3 read; 777# Example of so-called 'fast' ALTER TABLE 778alter table t1 modify i int default 1; 779insert into t1 values (); 780select * from t1; 781# And now full-blown ALTER TABLE 782alter table t1 change i c char(10) default "Two"; 783insert into t1 values (); 784select * from t1; 785# If table is renamed then it should stay in the list of locked tables 786# under new name (unless SE doesn't support atomic DDL and it is an error). 787# 'Fast' ALTER TABLE with RENAME clause: 788alter table t1 modify c char(10) default "Three", rename to t2; 789--error ER_TABLE_NOT_LOCKED 790select * from t1; 791select * from t2; 792select * from t3; 793unlock tables; 794insert into t2 values (); 795select * from t2; 796lock table t2 write, t3 read; 797# Full ALTER TABLE with RENAME 798alter table t2 change c vc varchar(100) default "Four", rename to t1; 799select * from t1; 800--error ER_TABLE_NOT_LOCKED 801select * from t2; 802select * from t3; 803unlock tables; 804insert into t1 values (); 805select * from t1; 806drop tables t1, t3; 807 808 809# 810# Bug#18775 - Temporary table from alter table visible to other threads 811# 812# Check if special characters work and duplicates are detected. 813CREATE TABLE `t+1` (c1 INT); 814ALTER TABLE `t+1` RENAME `t+2`; 815CREATE TABLE `t+1` (c1 INT); 816--error ER_TABLE_EXISTS_ERROR 817ALTER TABLE `t+1` RENAME `t+2`; 818DROP TABLE `t+1`, `t+2`; 819# 820# Same for temporary tables though these names do not become file names. 821CREATE TEMPORARY TABLE `tt+1` (c1 INT); 822ALTER TABLE `tt+1` RENAME `tt+2`; 823CREATE TEMPORARY TABLE `tt+1` (c1 INT); 824--error ER_TABLE_EXISTS_ERROR 825ALTER TABLE `tt+1` RENAME `tt+2`; 826SHOW CREATE TABLE `tt+1`; 827SHOW CREATE TABLE `tt+2`; 828DROP TABLE `tt+1`, `tt+2`; 829# 830# Check if special characters as in tmp_file_prefix work. 831CREATE TABLE `#sql1` (c1 INT); 832CREATE TABLE `@0023sql2` (c1 INT); 833SHOW TABLES; 834RENAME TABLE `#sql1` TO `@0023sql1`; 835RENAME TABLE `@0023sql2` TO `#sql2`; 836SHOW TABLES; 837ALTER TABLE `@0023sql1` RENAME `#sql-1`; 838ALTER TABLE `#sql2` RENAME `@0023sql-2`; 839SHOW TABLES; 840INSERT INTO `#sql-1` VALUES (1); 841INSERT INTO `@0023sql-2` VALUES (2); 842DROP TABLE `#sql-1`, `@0023sql-2`; 843# 844# Same for temporary tables though these names do not become file names. 845CREATE TEMPORARY TABLE `#sql1` (c1 INT); 846CREATE TEMPORARY TABLE `@0023sql2` (c1 INT); 847SHOW TABLES; 848ALTER TABLE `#sql1` RENAME `@0023sql1`; 849ALTER TABLE `@0023sql2` RENAME `#sql2`; 850SHOW TABLES; 851INSERT INTO `#sql2` VALUES (1); 852INSERT INTO `@0023sql1` VALUES (2); 853SHOW CREATE TABLE `#sql2`; 854SHOW CREATE TABLE `@0023sql1`; 855DROP TABLE `#sql2`, `@0023sql1`; 856 857# 858# Bug #22369: Alter table rename combined with other alterations causes lost tables 859# 860# This problem happens if the data change is compatible. 861# Changing to the same type is compatible for example. 862# 863--enable_warnings 864CREATE TABLE t1 ( 865 int_field INTEGER UNSIGNED NOT NULL, 866 char_field CHAR(10), 867 INDEX(`int_field`) 868); 869 870DESCRIBE t1; 871 872SHOW INDEXES FROM t1; 873 874INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); 875--echo "Non-copy data change - new frm, but old data and index files" 876ALTER TABLE t1 877 CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL, 878 RENAME t2; 879 880--error ER_NO_SUCH_TABLE 881SELECT * FROM t1 ORDER BY int_field; 882SELECT * FROM t2 ORDER BY unsigned_int_field; 883DESCRIBE t2; 884DESCRIBE t2; 885ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL; 886DESCRIBE t2; 887 888DROP TABLE t2; 889 890# 891# Bug#28427: Columns were renamed instead of moving by ALTER TABLE. 892# 893CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); 894INSERT INTO t1 VALUES (1, 2, NULL); 895SELECT * FROM t1; 896ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1; 897SELECT * FROM t1; 898ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2; 899SELECT * FROM t1; 900DROP TABLE t1; 901 902# 903# BUG#29957 - alter_table.test fails 904# 905create table t1 (c char(10) default "Two"); 906lock table t1 write; 907insert into t1 values (); 908alter table t1 modify c char(10) default "Three"; 909unlock tables; 910select * from t1; 911check table t1; 912drop table t1; 913 914# 915# Bug#33873: Fast ALTER TABLE doesn't work with multibyte character sets 916# 917 918CREATE TABLE t1 (id int, c int) character set latin1; 919INSERT INTO t1 VALUES (1,1); 920--enable_info 921ALTER TABLE t1 CHANGE c d int; 922ALTER TABLE t1 CHANGE d c int; 923ALTER TABLE t1 MODIFY c VARCHAR(10); 924ALTER TABLE t1 CHANGE c d varchar(10); 925ALTER TABLE t1 CHANGE d c varchar(10); 926--disable_info 927DROP TABLE t1; 928 929CREATE TABLE t1 (id int, c int) character set utf8; 930INSERT INTO t1 VALUES (1,1); 931--enable_info 932ALTER TABLE t1 CHANGE c d int; 933ALTER TABLE t1 CHANGE d c int; 934ALTER TABLE t1 MODIFY c VARCHAR(10); 935ALTER TABLE t1 CHANGE c d varchar(10); 936ALTER TABLE t1 CHANGE d c varchar(10); 937--disable_info 938DROP TABLE t1; 939 940# 941# pack_keys and max_data_length options are meant for Myisam tables 942# Bug#39372 "Smart" ALTER TABLE not so smart after all. 943# 944create table t1(f1 int not null, f2 int not null, key (f1), key (f2)) engine=myisam; 945let $count= 50; 946--disable_query_log 947while ($count) 948{ 949 EVAL insert into t1 values (1,1),(1,1),(1,1),(1,1),(1,1); 950 EVAL insert into t1 values (2,2),(2,2),(2,2),(2,2),(2,2); 951 dec $count ; 952} 953--enable_query_log 954 955select index_length into @unpaked_keys_size from 956information_schema.tables where table_name='t1'; 957alter table t1 pack_keys=1; 958select index_length into @paked_keys_size from 959information_schema.tables where table_name='t1'; 960select (@unpaked_keys_size > @paked_keys_size); 961 962select max_data_length into @orig_max_data_length from 963information_schema.tables where table_name='t1'; 964alter table t1 max_rows=100; 965select max_data_length into @changed_max_data_length from 966information_schema.tables where table_name='t1'; 967select (@orig_max_data_length > @changed_max_data_length); 968 969drop table t1; 970 971# 972# Bug #23113: Different behavior on altering ENUM fields between 5.0 and 5.1 973# 974CREATE TABLE t1(a INT AUTO_INCREMENT PRIMARY KEY, 975 b ENUM('a', 'b', 'c') NOT NULL); 976INSERT INTO t1 (b) VALUES ('a'), ('c'), ('b'), ('b'), ('a'); 977ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL; 978SELECT * FROM t1; 979DROP TABLE t1; 980 981--echo # 982--echo # Bug#45567: Fast ALTER TABLE broken for enum and set 983--echo # 984 985CREATE TABLE t1 (a ENUM('a1','a2')); 986INSERT INTO t1 VALUES ('a1'),('a2'); 987--enable_info 988--echo # No copy: No modification 989ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); 990--echo # No copy: Add new enumeration to the end 991ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); 992--echo # Copy: Modify and add new to the end 993ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); 994--echo # Copy: Remove from the end 995ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); 996--echo # Copy: Add new enumeration 997ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); 998--echo # No copy: Add new enumerations to the end 999ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); 1000--disable_info 1001DROP TABLE t1; 1002 1003CREATE TABLE t1 (a SET('a1','a2')); 1004INSERT INTO t1 VALUES ('a1'),('a2'); 1005--enable_info 1006--echo # No copy: No modification 1007ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); 1008--echo # No copy: Add new to the end 1009ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); 1010--echo # Copy: Modify and add new to the end 1011ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); 1012--echo # Copy: Remove from the end 1013ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); 1014--echo # Copy: Add new member 1015ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); 1016--echo # No copy: Add new to the end 1017ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); 1018--echo # Copy: Numerical increase (pack length) 1019ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); 1020--disable_info 1021DROP TABLE t1; 1022 1023# 1024# Bug#43508: Renaming timestamp or date column triggers table copy 1025# 1026 1027CREATE TABLE t1 (f1 TIMESTAMP NULL DEFAULT NULL, 1028 f2 INT(11) DEFAULT NULL) ENGINE=MYISAM DEFAULT CHARSET=utf8; 1029 1030INSERT INTO t1 VALUES (NULL, NULL), ("2009-10-09 11:46:19", 2); 1031 1032--echo this should affect no rows as there is no real change 1033--enable_info 1034ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL; 1035--disable_info 1036DROP TABLE t1; 1037 1038 1039--echo # 1040--echo # Bug #31145: ALTER TABLE DROP COLUMN, ADD COLUMN crashes (linux) 1041--echo # or freezes (win) the server 1042--echo # 1043 1044CREATE TABLE t1 (a TEXT, id INT, b INT); 1045ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST; 1046 1047DROP TABLE t1; 1048 1049 1050--echo # 1051--echo # Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION 1052--echo # FIRST CAN CAUSE DATA TO BE CORRUPTED". 1053--echo # 1054--echo # Use MyISAM engine as the fact that InnoDB doesn't support 1055--echo # in-place ALTER TABLE in cases when columns are being renamed 1056--echo # hides some bugs. 1057create table t1 (i int, j int) engine=myisam; 1058insert into t1 value (1, 2); 1059--echo # First, test for original problem described in the bug report. 1060select * from t1; 1061--echo # Change of column order by the below ALTER TABLE statement should 1062--echo # affect both column names and column contents. 1063alter table t1 modify column j int first; 1064select * from t1; 1065--echo # Now test for similar problem with the same root. 1066--echo # The below ALTER TABLE should change not only the name but 1067--echo # also the value for the last column of the table. 1068alter table t1 drop column i, add column k int default 0; 1069select * from t1; 1070--echo # Clean-up. 1071drop table t1; 1072 1073 1074--echo End of 5.1 tests 1075 1076# 1077# Bug #31031 ALTER TABLE regression in 5.0 1078# 1079# The ALTER TABLE operation failed with 1080# ERROR 1089 (HY000): Incorrect sub part key; ... 1081# 1082CREATE TABLE t1(c CHAR(10), 1083 i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY); 1084INSERT INTO t1 VALUES('a',2),('b',4),('c',6); 1085ALTER TABLE t1 1086 DROP i, 1087 ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT, 1088 AUTO_INCREMENT = 1; 1089DROP TABLE t1; 1090 1091 1092# 1093# Bug#50542 5.5.x doesn't check length of key prefixes: 1094# corruption and crash results 1095# 1096# This case is related to Bug#31031 (above) 1097# A statement where the index key is larger/wider than 1098# the column type, should cause an error 1099# 1100--error ER_WRONG_SUB_KEY 1101CREATE TABLE t1 (a CHAR(1), PRIMARY KEY (a(255))); 1102 1103# Test other variants of creating indices 1104CREATE TABLE t1 (a CHAR(1)); 1105# ALTER TABLE 1106--error ER_WRONG_SUB_KEY 1107ALTER TABLE t1 ADD PRIMARY KEY (a(20)); 1108--error ER_WRONG_SUB_KEY 1109ALTER TABLE t1 ADD KEY (a(20)); 1110# CREATE INDEX 1111--error ER_WRONG_SUB_KEY 1112CREATE UNIQUE INDEX i1 ON t1 (a(20)); 1113--error ER_WRONG_SUB_KEY 1114CREATE INDEX i2 ON t1 (a(20)); 1115# cleanup 1116DROP TABLE t1; 1117 1118 1119# 1120# Bug #45052 ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns 1121# The alter table fails if 2 or more new fields added and 1122# also added a key with these fields 1123# 1124CREATE TABLE t1 (id int); 1125INSERT INTO t1 VALUES (1), (2); 1126ALTER TABLE t1 ADD COLUMN (f1 INT), ADD COLUMN (f2 INT), ADD KEY f2k(f2); 1127DROP TABLE t1; 1128 1129 1130--echo # 1131--echo # Test for bug #53820 "ALTER a MEDIUMINT column table causes full 1132--echo # table copy". 1133--echo # 1134CREATE TABLE t1 (a INT, b MEDIUMINT); 1135INSERT INTO t1 VALUES (1, 1), (2, 2); 1136--echo # The below ALTER should not copy table and so no rows should 1137--echo # be shown as affected. 1138--enable_info 1139ALTER TABLE t1 CHANGE a id INT; 1140--disable_info 1141DROP TABLE t1; 1142 1143 1144--echo # 1145--echo # Bug#11754461 CANNOT ALTER TABLE WHEN KEY PREFIX TOO LONG 1146--echo # 1147 1148CREATE DATABASE db1 CHARACTER SET utf8; 1149--error ER_TOO_LONG_KEY 1150CREATE TABLE db1.t1 (bar TINYTEXT, KEY (bar(100))); 1151CREATE TABLE db1.t1 (bar TINYTEXT, KEY (bar(85))); 1152ALTER TABLE db1.t1 ADD baz INT; 1153 1154DROP DATABASE db1; 1155 1156 1157--echo # Additional coverage for refactoring which is made as part 1158--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege 1159--echo # to allow temp table operations". 1160--echo # 1161--echo # At some point the below test case failed on assertion. 1162 1163CREATE TEMPORARY TABLE t1 (i int) ENGINE=MyISAM; 1164 1165--error ER_ILLEGAL_HA 1166ALTER TABLE t1 DISCARD TABLESPACE; 1167 1168DROP TABLE t1; 1169 1170 1171--echo # 1172--echo # Bug#11938039 RE-EXECUTION OF FRM-ONLY ALTER TABLE WITH RENAME 1173--echo # CLAUSE FAILS OR ABORTS SERVER. 1174--echo # 1175create table t1 (a int); 1176prepare stmt1 from 'alter table t1 alter column a set default 1, rename to t2'; 1177execute stmt1; 1178rename table t2 to t1; 1179--echo # The below statement should succeed and not emit error or abort server. 1180execute stmt1; 1181deallocate prepare stmt1; 1182drop table t2; 1183 1184 1185--echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't 1186--echo # identify correct column name. 1187--echo # 1188 1189CREATE TABLE t1 (c1 int unsigned , c2 char(100) not null default ''); 1190ALTER TABLE t1 ADD c3 char(16) NOT NULL DEFAULT '' AFTER c2, 1191 MODIFY c2 char(100) NOT NULL DEFAULT '' AFTER c1; 1192SHOW CREATE TABLE t1; 1193DROP TABLE t1; 1194 1195 1196--echo # 1197--echo # WL#5534 Online ALTER, Phase 1 1198--echo # 1199 1200--echo # Single thread tests. 1201--echo # See innodb_mysql_sync.test for multi thread tests. 1202 1203CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; 1204CREATE TABLE m1(a INT PRIMARY KEY, b INT) engine=MyISAM; 1205INSERT INTO t1 VALUES (1,1), (2,2); 1206INSERT INTO m1 VALUES (1,1), (2,2); 1207 1208--echo # 1209--echo # 1: Test ALGORITHM keyword 1210--echo # 1211 1212--echo # --enable_info allows us to see how many rows were updated 1213--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. 1214 1215--enable_info 1216ALTER TABLE t1 ADD INDEX i1(b); 1217ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; 1218ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; 1219ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; 1220--error ER_UNKNOWN_ALTER_ALGORITHM 1221ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= INVALID; 1222 1223ALTER TABLE m1 ENABLE KEYS; 1224ALTER TABLE m1 ENABLE KEYS, ALGORITHM= DEFAULT; 1225ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY; 1226ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE; 1227--disable_info 1228 1229ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; 1230 1231--echo # 1232--echo # 2: Test ALGORITHM + old_alter_table 1233--echo # 1234 1235--enable_info 1236SET SESSION old_alter_table= 1; 1237ALTER TABLE t1 ADD INDEX i1(b); 1238ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; 1239ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; 1240ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; 1241SET SESSION old_alter_table= 0; 1242--disable_info 1243 1244ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; 1245 1246--echo # 1247--echo # 3: Test unsupported in-place operation 1248--echo # 1249 1250ALTER TABLE t1 ADD COLUMN (c1 INT); 1251ALTER TABLE t1 ADD COLUMN (c2 INT), ALGORITHM= DEFAULT; 1252ALTER TABLE t1 ADD COLUMN (c3 INT), ALGORITHM= COPY; 1253ALTER TABLE t1 ADD COLUMN (c4 INT), ALGORITHM= INPLACE; 1254 1255ALTER TABLE t1 DROP COLUMN c1, DROP COLUMN c2, DROP COLUMN c3, DROP COLUMN c4; 1256 1257--echo # 1258--echo # 4: Test LOCK keyword 1259--echo # 1260 1261--enable_info 1262ALTER TABLE t1 ADD INDEX i1(b), LOCK= DEFAULT; 1263ALTER TABLE t1 ADD INDEX i2(b), LOCK= NONE; 1264ALTER TABLE t1 ADD INDEX i3(b), LOCK= SHARED; 1265ALTER TABLE t1 ADD INDEX i4(b), LOCK= EXCLUSIVE; 1266--error ER_UNKNOWN_ALTER_LOCK 1267ALTER TABLE t1 ADD INDEX i5(b), LOCK= INVALID; 1268--disable_info 1269 1270ALTER TABLE m1 ENABLE KEYS, LOCK= DEFAULT; 1271--error ER_ALTER_OPERATION_NOT_SUPPORTED 1272ALTER TABLE m1 ENABLE KEYS, LOCK= NONE; 1273--error ER_ALTER_OPERATION_NOT_SUPPORTED 1274ALTER TABLE m1 ENABLE KEYS, LOCK= SHARED; 1275ALTER TABLE m1 ENABLE KEYS, LOCK= EXCLUSIVE; 1276 1277ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; 1278 1279--echo # 1280--echo # 5: Test ALGORITHM + LOCK 1281--echo # 1282 1283--enable_info 1284ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE; 1285ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED; 1286ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; 1287--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 1288ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; 1289ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; 1290ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE; 1291 1292--error ER_ALTER_OPERATION_NOT_SUPPORTED 1293ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE; 1294--error ER_ALTER_OPERATION_NOT_SUPPORTED 1295ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; 1296ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; 1297--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 1298ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; 1299# This works because the lock will be SNW for the copy phase. 1300# It will still require exclusive lock for actually enabling keys. 1301ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED; 1302ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= EXCLUSIVE; 1303--disable_info 1304 1305DROP TABLE t1, m1; 1306 1307# Disable/Enable keys supported by Myisam only 1308--echo # 1309--echo # 6: Possible deadlock involving thr_lock.c 1310--echo # 1311 1312CREATE TABLE t1(a INT PRIMARY KEY, b INT) ENGINE=MyISAM; 1313INSERT INTO t1 VALUES (1,1), (2,2); 1314 1315START TRANSACTION; 1316INSERT INTO t1 VALUES (3,3); 1317 1318--echo # Connection con1 1319connect (con1, localhost, root); 1320--echo # Sending: 1321--send ALTER TABLE t1 DISABLE KEYS 1322 1323--echo # Connection default 1324connection default; 1325--echo # Waiting until ALTER TABLE is blocked. 1326let $wait_condition= 1327 SELECT COUNT(*) = 1 FROM information_schema.processlist 1328 WHERE state = "Waiting for table metadata lock" AND 1329 info = "ALTER TABLE t1 DISABLE KEYS"; 1330--source include/wait_condition.inc 1331UPDATE t1 SET b = 4; 1332COMMIT; 1333 1334--echo # Connection con1 1335connection con1; 1336--echo # Reaping: ALTER TABLE t1 DISABLE KEYS 1337--reap 1338disconnect con1; 1339--source include/wait_until_disconnected.inc 1340 1341--echo # Connection default 1342connection default; 1343DROP TABLE t1; 1344 1345--echo # 1346--echo # 7: Which operations require copy and which can be done in-place? 1347--echo # 1348--echo # Test which ALTER TABLE operations are done in-place and 1349--echo # which operations are done using temporary table copy. 1350--echo # 1351--echo # --enable_info allows us to see how many rows were updated 1352--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. 1353--echo # 1354 1355--echo # Single operation tests 1356 1357CREATE TABLE ti1(a INT NOT NULL, b INT, c INT) engine=InnoDB; 1358CREATE TABLE tm1(a INT NOT NULL, b INT, c INT) engine=MyISAM; 1359CREATE TABLE ti2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=InnoDB; 1360CREATE TABLE tm2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=MyISAM; 1361INSERT INTO ti1 VALUES (1,1,1), (2,2,2); 1362INSERT INTO ti2 VALUES (1,1,1), (2,2,2); 1363INSERT INTO tm1 VALUES (1,1,1), (2,2,2); 1364INSERT INTO tm2 VALUES (1,1,1), (2,2,2); 1365 1366--enable_info 1367ALTER TABLE ti1; 1368ALTER TABLE tm1; 1369 1370ALTER TABLE ti1 ADD COLUMN d VARCHAR(200); 1371ALTER TABLE tm1 ADD COLUMN d VARCHAR(200); 1372ALTER TABLE ti1 ADD COLUMN d2 VARCHAR(200); 1373ALTER TABLE tm1 ADD COLUMN d2 VARCHAR(200); 1374ALTER TABLE ti1 ADD COLUMN e ENUM('a', 'b') FIRST; 1375ALTER TABLE tm1 ADD COLUMN e ENUM('a', 'b') FIRST; 1376ALTER TABLE ti1 ADD COLUMN f INT AFTER a; 1377ALTER TABLE tm1 ADD COLUMN f INT AFTER a; 1378 1379ALTER TABLE ti1 ADD INDEX ii1(b); 1380ALTER TABLE tm1 ADD INDEX im1(b); 1381ALTER TABLE ti1 ADD UNIQUE INDEX ii2 (c); 1382ALTER TABLE tm1 ADD UNIQUE INDEX im2 (c); 1383ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d); 1384ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d); 1385ALTER TABLE ti1 ADD FULLTEXT INDEX ii4 (d2); 1386ALTER TABLE tm1 ADD FULLTEXT INDEX im4 (d2); 1387 1388# Bug#14140038 INCONSISTENT HANDLING OF FULLTEXT INDEXES IN ALTER TABLE 1389--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 1390ALTER TABLE ti1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; 1391ALTER TABLE ti1 ADD PRIMARY KEY(a); 1392ALTER TABLE tm1 ADD PRIMARY KEY(a); 1393 1394ALTER TABLE ti1 DROP INDEX ii3; 1395ALTER TABLE tm1 DROP INDEX im3; 1396 1397ALTER TABLE ti1 DROP COLUMN d2; 1398ALTER TABLE tm1 DROP COLUMN d2; 1399 1400ALTER TABLE ti1 ADD CONSTRAINT fi1 FOREIGN KEY (b) REFERENCES ti2(a); 1401ALTER TABLE tm1 ADD CONSTRAINT fm1 FOREIGN KEY (b) REFERENCES tm2(a); 1402 1403ALTER TABLE ti1 ALTER COLUMN b SET DEFAULT 1; 1404ALTER TABLE tm1 ALTER COLUMN b SET DEFAULT 1; 1405ALTER TABLE ti1 ALTER COLUMN b DROP DEFAULT; 1406ALTER TABLE tm1 ALTER COLUMN b DROP DEFAULT; 1407 1408# This will set both ALTER_COLUMN_NAME and COLUMN_DEFAULT_VALUE 1409ALTER TABLE ti1 CHANGE COLUMN f g INT; 1410ALTER TABLE tm1 CHANGE COLUMN f g INT; 1411ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20); 1412ALTER TABLE tm1 CHANGE COLUMN g h VARCHAR(20); 1413ALTER TABLE ti1 MODIFY COLUMN e ENUM('a', 'b', 'c'); 1414ALTER TABLE tm1 MODIFY COLUMN e ENUM('a', 'b', 'c'); 1415ALTER TABLE ti1 MODIFY COLUMN e INT; 1416ALTER TABLE tm1 MODIFY COLUMN e INT; 1417# This will set both ALTER_COLUMN_ORDER and COLUMN_DEFAULT_VALUE 1418ALTER TABLE ti1 MODIFY COLUMN e INT AFTER h; 1419ALTER TABLE tm1 MODIFY COLUMN e INT AFTER h; 1420ALTER TABLE ti1 MODIFY COLUMN e INT FIRST; 1421ALTER TABLE tm1 MODIFY COLUMN e INT FIRST; 1422# This will set both ALTER_COLUMN_NOT_NULLABLE and COLUMN_DEFAULT_VALUE 1423ALTER TABLE ti1 MODIFY COLUMN c INT NOT NULL; 1424ALTER TABLE tm1 MODIFY COLUMN c INT NOT NULL; 1425# This will set both ALTER_COLUMN_NULLABLE and COLUMN_DEFAULT_VALUE 1426ALTER TABLE ti1 MODIFY COLUMN c INT NULL; 1427ALTER TABLE tm1 MODIFY COLUMN c INT NULL; 1428# This will set both ALTER_COLUMN_EQUAL_PACK_LENGTH and COLUMN_DEFAULT_VALUE 1429ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30); 1430ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30); 1431ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30) AFTER d; 1432ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30) AFTER d; 1433 1434ALTER TABLE ti1 DROP COLUMN h; 1435ALTER TABLE tm1 DROP COLUMN h; 1436 1437ALTER TABLE ti1 DROP INDEX ii2; 1438ALTER TABLE tm1 DROP INDEX im2; 1439ALTER TABLE ti1 DROP PRIMARY KEY; 1440ALTER TABLE tm1 DROP PRIMARY KEY; 1441 1442ALTER TABLE ti1 DROP FOREIGN KEY fi1; 1443ALTER TABLE tm1 DROP FOREIGN KEY fm1; 1444 1445ALTER TABLE ti1 RENAME TO ti3; 1446ALTER TABLE tm1 RENAME TO tm3; 1447ALTER TABLE ti3 RENAME TO ti1; 1448ALTER TABLE tm3 RENAME TO tm1; 1449 1450ALTER TABLE ti1 ORDER BY b; 1451ALTER TABLE tm1 ORDER BY b; 1452 1453ALTER TABLE ti1 CONVERT TO CHARACTER SET utf16; 1454ALTER TABLE tm1 CONVERT TO CHARACTER SET utf16; 1455ALTER TABLE ti1 DEFAULT CHARACTER SET utf8; 1456ALTER TABLE tm1 DEFAULT CHARACTER SET utf8; 1457 1458ALTER TABLE ti1 FORCE; 1459ALTER TABLE tm1 FORCE; 1460 1461ALTER TABLE ti1 AUTO_INCREMENT 3; 1462ALTER TABLE tm1 AUTO_INCREMENT 3; 1463ALTER TABLE ti1 AVG_ROW_LENGTH 10; 1464ALTER TABLE tm1 AVG_ROW_LENGTH 10; 1465ALTER TABLE ti1 CHECKSUM 1; 1466ALTER TABLE tm1 CHECKSUM 1; 1467ALTER TABLE ti1 COMMENT 'test'; 1468ALTER TABLE tm1 COMMENT 'test'; 1469ALTER TABLE ti1 MAX_ROWS 100; 1470ALTER TABLE tm1 MAX_ROWS 100; 1471ALTER TABLE ti1 MIN_ROWS 1; 1472ALTER TABLE tm1 MIN_ROWS 1; 1473ALTER TABLE ti1 PACK_KEYS 1; 1474ALTER TABLE tm1 PACK_KEYS 1; 1475 1476--disable_info 1477DROP TABLE ti1, ti2, tm1, tm2; 1478 1479--echo # Tests of >1 operation (InnoDB) 1480 1481CREATE TABLE ti1(a INT PRIMARY KEY AUTO_INCREMENT, b INT) engine=InnoDB; 1482INSERT INTO ti1(b) VALUES (1), (2); 1483 1484--enable_info 1485ALTER TABLE ti1 RENAME TO ti3, ADD INDEX ii1(b); 1486 1487ALTER TABLE ti3 DROP INDEX ii1, AUTO_INCREMENT 5; 1488--disable_info 1489INSERT INTO ti3(b) VALUES (5); 1490--enable_info 1491ALTER TABLE ti3 ADD INDEX ii1(b), AUTO_INCREMENT 7; 1492--disable_info 1493INSERT INTO ti3(b) VALUES (7); 1494SELECT * FROM ti3; 1495 1496DROP TABLE ti3; 1497 1498--echo # 1499--echo # 8: Scenario in which ALTER TABLE was returning an unwarranted 1500--echo # ER_ILLEGAL_HA error at some point during work on this WL. 1501--echo # 1502 1503CREATE TABLE tm1(i INT DEFAULT 1) engine=MyISAM; 1504ALTER TABLE tm1 ADD INDEX ii1(i), ALTER COLUMN i DROP DEFAULT; 1505DROP TABLE tm1; 1506 1507--echo # 1508--echo # Bug#11815557 60269: MYSQL SHOULD REJECT ATTEMPTS TO CREATE SYSTEM 1509--echo # TABLES IN INCORRECT ENGINE 1510--echo # 1511--echo # Note: This test assumes that only MyISAM and InnoDB supports system tables. 1512--echo # If other engines are made to support system tables, 1513--echo # then this test needs to be updated 1514--echo # 1515 1516use mysql; 1517--error ER_UNSUPPORTED_ENGINE 1518ALTER TABLE db ENGINE=memory; 1519--error ER_UNSUPPORTED_ENGINE 1520ALTER TABLE user ENGINE=memory; 1521--error ER_UNSUPPORTED_ENGINE 1522ALTER TABLE func ENGINE=csv; 1523--error ER_UNSUPPORTED_ENGINE 1524ALTER TABLE servers ENGINE=merge; 1525--error ER_UNSUPPORTED_ENGINE 1526ALTER TABLE procs_priv ENGINE=memory; 1527--error ER_UNSUPPORTED_ENGINE 1528ALTER TABLE tables_priv ENGINE=heap; 1529--error ER_UNSUPPORTED_ENGINE 1530ALTER TABLE columns_priv ENGINE=csv; 1531--error ER_UNSUPPORTED_ENGINE 1532ALTER TABLE time_zone ENGINE=merge; 1533--error ER_UNSUPPORTED_ENGINE 1534ALTER TABLE help_topic ENGINE=merge; 1535--error ER_UNSUPPORTED_ENGINE 1536CREATE TABLE db (dummy int) ENGINE=memory; 1537--error ER_UNSUPPORTED_ENGINE 1538CREATE TABLE user (dummy int) ENGINE=memory; 1539--error ER_UNSUPPORTED_ENGINE 1540CREATE TABLE func (dummy int) ENGINE=memory; 1541--error ER_UNSUPPORTED_ENGINE 1542CREATE TABLE servers (dummy int) ENGINE=merge; 1543--error ER_UNSUPPORTED_ENGINE 1544CREATE TABLE procs_priv (dummy int) ENGINE=memory; 1545--error ER_UNSUPPORTED_ENGINE 1546CREATE TABLE tables_priv (dummy int) ENGINE=heap; 1547--error ER_UNSUPPORTED_ENGINE 1548CREATE TABLE columns_priv (dummy int) ENGINE=memory; 1549--error ER_UNSUPPORTED_ENGINE 1550CREATE TABLE time_zone (dummy int) ENGINE=merge; 1551--error ER_UNSUPPORTED_ENGINE 1552CREATE TABLE help_topic (dummy int) ENGINE=merge; 1553use test; 1554--echo # End of Bug#11815557 1555 1556 1557--echo # 1558--echo # Tests for WL#6555 "Online rename index". 1559--echo # 1560 1561--echo # 1562--echo # 1) Tests for syntax and semantics of ALTER TABLE RENAME 1563--echo # KEY/INDEX result. 1564--echo # 1565--echo # 1.a) Both RENAME KEY and RENAME INDEX variants should be 1566--echo # allowed and produce expected results. 1567create table t1 (pk int primary key, i int, j int, key a(i)); 1568alter table t1 rename key a to b; 1569show create table t1; 1570alter table t1 rename index b to c; 1571show create table t1; 1572 1573--echo # 1.b) It should be impossible to rename index that doesn't 1574--echo # exists, dropped or added within the same ALTER TABLE. 1575--error ER_KEY_DOES_NOT_EXITS 1576alter table t1 rename key d to e; 1577show create table t1; 1578--error ER_KEY_DOES_NOT_EXITS 1579alter table t1 drop key c, rename key c to d; 1580show create table t1; 1581--error ER_KEY_DOES_NOT_EXITS 1582alter table t1 add key d(j), rename key d to e; 1583show create table t1; 1584 1585--echo # 1.c) It should be impossible to rename index to a name 1586--echo # which is already used by another index, or is used 1587--echo # by index which is added within the same ALTER TABLE. 1588alter table t1 add key d(j); 1589--error ER_DUP_KEYNAME 1590alter table t1 rename key c to d; 1591show create table t1; 1592alter table t1 drop key d; 1593--error ER_DUP_KEYNAME 1594alter table t1 add key d(j), rename key c to d; 1595show create table t1; 1596 1597--echo # 1.d) It should be possible to rename index to a name 1598--echo # which belongs to index which is dropped within the 1599--echo # same ALTER TABLE. 1600alter table t1 add key d(j); 1601alter table t1 drop key c, rename key d to c; 1602show create table t1; 1603 1604--echo # 1.e) We disallow renaming from/to PRIMARY as it might 1605--echo # lead to some other key becoming "primary" internally, 1606--echo # which will be interpreted as dropping/addition of 1607--echo # primary key. 1608--error ER_PARSE_ERROR 1609alter table t1 rename key primary to d; 1610show create table t1; 1611--echo # Even using 'funny' syntax. 1612--error ER_WRONG_NAME_FOR_INDEX 1613alter table t1 rename key `primary` to d; 1614show create table t1; 1615--error ER_PARSE_ERROR 1616alter table t1 rename key c to primary; 1617show create table t1; 1618--error ER_WRONG_NAME_FOR_INDEX 1619alter table t1 rename key c to `primary`; 1620show create table t1; 1621drop table t1; 1622 1623 1624--echo # 1625--echo # 2) More complex tests for semantics of ALTER TABLE. 1626--echo # 1627--echo # 2.a) Check that standalone RENAME KEY works as expected 1628--echo # for unique and non-unique indexes. 1629create table t1 (a int, unique u(a), b int, key k(b)); 1630alter table t1 rename key u to uu; 1631show create table t1; 1632alter table t1 rename key k to kk; 1633show create table t1; 1634 1635--echo # 2.b) Check how that this clause can be mixed with other 1636--echo # clauses which don't affect key or its columns. 1637alter table t1 rename key kk to kkk, add column c int; 1638show create table t1; 1639alter table t1 rename key uu to uuu, add key c(c); 1640show create table t1; 1641alter table t1 rename key kkk to k, drop key uuu; 1642show create table t1; 1643alter table t1 rename key k to kk, rename to t2; 1644show create table t2; 1645 1646--echo # 2.c) Check that this clause properly works even in case 1647--echo # when it is mixed with clauses affecting columns in 1648--echo # the key renamed. 1649alter table t2 rename key c to cc, modify column c bigint not null first; 1650show create table t2; 1651--echo # Create multi-component key for next example. 1652alter table t2 add unique u (a, b, c); 1653show create table t2; 1654alter table t2 rename key u to uu, drop column b; 1655show create table t2; 1656drop table t2; 1657 1658 1659--echo # 1660--echo # 3) Test coverage for handling of RENAME INDEX clause in 1661--echo # various storage engines and using different ALTER 1662--echo # algorithm. 1663--echo # 1664--echo # 3.a) Test coverage for simple storage engines (MyISAM/Heap). 1665create table t1 (i int, key k(i)) engine=myisam; 1666insert into t1 values (1); 1667create table t2 (i int, key k(i)) engine=memory; 1668insert into t2 values (1); 1669--echo # MyISAM and Heap should be able to handle key renaming in-place. 1670alter table t1 algorithm=inplace, rename key k to kk; 1671alter table t2 algorithm=inplace, rename key k to kk; 1672show create table t1; 1673show create table t2; 1674--echo # So by default in-place algorithm should be chosen. 1675--echo # (ALTER TABLE should report 0 rows affected). 1676--enable_info 1677alter table t1 rename key kk to kkk; 1678alter table t2 rename key kk to kkk; 1679--disable_info 1680show create table t1; 1681show create table t2; 1682--echo # Copy algorithm should work as well. 1683alter table t1 algorithm=copy, rename key kkk to kkkk; 1684alter table t2 algorithm=copy, rename key kkk to kkkk; 1685show create table t1; 1686show create table t2; 1687--echo # When renaming is combined with other in-place operation 1688--echo # it still works as expected (i.e. works in-place). 1689alter table t1 algorithm=inplace, rename key kkkk to k, alter column i set default 100; 1690alter table t2 algorithm=inplace, rename key kkkk to k, alter column i set default 100; 1691show create table t1; 1692show create table t2; 1693--echo # Combining with non-inplace operation results in the whole ALTER 1694--echo # becoming non-inplace. 1695--error ER_ALTER_OPERATION_NOT_SUPPORTED 1696alter table t1 algorithm=inplace, rename key k to kk, add column j int; 1697--error ER_ALTER_OPERATION_NOT_SUPPORTED 1698alter table t2 algorithm=inplace, rename key k to kk, add column j int; 1699drop table t1, t2; 1700 1701--echo # 3.b) Basic tests for InnoDB. More tests can be found in 1702--echo # innodb.innodb_rename_index* 1703create table t1 (i int, key k(i)) engine=innodb; 1704insert into t1 values (1); 1705--echo # Basic rename, inplace algorithm should be chosen 1706--enable_info 1707alter table t1 algorithm=inplace, rename key k to kk; 1708--disable_info 1709show create table t1; 1710--echo # copy algorithm should work as well. 1711--enable_info 1712alter table t1 algorithm=copy, rename key kk to kkk; 1713--disable_info 1714show create table t1; 1715drop table t1; 1716 1717--echo # 1718--echo # 4) Additional coverage for complex cases in which code 1719--echo # in ALTER TABLE comparing old and new table version 1720--echo # got confused. 1721--echo # 1722--echo # Once InnoDB starts to support in-place index renaming the result 1723--echo # of below statements should stay the same. Information about 1724--echo # indexes returned by SHOW CREATE TABLE (from .FRM) and by 1725--echo # InnoDB (from InnoDB data-dictionary) should be consistent. 1726--echo # 1727create table t1 ( a int, b int, c int, d int, 1728 primary key (a), index i1 (b), index i2 (c) ) engine=innodb; 1729alter table t1 add index i1 (d), rename index i1 to x; 1730show create table t1; 1731select i.name as k, f.name as c from information_schema.innodb_tables as t, 1732 information_schema.innodb_indexes as i, 1733 information_schema.innodb_fields as f 1734where t.name='test/t1' and t.table_id = i.table_id and i.index_id = f.index_id 1735order by k, c; 1736drop table t1; 1737create table t1 (a int, b int, c int, d int, 1738 primary key (a), index i1 (b), index i2 (c)) engine=innodb; 1739alter table t1 add index i1 (d), rename index i1 to i2, drop index i2; 1740show create table t1; 1741select i.name as k, f.name as c from information_schema.innodb_tables as t, 1742 information_schema.innodb_indexes as i, 1743 information_schema.innodb_fields as f 1744where t.name='test/t1' and t.table_id = i.table_id and i.index_id = f.index_id 1745order by k, c; 1746drop table t1; 1747--echo # 1748--echo # The below ALTER TABLE statement should either drop and recreate key 1749--echo # under new name, or simply rename it. It should not bring .FRM and 1750--echo # InnoDB data-dictionary out of sync thus causing asserts. 1751--echo # 1752create table t1 (i int, key x(i)) engine=InnoDB; 1753alter table t1 drop key x, add key X(i), alter column i set default 10; 1754drop table t1; 1755 1756 1757--echo # 1758--echo # Coverage for changes to ALTER TABLE ... IMPORT/DISCARD TABLESPACE 1759--echo # code introduced by WL#6671 "Improve scalability by not using 1760--echo # thr_lock.c locks for InnoDB tables". 1761--echo # 1762--echo # Check that ALTER TABLE ... IMPORT/DISCARD TABLESPACE acquires X 1763--echo # metadata lock on the table even when executed under LOCK TABLES. 1764--echo # 1765 1766--enable_connect_log 1767--echo # Suppress error messages which will be generated by IMPORT TABLESPACE 1768call mtr.add_suppression("Trying to import a tablespace, but could not open the tablespace file"); 1769call mtr.add_suppression("Operating system error number 2 in a file operation."); 1770call mtr.add_suppression("The error means the system cannot find the path specified."); 1771 1772CREATE TABLE t1 (i INT) ENGINE=InnoDB; 1773 1774--echo # 1775--echo # 1) Check that ALTER TABLE ... IMPORT/DISCARD TABLESPACE on 1776--echo # base table acquire X lock (when not under LOCK TABLES). 1777--echo # 1778 1779connect(con1, localhost, root); 1780--echo # Acquire S lock on table 't1'. 1781HANDLER t1 OPEN; 1782 1783connect(con2, localhost, root); 1784--echo # Sending: 1785--send ALTER TABLE t1 DISCARD TABLESPACE 1786 1787connection default; 1788--echo # Wait until ALTER TABLE is blocked since it tries to acquire X lock. 1789let $wait_condition= 1790 SELECT COUNT(*) = 1 FROM information_schema.processlist 1791 WHERE state = "Waiting for table metadata lock" AND 1792 info = "ALTER TABLE t1 DISCARD TABLESPACE"; 1793--source include/wait_condition.inc 1794 1795connection con1; 1796--echo # Unblock ALTER TABLE DISCARD 1797HANDLER t1 CLOSE; 1798 1799connection con2; 1800--echo # Reaping ALTER TABLE DISCARD 1801--reap 1802 1803connection con1; 1804--echo # Acquire S lock on table 't1'. 1805HANDLER t1 OPEN; 1806 1807connection con2; 1808--echo # Sending: 1809--send ALTER TABLE t1 IMPORT TABLESPACE 1810 1811connection default; 1812--echo # Wait until ALTER TABLE is blocked since it tries to acquire X lock. 1813let $wait_condition= 1814 SELECT COUNT(*) = 1 FROM information_schema.processlist 1815 WHERE state = "Waiting for table metadata lock" AND 1816 info = "ALTER TABLE t1 IMPORT TABLESPACE"; 1817--source include/wait_condition.inc 1818 1819connection con1; 1820--echo # Unblock ALTER TABLE IMPORT 1821HANDLER t1 CLOSE; 1822 1823connection con2; 1824--echo # Reaping ALTER TABLE IMPORT 1825--echo # It should fail as no tablespace was copied after DISCARD. 1826--error ER_TABLESPACE_MISSING 1827--reap 1828 1829connection default; 1830DROP TABLE t1; 1831 1832--echo # 1833--echo # 2) ALTER TABLE ... IMPORT/DISCARD TABLESPACE on temporary table 1834--echo # should not try to acquire X metadata lock on base table. 1835--echo # 1836 1837CREATE TABLE t1 (i INT) ENGINE=InnoDB; 1838 1839connection con1; 1840--echo # Acquire S lock on table 't1'. 1841HANDLER t1 OPEN; 1842 1843connection con2; 1844CREATE TEMPORARY TABLE t1 (j INT) ENGINE=InnoDB; 1845 1846--echo # Both DISCARD and IMPORT on temporary table should fail without 1847--echo # acquiring any locks and blocking. 1848--error ER_CANNOT_DISCARD_TEMPORARY_TABLE 1849ALTER TABLE t1 DISCARD TABLESPACE; 1850--error ER_CANNOT_DISCARD_TEMPORARY_TABLE 1851ALTER TABLE t1 IMPORT TABLESPACE; 1852 1853DROP TEMPORARY TABLE t1; 1854 1855--echo # 1856--echo # 3) Check that ALTER TABLE ... IMPORT/DISCARD TABLESPACE on 1857--echo # base table under LOCK TABLES acquire X lock. 1858--echo # 1859 1860connection con2; 1861LOCK TABLES t1 WRITE; 1862--echo # Sending: 1863--send ALTER TABLE t1 DISCARD TABLESPACE 1864 1865connection default; 1866--echo # Wait until ALTER TABLE is blocked since it tries to acquire X lock. 1867let $wait_condition= 1868 SELECT COUNT(*) = 1 FROM information_schema.processlist 1869 WHERE state = "Waiting for table metadata lock" AND 1870 info = "ALTER TABLE t1 DISCARD TABLESPACE"; 1871--source include/wait_condition.inc 1872 1873connection con1; 1874--echo # Unblock ALTER TABLE DISCARD 1875HANDLER t1 CLOSE; 1876 1877connection con2; 1878--echo # Reaping ALTER TABLE DISCARD 1879--reap 1880 1881connection con1; 1882--echo # Acquire S lock on table 't1'. 1883HANDLER t1 OPEN; 1884 1885connection con2; 1886--echo # Sending: 1887--send ALTER TABLE t1 IMPORT TABLESPACE 1888 1889connection default; 1890--echo # Wait until ALTER TABLE is blocked since it tries to acquire X lock. 1891let $wait_condition= 1892 SELECT COUNT(*) = 1 FROM information_schema.processlist 1893 WHERE state = "Waiting for table metadata lock" AND 1894 info = "ALTER TABLE t1 IMPORT TABLESPACE"; 1895--source include/wait_condition.inc 1896 1897connection con1; 1898--echo # Unblock ALTER TABLE IMPORT 1899HANDLER t1 CLOSE; 1900 1901connection con2; 1902--echo # Reaping ALTER TABLE IMPORT 1903--echo # It should fail as no tablespace was copied after DISCARD. 1904--error ER_TABLESPACE_MISSING 1905--reap 1906 1907UNLOCK TABLES; 1908 1909connection default; 1910DROP TABLE t1; 1911 1912--echo # 1913--echo # 4) Under LOCK TABLES, ALTER TABLE ... IMPORT/DISCARD TABLESPACE on 1914--echo # temporary table should not try to acquire X metadata lock on base 1915--echo # table. 1916--echo # 1917 1918CREATE TABLE t1 (i INT) ENGINE=InnoDB; 1919 1920connection con1; 1921--echo # Acquire S lock on table 't1'. 1922HANDLER t1 OPEN; 1923 1924connection con2; 1925CREATE TEMPORARY TABLE t1 (j INT) ENGINE=InnoDB; 1926LOCK TABLES t1 WRITE; 1927 1928--echo # Both DISCARD and IMPORT on temporary table should fail without 1929--echo # acquiring any locks and blocking. 1930--error ER_CANNOT_DISCARD_TEMPORARY_TABLE 1931ALTER TABLE t1 DISCARD TABLESPACE; 1932--error ER_CANNOT_DISCARD_TEMPORARY_TABLE 1933ALTER TABLE t1 IMPORT TABLESPACE; 1934 1935UNLOCK TABLES; 1936DROP TEMPORARY TABLE t1; 1937 1938connection con1; 1939HANDLER t1 CLOSE; 1940 1941--echo # 1942--echo # Clean-up. 1943--echo # 1944disconnect con1; 1945--source include/wait_until_disconnected.inc 1946disconnect con2; 1947--source include/wait_until_disconnected.inc 1948connection default; 1949--disable_connect_log 1950DROP TABLE t1; 1951SET sql_mode = default; 1952 1953 1954--echo # 1955--echo # BUG 19779365: INDEX COMMENT IN ADD INDEX IS IGNORED. 1956--echo # 1957 1958--echo # After the patch, the alter table reflects the new 1959--echo # index comment or the lack of comment for the indexes. 1960CREATE TABLE t1(fld1 int, key key1(fld1)); 1961SHOW INDEX FROM t1; 1962ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1) COMMENT 'test'; 1963SHOW INDEX FROM t1; 1964DROP TABLE t1; 1965 1966CREATE TABLE t1(fld1 int, key key1(fld1) COMMENT 'test'); 1967SHOW INDEX FROM t1; 1968ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1); 1969SHOW INDEX FROM t1; 1970DROP TABLE t1; 1971 1972CREATE TABLE t1(fld1 int, key key1(fld1) COMMENT 'test'); 1973SHOW INDEX FROM t1; 1974ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1) COMMENT 'success'; 1975SHOW INDEX FROM t1; 1976DROP TABLE t1; 1977 1978# Alter the comment, but keep the same comment length 1979CREATE TABLE t1(fld1 int, key key1(fld1) COMMENT 'old comment'); 1980SHOW INDEX FROM t1; 1981ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1) COMMENT 'new comment'; 1982SHOW INDEX FROM t1; 1983DROP TABLE t1; 1984 1985 1986--echo # 1987--echo # Bug#19635706 1988--echo # Verify that it is possible to add a unique key to a not-NULL POINT 1989--echo # column and that this key is promoted to primary key 1990--echo # 1991 1992CREATE TABLE t1(a INT NOT NULL, b POINT NOT NULL) ENGINE=INNODB; 1993SHOW CREATE TABLE t1; 1994--error ER_SPATIAL_UNIQUE_INDEX 1995ALTER TABLE t1 ADD UNIQUE INDEX (b); 1996 1997--echo # Note that SHOW CREATE TABLE does not list b as a primary key, 1998--echo # even though it was promoted. This appears to be the case also 1999--echo # for other column types. 2000SHOW CREATE TABLE t1; 2001 2002ALTER TABLE t1 ADD UNIQUE INDEX (a); 2003SHOW CREATE TABLE t1; 2004 2005--echo # Verify that the expected indices have been created by Innodb 2006SELECT T.NAME AS TABLE_NAME, I.NAME AS INDEX_NAME, 2007 CASE I.TYPE 2008 WHEN 0 THEN 'Secondary' 2009 WHEN 1 THEN 'Clustered' 2010 WHEN 2 THEN 'Unique' 2011 WHEN 3 THEN 'Primary' 2012 WHEN 32 THEN 'Full text' 2013 WHEN 64 THEN 'Spatial' 2014 ELSE 'Unknown' 2015 END AS INDEX_TYPE, 2016 F.NAME AS FIELD_NAME, F.POS AS FIELD_POS FROM 2017 INFORMATION_SCHEMA.INNODB_TABLES AS T JOIN 2018 INFORMATION_SCHEMA.INNODB_INDEXES AS I JOIN 2019 INFORMATION_SCHEMA.INNODB_FIELDS AS F 2020 ON I.INDEX_ID = F.INDEX_ID AND I.TABLE_ID = T.TABLE_ID 2021 WHERE T.NAME = 'test/t1' ORDER BY I.NAME, F.NAME; 2022 2023DROP TABLE t1; 2024 2025 2026--echo # 2027--echo # BUG#16886196 - ALTER TABLE FAILS TO CONVERT TO PREFIX INDEX IN 2028--echo # ALTER_COLUMN_EQUAL_PACK_LENGTH 2029 2030 2031SET @orig_sql_mode = @@sql_mode; 2032SET sql_mode= ''; 2033 2034--echo # Test with '767' as index size limit. 2035CREATE TABLE t1(fld1 VARCHAR(767), KEY a(fld1)) charset latin1 ENGINE= INNODB 2036ROW_FORMAT=COMPACT; 2037--echo # With patch for Bug#26848813, a warning is reported. 2038ALTER TABLE t1 CHANGE fld1 fld1 VARCHAR(768), ALGORITHM= INPLACE; 2039DROP TABLE t1; 2040 2041CREATE TABLE t1(fld1 VARCHAR(3072), KEY a(fld1)) charset latin1 ENGINE= INNODB, 2042ROW_FORMAT=DYNAMIC; 2043INSERT INTO t1 VALUES('a'); 2044ALTER TABLE t1 CHANGE fld1 fld1 VARCHAR(3073), ALGORITHM= INPLACE; 2045 2046--echo # Without patch, the below statement will assert in a debug build. 2047SELECT COUNT(*) FROM t1 WHERE fld1= 'a'; 2048 2049--echo # Cleanup. 2050DROP TABLE t1; 2051 2052--echo # Test with innodb prefix indexes up to 3072 bytes. 2053 2054CREATE TABLE t1(fld1 VARCHAR(3072), KEY a(fld1)) charset latin1 ENGINE= INNODB 2055ROW_FORMAT= DYNAMIC; 2056INSERT INTO t1 VALUES('a'); 2057ALTER TABLE t1 CHANGE fld1 fld1 VARCHAR(3073), ALGORITHM= INPLACE; 2058 2059--echo # Without patch, the below statement will assert in a debug build. 2060SELECT COUNT(*) FROM t1 WHERE fld1= 'a'; 2061 2062--echo # Cleanup. 2063DROP TABLE t1; 2064SET sql_mode= @orig_sql_mode; 2065 2066 2067 2068--echo # 2069--echo #BUG#20106553: ALTER TABLE WHICH CHANGES INDEX COMMENT IS NOT 2070--echo # LONGER INPLACE/FAST OPERATION. 2071 2072--echo #Without the patch, the ALTER TABLE to change the index 2073--echo #comment using INPLACE algorithm reports an error. 2074CREATE TABLE t1(fld1 int, key key1(fld1)) ENGINE= INNODB; 2075SHOW INDEX FROM t1; 2076ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1) COMMENT 'test', 2077ALGORITHM= INPLACE; 2078SHOW INDEX FROM t1; 2079DROP TABLE t1; 2080 2081CREATE TABLE t1(fld1 int, key key1(fld1) COMMENT 'test') ENGINE= MyISAM; 2082SHOW INDEX FROM t1; 2083ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1), ALGORITHM=INPLACE; 2084SHOW INDEX FROM t1; 2085DROP TABLE t1; 2086 2087CREATE TABLE t1(fld1 int, key key1(fld1) COMMENT 'test') ENGINE= INNODB; 2088SHOW INDEX FROM t1; 2089ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1) COMMENT 'success', 2090ALGORITHM= INPLACE; 2091SHOW INDEX FROM t1; 2092DROP TABLE t1; 2093 2094# Alter the comment, but keep the same comment length 2095CREATE TABLE t1(fld1 int, key key1(fld1) COMMENT 'old comment') ENGINE=MyISAM; 2096SHOW INDEX FROM t1; 2097ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1) COMMENT 'new comment', 2098ALGORITHM= INPLACE; 2099SHOW INDEX FROM t1; 2100DROP TABLE t1; 2101 2102--echo #Test cases with merge threshold specified in the index comment. 2103 2104CREATE TABLE t1(fld1 int, key key1(fld1)) ENGINE=INNODB; 2105SHOW INDEX FROM t1; 2106SELECT MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='key1'; 2107ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1) COMMENT 2108'MERGE_THRESHOLD=45'; 2109SHOW INDEX FROM t1; 2110SELECT MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='key1'; 2111DROP TABLE t1; 2112 2113CREATE TABLE t1(fld1 int, key key1(fld1) COMMENT 'MERGE_THRESHOLD=40') 2114ENGINE=INNODB; 2115SHOW INDEX FROM t1; 2116SELECT MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='key1'; 2117ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1); 2118SHOW INDEX FROM t1; 2119SELECT MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='key1'; 2120DROP TABLE t1; 2121 2122CREATE TABLE t1(fld1 int, key key1(fld1) COMMENT 'MERGE_THRESHOLD=40') 2123ENGINE=INNODB; 2124SHOW INDEX FROM t1; 2125SELECT MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='key1'; 2126ALTER TABLE t1 DROP INDEX key1, ADD INDEX key1(fld1) COMMENT 2127'MERGE_THRESHOLD=45'; 2128SHOW INDEX FROM t1; 2129SELECT MERGE_THRESHOLD from INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='key1'; 2130DROP TABLE t1; 2131 2132--echo # 2133--echo # Bug#20748660 THD->MDL_CONTEXT.OWNS_EQUAL_OR_STRONGER_LOCK | CREATE_INFO->TABLESPACE 2134--echo # 2135--echo # A failing DISCARD/IMPORT TABLESPACE may leave the flag THD::tablespace_op 2136--echo # in an incorrect state, causing a succeeding statement to fail. 2137--echo 2138--echo # Create a tablespace with a table. 2139CREATE TABLESPACE s ADD DATAFILE 's.ibd' ENGINE InnoDB; 2140CREATE TABLE t (i int) TABLESPACE s ENGINE InnoDB; 2141 2142--echo # The following statent will fail because the table is not partitioned. 2143--echo # Without the fix, the THD::tablespace_op flag will be left set. 2144--error ER_PARTITION_MGMT_ON_NONPARTITIONED 2145ALTER TABLE t DISCARD PARTITION p TABLESPACE; 2146 2147--echo # Without the patch, this statement will inherit the tablespace_op flag 2148--echo # from the previous statement, causing the statement below to fail due 2149--echo # to not acquiring the required MDL lock. 2150ALTER TABLE t TABLESPACE s; 2151 2152--echo # Drop table and tablespace. 2153DROP TABLE t; 2154DROP TABLESPACE s ENGINE InnoDB; 2155 2156--echo # 2157--echo # Bug#20279241 - ALTER TABLE ... CHARACTER SET UTF8, CONVERT TO 2158--echo # CHARACTER SET LATIN1 SHOULD FAIL 2159--echo # 2160 2161CREATE TABLE t1 (f1 INT); 2162 2163--echo # Case 1: Alter table with CHARACTER SET X, CHARACTER SET X 2164--echo # ALTER table operation should pass. 2165ALTER TABLE t1 CHARACTER SET utf8, CHARACTER SET utf8; 2166 2167--echo # Case 2: Alter table with CHARACTER SET X, CONVERT TO CHARACTER SET X 2168--echo # ALTER table operation should pass. 2169ALTER TABLE t1 CHARACTER SET utf8, CONVERT TO CHARACTER SET utf8; 2170 2171--echo # Case 3: Alter table with CONVERT TO CHARACTER SET X, CHARACTER SET X 2172--echo # ALTER table operation should pass. 2173ALTER TABLE t1 CONVERT TO CHARACTER SET utf8, CHARACTER SET utf8; 2174 2175--echo # Case 4: Alter table with CONVERT TO CHARACTER SET X, CONVERT TO CHARACTER SET X 2176--echo # ALTER table operation should pass. 2177ALTER TABLE t1 CONVERT TO CHARACTER SET utf8, CONVERT TO CHARACTER SET utf8; 2178 2179--echo # Case 5: Alter table with CHARACTER SET X, CONVERT TO CHARACTER SET Y 2180--echo # Alter table operations should fail with an error "ER_CONFLICTING_DECLARATIONS". 2181--error ER_CONFLICTING_DECLARATIONS 2182ALTER TABLE t1 CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1; 2183 2184--echo # Case 6: Alter table with CONVERT TO CHARACTER SET X, CHARACTER SET Y 2185--echo # Alter table operations should fail with an error "ER_CONFLICTING_DECLARATIONS". 2186--error ER_CONFLICTING_DECLARATIONS 2187ALTER TABLE t1 CONVERT TO CHARACTER SET utf8, CHARACTER SET latin1; 2188 2189--echo # Case 7: Alter table with CONVERT TO CHARACTER SET X, CONVERT TO CHARACTER SET Y 2190--echo # Alter table operations should fail with an error "ER_CONFLICTING_DECLARATIONS". 2191--error ER_CONFLICTING_DECLARATIONS 2192ALTER TABLE t1 CONVERT TO CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1; 2193 2194--echo # Case 8: Alter table with CHARACTER SET X COLLATE INCORRECT_COLLATION_NAME 2195--echo # Alter table operation should fail with an error "ER_COLLATION_CHARSET_MISMATCH" 2196--error ER_COLLATION_CHARSET_MISMATCH 2197ALTER TABLE t1 CHARACTER SET utf8 COLLATE latin1_danish_ci; 2198 2199--echo # Case 9: Alter table with CONVERT TO CHARACTER SET X COLLATE INCORRECT_COLLATION_NAME 2200--echo # Alter table operation should fail with an error "ER_COLLATION_CHARSET_MISMATCH" 2201--error ER_COLLATION_CHARSET_MISMATCH 2202ALTER TABLE t1 CONVERT TO CHARACTER SET utf8 COLLATE latin1_danish_ci; 2203 2204--echo # Case 10: Alter table with CHARACTER SET X, CONVERT TO CHARACTER SET Y COLLATE INCORRECT_COLLATION_NAME 2205--echo # Alter table operation should fail with an error "ER_COLLATION_CHARSET_MISMATCH" 2206--error ER_COLLATION_CHARSET_MISMATCH 2207ALTER TABLE t1 CHARACTER SET latin1, CONVERT TO CHARACTER SET utf8 COLLATE latin1_danish_ci; 2208 2209# Clean-up 2210DROP TABLE t1; 2211 2212--echo # 2213--echo # BUG#20106837: ALTER TABLE WHICH DROPS AND ADDS THE SAME FULLTEXT 2214--echo # INDEX IS NOT INPLACE/FAST. 2215 2216CREATE TABLE t1(fld1 varchar(200), FULLTEXT(fld1)) ENGINE=MyISAM; 2217INSERT INTO t1 VALUES('ABCD'); 2218 2219--enable_info 2220--echo #Without patch, it was not fast a INPLACE ALTER. 2221ALTER TABLE t1 DROP INDEX fld1, ADD FULLTEXT INDEX fld1(fld1); 2222--disable_info 2223 2224--echo #Without patch, reports an error 'ER_ALTER_OPERATION_NOT_SUPPORTED'. 2225ALTER TABLE t1 ALGORITHM=INPLACE, DROP INDEX fld1, 2226ADD FULLTEXT INDEX fld1(fld1); 2227DROP TABLE t1; 2228 2229--echo #Test with InnoDB engine. 2230CREATE TABLE t1(fld1 varchar(200), FULLTEXT(fld1)) ENGINE=INNODB; 2231INSERT INTO t1 VALUES('ABCD'); 2232 2233--enable_info 2234--echo #Without patch, it was not fast a INPLACE ALTER. 2235ALTER TABLE t1 DROP INDEX fld1, ADD FULLTEXT INDEX fld1(fld1); 2236--disable_info 2237 2238--echo #Without patch, reports an error 'ER_ALTER_OPERATION_NOT_SUPPORTED'. 2239ALTER TABLE t1 ALGORITHM=INPLACE, DROP INDEX fld1, 2240ADD FULLTEXT INDEX fld1(fld1); 2241DROP TABLE t1; 2242 2243 2244--echo # 2245--echo # Bug#20146455: FIND_KEY_CI RETURNS NULL, CAUSES CRASH IN 2246--echo # FILL_ALTER_INPLACE_INFO 2247--echo # 2248 2249CREATE TABLE t1 (a INT PRIMARY KEY, b INT, 2250 FOREIGN KEY (b) REFERENCES t1(a)) ENGINE= MyISAM; 2251ALTER TABLE t1 RENAME INDEX b TO w, ADD FOREIGN KEY (b) REFERENCES t1(a); 2252SHOW CREATE TABLE t1; 2253DROP TABLE t1; 2254 2255CREATE TABLE t1 (a INT PRIMARY KEY, b INT, 2256 FOREIGN KEY (b) REFERENCES t1(a)) ENGINE= InnoDB; 2257ALTER TABLE t1 RENAME INDEX b TO w, ADD FOREIGN KEY (b) REFERENCES t1(a); 2258SHOW CREATE TABLE t1; 2259DROP TABLE t1; 2260 2261 2262--echo # 2263--echo # Bug#22017616: ASSERTION FAILED: TABLE_SHARE->IS_MISSING_PRIMARY_KEY() 2264--echo # == M_PREBUILT->CLUST_IND 2265--echo # 2266--echo # Ensure that adding indexes with virtual columns are not promoted to 2267--echo # primary keys 2268--echo # 2269--echo # Base line with normal column - should be promoted 2270CREATE TABLE t0(a INT NOT NULL) ENGINE=INNODB; 2271ALTER TABLE t0 ADD UNIQUE INDEX (a); 2272 2273--echo # Case a: Create table with virtual unique not null column 2274--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN 2275CREATE TABLE t1(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL UNIQUE NOT NULL) ENGINE=INNODB; 2276 2277--echo # Case b: Create table with index on virtual point column 2278--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN 2279CREATE TABLE t2(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL NOT NULL, UNIQUE INDEX no_pk(a(1))) ENGINE=INNODB; 2280 2281--echo # Case c: Add unique index on virtual point column 2282CREATE TABLE t3(a POINT GENERATED ALWAYS AS (POINT(1,1)) VIRTUAL NOT NULL) 2283ENGINE=INNODB; 2284--echo # Case c: Add unique index on virtual point column 2285--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN 2286ALTER TABLE t3 ADD UNIQUE INDEX (a(1)); 2287SELECT * FROM t3; 2288 2289--echo # Case d: Add unique index on virtual blob column 2290CREATE TABLE t4 (a BLOB, b BLOB GENERATED ALWAYS AS (a) VIRTUAL NOT NULL) ENGINE=INNODB; 2291ALTER TABLE t4 ADD UNIQUE INDEX (b(1)); 2292SELECT * FROM t4; 2293 2294--echo # Query I_S to verify that 'a' is promoted to pk only when it 2295--echo # isn't virtual 2296SELECT T.NAME AS TABLE_NAME, I.NAME AS INDEX_NAME, 2297 CASE (I.TYPE & 3) 2298 WHEN 3 THEN "yes" 2299 ELSE "no" END AS IS_PRIMARY_KEY, 2300 F.NAME AS FIELD_NAME, F.POS AS FIELD_POS FROM 2301 INFORMATION_SCHEMA.INNODB_TABLES AS T JOIN 2302 INFORMATION_SCHEMA.INNODB_INDEXES AS I JOIN 2303 INFORMATION_SCHEMA.INNODB_FIELDS AS F 2304 ON I.INDEX_ID = F.INDEX_ID AND I.TABLE_ID = T.TABLE_ID 2305 WHERE T.NAME LIKE 'test/%' ORDER BY T.NAME, I.NAME, F.POS; 2306 2307 2308DROP TABLE t0; 2309DROP TABLE t3; 2310DROP TABLE t4; 2311 2312 2313--echo # 2314--echo # Bug #22141913 ASSERTION: !"INVALID KEY_PART->FIELDNR 2315--echo # FILL_DD_INDEX_ELEMENTS_FROM_KEY_PARTS 2316--echo # 2317 2318CREATE TABLE t (a TIMESTAMP(1) GENERATED ALWAYS AS (1) VIRTUAL, 2319 b INT GENERATED ALWAYS AS (1) VIRTUAL 2320 ) ENGINE=INNODB; 2321 2322--echo # The below ALTER asserts without the fix. 2323ALTER TABLE t ADD INDEX (b); 2324DROP TABLE t; 2325 2326 2327--echo # 2328--echo # Bug#21345391: ALTER TABLE ... CONVERT TO CHARACTER SET NOT EFFECT 2329--echo # AND REMAIN A TEMP TABLE 2330 2331CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE = INNODB CHARACTER SET gbk; 2332ALTER TABLE t1 CONVERT TO CHARACTER SET UTF8, ALGORITHM = INPLACE; 2333 2334--echo # Without fix, the CHARSET SET for table remains gbk. 2335SHOW CREATE TABLE t1; 2336 2337let $test_dir= `SELECT CONCAT(@@datadir, 'test/')`; 2338 2339--echo # Without fix, the temporary .frm file is not cleaned up. 2340--list_files $test_dir `#sql-*.frm` 2341 2342DROP TABLE t1; 2343 2344--echo # Test cases added for coverage. 2345 2346--echo # Reports an error for tables containing datatypes supporting 2347--echo # characters. 2348 2349CREATE TABLE t1 (fld1 CHAR(10) PRIMARY KEY) ENGINE = INNODB CHARACTER SET gbk; 2350 2351--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 2352ALTER TABLE t1 CONVERT TO CHARACTER SET UTF8, ALGORITHM = INPLACE; 2353 2354DROP TABLE t1; 2355 2356--echo # ALTER TABLE, CHARACTER SET operation. 2357 2358CREATE TABLE t1 (fld1 INT PRIMARY KEY, fld2 CHAR(10)) ENGINE = INNODB 2359CHARACTER SET gbk; 2360ALTER TABLE t1 CHARACTER SET UTF8, ALGORITHM = INPLACE; 2361 2362SHOW CREATE TABLE t1; 2363 2364let $test_dir= `SELECT CONCAT(@@datadir, 'test/')`; 2365 2366--list_files $test_dir `#sql-*.frm` 2367 2368DROP TABLE t1; 2369 2370--echo # 2371--echo # Bug#22227958: ASSERT IN DD::FILL_DD_TABLE_FROM_CREATE_INFO, DDL 2372--echo # 2373--echo # Verify that it is possible to set conflicting values for PACK_KEYS, 2374--echo # STATS_PERSISTENT CHECKSUM and DELAY_KEY_WRITE 2375--echo # The last value set should be displayed by SHOW CREATE TABLE. 2376 2377--echo # Create table with conflicting values 2378CREATE TABLE t1(i INT) ENGINE=INNODB PACK_KEYS=0 PACK_KEYS=1 2379STATS_PERSISTENT=0 STATS_PERSISTENT=1 CHECKSUM=0 CHECKSUM=1 2380DELAY_KEY_WRITE=0 DELAY_KEY_WRITE=1; 2381 2382--echo # Should show PACK_KEYS=1 STATS_PERSISTENT=1 2383--echo # CHECKSUM=1 DELAY_KEY_WRITE=1 2384SHOW CREATE TABLE t1; 2385 2386--echo # Alter table with conflicting values 2387ALTER TABLE t1 PACK_KEYS=1 PACK_KEYS=0 STATS_PERSISTENT=1 STATS_PERSISTENT=0 CHECKSUM=1 CHECKSUM=0 DELAY_KEY_WRITE=1 DELAY_KEY_WRITE=0; 2388 2389--echo # Should show PACK_KEYS=0 STATS_PERSISTENT=0 (0 is default for CHECKSUM and DELAY_KEY_WRITE) 2390SHOW CREATE TABLE t1; 2391 2392DROP TABLE t1; 2393 2394 2395--echo # 2396--echo # Bug#22740093: ERROR 1071 (42000): SPECIFIED KEY WAS TOO LONG 2397--echo # ERROR WHILE DROPPING INDEX IN 5.7 2398--echo # 2399 2400# Before you were allowed to create a prefix key for TEXT columns 2401# which were larger than the column length. The column length 2402# for TINYBLOB is 255 bytes, while prefix length is in characters. 2403--error ER_TOO_LONG_KEY 2404CREATE TABLE t1(id INT PRIMARY KEY, 2405 name TINYTEXT, 2406 KEY nameloc (name(64)) 2407) DEFAULT CHARSET=utf8mb4; 2408 2409# 63 characters of 4 bytes each fit in 255 bytes. 2410CREATE TABLE t1(id INT PRIMARY KEY, 2411 name TINYTEXT, 2412 KEY nameloc (name(63)) 2413) DEFAULT CHARSET=utf8mb4; 2414 2415# Check that we can rebuild the table without issue 2416ALTER TABLE t1 FORCE; 2417 2418# Also check ALTER TABLE 2419--error ER_TOO_LONG_KEY 2420ALTER TABLE t1 ADD INDEX idx (name(64), id); 2421ALTER TABLE t1 ADD INDEX idx (name(63), id); 2422DROP TABLE t1; 2423 2424# Check that we can create a table with a prefix index of 2425# 64 4-byte characters for a TEXT column. 2426 2427# 64 characters of 4 bytes each fit in 65535 bytes. 2428CREATE TABLE t1(id INT PRIMARY KEY, 2429 name TEXT, 2430 KEY nameloc (name(64)) 2431) DEFAULT CHARSET=utf8mb4; 2432 2433# Check ALTER TABLE where we change the type of the 2434# base column, reducing max field length, keeping the 2435# length of the prefix key the same. 2436--error ER_TOO_LONG_KEY 2437ALTER TABLE t1 MODIFY COLUMN name TINYTEXT; 2438DROP TABLE t1; 2439 2440 2441--echo # 2442--echo # BUG#16888677: OUT OF RANGE VALUE ACCEPTED FOR DATETIME COLUMN IN 2443--echo # ALTER TABLE...ADD COLUMN 2444 2445SET @saved_sql_mode = @@session.sql_mode; 2446 2447--echo # Test case with no SQL_MODE enabled. 2448SET SESSION sql_mode= ''; 2449CREATE TABLE t1(fld1 DATE NOT NULL) ENGINE= INNODB; 2450INSERT INTO t1 VALUES('2000-01-01'); 2451 2452--echo # No warnings or error is reported 2453ALTER TABLE t1 ADD COLUMN fld2 DATETIME NOT NULL, ALGORITHM=INPLACE; 2454ALTER TABLE t1 ADD COLUMN fld3 DATETIME NOT NULL, ALGORITHM=COPY; 2455 2456DROP TABLE t1; 2457 2458--echo # Test case with strict mode enabled. 2459 2460SET SESSION sql_mode= 'STRICT_ALL_TABLES'; 2461CREATE TABLE t1(fld1 DATE NOT NULL) ENGINE= INNODB; 2462INSERT INTO t1 VALUES('2000-01-01'); 2463 2464--echo # No warnings or error is reported 2465ALTER TABLE t1 ADD COLUMN fld2 DATETIME NOT NULL, ALGORITHM=INPLACE; 2466ALTER TABLE t1 ADD COLUMN fld3 DATETIME NOT NULL, ALGORITHM=COPY; 2467 2468DROP TABLE t1; 2469 2470--echo # Test case with 'NO_ZERO_DATE' enabled. 2471 2472SET SESSION sql_mode= 'NO_ZERO_DATE'; 2473CREATE TABLE t1(fld1 DATE NOT NULL) ENGINE= INNODB; 2474INSERT INTO t1 VALUES('2000-01-01'); 2475 2476--echo # Warnings are reported after patch. 2477ALTER TABLE t1 ADD COLUMN fld2 DATETIME NOT NULL, ALGORITHM=INPLACE; 2478ALTER TABLE t1 ADD COLUMN fld3 DATETIME NOT NULL, ALGORITHM=COPY; 2479 2480DROP TABLE t1; 2481 2482--echo # Test case with both 'NO_ZERO_DATE and strict mode' enabled. 2483 2484SET SESSION sql_mode= @saved_sql_mode; 2485 2486CREATE TABLE t1(fld1 DATE NOT NULL) ENGINE= INNODB; 2487INSERT INTO t1 VALUES('2000-01-01'); 2488 2489--echo # Without patch, the following statement succeeds. 2490--error ER_TRUNCATED_WRONG_VALUE 2491ALTER TABLE t1 ADD COLUMN fld2 DATETIME NOT NULL, ALGORITHM=INPLACE; 2492--error ER_TRUNCATED_WRONG_VALUE 2493ALTER TABLE t1 ADD COLUMN fld2 DATETIME NOT NULL, ALGORITHM=COPY; 2494 2495TRUNCATE TABLE t1; 2496--echo # Operation below succeeds, since the table has no records. 2497ALTER TABLE t1 ADD COLUMN fld4 DATETIME NOT NULL, ALGORITHM=INPLACE; 2498DROP TABLE t1; 2499 2500 2501--echo # 2502--echo # Additional coverage for WL#7743 "New data dictionary: changes to 2503--echo # DDL-related parts of SE API". Check that ALTER TABLE INPLACE with 2504--echo # RENAME TO clause correctly handles FKs and triggers. 2505--echo # 2506CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB; 2507CREATE TABLE t2 (fk INT, FOREIGN KEY (fk) REFERENCES t1 (pk)) ENGINE=InnoDB; 2508ALTER TABLE t2 ADD COLUMN j INT, RENAME TO t3, ALGORITHM=INPLACE; 2509SHOW CREATE TABLE t3; 2510DROP TABLE t3; 2511CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=0; 2512ALTER TABLE t1 ADD COLUMN j INT, RENAME TO t4, ALGORITHM=INPLACE; 2513SELECT trigger_name, event_object_schema, event_object_table, action_statement 2514 FROM information_schema.triggers WHERE event_object_schema = 'test'; 2515DROP TABLE t4; 2516 2517--echo 2518--echo Bug#25779239 ALTER TABLE FAILS WHEN DEFAULT 2519--echo CHARACTER SET CHANGES TO UTF8MB4 2520--echo 2521 2522CREATE TABLE t1( 2523 i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY) charset latin1; 2524ALTER TABLE t1 2525 DROP i, 2526 ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT, 2527 AUTO_INCREMENT = 1; 2528DROP TABLE t1; 2529 2530CREATE TABLE t1( 2531 i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY) charset utf8mb4; 2532ALTER TABLE t1 2533 DROP i, 2534 ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT, 2535 AUTO_INCREMENT = 1; 2536DROP TABLE t1; 2537 2538--echo # 2539--echo # BUG#25385334: MYSQL 5.7 ERROR WITH ALTER TABLE MODIFY SYNTAX 2540--echo # AND DATETIME TYPE. 2541 2542SET @saved_sql_mode = @@session.sql_mode; 2543 2544--echo # Test case with both strict mode and 'NO_ZERO_DATE' enabled. 2545CREATE TABLE t1 (fld1 INT, fld2 DATETIME NOT NULL) ENGINE= INNODB; 2546CREATE TABLE t2 (fld1 INT, fld2 POINT NOT NULL) ENGINE= INNODB; 2547 2548INSERT INTO t1 VALUES(1, '2000-01-01'); 2549INSERT INTO t2 values(1, ST_PointFromText('POINT(10 10)')); 2550 2551--echo # Without patch, the following statments report error. 2552ALTER TABLE t1 MODIFY fld2 DATETIME NOT NULL AFTER fld1; 2553ALTER TABLE t2 MODIFY fld2 POINT NOT NULL AFTER fld1; 2554 2555--echo # Without patch, the following statments report error. 2556ALTER TABLE t1 MODIFY fld2 DATETIME NOT NULL FIRST, ALGORITHM= COPY; 2557ALTER TABLE t2 MODIFY fld2 POINT NOT NULL FIRST, ALGORITHM= COPY; 2558 2559--echo # Tests added for coverage. 2560--error ER_TRUNCATED_WRONG_VALUE 2561ALTER TABLE t1 MODIFY fld2 DATETIME NOT NULL AFTER fld1, 2562 ADD COLUMN fld3 DATETIME NOT NULL; 2563--error ER_INVALID_USE_OF_NULL 2564ALTER TABLE t2 MODIFY fld2 POINT NOT NULL AFTER fld1, 2565 ADD COLUMN fld3 MULTIPOINT NOT NULL; 2566 2567--error ER_TRUNCATED_WRONG_VALUE 2568ALTER TABLE t1 MODIFY fld2 DATETIME NOT NULL AFTER fld1, 2569 ADD COLUMN fld3 DATETIME NOT NULL, ALGORITHM= COPY; 2570--error ER_INVALID_USE_OF_NULL 2571ALTER TABLE t2 MODIFY fld2 POINT NOT NULL AFTER fld1, 2572 ADD COLUMN fld3 MULTIPOINT NOT NULL, ALGORITHM= COPY; 2573 2574TRUNCATE TABLE t1; 2575TRUNCATE TABLE t2; 2576 2577--echo # Without patch, the following statments report error. 2578ALTER TABLE t1 MODIFY fld2 DATETIME NOT NULL AFTER fld1; 2579ALTER TABLE t2 MODIFY fld2 POINT NOT NULL AFTER fld1; 2580 2581--echo # Without patch, the following statments report error. 2582ALTER TABLE t1 MODIFY fld2 DATETIME NOT NULL FIRST, ALGORITHM= COPY; 2583ALTER TABLE t2 MODIFY fld2 POINT NOT NULL FIRST, ALGORITHM= COPY; 2584 2585--echo # Tests added for coverage. 2586ALTER TABLE t1 MODIFY fld2 DATETIME NOT NULL AFTER fld1, 2587 ADD COLUMN fld3 DATETIME NOT NULL; 2588ALTER TABLE t2 MODIFY fld2 POINT NOT NULL AFTER fld1, 2589 ADD COLUMN fld3 MULTIPOINT NOT NULL; 2590 2591ALTER TABLE t1 MODIFY fld2 DATETIME NOT NULL AFTER fld1, 2592 ADD COLUMN fld4 DATETIME NOT NULL, ALGORITHM= COPY; 2593ALTER TABLE t2 MODIFY fld2 POINT NOT NULL AFTER fld1, 2594 ADD COLUMN fld4 MULTIPOINT NOT NULL, ALGORITHM= COPY; 2595 2596DROP TABLE t1, t2; 2597 2598--echo # Test case when a non date column is converted to datetime column. 2599 2600--echo # Test case without any sql mode enabled. 2601SET SESSION sql_mode= ''; 2602CREATE TABLE t1 (fld1 char(25)) ENGINE= INNODB; 2603INSERT INTO t1 VALUES('0000-00-00'); 2604 2605--echo # No error or warning is reported. 2606ALTER TABLE t1 MODIFY fld1 DATETIME NOT NULL; 2607 2608DROP TABLE t1; 2609 2610--echo # Test case with only 'NO_ZERO_DATE' enabled. 2611SET SESSION sql_mode= 'NO_ZERO_DATE'; 2612CREATE TABLE t1 (fld1 char(25)) ENGINE= INNODB; 2613INSERT INTO t1 VALUES('0000-00-00'); 2614 2615--echo # Reports only a warning. 2616ALTER TABLE t1 MODIFY fld1 DATETIME NOT NULL; 2617 2618DROP TABLE t1; 2619 2620SET SESSION sql_mode= 'STRICT_ALL_TABLES'; 2621CREATE TABLE t1 (fld1 char(25)) ENGINE= INNODB; 2622INSERT INTO t1 VALUES('0000-00-00'); 2623 2624--echo # No error or warning is reported. 2625ALTER TABLE t1 MODIFY fld1 DATETIME NOT NULL; 2626 2627DROP TABLE t1; 2628 2629--echo # Test case with both STRICT MODE and 'NO_ZERO_DATE' enabled. 2630SET SESSION sql_mode= @saved_sql_mode; 2631CREATE TABLE t1 (fld1 char(25)) ENGINE= INNODB; 2632INSERT INTO t1 VALUES('0000-00-00'); 2633 2634--echo # Reports an error. 2635--error ER_TRUNCATED_WRONG_VALUE 2636ALTER TABLE t1 MODIFY fld1 DATETIME NOT NULL; 2637 2638DROP TABLE t1; 2639 2640--echo # Test case when a NULL column is converted to NOT NULL column 2641CREATE TABLE t1 (fld0 DATETIME, fld1 INT, fld2 DATETIME NOT NULL) ENGINE= INNODB; 2642CREATE TABLE t2 (fld0 POINT, fld1 INT, fld2 POINT NOT NULL) ENGINE= INNODB; 2643INSERT INTO t1 VALUES('2000-01-01', 1, '2000-01-01'); 2644INSERT INTO t2 values(ST_PointFromText('POINT(10 10)'), 1, 2645 ST_PointFromText('POINT(10 10)')); 2646 2647--echo # Reports an error without patch. 2648ALTER TABLE t1 MODIFY fld0 DATETIME NOT NULL AFTER fld2; 2649 2650--echo # Reports an error without patch 2651ALTER TABLE t2 MODIFY fld0 POINT NOT NULL AFTER fld2; 2652 2653DROP TABLE t1, t2; 2654 2655--echo # Test case demonstrating the NULL constraint check done by SE. 2656CREATE TABLE t1 (fld0 DATETIME, fld1 INT, fld2 DATETIME NOT NULL) ENGINE= INNODB; 2657CREATE TABLE t2 (fld0 POINT, fld1 INT, fld2 POINT NOT NULL) ENGINE= INNODB; 2658 2659INSERT INTO t1 VALUES(NULL, 1, '2000-01-01'); 2660INSERT INTO t2 values(NULL, 1, ST_PointFromText('POINT(10 10)')); 2661 2662--error ER_INVALID_USE_OF_NULL 2663ALTER TABLE t1 MODIFY fld0 DATETIME NOT NULL; 2664 2665--error ER_INVALID_USE_OF_NULL 2666ALTER TABLE t2 MODIFY fld0 POINT NOT NULL; 2667 2668DROP TABLE t1, t2; 2669 2670 2671--echo # 2672--echo # WL#10761 : ALTER TABLE RENAME COLUMN 2673--echo # 2674 2675CREATE TABLE t1(a INT, b VARCHAR(30), c FLOAT); 2676SHOW CREATE TABLE t1; 2677INSERT INTO t1 VALUES(1,'abcd',1.234); 2678CREATE TABLE t2(a INT, b VARCHAR(30), c FLOAT) ENGINE=MyIsam; 2679SHOW CREATE TABLE t2; 2680INSERT INTO t2 VALUES(1,'abcd',1.234); 2681 2682# Rename one column 2683ALTER TABLE t1 RENAME COLUMN a TO a; 2684ALTER TABLE t1 RENAME COLUMN a TO m; 2685SHOW CREATE TABLE t1; 2686SELECT * FROM t1; 2687 2688# Rename multiple column 2689ALTER TABLE t1 RENAME COLUMN m TO x, 2690 RENAME COLUMN b TO y, 2691 RENAME COLUMN c TO z; 2692SHOW CREATE TABLE t1; 2693SELECT * FROM t1; 2694 2695# Rename multiple columns with MyIsam Engine 2696ALTER TABLE t2 RENAME COLUMN a TO d, RENAME COLUMN b TO e, RENAME COLUMN c to f; 2697SHOW CREATE TABLE t2; 2698SELECT * FROM t2; 2699 2700# Mix different ALTER operations with RENAME COLUMN 2701ALTER TABLE t1 CHANGE COLUMN x a INT, RENAME COLUMN y TO b; 2702SHOW CREATE TABLE t1; 2703ALTER TABLE t1 CHANGE COLUMN z c DOUBLE, RENAME COLUMN b to b; 2704SHOW CREATE TABLE t1; 2705ALTER TABLE t1 CHANGE COLUMN a b int, RENAME COLUMN b TO c, CHANGE COLUMN c d FLOAT; 2706SHOW CREATE TABLE t1; 2707ALTER TABLE t1 ADD COLUMN zz INT, RENAME COLUMN d TO f; 2708SHOW CREATE TABLE t1; 2709ALTER TABLE t1 DROP COLUMN zz, RENAME COLUMN c TO zz; 2710SHOW CREATE TABLE t1; 2711ALTER TABLE t1 RENAME COLUMN zz to c, DROP COLUMN f; 2712SHOW CREATE TABLE t1; 2713ALTER TABLE t1 ADD COLUMN d INT DEFAULT 5, RENAME COLUMN c TO b, DROP COLUMN b; 2714SHOW CREATE TABLE t1; 2715 2716#Cyclic Rename 2717ALTER TABLE t1 RENAME COLUMN b TO d, RENAME COLUMN d TO b; 2718SHOW CREATE TABLE t1; 2719 2720# Rename with Indexes 2721ALTER TABLE t1 ADD KEY(b); 2722SHOW CREATE TABLE t1; 2723ALTER TABLE t1 RENAME COLUMN b TO bb; 2724SHOW CREATE TABLE t1; 2725SELECT * FROM t1; 2726 2727# Rename with Foreign keys. 2728CREATE TABLE t3(a int, b int, KEY(b)) ENGINE=InnoDB; 2729ALTER TABLE t3 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES t1(bb); 2730SHOW CREATE TABLE t3; 2731ALTER TABLE t1 RENAME COLUMN bb TO b; 2732SHOW CREATE TABLE t1; 2733ALTER TABLE t3 RENAME COLUMN b TO c; 2734SHOW CREATE TABLE t3; 2735 2736# Different Algorithm 2737CREATE TABLE t4(a int); 2738ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE; 2739SHOW CREATE TABLE t4; 2740ALTER TABLE t4 RENAME COLUMN aa TO a, ALGORITHM = COPY; 2741SHOW CREATE TABLE t4; 2742DROP TABLE t4; 2743 2744# View, Trigger and SP 2745CREATE VIEW v1 AS SELECT d,e,f FROM t2; 2746CREATE TRIGGER trg1 BEFORE UPDATE on t2 FOR EACH ROW SET NEW.d=OLD.d + 10; 2747CREATE PROCEDURE sp1() INSERT INTO t2(d) VALUES(10); 2748ALTER TABLE t2 RENAME COLUMN d TO g; 2749SHOW CREATE TABLE t2; 2750SHOW CREATE VIEW v1; 2751--error ER_VIEW_INVALID 2752SELECT * FROM v1; 2753--error ER_BAD_FIELD_ERROR 2754UPDATE t2 SET f = f + 10; 2755--error ER_BAD_FIELD_ERROR 2756CALL sp1(); 2757DROP TRIGGER trg1; 2758DROP PROCEDURE sp1; 2759 2760# Generated Columns 2761CREATE TABLE t_gen(a INT, b DOUBLE GENERATED ALWAYS AS (SQRT(a))); 2762INSERT INTO t_gen(a) VALUES(4); 2763SELECT * FROM t_gen; 2764SHOW CREATE TABLE t_gen; 2765ALTER TABLE t_gen RENAME COLUMN a TO c, CHANGE COLUMN b b DOUBLE GENERATED ALWAYS AS (SQRT(c)); 2766SELECT * FROM t_gen; 2767SHOW CREATE TABLE t_gen; 2768--error ER_DEPENDENT_BY_GENERATED_COLUMN 2769ALTER TABLE t_gen CHANGE COLUMN c a INT; 2770--error ER_DEPENDENT_BY_GENERATED_COLUMN 2771ALTER TABLE t_gen RENAME COLUMN c TO a; 2772DROP TABLE t_gen; 2773 2774## Histogram invalidation 2775CREATE TABLE foo (col1 INT); 2776INSERT INTO foo VALUES (1), (2); 2777ANALYZE TABLE foo UPDATE HISTOGRAM ON col1 WITH 10 BUCKETS; 2778# The following query should now list one entry for the column "col1" 2779# Remove 'last-updated' from the histogram, since it will change on every 2780SELECT schema_name, table_name, column_name, 2781 JSON_REMOVE(histogram, '$."last-updated"') 2782FROM information_schema.COLUMN_STATISTICS; 2783ALTER TABLE foo RENAME COLUMN col1 TO col2; 2784# It should not show an entry for "col1" now. 2785SELECT schema_name, table_name, column_name, 2786 JSON_REMOVE(histogram, '$."last-updated"') 2787FROM information_schema.COLUMN_STATISTICS; 2788DROP TABLE foo; 2789 2790# 2791# Negative tests 2792# 2793SHOW CREATE TABLE t1; 2794 2795# Invalid Syntax 2796--error ER_PARSE_ERROR 2797ALTER TABLE t1 RENAME COLUMN b z; 2798--error ER_PARSE_ERROR 2799ALTER TABLE t1 RENAME COLUMN FROM b TO z; 2800--error ER_PARSE_ERROR 2801ALTER TABLE t1 RENAME COLUMN b TO 1; 2802 2803# Duplicate column name 2804--error ER_BAD_FIELD_ERROR 2805ALTER TABLE t1 RENAME COLUMN b TO e, RENAME COLUMN c TO e; 2806--error ER_DUP_FIELDNAME 2807ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN b TO z; 2808 2809# Multiple operation on same column 2810--error ER_BAD_FIELD_ERROR 2811ALTER TABLE t1 DROP COLUMN b, RENAME COLUMN b TO z; 2812--error ER_BAD_FIELD_ERROR 2813ALTER TABLE t1 RENAME COLUMN b TO b, RENAME COLUMN b TO b; 2814--error ER_CANT_DROP_FIELD_OR_KEY 2815ALTER TABLE t1 RENAME COLUMN b TO c3, DROP COLUMN c3; 2816--error ER_BAD_FIELD_ERROR 2817ALTER TABLE t1 ADD COLUMN z INT, CHANGE COLUMN z y INT, DROP COLUMN y; 2818--error ER_BAD_FIELD_ERROR 2819ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN z TO y, DROP COLUMN y; 2820 2821# Invalid column name while renaming 2822--error ER_WRONG_COLUMN_NAME 2823ALTER TABLE t1 RENAME COLUMN b TO `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn`; 2824# This error is different compared to ALTER TABLE ... CHANGE command 2825--error ER_TOO_LONG_IDENT 2826ALTER TABLE t1 CHANGE b `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn` int; 2827 2828SHOW CREATE TABLE t1; 2829SELECT * FROM t1; 2830 2831# Cleanup 2832DROP VIEW v1; 2833DROP TABLE t3,t1,t2; 2834SET SESSION information_schema_stats_expiry=default; 2835 2836--echo # 2837--echo # Bug#27252354: UNABLE TO CREATE INDEX WHEN DATE/DATETIME DATATYPES 2838--echo # ARE USED WITH REQUIRED OPTION 2839--echo # 2840 2841CREATE TABLE t(a INT); 2842INSERT INTO t VALUES (); 2843# Allow adding NOT NULL generated DATE columns to a non-empty table. 2844ALTER TABLE t 2845ADD COLUMN b DATE GENERATED ALWAYS AS ('1999-09-09') VIRTUAL NOT NULL; 2846ALTER TABLE t 2847ADD COLUMN c DATE GENERATED ALWAYS AS ('1999-09-09') STORED NOT NULL; 2848# Constraints are not checked when adding virtual generated columns. 2849ALTER TABLE t 2850ADD COLUMN d DATE GENERATED ALWAYS AS (NULL) VIRTUAL NOT NULL; 2851# Constraints are checked when adding an index on a virtual column. 2852--error ER_BAD_NULL_ERROR 2853CREATE INDEX idx ON t(d); 2854# Constraints are checked when adding stored generated columns. 2855--error ER_BAD_NULL_ERROR 2856ALTER TABLE t 2857ADD COLUMN e DATE GENERATED ALWAYS AS (NULL) STORED NOT NULL; 2858SELECT * FROM t; 2859DROP TABLE t; 2860 2861--echo # 2862--echo # Basic test coverage for ALGORITHM=INSTANT support on SQL-layer. 2863--echo # 2864 2865--echo # 2866--echo # 1) Syntax. 2867--echo # 2868CREATE TABLE t1 (i INT) ENGINE=InnoDB; 2869ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 10, ALGORITHM=INSTANT; 2870SHOW CREATE TABLE t1; 2871--echo # ALGORITHM=INSTANT doesn't make sense with LOCK clause. 2872--error ER_WRONG_USAGE 2873ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 11, ALGORITHM=INSTANT, LOCK=NONE; 2874--error ER_WRONG_USAGE 2875ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 12, ALGORITHM=INSTANT, LOCK=SHARED; 2876--error ER_WRONG_USAGE 2877ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 13, ALGORITHM=INSTANT, LOCK=EXCLUSIVE; 2878--echo # ALGORITHM=INSTANT and LOCK=DEFAULT are OK though. 2879ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 13, ALGORITHM=INSTANT, LOCK=DEFAULT; 2880SHOW CREATE TABLE t1; 2881 2882--echo # 2883--echo # 2) We support INSTANT algorithm for a few trivial metadata-only 2884--echo # changes for InnoDB. 2885--echo # 2886ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 14, ALGORITHM=INSTANT; 2887SHOW CREATE TABLE t1; 2888ALTER TABLE t1 ALTER COLUMN i DROP DEFAULT, ALGORITHM=INSTANT; 2889SHOW CREATE TABLE t1; 2890ALTER TABLE t1 ADD COLUMN j ENUM('a', 'b', 'c'); 2891ALTER TABLE t1 MODIFY COLUMN j ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM=INSTANT; 2892SHOW CREATE TABLE t1; 2893ALTER TABLE t1 RENAME TO t2, ALGORITHM=INSTANT; 2894SHOW CREATE TABLE t2; 2895 2896--echo # 2897--echo # 3) You can still use ALGORITHM=INPLACE for these operations 2898--echo # (even though INSTANT algorithm will be used internally). 2899--echo # 2900ALTER TABLE t2 RENAME TO t1, ALGORITHM=INPLACE; 2901SHOW CREATE TABLE t1; 2902ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 15, ALGORITHM=INPLACE; 2903SHOW CREATE TABLE t1; 2904ALTER TABLE t1 ALTER COLUMN i DROP DEFAULT, ALGORITHM=INPLACE; 2905SHOW CREATE TABLE t1; 2906ALTER TABLE t1 MODIFY COLUMN j ENUM('a', 'b', 'c', 'd', 'e', 'f', 'g'), ALGORITHM=INPLACE; 2907SHOW CREATE TABLE t1; 2908 2909--echo # 2910--echo # 4) However, some operations are not supported as INSTANT. 2911--echo # 2912--error ER_ALTER_OPERATION_NOT_SUPPORTED 2913ALTER TABLE t1 ADD KEY(j), ALGORITHM=INSTANT; 2914--error ER_ALTER_OPERATION_NOT_SUPPORTED 2915ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 15, DROP COLUMN j, ALGORITHM=INSTANT; 2916 2917--echo # 2918--echo # 5) For MyISAM tables we support INSTANT algorithm for metadata-only 2919--echo # changes as well. 2920--echo # 2921DROP TABLE t1; 2922CREATE TABLE t1 (i INT, j ENUM('a', 'b'), KEY(i)) ENGINE=MyISAM; 2923ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 10, ALGORITHM=INSTANT; 2924SHOW CREATE TABLE t1; 2925ALTER TABLE t1 ALTER COLUMN i DROP DEFAULT, ALGORITHM=INSTANT; 2926SHOW CREATE TABLE t1; 2927ALTER TABLE t1 MODIFY COLUMN j ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM=INSTANT; 2928SHOW CREATE TABLE t1; 2929ALTER TABLE t1 CHANGE COLUMN i k INT, ALGORITHM=INSTANT; 2930SHOW CREATE TABLE t1; 2931ALTER TABLE t1 RENAME INDEX i TO k, ALGORITHM=INSTANT; 2932SHOW CREATE TABLE t1; 2933ALTER TABLE t1 RENAME TO t2, ALGORITHM=INSTANT; 2934SHOW CREATE TABLE t2; 2935 2936--echo # 2937--echo # 6) And you can still use ALGORITHM=INPLACE for the same operations 2938--echo # for MyISAM tables too. 2939--echo # 2940ALTER TABLE t2 RENAME TO t1, ALGORITHM=INPLACE; 2941SHOW CREATE TABLE t1; 2942ALTER TABLE t1 ALTER COLUMN k SET DEFAULT 11, ALGORITHM=INPLACE; 2943SHOW CREATE TABLE t1; 2944ALTER TABLE t1 ALTER COLUMN k DROP DEFAULT, ALGORITHM=INPLACE; 2945SHOW CREATE TABLE t1; 2946ALTER TABLE t1 MODIFY COLUMN j ENUM('a', 'b', 'c', 'd', 'e', 'f', 'g'), ALGORITHM=INPLACE; 2947SHOW CREATE TABLE t1; 2948ALTER TABLE t1 CHANGE COLUMN k i INT, ALGORITHM=INPLACE; 2949SHOW CREATE TABLE t1; 2950ALTER TABLE t1 RENAME INDEX k TO i, ALGORITHM=INPLACE; 2951SHOW CREATE TABLE t1; 2952 2953--echo # 2954--echo # 7) Indeed, some options are not supported as INSTANT 2955--echo # 2956--error ER_ALTER_OPERATION_NOT_SUPPORTED 2957ALTER TABLE t1 ADD COLUMN l INT, ALGORITHM=INSTANT; 2958--error ER_ALTER_OPERATION_NOT_SUPPORTED 2959ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 12, DROP COLUMN j, ALGORITHM=INSTANT; 2960DROP TABLE t1; 2961 2962--echo # 2963--echo # Bug#27864226: ASSERTION `(CREATE_INFO->USED_FIELDS & (1L << 8)) == 0 || 2964--echo # 2965 2966CREATE TABLE t1 (a INT) ENGINE=INNODB; 2967 2968--echo # Verify that ALTER can have both a CONVERT clause and a 2969--echo # DEFAULT CHARACTER SET clause with different charsets 2970ALTER TABLE t1 CONVERT TO CHARACTER SET utf16 COLLATE utf16_turkish_ci, 2971DEFAULT CHARACTER SET utf16 COLLATE utf16_slovak_ci; 2972 2973--echo # Verify that last default (utf16) overrides 2974SHOW CREATE TABLE t1; 2975 2976DROP TABLE t1; 2977 2978--echo # BUG#27909771 - [MYSQL 8.0 GA DEBUG BUILD] ASSERTION `(*IDX_EL_IT)->IS_PREFIX() == STATIC_CAST<B 2979--echo # 2980 2981SET SQL_MODE=''; 2982CREATE TABLE t1 SELECT 100000000000000000000000000000000000000000000000000000000000000001 AS c1; 2983ALTER TABLE t1 ADD INDEX (c1); 2984DROP TABLE t1; 2985SET SQL_MODE=default; 2986 2987--echo # 2988--echo # BUG#26848813: INDEXED COLUMN CAN'T BE CHANGED FROM VARCHAR(15) 2989--echo # TO VARCHAR(40) INSTANTANEOUSLY 2990 2991SET @orig_sql_mode = @@sql_mode; 2992 2993--echo # Tests where an error is reported under strict mode when the index 2994--echo # limit exceeds the maximum supported length by SE. 2995 2996--error ER_TOO_LONG_KEY 2997CREATE TABLE t1 (fld1 VARCHAR(768), KEY(fld1)) CHARSET latin1 ENGINE =InnoDB 2998ROW_FORMAT= COMPACT; 2999 3000--error ER_TOO_LONG_KEY 3001CREATE TABLE t2 (fld1 VARCHAR(3073), KEY(fld1)) CHARSET latin1 ENGINE= InnoDB; 3002 3003--echo # Test with innodb prefix indexes where the index limit is 767 bytes 3004CREATE TABLE t1 (fld1 VARCHAR(767), KEY(fld1)) CHARSET latin1 ENGINE=INNODB 3005ROW_FORMAT=COMPACT; 3006 3007--error ER_TOO_LONG_KEY 3008ALTER TABLE t1 MODIFY fld1 VARCHAR(768), ALGORITHM= INPLACE; 3009 3010--error ER_TOO_LONG_KEY 3011ALTER TABLE t1 MODIFY fld1 VARCHAR(768), ALGORITHM= COPY; 3012 3013--echo # Test with innodb prefix indexes where the index limit is 3072 bytes 3014CREATE TABLE t2 (fld1 VARCHAR(3072), KEY(fld1)) CHARSET latin1 ENGINE=INNODB 3015ROW_FORMAT=DYNAMIC; 3016 3017--error ER_TOO_LONG_KEY 3018ALTER TABLE t2 MODIFY fld1 VARCHAR(3073), ALGORITHM= INPLACE; 3019 3020--error ER_TOO_LONG_KEY 3021ALTER TABLE t2 MODIFY fld1 VARCHAR(3073), ALGORITHM= COPY; 3022 3023DROP TABLE t1, t2; 3024 3025SET sql_mode= ''; 3026 3027--enable_warnings 3028 3029--echo # Test where the indexes are truncated to fit the index limit and 3030--echo # a warning is reported under non-strict mode when the index exceeds 3031--echo # the SE limit. 3032CREATE TABLE t1 (fld1 VARCHAR(768), KEY(fld1)) ENGINE= InnoDB 3033ROW_FORMAT=COMPACT; 3034CREATE TABLE t2 (fld1 VARCHAR(3073), KEY(fld1)) ENGINE= InnoDB; 3035 3036--echo # Test with innodb prefix indexes where the index limit is 767 bytes. 3037CREATE TABLE t3 (fld1 VARCHAR(767), KEY(fld1))ENGINE=INNODB ROW_FORMAT=COMPACT; 3038 3039ALTER TABLE t3 MODIFY fld1 VARCHAR(768), ALGORITHM= INPLACE; 3040 3041ALTER TABLE t3 MODIFY fld1 VARCHAR(800), ALGORITHM= COPY; 3042 3043--echo # Test with innodb prefix indexes where the index limit is 3072 bytes. 3044CREATE TABLE t4 (fld1 VARCHAR(3072), KEY(fld1))ENGINE=INNODB 3045ROW_FORMAT=DYNAMIC; 3046 3047ALTER TABLE t4 MODIFY fld1 VARCHAR(3073), ALGORITHM= INPLACE; 3048 3049ALTER TABLE t4 MODIFY fld1 VARCHAR(3074), ALGORITHM= COPY; 3050 3051--echo # For unique and primary keys, an error is reported even in non-strict 3052--echo # mode. 3053 3054--error ER_TOO_LONG_KEY 3055CREATE TABLE t5(fld1 VARCHAR(768) PRIMARY KEY) ENGINE= InnoDB 3056ROW_FORMAT=COMPACT; 3057 3058--error ER_TOO_LONG_KEY 3059CREATE TABLE t5(fld1 VARCHAR(3073), UNIQUE KEY(fld1)) ENGINE= InnoDB; 3060 3061DROP TABLE t1, t2, t3, t4; 3062 3063SET sql_mode= @orig_sql_mode; 3064 3065--echo # Tests added for coverage. 3066CREATE TABLE t1(fld1 VARCHAR(3), KEY(fld1)) ENGINE=MYISAM; 3067 3068--echo # Conversion of unpacked keys to packed keys reports 3069--echo # error for INPLACE Alter. 3070--error ER_ALTER_OPERATION_NOT_SUPPORTED 3071ALTER TABLE t1 MODIFY fld1 VARCHAR(10), ALGORITHM=INPLACE; 3072 3073--echo # Succeeds with index rebuild. 3074ALTER TABLE t1 MODIFY fld1 VARCHAR(10), ALGORITHM=COPY; 3075 3076--echo # Succeeds since the row format is dynamic. 3077CREATE TABLE t2(fld1 VARCHAR(768), KEY(fld1)) ENGINE= InnoDB ROW_FORMAT= DYNAMIC; 3078 3079--echo # An error is reported when the index exceeds the column size 3080--echo # in both strict and non-strict mode. 3081--error ER_WRONG_SUB_KEY 3082ALTER TABLE t2 ADD INDEX idx1(fld1(769)); 3083 3084SET sql_mode= ''; 3085 3086--error ER_WRONG_SUB_KEY 3087ALTER TABLE t2 ADD INDEX idx1(fld1(769)); 3088 3089--echo # Cleanup. 3090DROP TABLE t1, t2; 3091SET sql_mode= @orig_sql_mode; 3092 3093 3094--echo # 3095--echo # BUG#27164393: ALTER COLUMN SET DEFAULT DOES NOT RETURN ERROR 3096--echo # ON GENERATED COLUMNS 3097 3098 3099CREATE TABLE t1(fld1 INT, fld2 INT GENERATED ALWAYS AS (-fld1) VIRTUAL, 3100 fld3 INT GENERATED ALWAYS AS (-fld1) STORED); 3101 3102--echo # Without patch, does not report an error. 3103--error ER_WRONG_USAGE 3104ALTER TABLE t1 ALTER COLUMN fld2 SET DEFAULT -1; 3105 3106--error ER_WRONG_USAGE 3107ALTER TABLE t1 ALTER COLUMN fld3 SET DEFAULT -1; 3108 3109--echo # Tests added for coverage 3110--error ER_WRONG_USAGE 3111ALTER TABLE t1 CHANGE fld2 fld2 INT GENERATED ALWAYS AS (-fld1) DEFAULT -1; 3112 3113--error ER_WRONG_USAGE 3114ALTER TABLE t1 MODIFY fld3 INT GENERATED ALWAYS AS (-fld1) STORED DEFAULT -1; 3115 3116DROP TABLE t1; 3117 3118 3119--echo # 3120--echo # BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS 3121--echo # 3122 3123--enable_warnings 3124SET GLOBAL max_allowed_packet=17825792; 3125 3126--connect(con1, localhost, root,,) 3127CREATE TABLE t1 (t1_fld1 TEXT) ENGINE=InnoDB; 3128CREATE TABLE t2 (t2_fld1 MEDIUMTEXT) ENGINE=InnoDB; 3129CREATE TABLE t3 (t3_fld1 LONGTEXT) ENGINE=InnoDB; 3130 3131INSERT INTO t1 VALUES (REPEAT('a',300)); 3132INSERT INTO t2 VALUES (REPEAT('b',65680)); 3133INSERT INTO t3 VALUES (REPEAT('c',16777300)); 3134 3135SELECT LENGTH(t1_fld1) FROM t1; 3136SELECT LENGTH(t2_fld1) FROM t2; 3137SELECT LENGTH(t3_fld1) FROM t3; 3138 3139--echo # With strict mode 3140SET SQL_MODE='STRICT_ALL_TABLES'; 3141 3142--error ER_DATA_TOO_LONG 3143ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; 3144--error ER_DATA_TOO_LONG 3145ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; 3146--error ER_DATA_TOO_LONG 3147ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; 3148 3149--echo # With non-strict mode 3150SET SQL_MODE=''; 3151 3152ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; 3153ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; 3154ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; 3155 3156SELECT LENGTH(my_t1_fld1) FROM t1; 3157SELECT LENGTH(my_t2_fld1) FROM t2; 3158SELECT LENGTH(my_t3_fld1) FROM t3; 3159 3160# Cleanup 3161--disconnect con1 3162--source include/wait_until_disconnected.inc 3163 3164--connection default 3165DROP TABLE t1, t2, t3; 3166 3167SET SQL_MODE=default; 3168SET GLOBAL max_allowed_packet=default; 3169 3170 3171--echo # 3172--echo # Testing wl#11605: Alter table with character set conversion as 3173--echo # inplace operation, Step 1, NO INDEX 3174--echo # 3175 3176SET NAMES UTF8MB4; 3177--echo # VARCHAR(512): SWE7 -> BINARY 3178CREATE TABLE t1(c1 VARCHAR(512) CHARSET SWE7); 3179ALTER TABLE t1 MODIFY COLUMN c1 VARCHAR(512) CHARSET BINARY, 3180ALGORITHM = INPLACE; 3181DROP TABLE t1; 3182 3183--echo # VARCHAR(512): ASCII -> BINARY 3184CREATE TABLE t1(c1 VARCHAR(512) CHARSET ASCII); 3185ALTER TABLE t1 MODIFY COLUMN c1 VARCHAR(512) CHARSET BINARY, 3186ALGORITHM = INPLACE; 3187DROP TABLE t1; 3188 3189--echo # VARCHAR(512): UTF8MB4 -> BINARY 3190--echo # Not allowed because 512 binary chars cannot hold all 512 char 3191--echo # utf8mb4 strings 3192CREATE TABLE t1(c1 VARCHAR(512) CHARSET UTF8MB4); 3193INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5)); 3194--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3195ALTER TABLE t1 MODIFY COLUMN c1 VARCHAR(512) CHARSET BINARY, 3196ALGORITHM = INPLACE; 3197 3198--echo # VARCHAR(512) CHARSET UTF8MB4 -> VARCHAR(2048) CHARSET BINARY: 3199--echo # Converting to BINARY and increasing the VARCHAR size accordingly is ok 3200ALTER TABLE t1 MODIFY COLUMN c1 VARCHAR(2048) CHARSET BINARY, 3201ALGORITHM = INPLACE; 3202INSERT INTO t1 VALUES (0xf0909080); 3203SELECT * FROM t1; 3204DROP TABLE t1; 3205 3206--echo # Conversions requiring COPY algorithm 3207CREATE TABLE t1(c1 VARCHAR(512) CHARSET ASCII); 3208INSERT INTO t1 VALUES ('a string'); 3209 3210--echo # VARCHAR(512): ASCII -> SWE7 3211--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3212ALTER TABLE t1 MODIFY COLUMN c1 VARCHAR(512) CHARSET SWE7, ALGORITHM = INPLACE; 3213 3214--echo # VARCHAR(512): ASCII -> UCS2 3215--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3216ALTER TABLE t1 MODIFY COLUMN c1 VARCHAR(512) CHARSET UCS2, ALGORITHM = INPLACE; 3217 3218--echo # VARCHAR(512): ASCII -> UTF8MB4 3219--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3220ALTER TABLE t1 MODIFY COLUMN c1 VARCHAR(512) CHARSET UTF8MB4, 3221ALGORITHM = INPLACE; 3222DROP TABLE t1; 3223 3224--echo # VARCHAR(512): UTF8MB3 -> UTF8MB4 3225CREATE TABLE t1(c1 VARCHAR(512) CHARSET UTF8MB3); 3226INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3227ALTER TABLE t1 MODIFY COLUMN c1 VARCHAR(512) CHARSET UTF8MB4, 3228ALGORITHM = INPLACE; 3229--echo # Insert chars that only can be represented in utf8mb4 3230INSERT INTO t1 VALUES (0xf0909080); 3231SELECT * FROM t1; 3232DROP TABLE t1; 3233 3234--echo # CHAR(1): UTF8MB3 -> UTF8MB4 3235CREATE TABLE t1(c1 CHAR(1) CHARSET UTF8MB3); 3236INSERT INTO t1 VALUES (0xc3a6), (0xc3b8); 3237ALTER TABLE t1 MODIFY COLUMN c1 CHAR(1) CHARSET UTF8MB4, ALGORITHM = INPLACE; 3238SELECT * FROM t1; 3239INSERT INTO t1 VALUES (0xf0909080); 3240SELECT * FROM t1; 3241SHOW CREATE TABLE t1; 3242DROP TABLE t1; 3243 3244--echo # CHAR(31): UTF8MB3 -> UTF8MB4 3245CREATE TABLE t1(c1 CHAR(31) CHARSET UTF8MB3); 3246INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3247ALTER TABLE t1 MODIFY COLUMN c1 CHAR(31) CHARSET UTF8MB4, ALGORITHM = INPLACE; 3248--echo # Insert chars that only can be represented in utf8mb4 3249INSERT INTO t1 VALUES (0xf0909080); 3250SELECT * FROM t1; 3251DROP TABLE t1; 3252 3253--echo # Crossing the boundary of 256 bytes usage, cannot be INPLACE. 3254--echo # 85*3 -> 85*4 and 64*3 -> 64*4 3255CREATE TABLE t1(c1 CHAR(85) CHARSET UTF8MB3); 3256INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3257--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3258ALTER TABLE t1 MODIFY COLUMN c1 CHAR(85) CHARSET UTF8MB4, ALGORITHM = INPLACE; 3259DROP TABLE t1; 3260CREATE TABLE t1(c1 CHAR(64) CHARSET UTF8MB3); 3261INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3262--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3263ALTER TABLE t1 MODIFY COLUMN c1 CHAR(64) CHARSET UTF8MB4, ALGORITHM = INPLACE; 3264SELECT * FROM t1; 3265DROP TABLE t1; 3266 3267--echo # Increase in column size above 256 bytes should be inplace. 3268--echo # 86*3 -> 86*4 3269CREATE TABLE t1(c1 CHAR(86) CHARSET UTF8MB3); 3270INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3271ALTER TABLE t1 MODIFY COLUMN c1 CHAR(86) CHARSET UTF8MB4, ALGORITHM = INPLACE; 3272DROP TABLE t1; 3273 3274--echo # Increase in column size within 256 bytes should be inplace. 3275--echo # 63*3 -> 63*4 3276CREATE TABLE t1(c1 CHAR(63) CHARSET UTF8MB3); 3277INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3278ALTER TABLE t1 MODIFY COLUMN c1 CHAR(63) CHARSET UTF8MB4, ALGORITHM = INPLACE; 3279INSERT INTO t1 VALUES (0xf0909080); 3280SELECT * FROM t1; 3281DROP TABLE t1; 3282 3283--echo # TINYTEXT: UTF8MB3 -> UTF8MB4 3284CREATE TABLE t1(c1 TINYTEXT CHARSET UTF8MB3); 3285INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3286ALTER TABLE t1 MODIFY COLUMN c1 TINYTEXT CHARSET UTF8MB4, ALGORITHM = INPLACE; 3287--echo # Insert chars that only can be represented in utf8mb4 3288INSERT INTO t1 VALUES (0xf0909080); 3289SELECT * FROM t1; 3290DROP TABLE t1; 3291 3292--echo # TEXT: UTF8MB3 -> UTF8MB4 3293CREATE TABLE t1(c1 TEXT CHARSET UTF8MB3); 3294INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3295ALTER TABLE t1 MODIFY COLUMN c1 TEXT CHARSET UTF8MB4, ALGORITHM = INPLACE; 3296--echo # Insert chars that only can be represented in utf8mb4 3297INSERT INTO t1 VALUES (0xf0909080); 3298SELECT * FROM t1; 3299DROP TABLE t1; 3300 3301--echo # MEDIUMTEXT: UTF8MB3 -> UTF8MB4 3302CREATE TABLE t1(c1 MEDIUMTEXT CHARSET UTF8MB3); 3303INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3304ALTER TABLE t1 MODIFY COLUMN c1 MEDIUMTEXT CHARSET UTF8MB4, ALGORITHM = INPLACE; 3305--echo # Insert chars that only can be represented in utf8mb4 3306INSERT INTO t1 VALUES (0xf0909080); 3307SELECT * FROM t1; 3308DROP TABLE t1; 3309 3310--echo # LONGTEXT: UTF8MB3 -> UTF8MB4 3311CREATE TABLE t1(c1 LONGTEXT CHARSET UTF8MB3); 3312INSERT INTO t1 VALUES (CONCAT(0xc3a6, 0xc3b8, 0xc3a5, "text")); 3313ALTER TABLE t1 MODIFY COLUMN c1 LONGTEXT CHARSET UTF8MB4, ALGORITHM = INPLACE; 3314--echo # Insert chars that only can be represented in utf8mb4 3315INSERT INTO t1 VALUES (0xf0909080); 3316SELECT * FROM t1; 3317DROP TABLE t1; 3318 3319--echo # ENUM: UTF8MB3 -> UTF8MB4 3320CREATE TABLE t1(c1 ENUM('a','b','c') CHARSET UTF8MB3); 3321INSERT INTO t1 VALUES ('a'); 3322ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a','b','c', 0xf0909080) CHARSET UTF8MB4, 3323ALGORITHM = INPLACE; 3324--echo # Insert chars that only can be represented in utf8mb4 3325INSERT INTO t1 VALUES (0xf0909080); 3326SELECT * FROM t1; 3327DROP TABLE t1; 3328 3329 3330--echo # ENUM: UTF8MB3 -> UTF8MB4: Inplace rejected, new value not at the end 3331CREATE TABLE t1(c1 ENUM(0xc3a6,0xc3b8,0xc3a5) CHARSET UTF8MB3); 3332--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3333ALTER TABLE t1 MODIFY COLUMN c1 ENUM(0xc3a6,0xf0909080,0xc3b8,0xc3a5) 3334CHARSET UTF8MB4, ALGORITHM = INPLACE; 3335DROP TABLE t1; 3336 3337--echo # ENUM: ASCII -> BINARY 3338CREATE TABLE t1(c1 ENUM('a', 'b','c') CHARSET ASCII); 3339ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a','b','c') CHARSET BINARY, 3340ALGORITHM = INPLACE; 3341DROP TABLE t1; 3342 3343--echo # ENUM: SWE7 -> BINARY 3344CREATE TABLE t1(c1 ENUM('a','b','c') CHARSET SWE7); 3345ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a','b','c') CHARSET BINARY, 3346ALGORITHM = INPLACE; 3347DROP TABLE t1; 3348 3349--echo # ENUM: UTF8MB3 -> BINARY 3350CREATE TABLE t1(c1 ENUM('a','b','c') CHARSET UTF8MB3); 3351ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a','b','c') CHARSET BINARY, 3352ALGORITHM = INPLACE; 3353DROP TABLE t1; 3354 3355--echo # ENUM: UTF8MB4 -> BINARY 3356CREATE TABLE t1(c1 ENUM('a','b','c') CHARSET UTF8MB4); 3357ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a','b','c') CHARSET BINARY, 3358ALGORITHM = INPLACE; 3359DROP TABLE t1; 3360 3361 3362--echo # SET: UTF8MB3 -> UTF8MB4 3363CREATE TABLE t1(c1 SET('b',0xc3a6,0xc3b8,0xc3a5) CHARSET UTF8MB3); 3364INSERT INTO t1 VALUES (CONCAT('b,', 0xc3a6)); 3365ALTER TABLE t1 MODIFY COLUMN c1 SET('b',0xc3a6,0xc3b8,0xc3a5,0xf0909080) 3366CHARSET UTF8MB4, ALGORITHM = INPLACE; 3367--echo # Insert set value which can only be represented in utf8mb4 3368INSERT INTO t1 VALUES (CONCAT('b,', 0xf0909080, ',', 0xc3b8)); 3369SELECT * FROM t1; 3370DROP TABLE t1; 3371 3372--echo # SET: UTF8MB3 -> UTF8MB4: Inplace rejected, new value not at the end 3373CREATE TABLE t1(c1 SET(0xc3a6,0xc3b8,0xc3a5) CHARSET UTF8MB3); 3374--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3375ALTER TABLE t1 MODIFY COLUMN c1 SET(0xc3a6,0xf0909080,0xc3b8,0xc3a5) 3376CHARSET UTF8MB4, ALGORITHM = INPLACE; 3377DROP TABLE t1; 3378 3379--echo # SET: ASCII -> BINARY 3380CREATE TABLE t1(c1 SET('a','b','c') CHARSET ASCII); 3381ALTER TABLE t1 MODIFY COLUMN c1 SET('a','b','c') CHARSET BINARY, 3382ALGORITHM = INPLACE; 3383DROP TABLE t1; 3384 3385--echo # SET: SWE7 -> BINARY 3386CREATE TABLE t1(c1 SET('a','b','c') CHARSET SWE7); 3387ALTER TABLE t1 MODIFY COLUMN c1 SET('a','b','c') CHARSET BINARY, 3388ALGORITHM = INPLACE; 3389DROP TABLE t1; 3390 3391--echo # SET: UTF8MB3 -> BINARY 3392CREATE TABLE t1(c1 SET('a','b','c') CHARSET UTF8MB3); 3393ALTER TABLE t1 MODIFY COLUMN c1 SET('a','b','c') CHARSET BINARY, 3394ALGORITHM = INPLACE; 3395DROP TABLE t1; 3396 3397--echo # SET: UTF8MB4 -> BINARY 3398CREATE TABLE t1(c1 SET('a','b','c') CHARSET UTF8MB4); 3399ALTER TABLE t1 MODIFY COLUMN c1 SET('a','b','c') CHARSET BINARY, 3400ALGORITHM = INPLACE; 3401DROP TABLE t1; 3402 3403--echo # SET operations 3404CREATE TABLE t1 (a SET('a1','a2')); 3405INSERT INTO t1 VALUES ('a1'),('a2'); 3406 3407--echo # No copy: Add new to the end 3408ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'), ALGORITHM = INPLACE; 3409INSERT INTO t1 VALUES ('a2,a3'); 3410SELECT a FROM t1; 3411 3412--echo # Copy: Modify and add new to the end 3413--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3414ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'), ALGORITHM = INPLACE; 3415 3416--echo # Copy: Remove from the end 3417--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3418ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'), ALGORITHM = INPLACE; 3419 3420--echo # Copy: Add new member in the middle 3421--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3422ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','a3'), ALGORITHM = INPLACE; 3423 3424--echo # No copy: Add new to the end 3425ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3','a4'), ALGORITHM = INPLACE; 3426INSERT INTO t1 VALUES ('a3,a4'); 3427SELECT a FROM t1; 3428 3429--echo # Numerical increase (pack length), but adding to the end 3430--echo # It should be possible to do this inplace, but leads to crash in 3431--echo # DML on altered table. 3432--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3433ALTER TABLE t1 MODIFY COLUMN a 3434SET('a1','a2','a3','a4','a5','a6','a7','a8','a9','a10'), ALGORITHM = INPLACE; 3435DROP TABLE t1; 3436 3437 3438--echo # Change charset for Instantly added column 3439--echo # UTF8MB3 -> UTF8MB4 3440CREATE TABLE t1(c1 int, c2 CHAR(1) CHARSET ASCII); 3441INSERT INTO t1 VALUES(1,'a'); 3442ALTER TABLE t1 ADD COLUMN c3 VARCHAR(1) CHARSET UTF8MB3 DEFAULT 'b', ALGORITHM = INSTANT; 3443SELECT * FROM t1; 3444ALTER TABLE t1 MODIFY COLUMN c3 VARCHAR(1) CHARSET UTF8MB4, ALGORITHM = INPLACE; 3445SELECT * FROM t1; 3446SHOW CREATE TABLE t1; 3447DROP TABLE t1; 3448 3449 3450--echo # Change charset of columns used in virtual column 3451--echo # UTF8MB3 -> UTF8MB4 3452 3453CREATE TABLE t1(c1 int, 3454 c2 VARCHAR(1) CHARSET UTF8MB3, 3455 c3 VARCHAR(1) CHARSET UTF8MB3, 3456 c4 VARCHAR(2) GENERATED ALWAYS AS (CONCAT(c2,c3)) virtual); 3457INSERT INTO t1(c1,c2,c3) VALUES(1,'a','b'); 3458SELECT * FROM t1; 3459ALTER TABLE t1 3460 MODIFY COLUMN c2 VARCHAR(1) CHARSET UTF8MB4, 3461 MODIFY COLUMN c3 VARCHAR(1) CHARSET UTF8MB4, ALGORITHM = INPLACE; 3462SELECT * FROM t1; 3463SHOW CREATE TABLE t1; 3464DROP TABLE t1; 3465 3466 3467--echo # There are few negative scenarios 3468--echo # char length cannot be changed during inplace 3469CREATE TABLE t1(c CHAR(1) CHARSET ASCII); 3470INSERT INTO t1 VALUES('a'); 3471SELECT * FROM t1; 3472--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3473ALTER TABLE t1 MODIFY COLUMN c CHAR(4) CHARSET BINARY, ALGORITHM = INPLACE; 3474ALTER TABLE t1 MODIFY COLUMN c CHAR(1) CHARSET BINARY, ALGORITHM = INPLACE; 3475SELECT * FROM t1; 3476DROP TABLE t1; 3477 3478 3479--echo # Charset cannot be changed inplace for indexed columns 3480CREATE TABLE t1(c VARCHAR(1) CHARSET ASCII UNIQUE KEY); 3481INSERT INTO t1 VALUES('a'); 3482SELECT * FROM t1; 3483--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3484ALTER TABLE t1 MODIFY COLUMN c VARCHAR(1) CHARSET BINARY, ALGORITHM = INPLACE; 3485DROP TABLE t1; 3486 3487--echo # Collation cannot be changed inplace for indexed columns even 3488--echo # if the character set stays the same (see bug#30386119). 3489CREATE TABLE t1(c VARCHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci PRIMARY KEY); 3490--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3491ALTER TABLE t1 MODIFY COLUMN c VARCHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_as_ci, ALGORITHM = INPLACE; 3492--echo # Using the copy algorithm works. 3493ALTER TABLE t1 MODIFY COLUMN c VARCHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_as_ci, ALGORITHM = COPY; 3494DROP TABLE t1; 3495 3496--echo # Collation cannot be changed inplace for non-indexed columns if an index 3497--echo # on the column is added in the same statement. 3498CREATE TABLE t1(c VARCHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci); 3499--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3500ALTER TABLE t1 MODIFY COLUMN c VARCHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_as_ci, ADD UNIQUE INDEX(c), ALGORITHM = INPLACE; 3501--echo # Using the copy algorithm works. 3502ALTER TABLE t1 MODIFY COLUMN c VARCHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_as_ci, ADD UNIQUE INDEX(c), ALGORITHM = COPY; 3503DROP TABLE t1; 3504 3505--echo # Add a composite index using a prefix key part. 3506CREATE TABLE t1(i INT, c VARCHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci); 3507--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3508ALTER TABLE t1 MODIFY COLUMN c VARCHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_0900_as_ci, ADD INDEX idx(i, c(5)), ALGORITHM = INPLACE; 3509--echo # Using the copy algorithm works. 3510ALTER TABLE t1 MODIFY COLUMN c VARCHAR(10) CHARSET utf8mb4 COLLATE utf8mb4_0900_as_ci, ADD INDEX idx(i, c(5)), ALGORITHM = COPY; 3511DROP TABLE t1; 3512 3513--echo # Collation can be changed inplace for indexed columns if the index is dropped 3514--echo # in the same statement. 3515CREATE TABLE t1(c VARCHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci, UNIQUE INDEX idx(c)); 3516ALTER TABLE t1 MODIFY COLUMN c VARCHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_as_ci, DROP INDEX idx, ALGORITHM = INPLACE; 3517DROP TABLE t1; 3518 3519--echo # No change (charset or otherwise) which makes 3520--echo # Field::max_display_length exceed 255, can be done inplace 3521CREATE TABLE t1(c VARCHAR(1) CHARSET ASCII); 3522INSERT INTO t1 VALUES('a'); 3523SELECT * FROM t1; 3524--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3525ALTER TABLE t1 MODIFY COLUMN c VARCHAR(256) CHARSET BINARY, ALGORITHM = INPLACE; 3526ALTER TABLE t1 MODIFY COLUMN c VARCHAR(255) CHARSET BINARY, ALGORITHM = INPLACE; 3527DROP TABLE t1; 3528 3529 3530--echo # table level charset change 3531CREATE TABLE t1(c VARCHAR(1)) CHARSET ASCII; 3532INSERT INTO t1 VALUES('a'); 3533SELECT * FROM t1; 3534ALTER TABLE t1 CONVERT TO CHARSET BINARY, ALGORITHM = INPLACE; 3535SELECT * FROM t1; 3536DROP TABLE t1; 3537 3538SET NAMES default; 3539 3540--echo # 3541--echo # Bug #29501324 ADD VIRTUAL FIRST + ADD COLUMN CAUSES ASSERTION FAILURE. 3542--echo # 3543 3544CREATE TABLE t1 (b INT); 3545--echo # The below ALTER TABLE causes assertion failure without the fix. 3546ALTER TABLE t1 ADD gcol INT AS (b + 1) VIRTUAL FIRST, ADD COLUMN a INT; 3547DROP TABLE t1; 3548 3549 3550--echo # 3551--echo # Bug#30943642 ERROR 1846 (0A000) AT LINE 1: ALGORITHM=INPLACE IS NOT 3552--echo # SUPPORTED. REASON: CANNO 3553--echo # 3554 3555CREATE TABLE t1 (c1 3556SET('1234567890123456789a','1234567890123456789b','12345678901234567890c')); 3557ALTER TABLE t1 ADD c2 INT NULL AFTER c1, ALGORITHM=INPLACE; 3558DROP TABLE t1; 3559 3560CREATE TABLE t1 (c1 3561SET('1234567890123456789a','1234567890123456789b','12345678901234567890cd') 3562DEFAULT NULL); 3563 3564--echo # Verify that it is possible to add column to table with set column 3565--echo # having display width in bytes >= 256 3566ALTER TABLE t1 ADD c2 INT NULL AFTER c1, ALGORITHM=INPLACE; 3567DROP TABLE t1; 3568 3569--echo # Enums are not affected, because in order for display width in bytes 3570--echo # to exceed 255 we need an invidual enum value with this this size, 3571--echo # and this is not permitted. 3572--error ER_TOO_LONG_SET_ENUM_VALUE 3573CREATE TABLE t1 (c1 ENUM ('000', '001', 3574'0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef')); 3575 3576CREATE TABLE t1 (c1 SET ('00', '01', '02', '03', '04', '05', '06')); 3577 3578--echo # Verify that a SET change which requires more space still cannot be 3579--echo # done inplace 3580--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3581ALTER TABLE t1 MODIFY COLUMN c1 SET ('00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15'), ALGORITHM=INPLACE; 3582DROP TABLE t1; 3583 3584CREATE TABLE t1 (c1 ENUM ('000', '001', '002', '003')); 3585 3586--echo # Verify that an ENUM change which requires more space still cannot be 3587--echo # done inplace 3588--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 3589ALTER TABLE t1 MODIFY COLUMN c1 ENUM ( 3590'000', '001', '002', '003', '004', '005', '006', '007', '008', '009', '010', '011', '012', '013', '014', '015', '016', '017', '018', '019', '020', '021', '022', '023', '024', '025', '026', '027', '028', '029', '030', '031', '032', '033', '034', '035', '036', '037', '038', '039', '040', '041', '042', '043', '044', '045', '046', '047', '048', '049', '050', '051', '052', '053', '054', '055', '056', '057', '058', '059', '060', '061', '062', '063', '064', '065', '066', '067', '068', '069', '070', '071', '072', '073', '074', '075', '076', '077', '078', '079', '080', '081', '082', '083', '084', '085', '086', '087', '088', '089', '090', '091', '092', '093', '094', '095', '096', '097', '098', '099', '100', '101', '102', '103', '104', '105', '106', '107', '108', '109', '110', '111', '112', '113', '114', '115', '116', '117', '118', '119', '120', '121', '122', '123', '124', '125', '126', '127', '128', '129', '130', '131', '132', '133', '134', '135', '136', '137', '138', '139', '140', '141', '142', '143', '144', '145', '146', '147', '148', '149', '150', '151', '152', '153', '154', '155', '156', '157', '158', '159', '160', '161', '162', '163', '164', '165', '166', '167', '168', '169', '170', '171', '172', '173', '174', '175', '176', '177', '178', '179', '180', '181', '182', '183', '184', '185', '186', '187', '188', '189', '190', '191', '192', '193', '194', '195', '196', '197', '198', '199', '200', '201', '202', '203', '204', '205', '206', '207', '208', '209', '210', '211', '212', '213', '214', '215', '216', '217', '218', '219', '220', '221', '222', '223', '224', '225', '226', '227', '228', '229', '230', '231', '232', '233', '234', '235', '236', '237', '238', '239', '240', '241', '242', '243', '244', '245', '246', '247', '248', '249', '250', '251', '252', '253', '254', '255', '256'), ALGORITHM=INPLACE; 3591DROP TABLE t1; 3592 3593--echo # 3594--echo # Bug#31396191: ASSERTION `LEX->SQL_COMMAND != SQLCOM_ALTER_TABLE' FAILED 3595--echo # 3596 3597--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 3598ALTER TABLE z ADD b INT AS (EXISTS((SELECT 1 UNION DISTINCT SELECT 2) LIMIT 3)); 3599