1 files changed, 376 insertions, 0 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test
index d48b1687fa0..f40f8c11fac 100644
--- a/mysql-test/t/alter_table.test
+++ b/mysql-test/t/alter_table.test
@@ -1,3 +1,4 @@
+--source include/have_innodb.inc
 #
 # Test of alter table
 #
@@ -1215,6 +1216,24 @@ 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.
@@ -1258,3 +1277,360 @@ CREATE INDEX IF NOT EXISTS x_param1 ON t1(x_param);
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1(a INT) engine=innodb;
+INSERT INTO t1 VALUES (1), (2);
+
+--enable_info
+--echo # This should not do anything
+ALTER TABLE t1;
+--echo # Check that we rebuild the table
+ALTER TABLE t1 engine=innodb;
+--echo # This should also rebuild the table
+ALTER TABLE t1 FORCE;
+--disable_info
+
+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;