diff options
| author | Thirunarayanan Balathandayuthapani <thiru@mariadb.com> | 2020-04-30 19:52:49 +0530 |
|---|---|---|
| committer | Thirunarayanan Balathandayuthapani <thiru@mariadb.com> | 2020-05-04 09:35:38 +0530 |
| commit | f98017bb6df0110aa0a359ab4378cc97f6a95473 (patch) | |
| tree | ff45419f5e1acd8ba0a427a0bda84e15654fdfc0 /mysql-test/suite | |
| parent | 644d9f38b97b198ec356aadfc390e0fef97c53f0 (diff) | |
| download | mariadb-git-f98017bb6df0110aa0a359ab4378cc97f6a95473.tar.gz | |
MDEV-16288 ALTER TABLE…ALGORITHM=DEFAULT does not override alter_algorithm
- Added the test case to prove that alter_algorithm variable doesn't
affect when ALTER statement during PREPARE PHASE or CREATE PROCEDURE.
Only when execution/call happens, alter uses the alter_algorithm
variable when user does not mention algorithm explicitly
Diffstat (limited to 'mysql-test/suite')
| -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 |