diff options
| -rw-r--r-- | mysql-test/suite/innodb/r/alter_algorithm2.result | 56 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/alter_algorithm2.test | 47 |
2 files changed, 103 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/alter_algorithm2.result b/mysql-test/suite/innodb/r/alter_algorithm2.result new file mode 100644 index 00000000000..1cea3f4cd43 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_algorithm2.result @@ -0,0 +1,56 @@ +CREATE TABLE t1 (a INT)ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); +SET alter_algorithm='INPLACE'; +affected rows: 0 +PREPARE stmt FROM 'ALTER TABLE t1 ADD KEY idx(a)'; +affected rows: 0 +info: Statement prepared +PREPARE stmt1 FROM 'ALTER TABLE t1 DROP KEY idx'; +affected rows: 0 +info: Statement prepared +CREATE OR REPLACE PROCEDURE p1() +BEGIN +ALTER TABLE t1 ADD KEY idx2(a); +END| +affected rows: 0 +CREATE OR REPLACE PROCEDURE p2() +BEGIN +ALTER TABLE t1 DROP KEY idx2; +END| +affected rows: 0 +SET alter_algorithm='COPY'; +affected rows: 0 +EXECUTE stmt; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +EXECUTE stmt1; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +call p1(); +affected rows: 1 +call p2(); +affected rows: 1 +SET alter_algorithm='NOCOPY'; +affected rows: 0 +EXECUTE stmt; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +EXECUTE stmt1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +call p1(); +affected rows: 0 +call p2(); +affected rows: 0 +SET alter_algorithm='INSTANT'; +affected rows: 0 +EXECUTE stmt; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY +call p1(); +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY +DROP TABLE t1; +affected rows: 0 +DROP PROCEDURE p1; +affected rows: 0 +DROP PROCEDURE p2; +affected rows: 0 diff --git a/mysql-test/suite/innodb/t/alter_algorithm2.test b/mysql-test/suite/innodb/t/alter_algorithm2.test new file mode 100644 index 00000000000..02b49c797bc --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_algorithm2.test @@ -0,0 +1,47 @@ +--source include/have_innodb.inc +CREATE TABLE t1 (a INT)ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); + +# alter_algorithm variable doesn't affect when ALTER stmt +# during PREPARE PHASE or CREATE PROCEDURE +# Only when execution/call happens, alter uses the alter_algorithm +# variable when user does not mention algorithm explicitly. + +--enable_info +SET alter_algorithm='INPLACE'; +PREPARE stmt FROM 'ALTER TABLE t1 ADD KEY idx(a)'; +PREPARE stmt1 FROM 'ALTER TABLE t1 DROP KEY idx'; +DELIMITER |; +CREATE OR REPLACE PROCEDURE p1() +BEGIN + ALTER TABLE t1 ADD KEY idx2(a); +END| + +CREATE OR REPLACE PROCEDURE p2() +BEGIN + ALTER TABLE t1 DROP KEY idx2; +END| +DELIMITER ;| + +SET alter_algorithm='COPY'; +EXECUTE stmt; +EXECUTE stmt1; +call p1(); +call p2(); + +SET alter_algorithm='NOCOPY'; +EXECUTE stmt; +EXECUTE stmt1; +call p1(); +call p2(); + +SET alter_algorithm='INSTANT'; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +EXECUTE stmt; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +call p1(); + +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +--disable_info |