diff options
44 files changed, 1337 insertions, 342 deletions
diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index 9b394926489..5f41e0d7a08 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -1810,9 +1810,7 @@ affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 0 ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d); affected rows: 0 -info: Records: 0 Duplicates: 0 Warnings: 1 -Warnings: -Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d); affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 0 diff --git a/mysql-test/main/alter_table_online.result b/mysql-test/main/alter_table_online.result index d5a2a028acc..2e3de2c0635 100644 --- a/mysql-test/main/alter_table_online.result +++ b/mysql-test/main/alter_table_online.result @@ -112,7 +112,7 @@ create table t1 (a int not null primary key, b int, c varchar(80)); create table t2 (a int not null primary key, b int, c varchar(80)); create table t3 (a int not null primary key, b int, c varchar(80)) engine=merge UNION=(t1); alter online table t3 union=(t1,t2); -ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE +ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=EXCLUSIVE drop table t1,t2,t3; create table t1 (i int) partition by hash(i) partitions 2; alter online table t1 comment 'test'; diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 356546a287b..9da52106f91 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -12,6 +12,9 @@ The following specify which files/extra groups are read (specified before remain without corresponding xxx_init() or xxx_deinit(). That also means that one can load any function from any library, for example exit() from libc.so + --alter-algorithm[=name] + Specify the alter table algorithm. One of: DEFAULT, COPY, + INPLACE, NOCOPY, INSTANT -a, --ansi Use ANSI SQL syntax instead of MySQL syntax. This mode will also set transaction isolation level 'serializable'. --auto-increment-increment[=#] @@ -617,7 +620,10 @@ The following specify which files/extra groups are read (specified before remain connection before aborting the write --old Use compatible behavior from previous MariaDB version. See also --old-mode - --old-alter-table Use old, non-optimized alter table + --old-alter-table[=name] + Alias for alter_algorithm. Deprecated. Use + --alter-algorithm instead.. One of: DEFAULT, COPY, + INPLACE, NOCOPY, INSTANT --old-mode=name Used to emulate old behavior from earlier MariaDB or MySQL versions. Any combination of: NO_DUP_KEY_WARNINGS_WITH_IGNORE, NO_PROGRESS_INFO, @@ -1334,6 +1340,7 @@ The following specify which files/extra groups are read (specified before remain Variables (--variable-name=value) allow-suspicious-udfs FALSE +alter-algorithm DEFAULT auto-increment-increment 1 auto-increment-offset 1 autocommit TRUE @@ -1509,7 +1516,7 @@ net-read-timeout 30 net-retry-count 10 net-write-timeout 60 old FALSE -old-alter-table FALSE +old-alter-table DEFAULT old-mode old-passwords FALSE old-style-user-limits FALSE diff --git a/mysql-test/suite/innodb/include/alter_instant.inc b/mysql-test/suite/innodb/include/alter_instant.inc new file mode 100644 index 00000000000..cf0c082416b --- /dev/null +++ b/mysql-test/suite/innodb/include/alter_instant.inc @@ -0,0 +1,33 @@ +CREATE TABLE t1(f1 INT NOT NULL, + f2 INT NOT NULL, + f3 INT AS (f2 * f2) VIRTUAL)engine=innodb; + +INSERT INTO t1(f1, f2) VALUES(1, 1); + +--echo # +--echo # ALGORITHM=$algorithm_type +--echo # + +--enable_info +--echo # Add column at the end of the table +--error $error_code +--eval ALTER TABLE t1 ADD COLUMN f4 char(100) default "BIG WALL", ALGORITHM=$algorithm_type + +--echo # Change virtual column expression +--error $error_code +--eval ALTER TABLE t1 CHANGE f3 f3 INT AS (f2 * f2) VIRTUAL, ALGORITHM=$algorithm_type + +--echo # Add virtual column +--error $error_code +--eval ALTER TABLE t1 ADD COLUMN f5 INT AS (f2) VIRTUAL, ALGORITHM=$algorithm_type + +--echo # Rename Column +--error $error_code +--eval ALTER TABLE t1 CHANGE f3 vcol INT AS (f2) VIRTUAL, ALGORITHM=$algorithm_type + +--echo # Rename table +--error $error_code +--eval ALTER TABLE t1 RENAME t2, algorithm=$algorithm_type + +DROP TABLE t2; +--disable_info diff --git a/mysql-test/suite/innodb/include/alter_nocopy.inc b/mysql-test/suite/innodb/include/alter_nocopy.inc new file mode 100644 index 00000000000..6b19d244bd9 --- /dev/null +++ b/mysql-test/suite/innodb/include/alter_nocopy.inc @@ -0,0 +1,33 @@ +CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL, + f3 INT AS (f2 * f2) VIRTUAL, + f4 INT NOT NULL UNIQUE, + f5 INT NOT NULL, + INDEX`idx`(f2))ENGINE=INNODB; + +CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL, + INDEX(f1), + FOREIGN KEY `fidx` (f1) REFERENCES t1(f1))ENGINE=INNODB; + +INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); + +SELECT @@alter_algorithm; + +--enable_info +--error $error_code +--eval ALTER TABLE t1 ADD INDEX idx1(f4) + +--error $error_code +--eval ALTER TABLE t1 DROP INDEX idx + +--error $error_code +--eval ALTER TABLE t1 ADD UNIQUE INDEX u1(f2) + +--error $error_code +--eval ALTER TABLE t1 DROP INDEX f4 + +SET foreign_key_checks = 0; +--error $error_code +--eval ALTER TABLE t1 ADD FOREIGN KEY(f5) REFERENCES t2(f1) + +DROP TABLE t2, t1; +--disable_info diff --git a/mysql-test/suite/innodb/include/alter_nocopy_fail.inc b/mysql-test/suite/innodb/include/alter_nocopy_fail.inc new file mode 100644 index 00000000000..a075cf96e3c --- /dev/null +++ b/mysql-test/suite/innodb/include/alter_nocopy_fail.inc @@ -0,0 +1,51 @@ +CREATE TABLE t1(f1 INT NOT NULL, + f2 INT NOT NULL, + f3 INT NULL, + f4 INT as (f2) STORED, + f5 INT as (f3) STORED, + PRIMARY KEY(f1))ROW_FORMAT=COMPRESSED, ENGINE=INNODB; +INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); + +SELECT @@alter_algorithm; + +--enable_info +--echo # All the following cases needs table rebuild + +--echo # Add and Drop primary key +--error $error_code +--eval ALTER TABLE t1 ADD COLUMN col1 INT NOT NULL,DROP PRIMARY KEY,ADD PRIMARY KEY(col1) + +--echo # Make existing column NULLABLE +--error $error_code +--eval ALTER TABLE t1 MODIFY f2 INT + +--echo # Make existing column NON-NULLABLE +--error $error_code +--eval ALTER TABLE t1 MODIFY f3 INT NOT NULL + +--echo # Drop Stored Column +--error $error_code +--eval ALTER TABLE t1 DROP COLUMN f5 + +--echo # Add base non-generated column as a last column in the compressed table +--error $error_code +--eval ALTER TABLE t1 ADD COLUMN f6 INT NOT NULL + +--echo # Add base non-generated column but not in the last position +--error $error_code +--eval ALTER TABLE t1 ADD COLUMN f7 INT NOT NULL after f3 + +--echo # Force the table to rebuild +--error $error_code +--eval ALTER TABLE t1 FORCE + +--echo # Row format changes +--error $error_code +--eval ALTER TABLE t1 ROW_FORMAT=COMPRESSED + +--echo # Engine table +--error $error_code +--eval ALTER TABLE t1 ENGINE=INNODB + +DROP TABLE t1; +--disable_info diff --git a/mysql-test/suite/innodb/r/alter_algorithm,COPY.rdiff b/mysql-test/suite/innodb/r/alter_algorithm,COPY.rdiff new file mode 100644 index 00000000000..be71e125e22 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_algorithm,COPY.rdiff @@ -0,0 +1,92 @@ +--- alter_algorithm.result 2018-05-06 23:42:08.022302601 +0530 ++++ alter_algorithm.reject 2018-05-06 23:42:16.382634082 +0530 +@@ -7,35 +7,44 @@ + INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); + SELECT @@alter_algorithm; + @@alter_algorithm +-NOCOPY ++COPY + # All the following cases needs table rebuild + # Add and Drop primary key + ALTER TABLE t1 ADD COLUMN col1 INT NOT NULL,DROP PRIMARY KEY,ADD PRIMARY KEY(col1); +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Make existing column NULLABLE + ALTER TABLE t1 MODIFY f2 INT; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Make existing column NON-NULLABLE + ALTER TABLE t1 MODIFY f3 INT NOT NULL; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Drop Stored Column + ALTER TABLE t1 DROP COLUMN f5; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Add base non-generated column as a last column in the compressed table + ALTER TABLE t1 ADD COLUMN f6 INT NOT NULL; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Add base non-generated column but not in the last position + ALTER TABLE t1 ADD COLUMN f7 INT NOT NULL after f3; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Force the table to rebuild + ALTER TABLE t1 FORCE; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Row format changes + ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Engine table + ALTER TABLE t1 ENGINE=INNODB; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + DROP TABLE t1; + affected rows: 0 + CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL, +@@ -49,23 +58,23 @@ + INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); + SELECT @@alter_algorithm; + @@alter_algorithm +-NOCOPY ++COPY + ALTER TABLE t1 ADD INDEX idx1(f4); +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + ALTER TABLE t1 DROP INDEX idx; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + ALTER TABLE t1 ADD UNIQUE INDEX u1(f2); +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + ALTER TABLE t1 DROP INDEX f4; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + SET foreign_key_checks = 0; + affected rows: 0 + ALTER TABLE t1 ADD FOREIGN KEY(f5) REFERENCES t2(f1); +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + DROP TABLE t2, t1; + affected rows: 0 diff --git a/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff b/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff new file mode 100644 index 00000000000..71891bbf473 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff @@ -0,0 +1,66 @@ +--- alter_algorithm.result 2018-05-06 23:42:08.022302601 +0530 ++++ alter_algorithm.reject 2018-05-06 23:45:23.813346814 +0530 +@@ -7,35 +7,44 @@ + INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); + SELECT @@alter_algorithm; + @@alter_algorithm +-NOCOPY ++INPLACE + # All the following cases needs table rebuild + # Add and Drop primary key + ALTER TABLE t1 ADD COLUMN col1 INT NOT NULL,DROP PRIMARY KEY,ADD PRIMARY KEY(col1); +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 + # Make existing column NULLABLE + ALTER TABLE t1 MODIFY f2 INT; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 + # Make existing column NON-NULLABLE + ALTER TABLE t1 MODIFY f3 INT NOT NULL; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 + # Drop Stored Column + ALTER TABLE t1 DROP COLUMN f5; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 + # Add base non-generated column as a last column in the compressed table + ALTER TABLE t1 ADD COLUMN f6 INT NOT NULL; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 + # Add base non-generated column but not in the last position + ALTER TABLE t1 ADD COLUMN f7 INT NOT NULL after f3; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 + # Force the table to rebuild + ALTER TABLE t1 FORCE; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 + # Row format changes + ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 + # Engine table + ALTER TABLE t1 ENGINE=INNODB; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++affected rows: 0 ++info: Records: 0 Duplicates: 0 Warnings: 0 + DROP TABLE t1; + affected rows: 0 + CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL, +@@ -49,7 +58,7 @@ + INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); + SELECT @@alter_algorithm; + @@alter_algorithm +-NOCOPY ++INPLACE + ALTER TABLE t1 ADD INDEX idx1(f4); + affected rows: 0 + info: Records: 0 Duplicates: 0 Warnings: 0 diff --git a/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff b/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff new file mode 100644 index 00000000000..6e12b78fb9d --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff @@ -0,0 +1,78 @@ +--- alter_algorithm.result 2018-05-06 23:42:08.022302601 +0530 ++++ alter_algorithm.reject 2018-05-06 23:46:08.482772800 +0530 +@@ -7,35 +7,35 @@ + INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); + SELECT @@alter_algorithm; + @@alter_algorithm +-NOCOPY ++INSTANT + # All the following cases needs table rebuild + # Add and Drop primary key + ALTER TABLE t1 ADD COLUMN col1 INT NOT NULL,DROP PRIMARY KEY,ADD PRIMARY KEY(col1); +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++Got one of the listed errors + # Make existing column NULLABLE + ALTER TABLE t1 MODIFY f2 INT; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++Got one of the listed errors + # Make existing column NON-NULLABLE + ALTER TABLE t1 MODIFY f3 INT NOT NULL; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++Got one of the listed errors + # Drop Stored Column + ALTER TABLE t1 DROP COLUMN f5; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++Got one of the listed errors + # Add base non-generated column as a last column in the compressed table + ALTER TABLE t1 ADD COLUMN f6 INT NOT NULL; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++Got one of the listed errors + # Add base non-generated column but not in the last position + ALTER TABLE t1 ADD COLUMN f7 INT NOT NULL after f3; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++Got one of the listed errors + # Force the table to rebuild + ALTER TABLE t1 FORCE; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++Got one of the listed errors + # Row format changes + ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++Got one of the listed errors + # Engine table + ALTER TABLE t1 ENGINE=INNODB; +-ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE ++Got one of the listed errors + DROP TABLE t1; + affected rows: 0 + CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL, +@@ -49,23 +49,18 @@ + INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); + SELECT @@alter_algorithm; + @@alter_algorithm +-NOCOPY ++INSTANT + ALTER TABLE t1 ADD INDEX idx1(f4); +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY + ALTER TABLE t1 DROP INDEX idx; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: DROP INDEX. Try ALGORITHM=NOCOPY + ALTER TABLE t1 ADD UNIQUE INDEX u1(f2); +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY + ALTER TABLE t1 DROP INDEX f4; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: DROP INDEX. Try ALGORITHM=NOCOPY + SET foreign_key_checks = 0; + affected rows: 0 + ALTER TABLE t1 ADD FOREIGN KEY(f5) REFERENCES t2(f1); +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY + DROP TABLE t2, t1; + affected rows: 0 diff --git a/mysql-test/suite/innodb/r/alter_algorithm.result b/mysql-test/suite/innodb/r/alter_algorithm.result new file mode 100644 index 00000000000..ee91159bf7a --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_algorithm.result @@ -0,0 +1,71 @@ +CREATE TABLE t1(f1 INT NOT NULL, +f2 INT NOT NULL, +f3 INT NULL, +f4 INT as (f2) STORED, +f5 INT as (f3) STORED, +PRIMARY KEY(f1))ROW_FORMAT=COMPRESSED, ENGINE=INNODB; +INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); +SELECT @@alter_algorithm; +@@alter_algorithm +NOCOPY +# All the following cases needs table rebuild +# Add and Drop primary key +ALTER TABLE t1 ADD COLUMN col1 INT NOT NULL,DROP PRIMARY KEY,ADD PRIMARY KEY(col1); +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +# Make existing column NULLABLE +ALTER TABLE t1 MODIFY f2 INT; +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +# Make existing column NON-NULLABLE +ALTER TABLE t1 MODIFY f3 INT NOT NULL; +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +# Drop Stored Column +ALTER TABLE t1 DROP COLUMN f5; +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +# Add base non-generated column as a last column in the compressed table +ALTER TABLE t1 ADD COLUMN f6 INT NOT NULL; +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +# Add base non-generated column but not in the last position +ALTER TABLE t1 ADD COLUMN f7 INT NOT NULL after f3; +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +# Force the table to rebuild +ALTER TABLE t1 FORCE; +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +# Row format changes +ALTER TABLE t1 ROW_FORMAT=COMPRESSED; +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +# Engine table +ALTER TABLE t1 ENGINE=INNODB; +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +DROP TABLE t1; +affected rows: 0 +CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL, +f3 INT AS (f2 * f2) VIRTUAL, +f4 INT NOT NULL UNIQUE, +f5 INT NOT NULL, +INDEX`idx`(f2))ENGINE=INNODB; +CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL, +INDEX(f1), +FOREIGN KEY `fidx` (f1) REFERENCES t1(f1))ENGINE=INNODB; +INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); +SELECT @@alter_algorithm; +@@alter_algorithm +NOCOPY +ALTER TABLE t1 ADD INDEX idx1(f4); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 DROP INDEX idx; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD UNIQUE INDEX u1(f2); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 DROP INDEX f4; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SET foreign_key_checks = 0; +affected rows: 0 +ALTER TABLE t1 ADD FOREIGN KEY(f5) REFERENCES t2(f1); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +DROP TABLE t2, t1; +affected rows: 0 diff --git a/mysql-test/suite/innodb/r/alter_instant,COPY.rdiff b/mysql-test/suite/innodb/r/alter_instant,COPY.rdiff new file mode 100644 index 00000000000..cb4a72614b9 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_instant,COPY.rdiff @@ -0,0 +1,61 @@ +--- alter_instant.result 2018-04-10 11:19:46.299868465 +0530 ++++ alter_instant.reject 2018-04-10 11:21:19.648918489 +0530 +@@ -8,30 +8,30 @@ + INSERT INTO t1(f1, f2) VALUES(1, 1); + select @@alter_algorithm; + @@alter_algorithm +-NOCOPY ++COPY + # Add column at the end of the table + ALTER TABLE t1 ADD COLUMN f4 char(100) default "BIG WALL"; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Change virtual column expression + ALTER TABLE t1 CHANGE f3 f3 INT AS (f2 * f2) VIRTUAL; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Add virtual column + ALTER TABLE t1 ADD COLUMN f5 INT AS (f2) VIRTUAL; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Rename Column + ALTER TABLE t1 CHANGE f3 vcol INT AS (f2) VIRTUAL; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Rename table + ALTER TABLE t1 RENAME t3; + affected rows: 0 + # Drop Virtual Column + ALTER TABLE t3 DROP COLUMN vcol; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + # Column length varies + ALTER TABLE t2 CHANGE f3 f3 VARCHAR(20); + affected rows: 0 +@@ -39,12 +39,12 @@ + SET foreign_key_checks = 0; + affected rows: 0 + ALTER TABLE t3 ADD FOREIGN KEY `fidx`(f2) REFERENCES t2(f1); +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + SET foreign_key_checks = 1; + affected rows: 0 + ALTER TABLE t3 DROP FOREIGN KEY `fidx`; +-affected rows: 0 +-info: Records: 0 Duplicates: 0 Warnings: 0 ++affected rows: 1 ++info: Records: 1 Duplicates: 0 Warnings: 0 + DROP TABLE t3, t2; + affected rows: 0 diff --git a/mysql-test/suite/innodb/r/alter_instant,INPLACE.rdiff b/mysql-test/suite/innodb/r/alter_instant,INPLACE.rdiff new file mode 100644 index 00000000000..ec80e1d8ef0 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_instant,INPLACE.rdiff @@ -0,0 +1,11 @@ +--- alter_instant.result 2018-04-10 11:19:46.299868465 +0530 ++++ alter_instant.reject 2018-04-10 11:22:19.433617807 +0530 +@@ -8,7 +8,7 @@ + INSERT INTO t1(f1, f2) VALUES(1, 1); + select @@alter_algorithm; + @@alter_algorithm +-NOCOPY ++INPLACE + # Add column at the end of the table + ALTER TABLE t1 ADD COLUMN f4 char(100) default "BIG WALL"; + affected rows: 0 diff --git a/mysql-test/suite/innodb/r/alter_instant,INSTANT.rdiff b/mysql-test/suite/innodb/r/alter_instant,INSTANT.rdiff new file mode 100644 index 00000000000..cf2f8a2d719 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_instant,INSTANT.rdiff @@ -0,0 +1,11 @@ +--- alter_instant.result 2018-04-10 11:19:46.299868465 +0530 ++++ alter_instant.reject 2018-04-10 11:22:47.281949905 +0530 +@@ -8,7 +8,7 @@ + INSERT INTO t1(f1, f2) VALUES(1, 1); + select @@alter_algorithm; + @@alter_algorithm +-NOCOPY ++INSTANT + # Add column at the end of the table + ALTER TABLE t1 ADD COLUMN f4 char(100) default "BIG WALL"; + affected rows: 0 diff --git a/mysql-test/suite/innodb/r/alter_instant.result b/mysql-test/suite/innodb/r/alter_instant.result new file mode 100644 index 00000000000..ec64e41cd01 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_instant.result @@ -0,0 +1,50 @@ +CREATE TABLE t1(f1 INT NOT NULL, +f2 INT NOT NULL, +f3 INT AS (f2 * f2) VIRTUAL, +INDEX idx (f2))engine=innodb; +CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL, +f3 VARCHAR(10), +INDEX(f1))ENGINE=INNODB; +INSERT INTO t1(f1, f2) VALUES(1, 1); +select @@alter_algorithm; +@@alter_algorithm +NOCOPY +# Add column at the end of the table +ALTER TABLE t1 ADD COLUMN f4 char(100) default "BIG WALL"; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Change virtual column expression +ALTER TABLE t1 CHANGE f3 f3 INT AS (f2 * f2) VIRTUAL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Add virtual column +ALTER TABLE t1 ADD COLUMN f5 INT AS (f2) VIRTUAL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Rename Column +ALTER TABLE t1 CHANGE f3 vcol INT AS (f2) VIRTUAL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Rename table +ALTER TABLE t1 RENAME t3; +affected rows: 0 +# Drop Virtual Column +ALTER TABLE t3 DROP COLUMN vcol; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +# Column length varies +ALTER TABLE t2 CHANGE f3 f3 VARCHAR(20); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SET foreign_key_checks = 0; +affected rows: 0 +ALTER TABLE t3 ADD FOREIGN KEY `fidx`(f2) REFERENCES t2(f1); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SET foreign_key_checks = 1; +affected rows: 0 +ALTER TABLE t3 DROP FOREIGN KEY `fidx`; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +DROP TABLE t3, t2; +affected rows: 0 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 79c0f2386aa..faf6ab7fa4a 100644 --- a/mysql-test/suite/innodb/r/innodb-online-alter-gis.result +++ b/mysql-test/suite/innodb/r/innodb-online-alter-gis.result @@ -1,13 +1,13 @@ create table t1(a int not null primary key, b geometry not null) engine=innodb; -ALTER ONLINE TABLE t1 ADD SPATIAL INDEX new(b); -ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED +ALTER ONLINE TABLE t1 ADD SPATIAL INDEX new(b), ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Do not support online operation on table with GIS index. Try ALGORITHM=NOCOPY show warnings; Level Code Message -Error 1846 LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED +Error 1846 ALGORITHM=INSTANT is not supported. Reason: Do not support online operation on table with GIS index. Try ALGORITHM=NOCOPY show errors; Level Code Message -Error 1846 LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED -ALTER ONLINE TABLE t1 ADD SPATIAL INDEX new(b), LOCK=SHARED; +Error 1846 ALGORITHM=INSTANT is not supported. Reason: Do not support online operation on table with GIS index. Try ALGORITHM=NOCOPY +ALTER ONLINE TABLE t1 ADD SPATIAL INDEX new(b), LOCK=SHARED, ALGORITHM=NOCOPY; show warnings; Level Code Message show errors; diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result index 5f2a10f82a9..389a04007e2 100644 --- a/mysql-test/suite/innodb/r/instant_alter_debug.result +++ b/mysql-test/suite/innodb/r/instant_alter_debug.result @@ -36,14 +36,7 @@ SELECT COUNT(*)>0 FROM INFORMATION_SCHEMA.COLUMNS LEFT JOIN t4 ON (NUMERIC_SCALE = pk); COUNT(*)>0 1 -SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL enter WAIT_FOR delete'; ALTER TABLE t4 ADD COLUMN c INT; -connect dml,localhost,root,,; -SET DEBUG_SYNC='now WAIT_FOR enter'; -DELETE FROM t4; -InnoDB 0 transactions not purged -SET DEBUG_SYNC='now SIGNAL delete'; -connection default; CREATE TABLE t5 (i INT, KEY(i)) ENGINE=InnoDB; INSERT INTO t5 VALUES (-42); ALTER TABLE t5 ADD UNIQUE ui(i); @@ -61,7 +54,7 @@ INSERT INTO t8 VALUES (NULL); ALTER TABLE t8 ADD c CHAR(3); SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR dml'; ALTER TABLE t8 FORCE; -connection dml; +connect dml,localhost,root,,; SET DEBUG_SYNC='now WAIT_FOR rebuilt'; BEGIN; INSERT INTO t8 SET i=1; diff --git a/mysql-test/suite/innodb/t/alter_algorithm.combinations b/mysql-test/suite/innodb/t/alter_algorithm.combinations new file mode 100644 index 00000000000..197748d168e --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_algorithm.combinations @@ -0,0 +1,11 @@ +[COPY] +--alter_algorithm=copy + +[INPLACE] +--alter_algorithm=inplace + +[NOCOPY] +--alter_algorithm=nocopy + +[INSTANT] +--alter_algorithm=instant diff --git a/mysql-test/suite/innodb/t/alter_algorithm.inc b/mysql-test/suite/innodb/t/alter_algorithm.inc new file mode 100644 index 00000000000..1aa3caf7d66 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_algorithm.inc @@ -0,0 +1,2 @@ +# See also alter_algorithm.combinations +--source include/have_innodb.inc diff --git a/mysql-test/suite/innodb/t/alter_algorithm.test b/mysql-test/suite/innodb/t/alter_algorithm.test new file mode 100644 index 00000000000..5a720489281 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_algorithm.test @@ -0,0 +1,22 @@ +--source include/have_innodb.inc +let $algorithm = `SELECT @@ALTER_ALGORITHM`; +let $error_code = 0; + +if ($algorithm == "NOCOPY") { + let $error_code = ER_ALTER_OPERATION_NOT_SUPPORTED; +} + +if ($algorithm == "INSTANT") { + let $error_code = ER_ALTER_OPERATION_NOT_SUPPORTED, ER_ALTER_OPERATION_NOT_SUPPORTED_REASON; +} + +--source include/alter_nocopy_fail.inc + +if ($algorithm == "NOCOPY") { + let $error_code = 0; +} + +if ($algorithm == "INSTANT") { + let $error_code = ER_ALTER_OPERATION_NOT_SUPPORTED_REASON; +} +--source include/alter_nocopy.inc diff --git a/mysql-test/suite/innodb/t/alter_instant.test b/mysql-test/suite/innodb/t/alter_instant.test new file mode 100644 index 00000000000..dddb7b8ce27 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_instant.test @@ -0,0 +1,45 @@ +--source alter_algorithm.inc + +CREATE TABLE t1(f1 INT NOT NULL, + f2 INT NOT NULL, + f3 INT AS (f2 * f2) VIRTUAL, + INDEX idx (f2))engine=innodb; + +CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL, + f3 VARCHAR(10), + INDEX(f1))ENGINE=INNODB; + +INSERT INTO t1(f1, f2) VALUES(1, 1); + +select @@alter_algorithm; + +--enable_info +--echo # Add column at the end of the table +--eval ALTER TABLE t1 ADD COLUMN f4 char(100) default "BIG WALL" + +--echo # Change virtual column expression +--eval ALTER TABLE t1 CHANGE f3 f3 INT AS (f2 * f2) VIRTUAL + +--echo # Add virtual column +--eval ALTER TABLE t1 ADD COLUMN f5 INT AS (f2) VIRTUAL + +--echo # Rename Column +--eval ALTER TABLE t1 CHANGE f3 vcol INT AS (f2) VIRTUAL + +--echo # Rename table +--eval ALTER TABLE t1 RENAME t3 + +--echo # Drop Virtual Column +--eval ALTER TABLE t3 DROP COLUMN vcol + +--echo # Column length varies +--eval ALTER TABLE t2 CHANGE f3 f3 VARCHAR(20) + +SET foreign_key_checks = 0; +--eval ALTER TABLE t3 ADD FOREIGN KEY `fidx`(f2) REFERENCES t2(f1) + +SET foreign_key_checks = 1; +--eval ALTER TABLE t3 DROP FOREIGN KEY `fidx` + +DROP TABLE t3, t2; +--disable_info 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 2cb88d398bb..570e22d5dd1 100644 --- a/mysql-test/suite/innodb/t/innodb-online-alter-gis.test +++ b/mysql-test/suite/innodb/t/innodb-online-alter-gis.test @@ -2,10 +2,10 @@ create table t1(a int not null primary key, b geometry not null) engine=innodb; --error 1846 -ALTER ONLINE TABLE t1 ADD SPATIAL INDEX new(b); +ALTER ONLINE TABLE t1 ADD SPATIAL INDEX new(b), ALGORITHM=INSTANT; show warnings; show errors; -ALTER ONLINE TABLE t1 ADD SPATIAL INDEX new(b), LOCK=SHARED; +ALTER ONLINE TABLE t1 ADD SPATIAL INDEX new(b), LOCK=SHARED, ALGORITHM=NOCOPY; show warnings; show errors; drop table t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test index b4b64392245..9d85d281361 100644 --- a/mysql-test/suite/innodb/t/instant_alter_debug.test +++ b/mysql-test/suite/innodb/t/instant_alter_debug.test @@ -40,16 +40,7 @@ INSERT INTO t4 VALUES (0); ALTER TABLE t4 ADD COLUMN b INT; SELECT COUNT(*)>0 FROM INFORMATION_SCHEMA.COLUMNS LEFT JOIN t4 ON (NUMERIC_SCALE = pk); -SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL enter WAIT_FOR delete'; ---send ALTER TABLE t4 ADD COLUMN c INT; -connect (dml,localhost,root,,); -SET DEBUG_SYNC='now WAIT_FOR enter'; -DELETE FROM t4; ---source include/wait_all_purged.inc -SET DEBUG_SYNC='now SIGNAL delete'; -connection default; -reap; CREATE TABLE t5 (i INT, KEY(i)) ENGINE=InnoDB; INSERT INTO t5 VALUES (-42); @@ -72,7 +63,7 @@ ALTER TABLE t8 ADD c CHAR(3); SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR dml'; --send ALTER TABLE t8 FORCE; -connection dml; +connect (dml,localhost,root,,); SET DEBUG_SYNC='now WAIT_FOR rebuilt'; BEGIN; INSERT INTO t8 SET i=1; 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 3bf15bd98b5..c5c549f67a1 100644 --- a/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result +++ b/mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result @@ -10,7 +10,9 @@ 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); +ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY; +ERROR 0A000: ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE +ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=INPLACE; Warnings: Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID SELECT * FROM fts_test WHERE MATCH (title, body) @@ -26,7 +28,7 @@ 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); +ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY; SELECT * FROM fts_test WHERE MATCH (title, body) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); id title body @@ -68,7 +70,6 @@ FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT ) ENGINE=InnoDB; -create unique index FTS_DOC_ID_INDEX on fts_test(FTS_DOC_ID); INSERT INTO fts_test (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...') , ('How To Use MySQL Well','After you went through a ...'), @@ -78,7 +79,7 @@ INSERT INTO fts_test (title,body) VALUES ('MySQL Security','When configured properly, MySQL ...'); CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED -CREATE FULLTEXT INDEX idx on fts_test (title, body); +ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY; ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED ALTER TABLE fts_test ROW_FORMAT=REDUNDANT; @@ -95,7 +96,6 @@ FTS_DOC_ID title body 1 MySQL Tutorial DBMS stands for DataBase ... 3 Optimizing MySQL In this tutorial we will show ... drop index idx on fts_test; -drop index FTS_DOC_ID_INDEX on fts_test; CREATE FULLTEXT INDEX idx on fts_test (title, body); SELECT * FROM fts_test WHERE MATCH (title, body) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); @@ -202,7 +202,7 @@ FTS_DOC_ID title body DROP TABLE articles; create table articles(`FTS_DOC_ID` serial, `col32` timestamp not null,`col115` text) engine=innodb; -create fulltext index `idx5` on articles(`col115`) ; +create fulltext index `idx5` on articles(`col115`) ; alter ignore table articles add primary key (`col32`) ; drop table articles; CREATE TABLE articles ( 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 58282809083..ddd92556772 100644 --- a/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test +++ b/mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test @@ -18,14 +18,17 @@ INSERT INTO fts_test (title,body) VALUES ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); +# Table does rebuild when fts index builds for the first time +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY; + # Create the FTS index -CREATE FULLTEXT INDEX idx on fts_test (title, body); +ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=INPLACE; # Select word "tutorial" in the table SELECT * FROM fts_test WHERE MATCH (title, body) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE); - # Drop the FTS idx DROP INDEX idx ON fts_test; @@ -38,9 +41,8 @@ INSERT INTO fts_test (title,body) VALUES ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...'); - -# Recreate the FTS index -CREATE FULLTEXT INDEX idx on fts_test (title, body); +# FTS_DOC_ID hidden column and FTS_DOC_ID index exist +ALTER TABLE fts_test ADD FULLTEXT `idx` (title, body), ALGORITHM=NOCOPY; # Select word "tutorial" in the table SELECT * FROM fts_test WHERE MATCH (title, body) @@ -81,8 +83,6 @@ CREATE TABLE fts_test ( body TEXT ) ENGINE=InnoDB; -create unique index FTS_DOC_ID_INDEX on fts_test(FTS_DOC_ID); - # Insert six rows INSERT INTO fts_test (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...') , @@ -97,7 +97,7 @@ INSERT INTO fts_test (title,body) VALUES # 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; -CREATE FULLTEXT INDEX idx on fts_test (title, body); +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; @@ -117,8 +117,6 @@ SELECT * FROM fts_test WHERE MATCH (title, body) # Drop the FTS_DOC_ID_INDEX and try again drop index idx on fts_test; -drop index FTS_DOC_ID_INDEX on fts_test; - CREATE FULLTEXT INDEX idx on fts_test (title, body); SELECT * FROM fts_test WHERE MATCH (title, body) @@ -232,7 +230,7 @@ DROP TABLE articles; create table articles(`FTS_DOC_ID` serial, `col32` timestamp not null,`col115` text) engine=innodb; -create fulltext index `idx5` on articles(`col115`) ; +create fulltext index `idx5` on articles(`col115`) ; alter ignore table articles add primary key (`col32`) ; diff --git a/mysql-test/suite/sys_vars/r/old_alter_table_basic.result b/mysql-test/suite/sys_vars/r/old_alter_table_basic.result index 5cc17917242..8b668340d1a 100644 --- a/mysql-test/suite/sys_vars/r/old_alter_table_basic.result +++ b/mysql-test/suite/sys_vars/r/old_alter_table_basic.result @@ -1,45 +1,105 @@ SET @start_global_value = @@global.old_alter_table; SELECT @start_global_value; @start_global_value -0 +DEFAULT select @@global.old_alter_table; @@global.old_alter_table -0 +DEFAULT select @@session.old_alter_table; @@session.old_alter_table -0 +DEFAULT show global variables like 'old_alter_table'; Variable_name Value -old_alter_table OFF +old_alter_table DEFAULT show session variables like 'old_alter_table'; Variable_name Value -old_alter_table OFF +old_alter_table DEFAULT select * from information_schema.global_variables where variable_name='old_alter_table'; VARIABLE_NAME VARIABLE_VALUE -OLD_ALTER_TABLE OFF +OLD_ALTER_TABLE DEFAULT select * from information_schema.session_variables where variable_name='old_alter_table'; VARIABLE_NAME VARIABLE_VALUE -OLD_ALTER_TABLE OFF +OLD_ALTER_TABLE DEFAULT set global old_alter_table=1; -set session old_alter_table=ON; +set session old_alter_table=1; select @@global.old_alter_table; @@global.old_alter_table -1 +COPY select @@session.old_alter_table; @@session.old_alter_table -1 +COPY show global variables like 'old_alter_table'; Variable_name Value -old_alter_table ON +old_alter_table COPY show session variables like 'old_alter_table'; Variable_name Value -old_alter_table ON +old_alter_table COPY select * from information_schema.global_variables where variable_name='old_alter_table'; VARIABLE_NAME VARIABLE_VALUE -OLD_ALTER_TABLE ON +OLD_ALTER_TABLE COPY select * from information_schema.session_variables where variable_name='old_alter_table'; VARIABLE_NAME VARIABLE_VALUE -OLD_ALTER_TABLE ON +OLD_ALTER_TABLE COPY +set global old_alter_table=2; +set session old_alter_table=2; +select @@global.old_alter_table; +@@global.old_alter_table +INPLACE +select @@session.old_alter_table; +@@session.old_alter_table +INPLACE +show global variables like 'old_alter_table'; +Variable_name Value +old_alter_table INPLACE +show session variables like 'old_alter_table'; +Variable_name Value +old_alter_table INPLACE +select * from information_schema.global_variables where variable_name='old_alter_table'; +VARIABLE_NAME VARIABLE_VALUE +OLD_ALTER_TABLE INPLACE +select * from information_schema.session_variables where variable_name='old_alter_table'; +VARIABLE_NAME VARIABLE_VALUE +OLD_ALTER_TABLE INPLACE +set global old_alter_table=3; +set session old_alter_table=3; +select @@global.old_alter_table; +@@global.old_alter_table +NOCOPY +select @@session.old_alter_table; +@@session.old_alter_table +NOCOPY +show global variables like 'old_alter_table'; +Variable_name Value +old_alter_table NOCOPY +show session variables like 'old_alter_table'; +Variable_name Value +old_alter_table NOCOPY +select * from information_schema.global_variables where variable_name='old_alter_table'; +VARIABLE_NAME VARIABLE_VALUE +OLD_ALTER_TABLE NOCOPY +select * from information_schema.session_variables where variable_name='old_alter_table'; +VARIABLE_NAME VARIABLE_VALUE +OLD_ALTER_TABLE NOCOPY +set global old_alter_table=4; +set session old_alter_table=4; +select @@global.old_alter_table; +@@global.old_alter_table +INSTANT +select @@session.old_alter_table; +@@session.old_alter_table +INSTANT +show global variables like 'old_alter_table'; +Variable_name Value +old_alter_table INSTANT +show session variables like 'old_alter_table'; +Variable_name Value +old_alter_table INSTANT +select * from information_schema.global_variables where variable_name='old_alter_table'; +VARIABLE_NAME VARIABLE_VALUE +OLD_ALTER_TABLE INSTANT +select * from information_schema.session_variables where variable_name='old_alter_table'; +VARIABLE_NAME VARIABLE_VALUE +OLD_ALTER_TABLE INSTANT set global old_alter_table=1.1; ERROR 42000: Incorrect argument type to variable 'old_alter_table' set global old_alter_table=1e1; @@ -49,4 +109,4 @@ ERROR 42000: Variable 'old_alter_table' can't be set to the value of 'foo' SET @@global.old_alter_table = @start_global_value; SELECT @@global.old_alter_table; @@global.old_alter_table -0 +DEFAULT diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index b81a1ac58dd..89b7b5c97cf 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -26,6 +26,20 @@ variable_name not in ( 'version_malloc_library', 'version_ssl_library', 'version' ) order by variable_name; +VARIABLE_NAME ALTER_ALGORITHM +SESSION_VALUE DEFAULT +GLOBAL_VALUE DEFAULT +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE DEFAULT +VARIABLE_SCOPE SESSION +VARIABLE_TYPE ENUM +VARIABLE_COMMENT Specify the alter table algorithm +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST DEFAULT,COPY,INPLACE,NOCOPY,INSTANT +READ_ONLY NO +COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME AUTOCOMMIT SESSION_VALUE ON GLOBAL_VALUE ON @@ -2589,17 +2603,17 @@ ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME OLD_ALTER_TABLE -SESSION_VALUE OFF -GLOBAL_VALUE OFF +SESSION_VALUE DEFAULT +GLOBAL_VALUE DEFAULT GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE OFF +DEFAULT_VALUE DEFAULT VARIABLE_SCOPE SESSION -VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Use old, non-optimized alter table +VARIABLE_TYPE ENUM +VARIABLE_COMMENT Alias for alter_algorithm. Deprecated. Use --alter-algorithm instead. NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST OFF,ON +ENUM_VALUE_LIST DEFAULT,COPY,INPLACE,NOCOPY,INSTANT READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME OLD_MODE diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 388bd485c71..3246d00d20e 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -26,6 +26,20 @@ variable_name not in ( 'version_malloc_library', 'version_ssl_library', 'version' ) order by variable_name; +VARIABLE_NAME ALTER_ALGORITHM +SESSION_VALUE DEFAULT +GLOBAL_VALUE DEFAULT +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE DEFAULT +VARIABLE_SCOPE SESSION +VARIABLE_TYPE ENUM +VARIABLE_COMMENT Specify the alter table algorithm +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST DEFAULT,COPY,INPLACE,NOCOPY,INSTANT +READ_ONLY NO +COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME AUTOCOMMIT SESSION_VALUE ON GLOBAL_VALUE ON @@ -2799,17 +2813,17 @@ ENUM_VALUE_LIST OFF,ON READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME OLD_ALTER_TABLE -SESSION_VALUE OFF -GLOBAL_VALUE OFF +SESSION_VALUE DEFAULT +GLOBAL_VALUE DEFAULT GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE OFF +DEFAULT_VALUE DEFAULT VARIABLE_SCOPE SESSION -VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Use old, non-optimized alter table +VARIABLE_TYPE ENUM +VARIABLE_COMMENT Alias for alter_algorithm. Deprecated. Use --alter-algorithm instead. NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST OFF,ON +ENUM_VALUE_LIST DEFAULT,COPY,INPLACE,NOCOPY,INSTANT READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME OLD_MODE diff --git a/mysql-test/suite/sys_vars/t/old_alter_table_basic.test b/mysql-test/suite/sys_vars/t/old_alter_table_basic.test index cce180fea67..9a6cb4779e5 100644 --- a/mysql-test/suite/sys_vars/t/old_alter_table_basic.test +++ b/mysql-test/suite/sys_vars/t/old_alter_table_basic.test @@ -20,7 +20,7 @@ select * from information_schema.session_variables where variable_name='old_alte # show that it's writable # set global old_alter_table=1; -set session old_alter_table=ON; +set session old_alter_table=1; select @@global.old_alter_table; select @@session.old_alter_table; show global variables like 'old_alter_table'; @@ -28,6 +28,32 @@ show session variables like 'old_alter_table'; select * from information_schema.global_variables where variable_name='old_alter_table'; select * from information_schema.session_variables where variable_name='old_alter_table'; +set global old_alter_table=2; +set session old_alter_table=2; +select @@global.old_alter_table; +select @@session.old_alter_table; +show global variables like 'old_alter_table'; +show session variables like 'old_alter_table'; +select * from information_schema.global_variables where variable_name='old_alter_table'; +select * from information_schema.session_variables where variable_name='old_alter_table'; + +set global old_alter_table=3; +set session old_alter_table=3; +select @@global.old_alter_table; +select @@session.old_alter_table; +show global variables like 'old_alter_table'; +show session variables like 'old_alter_table'; +select * from information_schema.global_variables where variable_name='old_alter_table'; +select * from information_schema.session_variables where variable_name='old_alter_table'; + +set global old_alter_table=4; +set session old_alter_table=4; +select @@global.old_alter_table; +select @@session.old_alter_table; +show global variables like 'old_alter_table'; +show session variables like 'old_alter_table'; +select * from information_schema.global_variables where variable_name='old_alter_table'; +select * from information_schema.session_variables where variable_name='old_alter_table'; # # incorrect types # diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index a6f1bb38a76..fc9d225d388 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -454,8 +454,6 @@ t CREATE TABLE `t` ( `c` text DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING alter table t add fulltext key (c); -Warnings: -Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID create or replace table t (a int) with system versioning; alter table t drop column a; ERROR HY000: Table `t` must have at least one versioned column diff --git a/sql/handler.cc b/sql/handler.cc index 10db495d34b..7323d4c1d9d 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -4471,7 +4471,7 @@ handler::check_if_supported_inplace_alter(TABLE *altered_table, } void Alter_inplace_info::report_unsupported_error(const char *not_supported, - const char *try_instead) + const char *try_instead) const { if (unsupported_reason == NULL) my_error(ER_ALTER_OPERATION_NOT_SUPPORTED, MYF(0), diff --git a/sql/handler.h b/sql/handler.h index 549fcb264be..9c4766c4f39 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -73,11 +73,14 @@ class sequence_definition; */ enum enum_alter_inplace_result { HA_ALTER_ERROR, + HA_ALTER_INPLACE_COPY_NO_LOCK, + HA_ALTER_INPLACE_COPY_LOCK, + HA_ALTER_INPLACE_NOCOPY_LOCK, + HA_ALTER_INPLACE_NOCOPY_NO_LOCK, + HA_ALTER_INPLACE_INSTANT, HA_ALTER_INPLACE_NOT_SUPPORTED, HA_ALTER_INPLACE_EXCLUSIVE_LOCK, - HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE, HA_ALTER_INPLACE_SHARED_LOCK, - HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, HA_ALTER_INPLACE_NO_LOCK }; @@ -2304,8 +2307,7 @@ public: /** Can be set by handler to describe why a given operation cannot be done in-place (HA_ALTER_INPLACE_NOT_SUPPORTED) or why it cannot be done - online (HA_ALTER_INPLACE_NO_LOCK or - HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE) + online (HA_ALTER_INPLACE_NO_LOCK or HA_ALTER_INPLACE_COPY_NO_LOCK) If set, it will be used with ER_ALTER_OPERATION_NOT_SUPPORTED_REASON if results from handler::check_if_supported_inplace_alter() doesn't match requirements set by user. If not set, the more generic @@ -2353,7 +2355,7 @@ public: replace not_supported with. */ void report_unsupported_error(const char *not_supported, - const char *try_instead); + const char *try_instead) const; }; @@ -4060,8 +4062,8 @@ public: *) As the first step, we acquire a lock corresponding to the concurrency level which was returned by handler::check_if_supported_inplace_alter() and requested by the user. This lock is held for most of the - duration of in-place ALTER (if HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE - or HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE were returned we acquire an + duration of in-place ALTER (if HA_ALTER_INPLACE_COPY_LOCK + or HA_ALTER_INPLACE_COPY_NO_LOCK were returned we acquire an exclusive lock for duration of the next step only). *) After that we call handler::ha_prepare_inplace_alter_table() to give the storage engine a chance to update its internal structures with a higher @@ -4105,12 +4107,12 @@ public: @retval HA_ALTER_ERROR Unexpected error. @retval HA_ALTER_INPLACE_NOT_SUPPORTED Not supported, must use copy. @retval HA_ALTER_INPLACE_EXCLUSIVE_LOCK Supported, but requires X lock. - @retval HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE + @retval HA_ALTER_INPLACE_COPY_LOCK Supported, but requires SNW lock during main phase. Prepare phase requires X lock. @retval HA_ALTER_INPLACE_SHARED_LOCK Supported, but requires SNW lock. - @retval HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE + @retval HA_ALTER_INPLACE_COPY_NO_LOCK Supported, concurrent reads/writes allowed. However, prepare phase requires X lock. @@ -4170,10 +4172,9 @@ protected: /** Allows the storage engine to update internal structures with concurrent writes blocked. If check_if_supported_inplace_alter() returns - HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE or - HA_ALTER_INPLACE_SHARED_AFTER_PREPARE, this function is called with - exclusive lock otherwise the same level of locking as for - inplace_alter_table() will be used. + HA_ALTER_INPLACE_COPY_NO_LOCK or HA_ALTER_INPLACE_COPY_LOCK, + this function is called with exclusive lock otherwise the same level + of locking as for inplace_alter_table() will be used. @note Storage engines are responsible for reporting any errors by calling my_error()/print_error() diff --git a/sql/rpl_parallel.cc b/sql/rpl_parallel.cc index bdd68a33cc4..ad38e9f3568 100644 --- a/sql/rpl_parallel.cc +++ b/sql/rpl_parallel.cc @@ -1036,6 +1036,8 @@ handle_rpl_parallel_thread(void *arg) thd->system_thread= SYSTEM_THREAD_SLAVE_SQL; thd->security_ctx->skip_grants(); thd->variables.max_allowed_packet= slave_max_allowed_packet; + /* Ensure that slave can exeute any alter table it gets from master */ + thd->variables.alter_algorithm= (ulong) Alter_info::ALTER_TABLE_ALGORITHM_DEFAULT; thd->slave_thread= 1; set_slave_thread_options(thd); diff --git a/sql/slave.cc b/sql/slave.cc index 1856ddd5377..cdf3dfaae45 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -5278,6 +5278,10 @@ pthread_handler_t handle_slave_sql(void *arg) applied. In all other cases it must be FALSE. */ thd->variables.binlog_annotate_row_events= 0; + + /* Ensure that slave can exeute any alter table it gets from master */ + thd->variables.alter_algorithm= (ulong) Alter_info::ALTER_TABLE_ALGORITHM_DEFAULT; + add_to_active_threads(thd); /* We are going to set slave_running to 1. Assuming slave I/O thread is diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc index 54f64b47348..0825e9754ad 100644 --- a/sql/sql_alter.cc +++ b/sql/sql_alter.cc @@ -60,6 +60,10 @@ bool Alter_info::set_requested_algorithm(const LEX_CSTRING *str) requested_algorithm= ALTER_TABLE_ALGORITHM_COPY; else if (lex_string_eq(str, STRING_WITH_LEN("DEFAULT"))) requested_algorithm= ALTER_TABLE_ALGORITHM_DEFAULT; + else if (lex_string_eq(str, STRING_WITH_LEN("NOCOPY"))) + requested_algorithm= ALTER_TABLE_ALGORITHM_NOCOPY; + else if (lex_string_eq(str, STRING_WITH_LEN("INSTANT"))) + requested_algorithm= ALTER_TABLE_ALGORITHM_INSTANT; else return true; return false; @@ -82,6 +86,128 @@ bool Alter_info::set_requested_lock(const LEX_CSTRING *str) return false; } +const char* Alter_info::algorithm() const +{ + switch (requested_algorithm) { + case ALTER_TABLE_ALGORITHM_INPLACE: + return "ALGORITHM=INPLACE"; + case ALTER_TABLE_ALGORITHM_COPY: + return "ALGORITHM=COPY"; + case ALTER_TABLE_ALGORITHM_DEFAULT: + return "ALGORITHM=DEFAULT"; + case ALTER_TABLE_ALGORITHM_NOCOPY: + return "ALGORITHM=NOCOPY"; + case ALTER_TABLE_ALGORITHM_INSTANT: + return "ALGORITHM=INSTANT"; + } + + return NULL; /* purecov: begin deadcode */ +} + +const char* Alter_info::lock() const +{ + switch (requested_lock) { + case ALTER_TABLE_LOCK_SHARED: + return "LOCK=SHARED"; + case ALTER_TABLE_LOCK_NONE: + return "LOCK=NONE"; + case ALTER_TABLE_LOCK_DEFAULT: + return "LOCK=DEFAULT"; + case ALTER_TABLE_LOCK_EXCLUSIVE: + return "LOCK=EXCLUSIVE"; + } + return NULL; /* purecov: begin deadcode */ +} + + +bool Alter_info::supports_algorithm(THD *thd, enum_alter_inplace_result result, + const Alter_inplace_info *ha_alter_info) +{ + if (requested_algorithm == Alter_info::ALTER_TABLE_ALGORITHM_DEFAULT) + requested_algorithm = (Alter_info::enum_alter_table_algorithm) thd->variables.alter_algorithm; + + switch (result) { + case HA_ALTER_INPLACE_EXCLUSIVE_LOCK: + case HA_ALTER_INPLACE_SHARED_LOCK: + case HA_ALTER_INPLACE_NO_LOCK: + case HA_ALTER_INPLACE_INSTANT: + return false; + case HA_ALTER_INPLACE_COPY_NO_LOCK: + case HA_ALTER_INPLACE_COPY_LOCK: + if (requested_algorithm >= Alter_info::ALTER_TABLE_ALGORITHM_NOCOPY) + { + ha_alter_info->report_unsupported_error(algorithm(), + "ALGORITHM=INPLACE"); + return true; + } + return false; + case HA_ALTER_INPLACE_NOCOPY_NO_LOCK: + case HA_ALTER_INPLACE_NOCOPY_LOCK: + if (requested_algorithm == Alter_info::ALTER_TABLE_ALGORITHM_INSTANT) + { + ha_alter_info->report_unsupported_error("ALGORITHM=INSTANT", + "ALGORITHM=NOCOPY"); + return true; + } + return false; + case HA_ALTER_INPLACE_NOT_SUPPORTED: + if (requested_algorithm >= Alter_info::ALTER_TABLE_ALGORITHM_INPLACE) + { + ha_alter_info->report_unsupported_error(algorithm(), + "ALGORITHM=COPY"); + return true; + } + return false; + case HA_ALTER_ERROR: + return true; + } + /* purecov: begin deadcode */ + DBUG_ASSERT(0); + return false; +} + + +bool Alter_info::supports_lock(THD *thd, enum_alter_inplace_result result, + const Alter_inplace_info *ha_alter_info) +{ + switch (result) { + case HA_ALTER_INPLACE_EXCLUSIVE_LOCK: + // If SHARED lock and no particular algorithm was requested, use COPY. + if (requested_lock == Alter_info::ALTER_TABLE_LOCK_SHARED && + requested_algorithm == Alter_info::ALTER_TABLE_ALGORITHM_DEFAULT && + thd->variables.alter_algorithm == + Alter_info::ALTER_TABLE_ALGORITHM_DEFAULT) + return false; + + if (requested_lock == Alter_info::ALTER_TABLE_LOCK_SHARED || + requested_lock == Alter_info::ALTER_TABLE_LOCK_NONE) + { + ha_alter_info->report_unsupported_error(lock(), "LOCK=EXCLUSIVE"); + return true; + } + return false; + case HA_ALTER_INPLACE_NO_LOCK: + case HA_ALTER_INPLACE_INSTANT: + case HA_ALTER_INPLACE_COPY_NO_LOCK: + case HA_ALTER_INPLACE_NOCOPY_NO_LOCK: + return false; + case HA_ALTER_INPLACE_COPY_LOCK: + case HA_ALTER_INPLACE_NOCOPY_LOCK: + case HA_ALTER_INPLACE_NOT_SUPPORTED: + 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; + } + return false; + case HA_ALTER_ERROR: + return true; + } + /* purecov: begin deadcode */ + DBUG_ASSERT(0); + return false; +} Alter_table_ctx::Alter_table_ctx() : datetime_field(NULL), error_if_not_empty(false), diff --git a/sql/sql_alter.h b/sql/sql_alter.h index 85a7b993e12..268dbc43abd 100644 --- a/sql/sql_alter.h +++ b/sql/sql_alter.h @@ -47,14 +47,24 @@ public: */ enum enum_alter_table_algorithm { - // In-place if supported, copy otherwise. +/* + Use thd->variables.alter_algorithm for alter method. If this is also + default then use the fastest possible ALTER TABLE method + (INSTANT, NOCOPY, INPLACE, COPY) +*/ ALTER_TABLE_ALGORITHM_DEFAULT, + // Copy if supported, error otherwise. + ALTER_TABLE_ALGORITHM_COPY, + // In-place if supported, error otherwise. ALTER_TABLE_ALGORITHM_INPLACE, - // Copy if supported, error otherwise. - ALTER_TABLE_ALGORITHM_COPY + // No Copy will refuse any operation which does rebuild. + ALTER_TABLE_ALGORITHM_NOCOPY, + + // Instant should allow any operation that changes metadata only. + ALTER_TABLE_ALGORITHM_INSTANT }; @@ -67,7 +77,7 @@ public: // Maximum supported level of concurency for the given operation. ALTER_TABLE_LOCK_DEFAULT, - // Allow concurrent reads & writes. If not supported, give erorr. + // Allow concurrent reads & writes. If not supported, give error. ALTER_TABLE_LOCK_NONE, // Allow concurrent reads only. If not supported, give error. @@ -174,6 +184,45 @@ public: bool set_requested_lock(const LEX_CSTRING *str); + /** + Returns the algorithm value in the format "algorithm=value" + */ + const char* algorithm() const; + + /** + Returns the lock value in the format "lock=value" + */ + const char* lock() const; + + /** + Check whether the given result can be supported + with the specified user alter algorithm. + + @param thd Thread handle + @param result Operation supported for inplace alter + @param ha_alter_info Structure describing changes to be done + by ALTER TABLE and holding data during + in-place alter + @retval false Supported operation + @retval true Not supported value + */ + bool supports_algorithm(THD *thd, enum_alter_inplace_result result, + const Alter_inplace_info *ha_alter_info); + + /** + Check whether the given result can be supported + with the specified user lock type. + + @param result Operation supported for inplace alter + @param ha_alter_info Structure describing changes to be done + by ALTER TABLE and holding data during + in-place alter + @retval false Supported lock type + @retval true Not supported value + */ + bool supports_lock(THD *thd, enum_alter_inplace_result result, + const Alter_inplace_info *ha_alter_info); + private: Alter_info &operator=(const Alter_info &rhs); // not implemented Alter_info(const Alter_info &rhs); // not implemented diff --git a/sql/sql_class.h b/sql/sql_class.h index dc67f487a57..28f785d06a7 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -632,6 +632,7 @@ typedef struct system_variables ulong query_cache_type; ulong tx_isolation; ulong updatable_views_with_limit; + ulong alter_algorithm; int max_user_connections; ulong server_id; /** @@ -655,7 +656,6 @@ typedef struct system_variables my_bool keep_files_on_create; my_bool old_mode; - my_bool old_alter_table; my_bool old_passwords; my_bool big_tables; my_bool only_standard_compliant_cte; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 4570be0c6ce..51f1fa83ccb 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -7370,8 +7370,11 @@ static bool mysql_inplace_alter_table(THD *thd, exclusive lock is required for duration of the whole statement. */ if (inplace_supported == HA_ALTER_INPLACE_EXCLUSIVE_LOCK || - ((inplace_supported == HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE || - inplace_supported == HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE) && + ((inplace_supported == HA_ALTER_INPLACE_COPY_NO_LOCK || + inplace_supported == HA_ALTER_INPLACE_COPY_LOCK || + inplace_supported == HA_ALTER_INPLACE_NOCOPY_NO_LOCK || + inplace_supported == HA_ALTER_INPLACE_NOCOPY_LOCK || + inplace_supported == HA_ALTER_INPLACE_INSTANT) && (thd->locked_tables_mode == LTM_LOCK_TABLES || thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES)) || alter_info->requested_lock == Alter_info::ALTER_TABLE_LOCK_EXCLUSIVE) @@ -7395,8 +7398,11 @@ static bool mysql_inplace_alter_table(THD *thd, */ reopen_tables= true; } - else if (inplace_supported == HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE || - inplace_supported == HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE) + else if (inplace_supported == HA_ALTER_INPLACE_COPY_LOCK || + inplace_supported == HA_ALTER_INPLACE_COPY_NO_LOCK || + inplace_supported == HA_ALTER_INPLACE_NOCOPY_LOCK || + inplace_supported == HA_ALTER_INPLACE_NOCOPY_NO_LOCK || + inplace_supported == HA_ALTER_INPLACE_INSTANT) { /* Storage engine has requested exclusive lock only for prepare phase @@ -7441,7 +7447,9 @@ static bool mysql_inplace_alter_table(THD *thd, DBUG_ASSERT(0); // fall through case HA_ALTER_INPLACE_NO_LOCK: - case HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE: + case HA_ALTER_INPLACE_INSTANT: + case HA_ALTER_INPLACE_COPY_NO_LOCK: + case HA_ALTER_INPLACE_NOCOPY_NO_LOCK: switch (alter_info->requested_lock) { case Alter_info::ALTER_TABLE_LOCK_DEFAULT: case Alter_info::ALTER_TABLE_LOCK_NONE: @@ -7453,8 +7461,9 @@ static bool mysql_inplace_alter_table(THD *thd, } break; case HA_ALTER_INPLACE_EXCLUSIVE_LOCK: - case HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE: case HA_ALTER_INPLACE_SHARED_LOCK: + case HA_ALTER_INPLACE_COPY_LOCK: + case HA_ALTER_INPLACE_NOCOPY_LOCK: break; } @@ -7469,19 +7478,23 @@ static bool mysql_inplace_alter_table(THD *thd, necessary only for prepare phase (unless we are not under LOCK TABLES) and user has not explicitly requested exclusive lock. */ - if ((inplace_supported == HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE || - inplace_supported == HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE) && + if ((inplace_supported == HA_ALTER_INPLACE_COPY_NO_LOCK || + inplace_supported == HA_ALTER_INPLACE_COPY_LOCK || + inplace_supported == HA_ALTER_INPLACE_NOCOPY_LOCK || + inplace_supported == HA_ALTER_INPLACE_NOCOPY_NO_LOCK) && !(thd->locked_tables_mode == LTM_LOCK_TABLES || thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES) && (alter_info->requested_lock != Alter_info::ALTER_TABLE_LOCK_EXCLUSIVE)) { /* If storage engine or user requested shared lock downgrade to SNW. */ - if (inplace_supported == HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE || + if (inplace_supported == HA_ALTER_INPLACE_COPY_LOCK || + inplace_supported == HA_ALTER_INPLACE_NOCOPY_LOCK || alter_info->requested_lock == Alter_info::ALTER_TABLE_LOCK_SHARED) table->mdl_ticket->downgrade_lock(MDL_SHARED_NO_WRITE); else { - DBUG_ASSERT(inplace_supported == HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE); + DBUG_ASSERT(inplace_supported == HA_ALTER_INPLACE_COPY_NO_LOCK || + inplace_supported == HA_ALTER_INPLACE_NOCOPY_NO_LOCK); table->mdl_ticket->downgrade_lock(MDL_SHARED_UPGRADABLE); } } @@ -9389,7 +9402,7 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db, supports auto-partitioning as such engines can do some changes using in-place API. */ - if ((thd->variables.old_alter_table && + if ((thd->variables.alter_algorithm == Alter_info::ALTER_TABLE_ALGORITHM_COPY && alter_info->requested_algorithm != Alter_info::ALTER_TABLE_ALGORITHM_INPLACE) || is_inplace_alter_impossible(table, create_info, alter_info) @@ -9597,74 +9610,32 @@ bool mysql_alter_table(THD *thd, const LEX_CSTRING *new_db, goto err_new_table_cleanup; thd->count_cuted_fields= CHECK_FIELD_IGNORE; + if (alter_info->requested_lock == Alter_info::ALTER_TABLE_LOCK_NONE) + ha_alter_info.online= true; // Ask storage engine whether to use copy or in-place enum_alter_inplace_result inplace_supported= table->file->check_if_supported_inplace_alter(altered_table, &ha_alter_info); - switch (inplace_supported) { - case HA_ALTER_INPLACE_EXCLUSIVE_LOCK: - // If SHARED lock and no particular algorithm was requested, use COPY. - if (alter_info->requested_lock == - Alter_info::ALTER_TABLE_LOCK_SHARED && - alter_info->requested_algorithm == - Alter_info::ALTER_TABLE_ALGORITHM_DEFAULT) - { - use_inplace= false; - } - // Otherwise, if weaker lock was requested, report errror. - else if (alter_info->requested_lock == - Alter_info::ALTER_TABLE_LOCK_NONE || - alter_info->requested_lock == - Alter_info::ALTER_TABLE_LOCK_SHARED) - { - ha_alter_info.report_unsupported_error("LOCK=NONE/SHARED", - "LOCK=EXCLUSIVE"); - thd->drop_temporary_table(altered_table, NULL, false); - goto err_new_table_cleanup; - } - break; - case HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE: - case HA_ALTER_INPLACE_SHARED_LOCK: - // If weaker lock was requested, report errror. - if (alter_info->requested_lock == - Alter_info::ALTER_TABLE_LOCK_NONE) - { - ha_alter_info.report_unsupported_error("LOCK=NONE", "LOCK=SHARED"); - thd->drop_temporary_table(altered_table, NULL, false); - goto err_new_table_cleanup; - } - break; - case HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE: - case HA_ALTER_INPLACE_NO_LOCK: - break; - case HA_ALTER_INPLACE_NOT_SUPPORTED: - // If INPLACE was requested, report error. - if (alter_info->requested_algorithm == - Alter_info::ALTER_TABLE_ALGORITHM_INPLACE) - { - ha_alter_info.report_unsupported_error("ALGORITHM=INPLACE", - "ALGORITHM=COPY"); - thd->drop_temporary_table(altered_table, NULL, false); - goto err_new_table_cleanup; - } - // COPY with LOCK=NONE is not supported, no point in trying. - if (alter_info->requested_lock == - Alter_info::ALTER_TABLE_LOCK_NONE) - { - ha_alter_info.report_unsupported_error("LOCK=NONE", "LOCK=SHARED"); - thd->drop_temporary_table(altered_table, NULL, false); - goto err_new_table_cleanup; - } - // Otherwise use COPY - use_inplace= false; - break; - case HA_ALTER_ERROR: - default: + if (alter_info->supports_algorithm(thd, inplace_supported, &ha_alter_info) || + alter_info->supports_lock(thd, inplace_supported, &ha_alter_info)) + { thd->drop_temporary_table(altered_table, NULL, false); goto err_new_table_cleanup; } + // If SHARED lock and no particular algorithm was requested, use COPY. + if (inplace_supported == HA_ALTER_INPLACE_EXCLUSIVE_LOCK && + alter_info->requested_lock == Alter_info::ALTER_TABLE_LOCK_SHARED && + alter_info->requested_algorithm == + Alter_info::ALTER_TABLE_ALGORITHM_DEFAULT && + thd->variables.alter_algorithm == + Alter_info::ALTER_TABLE_ALGORITHM_DEFAULT) + use_inplace= false; + + if (inplace_supported == HA_ALTER_INPLACE_NOT_SUPPORTED) + use_inplace= false; + if (use_inplace) { table->s->frm_image= &frm; diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index bced6e123ad..3cd7f1a6dd2 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2371,9 +2371,19 @@ static Sys_var_mybool Sys_old_mode( "old", "Use compatible behavior from previous MariaDB version. See also --old-mode", SESSION_VAR(old_mode), CMD_LINE(OPT_ARG), DEFAULT(FALSE)); -static Sys_var_mybool Sys_old_alter_table( - "old_alter_table", "Use old, non-optimized alter table", - SESSION_VAR(old_alter_table), CMD_LINE(OPT_ARG), DEFAULT(FALSE)); +static const char *alter_algorithm_modes[]= {"DEFAULT", "COPY", "INPLACE", +"NOCOPY", "INSTANT", NULL}; + +static Sys_var_enum Sys_alter_algorithm( + "alter_algorithm", "Specify the alter table algorithm", + SESSION_VAR(alter_algorithm), CMD_LINE(OPT_ARG), + alter_algorithm_modes, DEFAULT(0)); + +static Sys_var_enum Sys_old_alter_table( + "old_alter_table", "Alias for alter_algorithm. " + "Deprecated. Use --alter-algorithm instead.", + SESSION_VAR(alter_algorithm), CMD_LINE(OPT_ARG), + alter_algorithm_modes, DEFAULT(0)); static bool check_old_passwords(sys_var *self, THD *thd, set_var *var) { diff --git a/storage/connect/ha_connect.cc b/storage/connect/ha_connect.cc index 1bb689000f8..65e2a38166b 100644 --- a/storage/connect/ha_connect.cc +++ b/storage/connect/ha_connect.cc @@ -6882,12 +6882,12 @@ bool ha_connect::NoFieldOptionChange(TABLE *tab) @retval HA_ALTER_ERROR Unexpected error. @retval HA_ALTER_INPLACE_NOT_SUPPORTED Not supported, must use copy. @retval HA_ALTER_INPLACE_EXCLUSIVE_LOCK Supported, but requires X lock. - @retval HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE + @retval HA_ALTER_INPLACE_COPY_LOCK Supported, but requires SNW lock during main phase. Prepare phase requires X lock. @retval HA_ALTER_INPLACE_SHARED_LOCK Supported, but requires SNW lock. - @retval HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE + @retval HA_ALTER_INPLACE_COPY_NO_LOCK Supported, concurrent reads/writes allowed. However, prepare phase requires X lock. diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h index 512a4212451..d4fda9d43c5 100644 --- a/storage/innobase/handler/ha_innodb.h +++ b/storage/innobase/handler/ha_innodb.h @@ -298,12 +298,24 @@ public: by ALTER TABLE and holding data used during in-place alter. @retval HA_ALTER_INPLACE_NOT_SUPPORTED Not supported - @retval HA_ALTER_INPLACE_NO_LOCK Supported - @retval HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE - Supported, but requires lock during main phase and - exclusive lock during prepare phase. - @retval HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE - Supported, prepare phase requires exclusive lock. */ + @retval HA_ALTER_INPLACE_INSTANT + MDL_EXCLUSIVE is needed for executing prepare_inplace_alter_table() + and commit_inplace_alter_table(). inplace_alter_table() + will not be called. + @retval HA_ALTER_INPLACE_COPY_NO_LOCK + MDL_EXCLUSIVE in prepare_inplace_alter_table(), which can be downgraded + to LOCK=NONE for rebuilding the table in inplace_alter_table() + @retval HA_ALTER_INPLACE_COPY_LOCK + MDL_EXCLUSIVE in prepare_inplace_alter_table(), which can be downgraded + to LOCK=SHARED for rebuilding the table in inplace_alter_table() + @retval HA_ALTER_INPLACE_NOCOPY_NO_LOCK + MDL_EXCLUSIVE in prepare_inplace_alter_table(), which can be downgraded + to LOCK=NONE for inplace_alter_table() which will not rebuild the table + @retval HA_ALTER_INPLACE_NOCOPY_LOCK + MDL_EXCLUSIVE in prepare_inplace_alter_table(), which can be downgraded + to LOCK=SHARED for inplace_alter_table() which will not rebuild + the table. */ + enum_alter_inplace_result check_if_supported_inplace_alter( TABLE* altered_table, Alter_inplace_info* ha_alter_info); diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 686037cd70f..19da290e59f 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -115,17 +115,21 @@ static const alter_table_operations INNOBASE_FOREIGN_OPERATIONS /** Operations that InnoDB cares about and can perform without rebuild */ static const alter_table_operations INNOBASE_ALTER_NOREBUILD = INNOBASE_ONLINE_CREATE - | INNOBASE_FOREIGN_OPERATIONS | ALTER_DROP_NON_UNIQUE_NON_PRIM_INDEX | ALTER_DROP_UNIQUE_INDEX #ifdef MYSQL_RENAME_INDEX | ALTER_RENAME_INDEX #endif + ; + +/** Operations that can be performed instantly, without inplace_alter_table() */ +static const alter_table_operations INNOBASE_ALTER_INSTANT + = ALTER_VIRTUAL_COLUMN_ORDER | ALTER_COLUMN_NAME - | ALTER_COLUMN_EQUAL_PACK_LENGTH | ALTER_ADD_VIRTUAL_COLUMN - | ALTER_DROP_VIRTUAL_COLUMN - | ALTER_VIRTUAL_COLUMN_ORDER; + | INNOBASE_FOREIGN_OPERATIONS + | ALTER_COLUMN_EQUAL_PACK_LENGTH + | ALTER_DROP_VIRTUAL_COLUMN; static const alter_table_operations INNOBASE_DEFAULTS = ALTER_COLUMN_NOT_NULLABLE @@ -526,7 +530,8 @@ innobase_need_rebuild( const Alter_inplace_info* ha_alter_info, const TABLE* table) { - if ((ha_alter_info->handler_flags & ~INNOBASE_INPLACE_IGNORE) + if ((ha_alter_info->handler_flags & ~(INNOBASE_INPLACE_IGNORE + | INNOBASE_ALTER_INSTANT)) == ALTER_CHANGE_CREATE_OPTION) { return create_option_need_rebuild(ha_alter_info, table); } @@ -722,30 +727,125 @@ static bool set_default_value(Field* field) return false; } +/** Check whether the table has the FTS_DOC_ID column +@param[in] table InnoDB table with fulltext index +@param[in] altered_table MySQL table with fulltext index +@param[out] fts_doc_col_no The column number for Doc ID, + or ULINT_UNDEFINED if it is of wrong type +@param[out] num_v Number of virtual column +@param[in] check_only check only whether fts doc id exist. +@return whether there exists an FTS_DOC_ID column */ +static +bool +innobase_fts_check_doc_id_col( + const dict_table_t* table, + const TABLE* altered_table, + ulint* fts_doc_col_no, + ulint* num_v, + bool check_only=false) +{ + *fts_doc_col_no = ULINT_UNDEFINED; + + const uint n_cols = altered_table->s->fields; + ulint i; + int err = 0; + *num_v = 0; + + for (i = 0; i < n_cols; i++) { + const Field* field = altered_table->field[i]; + + if (innobase_is_v_fld(field)) { + (*num_v)++; + } + + if (my_strcasecmp(system_charset_info, + field->field_name.str, FTS_DOC_ID_COL_NAME)) { + continue; + } + + if (strcmp(field->field_name.str, FTS_DOC_ID_COL_NAME)) { + err = ER_WRONG_COLUMN_NAME; + } else if (field->type() != MYSQL_TYPE_LONGLONG + || field->pack_length() != 8 + || field->real_maybe_null() + || !(field->flags & UNSIGNED_FLAG) + || innobase_is_v_fld(field)) { + err = ER_INNODB_FT_WRONG_DOCID_COLUMN; + } else { + *fts_doc_col_no = i - *num_v; + } + + if (err && !check_only) { + my_error(err, MYF(0), field->field_name.str); + } + + return(true); + } + + if (!table) { + return(false); + } + + /* Not to count the virtual columns */ + i -= *num_v; + + for (; i + DATA_N_SYS_COLS < (uint) table->n_cols; i++) { + const char* name = dict_table_get_col_name(table, i); + + if (strcmp(name, FTS_DOC_ID_COL_NAME) == 0) { +#ifdef UNIV_DEBUG + const dict_col_t* col; + + col = dict_table_get_nth_col(table, i); + + /* Because the FTS_DOC_ID does not exist in + the MySQL data dictionary, this must be the + internally created FTS_DOC_ID column. */ + ut_ad(col->mtype == DATA_INT); + ut_ad(col->len == 8); + ut_ad(col->prtype & DATA_NOT_NULL); + ut_ad(col->prtype & DATA_UNSIGNED); +#endif /* UNIV_DEBUG */ + *fts_doc_col_no = i; + return(true); + } + } + + return(false); +} + /** Check if InnoDB supports a particular alter table in-place @param altered_table TABLE object for new version of table. @param ha_alter_info Structure describing changes to be done by ALTER TABLE and holding data used during in-place alter. @retval HA_ALTER_INPLACE_NOT_SUPPORTED Not supported -@retval HA_ALTER_INPLACE_NO_LOCK Supported -@retval HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE Supported, but requires -lock during main phase and exclusive lock during prepare phase. -@retval HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE Supported, prepare phase -requires exclusive lock (any transactions that have accessed the table -must commit or roll back first, and no transactions can access the table -while prepare_inplace_alter_table() is executing) +@retval HA_ALTER_INPLACE_INSTANT +MDL_EXCLUSIVE is needed for executing prepare_inplace_alter_table() +and commit_inplace_alter_table(). inplace_alter_table() will not be called. +@retval HA_ALTER_INPLACE_COPY_NO_LOCK +MDL_EXCLUSIVE in prepare_inplace_alter_table(), which can be downgraded to +LOCK=NONE for rebuilding the table in inplace_alter_table() +@retval HA_ALTER_INPLACE_COPY_LOCK +MDL_EXCLUSIVE in prepare_inplace_alter_table(), which can be downgraded to +LOCK=SHARED for rebuilding the table in inplace_alter_table() +@retval HA_ALTER_INPLACE_NOCOPY_NO_LOCK +MDL_EXCLUSIVE in prepare_inplace_alter_table(), which can be downgraded to +LOCK=NONE for inplace_alter_table() which will not rebuild the table +@retval HA_ALTER_INPLACE_NOCOPY_LOCK +MDL_EXCLUSIVE in prepare_inplace_alter_table(), which can be downgraded to +LOCK=SHARED for inplace_alter_table() which will not rebuild the table */ enum_alter_inplace_result ha_innobase::check_if_supported_inplace_alter( -/*==========================================*/ TABLE* altered_table, Alter_inplace_info* ha_alter_info) { DBUG_ENTER("check_if_supported_inplace_alter"); - if ((table->versioned(VERS_TIMESTAMP) || altered_table->versioned(VERS_TIMESTAMP)) + if ((table->versioned(VERS_TIMESTAMP) + || altered_table->versioned(VERS_TIMESTAMP)) && innobase_need_rebuild(ha_alter_info, table)) { ha_alter_info->unsupported_reason = "Not implemented for system-versioned tables"; @@ -780,6 +880,7 @@ ha_innobase::check_if_supported_inplace_alter( if (ha_alter_info->handler_flags & ~(INNOBASE_INPLACE_IGNORE + | INNOBASE_ALTER_INSTANT | INNOBASE_ALTER_NOREBUILD | INNOBASE_ALTER_REBUILD)) { @@ -788,6 +889,7 @@ ha_innobase::check_if_supported_inplace_alter( ha_alter_info->unsupported_reason = my_get_err_msg( ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_COLUMN_TYPE); } + DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED); } @@ -809,7 +911,7 @@ ha_innobase::check_if_supported_inplace_alter( #endif if (!(ha_alter_info->handler_flags & ~INNOBASE_INPLACE_IGNORE)) { - DBUG_RETURN(HA_ALTER_INPLACE_NO_LOCK); + DBUG_RETURN(HA_ALTER_INPLACE_INSTANT); } /* Only support NULL -> NOT NULL change if strict table sql_mode @@ -865,8 +967,9 @@ ha_innobase::check_if_supported_inplace_alter( */ for (ulint i = 0, icol= 0; i < table->s->fields; i++) { const Field* field = table->field[i]; - const dict_col_t* col = dict_table_get_nth_col(m_prebuilt->table, icol); - ulint unsigned_flag; + const dict_col_t* col = dict_table_get_nth_col( + m_prebuilt->table, icol); + ulint unsigned_flag; if (!field->stored_in_db()) { continue; @@ -874,7 +977,8 @@ ha_innobase::check_if_supported_inplace_alter( icol++; - if (col->mtype != get_innobase_type_from_mysql_type(&unsigned_flag, field)) { + if (col->mtype != get_innobase_type_from_mysql_type( + &unsigned_flag, field)) { DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED); } @@ -942,7 +1046,7 @@ ha_innobase::check_if_supported_inplace_alter( /* We should be able to do the operation in-place. See if we can do it online (LOCK=NONE). */ - bool online = true; + bool online = true; List_iterator_fast<Create_field> cf_it( ha_alter_info->alter_info->create_list); @@ -964,7 +1068,8 @@ ha_innobase::check_if_supported_inplace_alter( } for (KEY_PART_INFO* key_part = new_key->key_part; - key_part < new_key->key_part + new_key->user_defined_key_parts; + key_part < (new_key->key_part + + new_key->user_defined_key_parts); key_part++) { const Create_field* new_field; @@ -1023,8 +1128,12 @@ ha_innobase::check_if_supported_inplace_alter( column values during online ALTER. */ DBUG_ASSERT(key_part->field == altered_table -> found_next_number_field); - ha_alter_info->unsupported_reason = my_get_err_msg( - ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_AUTOINC); + + if (ha_alter_info->online) { + ha_alter_info->unsupported_reason = my_get_err_msg( + ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_AUTOINC); + } + online = false; } @@ -1040,20 +1149,25 @@ ha_innobase::check_if_supported_inplace_alter( DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED); } - ha_alter_info->unsupported_reason = - MSG_UNSUPPORTED_ALTER_ONLINE_ON_VIRTUAL_COLUMN; + if (ha_alter_info->online + && !ha_alter_info->unsupported_reason) { + ha_alter_info->unsupported_reason = + MSG_UNSUPPORTED_ALTER_ONLINE_ON_VIRTUAL_COLUMN; + } + online = false; } } } - DBUG_ASSERT(!m_prebuilt->table->fts || m_prebuilt->table->fts->doc_col - <= table->s->fields); - DBUG_ASSERT(!m_prebuilt->table->fts || m_prebuilt->table->fts->doc_col - < dict_table_get_n_user_cols(m_prebuilt->table)); + DBUG_ASSERT(!m_prebuilt->table->fts + || (m_prebuilt->table->fts->doc_col <= table->s->fields)); + + DBUG_ASSERT(!m_prebuilt->table->fts + || (m_prebuilt->table->fts->doc_col + < dict_table_get_n_user_cols(m_prebuilt->table))); - if (m_prebuilt->table->fts - && innobase_fulltext_exist(altered_table)) { + if (m_prebuilt->table->fts && innobase_fulltext_exist(altered_table)) { /* FULLTEXT indexes are supposed to remain. */ /* Disallow DROP INDEX FTS_DOC_ID_INDEX */ @@ -1187,16 +1301,17 @@ next_column: == n_stored_cols && m_prebuilt->table->supports_instant() && instant_alter_column_possible(ha_alter_info, table)) { - /* We can perform instant ADD COLUMN, because all - columns are going to be added after existing ones - (and not after hidden InnoDB columns, such as FTS_DOC_ID). */ - /* MDEV-14246 FIXME: return HA_ALTER_INPLACE_NO_LOCK and - perform all work in ha_innobase::commit_inplace_alter_table(), - to avoid an unnecessary MDL upgrade/downgrade cycle. */ - DBUG_RETURN(HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE); + DBUG_RETURN(HA_ALTER_INPLACE_INSTANT); } + if (!(ha_alter_info->handler_flags & ~(INNOBASE_ALTER_INSTANT + | INNOBASE_INPLACE_IGNORE))) { + DBUG_RETURN(HA_ALTER_INPLACE_INSTANT); + } + + bool fts_need_rebuild = false; + if (!online) { /* We already determined that only a non-locking operation is possible. */ @@ -1213,28 +1328,31 @@ next_column: refuse to rebuild the table natively altogether. */ if (m_prebuilt->table->fts) { cannot_create_many_fulltext_index: - ha_alter_info->unsupported_reason = my_get_err_msg( - ER_INNODB_FT_LIMIT); + ha_alter_info->unsupported_reason = + my_get_err_msg(ER_INNODB_FT_LIMIT); DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED); } - if (innobase_spatial_exist(altered_table)) { - ha_alter_info->unsupported_reason = - my_get_err_msg( - ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_GIS); - } else if (!innobase_fulltext_exist(altered_table)) { - /* MDEV-14341 FIXME: Remove this limitation. */ - ha_alter_info->unsupported_reason = - "online rebuild with indexed virtual columns"; - } else { - ha_alter_info->unsupported_reason = - my_get_err_msg( - ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_FTS); + if (ha_alter_info->online + && !ha_alter_info->unsupported_reason) { + + if (innobase_spatial_exist(altered_table)) { + ha_alter_info->unsupported_reason = my_get_err_msg( + ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_GIS); + } else if (!innobase_fulltext_exist(altered_table)) { + /* MDEV-14341 FIXME: Remove this limitation. */ + ha_alter_info->unsupported_reason = + "online rebuild with indexed virtual columns"; + } else { + ha_alter_info->unsupported_reason = my_get_err_msg( + ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_FTS); + } } + } if (ha_alter_info->handler_flags - & ALTER_ADD_NON_UNIQUE_NON_PRIM_INDEX) { + & ALTER_ADD_NON_UNIQUE_NON_PRIM_INDEX) { /* ADD FULLTEXT|SPATIAL INDEX requires a lock. We could do ADD FULLTEXT INDEX without a lock if the @@ -1259,30 +1377,72 @@ cannot_create_many_fulltext_index: if (add_fulltext) { goto cannot_create_many_fulltext_index; } + add_fulltext = true; - ha_alter_info->unsupported_reason = my_get_err_msg( - ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_FTS); + if (ha_alter_info->online + && !ha_alter_info->unsupported_reason) { + ha_alter_info->unsupported_reason = my_get_err_msg( + ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_FTS); + } + online = false; + + /* Full text search index exists, check + whether the table already has DOC ID column. + If not, InnoDB have to rebuild the table to + add a Doc ID hidden column and change + primary index. */ + ulint fts_doc_col_no; + ulint num_v = 0; + + fts_need_rebuild = + !innobase_fts_check_doc_id_col( + m_prebuilt->table, + altered_table, + &fts_doc_col_no, &num_v, true); } + if (online && (key->flags & HA_SPATIAL)) { - ha_alter_info->unsupported_reason = my_get_err_msg( - ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_GIS); + + if (ha_alter_info->online) { + ha_alter_info->unsupported_reason = my_get_err_msg( + ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_GIS); + } + online = false; } } } // FIXME: implement Online DDL for system-versioned tables - if ((table->versioned(VERS_TRX_ID) || altered_table->versioned(VERS_TRX_ID)) + if ((table->versioned(VERS_TRX_ID) + || altered_table->versioned(VERS_TRX_ID)) && innobase_need_rebuild(ha_alter_info, table)) { - ha_alter_info->unsupported_reason = - "Not implemented for system-versioned tables"; + + if (ha_alter_info->online) { + ha_alter_info->unsupported_reason = + "Not implemented for system-versioned tables"; + } + online = false; } + if (fts_need_rebuild || innobase_need_rebuild(ha_alter_info, table)) { + DBUG_RETURN(online + ? HA_ALTER_INPLACE_COPY_NO_LOCK + : HA_ALTER_INPLACE_COPY_LOCK); + } + + if (ha_alter_info->unsupported_reason) { + } else if (ha_alter_info->handler_flags & INNOBASE_ONLINE_CREATE) { + ha_alter_info->unsupported_reason = "ADD INDEX"; + } else { + ha_alter_info->unsupported_reason = "DROP INDEX"; + } + DBUG_RETURN(online - ? HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE - : HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE); + ? HA_ALTER_INPLACE_NOCOPY_NO_LOCK + : HA_ALTER_INPLACE_NOCOPY_LOCK); } /*************************************************************//** @@ -2482,92 +2642,6 @@ innobase_create_index_def( } /*******************************************************************//** -Check whether the table has the FTS_DOC_ID column -@return whether there exists an FTS_DOC_ID column */ -static -bool -innobase_fts_check_doc_id_col( -/*==========================*/ - const dict_table_t* table, /*!< in: InnoDB table with - fulltext index */ - const TABLE* altered_table, - /*!< in: MySQL table with - fulltext index */ - ulint* fts_doc_col_no, - /*!< out: The column number for - Doc ID, or ULINT_UNDEFINED - if it is of wrong type */ - ulint* num_v) /*!< out: number of virtual column */ -{ - *fts_doc_col_no = ULINT_UNDEFINED; - - const uint n_cols = altered_table->s->fields; - ulint i; - - *num_v = 0; - - for (i = 0; i < n_cols; i++) { - const Field* field = altered_table->field[i]; - - if (innobase_is_v_fld(field)) { - (*num_v)++; - } - - if (my_strcasecmp(system_charset_info, - field->field_name.str, FTS_DOC_ID_COL_NAME)) { - continue; - } - - if (strcmp(field->field_name.str, FTS_DOC_ID_COL_NAME)) { - my_error(ER_WRONG_COLUMN_NAME, MYF(0), - field->field_name.str); - } else if (field->type() != MYSQL_TYPE_LONGLONG - || field->pack_length() != 8 - || field->real_maybe_null() - || !(field->flags & UNSIGNED_FLAG) - || innobase_is_v_fld(field)) { - my_error(ER_INNODB_FT_WRONG_DOCID_COLUMN, MYF(0), - field->field_name.str); - } else { - *fts_doc_col_no = i - *num_v; - } - - return(true); - } - - if (!table) { - return(false); - } - - /* Not to count the virtual columns */ - i -= *num_v; - - for (; i + DATA_N_SYS_COLS < (uint) table->n_cols; i++) { - const char* name = dict_table_get_col_name(table, i); - - if (strcmp(name, FTS_DOC_ID_COL_NAME) == 0) { -#ifdef UNIV_DEBUG - const dict_col_t* col; - - col = dict_table_get_nth_col(table, i); - - /* Because the FTS_DOC_ID does not exist in - the MySQL data dictionary, this must be the - internally created FTS_DOC_ID column. */ - ut_ad(col->mtype == DATA_INT); - ut_ad(col->len == 8); - ut_ad(col->prtype & DATA_NOT_NULL); - ut_ad(col->prtype & DATA_UNSIGNED); -#endif /* UNIV_DEBUG */ - *fts_doc_col_no = i; - return(true); - } - } - - return(false); -} - -/*******************************************************************//** Check whether the table has a unique index with FTS_DOC_ID_INDEX_NAME on the Doc ID column. @return the status of the FTS_DOC_ID index */ @@ -6437,7 +6511,8 @@ check_if_ok_to_rename: n_drop_fk = 0; if (ha_alter_info->handler_flags - & (INNOBASE_ALTER_NOREBUILD | INNOBASE_ALTER_REBUILD)) { + & (INNOBASE_ALTER_NOREBUILD | INNOBASE_ALTER_REBUILD + | INNOBASE_ALTER_INSTANT)) { heap = mem_heap_create(1024); if (ha_alter_info->handler_flags @@ -6738,7 +6813,8 @@ err_exit: } if (!(ha_alter_info->handler_flags & INNOBASE_ALTER_DATA) - || ((ha_alter_info->handler_flags & ~INNOBASE_INPLACE_IGNORE) + || ((ha_alter_info->handler_flags & ~(INNOBASE_INPLACE_IGNORE + | INNOBASE_ALTER_INSTANT)) == ALTER_CHANGE_CREATE_OPTION && !create_option_need_rebuild(ha_alter_info, table))) { @@ -6757,7 +6833,7 @@ err_exit: } DBUG_ASSERT(m_prebuilt->trx->dict_operation_lock_mode == 0); - if (ha_alter_info->handler_flags & ~INNOBASE_INPLACE_IGNORE) { + if (ha_alter_info->handler_flags & ~(INNOBASE_INPLACE_IGNORE)) { online_retry_drop_indexes( m_prebuilt->table, m_user_thd); @@ -6801,6 +6877,7 @@ err_exit: HA_ERR_WRONG_INDEX, "InnoDB rebuilding table to add" " column " FTS_DOC_ID_COL_NAME); + } else if (fts_doc_col_no == ULINT_UNDEFINED) { goto err_exit; } diff --git a/storage/innobase/row/row0umod.cc b/storage/innobase/row/row0umod.cc index 3a626e0d010..d3aa6f89811 100644 --- a/storage/innobase/row/row0umod.cc +++ b/storage/innobase/row/row0umod.cc @@ -893,8 +893,8 @@ row_undo_mod_upd_del_sec( } /* During online index creation, - HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE should - guarantee that any active transaction has not modified + HA_ALTER_INPLACE_COPY_NO_LOCK or HA_ALTER_INPLACE_NOCOPY_NO_LOCk + should guarantee that any active transaction has not modified indexed columns such that col->ord_part was 0 at the time when the undo log record was written. When we get to roll back an undo log entry TRX_UNDO_DEL_MARK_REC, @@ -959,8 +959,8 @@ row_undo_mod_del_mark_sec( } /* During online index creation, - HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE should - guarantee that any active transaction has not modified + HA_ALTER_INPLACE_COPY_NO_LOCK or HA_ALTER_INPLACE_NOCOPY_NO_LOCK + should guarantee that any active transaction has not modified indexed columns such that col->ord_part was 0 at the time when the undo log record was written. When we get to roll back an undo log entry TRX_UNDO_DEL_MARK_REC, diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index 0e6ca98162a..73f1cef2268 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -11053,16 +11053,17 @@ my_core::enum_alter_inplace_result ha_rocksdb::check_if_supported_inplace_alter( DBUG_RETURN(my_core::HA_ALTER_INPLACE_NOT_SUPPORTED); } - DBUG_RETURN(my_core::HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE); + /* FIXME: MDEV-16099 Use alter algorithm=nocopy or algorithm=instant + for non-InnoDB engine */ + DBUG_RETURN(my_core::HA_ALTER_INPLACE_COPY_LOCK); } /** Allows the storage engine to update internal structures with concurrent writes blocked. If check_if_supported_inplace_alter() returns - HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE or - HA_ALTER_INPLACE_SHARED_AFTER_PREPARE, this function is called with - exclusive lock otherwise the same level of locking as for - inplace_alter_table() will be used. + HA_ALTER_INPLACE_COPY_NO_LOCK or HA_ALTER_INPLACE_COPY_LOCK. + this function is called with exclusive lock otherwise the same level of + locking as for inplace_alter_table() will be used. @note Storage engines are responsible for reporting any errors by calling my_error()/print_error() diff --git a/storage/tokudb/ha_tokudb_alter_56.cc b/storage/tokudb/ha_tokudb_alter_56.cc index c2d07110ecb..fe6ad1b45f5 100644 --- a/storage/tokudb/ha_tokudb_alter_56.cc +++ b/storage/tokudb/ha_tokudb_alter_56.cc @@ -257,13 +257,13 @@ static bool only_flags(ulong bits, ulong mask) { // HA_ALTER_INPLACE_EXCLUSIVE_LOCK: prepare and alter runs with MDL X -// HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE: prepare runs with MDL X, +// HA_ALTER_INPLACE_COPY_LOCK: prepare runs with MDL X, // alter runs with MDL SNW // HA_ALTER_INPLACE_SHARED_LOCK: prepare and alter methods called with MDL SNW, // concurrent reads, no writes -// HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE: prepare runs with MDL X, +// HA_ALTER_INPLACE_COPY_NO_LOCK: prepare runs with MDL X, // alter runs with MDL SW // HA_ALTER_INPLACE_NO_LOCK: prepare and alter methods called with MDL SW, @@ -319,7 +319,9 @@ enum_alter_inplace_result ha_tokudb::check_if_supported_inplace_alter( // we grab an exclusive MDL for the drop index. result = HA_ALTER_INPLACE_EXCLUSIVE_LOCK; } else { - result = HA_ALTER_INPLACE_SHARED_LOCK_AFTER_PREPARE; + /* FIXME: MDEV-16099 Use alter algorithm=nocopy + or algorithm=instant for non-InnoDB engine */ + result = HA_ALTER_INPLACE_COPY_LOCK; // someday, allow multiple hot indexes via alter table add key. // don't forget to change the store_lock function. @@ -336,7 +338,9 @@ enum_alter_inplace_result ha_tokudb::check_if_supported_inplace_alter( tokudb::sysvars::create_index_online(thd)) { // external_lock set WRITE_ALLOW_WRITE which allows writes // concurrent with the index creation - result = HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE; + /* FIXME: MDEV-16099 Use alter algorithm=nocopy + or algorithm=instant for non-InnoDB engine */ + result = HA_ALTER_INPLACE_COPY_NO_LOCK; } } } @@ -509,7 +513,9 @@ enum_alter_inplace_result ha_tokudb::check_if_supported_inplace_alter( ALTER_RECREATE_TABLE | ALTER_COLUMN_DEFAULT)) { ctx->optimize_needed = true; - result = HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE; + /* FIXME: MDEV-16099 Use alter algorithm=nocopy + or algorithm=instant for non-InnoDB engine */ + result = HA_ALTER_INPLACE_COPY_NO_LOCK; } #endif |