diff options
Diffstat (limited to 'mysql-test/r/alter_table.test')
| -rw-r--r-- | mysql-test/r/alter_table.test | 1908 |
1 files changed, 0 insertions, 1908 deletions
diff --git a/mysql-test/r/alter_table.test b/mysql-test/r/alter_table.test deleted file mode 100644 index aea1a880a81..00000000000 --- a/mysql-test/r/alter_table.test +++ /dev/null @@ -1,1908 +0,0 @@ -if (`select plugin_auth_version < "5.6.26" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in XtraDB below 5.6.26 -} ---source include/have_innodb.inc -# -# Test of alter table -# ---disable_warnings -drop table if exists t1,t2; -drop database if exists mysqltest; ---enable_warnings - -create table t1 ( -col1 int not null auto_increment primary key, -col2 varchar(30) not null, -col3 varchar (20) not null, -col4 varchar(4) not null, -col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null, -col6 int not null, to_be_deleted int); -insert into t1 values (2,4,3,5,"PENDING",1,7); -alter table t1 -add column col4_5 varchar(20) not null after col4, -add column col7 varchar(30) not null after col5, -add column col8 datetime not null, drop column to_be_deleted, -change column col2 fourth varchar(30) not null after col3, -modify column col6 int not null first; -select * from t1; -drop table t1; - -create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL); -insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12); -alter table t1 add column new_col int, order by payoutid,bandid; -select * from t1; -alter table t1 order by bandid,payoutid; -select * from t1; -drop table t1; - -# Check that pack_keys and dynamic length rows are not forced. - -CREATE TABLE t1 ( -GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL, -LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL, -NAME varchar(80) DEFAULT '' NOT NULL, -PRIMARY KEY (GROUP_ID,LANG_ID), -KEY NAME (NAME)); -#show table status like "t1"; -ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null; ---replace_column 8 # -SHOW FULL COLUMNS FROM t1; -DROP TABLE t1; - -# -# Test of ALTER TABLE ... ORDER BY -# - -create table t1 (n int); -insert into t1 values(9),(3),(12),(10); -alter table t1 order by n; -select * from t1; -drop table t1; - -CREATE TABLE t1 ( - id int(11) unsigned NOT NULL default '0', - category_id tinyint(4) unsigned NOT NULL default '0', - type_id tinyint(4) unsigned NOT NULL default '0', - body text NOT NULL, - user_id int(11) unsigned NOT NULL default '0', - status enum('new','old') NOT NULL default 'new', - PRIMARY KEY (id) -) ENGINE=MyISAM; - -ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body; -DROP TABLE t1; - -# -# The following combination found a hang-bug in MyISAM -# - -CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) engine=myisam; -insert into t1 values (null,"hello"); -LOCK TABLES t1 WRITE; -ALTER TABLE t1 ADD Column new_col int not null; -UNLOCK TABLES; -OPTIMIZE TABLE t1; -DROP TABLE t1; - -# -# Drop and add an auto_increment column -# - -create table t1 (i int unsigned not null auto_increment primary key); -insert into t1 values (null),(null),(null),(null); -alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i); -select * from t1; -drop table t1; - -# -# Bug #2628: 'alter table t1 rename mysqltest.t1' silently drops mysqltest.t1 -# if it exists -# -create table t1 (name char(15)); -insert into t1 (name) values ("current"); -create database mysqltest; -create table mysqltest.t1 (name char(15)); -insert into mysqltest.t1 (name) values ("mysqltest"); -select * from t1; -select * from mysqltest.t1; ---error ER_TABLE_EXISTS_ERROR -alter table t1 rename mysqltest.t1; -select * from t1; -select * from mysqltest.t1; -drop table t1; -drop database mysqltest; - -# -# ALTER TABLE ... ENABLE/DISABLE KEYS - -create table t1 (n1 int not null, n2 int, n3 int, n4 float, - unique(n1), - key (n1, n2, n3, n4), - key (n2, n3, n4, n1), - key (n3, n4, n1, n2), - key (n4, n1, n2, n3) ); -alter table t1 disable keys; -show keys from t1; -#let $1=10000; -let $1=10; ---disable_query_log -begin; -while ($1) -{ - eval insert into t1 values($1,RAND()*1000,RAND()*1000,RAND()); - dec $1; -} -commit; ---enable_query_log -alter table t1 enable keys; -show keys from t1; -drop table t1; - -# -# Alter table and rename -# - -create table t1 (i int unsigned not null auto_increment primary key); -alter table t1 rename t2; -alter table t2 rename t1, add c char(10) comment "no comment"; -show columns from t1; -drop table t1; - -# implicit analyze - -create table t1 (a int, b int); -let $1=100; ---disable_query_log -begin; -while ($1) -{ - eval insert into t1 values(1,$1), (2,$1), (3, $1); - dec $1; -} -commit; ---enable_query_log -alter table t1 add unique (a,b), add key (b); -show keys from t1; -analyze table t1; -show keys from t1; -drop table t1; - -# -# Test of ALTER TABLE DELAYED -# - -CREATE TABLE t1 (i int(10), index(i) ) ENGINE=MyISAM; -ALTER TABLE t1 DISABLE KEYS; -INSERT DELAYED INTO t1 VALUES(1),(2),(3); -ALTER TABLE t1 ENABLE KEYS; -drop table t1; - -# -# Test ALTER TABLE ENABLE/DISABLE keys when things are locked -# - -CREATE TABLE t1 ( - Host varchar(16) binary NOT NULL default '', - User varchar(16) binary NOT NULL default '', - PRIMARY KEY (Host,User) -) ENGINE=MyISAM; - -ALTER TABLE t1 DISABLE KEYS; -LOCK TABLES t1 WRITE; -INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); -SHOW INDEX FROM t1; -ALTER TABLE t1 ENABLE KEYS; -UNLOCK TABLES; -CHECK TABLES t1; -DROP TABLE t1; - -# -# Test with two keys -# - -CREATE TABLE t1 ( - Host varchar(16) binary NOT NULL default '', - User varchar(16) binary NOT NULL default '', - PRIMARY KEY (Host,User), - KEY (Host) -) ENGINE=MyISAM; - -ALTER TABLE t1 DISABLE KEYS; -SHOW INDEX FROM t1; -LOCK TABLES t1 WRITE; -INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); -SHOW INDEX FROM t1; -ALTER TABLE t1 ENABLE KEYS; -SHOW INDEX FROM t1; -UNLOCK TABLES; -CHECK TABLES t1; - -# Test RENAME with LOCK TABLES -LOCK TABLES t1 WRITE; -ALTER TABLE t1 RENAME t2; -UNLOCK TABLES; -select * from t2; -DROP TABLE t2; - -# -# Test disable keys with locking -# -CREATE TABLE t1 ( - Host varchar(16) binary NOT NULL default '', - User varchar(16) binary NOT NULL default '', - PRIMARY KEY (Host,User), - KEY (Host) -) ENGINE=MyISAM; - -LOCK TABLES t1 WRITE; -ALTER TABLE t1 DISABLE KEYS; -SHOW INDEX FROM t1; -DROP TABLE t1; - -# -# BUG#4717 - check for valid table names -# -create table t1 (a int); ---error ER_WRONG_TABLE_NAME -alter table t1 rename to ``; ---error ER_WRONG_TABLE_NAME -rename table t1 to ``; -drop table t1; - -# -# BUG#6236 - ALTER TABLE MODIFY should set implicit NOT NULL on PK columns -# -drop table if exists t1, t2; -create table t1 ( a varchar(10) not null primary key ) engine=myisam; -create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1); -flush tables; -alter table t1 modify a varchar(10); -show create table t2; -flush tables; -alter table t1 modify a varchar(10) not null; -show create table t2; -drop table if exists t1, t2; - -# The following is also part of bug #6236 (CREATE TABLE didn't properly count -# not null columns for primary keys) - -create 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; -insert into t1 (a) values(1); ---replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X -show table status like 't1'; -alter table t1 modify a int; ---replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X -show table status like 't1'; -drop table t1; -create 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; -insert into t1 (a) values(1); ---replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X -show table status like 't1'; -drop table t1; - -# -# Test that data get converted when character set is changed -# Test that data doesn't get converted when src or dst is BINARY/BLOB -# -set names koi8r; -create table t1 (a char(10) character set koi8r); -insert into t1 values ('ÔÅÓÔ'); -select a,hex(a) from t1; -alter table t1 change a a char(10) character set cp1251; -select a,hex(a) from t1; -alter table t1 change a a binary(4); -select a,hex(a) from t1; -alter table t1 change a a char(10) character set cp1251; -select a,hex(a) from t1; -alter table t1 change a a char(10) character set koi8r; -select a,hex(a) from t1; -alter table t1 change a a varchar(10) character set cp1251; -select a,hex(a) from t1; -alter table t1 change a a char(10) character set koi8r; -select a,hex(a) from t1; -alter table t1 change a a text character set cp1251; -select a,hex(a) from t1; -alter table t1 change a a char(10) character set koi8r; -select a,hex(a) from t1; -delete from t1; - -# -# Test ALTER TABLE .. CHARACTER SET .. -# -show create table t1; -alter table t1 DEFAULT CHARACTER SET latin1; -show create table t1; -alter table t1 CONVERT TO CHARACTER SET latin1; -show create table t1; -alter table t1 DEFAULT CHARACTER SET cp1251; -show create table t1; - -drop table t1; - -# -# Bug#2821 -# Test that table CHARACTER SET does not affect blobs -# -create table t1 (myblob longblob,mytext longtext) -default charset latin1 collate latin1_general_cs; -show create table t1; -alter table t1 character set latin2; -show create table t1; -drop table t1; - -# -# Bug 2361 (Don't drop UNIQUE with DROP PRIMARY KEY) -# - -CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE); -ALTER TABLE t1 DROP PRIMARY KEY; -SHOW CREATE TABLE t1; ---error ER_CANT_DROP_FIELD_OR_KEY -ALTER TABLE t1 DROP PRIMARY KEY; -DROP TABLE t1; - -# BUG#3899 -create table t1 (a int, b int, key(a)); -insert into t1 values (1,1), (2,2); ---error ER_CANT_DROP_FIELD_OR_KEY -alter table t1 drop key no_such_key; -alter table t1 drop key a; -drop table t1; - -# -# BUG 12207 alter table ... discard table space on MyISAM table causes ERROR 2013 (HY000) -# -# Some platforms (Mac OS X, Windows) will send the error message using small letters. -CREATE TABLE T12207(a int) ENGINE=MYISAM; ---replace_result t12207 T12207 ---error ER_ILLEGAL_HA -ALTER TABLE T12207 DISCARD TABLESPACE; -DROP TABLE T12207; - -# -# Bug #6479 ALTER TABLE ... changing charset fails for TEXT columns -# -# The column's character set was changed but the actual data was not -# modified. In other words, the values were reinterpreted -# as UTF8 instead of being converted. -create table t1 (a text) character set koi8r; -insert into t1 values (_koi8r'ÔÅÓÔ'); -select hex(a) from t1; -alter table t1 convert to character set cp1251; -select hex(a) from t1; -drop table t1; - -# -# Test for bug #7884 "Able to add invalid unique index on TIMESTAMP prefix" -# MySQL should not think that packed field with non-zero decimals is -# geometry field and allow to create prefix index which is -# shorter than packed field length. -# -create table t1 ( a timestamp ); ---error ER_WRONG_SUB_KEY -alter table t1 add unique ( a(1) ); -drop table t1; - -# -# Bug #24395: ALTER TABLE DISABLE KEYS doesn't work when modifying the table -# -# This problem happens if the data change is compatible. -# Changing to the same type is compatible for example. -# ---disable_warnings -drop table if exists t1; ---enable_warnings -create table t1 (a int, key(a)); -show indexes from t1; ---echo "this used not to disable the index" -alter table t1 modify a int, disable keys; -show indexes from t1; - -alter table t1 enable keys; -show indexes from t1; - -alter table t1 modify a bigint, disable keys; -show indexes from t1; - -alter table t1 enable keys; -show indexes from t1; - -alter table t1 add b char(10), disable keys; -show indexes from t1; - -alter table t1 add c decimal(10,2), enable keys; -show indexes from t1; - ---echo "this however did" -alter table t1 disable keys; -show indexes from t1; - -desc t1; - -alter table t1 add d decimal(15,5); ---echo "The key should still be disabled" -show indexes from t1; - -drop table t1; - ---echo "Now will test with one unique index" -create table t1(a int, b char(10), unique(a)); -show indexes from t1; -alter table t1 disable keys; -show indexes from t1; -alter table t1 enable keys; - ---echo "If no copy on noop change, this won't touch the data file" ---echo "Unique index, no change" -alter table t1 modify a int, disable keys; -show indexes from t1; - ---echo "Change the type implying data copy" ---echo "Unique index, no change" -alter table t1 modify a bigint, disable keys; -show indexes from t1; - -alter table t1 modify a bigint; -show indexes from t1; - -alter table t1 modify a int; -show indexes from t1; - -drop table t1; - ---echo "Now will test with one unique and one non-unique index" -create table t1(a int, b char(10), unique(a), key(b)); -show indexes from t1; -alter table t1 disable keys; -show indexes from t1; -alter table t1 enable keys; - - ---echo "If no copy on noop change, this won't touch the data file" ---echo "The non-unique index will be disabled" -alter table t1 modify a int, disable keys; -show indexes from t1; -alter table t1 enable keys; -show indexes from t1; - ---echo "Change the type implying data copy" ---echo "The non-unique index will be disabled" -alter table t1 modify a bigint, disable keys; -show indexes from t1; - ---echo "Change again the type, but leave the indexes as_is" -alter table t1 modify a int; -show indexes from t1; ---echo "Try the same. When data is no copied on similar tables, this is noop" -alter table t1 modify a int; -show indexes from t1; - -drop table t1; - - -# -# Bug#11493 - Alter table rename to default database does not work without -# db name qualifying -# -create database mysqltest; -create table t1 (c1 int); -# Move table to other database. -alter table t1 rename mysqltest.t1; -# Assure that it has moved. ---error ER_BAD_TABLE_ERROR -drop table t1; -# Move table back. -alter table mysqltest.t1 rename t1; -# Assure that it is back. -drop table t1; -# Now test for correct message if no database is selected. -# Create t1 in 'test'. -create table t1 (c1 int); -# Change to other db. -use mysqltest; -# Drop the current db. This de-selects any db. -drop database mysqltest; -# Now test for correct message. ---error ER_NO_DB_ERROR -alter table test.t1 rename t1; -# Check that explicit qualifying works even with no selected db. -alter table test.t1 rename test.t1; -# Go back to standard 'test' db. -use test; -drop table t1; - -# -# BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the -# table -# -CREATE TABLE t1(a INT) ROW_FORMAT=FIXED; -CREATE INDEX i1 ON t1(a); -SHOW CREATE TABLE t1; -DROP INDEX i1 ON t1; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -# -# Bug#24219 - ALTER TABLE ... RENAME TO ... , DISABLE KEYS leads to crash -# ---disable_warnings -DROP TABLE IF EXISTS bug24219; -DROP TABLE IF EXISTS bug24219_2; ---enable_warnings - -CREATE TABLE bug24219 (a INT, INDEX(a)); - -SHOW INDEX FROM bug24219; - -ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; - -SHOW INDEX FROM bug24219_2; - -DROP TABLE bug24219_2; - -# -# Bug#24562 (ALTER TABLE ... ORDER BY ... with complex expression asserts) -# - ---disable_warnings -drop table if exists table_24562; ---enable_warnings - -create table table_24562( - section int, - subsection int, - title varchar(50)); - -insert into table_24562 values -(1, 0, "Introduction"), -(1, 1, "Authors"), -(1, 2, "Acknowledgements"), -(2, 0, "Basics"), -(2, 1, "Syntax"), -(2, 2, "Client"), -(2, 3, "Server"), -(3, 0, "Intermediate"), -(3, 1, "Complex queries"), -(3, 2, "Stored Procedures"), -(3, 3, "Stored Functions"), -(4, 0, "Advanced"), -(4, 1, "Replication"), -(4, 2, "Load balancing"), -(4, 3, "High availability"), -(5, 0, "Conclusion"); - -select * from table_24562; - -alter table table_24562 add column reviewer varchar(20), -order by title; - -select * from table_24562; - -update table_24562 set reviewer="Me" where section=2; -update table_24562 set reviewer="You" where section=3; - -alter table table_24562 -order by section ASC, subsection DESC; - -select * from table_24562; - -alter table table_24562 -order by table_24562.subsection ASC, table_24562.section DESC; - -select * from table_24562; - ---error ER_PARSE_ERROR -alter table table_24562 order by 12; ---error ER_PARSE_ERROR -alter table table_24562 order by (section + 12); ---error ER_PARSE_ERROR -alter table table_24562 order by length(title); ---error ER_PARSE_ERROR -alter table table_24562 order by (select 12 from dual); - ---error ER_BAD_FIELD_ERROR -alter table table_24562 order by no_such_col; - -drop table table_24562; - -# End of 4.1 tests - -# -# Bug #14693 (ALTER SET DEFAULT doesn't work) -# - -create table t1 (mycol int(10) not null); -alter table t1 alter column mycol set default 0; -desc t1; -drop table t1; - -# -# Bug#25262 Auto Increment lost when changing Engine type -# - -create table t1(id int(8) primary key auto_increment) engine=heap; - -insert into t1 values (null); -insert into t1 values (null); - -select * from t1; - -# Set auto increment to 50 -alter table t1 auto_increment = 50; - -# Alter to myisam -alter table t1 engine = myisam; - -# This insert should get id 50 -insert into t1 values (null); -select * from t1; - -# Alter to heap again -alter table t1 engine = heap; -insert into t1 values (null); -select * from t1; - -drop table t1; - -# -# Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the -# NO_ZERO_DATE mode. -# -set @orig_sql_mode = @@sql_mode; -set sql_mode="no_zero_date"; -create table t1(f1 int); -alter table t1 add column f2 datetime not null, add column f21 date not null; -insert into t1 values(1,'2000-01-01','2000-01-01'); ---error 1292 -alter table t1 add column f3 datetime not null; ---error 1292 -alter table t1 add column f3 date not null; ---error 1292 -alter table t1 add column f4 datetime not null default '2002-02-02', - add column f41 date not null; -alter table t1 add column f4 datetime not null default '2002-02-02', - add column f41 date not null default '2002-02-02'; -select * from t1; -drop table t1; -set sql_mode= @orig_sql_mode; - -# -# Some additional tests for new, faster alter table. Note that most of the -# whole alter table code is being tested all around the test suite already. -# - -create table t1 (v varchar(32)); -insert into t1 values ('def'),('abc'),('hij'),('3r4f'); -select * from t1; -# Fast alter, no copy performed -alter table t1 change v v2 varchar(32); -select * from t1; -# Fast alter, no copy performed -alter table t1 change v2 v varchar(64); -select * from t1; -update t1 set v = 'lmn' where v = 'hij'; -select * from t1; -# Regular alter table -alter table t1 add i int auto_increment not null primary key first; -select * from t1; -update t1 set i=5 where i=3; -select * from t1; -alter table t1 change i i bigint; -select * from t1; -alter table t1 add unique key (i, v); -select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn'); -drop table t1; - -# -# Bug#6073 "ALTER table minor glich": ALTER TABLE complains that an index -# without # prefix is not allowed for TEXT columns, while index -# is defined with prefix. -# -create table t1 (t varchar(255) default null, key t (t(80))) -engine=myisam default charset=latin1; -alter table t1 change t t text; -drop table t1; - -# -# Bug #26794: Adding an index with a prefix on a SPATIAL type breaks ALTER -# TABLE -# -CREATE TABLE t1 (a varchar(500)); - -ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b); -SHOW CREATE TABLE t1; -ALTER TABLE t1 ADD KEY(b(50)); -SHOW CREATE TABLE t1; - -ALTER TABLE t1 ADD c POINT; -SHOW CREATE TABLE t1; - ---error ER_WRONG_SUB_KEY -CREATE TABLE t2 (a INT, KEY (a(20))); - -ALTER TABLE t1 ADD d INT; ---error ER_WRONG_SUB_KEY -ALTER TABLE t1 ADD KEY (d(20)); - -# the 5.1 part of the test ---error ER_WRONG_SUB_KEY -ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30)); - -DROP TABLE t1; - -# -# Bug#18038 MySQL server corrupts binary columns data -# - -CREATE TABLE t1 (s CHAR(8) BINARY); -INSERT INTO t1 VALUES ('test'); -SELECT LENGTH(s) FROM t1; -ALTER TABLE t1 MODIFY s CHAR(10) BINARY; -SELECT LENGTH(s) FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (s BINARY(8)); -INSERT INTO t1 VALUES ('test'); -SELECT LENGTH(s) FROM t1; -SELECT HEX(s) FROM t1; -ALTER TABLE t1 MODIFY s BINARY(10); -SELECT HEX(s) FROM t1; -SELECT LENGTH(s) FROM t1; -DROP TABLE t1; - -# -# Bug#19386: Multiple alter causes crashed table -# The trailing column would get corrupted data, or server could not even read -# it. -# - -CREATE TABLE t1 (v VARCHAR(3), b INT); -INSERT INTO t1 VALUES ('abc', 5); -SELECT * FROM t1; -ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4); -SELECT * FROM t1; -DROP TABLE t1; - - -# -# Bug#31291 ALTER TABLE CONVERT TO CHARACTER SET does not change some data types -# -create table t1 (a tinytext character set latin1); -alter table t1 convert to character set utf8; -show create table t1; -drop table t1; -create table t1 (a mediumtext character set latin1); -alter table t1 convert to character set utf8; -show create table t1; -drop table t1; - ---echo End of 5.0 tests - -# -# Extended test coverage for ALTER TABLE behaviour under LOCK TABLES -# It should be consistent across all platforms and for all engines -# (Before 5.1 this was not true as behavior was different between -# Unix/Windows and transactional/non-transactional tables). -# See also innodb_mysql.test -# ---disable_warnings -drop table if exists t1, t2, t3; ---enable_warnings -create table t1 (i int); -create table t3 (j int); -insert into t1 values (); -insert into t3 values (); -# Table which is altered under LOCK TABLES it should stay in list of locked -# tables and be available after alter takes place unless ALTER contains RENAME -# clause. We should see the new definition of table, of course. -lock table t1 write, t3 read; -# Example of so-called 'fast' ALTER TABLE -alter table t1 modify i int default 1; -insert into t1 values (); -select * from t1; -# And now full-blown ALTER TABLE -alter table t1 change i c char(10) default "Two"; -insert into t1 values (); -select * from t1; -# If table is renamed then it should be removed from the list -# of locked tables. 'Fast' ALTER TABLE with RENAME clause: -alter table t1 modify c char(10) default "Three", rename to t2; ---error ER_TABLE_NOT_LOCKED -select * from t1; ---error ER_TABLE_NOT_LOCKED -select * from t2; -select * from t3; -unlock tables; -insert into t2 values (); -select * from t2; -lock table t2 write, t3 read; -# Full ALTER TABLE with RENAME -alter table t2 change c vc varchar(100) default "Four", rename to t1; ---error ER_TABLE_NOT_LOCKED -select * from t1; ---error ER_TABLE_NOT_LOCKED -select * from t2; -select * from t3; -unlock tables; -insert into t1 values (); -select * from t1; -drop tables t1, t3; - - -# -# Bug#18775 - Temporary table from alter table visible to other threads -# -# Check if special characters work and duplicates are detected. ---disable_warnings -DROP TABLE IF EXISTS `t+1`, `t+2`; ---enable_warnings -CREATE TABLE `t+1` (c1 INT); -ALTER TABLE `t+1` RENAME `t+2`; -CREATE TABLE `t+1` (c1 INT); ---error ER_TABLE_EXISTS_ERROR -ALTER TABLE `t+1` RENAME `t+2`; -DROP TABLE `t+1`, `t+2`; -# -# Same for temporary tables though these names do not become file names. -CREATE TEMPORARY TABLE `tt+1` (c1 INT); -ALTER TABLE `tt+1` RENAME `tt+2`; -CREATE TEMPORARY TABLE `tt+1` (c1 INT); ---error ER_TABLE_EXISTS_ERROR -ALTER TABLE `tt+1` RENAME `tt+2`; -SHOW CREATE TABLE `tt+1`; -SHOW CREATE TABLE `tt+2`; -DROP TABLE `tt+1`, `tt+2`; -# -# Check if special characters as in tmp_file_prefix work. -CREATE TABLE `#sql1` (c1 INT); -CREATE TABLE `@0023sql2` (c1 INT); -SHOW TABLES; -RENAME TABLE `#sql1` TO `@0023sql1`; -RENAME TABLE `@0023sql2` TO `#sql2`; -SHOW TABLES; -ALTER TABLE `@0023sql1` RENAME `#sql-1`; -ALTER TABLE `#sql2` RENAME `@0023sql-2`; -SHOW TABLES; -INSERT INTO `#sql-1` VALUES (1); -INSERT INTO `@0023sql-2` VALUES (2); -DROP TABLE `#sql-1`, `@0023sql-2`; -# -# Same for temporary tables though these names do not become file names. -CREATE TEMPORARY TABLE `#sql1` (c1 INT); -CREATE TEMPORARY TABLE `@0023sql2` (c1 INT); -SHOW TABLES; -ALTER TABLE `#sql1` RENAME `@0023sql1`; -ALTER TABLE `@0023sql2` RENAME `#sql2`; -SHOW TABLES; -INSERT INTO `#sql2` VALUES (1); -INSERT INTO `@0023sql1` VALUES (2); -SHOW CREATE TABLE `#sql2`; -SHOW CREATE TABLE `@0023sql1`; -DROP TABLE `#sql2`, `@0023sql1`; - -# -# Bug #22369: Alter table rename combined with other alterations causes lost tables -# -# This problem happens if the data change is compatible. -# Changing to the same type is compatible for example. -# ---disable_warnings -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; ---enable_warnings -CREATE TABLE t1 ( - int_field INTEGER UNSIGNED NOT NULL, - char_field CHAR(10), - INDEX(`int_field`) -); - -DESCRIBE t1; - -SHOW INDEXES FROM t1; - -INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); ---echo "Non-copy data change - new frm, but old data and index files" -ALTER TABLE t1 - CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL, - RENAME t2; - ---error ER_NO_SUCH_TABLE -SELECT * FROM t1 ORDER BY int_field; -SELECT * FROM t2 ORDER BY unsigned_int_field; -DESCRIBE t2; -DESCRIBE t2; -ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL; -DESCRIBE t2; - -DROP TABLE t2; - -# -# Bug#28427: Columns were renamed instead of moving by ALTER TABLE. -# -CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); -INSERT INTO t1 VALUES (1, 2, NULL); -SELECT * FROM t1; -ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1; -SELECT * FROM t1; -ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2; -SELECT * FROM t1; -DROP TABLE t1; - -# -# BUG#29957 - alter_table.test fails -# -create table t1 (c char(10) default "Two"); -lock table t1 write; -insert into t1 values (); -alter table t1 modify c char(10) default "Three"; -unlock tables; -select * from t1; -check table t1; -drop table t1; - -# -# Bug#33873: Fast ALTER TABLE doesn't work with multibyte character sets -# - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings -CREATE TABLE t1 (id int, c int) character set latin1; -INSERT INTO t1 VALUES (1,1); ---enable_info -ALTER TABLE t1 CHANGE c d int; -ALTER TABLE t1 CHANGE d c int; -ALTER TABLE t1 MODIFY c VARCHAR(10); -ALTER TABLE t1 CHANGE c d varchar(10); -ALTER TABLE t1 CHANGE d c varchar(10); ---disable_info -DROP TABLE t1; - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings -CREATE TABLE t1 (id int, c int) character set utf8; -INSERT INTO t1 VALUES (1,1); ---enable_info -ALTER TABLE t1 CHANGE c d int; -ALTER TABLE t1 CHANGE d c int; -ALTER TABLE t1 MODIFY c VARCHAR(10); -ALTER TABLE t1 CHANGE c d varchar(10); -ALTER TABLE t1 CHANGE d c varchar(10); ---disable_info -DROP TABLE t1; - -# -# Bug#39372 "Smart" ALTER TABLE not so smart after all. -# -create table t1(f1 int not null, f2 int not null, key (f1), key (f2)); -let $count= 50; ---disable_query_log -begin; -while ($count) -{ - EVAL insert into t1 values (1,1),(1,1),(1,1),(1,1),(1,1); - EVAL insert into t1 values (2,2),(2,2),(2,2),(2,2),(2,2); - dec $count ; -} -commit; ---enable_query_log - -select index_length into @unpaked_keys_size from -information_schema.tables where table_name='t1'; -alter table t1 pack_keys=1; -select index_length into @paked_keys_size from -information_schema.tables where table_name='t1'; -select (@unpaked_keys_size > @paked_keys_size); - -select max_data_length into @orig_max_data_length from -information_schema.tables where table_name='t1'; -alter table t1 max_rows=100; -select max_data_length into @changed_max_data_length from -information_schema.tables where table_name='t1'; -select (@orig_max_data_length > @changed_max_data_length); - -drop table t1; - -# -# Bug #23113: Different behavior on altering ENUM fields between 5.0 and 5.1 -# -CREATE TABLE t1(a INT AUTO_INCREMENT PRIMARY KEY, - b ENUM('a', 'b', 'c') NOT NULL); -INSERT INTO t1 (b) VALUES ('a'), ('c'), ('b'), ('b'), ('a'); -ALTER TABLE t1 MODIFY b ENUM('a', 'z', 'b', 'c') NOT NULL; -SELECT * FROM t1; -DROP TABLE t1; - -# -# Test for ALTER column DROP DEFAULT -# - -SET @save_sql_mode=@@sql_mode; -SET sql_mode=strict_all_tables; - -CREATE TABLE t1 (a int NOT NULL default 42); -INSERT INTO t1 values (); -SELECT * FROM t1; -ALTER TABLE t1 ALTER COLUMN a DROP DEFAULT; ---error 1364 -INSERT INTO t1 values (); -INSERT INTO t1 (a) VALUES (11); -SELECT * FROM t1 ORDER BY a; -DROP TABLE t1; -SET @@sql_mode=@save_sql_mode; ---echo # ---echo # Bug#45567: Fast ALTER TABLE broken for enum and set ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - -CREATE TABLE t1 (a ENUM('a1','a2')); -INSERT INTO t1 VALUES ('a1'),('a2'); ---enable_info ---echo # No copy: No modification -ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2'); ---echo # No copy: Add new enumeration to the end -ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a3'); ---echo # Copy: Modify and add new to the end -ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx','a5'); ---echo # Copy: Remove from the end -ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','xx'); ---echo # Copy: Add new enumeration -ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx'); ---echo # No copy: Add new enumerations to the end -ALTER TABLE t1 MODIFY COLUMN a ENUM('a1','a2','a0','xx','a5','a6'); ---disable_info -DROP TABLE t1; - -CREATE TABLE t1 (a SET('a1','a2')); -INSERT INTO t1 VALUES ('a1'),('a2'); ---enable_info ---echo # No copy: No modification -ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2'); ---echo # No copy: Add new to the end -ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a3'); ---echo # Copy: Modify and add new to the end -ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx','a5'); ---echo # Copy: Remove from the end -ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','xx'); ---echo # Copy: Add new member -ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx'); ---echo # No copy: Add new to the end -ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6'); ---echo # Copy: Numerical incrase (pack lenght) -ALTER TABLE t1 MODIFY COLUMN a SET('a1','a2','a0','xx','a5','a6','a7','a8','a9','a10'); ---disable_info -DROP TABLE t1; - -# -# Bug#43508: Renaming timestamp or date column triggers table copy -# - -CREATE TABLE t1 (f1 TIMESTAMP NULL DEFAULT NULL, - f2 INT(11) DEFAULT NULL) ENGINE=MYISAM DEFAULT CHARSET=utf8; - -INSERT INTO t1 VALUES (NULL, NULL), ("2009-10-09 11:46:19", 2); - ---echo this should affect no rows as there is no real change ---enable_info -ALTER TABLE t1 CHANGE COLUMN f1 f1_no_real_change TIMESTAMP NULL DEFAULT NULL; ---disable_info -DROP TABLE t1; - - ---echo # ---echo # Bug #31145: ALTER TABLE DROP COLUMN, ADD COLUMN crashes (linux) ---echo # or freezes (win) the server ---echo # - -CREATE TABLE t1 (a TEXT, id INT, b INT); -ALTER TABLE t1 DROP COLUMN a, ADD COLUMN c TEXT FIRST; - -DROP TABLE t1; - - ---echo # ---echo # Test for bug #12652385 - "61493: REORDERING COLUMNS TO POSITION ---echo # FIRST CAN CAUSE DATA TO BE CORRUPTED". ---echo # ---disable_warnings -drop table if exists t1; ---enable_warnings ---echo # Use MyISAM engine as the fact that InnoDB doesn't support ---echo # in-place ALTER TABLE in cases when columns are being renamed ---echo # hides some bugs. -create table t1 (i int, j int) engine=myisam; -insert into t1 value (1, 2); ---echo # First, test for original problem described in the bug report. -select * from t1; ---echo # Change of column order by the below ALTER TABLE statement should ---echo # affect both column names and column contents. -alter table t1 modify column j int first; -select * from t1; ---echo # Now test for similar problem with the same root. ---echo # The below ALTER TABLE should change not only the name but ---echo # also the value for the last column of the table. -alter table t1 drop column i, add column k int default 0; -select * from t1; ---echo # Clean-up. -drop table t1; - - ---echo End of 5.1 tests - -# -# Bug #31031 ALTER TABLE regression in 5.0 -# -# The ALTER TABLE operation failed with -# ERROR 1089 (HY000): Incorrect sub part key; ... -# -CREATE TABLE t1(c CHAR(10), - i INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY); -INSERT INTO t1 VALUES('a',2),('b',4),('c',6); -ALTER TABLE t1 - DROP i, - ADD i INT UNSIGNED NOT NULL AUTO_INCREMENT, - AUTO_INCREMENT = 1; -DROP TABLE t1; - - -# -# Bug#50542 5.5.x doesn't check length of key prefixes: -# corruption and crash results -# -# This case is related to Bug#31031 (above) -# A statement where the index key is larger/wider than -# the column type, should cause an error -# ---error ER_WRONG_SUB_KEY -CREATE TABLE t1 (a CHAR(1), PRIMARY KEY (a(255))); - -# Test other variants of creating indices -CREATE TABLE t1 (a CHAR(1)); -# ALTER TABLE ---error ER_WRONG_SUB_KEY -ALTER TABLE t1 ADD PRIMARY KEY (a(20)); ---error ER_WRONG_SUB_KEY -ALTER TABLE t1 ADD KEY (a(20)); -# CREATE INDEX ---error ER_WRONG_SUB_KEY -CREATE UNIQUE INDEX i1 ON t1 (a(20)); ---error ER_WRONG_SUB_KEY -CREATE INDEX i2 ON t1 (a(20)); -# cleanup -DROP TABLE t1; - - -# -# Bug #45052 ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns -# The alter table fails if 2 or more new fields added and -# also added a key with these fields -# -CREATE TABLE t1 (id int); -INSERT INTO t1 VALUES (1), (2); -ALTER TABLE t1 ADD COLUMN (f1 INT), ADD COLUMN (f2 INT), ADD KEY f2k(f2); -DROP TABLE t1; - - ---echo # ---echo # Test for bug #53820 "ALTER a MEDIUMINT column table causes full ---echo # table copy". ---echo # ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings -CREATE TABLE t1 (a INT, b MEDIUMINT); -INSERT INTO t1 VALUES (1, 1), (2, 2); ---echo # The below ALTER should not copy table and so no rows should ---echo # be shown as affected. ---enable_info -ALTER TABLE t1 CHANGE a id INT; ---disable_info -DROP TABLE t1; - - ---echo # ---echo # Bug#11754461 CANNOT ALTER TABLE WHEN KEY PREFIX TOO LONG ---echo # - ---disable_warnings -DROP DATABASE IF EXISTS db1; ---enable_warnings - -CREATE DATABASE db1 CHARACTER SET utf8; -CREATE TABLE db1.t1 (bar TINYTEXT, KEY (bar(100))); -ALTER TABLE db1.t1 ADD baz INT; - -DROP DATABASE db1; - - ---echo # Additional coverage for refactoring which is made as part ---echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege ---echo # to allow temp table operations". ---echo # ---echo # At some point the below test case failed on assertion. - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - -CREATE TEMPORARY TABLE t1 (i int) ENGINE=MyISAM; - ---error ER_ILLEGAL_HA -ALTER TABLE t1 DISCARD TABLESPACE; - -DROP TABLE t1; - - ---echo # ---echo # Bug#11938039 RE-EXECUTION OF FRM-ONLY ALTER TABLE WITH RENAME ---echo # CLAUSE FAILS OR ABORTS SERVER. ---echo # ---disable_warnings -drop table if exists t1; ---enable_warnings -create table t1 (a int); -prepare stmt1 from 'alter table t1 alter column a set default 1, rename to t2'; -execute stmt1; -rename table t2 to t1; ---echo # The below statement should succeed and not emit error or abort server. -execute stmt1; -deallocate prepare stmt1; -drop table t2; - ---echo # ---echo # MDEV-8960 Can't refer the same column twice in one ALTER TABLE ---echo # - -CREATE TABLE t1 ( - `a` int(11) DEFAULT NULL -) DEFAULT CHARSET=utf8; - -ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, -ALTER COLUMN `consultant_id` DROP DEFAULT; - -SHOW CREATE TABLE t1; -DROP TABLE t1; - -CREATE TABLE t1 ( - `a` int(11) DEFAULT NULL -) DEFAULT CHARSET=utf8; - -ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL, -ALTER COLUMN `consultant_id` SET DEFAULT 2; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -CREATE TABLE t1 ( - `a` int(11) DEFAULT NULL -) DEFAULT CHARSET=utf8; - -ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, -ALTER COLUMN `consultant_id` DROP DEFAULT; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -CREATE TABLE t1 ( - `a` int(11) DEFAULT NULL -) DEFAULT CHARSET=utf8; - -ALTER TABLE t1 ADD COLUMN `consultant_id` integer NOT NULL DEFAULT 2, -ALTER COLUMN `consultant_id` DROP DEFAULT, -MODIFY COLUMN `consultant_id` BIGINT; -SHOW CREATE TABLE t1; -DROP TABLE t1; - ---echo # ---echo # BUG#27788685: NO WARNING WHEN TRUNCATING A STRING WITH DATA LOSS ---echo # - -SET GLOBAL max_allowed_packet=17825792; - ---connect(con1, localhost, root,,) -CREATE TABLE t1 (t1_fld1 TEXT); -CREATE TABLE t2 (t2_fld1 MEDIUMTEXT); -CREATE TABLE t3 (t3_fld1 LONGTEXT); - -INSERT INTO t1 VALUES (REPEAT('a',300)); -INSERT INTO t2 VALUES (REPEAT('b',65680)); -INSERT INTO t3 VALUES (REPEAT('c',16777300)); - -SELECT LENGTH(t1_fld1) FROM t1; -SELECT LENGTH(t2_fld1) FROM t2; -SELECT LENGTH(t3_fld1) FROM t3; - ---echo # With strict mode -SET SQL_MODE='STRICT_ALL_TABLES'; - ---error ER_DATA_TOO_LONG -ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; ---error ER_DATA_TOO_LONG -ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; ---error ER_DATA_TOO_LONG -ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; - ---echo # With non-strict mode -SET SQL_MODE=''; - -ALTER TABLE t1 CHANGE `t1_fld1` `my_t1_fld1` TINYTEXT; -ALTER TABLE t2 CHANGE `t2_fld1` `my_t2_fld1` TEXT; -ALTER TABLE t3 CHANGE `t3_fld1` `my_t3_fld1` MEDIUMTEXT; - -SELECT LENGTH(my_t1_fld1) FROM t1; -SELECT LENGTH(my_t2_fld1) FROM t2; -SELECT LENGTH(my_t3_fld1) FROM t3; - -# Cleanup ---disconnect con1 ---source include/wait_until_disconnected.inc - ---connection default -DROP TABLE t1, t2, t3; - -SET SQL_MODE=default; -SET GLOBAL max_allowed_packet=default; - -# -# Test of ALTER TABLE IF [NOT] EXISTS -# - -CREATE TABLE t1 ( - id INT(11) NOT NULL, - x_param INT(11) DEFAULT NULL, - PRIMARY KEY (id) -) ENGINE=MYISAM; - -ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, - ADD COLUMN IF NOT EXISTS lol INT AFTER id; -ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; -ALTER TABLE t1 DROP COLUMN IF EXISTS lol; -ALTER TABLE t1 DROP COLUMN IF EXISTS lol; - -ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); -ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); -ALTER TABLE t1 MODIFY IF EXISTS lol INT; - -DROP INDEX IF EXISTS x_param ON t1; -DROP INDEX IF EXISTS x_param ON t1; -CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); -CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); -SHOW CREATE TABLE t1; -DROP TABLE t1; - -CREATE TABLE t1 ( - id INT(11) NOT NULL, - x_param INT(11) DEFAULT NULL, - PRIMARY KEY (id) -) ENGINE=INNODB; - -CREATE TABLE t2 ( - id INT(11) NOT NULL) ENGINE=INNODB; - -ALTER TABLE t1 ADD COLUMN IF NOT EXISTS id INT, - ADD COLUMN IF NOT EXISTS lol INT AFTER id; -ALTER TABLE t1 ADD COLUMN IF NOT EXISTS lol INT AFTER id; -ALTER TABLE t1 DROP COLUMN IF EXISTS lol; -ALTER TABLE t1 DROP COLUMN IF EXISTS lol; - -ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); -ALTER TABLE t1 ADD KEY IF NOT EXISTS x_param(x_param); -ALTER TABLE t1 MODIFY IF EXISTS lol INT; - -DROP INDEX IF EXISTS x_param ON t1; -DROP INDEX IF EXISTS x_param ON t1; -CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); -CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param); -SHOW CREATE TABLE t1; - -ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id); -ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS fk(id) REFERENCES t1(id); -ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk; -ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS fk; -SHOW CREATE TABLE t2; -ALTER TABLE t2 ADD FOREIGN KEY (id) REFERENCES t1(id); -ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS t2_ibfk_1(id) REFERENCES t1(id); -ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1; -ALTER TABLE t2 DROP FOREIGN KEY IF EXISTS t2_ibfk_1; -SHOW CREATE TABLE t2; - -DROP TABLE t2; -CREATE TABLE t2 ( - id INT(11) NOT NULL); -ALTER TABLE t2 ADD COLUMN a INT, ADD COLUMN IF NOT EXISTS a INT; -ALTER TABLE t2 ADD KEY k_id(id), ADD KEY IF NOT EXISTS k_id(id); -SHOW CREATE TABLE t2; -ALTER TABLE t2 DROP KEY k_id, DROP KEY IF EXISTS k_id; -ALTER TABLE t2 DROP COLUMN a, DROP COLUMN IF EXISTS a; -SHOW CREATE TABLE t2; - -DROP TABLE t2; -DROP TABLE t1; - -CREATE TABLE t1 ( - `transaction_id` int(11) NOT NULL DEFAULT '0', - KEY `transaction_id` (`transaction_id`)); -ALTER TABLE t1 DROP KEY IF EXISTS transaction_id, ADD PRIMARY KEY IF NOT EXISTS (transaction_id); -SHOW CREATE TABLE t1; - -DROP TABLE t1; - ---echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't ---echo # identify correct column name. ---echo # - -CREATE TABLE t1 (c1 int unsigned , c2 char(100) not null default ''); -ALTER TABLE t1 ADD c3 char(16) NOT NULL DEFAULT '' AFTER c2, - MODIFY c2 char(100) NOT NULL DEFAULT '' AFTER c1; -SHOW CREATE TABLE t1; -DROP TABLE t1; - ---echo # ---echo # WL#5534 Online ALTER, Phase 1 ---echo # - ---echo # Single thread tests. ---echo # See innodb_mysql_sync.test for multi thread tests. - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - -CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; -CREATE TABLE m1(a INT PRIMARY KEY, b INT) engine=MyISAM; -INSERT INTO t1 VALUES (1,1), (2,2); -INSERT INTO m1 VALUES (1,1), (2,2); - ---echo # ---echo # 1: Test ALGORITHM keyword ---echo # - ---echo # --enable_info allows us to see how many rows were updated ---echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. - ---enable_info -ALTER TABLE t1 ADD INDEX i1(b); -ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; -ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; -ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; ---error ER_UNKNOWN_ALTER_ALGORITHM -ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= INVALID; - -ALTER TABLE m1 ENABLE KEYS; -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= DEFAULT; -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY; -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE; ---disable_info - -ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; - ---echo # ---echo # 2: Test ALGORITHM + old_alter_table ---echo # - ---enable_info -SET SESSION old_alter_table= 1; -ALTER TABLE t1 ADD INDEX i1(b); -ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; -ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; -ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; -SET SESSION old_alter_table= 0; ---disable_info - -ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; - ---echo # ---echo # 3: Test unsupported in-place operation ---echo # - -ALTER TABLE t1 ADD COLUMN (c1 INT); -ALTER TABLE t1 ADD COLUMN (c2 INT), ALGORITHM= DEFAULT; -ALTER TABLE t1 ADD COLUMN (c3 INT), ALGORITHM= COPY; -ALTER TABLE t1 ADD COLUMN (c4 INT), ALGORITHM= INPLACE; - -ALTER TABLE t1 DROP COLUMN c1, DROP COLUMN c2, DROP COLUMN c3, DROP COLUMN c4; - ---echo # ---echo # 4: Test LOCK keyword ---echo # - ---enable_info -ALTER TABLE t1 ADD INDEX i1(b), LOCK= DEFAULT; -ALTER TABLE t1 ADD INDEX i2(b), LOCK= NONE; -ALTER TABLE t1 ADD INDEX i3(b), LOCK= SHARED; -ALTER TABLE t1 ADD INDEX i4(b), LOCK= EXCLUSIVE; ---error ER_UNKNOWN_ALTER_LOCK -ALTER TABLE t1 ADD INDEX i5(b), LOCK= INVALID; ---disable_info - -ALTER TABLE m1 ENABLE KEYS, LOCK= DEFAULT; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE m1 ENABLE KEYS, LOCK= NONE; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE m1 ENABLE KEYS, LOCK= SHARED; -ALTER TABLE m1 ENABLE KEYS, LOCK= EXCLUSIVE; - -ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; - ---echo # ---echo # 5: Test ALGORITHM + LOCK ---echo # - ---enable_info -ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE; -ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED; -ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; -ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; -ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE; - ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; -# This works because the lock will be SNW for the copy phase. -# It will still require exclusive lock for actually enabling keys. -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED; -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= EXCLUSIVE; ---disable_info - -DROP TABLE t1, m1; - ---echo # ---echo # 6: Possible deadlock involving thr_lock.c ---echo # - -CREATE TABLE t1(a INT PRIMARY KEY, b INT); -INSERT INTO t1 VALUES (1,1), (2,2); - -START TRANSACTION; -INSERT INTO t1 VALUES (3,3); - ---echo # Connection con1 -connect (con1, localhost, root); ---echo # Sending: ---send ALTER TABLE t1 DISABLE KEYS - ---echo # Connection default -connection default; ---echo # Waiting until ALTER TABLE is blocked. -let $wait_condition= - SELECT COUNT(*) = 1 FROM information_schema.processlist - WHERE state = "Waiting for table metadata lock" AND - info = "ALTER TABLE t1 DISABLE KEYS"; ---source include/wait_condition.inc -UPDATE t1 SET b = 4; -COMMIT; - ---echo # Connection con1 -connection con1; ---echo # Reaping: ALTER TABLE t1 DISABLE KEYS ---reap -disconnect con1; ---source include/wait_until_disconnected.inc - ---echo # Connection default -connection default; -DROP TABLE t1; - ---echo # ---echo # 7: Which operations require copy and which can be done in-place? ---echo # ---echo # Test which ALTER TABLE operations are done in-place and ---echo # which operations are done using temporary table copy. ---echo # ---echo # --enable_info allows us to see how many rows were updated ---echo # by ALTER TABLE. in-place will show 0 rows, while copy > 0. ---echo # - ---disable_warnings -DROP TABLE IF EXISTS ti1, ti2, ti3, tm1, tm2, tm3; ---enable_warnings - ---echo # Single operation tests - -CREATE TABLE ti1(a INT NOT NULL, b INT, c INT) engine=InnoDB; -CREATE TABLE tm1(a INT NOT NULL, b INT, c INT) engine=MyISAM; -CREATE TABLE ti2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=InnoDB; -CREATE TABLE tm2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=MyISAM; -INSERT INTO ti1 VALUES (1,1,1), (2,2,2); -INSERT INTO ti2 VALUES (1,1,1), (2,2,2); -INSERT INTO tm1 VALUES (1,1,1), (2,2,2); -INSERT INTO tm2 VALUES (1,1,1), (2,2,2); - ---enable_info -ALTER TABLE ti1; -ALTER TABLE tm1; - -ALTER TABLE ti1 ADD COLUMN d VARCHAR(200); -ALTER TABLE tm1 ADD COLUMN d VARCHAR(200); -ALTER TABLE ti1 ADD COLUMN d2 VARCHAR(200); -ALTER TABLE tm1 ADD COLUMN d2 VARCHAR(200); -ALTER TABLE ti1 ADD COLUMN e ENUM('a', 'b') FIRST; -ALTER TABLE tm1 ADD COLUMN e ENUM('a', 'b') FIRST; -ALTER TABLE ti1 ADD COLUMN f INT AFTER a; -ALTER TABLE tm1 ADD COLUMN f INT AFTER a; - -ALTER TABLE ti1 ADD INDEX ii1(b); -ALTER TABLE tm1 ADD INDEX im1(b); -ALTER TABLE ti1 ADD UNIQUE INDEX ii2 (c); -ALTER TABLE tm1 ADD UNIQUE INDEX im2 (c); -ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d); -ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d); -ALTER TABLE ti1 ADD FULLTEXT INDEX ii4 (d2); -ALTER TABLE tm1 ADD FULLTEXT INDEX im4 (d2); - -# Bug#14140038 INCONSISTENT HANDLING OF FULLTEXT INDEXES IN ALTER TABLE ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE ti1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; -ALTER TABLE ti1 ADD PRIMARY KEY(a); -ALTER TABLE tm1 ADD PRIMARY KEY(a); - -ALTER TABLE ti1 DROP INDEX ii3; -ALTER TABLE tm1 DROP INDEX im3; - -ALTER TABLE ti1 DROP COLUMN d2; -ALTER TABLE tm1 DROP COLUMN d2; - -ALTER TABLE ti1 ADD CONSTRAINT fi1 FOREIGN KEY (b) REFERENCES ti2(a); -ALTER TABLE tm1 ADD CONSTRAINT fm1 FOREIGN KEY (b) REFERENCES tm2(a); - -ALTER TABLE ti1 ALTER COLUMN b SET DEFAULT 1; -ALTER TABLE tm1 ALTER COLUMN b SET DEFAULT 1; -ALTER TABLE ti1 ALTER COLUMN b DROP DEFAULT; -ALTER TABLE tm1 ALTER COLUMN b DROP DEFAULT; - -# This will set both ALTER_COLUMN_NAME and COLUMN_DEFAULT_VALUE -ALTER TABLE ti1 CHANGE COLUMN f g INT; -ALTER TABLE tm1 CHANGE COLUMN f g INT; -ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20); -ALTER TABLE tm1 CHANGE COLUMN g h VARCHAR(20); -ALTER TABLE ti1 MODIFY COLUMN e ENUM('a', 'b', 'c'); -ALTER TABLE tm1 MODIFY COLUMN e ENUM('a', 'b', 'c'); -ALTER TABLE ti1 MODIFY COLUMN e INT; -ALTER TABLE tm1 MODIFY COLUMN e INT; -# This will set both ALTER_COLUMN_ORDER and COLUMN_DEFAULT_VALUE -ALTER TABLE ti1 MODIFY COLUMN e INT AFTER h; -ALTER TABLE tm1 MODIFY COLUMN e INT AFTER h; -ALTER TABLE ti1 MODIFY COLUMN e INT FIRST; -ALTER TABLE tm1 MODIFY COLUMN e INT FIRST; -# This will set both ALTER_COLUMN_NOT_NULLABLE and COLUMN_DEFAULT_VALUE ---disable_info -# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. -SET @orig_sql_mode = @@sql_mode; -SET @@sql_mode = 'STRICT_TRANS_TABLES'; ---enable_info -ALTER TABLE ti1 MODIFY COLUMN c INT NOT NULL; ---disable_info -SET @@sql_mode = @orig_sql_mode; ---enable_info -ALTER TABLE tm1 MODIFY COLUMN c INT NOT NULL; -# This will set both ALTER_COLUMN_NULLABLE and COLUMN_DEFAULT_VALUE -ALTER TABLE ti1 MODIFY COLUMN c INT NULL; -ALTER TABLE tm1 MODIFY COLUMN c INT NULL; -# This will set both ALTER_COLUMN_EQUAL_PACK_LENGTH and COLUMN_DEFAULT_VALUE -ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30); -ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30); -ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30) AFTER d; -ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30) AFTER d; - -ALTER TABLE ti1 DROP COLUMN h; -ALTER TABLE tm1 DROP COLUMN h; - -ALTER TABLE ti1 DROP INDEX ii2; -ALTER TABLE tm1 DROP INDEX im2; -ALTER TABLE ti1 DROP PRIMARY KEY; -ALTER TABLE tm1 DROP PRIMARY KEY; - -ALTER TABLE ti1 DROP FOREIGN KEY fi1; -ALTER TABLE tm1 DROP FOREIGN KEY fm1; - -ALTER TABLE ti1 RENAME TO ti3; -ALTER TABLE tm1 RENAME TO tm3; -ALTER TABLE ti3 RENAME TO ti1; -ALTER TABLE tm3 RENAME TO tm1; - -ALTER TABLE ti1 ORDER BY b; -ALTER TABLE tm1 ORDER BY b; - -ALTER TABLE ti1 CONVERT TO CHARACTER SET utf16; -ALTER TABLE tm1 CONVERT TO CHARACTER SET utf16; -ALTER TABLE ti1 DEFAULT CHARACTER SET utf8; -ALTER TABLE tm1 DEFAULT CHARACTER SET utf8; - -ALTER TABLE ti1 FORCE; -ALTER TABLE tm1 FORCE; - -ALTER TABLE ti1 AUTO_INCREMENT 3; -ALTER TABLE tm1 AUTO_INCREMENT 3; -ALTER TABLE ti1 AVG_ROW_LENGTH 10; -ALTER TABLE tm1 AVG_ROW_LENGTH 10; -ALTER TABLE ti1 CHECKSUM 1; -ALTER TABLE tm1 CHECKSUM 1; -ALTER TABLE ti1 COMMENT 'test'; -ALTER TABLE tm1 COMMENT 'test'; -ALTER TABLE ti1 MAX_ROWS 100; -ALTER TABLE tm1 MAX_ROWS 100; -ALTER TABLE ti1 MIN_ROWS 1; -ALTER TABLE tm1 MIN_ROWS 1; -ALTER TABLE ti1 PACK_KEYS 1; -ALTER TABLE tm1 PACK_KEYS 1; - ---disable_info -DROP TABLE ti1, ti2, tm1, tm2; - ---echo # Tests of >1 operation (InnoDB) - -CREATE TABLE ti1(a INT PRIMARY KEY AUTO_INCREMENT, b INT) engine=InnoDB; -INSERT INTO ti1(b) VALUES (1), (2); - ---enable_info -ALTER TABLE ti1 RENAME TO ti3, ADD INDEX ii1(b); - -ALTER TABLE ti3 DROP INDEX ii1, AUTO_INCREMENT 5; ---disable_info -INSERT INTO ti3(b) VALUES (5); ---enable_info -ALTER TABLE ti3 ADD INDEX ii1(b), AUTO_INCREMENT 7; ---disable_info -INSERT INTO ti3(b) VALUES (7); -SELECT * FROM ti3; - -DROP TABLE ti3; - ---echo # ---echo # 8: Scenario in which ALTER TABLE was returning an unwarranted ---echo # ER_ILLEGAL_HA error at some point during work on this WL. ---echo # - -CREATE TABLE tm1(i INT DEFAULT 1) engine=MyISAM; -ALTER TABLE tm1 ADD INDEX ii1(i), ALTER COLUMN i DROP DEFAULT; -DROP TABLE tm1; - -# -# MDEV-4435 Server crashes in my_strcasecmp_utf8 on ADD KEY IF NOT EXISTS with implicit name when the key exists. -# -create table if not exists t1 (i int); -alter table t1 add key (i); -alter table t1 add key if not exists (i); -DROP TABLE t1; - -# -# MDEV-4436 CHANGE COLUMN IF EXISTS does not work and throws wrong warning. -# -create table t1 (a int); -alter table t1 change column if exists a b bigint; -show create table t1; -DROP TABLE t1; - -# -# MDEV-4437 ALTER TABLE .. ADD UNIQUE INDEX IF NOT EXISTS causes syntax error. -# - -create table t1 (i int); -alter table t1 add unique index if not exists idx(i); -alter table t1 add unique index if not exists idx(i); -show create table t1; -DROP TABLE t1; - -# -# MDEV-8358 ADD PRIMARY KEY IF NOT EXISTS -> ERROR 1068 (42000): Multiple primary key -# - -CREATE TABLE t1 ( - `event_id` bigint(20) unsigned NOT NULL DEFAULT '0', - `market_id` bigint(20) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`event_id`,`market_id`) - ); -ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS event_id (event_id,market_id); -DROP TABLE t1; - ---echo # ---echo # MDEV-11126 Crash while altering persistent virtual column ---echo # - -CREATE TABLE `tab1` ( - `id` bigint(20) NOT NULL AUTO_INCREMENT, - `field2` set('option1','option2','option3','option4') NOT NULL, - `field3` set('option1','option2','option3','option4','option5') NOT NULL, - `field4` set('option1','option2','option3','option4') NOT NULL, - `field5` varchar(32) NOT NULL, - `field6` varchar(32) NOT NULL, - `field7` varchar(32) NOT NULL, - `field8` varchar(32) NOT NULL, - `field9` int(11) NOT NULL DEFAULT '1', - `field10` varchar(16) NOT NULL, - `field11` enum('option1','option2','option3') NOT NULL DEFAULT 'option1', - `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, - PRIMARY KEY (`id`) -) DEFAULT CHARSET=latin1; - -ALTER TABLE `tab1` CHANGE COLUMN v_col `v_col` varchar(128); -SHOW CREATE TABLE `tab1`; -ALTER 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; -SHOW CREATE TABLE `tab1`; -DROP TABLE `tab1`; - ---echo # ---echo # MDEV-11548 Reproducible server crash after the 2nd ALTER TABLE ADD FOREIGN KEY IF NOT EXISTS ---echo # - -CREATE TABLE t1 (id INT UNSIGNED NOT NULL PRIMARY KEY); -CREATE TABLE t2 (id1 INT UNSIGNED NOT NULL); - -ALTER TABLE t2 -ADD FOREIGN KEY IF NOT EXISTS (id1) - REFERENCES t1 (id); - -ALTER TABLE t2 -ADD FOREIGN KEY IF NOT EXISTS (id1) -REFERENCES t1 (id); - -DROP TABLE t2; -DROP TABLE t1; - - ---echo # ---echo # MDEV-6390 CONVERT TO CHARACTER SET utf8 doesn't change DEFAULT CHARSET. ---echo # - -CREATE TABLE t1 (id int(11) NOT NULL, a int(11) NOT NULL, b int(11)) - ENGINE=InnoDB DEFAULT CHARSET=latin1; -SHOW CREATE TABLE t1; -ALTER TABLE t1 CONVERT TO CHARACTER SET utf8; -SHOW CREATE TABLE t1; -DROP TABLE t1; - ---echo # ---echo # ---echo # MDEV-15308 ---echo # Assertion `ha_alter_info->alter_info->drop_list.elements > 0' failed ---echo # in ha_innodb::prepare_inplace_alter_table ---echo # - -CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; -ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP COLUMN b; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; -ALTER TABLE t1 DROP INDEX IF EXISTS fk, DROP COLUMN b; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -CREATE TABLE t1 (a INT, b INT, c INT, KEY(c)) ENGINE=InnoDB; -ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP COLUMN c; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -CREATE TABLE t1 (a INT, b INT, c INT, KEY c1(c)) ENGINE=InnoDB; -ALTER TABLE t1 DROP FOREIGN KEY IF EXISTS fk, DROP INDEX c1; -SHOW CREATE TABLE t1; -DROP TABLE t1; - -CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB; -ALTER TABLE t1 DROP INDEX IF EXISTS fk, DROP COLUMN IF EXISTS c; -SHOW CREATE TABLE t1; -DROP TABLE t1; - ---echo # ---echo # MDEV-14668 ADD PRIMARY KEY IF NOT EXISTS on composite key ---echo # -CREATE TABLE t1 ( - `ID` BIGINT(20) NOT NULL, - `RANK` MEDIUMINT(4) NOT NULL, - `CHECK_POINT` BIGINT(20) NOT NULL, - UNIQUE INDEX `HORIZON_UIDX01` (`ID`, `RANK`) - ) ENGINE=InnoDB; - -ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); -SHOW CREATE TABLE t1; -ALTER TABLE t1 ADD PRIMARY KEY IF NOT EXISTS (`ID`, `CHECK_POINT`); -DROP TABLE t1; |