1--source include/have_innodb.inc 2# 3# Test of alter table 4# 5--disable_warnings 6drop table if exists t1,t2; 7drop database if exists mysqltest; 8--enable_warnings 9set @save_max_allowed_packet=@@global.max_allowed_packet; 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); 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# Check that pack_keys and dynamic length rows are not forced. 37 38CREATE TABLE t1 ( 39GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL, 40LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL, 41NAME varchar(80) DEFAULT '' NOT NULL, 42PRIMARY KEY (GROUP_ID,LANG_ID), 43KEY NAME (NAME)); 44#show table status like "t1"; 45ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null; 46--replace_column 8 # 47SHOW FULL COLUMNS FROM t1; 48DROP TABLE t1; 49 50# 51# Test of ALTER TABLE ... ORDER BY 52# 53 54create table t1 (n int); 55insert into t1 values(9),(3),(12),(10); 56alter table t1 order by n; 57select * from t1; 58drop table t1; 59 60CREATE TABLE t1 ( 61 id int(11) unsigned NOT NULL default '0', 62 category_id tinyint(4) unsigned NOT NULL default '0', 63 type_id tinyint(4) unsigned NOT NULL default '0', 64 body text NOT NULL, 65 user_id int(11) unsigned NOT NULL default '0', 66 status enum('new','old') NOT NULL default 'new', 67 PRIMARY KEY (id) 68) ENGINE=MyISAM; 69 70ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body; 71DROP TABLE t1; 72 73# 74# The following combination found a hang-bug in MyISAM 75# 76 77CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) engine=myisam; 78insert into t1 values (null,"hello"); 79LOCK TABLES t1 WRITE; 80ALTER TABLE t1 ADD Column new_col int not null; 81UNLOCK TABLES; 82OPTIMIZE TABLE t1; 83DROP TABLE t1; 84 85# 86# Drop and add an auto_increment column 87# 88 89create table t1 (i int unsigned not null auto_increment primary key); 90insert into t1 values (null),(null),(null),(null); 91alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i); 92select * from t1; 93drop table t1; 94 95# 96# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1 97# if it exists 98# 99create table t1 (name char(15)); 100insert into t1 (name) values ("current"); 101create database mysqltest; 102create table mysqltest.t1 (name char(15)); 103insert into mysqltest.t1 (name) values ("mysqltest"); 104select * from t1; 105select * from mysqltest.t1; 106--error ER_TABLE_EXISTS_ERROR 107alter table t1 rename mysqltest.t1; 108select * from t1; 109select * from mysqltest.t1; 110drop table t1; 111drop database mysqltest; 112 113# 114# ALTER TABLE ... ENABLE/DISABLE KEYS 115 116create table t1 (n1 int not null, n2 int, n3 int, n4 float, 117 unique(n1), 118 key (n1, n2, n3, n4), 119 key (n2, n3, n4, n1), 120 key (n3, n4, n1, n2), 121 key (n4, n1, n2, n3) ); 122alter table t1 disable keys; 123show keys from t1; 124#let $1=10000; 125let $1=10; 126--disable_query_log 127begin; 128while ($1) 129{ 130 eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND()); 131 dec $1; 132} 133commit; 134--enable_query_log 135alter table t1 enable keys; 136show keys from t1; 137drop table t1; 138 139# 140# Alter table and rename 141# 142 143create table t1 (i int unsigned not null auto_increment primary key); 144alter table t1 rename t2; 145alter table t2 rename t1, add c char(10) comment "no comment"; 146show columns from t1; 147drop table t1; 148 149# implicit analyze 150 151create table t1 (a int, b int); 152let $1=100; 153--disable_query_log 154begin; 155while ($1) 156{ 157 eval insert into t1 values(1,$1), (2,$1), (3, $1); 158 dec $1; 159} 160commit; 161--enable_query_log 162alter table t1 add unique (a,b), add key (b); 163show keys from t1; 164analyze table t1; 165show keys from t1; 166drop table t1; 167 168# 169# Test of ALTER TABLE DELAYED 170# 171 172CREATE TABLE t1 (i int(10), index(i) ) ENGINE=MyISAM; 173ALTER TABLE t1 DISABLE KEYS; 174INSERT DELAYED INTO t1 VALUES(1),(2),(3); 175ALTER TABLE t1 ENABLE KEYS; 176drop table t1; 177 178# 179# Test ALTER TABLE ENABLE/DISABLE keys when things are locked 180# 181 182CREATE TABLE t1 ( 183 Host varchar(16) binary NOT NULL default '', 184 User varchar(16) binary NOT NULL default '', 185 PRIMARY KEY (Host,User) 186) ENGINE=MyISAM; 187 188ALTER TABLE t1 DISABLE KEYS; 189LOCK TABLES t1 WRITE; 190INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); 191SHOW INDEX FROM t1; 192ALTER TABLE t1 ENABLE KEYS; 193UNLOCK TABLES; 194CHECK TABLES t1; 195DROP TABLE t1; 196 197# 198# Test with two keys 199# 200 201CREATE TABLE t1 ( 202 Host varchar(16) binary NOT NULL default '', 203 User varchar(16) binary NOT NULL default '', 204 PRIMARY KEY (Host,User), 205 KEY (Host) 206) ENGINE=MyISAM; 207 208ALTER TABLE t1 DISABLE KEYS; 209SHOW INDEX FROM t1; 210LOCK TABLES t1 WRITE; 211INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); 212SHOW INDEX FROM t1; 213ALTER TABLE t1 ENABLE KEYS; 214SHOW INDEX FROM t1; 215UNLOCK TABLES; 216CHECK TABLES t1; 217 218# Test RENAME with LOCK TABLES 219LOCK TABLES t1 WRITE; 220ALTER TABLE t1 RENAME t2; 221UNLOCK TABLES; 222select * from t2; 223DROP TABLE t2; 224 225# 226# Test disable keys with locking 227# 228CREATE TABLE t1 ( 229 Host varchar(16) binary NOT NULL default '', 230 User varchar(16) binary NOT NULL default '', 231 PRIMARY KEY (Host,User), 232 KEY (Host) 233) ENGINE=MyISAM; 234 235LOCK TABLES t1 WRITE; 236ALTER TABLE t1 DISABLE KEYS; 237SHOW INDEX FROM t1; 238DROP TABLE t1; 239 240# 241# BUG#4717 - check for valid table names 242# 243create table t1 (a int); 244--error ER_WRONG_TABLE_NAME 245alter table t1 rename to ``; 246--error ER_WRONG_TABLE_NAME 247rename table t1 to ``; 248drop table t1; 249 250# 251# BUG#6236 - ALTER TABLE MODIFY should set implicit NOT NULL on PK columns 252# 253drop table if exists t1, t2; 254create table t1 ( a varchar(10) not null primary key ) engine=myisam; 255create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1); 256flush tables; 257alter table t1 modify a varchar(10); 258show create table t2; 259flush tables; 260alter table t1 modify a varchar(10) not null; 261show create table t2; 262drop table if exists t1, t2; 263 264# The following is also part of bug #6236 (CREATE TABLE didn't properly count 265# not null columns for primary keys) 266 267create 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; 268insert ignore into t1 (a) values(1); 269--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 19 X 270show table status like 't1'; 271alter table t1 modify a int; 272--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 19 X 273show table status like 't1'; 274drop table t1; 275create 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; 276insert ignore into t1 (a) values(1); 277--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X 19 X 278show table status like 't1'; 279drop table t1; 280 281# 282# Test that data get converted when character set is changed 283# Test that data doesn't get converted when src or dst is BINARY/BLOB 284# 285set names koi8r; 286create table t1 (a char(10) character set koi8r); 287insert into t1 values ('����'); 288select a,hex(a) from t1; 289alter table t1 change a a char(10) character set cp1251; 290select a,hex(a) from t1; 291alter table t1 change a a binary(4); 292select a,hex(a) from t1; 293alter table t1 change a a char(10) character set cp1251; 294select a,hex(a) from t1; 295alter table t1 change a a char(10) character set koi8r; 296select a,hex(a) from t1; 297alter table t1 change a a varchar(10) character set cp1251; 298select a,hex(a) from t1; 299alter table t1 change a a char(10) character set koi8r; 300select a,hex(a) from t1; 301alter table t1 change a a text character set cp1251; 302select a,hex(a) from t1; 303alter table t1 change a a char(10) character set koi8r; 304select a,hex(a) from t1; 305delete from t1; 306 307# 308# Test ALTER TABLE .. CHARACTER SET .. 309# 310show create table t1; 311alter table t1 DEFAULT CHARACTER SET latin1; 312show create table t1; 313alter table t1 CONVERT TO CHARACTER SET latin1; 314show create table t1; 315alter table t1 DEFAULT CHARACTER SET cp1251; 316show create table t1; 317 318drop table t1; 319 320# 321# Bug#2821 322# Test that table CHARACTER SET does not affect blobs 323# 324create table t1 (myblob longblob,mytext longtext) 325default charset latin1 collate latin1_general_cs; 326show create table t1; 327alter table t1 character set latin2; 328show create table t1; 329drop table t1; 330 331# 332# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY) 333# 334 335CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE); 336ALTER TABLE t1 DROP PRIMARY KEY; 337SHOW CREATE TABLE t1; 338--error ER_CANT_DROP_FIELD_OR_KEY 339ALTER TABLE t1 DROP PRIMARY KEY; 340DROP TABLE t1; 341 342# BUG#3899 343create table t1 (a int, b int, key(a)); 344insert into t1 values (1,1), (2,2); 345--error ER_CANT_DROP_FIELD_OR_KEY 346alter table t1 drop key no_such_key; 347alter table t1 drop key a; 348drop table t1; 349 350# 351# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000) 352# 353# Some platforms (Mac OS X, Windows) will send the error message using small letters. 354CREATE TABLE T12207(a int) ENGINE=MYISAM; 355--replace_result t12207 T12207 356--error ER_ILLEGAL_HA 357ALTER TABLE T12207 DISCARD TABLESPACE; 358DROP TABLE T12207; 359 360# 361# Bug #6479 ALTER TABLE ... changing charset fails for TEXT columns 362# 363# The column's character set was changed but the actual data was not 364# modified. In other words, the values were reinterpreted 365# as UTF8 instead of being converted. 366create table t1 (a text) character set koi8r; 367insert into t1 values (_koi8r'����'); 368select hex(a) from t1; 369alter table t1 convert to character set cp1251; 370select hex(a) from t1; 371drop table t1; 372 373# 374# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix" 375# MySQL should not think that packed field with non-zero decimals is 376# geometry field and allow to create prefix index which is 377# shorter than packed field length. 378# 379create table t1 ( a timestamp ); 380--error ER_WRONG_SUB_KEY 381alter table t1 add unique ( a(1) ); 382drop table t1; 383 384# 385# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table 386# 387# This problem happens if the data change is compatible. 388# Changing to the same type is compatible for example. 389# 390--disable_warnings 391drop table if exists t1; 392--enable_warnings 393create table t1 (a int, key(a)); 394show indexes from t1; 395--echo "this used not to disable the index" 396alter table t1 modify a int, disable keys; 397show indexes from t1; 398 399alter table t1 enable keys; 400show indexes from t1; 401 402alter table t1 modify a bigint, disable keys; 403show indexes from t1; 404 405alter table t1 enable keys; 406show indexes from t1; 407 408alter table t1 add b char(10), disable keys; 409show indexes from t1; 410 411alter table t1 add c decimal(10,2), enable keys; 412show indexes from t1; 413 414--echo "this however did" 415alter table t1 disable keys; 416show indexes from t1; 417 418desc t1; 419 420alter table t1 add d decimal(15,5); 421--echo "The key should still be disabled" 422show indexes from t1; 423 424drop table t1; 425 426--echo "Now will test with one unique index" 427create table t1(a int, b char(10), unique(a)); 428show indexes from t1; 429alter table t1 disable keys; 430show indexes from t1; 431alter table t1 enable keys; 432 433--echo "If no copy on noop change, this won't touch the data file" 434--echo "Unique index, no change" 435alter table t1 modify a int, disable keys; 436show indexes from t1; 437 438--echo "Change the type implying data copy" 439--echo "Unique index, no change" 440alter table t1 modify a bigint, disable keys; 441show indexes from t1; 442 443alter table t1 modify a bigint; 444show indexes from t1; 445 446alter table t1 modify a int; 447show indexes from t1; 448 449drop table t1; 450 451--echo "Now will test with one unique and one non-unique index" 452create table t1(a int, b char(10), unique(a), key(b)); 453show indexes from t1; 454alter table t1 disable keys; 455show indexes from t1; 456alter table t1 enable keys; 457 458 459--echo "If no copy on noop change, this won't touch the data file" 460--echo "The non-unique index will be disabled" 461alter table t1 modify a int, disable keys; 462show indexes from t1; 463alter table t1 enable keys; 464show indexes from t1; 465 466--echo "Change the type implying data copy" 467--echo "The non-unique index will be disabled" 468alter table t1 modify a bigint, disable keys; 469show indexes from t1; 470 471--echo "Change again the type, but leave the indexes as_is" 472alter table t1 modify a int; 473show indexes from t1; 474--echo "Try the same. When data is no copied on similar tables, this is noop" 475alter table t1 modify a int; 476show indexes from t1; 477 478drop table t1; 479 480 481# 482# Bug#11493 - Alter table rename to default database does not work without 483# db name qualifying 484# 485create database mysqltest; 486create table t1 (c1 int); 487# Move table to other database. 488alter table t1 rename mysqltest.t1; 489# Assure that it has moved. 490--error ER_BAD_TABLE_ERROR 491drop table t1; 492# Move table back. 493alter table mysqltest.t1 rename t1; 494# Assure that it is back. 495drop table t1; 496# Now test for correct message if no database is selected. 497# Create t1 in 'test'. 498create table t1 (c1 int); 499# Change to other db. 500use mysqltest; 501# Drop the current db. This de-selects any db. 502drop database mysqltest; 503# Now test for correct message. 504--error ER_NO_DB_ERROR 505alter table test.t1 rename t1; 506# Check that explicit qualifying works even with no selected db. 507alter table test.t1 rename test.t1; 508# Go back to standard 'test' db. 509use test; 510drop table t1; 511 512# 513# BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the 514# table 515# 516CREATE TABLE t1(a INT) ROW_FORMAT=FIXED; 517CREATE INDEX i1 ON t1(a); 518SHOW CREATE TABLE t1; 519DROP INDEX i1 ON t1; 520SHOW CREATE TABLE t1; 521DROP TABLE t1; 522 523# 524# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash 525# 526--disable_warnings 527DROP TABLE IF EXISTS bug24219; 528DROP TABLE IF EXISTS bug24219_2; 529--enable_warnings 530 531CREATE TABLE bug24219 (a INT, INDEX(a)); 532 533SHOW INDEX FROM bug24219; 534 535ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; 536 537SHOW INDEX FROM bug24219_2; 538 539DROP TABLE bug24219_2; 540 541# 542# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts) 543# 544 545--disable_warnings 546drop table if exists table_24562; 547--enable_warnings 548 549create table table_24562( 550 section int, 551 subsection int, 552 title varchar(50)); 553 554insert into table_24562 values 555(1, 0, "Introduction"), 556(1, 1, "Authors"), 557(1, 2, "Acknowledgements"), 558(2, 0, "Basics"), 559(2, 1, "Syntax"), 560(2, 2, "Client"), 561(2, 3, "Server"), 562(3, 0, "Intermediate"), 563(3, 1, "Complex queries"), 564(3, 2, "Stored Procedures"), 565(3, 3, "Stored Functions"), 566(4, 0, "Advanced"), 567(4, 1, "Replication"), 568(4, 2, "Load balancing"), 569(4, 3, "High availability"), 570(5, 0, "Conclusion"); 571 572select * from table_24562; 573 574alter table table_24562 add column reviewer varchar(20), 575order by title; 576 577select * from table_24562; 578 579update table_24562 set reviewer="Me" where section=2; 580update table_24562 set reviewer="You" where section=3; 581 582alter table table_24562 583order by section ASC, subsection DESC; 584 585select * from table_24562; 586 587alter table table_24562 588order by table_24562.subsection ASC, table_24562.section DESC; 589 590select * from table_24562; 591 592--error ER_PARSE_ERROR 593alter table table_24562 order by 12; 594--error ER_PARSE_ERROR 595alter table table_24562 order by (section + 12); 596--error ER_PARSE_ERROR 597alter table table_24562 order by length(title); 598--error ER_PARSE_ERROR 599alter table table_24562 order by (select 12 from dual); 600 601--error ER_BAD_FIELD_ERROR 602alter table table_24562 order by no_such_col; 603 604drop table table_24562; 605 606# End of 4.1 tests 607 608# 609# Bug #14693 (ALTER SET DEFAULT doesn't work) 610# 611 612create table t1 (mycol int(10) not null); 613alter table t1 alter column mycol set default 0; 614desc t1; 615drop table t1; 616 617# 618# Bug#25262 Auto Increment lost when changing Engine type 619# 620 621create table t1(id int(8) primary key auto_increment) engine=heap; 622 623insert into t1 values (null); 624insert into t1 values (null); 625 626select * from t1; 627 628# Set auto increment to 50 629alter table t1 auto_increment = 50; 630 631# Alter to myisam 632alter table t1 engine = myisam; 633 634# This insert should get id 50 635insert into t1 values (null); 636select * from t1; 637 638# Alter to heap again 639alter table t1 engine = heap; 640insert into t1 values (null); 641select * from t1; 642 643drop table t1; 644 645# 646# Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the 647# NO_ZERO_DATE mode. 648# 649set @orig_sql_mode = @@sql_mode; 650set sql_mode="no_zero_date"; 651create table t1(f1 int); 652alter table t1 add column f2 datetime not null, add column f21 date not null; 653insert into t1 values(1,'2000-01-01','2000-01-01'); 654--error 1292 655alter table t1 add column f3 datetime not null; 656--error 1292 657alter table t1 add column f3 date not null; 658--error 1292 659alter table t1 add column f4 datetime not null default '2002-02-02', 660 add column f41 date not null; 661alter table t1 add column f4 datetime not null default '2002-02-02', 662 add column f41 date not null default '2002-02-02'; 663select * from t1; 664drop table t1; 665set sql_mode= @orig_sql_mode; 666 667# 668# Some additional tests for new, faster alter table. Note that most of the 669# whole alter table code is being tested all around the test suite already. 670# 671 672create table t1 (v varchar(32)); 673insert into t1 values ('def'),('abc'),('hij'),('3r4f'); 674select * from t1; 675# Fast alter, no copy performed 676alter table t1 change v v2 varchar(32); 677select * from t1; 678# Fast alter, no copy performed 679alter table t1 change v2 v varchar(64); 680select * from t1; 681update t1 set v = 'lmn' where v = 'hij'; 682select * from t1; 683# Regular alter table 684alter table t1 add i int auto_increment not null primary key first; 685select * from t1; 686update t1 set i=5 where i=3; 687select * from t1; 688alter table t1 change i i bigint; 689select * from t1; 690alter table t1 add unique key (i, v); 691select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn'); 692drop table t1; 693 694# 695# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index 696# without # prefix is not allowed for TEXT columns, while index 697# is defined with prefix. 698# 699create table t1 (t varchar(255) default null, key t (t(80))) 700engine=myisam default charset=latin1; 701alter table t1 change t t text; 702drop table t1; 703 704# 705# Bug #26794: Adding an index with a prefix on a SPATIAL type breaks ALTER 706# TABLE 707# 708CREATE TABLE t1 (a varchar(500)); 709 710ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b); 711SHOW CREATE TABLE t1; 712ALTER TABLE t1 ADD KEY(b(50)); 713SHOW CREATE TABLE t1; 714 715ALTER TABLE t1 ADD c POINT; 716SHOW CREATE TABLE t1; 717 718--error ER_WRONG_SUB_KEY 719CREATE TABLE t2 (a INT, KEY (a(20))); 720 721ALTER TABLE t1 ADD d INT; 722--error ER_WRONG_SUB_KEY 723ALTER TABLE t1 ADD KEY (d(20)); 724 725# the 5.1 part of the test 726--error ER_WRONG_SUB_KEY 727ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30)); 728 729DROP TABLE t1; 730 731# 732# Bug#18038 MySQL server corrupts binary columns data 733# 734 735CREATE TABLE t1 (s CHAR(8) BINARY); 736INSERT INTO t1 VALUES ('test'); 737SELECT LENGTH(s) FROM t1; 738ALTER TABLE t1 MODIFY s CHAR(10) BINARY; 739SELECT LENGTH(s) FROM t1; 740DROP TABLE t1; 741 742CREATE TABLE t1 (s BINARY(8)); 743INSERT INTO t1 VALUES ('test'); 744SELECT LENGTH(s) FROM t1; 745SELECT HEX(s) FROM t1; 746ALTER TABLE t1 MODIFY s BINARY(10); 747SELECT HEX(s) FROM t1; 748SELECT LENGTH(s) FROM t1; 749DROP TABLE t1; 750 751# 752# Bug#19386: Multiple alter causes crashed table 753# The trailing column would get corrupted data, or server could not even read 754# it. 755# 756 757CREATE TABLE t1 (v VARCHAR(3), b INT); 758INSERT INTO t1 VALUES ('abc', 5); 759SELECT * FROM t1; 760ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4); 761SELECT * FROM t1; 762DROP TABLE t1; 763 764 765# 766# Bug#31291 ALTER TABLE CONVERT TO CHARACTER SET does not change some data types 767# 768create table t1 (a tinytext character set latin1); 769alter table t1 convert to character set utf8; 770show create table t1; 771drop table t1; 772create table t1 (a mediumtext character set latin1); 773alter table t1 convert to character set utf8; 774show create table t1; 775drop table t1; 776 777--echo End of 5.0 tests 778 779# 780# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES 781# It should be consistent across all platforms and for all engines 782# (Before 5.1 this was not true as behavior was different between 783# Unix/Windows and transactional/non-transactional tables). 784# See also innodb_mysql.test 785# 786--disable_warnings 787drop table if exists t1, t2, t3; 788--enable_warnings 789create table t1 (i int); 790create table t3 (j int); 791insert into t1 values (); 792insert into t3 values (); 793# Table which is altered under LOCK TABLES it should stay in list of locked 794# tables and be available after alter takes place unless ALTER contains RENAME 795# clause. We should see the new definition of table, of course. 796lock table t1 write, t3 read; 797# Example of so-called 'fast' ALTER TABLE 798alter table t1 modify i int default 1; 799insert into t1 values (); 800select * from t1; 801# And now full-blown ALTER TABLE 802alter table t1 change i c char(10) default "Two"; 803insert into t1 values (); 804select * from t1; 805# If table is renamed then it should be removed from the list 806# of locked tables. 'Fast' ALTER TABLE with RENAME clause: 807alter table t1 modify c char(10) default "Three", rename to t2; 808--error ER_TABLE_NOT_LOCKED 809select * from t1; 810--error ER_TABLE_NOT_LOCKED 811select * from t2; 812select * from t3; 813unlock tables; 814insert into t2 values (); 815select * from t2; 816lock table t2 write, t3 read; 817# Full ALTER TABLE with RENAME 818alter table t2 change c vc varchar(100) default "Four", rename to t1; 819--error ER_TABLE_NOT_LOCKED 820select * from t1; 821--error ER_TABLE_NOT_LOCKED 822select * from t2; 823select * from t3; 824unlock tables; 825insert into t1 values (); 826select * from t1; 827drop tables t1, t3; 828 829 830# 831# Bug#18775 - Temporary table from alter table visible to other threads 832# 833# Check if special characters work and duplicates are detected. 834--disable_warnings 835DROP TABLE IF EXISTS `t+1`, `t+2`; 836--enable_warnings 837CREATE TABLE `t+1` (c1 INT); 838ALTER TABLE `t+1` RENAME `t+2`; 839CREATE TABLE `t+1` (c1 INT); 840--error ER_TABLE_EXISTS_ERROR 841ALTER TABLE `t+1` RENAME `t+2`; 842DROP TABLE `t+1`, `t+2`; 843# 844# Same for temporary tables though these names do not become file names. 845CREATE TEMPORARY TABLE `tt+1` (c1 INT); 846ALTER TABLE `tt+1` RENAME `tt+2`; 847CREATE TEMPORARY TABLE `tt+1` (c1 INT); 848--error ER_TABLE_EXISTS_ERROR 849ALTER TABLE `tt+1` RENAME `tt+2`; 850SHOW CREATE TABLE `tt+1`; 851SHOW CREATE TABLE `tt+2`; 852DROP TABLE `tt+1`, `tt+2`; 853# 854# Check if special characters as in tmp_file_prefix work. 855CREATE TABLE `#sql1` (c1 INT); 856CREATE TABLE `@0023sql2` (c1 INT); 857SHOW TABLES; 858RENAME TABLE `#sql1` TO `@0023sql1`; 859RENAME TABLE `@0023sql2` TO `#sql2`; 860SHOW TABLES; 861ALTER TABLE `@0023sql1` RENAME `#sql-1`; 862ALTER TABLE `#sql2` RENAME `@0023sql-2`; 863SHOW TABLES; 864INSERT INTO `#sql-1` VALUES (1); 865INSERT INTO `@0023sql-2` VALUES (2); 866DROP TABLE `#sql-1`, `@0023sql-2`; 867# 868# Same for temporary tables though these names do not become file names. 869CREATE TEMPORARY TABLE `#sql1` (c1 INT); 870CREATE TEMPORARY TABLE `@0023sql2` (c1 INT); 871SHOW TABLES; 872ALTER TABLE `#sql1` RENAME `@0023sql1`; 873ALTER TABLE `@0023sql2` RENAME `#sql2`; 874SHOW TABLES; 875INSERT INTO `#sql2` VALUES (1); 876INSERT INTO `@0023sql1` VALUES (2); 877SHOW CREATE TABLE `#sql2`; 878SHOW CREATE TABLE `@0023sql1`; 879DROP TABLE `#sql2`, `@0023sql1`; 880 881# 882# Bug #22369: Alter table rename combined with other alterations causes lost tables 883# 884# This problem happens if the data change is compatible. 885# Changing to the same type is compatible for example. 886# 887--disable_warnings 888DROP TABLE IF EXISTS t1; 889DROP TABLE IF EXISTS t2; 890--enable_warnings 891CREATE TABLE t1 ( 892 int_field INTEGER UNSIGNED NOT NULL, 893 char_field CHAR(10), 894 INDEX(`int_field`) 895); 896 897DESCRIBE t1; 898 899SHOW INDEXES FROM t1; 900 901INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); 902--echo "Non-copy data change - new frm, but old data and index files" 903ALTER TABLE t1 904 CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL, 905 RENAME t2; 906 907--error ER_NO_SUCH_TABLE 908SELECT * FROM t1 ORDER BY int_field; 909SELECT * FROM t2 ORDER BY unsigned_int_field; 910DESCRIBE t2; 911DESCRIBE t2; 912ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL; 913DESCRIBE t2; 914 915DROP TABLE t2; 916 917# 918# Bug#28427: Columns were renamed instead of moving by ALTER TABLE. 919# 920CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); 921INSERT INTO t1 VALUES (1, 2, NULL); 922SELECT * FROM t1; 923ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1; 924SELECT * FROM t1; 925ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2; 926SELECT * FROM t1; 927DROP TABLE t1; 928 929# 930# BUG#29957 - alter_table.test fails 931# 932create table t1 (c char(10) default "Two"); 933lock table t1 write; 934insert into t1 values (); 935alter table t1 modify c char(10) default "Three"; 936unlock tables; 937select * from t1; 938check table t1; 939drop table t1; 940 941# 942# Bug#33873: Fast ALTER TABLE doesn't work with multibyte character sets 943# 944 945--disable_warnings 946DROP TABLE IF EXISTS t1; 947--enable_warnings 948CREATE TABLE t1 (id int, c int) character set latin1; 949INSERT INTO t1 VALUES (1,1); 950--enable_info 951ALTER TABLE t1 CHANGE c d int; 952ALTER TABLE t1 CHANGE d c int; 953ALTER TABLE t1 MODIFY c VARCHAR(10); 954ALTER TABLE t1 CHANGE c d varchar(10); 955ALTER TABLE t1 CHANGE d c varchar(10); 956--disable_info 957DROP TABLE t1; 958 959--disable_warnings 960DROP TABLE IF EXISTS t1; 961--enable_warnings 962CREATE TABLE t1 (id int, c int) character set utf8; 963INSERT INTO t1 VALUES (1,1); 964--enable_info 965ALTER TABLE t1 CHANGE c d int; 966ALTER TABLE t1 CHANGE d c int; 967ALTER TABLE t1 MODIFY c VARCHAR(10); 968ALTER TABLE t1 CHANGE c d varchar(10); 969ALTER TABLE t1 CHANGE d c varchar(10); 970--disable_info 971DROP TABLE t1; 972 973# 974# Bug#39372 "Smart" ALTER TABLE not so smart after all. 975# 976create table t1(f1 int not null, f2 int not null, key (f1), key (f2)); 977let $count= 50; 978--disable_query_log 979begin; 980while ($count) 981{ 982 EVAL insert into t1 values (1,1),(1,1),(1,1),(1,1),(1,1); 983 EVAL insert into t1 values (2,2),(2,2),(2,2),(2,2),(2,2); 984 dec $count ; 985} 986commit; 987--enable_query_log 988 989select index_length into @unpaked_keys_size from 990information_schema.tables where table_name='t1'; 991alter table t1 pack_keys=1; 992select index_length into @paked_keys_size from 993information_schema.tables where table_name='t1'; 994select (@unpaked_keys_size > @paked_keys_size); 995 996select max_data_length into @orig_max_data_length from 997information_schema.tables where table_name='t1'; 998alter table t1 max_rows=100; 999select max_data_length into @changed_max_data_length from 1000information_schema.tables where table_name='t1'; 1001select (@orig_max_data_length > @changed_max_data_length); 1002 1003drop table t1; 1004 1005# 1006# Bug #23113: Different behavior on altering ENUM fields between 5.0 and 5.1 1007# 1008CREATE TABLE t1(a INT AUTO_INCREMENT PRIMARY KEY, 1009 b ENUM('a', 'b', 'c') NOT NULL); 1010INSERT INTO t1 (b) VALUES ('a'), ('c'), ('b'), ('b'), ('a'); 1011ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL; 1012SELECT * FROM t1; 1013DROP TABLE t1; 1014 1015# 1016# Test for ALTER column DROP DEFAULT 1017# 1018 1019SET @save_sql_mode=@@sql_mode; 1020SET sql_mode=strict_all_tables; 1021 1022CREATE TABLE t1 (a int NOT NULL default 42); 1023INSERT INTO t1 values (); 1024SELECT * FROM t1; 1025ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT; 1026--error 1364 1027INSERT INTO t1 values (); 1028INSERT INTO t1 (a) VALUES (11); 1029SELECT * FROM t1 ORDER BY a; 1030DROP TABLE t1; 1031SET @@sql_mode=@save_sql_mode; 1032--echo # 1033--echo # Bug#45567: Fast ALTER TABLE broken for enum and set 1034--echo # 1035 1036--disable_warnings 1037DROP TABLE IF EXISTS t1; 1038--enable_warnings 1039 1040CREATE TABLE t1 (a ENUM('a1','a2')); 1041INSERT INTO t1 VALUES ('a1'),('a2'); 1042--enable_info 1043--echo # No copy: No modification 1044ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); 1045--echo # No copy: Add new enumeration to the end 1046ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); 1047--echo # Copy: Modify and add new to the end 1048ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); 1049--echo # Copy: Remove from the end 1050ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); 1051--echo # Copy: Add new enumeration 1052ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); 1053--echo # No copy: Add new enumerations to the end 1054ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); 1055--disable_info 1056DROP TABLE t1; 1057 1058CREATE TABLE t1 (a SET('a1','a2')); 1059INSERT INTO t1 VALUES ('a1'),('a2'); 1060--enable_info 1061--echo # No copy: No modification 1062ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); 1063--echo # No copy: Add new to the end 1064ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); 1065--echo # Copy: Modify and add new to the end 1066ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); 1067--echo # Copy: Remove from the end 1068ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); 1069--echo # Copy: Add new member 1070ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); 1071--echo # No copy: Add new to the end 1072ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); 1073--echo # Copy: Numerical incrase (pack lenght) 1074ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); 1075--disable_info 1076DROP TABLE t1; 1077 1078# 1079# Bug#43508: Renaming timestamp or date column triggers table copy 1080# 1081 1082CREATE TABLE t1 (f1 TIMESTAMP NULL DEFAULT NULL, 1083 f2 INT(11) DEFAULT NULL) ENGINE=MYISAM DEFAULT CHARSET=utf8; 1084 1085INSERT INTO t1 VALUES (NULL, NULL), ("2009-10-09 11:46:19", 2); 1086 1087--echo this should affect no rows as there is no real change 1088--enable_info 1089ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL; 1090--disable_info 1091DROP TABLE t1; 1092 1093 1094--echo # 1095--echo # Bug #31145: ALTER TABLE DROP COLUMN, ADD COLUMN crashes (linux) 1096--echo # or freezes (win) the server 1097--echo # 1098 1099CREATE TABLE t1 (a TEXT, id INT, b INT); 1100ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST; 1101 1102DROP TABLE t1; 1103 1104 1105--echo # 1106--echo # Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION 1107--echo # FIRST CAN CAUSE DATA TO BE CORRUPTED". 1108--echo # 1109--disable_warnings 1110drop table if exists t1; 1111--enable_warnings 1112--echo # Use MyISAM engine as the fact that InnoDB doesn't support 1113--echo # in-place ALTER TABLE in cases when columns are being renamed 1114--echo # hides some bugs. 1115create table t1 (i int, j int) engine=myisam; 1116insert into t1 value (1, 2); 1117--echo # First, test for original problem described in the bug report. 1118select * from t1; 1119--echo # Change of column order by the below ALTER TABLE statement should 1120--echo # affect both column names and column contents. 1121alter table t1 modify column j int first; 1122select * from t1; 1123--echo # Now test for similar problem with the same root. 1124--echo # The below ALTER TABLE should change not only the name but 1125--echo # also the value for the last column of the table. 1126alter table t1 drop column i, add column k int default 0; 1127select * from t1; 1128--echo # Clean-up. 1129drop table t1; 1130 1131 1132--echo End of 5.1 tests 1133 1134# 1135# Bug #31031 ALTER TABLE regression in 5.0 1136# 1137# The ALTER TABLE operation failed with 1138# ERROR 1089 (HY000): Incorrect sub part key; ... 1139# 1140CREATE TABLE t1(c CHAR(10), 1141 i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY); 1142INSERT INTO t1 VALUES('a',2),('b',4),('c',6); 1143ALTER TABLE t1 1144 DROP i, 1145 ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT, 1146 AUTO_INCREMENT = 1; 1147DROP TABLE t1; 1148 1149 1150# 1151# Bug#50542 5.5.x doesn't check length of key prefixes: 1152# corruption and crash results 1153# 1154# This case is related to Bug#31031 (above) 1155# A statement where the index key is larger/wider than 1156# the column type, should cause an error 1157# 1158--error ER_WRONG_SUB_KEY 1159CREATE TABLE t1 (a CHAR(1), PRIMARY KEY (a(255))); 1160 1161# Test other variants of creating indices 1162CREATE TABLE t1 (a CHAR(1)); 1163# ALTER TABLE 1164--error ER_WRONG_SUB_KEY 1165ALTER TABLE t1 ADD PRIMARY KEY (a(20)); 1166--error ER_WRONG_SUB_KEY 1167ALTER TABLE t1 ADD KEY (a(20)); 1168# CREATE INDEX 1169--error ER_WRONG_SUB_KEY 1170CREATE UNIQUE INDEX i1 ON t1 (a(20)); 1171--error ER_WRONG_SUB_KEY 1172CREATE INDEX i2 ON t1 (a(20)); 1173# cleanup 1174DROP TABLE t1; 1175 1176 1177# 1178# Bug #45052 ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns 1179# The alter table fails if 2 or more new fields added and 1180# also added a key with these fields 1181# 1182CREATE TABLE t1 (id int); 1183INSERT INTO t1 VALUES (1), (2); 1184ALTER TABLE t1 ADD COLUMN (f1 INT), ADD COLUMN (f2 INT), ADD KEY f2k(f2); 1185DROP TABLE t1; 1186 1187 1188--echo # 1189--echo # Test for bug #53820 "ALTER a MEDIUMINT column table causes full 1190--echo # table copy". 1191--echo # 1192--disable_warnings 1193DROP TABLE IF EXISTS t1; 1194--enable_warnings 1195CREATE TABLE t1 (a INT, b MEDIUMINT); 1196INSERT INTO t1 VALUES (1, 1), (2, 2); 1197--echo # The below ALTER should not copy table and so no rows should 1198--echo # be shown as affected. 1199--enable_info 1200ALTER TABLE t1 CHANGE a id INT; 1201--disable_info 1202DROP TABLE t1; 1203 1204 1205--echo # 1206--echo # Bug#11754461 CANNOT ALTER TABLE WHEN KEY PREFIX TOO LONG 1207--echo # 1208 1209--disable_warnings 1210DROP DATABASE IF EXISTS db1; 1211--enable_warnings 1212 1213CREATE DATABASE db1 CHARACTER SET utf8; 1214CREATE TABLE db1.t1 (bar TINYTEXT, KEY (bar(100))); 1215ALTER TABLE db1.t1 ADD baz INT; 1216 1217DROP DATABASE db1; 1218 1219 1220--echo # Additional coverage for refactoring which is made as part 1221--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege 1222--echo # to allow temp table operations". 1223--echo # 1224--echo # At some point the below test case failed on assertion. 1225 1226--disable_warnings 1227DROP TABLE IF EXISTS t1; 1228--enable_warnings 1229 1230CREATE TEMPORARY TABLE t1 (i int) ENGINE=MyISAM; 1231 1232--error ER_ILLEGAL_HA 1233ALTER TABLE t1 DISCARD TABLESPACE; 1234 1235DROP TABLE t1; 1236 1237 1238--echo # 1239--echo # Bug#11938039 RE-EXECUTION OF FRM-ONLY ALTER TABLE WITH RENAME 1240--echo # CLAUSE FAILS OR ABORTS SERVER. 1241--echo # 1242--disable_warnings 1243drop table if exists t1; 1244--enable_warnings 1245create table t1 (a int); 1246prepare stmt1 from 'alter table t1 alter column a set default 1, rename to t2'; 1247execute stmt1; 1248rename table t2 to t1; 1249--echo # The below statement should succeed and not emit error or abort server. 1250execute stmt1; 1251deallocate prepare stmt1; 1252drop table t2; 1253 1254--echo # 1255--echo # MDEV-8960 Can't refer the same column twice in one ALTER TABLE 1256--echo # 1257 1258CREATE TABLE t1 ( 1259 `a` int(11) DEFAULT NULL 1260) DEFAULT CHARSET=utf8; 1261 1262ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, 1263ALTER COLUMN `consultant_id` DROP DEFAULT; 1264 1265SHOW CREATE TABLE t1; 1266DROP TABLE t1; 1267 1268CREATE TABLE t1 ( 1269 `a` int(11) DEFAULT NULL 1270) DEFAULT CHARSET=utf8; 1271 1272ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, 1273ALTER COLUMN `consultant_id` SET DEFAULT 2; 1274SHOW CREATE TABLE t1; 1275DROP TABLE t1; 1276 1277CREATE TABLE t1 ( 1278 `a` int(11) DEFAULT NULL 1279) DEFAULT CHARSET=utf8; 1280 1281ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, 1282ALTER COLUMN `consultant_id` DROP DEFAULT; 1283SHOW CREATE TABLE t1; 1284DROP TABLE t1; 1285 1286CREATE TABLE t1 ( 1287 `a` int(11) DEFAULT NULL 1288) DEFAULT CHARSET=utf8; 1289 1290ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, 1291ALTER COLUMN `consultant_id` DROP DEFAULT, 1292MODIFY COLUMN `consultant_id` BIGINT; 1293SHOW CREATE TABLE t1; 1294DROP TABLE t1; 1295 1296--echo # 1297--echo # BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS 1298--echo # 1299 1300SET GLOBAL max_allowed_packet=17825792; 1301 1302--connect(con1, localhost, root,,) 1303CREATE TABLE t1 (t1_fld1 TEXT); 1304CREATE TABLE t2 (t2_fld1 MEDIUMTEXT); 1305CREATE TABLE t3 (t3_fld1 LONGTEXT); 1306 1307INSERT INTO t1 VALUES (REPEAT('a',300)); 1308INSERT INTO t2 VALUES (REPEAT('b',65680)); 1309INSERT INTO t3 VALUES (REPEAT('c',16777300)); 1310 1311SELECT LENGTH(t1_fld1) FROM t1; 1312SELECT LENGTH(t2_fld1) FROM t2; 1313SELECT LENGTH(t3_fld1) FROM t3; 1314 1315--echo # With strict mode 1316SET SQL_MODE='STRICT_ALL_TABLES'; 1317 1318--error ER_DATA_TOO_LONG 1319ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; 1320--error ER_DATA_TOO_LONG 1321ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; 1322--error ER_DATA_TOO_LONG 1323ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; 1324 1325--echo # With non-strict mode 1326SET SQL_MODE=''; 1327 1328ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; 1329ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; 1330ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; 1331 1332SELECT LENGTH(my_t1_fld1) FROM t1; 1333SELECT LENGTH(my_t2_fld1) FROM t2; 1334SELECT LENGTH(my_t3_fld1) FROM t3; 1335 1336# Cleanup 1337--disconnect con1 1338--source include/wait_until_disconnected.inc 1339 1340--connection default 1341DROP TABLE t1, t2, t3; 1342 1343SET SQL_MODE=default; 1344SET GLOBAL max_allowed_packet=@save_max_allowed_packet; 1345 1346# 1347# Test of ALTER TABLE IF [NOT] EXISTS 1348# 1349 1350CREATE TABLE t1 ( 1351 id INT(11) NOT NULL, 1352 x_param INT(11) DEFAULT NULL, 1353 PRIMARY KEY (id) 1354) ENGINE=MYISAM; 1355 1356ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, 1357 ADD COLUMN IF NOT EXISTS lol INT AFTER id; 1358ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; 1359ALTER TABLE t1 DROP COLUMN IF EXISTS lol; 1360ALTER TABLE t1 DROP COLUMN IF EXISTS lol; 1361 1362ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); 1363ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); 1364ALTER TABLE t1 MODIFY IF EXISTS lol INT; 1365 1366DROP INDEX IF EXISTS x_param ON t1; 1367DROP INDEX IF EXISTS x_param ON t1; 1368CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); 1369CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); 1370SHOW CREATE TABLE t1; 1371DROP TABLE t1; 1372 1373CREATE TABLE t1 ( 1374 id INT(11) NOT NULL, 1375 x_param INT(11) DEFAULT NULL, 1376 PRIMARY KEY (id) 1377) ENGINE=INNODB; 1378 1379CREATE TABLE t2 ( 1380 id INT(11) NOT NULL) ENGINE=INNODB; 1381 1382ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, 1383 ADD COLUMN IF NOT EXISTS lol INT AFTER id; 1384ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; 1385ALTER TABLE t1 DROP COLUMN IF EXISTS lol; 1386ALTER TABLE t1 DROP COLUMN IF EXISTS lol; 1387 1388ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); 1389ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); 1390ALTER TABLE t1 MODIFY IF EXISTS lol INT; 1391 1392DROP INDEX IF EXISTS x_param ON t1; 1393DROP INDEX IF EXISTS x_param ON t1; 1394CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); 1395CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); 1396SHOW CREATE TABLE t1; 1397 1398ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id); 1399ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id); 1400ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk; 1401ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk; 1402SHOW CREATE TABLE t2; 1403ALTER TABLE t2 ADD FOREIGN KEY (id) REFERENCES t1(id); 1404ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS t2_ibfk_1(id) REFERENCES t1(id); 1405ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1; 1406ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1; 1407SHOW CREATE TABLE t2; 1408 1409DROP TABLE t2; 1410CREATE TABLE t2 ( 1411 id INT(11) NOT NULL); 1412ALTER TABLE t2 ADD COLUMN a INT, ADD COLUMN IF NOT EXISTS a INT; 1413ALTER TABLE t2 ADD KEY k_id(id), ADD KEY IF NOT EXISTS k_id(id); 1414SHOW CREATE TABLE t2; 1415ALTER TABLE t2 DROP KEY k_id, DROP KEY IF EXISTS k_id; 1416ALTER TABLE t2 DROP COLUMN a, DROP COLUMN IF EXISTS a; 1417SHOW CREATE TABLE t2; 1418 1419DROP TABLE t2; 1420DROP TABLE t1; 1421 1422CREATE TABLE t1 ( 1423 `transaction_id` int(11) NOT NULL DEFAULT '0', 1424 KEY `transaction_id` (`transaction_id`)); 1425ALTER TABLE t1 DROP KEY IF EXISTS transaction_id, ADD PRIMARY KEY IF NOT EXISTS (transaction_id); 1426SHOW CREATE TABLE t1; 1427 1428DROP TABLE t1; 1429 1430--echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't 1431--echo # identify correct column name. 1432--echo # 1433 1434CREATE TABLE t1 (c1 int unsigned , c2 char(100) not null default ''); 1435ALTER TABLE t1 ADD c3 char(16) NOT NULL DEFAULT '' AFTER c2, 1436 MODIFY c2 char(100) NOT NULL DEFAULT '' AFTER c1; 1437SHOW CREATE TABLE t1; 1438DROP TABLE t1; 1439 1440--echo # 1441--echo # WL#5534 Online ALTER, Phase 1 1442--echo # 1443 1444--echo # Single thread tests. 1445--echo # See innodb_mysql_sync.test for multi thread tests. 1446 1447--disable_warnings 1448DROP TABLE IF EXISTS t1; 1449--enable_warnings 1450 1451CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; 1452CREATE TABLE m1(a INT PRIMARY KEY, b INT) engine=MyISAM; 1453INSERT INTO t1 VALUES (1,1), (2,2); 1454INSERT INTO m1 VALUES (1,1), (2,2); 1455 1456--echo # 1457--echo # 1: Test ALGORITHM keyword 1458--echo # 1459 1460--echo # --enable_info allows us to see how many rows were updated 1461--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. 1462 1463--enable_info 1464ALTER TABLE t1 ADD INDEX i1(b); 1465ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; 1466ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; 1467ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; 1468--error ER_UNKNOWN_ALTER_ALGORITHM 1469ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= INVALID; 1470 1471ALTER TABLE m1 ENABLE KEYS; 1472ALTER TABLE m1 ENABLE KEYS, ALGORITHM= DEFAULT; 1473ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY; 1474ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE; 1475--disable_info 1476 1477ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; 1478 1479--echo # 1480--echo # 2: Test ALGORITHM + old_alter_table 1481--echo # 1482 1483--enable_info 1484SET SESSION old_alter_table= 1; 1485ALTER TABLE t1 ADD INDEX i1(b); 1486ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; 1487ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; 1488ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; 1489SET SESSION old_alter_table= 0; 1490--disable_info 1491 1492ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; 1493 1494--echo # 1495--echo # 3: Test unsupported in-place operation 1496--echo # 1497 1498ALTER TABLE t1 ADD COLUMN (c1 INT); 1499ALTER TABLE t1 ADD COLUMN (c2 INT), ALGORITHM= DEFAULT; 1500ALTER TABLE t1 ADD COLUMN (c3 INT), ALGORITHM= COPY; 1501ALTER TABLE t1 ADD COLUMN (c4 INT), ALGORITHM= INPLACE; 1502 1503ALTER TABLE t1 DROP COLUMN c1, DROP COLUMN c2, DROP COLUMN c3, DROP COLUMN c4; 1504 1505--echo # 1506--echo # 4: Test LOCK keyword 1507--echo # 1508 1509--enable_info 1510ALTER TABLE t1 ADD INDEX i1(b), LOCK= DEFAULT; 1511ALTER TABLE t1 ADD INDEX i2(b), LOCK= NONE; 1512ALTER TABLE t1 ADD INDEX i3(b), LOCK= SHARED; 1513ALTER TABLE t1 ADD INDEX i4(b), LOCK= EXCLUSIVE; 1514--error ER_UNKNOWN_ALTER_LOCK 1515ALTER TABLE t1 ADD INDEX i5(b), LOCK= INVALID; 1516--disable_info 1517 1518ALTER TABLE m1 ENABLE KEYS, LOCK= DEFAULT; 1519--error ER_ALTER_OPERATION_NOT_SUPPORTED 1520ALTER TABLE m1 ENABLE KEYS, LOCK= NONE; 1521--error ER_ALTER_OPERATION_NOT_SUPPORTED 1522ALTER TABLE m1 ENABLE KEYS, LOCK= SHARED; 1523ALTER TABLE m1 ENABLE KEYS, LOCK= EXCLUSIVE; 1524 1525ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; 1526 1527--echo # 1528--echo # 5: Test ALGORITHM + LOCK 1529--echo # 1530 1531--enable_info 1532ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE; 1533ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED; 1534ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; 1535--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 1536ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; 1537ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; 1538ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE; 1539 1540--error ER_ALTER_OPERATION_NOT_SUPPORTED 1541ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE; 1542--error ER_ALTER_OPERATION_NOT_SUPPORTED 1543ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; 1544ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; 1545--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 1546ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; 1547--error ER_ALTER_OPERATION_NOT_SUPPORTED 1548ALTER ONLINE TABLE m1 ADD COLUMN c int; 1549# This works because the lock will be SNW for the copy phase. 1550# It will still require exclusive lock for actually enabling keys. 1551ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED; 1552ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= EXCLUSIVE; 1553--disable_info 1554 1555DROP TABLE t1, m1; 1556 1557--echo # 1558--echo # 6: Possible deadlock involving thr_lock.c 1559--echo # 1560 1561CREATE TABLE t1(a INT PRIMARY KEY, b INT); 1562INSERT INTO t1 VALUES (1,1), (2,2); 1563 1564START TRANSACTION; 1565INSERT INTO t1 VALUES (3,3); 1566 1567connect (con1, localhost, root); 1568--echo # Sending: 1569--send ALTER TABLE t1 DISABLE KEYS 1570 1571connection default; 1572--echo # Waiting until ALTER TABLE is blocked. 1573let $wait_condition= 1574 SELECT COUNT(*) = 1 FROM information_schema.processlist 1575 WHERE state = "Waiting for table metadata lock" AND 1576 info = "ALTER TABLE t1 DISABLE KEYS"; 1577--source include/wait_condition.inc 1578UPDATE t1 SET b = 4; 1579COMMIT; 1580 1581connection con1; 1582--echo # Reaping: ALTER TABLE t1 DISABLE KEYS 1583--reap 1584disconnect con1; 1585--source include/wait_until_disconnected.inc 1586 1587connection default; 1588DROP TABLE t1; 1589 1590--echo # 1591--echo # 7: Which operations require copy and which can be done in-place? 1592--echo # 1593--echo # Test which ALTER TABLE operations are done in-place and 1594--echo # which operations are done using temporary table copy. 1595--echo # 1596--echo # --enable_info allows us to see how many rows were updated 1597--echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. 1598--echo # 1599 1600--disable_warnings 1601DROP TABLE IF EXISTS ti1, ti2, ti3, tm1, tm2, tm3; 1602--enable_warnings 1603 1604--echo # Single operation tests 1605 1606CREATE TABLE ti1(a INT NOT NULL, b INT, c INT) engine=InnoDB; 1607CREATE TABLE tm1(a INT NOT NULL, b INT, c INT) engine=MyISAM; 1608CREATE TABLE ti2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=InnoDB; 1609CREATE TABLE tm2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=MyISAM; 1610INSERT INTO ti1 VALUES (1,1,1), (2,2,2); 1611INSERT INTO ti2 VALUES (1,1,1), (2,2,2); 1612INSERT INTO tm1 VALUES (1,1,1), (2,2,2); 1613INSERT INTO tm2 VALUES (1,1,1), (2,2,2); 1614 1615--enable_info 1616ALTER TABLE ti1; 1617ALTER TABLE tm1; 1618 1619ALTER TABLE ti1 ADD COLUMN d VARCHAR(200); 1620ALTER TABLE tm1 ADD COLUMN d VARCHAR(200); 1621ALTER TABLE ti1 ADD COLUMN d2 VARCHAR(200); 1622ALTER TABLE tm1 ADD COLUMN d2 VARCHAR(200); 1623ALTER TABLE ti1 ADD COLUMN e ENUM('a', 'b') FIRST; 1624ALTER TABLE tm1 ADD COLUMN e ENUM('a', 'b') FIRST; 1625ALTER TABLE ti1 ADD COLUMN f INT AFTER a; 1626ALTER TABLE tm1 ADD COLUMN f INT AFTER a; 1627 1628ALTER TABLE ti1 ADD INDEX ii1(b); 1629ALTER TABLE tm1 ADD INDEX im1(b); 1630ALTER TABLE ti1 ADD UNIQUE INDEX ii2 (c); 1631ALTER TABLE tm1 ADD UNIQUE INDEX im2 (c); 1632ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d); 1633ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d); 1634ALTER TABLE ti1 ADD FULLTEXT INDEX ii4 (d2); 1635ALTER TABLE tm1 ADD FULLTEXT INDEX im4 (d2); 1636 1637# Bug#14140038 INCONSISTENT HANDLING OF FULLTEXT INDEXES IN ALTER TABLE 1638--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON 1639ALTER TABLE ti1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; 1640ALTER TABLE ti1 ADD PRIMARY KEY(a); 1641ALTER TABLE tm1 ADD PRIMARY KEY(a); 1642 1643ALTER TABLE ti1 DROP INDEX ii3; 1644ALTER TABLE tm1 DROP INDEX im3; 1645 1646ALTER TABLE ti1 DROP COLUMN d2; 1647ALTER TABLE tm1 DROP COLUMN d2; 1648 1649ALTER TABLE ti1 ADD CONSTRAINT fi1 FOREIGN KEY (b) REFERENCES ti2(a); 1650ALTER TABLE tm1 ADD CONSTRAINT fm1 FOREIGN KEY (b) REFERENCES tm2(a); 1651 1652ALTER TABLE ti1 ALTER COLUMN b SET DEFAULT 1; 1653ALTER TABLE tm1 ALTER COLUMN b SET DEFAULT 1; 1654ALTER TABLE ti1 ALTER COLUMN b DROP DEFAULT; 1655ALTER TABLE tm1 ALTER COLUMN b DROP DEFAULT; 1656 1657# This will set both ALTER_COLUMN_NAME and COLUMN_DEFAULT_VALUE 1658ALTER TABLE ti1 CHANGE COLUMN f g INT; 1659ALTER TABLE tm1 CHANGE COLUMN f g INT; 1660ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20); 1661ALTER TABLE tm1 CHANGE COLUMN g h VARCHAR(20); 1662ALTER TABLE ti1 MODIFY COLUMN e ENUM('a', 'b', 'c'); 1663ALTER TABLE tm1 MODIFY COLUMN e ENUM('a', 'b', 'c'); 1664ALTER TABLE ti1 MODIFY COLUMN e INT; 1665ALTER TABLE tm1 MODIFY COLUMN e INT; 1666# This will set both ALTER_COLUMN_ORDER and COLUMN_DEFAULT_VALUE 1667ALTER TABLE ti1 MODIFY COLUMN e INT AFTER h; 1668ALTER TABLE tm1 MODIFY COLUMN e INT AFTER h; 1669ALTER TABLE ti1 MODIFY COLUMN e INT FIRST; 1670ALTER TABLE tm1 MODIFY COLUMN e INT FIRST; 1671# This will set both ALTER_COLUMN_NOT_NULLABLE and COLUMN_DEFAULT_VALUE 1672--disable_info 1673# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. 1674SET @orig_sql_mode = @@sql_mode; 1675SET @@sql_mode = 'STRICT_TRANS_TABLES'; 1676--enable_info 1677ALTER TABLE ti1 MODIFY COLUMN c INT NOT NULL; 1678--disable_info 1679SET @@sql_mode = @orig_sql_mode; 1680--enable_info 1681ALTER TABLE tm1 MODIFY COLUMN c INT NOT NULL; 1682# This will set both ALTER_COLUMN_NULLABLE and COLUMN_DEFAULT_VALUE 1683ALTER TABLE ti1 MODIFY COLUMN c INT NULL; 1684ALTER TABLE tm1 MODIFY COLUMN c INT NULL; 1685# This will set both ALTER_COLUMN_EQUAL_PACK_LENGTH and COLUMN_DEFAULT_VALUE 1686ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30); 1687ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30); 1688ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30) AFTER d; 1689ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30) AFTER d; 1690 1691ALTER TABLE ti1 DROP COLUMN h; 1692ALTER TABLE tm1 DROP COLUMN h; 1693 1694ALTER TABLE ti1 DROP INDEX ii2; 1695ALTER TABLE tm1 DROP INDEX im2; 1696ALTER TABLE ti1 DROP PRIMARY KEY; 1697ALTER TABLE tm1 DROP PRIMARY KEY; 1698 1699ALTER TABLE ti1 DROP FOREIGN KEY fi1; 1700ALTER TABLE tm1 DROP FOREIGN KEY fm1; 1701 1702ALTER TABLE ti1 RENAME TO ti3; 1703ALTER TABLE tm1 RENAME TO tm3; 1704ALTER TABLE ti3 RENAME TO ti1; 1705ALTER TABLE tm3 RENAME TO tm1; 1706 1707ALTER TABLE ti1 ORDER BY b; 1708ALTER TABLE tm1 ORDER BY b; 1709 1710ALTER TABLE ti1 CONVERT TO CHARACTER SET utf16; 1711ALTER TABLE tm1 CONVERT TO CHARACTER SET utf16; 1712ALTER TABLE ti1 DEFAULT CHARACTER SET utf8; 1713ALTER TABLE tm1 DEFAULT CHARACTER SET utf8; 1714 1715ALTER TABLE ti1 FORCE; 1716ALTER TABLE tm1 FORCE; 1717 1718ALTER TABLE ti1 AUTO_INCREMENT 3; 1719ALTER TABLE tm1 AUTO_INCREMENT 3; 1720ALTER TABLE ti1 AVG_ROW_LENGTH 10; 1721ALTER TABLE tm1 AVG_ROW_LENGTH 10; 1722ALTER TABLE ti1 CHECKSUM 1; 1723ALTER TABLE tm1 CHECKSUM 1; 1724ALTER TABLE ti1 COMMENT 'test'; 1725ALTER TABLE tm1 COMMENT 'test'; 1726ALTER TABLE ti1 MAX_ROWS 100; 1727ALTER TABLE tm1 MAX_ROWS 100; 1728ALTER TABLE ti1 MIN_ROWS 1; 1729ALTER TABLE tm1 MIN_ROWS 1; 1730ALTER TABLE ti1 PACK_KEYS 1; 1731ALTER TABLE tm1 PACK_KEYS 1; 1732 1733--disable_info 1734DROP TABLE ti1, ti2, tm1, tm2; 1735 1736--echo # Tests of >1 operation (InnoDB) 1737 1738CREATE TABLE ti1(a INT PRIMARY KEY AUTO_INCREMENT, b INT) engine=InnoDB; 1739INSERT INTO ti1(b) VALUES (1), (2); 1740 1741--enable_info 1742ALTER TABLE ti1 RENAME TO ti3, ADD INDEX ii1(b); 1743 1744ALTER TABLE ti3 DROP INDEX ii1, AUTO_INCREMENT 5; 1745--disable_info 1746INSERT INTO ti3(b) VALUES (5); 1747--enable_info 1748ALTER TABLE ti3 ADD INDEX ii1(b), AUTO_INCREMENT 7; 1749--disable_info 1750INSERT INTO ti3(b) VALUES (7); 1751SELECT * FROM ti3; 1752 1753DROP TABLE ti3; 1754 1755--echo # 1756--echo # 8: Scenario in which ALTER TABLE was returning an unwarranted 1757--echo # ER_ILLEGAL_HA error at some point during work on this WL. 1758--echo # 1759 1760CREATE TABLE tm1(i INT DEFAULT 1) engine=MyISAM; 1761ALTER TABLE tm1 ADD INDEX ii1(i), ALTER COLUMN i DROP DEFAULT; 1762DROP TABLE tm1; 1763 1764# 1765# MDEV-4435 Server crashes in my_strcasecmp_utf8 on ADD KEY IF NOT EXISTS with implicit name when the key exists. 1766# 1767create table if not exists t1 (i int); 1768alter table t1 add key (i); 1769alter table t1 add key if not exists (i); 1770DROP TABLE t1; 1771 1772# 1773# MDEV-4436 CHANGE COLUMN IF EXISTS does not work and throws wrong warning. 1774# 1775create table t1 (a int); 1776alter table t1 change column if exists a b bigint; 1777show create table t1; 1778DROP TABLE t1; 1779 1780# 1781# MDEV-4437 ALTER TABLE .. ADD UNIQUE INDEX IF NOT EXISTS causes syntax error. 1782# 1783 1784create table t1 (i int); 1785alter table t1 add unique index if not exists idx(i); 1786alter table t1 add unique index if not exists idx(i); 1787show create table t1; 1788DROP TABLE t1; 1789 1790# 1791# MDEV-8358 ADD PRIMARY KEY IF NOT EXISTS -> ERROR 1068 (42000): Multiple primary key 1792# 1793 1794CREATE TABLE t1 ( 1795 `event_id` bigint(20) unsigned NOT NULL DEFAULT '0', 1796 `market_id` bigint(20) unsigned NOT NULL DEFAULT '0', 1797 PRIMARY KEY (`event_id`,`market_id`) 1798 ); 1799ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS event_id (event_id,market_id); 1800DROP TABLE t1; 1801 1802--echo # 1803--echo # MDEV-11126 Crash while altering persistent virtual column 1804--echo # 1805 1806CREATE TABLE `tab1` ( 1807 `id` bigint(20) NOT NULL AUTO_INCREMENT, 1808 `field2` set('option1','option2','option3','option4') NOT NULL, 1809 `field3` set('option1','option2','option3','option4','option5') NOT NULL, 1810 `field4` set('option1','option2','option3','option4') NOT NULL, 1811 `field5` varchar(32) NOT NULL, 1812 `field6` varchar(32) NOT NULL, 1813 `field7` varchar(32) NOT NULL, 1814 `field8` varchar(32) NOT NULL, 1815 `field9` int(11) NOT NULL DEFAULT '1', 1816 `field10` varchar(16) NOT NULL, 1817 `field11` enum('option1','option2','option3') NOT NULL DEFAULT 'option1', 1818 `v_col` varchar(128) AS (IF(field11='option1',CONCAT_WS(":","field1",field2,field3,field4,field5,field6,field7,field8,field9,field10), CONCAT_WS(":","field1",field11,field2,field3,field4,field5,field6,field7,field8,field9,field10))) PERSISTENT, 1819 PRIMARY KEY (`id`) 1820) DEFAULT CHARSET=latin1; 1821 1822ALTER TABLE `tab1` CHANGE COLUMN v_col `v_col` varchar(128); 1823SHOW CREATE TABLE `tab1`; 1824ALTER TABLE `tab1` CHANGE COLUMN v_col `v_col` varchar(128) AS (IF(field11='option1',CONCAT_WS(":","field1",field2,field3,field4,field5,field6,field7,field8,field9,field10), CONCAT_WS(":","field1",field11,field2,field3,field4,field5,field6,field7,field8,field9,field10))) PERSISTENT; 1825SHOW CREATE TABLE `tab1`; 1826DROP TABLE `tab1`; 1827 1828--echo # 1829--echo # MDEV-11548 Reproducible server crash after the 2nd ALTER TABLE ADD FOREIGN KEY IF NOT EXISTS 1830--echo # 1831 1832CREATE TABLE t1 (id INT UNSIGNED NOT NULL PRIMARY KEY); 1833CREATE TABLE t2 (id1 INT UNSIGNED NOT NULL); 1834 1835ALTER TABLE t2 1836ADD FOREIGN KEY IF NOT EXISTS (id1) 1837 REFERENCES t1 (id); 1838 1839ALTER TABLE t2 1840ADD FOREIGN KEY IF NOT EXISTS (id1) 1841REFERENCES t1 (id); 1842 1843DROP TABLE t2; 1844DROP TABLE t1; 1845 1846--echo # 1847--echo # MDEV-6390 CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET. 1848--echo # 1849 1850CREATE TABLE t1 (id int(11) NOT NULL, a int(11) NOT NULL, b int(11)) 1851 ENGINE=InnoDB DEFAULT CHARSET=latin1; 1852SHOW CREATE TABLE t1; 1853ALTER TABLE t1 CONVERT TO CHARACTER SET utf8; 1854SHOW CREATE TABLE t1; 1855DROP TABLE t1; 1856 1857--echo # 1858--echo # MDEV-15308 1859--echo # Assertion `ha_alter_info->alter_info->drop_list.elements > 0' failed 1860--echo # in ha_innodb::prepare_inplace_alter_table 1861--echo # 1862 1863CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; 1864ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP COLUMN b; 1865SHOW CREATE TABLE t1; 1866DROP TABLE t1; 1867 1868CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; 1869ALTER TABLE t1 DROP INDEX IF EXISTS fk, DROP COLUMN b; 1870SHOW CREATE TABLE t1; 1871DROP TABLE t1; 1872 1873CREATE TABLE t1 (a INT, b INT, c INT, KEY(c)) ENGINE=InnoDB; 1874ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP COLUMN c; 1875SHOW CREATE TABLE t1; 1876DROP TABLE t1; 1877 1878CREATE TABLE t1 (a INT, b INT, c INT, KEY c1(c)) ENGINE=InnoDB; 1879ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP INDEX c1; 1880SHOW CREATE TABLE t1; 1881DROP TABLE t1; 1882 1883CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; 1884ALTER TABLE t1 DROP INDEX IF EXISTS fk, DROP COLUMN IF EXISTS c; 1885SHOW CREATE TABLE t1; 1886DROP TABLE t1; 1887 1888--echo # 1889--echo # MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key 1890--echo # 1891CREATE TABLE t1 ( 1892 `ID` BIGINT(20) NOT NULL, 1893 `RANK` MEDIUMINT(4) NOT NULL, 1894 `CHECK_POINT` BIGINT(20) NOT NULL, 1895 UNIQUE INDEX `HORIZON_UIDX01` (`ID`, `RANK`) 1896 ) ENGINE=InnoDB; 1897 1898ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); 1899SHOW CREATE TABLE t1; 1900ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); 1901DROP TABLE t1; 1902 1903--echo # 1904--echo # End of 10.0 tests 1905--echo # 1906 1907--echo # 1908--echo # MDEV-7374 : Losing connection to MySQL while running ALTER TABLE 1909--echo # 1910CREATE TABLE t1(i INT) ENGINE=INNODB; 1911INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 1912INSERT INTO t1 SELECT a.* FROM t1 a, t1 b, t1 c, t1 d, t1 e; 1913ALTER TABLE t1 MODIFY i FLOAT; 1914DROP TABLE t1; 1915 1916--echo # 1917--echo # MDEV-7816 ALTER with DROP INDEX and ADD INDEX .. COMMENT='comment2' ignores the new comment 1918--echo # 1919CREATE TABLE t1(a INT); 1920CREATE INDEX i1 ON t1(a) COMMENT 'comment1'; 1921ALTER TABLE t1 DROP INDEX i1, ADD INDEX i1(a) COMMENT 'comment2'; 1922SHOW CREATE TABLE t1; 1923DROP TABLE t1; 1924 1925--echo # 1926--echo # End of 10.1 tests 1927--echo # 1928 1929--echo # 1930--echo # MDEV-10421 duplicate CHECK CONSTRAINTs 1931--echo # 1932CREATE TABLE t1 (a INT, b INT) engine=myisam; 1933ALTER TABLE t1 ADD CONSTRAINT IF NOT EXISTS `min` CHECK (a+b > 100); 1934--error ER_DUP_CONSTRAINT_NAME 1935ALTER TABLE t1 ADD CONSTRAINT `min` CHECK (a+b > 100); 1936ALTER TABLE t1 ADD CONSTRAINT IF NOT EXISTS `min` CHECK (a+b > 100); 1937ALTER TABLE t1 ADD CONSTRAINT `mini` CHECK (a+b > 100); 1938SHOW CREATE TABLE t1; 1939DROP TABLE t1; 1940--error ER_DUP_CONSTRAINT_NAME 1941CREATE TABLE t1(a INT, b INT, CONSTRAINT min check (a>5), 1942 CONSTRAINT min check (b>5)); 1943 1944# 1945# MDEV-11114 Cannot drop column referenced by CHECK constraint 1946# 1947create table t1 (a int, b int, check(a>b)); 1948--error ER_BAD_FIELD_ERROR 1949alter table t1 drop column a; 1950--error ER_BAD_FIELD_ERROR 1951alter table t1 drop column b, add column b bigint first; 1952alter table t1 drop column a, drop constraint constraint_1; 1953show create table t1; 1954drop table t1; 1955 1956create table t1 (a int, b int, check(a>0)); 1957alter table t1 drop column a; 1958show create table t1; 1959drop table t1; 1960 1961create table t1 (a int, b int, check(a>0)); 1962alter table t1 drop column a, add column a bigint first; 1963show create table t1; 1964drop table t1; 1965 1966create table t1 (a int, b int, c int, unique(a)); 1967alter table t1 drop column a; 1968show create table t1; 1969drop table t1; 1970 1971create table t1 (a int, b int, c int, unique(a,b)); 1972--error ER_KEY_COLUMN_DOES_NOT_EXITS 1973alter table t1 drop column a; 1974alter table t1 drop column a, drop index a; 1975show create table t1; 1976drop table t1; 1977 1978# 1979# MDEV-14694 ALTER COLUMN IF EXISTS .. causes syntax error 1980# 1981 1982create table t1 (i int); 1983alter table t1 alter column if exists a set default 1; 1984alter table t1 alter column if exists a drop default; 1985show create table t1; 1986drop table t1; 1987 1988--echo # 1989--echo # MDEV-13508 Check that rename of columns changes defaults, virtual 1990--echo # columns and constraints 1991--echo # 1992 1993create table t1 (a int, b int, check(a>b)); 1994alter table t1 change column a b int, change column b a int; 1995show create table t1; 1996drop table t1; 1997 1998create table t1 (a int primary key, b int, c int default (a+b) check (a+b>0), 1999 d int as (a+b), 2000 key (b), 2001 constraint test check (a+b > 1)); 2002alter table t1 change b new_b int not null, add column b char(1), add constraint new check (length(b) > 0); 2003show create table t1; 2004drop table t1; 2005 2006--echo # 2007--echo # MDEV-11071: Assertion `thd->transaction.stmt.is_empty()' failed 2008--echo # in Locked_tables_list::unlock_locked_tables 2009--echo # 2010 2011CREATE TABLE t1 (d DATETIME DEFAULT CURRENT_TIMESTAMP, i INT) ENGINE=InnoDB; 2012INSERT INTO t1 (i) VALUES (1),(1); 2013LOCK TABLE t1 WRITE; 2014--error ER_DUP_ENTRY 2015ALTER TABLE t1 ADD UNIQUE(i); 2016 2017# Cleanup 2018UNLOCK TABLES; 2019DROP TABLE t1; 2020 2021 2022--echo # 2023--echo # MDEV-17599 ALTER TABLE DROP CONSTRAINT does not work for foreign keys. 2024--echo # 2025 2026CREATE TABLE t1(id INT PRIMARY KEY, c1 INT) ENGINE= INNODB; 2027CREATE TABLE t2(id INT PRIMARY KEY, c1 INT, c2 INT NOT NULL, 2028 CONSTRAINT sid FOREIGN KEY (`c1`) REFERENCES t1 (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, 2029 CONSTRAINT UNIQUE `ui`(c2)) ENGINE= INNODB; 2030SHOW CREATE TABLE t2; 2031ALTER TABLE t2 DROP CONSTRAINT sid; 2032SHOW CREATE TABLE t2; 2033ALTER TABLE t2 DROP CONSTRAINT ui; 2034SHOW CREATE TABLE t2; 2035ALTER TABLE t2 DROP CONSTRAINT PRIMARY KEY; 2036SHOW CREATE TABLE t2; 2037DROP TABLE t2, t1; 2038 2039--echo # 2040--echo # MDEV-18163: Assertion `table_share->tmp_table != NO_TMP_TABLE || 2041--echo # m_lock_type != 2' failed in handler::ha_rnd_next(); / Assertion 2042--echo # `table_list->table' failed in find_field_in_table_ref / ERROR 1901 2043--echo # (on optimized builds) 2044--echo # 2045 2046 2047CREATE TABLE t1 (k1 varchar(10) DEFAULT 5); 2048CREATE TABLE t2 (i1 int); 2049--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED 2050ALTER TABLE t1 ALTER COLUMN k1 SET DEFAULT (SELECT 1 FROM t2 limit 1); 2051DROP TABLE t1,t2; 2052 2053--echo # 2054--echo # MDEV-25403 ALTER TABLE wrongly checks for field's default value if AFTER is used 2055--echo # 2056create table t1(t int, d date not null); 2057insert into t1 values (1,'2001-1-1'); 2058set sql_mode = "no_zero_date"; 2059alter table t1 change d d date not null after t, add i int; 2060show create table t1; 2061--error ER_TRUNCATED_WRONG_VALUE 2062alter table t1 add x date not null; 2063drop table t1; 2064 2065--echo # 2066--echo # End of 10.2 tests 2067--echo # 2068 2069--echo # 2070--echo # MDEV-17778: Alter table leads to a truncation warning with ANALYZE command 2071--echo # 2072 2073set @save_use_stat_tables= @@use_stat_tables; 2074set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; 2075set @@optimizer_use_condition_selectivity=4; 2076set @@use_stat_tables=PREFERABLY; 2077 2078create table t1 (a int)engine=InnoDB; 2079insert into t1 values (1),(1),(2),(3); 2080 2081analyze table t1; 2082alter table t1 change a b int; 2083analyze table t1; 2084set @@use_stat_tables= @save_use_stat_tables; 2085set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; 2086drop table t1; 2087 2088--echo # 2089--echo # MDEV-18154 Deadlock and assertion upon no-op ALTER under LOCK TABLES 2090--echo # 2091create or replace table t1 (pk int, i int, primary key (pk)) engine myisam; 2092create or replace view v1 as select * from t1; 2093lock table v1 read, t1 write; 2094--error ER_BAD_FIELD_ERROR 2095alter table t1 change f1 f2 int; 2096set max_statement_time= 1; 2097alter table t1 add column if not exists i int after pk; 2098set max_statement_time= 0; 2099drop table t1; 2100drop view v1; 2101 2102--echo # 2103--echo # MDEV-25803 Inplace ALTER breaks MyISAM/Aria tables when order of keys is changed 2104--echo # 2105set @save_default_engine= @@default_storage_engine; 2106--disable_query_log 2107if ($MTR_COMBINATION_INNODB) 2108{ 2109 set default_storage_engine= innodb; 2110} 2111if ($MTR_COMBINATION_ARIA) 2112{ 2113 set default_storage_engine= aria; 2114} 2115--enable_query_log 2116 2117if (!$MTR_COMBINATION_INNODB) 2118{ 2119 --disable_query_log 2120 --disable_result_log 2121 # There is no inplace ADD INDEX for MyISAM/Aria: 2122 create or replace table t1 (x int); 2123 --error ER_ALTER_OPERATION_NOT_SUPPORTED 2124 alter table t1 add unique (x), algorithm=inplace; 2125 --error ER_ALTER_OPERATION_NOT_SUPPORTED 2126 alter table t1 add primary key(x), algorithm=inplace; 2127 --error ER_ALTER_OPERATION_NOT_SUPPORTED 2128 alter table t1 add index(x), algorithm=inplace; 2129 --enable_query_log 2130 --enable_result_log 2131} 2132 2133create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)) engine myisam; 2134alter table t1 change x xx int, algorithm=inplace; 2135check table t1; 2136create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)); 2137alter table t1 change x xx int, algorithm=inplace; 2138check table t1; 2139# cleanup 2140drop table t1; 2141set @@default_storage_engine= @save_default_engine; 2142 2143--echo # 2144--echo # End of 10.3 tests 2145--echo # 2146 2147--echo # 2148--echo # MDEV-22563 Segfault on duplicate free of Item_func_in::array 2149--echo # 2150create or replace table person_principal ( 2151 person_id bigint not null, 2152 insurant_id varchar(10) not null, 2153 principal_id bigint not null, 2154 principal_officer_id bigint not null, 2155 nursing_degree tinyint null, 2156 nursing_degree_valid_from date not null default cast(current_timestamp(6) as date), 2157 carma_user_id bigint not null, 2158 current_date_time timestamp(6) not null default current_timestamp(6) on update current_timestamp(6), 2159 constraint pk_person_principal primary key (person_id asc), 2160 constraint ck_person_principal_nursing_degree check (nursing_degree in (1,2,3,4,5))); 2161 2162create or replace table person_principal_hist ( 2163 person_id bigint not null, 2164 insurant_id varchar(10) not null, 2165 principal_id bigint not null, 2166 principal_officer_id bigint not null, 2167 nursing_degree tinyint null, 2168 nursing_degree_valid_from date not null default cast(now() as date), 2169 carma_user_id bigint not null, 2170 orig_date_time datetime(6) not null, 2171 constraint pk_person_principal_hist primary key (person_id asc, orig_date_time asc), 2172 constraint ck_person_principal_hist_nursing_degree check (nursing_degree in (1,2,3,4,5))); 2173 2174insert into person_principal (person_id, insurant_id, principal_id, principal_officer_id, nursing_degree, nursing_degree_valid_from, carma_user_id) 2175values (1, 'A123456789', 5, 1, 1, '2018-05-06', 1); 2176alter table person_principal add column if not exists date_mask tinyint null; 2177update person_principal set date_mask = 0; 2178alter table person_principal modify column date_mask tinyint not null; 2179drop tables person_principal_hist, person_principal; 2180 2181# 2182# The following ALTER TABLE caused crash in 10.4.13 (Reported on freenode) 2183# 2184 2185CREATE OR REPLACE TABLE `t1` ( `id` varchar(64) NOT NULL, `name` varchar(255) NOT NULL, `extra` text DEFAULT NULL, `password` varchar(128) DEFAULT NULL, `enabled` tinyint(1) DEFAULT NULL, `domain_id` varchar(64) NOT NULL, `default_project_id` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT `CONSTRAINT_1` CHECK (`enabled` in (0,1)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2186insert into t1 (id,name,enabled,domain_id) values (1,"Monty",1,"domain_id"); 2187insert into t1 (id,name,enabled,domain_id) values (2,"Monty2",1,"domain_id2"); 2188ALTER TABLE t1 ADD CONSTRAINT ixu_user2_name_domain_id UNIQUE (domain_id, name); 2189DROP TABLE t1; 2190 2191--echo # 2192--echo # End of 10.4 tests 2193--echo # 2194 2195--echo # 2196--echo # MDEV-16290 ALTER TABLE ... RENAME COLUMN syntax 2197--echo # 2198SET @save_default_engine= @@DEFAULT_STORAGE_ENGINE; 2199--disable_query_log 2200if ($MTR_COMBINATION_INNODB) 2201{ 2202SET DEFAULT_STORAGE_ENGINE= INNODB; 2203} 2204if ($MTR_COMBINATION_ARIA) 2205{ 2206SET DEFAULT_STORAGE_ENGINE= ARIA; 2207} 2208if ($MTR_COMBINATION_HEAP) 2209{ 2210SET DEFAULT_STORAGE_ENGINE= MEMORY; 2211} 2212--enable_query_log 2213let $default_engine= `select @@default_storage_engine`; 2214 2215CREATE TABLE t1(a INT, b VARCHAR(30), c FLOAT); 2216--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2217SHOW CREATE TABLE t1; 2218INSERT INTO t1 VALUES(1,'abcd',1.234); 2219CREATE TABLE t2(a INT, b VARCHAR(30), c FLOAT) ENGINE=MyIsam; 2220SHOW CREATE TABLE t2; 2221INSERT INTO t2 VALUES(1,'abcd',1.234); 2222 2223# Rename one column 2224ALTER TABLE t1 RENAME COLUMN a TO a; 2225--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2226SHOW CREATE TABLE t1; 2227ALTER TABLE t1 RENAME COLUMN a TO m; 2228--error ER_BAD_FIELD_ERROR 2229ALTER TABLE t1 RENAME COLUMN a TO m; 2230ALTER TABLE t1 RENAME COLUMN IF EXISTS a TO m; 2231--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2232SHOW CREATE TABLE t1; 2233SELECT * FROM t1; 2234 2235# Rename multiple column 2236ALTER TABLE t1 RENAME COLUMN m TO x, 2237 RENAME COLUMN b TO y, 2238 RENAME COLUMN c TO z; 2239--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2240SHOW CREATE TABLE t1; 2241SELECT * FROM t1; 2242 2243# Rename multiple columns with MyIsam Engine 2244ALTER TABLE t2 RENAME COLUMN a TO d, RENAME COLUMN b TO e, RENAME COLUMN c to f; 2245SHOW CREATE TABLE t2; 2246SELECT * FROM t2; 2247 2248# Mix different ALTER operations with RENAME COLUMN 2249ALTER TABLE t1 CHANGE COLUMN x a INT, RENAME COLUMN y TO b; 2250--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2251SHOW CREATE TABLE t1; 2252ALTER TABLE t1 CHANGE COLUMN z c DOUBLE, RENAME COLUMN b to b; 2253--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2254SHOW CREATE TABLE t1; 2255ALTER TABLE t1 CHANGE COLUMN a b int, RENAME COLUMN b TO c, CHANGE COLUMN c d FLOAT; 2256--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2257SHOW CREATE TABLE t1; 2258ALTER TABLE t1 ADD COLUMN zz INT, RENAME COLUMN d TO f; 2259--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2260SHOW CREATE TABLE t1; 2261ALTER TABLE t1 DROP COLUMN zz, RENAME COLUMN c TO zz; 2262--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2263SHOW CREATE TABLE t1; 2264ALTER TABLE t1 RENAME COLUMN zz to c, DROP COLUMN f; 2265--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2266SHOW CREATE TABLE t1; 2267ALTER TABLE t1 ADD COLUMN d INT DEFAULT 5, RENAME COLUMN c TO b, DROP COLUMN b; 2268--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2269SHOW CREATE TABLE t1; 2270 2271#Cyclic Rename 2272ALTER TABLE t1 RENAME COLUMN b TO d, RENAME COLUMN d TO b; 2273--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2274SHOW CREATE TABLE t1; 2275 2276# Rename with Indexes 2277ALTER TABLE t1 ADD KEY(b); 2278--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2279SHOW CREATE TABLE t1; 2280ALTER TABLE t1 RENAME COLUMN b TO bb; 2281--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2282SHOW CREATE TABLE t1; 2283SELECT * FROM t1; 2284 2285# Rename with Foreign keys. 2286CREATE TABLE t3(a int, b int, KEY(b)); 2287ALTER TABLE t3 ADD CONSTRAINT FOREIGN KEY(b) REFERENCES t1(bb); 2288--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2289SHOW CREATE TABLE t3; 2290ALTER TABLE t1 RENAME COLUMN bb TO b; 2291--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2292SHOW CREATE TABLE t1; 2293ALTER TABLE t3 RENAME COLUMN b TO c; 2294--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2295SHOW CREATE TABLE t3; 2296 2297# Different Algorithm 2298CREATE TABLE t4(a int); 2299ALTER TABLE t4 RENAME COLUMN a TO aa, ALGORITHM = INPLACE; 2300--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2301SHOW CREATE TABLE t4; 2302ALTER TABLE t4 RENAME COLUMN aa TO a, ALGORITHM = COPY; 2303--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2304SHOW CREATE TABLE t4; 2305DROP TABLE t4; 2306 2307# View, Trigger and SP 2308CREATE VIEW v1 AS SELECT d,e,f FROM t2; 2309CREATE TRIGGER trg1 BEFORE UPDATE on t2 FOR EACH ROW SET NEW.d=OLD.d + 10; 2310CREATE PROCEDURE sp1() INSERT INTO t2(d) VALUES(10); 2311ALTER TABLE t2 RENAME COLUMN d TO g; 2312--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2313SHOW CREATE TABLE t2; 2314SHOW CREATE VIEW v1; 2315--error ER_VIEW_INVALID 2316SELECT * FROM v1; 2317--error ER_BAD_FIELD_ERROR 2318UPDATE t2 SET f = f + 10; 2319--error ER_BAD_FIELD_ERROR 2320CALL sp1(); 2321DROP TRIGGER trg1; 2322DROP PROCEDURE sp1; 2323 2324# Generated Columns 2325if (!$MTR_COMBINATION_HEAP) 2326{ 2327CREATE TABLE t_gen(a INT, b DOUBLE GENERATED ALWAYS AS (SQRT(a))); 2328INSERT INTO t_gen(a) VALUES(4); 2329SELECT * FROM t_gen; 2330--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2331SHOW CREATE TABLE t_gen; 2332ALTER TABLE t_gen RENAME COLUMN a TO c, CHANGE COLUMN b b DOUBLE GENERATED ALWAYS AS (SQRT(c)); 2333SELECT * FROM t_gen; 2334--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2335SHOW CREATE TABLE t_gen; 2336#--error ER_DEPENDENT_BY_GENERATED_COLUMN 2337ALTER TABLE t_gen CHANGE COLUMN c x INT; 2338--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2339show create table t_gen; 2340#--error ER_DEPENDENT_BY_GENERATED_COLUMN 2341ALTER TABLE t_gen RENAME COLUMN x TO a; 2342DROP TABLE t_gen; 2343} 2344 2345 2346# 2347# Negative tests 2348# 2349--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2350SHOW CREATE TABLE t1; 2351 2352# Invalid Syntax 2353--error ER_PARSE_ERROR 2354ALTER TABLE t1 RENAME COLUMN b z; 2355--error ER_PARSE_ERROR 2356ALTER TABLE t1 RENAME COLUMN FROM b TO z; 2357--error ER_PARSE_ERROR 2358ALTER TABLE t1 RENAME COLUMN b TO 1; 2359 2360# Duplicate column name 2361--error ER_BAD_FIELD_ERROR 2362ALTER TABLE t1 RENAME COLUMN b TO e, RENAME COLUMN c TO e; 2363--error ER_DUP_FIELDNAME 2364ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN b TO z; 2365 2366# Multiple operation on same column 2367--error ER_BAD_FIELD_ERROR 2368ALTER TABLE t1 DROP COLUMN b, RENAME COLUMN b TO z; 2369--error ER_BAD_FIELD_ERROR 2370ALTER TABLE t1 RENAME COLUMN b TO b, RENAME COLUMN b TO b; 2371--error ER_CANT_DROP_FIELD_OR_KEY 2372ALTER TABLE t1 RENAME COLUMN b TO c3, DROP COLUMN c3; 2373--error ER_BAD_FIELD_ERROR 2374ALTER TABLE t1 ADD COLUMN z INT, CHANGE COLUMN z y INT, DROP COLUMN y; 2375--error ER_BAD_FIELD_ERROR 2376ALTER TABLE t1 ADD COLUMN z INT, RENAME COLUMN z TO y, DROP COLUMN y; 2377 2378# Invalid column name while renaming 2379--error ER_WRONG_COLUMN_NAME 2380ALTER TABLE t1 RENAME COLUMN b TO `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn`; 2381# This error is different compared to ALTER TABLE ... CHANGE command 2382--error ER_TOO_LONG_IDENT 2383ALTER TABLE t1 CHANGE b `nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn` int; 2384 2385--replace_result $default_engine DEFAULT_ENGINE " PAGE_CHECKSUM=1" "" 2386SHOW CREATE TABLE t1; 2387SELECT * FROM t1; 2388 2389# Cleanup 2390DROP VIEW v1; 2391DROP TABLE t3,t1,t2; 2392SET DEFAULT_STORAGE_ENGINE= @save_default_engine; 2393 2394--echo # 2395--echo # MDEV-7318 RENAME INDEX 2396--echo # 2397 2398--echo # 2399--echo # 1) Tests for syntax and semantics of ALTER TABLE RENAME 2400--echo # KEY/INDEX result. 2401--echo # 2402--echo # 1.a) Both RENAME KEY and RENAME INDEX variants should be 2403--echo # allowed and produce expected results. 2404create table t1 (pk int primary key, i int, j int, key a(i)); 2405alter table t1 rename key a to b; 2406show create table t1; 2407alter table t1 rename index b to c; 2408show create table t1; 2409 2410--echo # 1.b) It should be impossible to rename index that doesn't 2411--echo # exists, dropped or added within the same ALTER TABLE. 2412--error ER_KEY_DOES_NOT_EXISTS 2413alter table t1 rename key d to e; 2414alter table t1 rename key if exists d to e; 2415show create table t1; 2416--error ER_KEY_DOES_NOT_EXISTS 2417alter table t1 drop key c, rename key c to d; 2418show create table t1; 2419--error ER_KEY_DOES_NOT_EXISTS 2420alter table t1 add key d(j), rename key d to e; 2421show create table t1; 2422 2423--echo # 1.c) It should be impossible to rename index to a name 2424--echo # which is already used by another index, or is used 2425--echo # by index which is added within the same ALTER TABLE. 2426alter table t1 add key d(j); 2427--error ER_DUP_KEYNAME 2428alter table t1 rename key c to d; 2429show create table t1; 2430alter table t1 drop key d; 2431--error ER_DUP_KEYNAME 2432alter table t1 add key d(j), rename key c to d; 2433show create table t1; 2434 2435--echo # 1.d) It should be possible to rename index to a name 2436--echo # which belongs to index which is dropped within the 2437--echo # same ALTER TABLE. 2438alter table t1 add key d(j); 2439alter table t1 drop key c, rename key d to c; 2440show create table t1; 2441 2442--echo # 1.e) We disallow renaming from/to PRIMARY as it might 2443--echo # lead to some other key becoming "primary" internally, 2444--echo # which will be interpreted as dropping/addition of 2445--echo # primary key. 2446--error ER_PARSE_ERROR 2447alter table t1 rename key primary to d; 2448show create table t1; 2449--echo # Even using 'funny' syntax. 2450--error ER_WRONG_NAME_FOR_INDEX 2451alter table t1 rename key `primary` to d; 2452show create table t1; 2453--error ER_PARSE_ERROR 2454alter table t1 rename key c to primary; 2455show create table t1; 2456--error ER_WRONG_NAME_FOR_INDEX 2457alter table t1 rename key c to `primary`; 2458show create table t1; 2459drop table t1; 2460 2461 2462--echo # 2463--echo # 2) More complex tests for semantics of ALTER TABLE. 2464--echo # 2465--echo # 2.a) Check that standalone RENAME KEY works as expected 2466--echo # for unique and non-unique indexes. 2467create table t1 (a int, unique u(a), b int, key k(b)); 2468alter table t1 rename key u to uu; 2469show create table t1; 2470alter table t1 rename key k to kk; 2471show create table t1; 2472 2473--echo # 2.b) Check how that this clause can be mixed with other 2474--echo # clauses which don't affect key or its columns. 2475alter table t1 rename key kk to kkk, add column c int; 2476show create table t1; 2477alter table t1 rename key uu to uuu, add key c(c); 2478show create table t1; 2479alter table t1 rename key kkk to k, drop key uuu; 2480show create table t1; 2481alter table t1 rename key k to kk, rename to t2; 2482show create table t2; 2483drop table t2; 2484 2485 2486--echo # 2487--echo # 3) Test coverage for handling of RENAME INDEX clause in 2488--echo # various storage engines and using different ALTER 2489--echo # algorithm. 2490--echo # 2491--echo # 3.a) Test coverage for simple storage engines (MyISAM/Heap). 2492create table t1 (i int, key k(i)) engine=myisam; 2493insert into t1 values (1); 2494create table t2 (i int, key k(i)) engine=memory; 2495insert into t2 values (1); 2496--echo # MyISAM and Heap should be able to handle key renaming in-place. 2497alter table t1 algorithm=inplace, rename key k to kk; 2498alter table t2 algorithm=inplace, rename key k to kk; 2499show create table t1; 2500show create table t2; 2501--echo # So by default in-place algorithm should be chosen. 2502--echo # (ALTER TABLE should report 0 rows affected). 2503--enable_info 2504alter table t1 rename key kk to kkk; 2505alter table t2 rename key kk to kkk; 2506--disable_info 2507show create table t1; 2508show create table t2; 2509--echo # Copy algorithm should work as well. 2510alter table t1 algorithm=copy, rename key kkk to kkkk; 2511alter table t2 algorithm=copy, rename key kkk to kkkk; 2512show create table t1; 2513show create table t2; 2514--echo # When renaming is combined with other in-place operation 2515--echo # it still works as expected (i.e. works in-place). 2516alter table t1 algorithm=inplace, rename key kkkk to k, alter column i set default 100; 2517alter table t2 algorithm=inplace, rename key kkkk to k, alter column i set default 100; 2518show create table t1; 2519show create table t2; 2520--echo # Combining with non-inplace operation results in the whole ALTER 2521--echo # becoming non-inplace. 2522--error ER_ALTER_OPERATION_NOT_SUPPORTED 2523alter table t1 algorithm=inplace, rename key k to kk, add column j int; 2524--error ER_ALTER_OPERATION_NOT_SUPPORTED 2525alter table t2 algorithm=inplace, rename key k to kk, add column j int; 2526drop table t1, t2; 2527 2528--echo # 3.b) Basic tests for InnoDB. More tests can be found in 2529--echo # innodb.innodb_rename_index* 2530create table t1 (i int, key k(i)) engine=innodb; 2531insert into t1 values (1); 2532--echo # Basic rename, inplace algorithm should be chosen 2533--enable_info 2534alter table t1 algorithm=inplace, rename key k to kk; 2535--disable_info 2536show create table t1; 2537--echo # copy algorithm should work as well. 2538--enable_info 2539alter table t1 algorithm=copy, rename key kk to kkk; 2540--disable_info 2541show create table t1; 2542drop table t1; 2543 2544--echo # 2545--echo # 4) Additional coverage for complex cases in which code 2546--echo # in ALTER TABLE comparing old and new table version 2547--echo # got confused. 2548--echo # 2549--echo # Once InnoDB starts to support in-place index renaming the result 2550--echo # of below statements should stay the same. Information about 2551--echo # indexes returned by SHOW CREATE TABLE (from .FRM) and by 2552--echo # InnoDB (from InnoDB data-dictionary) should be consistent. 2553--echo # 2554create table t1 ( a int, b int, c int, d int, 2555 primary key (a), index i1 (b), index i2 (c) ) engine=innodb; 2556alter table t1 add index i1 (d), rename index i1 to x; 2557show create table t1; 2558select i.name as k, f.name as c from information_schema.innodb_sys_tables as t, 2559 information_schema.innodb_sys_indexes as i, 2560 information_schema.innodb_sys_fields as f 2561where t.name='test/t1' and t.table_id = i.table_id and i.index_id = f.index_id 2562order by k, c; 2563drop table t1; 2564create table t1 (a int, b int, c int, d int, 2565 primary key (a), index i1 (b), index i2 (c)) engine=innodb; 2566alter table t1 add index i1 (d), rename index i1 to i2, drop index i2; 2567show create table t1; 2568select i.name as k, f.name as c from information_schema.innodb_sys_tables as t, 2569 information_schema.innodb_sys_indexes as i, 2570 information_schema.innodb_sys_fields as f 2571where t.name='test/t1' and t.table_id = i.table_id and i.index_id = f.index_id 2572order by k, c; 2573drop table t1; 2574 2575--echo # 2576--echo # ALTER TABLE IF EXISTS 2577--echo # 2578 2579create table t1 (a int); 2580alter table if exists t1 add column b int; 2581alter table if exists t2 add column c int; 2582alter table if exists t9 rename t1; 2583alter table if exists t1 rename t2; 2584show create table t2; 2585drop table t2; 2586 2587--echo # 2588--echo # MDEV-22881 Unexpected errors, corrupt output, Valgrind / ASAN errors in Item_ident::print or append_identifier 2589--echo # 2590create table t1 (a int check (a >= 0)); 2591lock tables t1 write; 2592alter table t1 rename column a to a; 2593alter table t1 rename key if exists x to xx; 2594 2595# cleanup 2596unlock tables; 2597drop table t1; 2598 2599--echo # 2600--echo # MDEV-23852 alter table rename column to uppercase doesn't work 2601--echo # 2602create table t1 (abc int); 2603alter table t1 rename column abc to Abc, algorithm=copy; 2604show create table t1; 2605alter table t1 rename column abc to ABc, algorithm=inplace; 2606show create table t1; 2607alter table t1 rename column abc to ABC; 2608show create table t1; 2609drop table t1; 2610 2611--echo # 2612--echo # MDEV-25803 Inplace ALTER breaks MyISAM/Aria tables when order of keys is changed 2613--echo # 2614set @save_default_engine= @@default_storage_engine; 2615--disable_query_log 2616if ($MTR_COMBINATION_INNODB) 2617{ 2618 set default_storage_engine= innodb; 2619} 2620if ($MTR_COMBINATION_ARIA) 2621{ 2622 set default_storage_engine= aria; 2623} 2624--enable_query_log 2625 2626if (!$MTR_COMBINATION_INNODB) 2627{ 2628 --disable_query_log 2629 --disable_result_log 2630 # There is no inplace ADD INDEX for MyISAM/Aria: 2631 create or replace table t1 (x int); 2632 --error ER_ALTER_OPERATION_NOT_SUPPORTED 2633 alter table t1 add unique (x), algorithm=inplace; 2634 --error ER_ALTER_OPERATION_NOT_SUPPORTED 2635 alter table t1 add primary key(x), algorithm=inplace; 2636 --error ER_ALTER_OPERATION_NOT_SUPPORTED 2637 alter table t1 add index(x), algorithm=inplace; 2638 --enable_query_log 2639 --enable_result_log 2640} 2641 2642create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)) engine myisam; 2643alter table t1 change x xx int, algorithm=inplace; 2644check table t1; 2645create or replace table t1 (x int, y int, unique (y), unique (x), primary key(x)); 2646alter table t1 change x xx int, algorithm=inplace; 2647check table t1; 2648# cleanup 2649drop table t1; 2650set @@default_storage_engine= @save_default_engine; 2651 2652--echo # 2653--echo # MDEV-25555 Server crashes in tree_record_pos after INPLACE-recreating index on HEAP table 2654--echo # 2655create table t1 (a int, key idx1(a), key idx2 using btree(a)) engine=memory; 2656alter table t1 rename index idx1 to idx3, algorithm=inplace; 2657delete from t1 where a = 10; 2658--error ER_ALTER_OPERATION_NOT_SUPPORTED 2659alter table t1 drop key idx3, add key idx1(a), algorithm=inplace; 2660delete from t1 where a = 11; 2661# cleanup 2662drop table t1; 2663 2664--echo # 2665--echo # MDEV-23836: Assertion `! is_set() || m_can_overwrite_status' in 2666--echo # Diagnostics_area::set_error_status (interrupted ALTER TABLE under LOCK) 2667--echo # 2668 2669SET @max_session_mem_used_save= @@max_session_mem_used; 2670 2671CREATE TABLE t1 (a INT); 2672SELECT * FROM t1; 2673 2674--error ER_NO_SUCH_TABLE 2675ALTER TABLE x MODIFY xx INT; 2676 2677SET SESSION max_session_mem_used= 8192; 2678LOCK TABLE t1 WRITE; 2679 2680ALTER TABLE t1 CHANGE COLUMN IF EXISTS b c INT; 2681 2682SET SESSION max_session_mem_used = @max_session_mem_used_save; 2683UNLOCK TABLES; 2684DROP TABLE t1; 2685 2686--echo # 2687--echo # End of 10.5 tests 2688--echo # 2689