diff options
author | Sergei Golubchik <serg@mariadb.org> | 2022-05-24 20:10:48 +0200 |
---|---|---|
committer | Nikita Malyavin <nikitamalyavin@gmail.com> | 2022-08-23 16:36:25 +0300 |
commit | 8796febfe9388d4610fe9ff8d9c2e27eb80c128a (patch) | |
tree | 7db85ac273ab18ffe3edc93982f0ae70a5a9c154 | |
parent | 9e26cb2e7f27e0d4f964c43a41c27fc8530705ad (diff) | |
download | mariadb-git-8796febfe9388d4610fe9ff8d9c2e27eb80c128a.tar.gz |
support 'alter online table t1 page_checksum=0'
36 files changed, 139 insertions, 143 deletions
diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index 34978a2d65b..ef5fe5667c5 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -1755,6 +1755,7 @@ affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 1 Warnings: Note 1831 Duplicate index `i3`. This is deprecated and will be disallowed in a future release +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 1 @@ -1771,8 +1772,7 @@ ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; affected rows: 0 -ALTER ONLINE TABLE m1 ADD COLUMN c int; -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED; affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 0 diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test index a874e9dfe40..81d9de6c2e9 100644 --- a/mysql-test/main/alter_table.test +++ b/mysql-test/main/alter_table.test @@ -1528,6 +1528,13 @@ ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; 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; +--disable_info +--disable_warnings +--error 0,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +# COPY/NONE works now, but still an error in embedded +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; +--enable_warnings +--enable_info ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE; @@ -1536,8 +1543,11 @@ 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 -ALTER ONLINE TABLE m1 ADD COLUMN c int; +--disable_info +--error 0,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +# COPY/NONE works now, but still an error in embedded +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; +--enable_info # 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; diff --git a/mysql-test/main/alter_table_locknone.result b/mysql-test/main/alter_table_locknone.result index ea040925efe..210d9600cf6 100644 --- a/mysql-test/main/alter_table_locknone.result +++ b/mysql-test/main/alter_table_locknone.result @@ -53,30 +53,11 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`a`) ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 COMMENT='new comment' alter online table t1 page_checksum=1; -alter online table t1 page_checksum=0; -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED drop table t1; create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); -alter online table t1 drop column b, add b int; -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED -alter online table t1 modify b bigint; -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED -alter online table t1 modify e enum('c','a','b'); -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED -alter online table t1 modify c varchar(50); -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED -alter online table t1 modify c varchar(100); -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED -alter online table t1 add f int; -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED -alter online table t1 engine=memory; alter online table t1 rename to t2; ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE -alter online table t1 checksum=1; -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED -alter online table t1 add constraint check (b > 0); -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED alter table t1 engine=innodb; alter table t1 add index (b); alter online table t1 add index c (c); diff --git a/mysql-test/main/alter_table_locknone.test b/mysql-test/main/alter_table_locknone.test index f07ded67cd2..9588ec6678c 100644 --- a/mysql-test/main/alter_table_locknone.test +++ b/mysql-test/main/alter_table_locknone.test @@ -33,7 +33,6 @@ drop table t1; # create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); - alter online table t1 modify b int default 5, alter c set default 'X'; alter online table t1 change b new_name int; alter online table t1 modify e enum('a','b','c'); @@ -54,37 +53,13 @@ alter online table t1 modify e enum('a','b','c'); alter online table t1 comment "new comment"; show create table t1; alter online table t1 page_checksum=1; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 page_checksum=0; drop table t1; -# -# Test of things that is not possible to do online -# - create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); --error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 drop column b, add b int; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 modify b bigint; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 modify e enum('c','a','b'); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 modify c varchar(50); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 modify c varchar(100); ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 add f int; ---error 0,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -alter online table t1 engine=memory; ---error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 rename to t2; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 checksum=1; ---error ER_ALTER_OPERATION_NOT_SUPPORTED -alter online table t1 add constraint check (b > 0); alter table t1 engine=innodb; alter table t1 add index (b); diff --git a/mysql-test/main/alter_table_locknone_notembedded.result b/mysql-test/main/alter_table_locknone_notembedded.result new file mode 100644 index 00000000000..646b9267ee0 --- /dev/null +++ b/mysql-test/main/alter_table_locknone_notembedded.result @@ -0,0 +1,25 @@ +create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')) engine=aria; +insert into t1 (a) values (1),(2),(3); +alter online table t1 page_checksum=1; +alter online table t1 page_checksum=0; +alter online table t1 drop column b, add b int; +alter online table t1 modify b bigint; +alter online table t1 modify e enum('c','a','b'); +alter online table t1 modify c varchar(50); +alter online table t1 modify c varchar(100); +alter online table t1 add f int; +alter online table t1 engine=memory; +alter online table t1 checksum=1; +alter online table t1 add constraint check (b > 0); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `c` varchar(100) DEFAULT NULL, + `e` enum('c','a','b') DEFAULT NULL, + `b` bigint(20) DEFAULT NULL, + `f` int(11) DEFAULT NULL, + PRIMARY KEY (`a`), + CONSTRAINT `CONSTRAINT_1` CHECK (`b` > 0) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 CHECKSUM=1 +drop table t1; diff --git a/mysql-test/main/alter_table_locknone_notembedded.test b/mysql-test/main/alter_table_locknone_notembedded.test new file mode 100644 index 00000000000..00649740ca4 --- /dev/null +++ b/mysql-test/main/alter_table_locknone_notembedded.test @@ -0,0 +1,22 @@ +source include/not_embedded.inc; +# +# Test of ALTER ONLINE TABLE syntax +# (same as LOCK=NONE) +# + +create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')) engine=aria; +insert into t1 (a) values (1),(2),(3); +alter online table t1 page_checksum=1; +alter online table t1 page_checksum=0; +alter online table t1 drop column b, add b int; +alter online table t1 modify b bigint; +alter online table t1 modify e enum('c','a','b'); +alter online table t1 modify c varchar(50); +alter online table t1 modify c varchar(100); +alter online table t1 add f int; +alter online table t1 engine=memory; +alter online table t1 checksum=1; +alter online table t1 add constraint check (b > 0); + +show create table t1; +drop table t1; diff --git a/mysql-test/suite/gcol/inc/gcol_select.inc b/mysql-test/suite/gcol/inc/gcol_select.inc index 8f6314233bd..595bc05dcc0 100644 --- a/mysql-test/suite/gcol/inc/gcol_select.inc +++ b/mysql-test/suite/gcol/inc/gcol_select.inc @@ -872,6 +872,7 @@ CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(2147483647); ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL; ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY; +# COPY/NONE is not supported in embedded --error ER_WARN_DATA_OUT_OF_RANGE,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; --error ER_WARN_DATA_OUT_OF_RANGE,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON diff --git a/mysql-test/suite/gcol/r/innodb_virtual_basic.result b/mysql-test/suite/gcol/r/innodb_virtual_basic.result index 4e8adbd88e6..2ae371f7475 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_basic.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_basic.result @@ -957,12 +957,12 @@ CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); INSERT INTO t1 VALUES (11, 3, DEFAULT, 'mm'); INSERT INTO t1 VALUES (18, 1, DEFAULT, 'mm'); INSERT INTO t1 VALUES (28, 1, DEFAULT, 'mm'); -ALTER TABLE t1 ADD INDEX idx12 (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 ADD INDEX idx12 (c), FORCE, ALGORITHM = INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED ALTER TABLE t1 ADD INDEX idx12 (c), LOCK=NONE; -ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), FORCE, ALGORITHM = INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), LOCK=NONE; +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), ALGORITHM = INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED DROP TABLE t1 ; CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); diff --git a/mysql-test/suite/gcol/r/innodb_virtual_debug.result b/mysql-test/suite/gcol/r/innodb_virtual_debug.result index 9cac55c25e3..91dda83b2e6 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_debug.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_debug.result @@ -53,7 +53,7 @@ a b c h 18 1 19 mm 28 1 29 mm NULL NULL NULL mx -ALTER TABLE t FORCE, LOCK=NONE; +ALTER TABLE t FORCE, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED disconnect con1; DROP TABLE t; diff --git a/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result b/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result index 35f37034721..028ae02360c 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result @@ -6,30 +6,30 @@ CREATE TABLE t4 (i INT, v INT AS (i) VIRTUAL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; INSERT INTO t4 SET i=1; ALTER TABLE t4 ADD INDEX(v), LOCK=NONE; -ALTER TABLE t4 ADD COLUMN k INT, LOCK=NONE; +ALTER TABLE t4 ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t4 DROP k, LOCK=NONE; +ALTER TABLE t4 DROP k, ALGORITHM=INPLACE, LOCK=NONE; ERROR 42000: Can't DROP COLUMN `k`; check that it exists ALTER TABLE t4 DROP INDEX v, LOCK=NONE; INSERT INTO t3 SET i=1; ALTER TABLE t3 ADD INDEX(v), LOCK=NONE; -ALTER TABLE t3 ADD COLUMN k INT, LOCK=NONE; +ALTER TABLE t3 ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t3 DROP k, LOCK=NONE; +ALTER TABLE t3 DROP k, ALGORITHM=INPLACE, LOCK=NONE; ERROR 42000: Can't DROP COLUMN `k`; check that it exists ALTER TABLE t3 DROP INDEX v, LOCK=NONE; INSERT INTO t2 SET i=1; ALTER TABLE t2 ADD INDEX(v), LOCK=NONE; -ALTER TABLE t2 ADD COLUMN k INT, LOCK=NONE; +ALTER TABLE t2 ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t2 DROP k, LOCK=NONE; +ALTER TABLE t2 DROP k, ALGORITHM=INPLACE, LOCK=NONE; ERROR 42000: Can't DROP COLUMN `k`; check that it exists ALTER TABLE t2 DROP INDEX v, LOCK=NONE; INSERT INTO t1 SET i=1; ALTER TABLE t1 ADD INDEX(v), LOCK=NONE; -ALTER TABLE t1 ADD COLUMN k INT, LOCK=NONE; +ALTER TABLE t1 ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t1 DROP k, LOCK=NONE; +ALTER TABLE t1 DROP k, ALGORITHM=INPLACE, LOCK=NONE; ERROR 42000: Can't DROP COLUMN `k`; check that it exists ALTER TABLE t1 DROP INDEX v, LOCK=NONE; connect ddl,localhost,root,,test; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_basic.test b/mysql-test/suite/gcol/t/innodb_virtual_basic.test index 87251ad7d52..b3a7aa614dc 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_basic.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_basic.test @@ -886,12 +886,12 @@ INSERT INTO t1 VALUES (18, 1, DEFAULT, 'mm'); INSERT INTO t1 VALUES (28, 1, DEFAULT, 'mm'); --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 ADD INDEX idx12 (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 ADD INDEX idx12 (c), FORCE, ALGORITHM = INPLACE, LOCK=NONE; ALTER TABLE t1 ADD INDEX idx12 (c), LOCK=NONE; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), FORCE, ALGORITHM = INPLACE, LOCK=NONE; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), LOCK=NONE; +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), ALGORITHM = INPLACE, LOCK=NONE; DROP TABLE t1 ; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug.test b/mysql-test/suite/gcol/t/innodb_virtual_debug.test index cd2b860400c..c1bd76342e5 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_debug.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_debug.test @@ -54,7 +54,7 @@ SHOW CREATE TABLE t; SELECT * FROM t; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t FORCE, LOCK=NONE; +ALTER TABLE t FORCE, ALGORITHM=INPLACE, LOCK=NONE; if (0) {# MDEV-14341 TODO: re-enable this SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; --send ALTER TABLE t FORCE diff --git a/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test b/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test index fe4f5e307b3..85707ca01f1 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test @@ -16,9 +16,9 @@ eval INSERT INTO t$n SET i=1; eval ALTER TABLE t$n ADD INDEX(v), LOCK=NONE; # MDEV-17468 FIXME: Fix this, and remove the 2 --error below. --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -eval ALTER TABLE t$n ADD COLUMN k INT, LOCK=NONE; +eval ALTER TABLE t$n ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; --error ER_CANT_DROP_FIELD_OR_KEY -eval ALTER TABLE t$n DROP k, LOCK=NONE; +eval ALTER TABLE t$n DROP k, ALGORITHM=INPLACE, LOCK=NONE; eval ALTER TABLE t$n DROP INDEX v, LOCK=NONE; dec $n; } diff --git a/mysql-test/suite/innodb/r/autoinc_persist.result b/mysql-test/suite/innodb/r/autoinc_persist.result index 91d6d908a82..ea72f959d1f 100644 --- a/mysql-test/suite/innodb/r/autoinc_persist.result +++ b/mysql-test/suite/innodb/r/autoinc_persist.result @@ -626,7 +626,7 @@ CREATE TABLE mdev6076a (b INT) ENGINE=InnoDB; INSERT INTO mdev6076a VALUES(2),(1); CREATE TABLE mdev6076b (b INT) ENGINE=InnoDB; INSERT INTO mdev6076b VALUES(2),(1); -ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, LOCK=NONE; +ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE; ALTER TABLE mdev6076b ADD COLUMN a SERIAL FIRST, AUTO_INCREMENT=100, diff --git a/mysql-test/suite/innodb/r/innodb-alter-autoinc.result b/mysql-test/suite/innodb/r/innodb-alter-autoinc.result index a96d2f634cb..2a841bede1c 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-autoinc.result +++ b/mysql-test/suite/innodb/r/innodb-alter-autoinc.result @@ -5,7 +5,7 @@ SET @@sql_mode = 'STRICT_TRANS_TABLES'; ALTER TABLE t1 ADD PRIMARY KEY(a); SET @@sql_mode = @old_sql_mode; ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, -LOCK=NONE; +ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED ALTER TABLE t1 ADD id INT AUTO_INCREMENT; ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key @@ -50,7 +50,7 @@ t1 CREATE TABLE `t1` ( KEY `id` (`id`,`a`) ) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=latin1 ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, -DROP COLUMN id, AUTO_INCREMENT = 42, LOCK=NONE; +DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE; diff --git a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result index cbdf5cd828b..2372d31a45b 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result +++ b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result @@ -86,8 +86,8 @@ ALTER TABLE t1 ADD COLUMN d2 TIMESTAMP DEFAULT '2017-05-08 16:23:45', LOCK=NONE; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 -ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED +ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE, LOCK=NONE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1; diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result index a25b072cd53..40d4c71e8de 100644 --- a/mysql-test/suite/innodb/r/innodb-alter.result +++ b/mysql-test/suite/innodb/r/innodb-alter.result @@ -438,28 +438,28 @@ tt CREATE TABLE `tt` ( PRIMARY KEY (`pk`), FULLTEXT KEY `ct` (`ct`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED -ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED +ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE +ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE CREATE TABLE tu ( pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT, FULLTEXT INDEX(t) ) ENGINE=InnoDB; -ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED -ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED +ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE +ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE DROP TABLE tu; CREATE TABLE tv ( pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT, UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID), FULLTEXT INDEX(t) ) ENGINE=InnoDB; -ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED -ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED +ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE +ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE DROP TABLE tv; ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=COPY; ERROR 42000: Incorrect column name 'dB_row_Id' diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result index 4b2e88e3029..7af774faffd 100644 --- a/mysql-test/suite/innodb/r/innodb-index.result +++ b/mysql-test/suite/innodb/r/innodb-index.result @@ -1598,8 +1598,8 @@ create table t1(f1 int not null, f2 int not null, primary key (f1), unique key(f1, f2))engine=innodb; insert into t1 values(1,3), (2,2); alter table t1 drop primary key, lock=none; -alter table t1 drop index f1, lock=none; -ERROR 0A000: LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED +alter table t1 drop index f1, algorithm=inplace, lock=none; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY drop table t1; # # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP diff --git a/mysql-test/suite/innodb/r/innodb-online-alter-gis.result b/mysql-test/suite/innodb/r/innodb-online-alter-gis.result index 65dc23ddf2c..299190b1957 100644 --- a/mysql-test/suite/innodb/r/innodb-online-alter-gis.result +++ b/mysql-test/suite/innodb/r/innodb-online-alter-gis.result @@ -23,7 +23,7 @@ t1 CREATE TABLE `t1` ( SPATIAL KEY `c` (`b`), KEY `d` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -ALTER ONLINE TABLE t1 ADD PRIMARY KEY(a),DROP INDEX d; +ALTER ONLINE TABLE t1 ADD PRIMARY KEY(a),DROP INDEX d, ALGORITHM=INPLACE; ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED show warnings; Level Code Message diff --git a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result index 08ee9688603..a41684be5df 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result +++ b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result @@ -1385,8 +1385,8 @@ t1c.ibd t1p.ibd tt.ibd ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, -LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try LOCK=SHARED +ALGORITHM=INPLACE, LOCK=NONE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY SELECT sc.pos FROM information_schema.innodb_sys_columns sc INNER JOIN information_schema.innodb_sys_tables st ON sc.TABLE_ID=st.TABLE_ID diff --git a/mysql-test/suite/innodb/t/autoinc_persist.test b/mysql-test/suite/innodb/t/autoinc_persist.test index c6135b5580c..20064819d3a 100644 --- a/mysql-test/suite/innodb/t/autoinc_persist.test +++ b/mysql-test/suite/innodb/t/autoinc_persist.test @@ -392,7 +392,7 @@ INSERT INTO mdev6076a VALUES(2),(1); CREATE TABLE mdev6076b (b INT) ENGINE=InnoDB; INSERT INTO mdev6076b VALUES(2),(1); --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, LOCK=NONE; +ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE, LOCK=NONE; ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE; ALTER TABLE mdev6076b ADD COLUMN a SERIAL FIRST, AUTO_INCREMENT=100, ALGORITHM=INPLACE; diff --git a/mysql-test/suite/innodb/t/innodb-alter-autoinc.test b/mysql-test/suite/innodb/t/innodb-alter-autoinc.test index e01c44aa26e..49b26691f08 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-autoinc.test +++ b/mysql-test/suite/innodb/t/innodb-alter-autoinc.test @@ -11,7 +11,7 @@ SET @@sql_mode = @old_sql_mode; # We cannot assign AUTO_INCREMENT values during online index creation. --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, - LOCK=NONE; + ALGORITHM=INPLACE, LOCK=NONE; --error ER_WRONG_AUTO_KEY ALTER TABLE t1 ADD id INT AUTO_INCREMENT; @@ -42,7 +42,7 @@ SHOW CREATE TABLE t1; # We cannot assign AUTO_INCREMENT values during online index creation. --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, - DROP COLUMN id, AUTO_INCREMENT = 42, LOCK=NONE; + DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE, LOCK=NONE; ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE; diff --git a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test index 95df8d6b649..eeeeaa55410 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test +++ b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test @@ -67,7 +67,7 @@ SELECT u1, COUNT(DISTINCT d1) FROM t1 GROUP BY u1; ALTER TABLE t1 ADD COLUMN d2 TIMESTAMP DEFAULT '2017-05-08 16:23:45', LOCK=NONE; --error ER_ALTER_OPERATION_NOT_SUPPORTED -ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, LOCK=NONE; +ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE, LOCK=NONE; --error ER_ALTER_OPERATION_NOT_SUPPORTED ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE; ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1; diff --git a/mysql-test/suite/innodb/t/innodb-alter.test b/mysql-test/suite/innodb/t/innodb-alter.test index 78550763a9f..c6a76b66b3e 100644 --- a/mysql-test/suite/innodb/t/innodb-alter.test +++ b/mysql-test/suite/innodb/t/innodb-alter.test @@ -195,20 +195,20 @@ ALGORITHM=INPLACE, LOCK=SHARED; SHOW CREATE TABLE tt; # Non-instant ADD COLUMN would require the table to be rebuilt. ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT; # This is still non-instant ADD COLUMN, because FTS_DOC_ID is hidden. ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT; CREATE TABLE tu ( pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT, FULLTEXT INDEX(t) ) ENGINE=InnoDB; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT; DROP TABLE tu; CREATE TABLE tv ( @@ -216,10 +216,10 @@ CREATE TABLE tv ( UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID), FULLTEXT INDEX(t) ) ENGINE=InnoDB; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT; DROP TABLE tv; # DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR are reserved InnoDB system column names. diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index c80e3e6c066..b434ef46627 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -921,7 +921,7 @@ create table t1(f1 int not null, f2 int not null, insert into t1 values(1,3), (2,2); alter table t1 drop primary key, lock=none; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -alter table t1 drop index f1, lock=none; +alter table t1 drop index f1, algorithm=inplace, lock=none; drop table t1; --echo # diff --git a/mysql-test/suite/innodb/t/innodb-online-alter-gis.test b/mysql-test/suite/innodb/t/innodb-online-alter-gis.test index df0ab2e8184..26311793992 100644 --- a/mysql-test/suite/innodb/t/innodb-online-alter-gis.test +++ b/mysql-test/suite/innodb/t/innodb-online-alter-gis.test @@ -11,8 +11,8 @@ show errors; drop table t1; create table t1(a int not null, b geometry not null, d int,spatial key c(b), key d(d)) engine=innodb; show create table t1; ---error 1846 -ALTER ONLINE TABLE t1 ADD PRIMARY KEY(a),DROP INDEX d; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER ONLINE TABLE t1 ADD PRIMARY KEY(a),DROP INDEX d, ALGORITHM=INPLACE; show warnings; show errors; ALTER ONLINE TABLE t1 ADD PRIMARY KEY(a),DROP INDEX d, LOCK=SHARED; diff --git a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test index 09c54db644f..b1051b514a0 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test +++ b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test @@ -611,7 +611,7 @@ ALGORITHM=INPLACE; # This would create a hidden FTS_DOC_ID column, which cannot be done online. --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, -LOCK=NONE; +ALGORITHM=INPLACE, LOCK=NONE; # This should not show duplicates. SELECT sc.pos FROM information_schema.innodb_sys_columns sc diff --git a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result index daf552cde8b..7f46a5a4573 100644 --- a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result +++ b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result @@ -75,10 +75,10 @@ INSERT INTO fts_test (title,body) VALUES ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); -CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE; +ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY; -ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE; +ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED ALTER TABLE fts_test ROW_FORMAT=REDUNDANT; SELECT * FROM fts_test WHERE MATCH (title, body) diff --git a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test index 1ed164492d5..2118acbe470 100644 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test +++ b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test @@ -96,11 +96,11 @@ INSERT INTO fts_test (title,body) VALUES # We could support online fulltext index creation when a FTS_DOC_ID # column already exists. This has not been implemented yet. --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE; +ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY, LOCK=NONE; ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE; +ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE; ALTER TABLE fts_test ROW_FORMAT=REDUNDANT; SELECT * FROM fts_test WHERE MATCH (title, body) diff --git a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result index ae9dd47233b..791a16fa249 100644 --- a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result +++ b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result @@ -759,17 +759,6 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), add spatial index(b), algorithm=copy; DROP table t1; -# -# BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED -# WITHOUT STATING A REASON -# -CREATE TABLE t1(p point NOT NULL) ENGINE=innodb; -ALTER TABLE t1 ADD SPATIAL INDEX(p), LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED -ALTER TABLE t1 ADD SPATIAL INDEX(p); -ALTER TABLE t1 FORCE, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED -DROP TABLE t1; create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb; set timestamp=10; insert into t1 values(default); diff --git a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test index 6d1607656d3..ff5e4b9b289 100644 --- a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test +++ b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test @@ -747,18 +747,6 @@ alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), add spatial index(b), algorithm=copy; DROP table t1; ---echo # ---echo # BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED ---echo # WITHOUT STATING A REASON ---echo # -CREATE TABLE t1(p point NOT NULL) ENGINE=innodb; ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 ADD SPATIAL INDEX(p), LOCK=NONE; -ALTER TABLE t1 ADD SPATIAL INDEX(p); ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 FORCE, LOCK=NONE; -DROP TABLE t1; - create table t1 (p point not null default if(unix_timestamp()>10,POINT(1,1),LineString(Point(0,0),Point(1,1)))) ENGINE=innodb; set timestamp=10; --error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD diff --git a/mysql-test/suite/versioning/r/online.result b/mysql-test/suite/versioning/r/online.result index 62d62913428..2cfaa2b9b5c 100644 --- a/mysql-test/suite/versioning/r/online.result +++ b/mysql-test/suite/versioning/r/online.result @@ -1,7 +1,7 @@ set system_versioning_alter_history=keep; create or replace table t (a int); alter table t add system versioning, lock=none; -ERROR 0A000: LOCK=NONE is not supported. Reason: Not implemented for system-versioned timestamp tables. Try LOCK=SHARED +alter table t drop system versioning; alter table t add system versioning, algorithm=inplace; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Not implemented for system-versioned timestamp tables. Try ALGORITHM=COPY alter table t add system versioning, lock=shared; diff --git a/mysql-test/suite/versioning/t/online.test b/mysql-test/suite/versioning/t/online.test index 5932c34687a..469b0c6408d 100644 --- a/mysql-test/suite/versioning/t/online.test +++ b/mysql-test/suite/versioning/t/online.test @@ -5,8 +5,8 @@ set system_versioning_alter_history=keep; create or replace table t (a int); ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON alter table t add system versioning, lock=none; +alter table t drop system versioning; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON alter table t add system versioning, algorithm=inplace; alter table t add system versioning, lock=shared; diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc index 1d5875733ac..e482bb24173 100644 --- a/sql/sql_alter.cc +++ b/sql/sql_alter.cc @@ -177,8 +177,8 @@ bool Alter_info::supports_algorithm(THD *thd, } -bool Alter_info::supports_lock(THD *thd, - const Alter_inplace_info *ha_alter_info) +bool Alter_info::supports_lock(THD *thd, bool online, + Alter_inplace_info *ha_alter_info) { switch (ha_alter_info->inplace_supported) { case HA_ALTER_INPLACE_EXCLUSIVE_LOCK: @@ -207,8 +207,13 @@ bool Alter_info::supports_lock(THD *thd, case HA_ALTER_INPLACE_SHARED_LOCK: if (requested_lock == Alter_info::ALTER_TABLE_LOCK_NONE) { - ha_alter_info->report_unsupported_error("LOCK=NONE", "LOCK=SHARED"); - return true; + if (online) + ha_alter_info->inplace_supported= HA_ALTER_INPLACE_NOT_SUPPORTED; + else + { + ha_alter_info->report_unsupported_error("LOCK=NONE", "LOCK=SHARED"); + return true; + } } return false; case HA_ALTER_ERROR: diff --git a/sql/sql_alter.h b/sql/sql_alter.h index bf1edd4c964..55a83bdb36b 100644 --- a/sql/sql_alter.h +++ b/sql/sql_alter.h @@ -226,7 +226,7 @@ public: @retval false Supported lock type @retval true Not supported value */ - bool supports_lock(THD *thd, const Alter_inplace_info *ha_alter_info); + bool supports_lock(THD *thd, bool, Alter_inplace_info *ha_alter_info); /** Return user requested algorithm. If user does not specify diff --git a/sql/sql_table.cc b/sql/sql_table.cc index ae3947b51db..32a1e000c9d 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -10695,7 +10695,7 @@ do_continue:; } if (alter_info->supports_algorithm(thd, &ha_alter_info) || - alter_info->supports_lock(thd, &ha_alter_info)) + alter_info->supports_lock(thd, online, &ha_alter_info)) { cleanup_table_after_inplace_alter(&altered_table); goto err_new_table_cleanup; |