diff options
| author | Galina Shalygina <galina.shalygina@mariadb.com> | 2018-06-01 21:57:10 +0200 |
|---|---|---|
| committer | Galina Shalygina <galina.shalygina@mariadb.com> | 2018-06-01 21:57:10 +0200 |
| commit | 6db465d7ce455cf75ec224108cbe61ca8be63d3d (patch) | |
| tree | 9648ff1fc677eebb60b278c2e2c13131934ed2a0 /mysql-test/suite/innodb | |
| parent | ffe83e8e7bef32eb2a80aad2d382f0b023dd3a44 (diff) | |
| parent | 4a49f7f88cfa82ae6eb8e7b5a528e91416b33b52 (diff) | |
| download | mariadb-git-shagalla-10.4.tar.gz | |
Merge 10.3.7 into 10.4shagalla-10.4
Diffstat (limited to 'mysql-test/suite/innodb')
80 files changed, 2164 insertions, 102 deletions
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_copy.result b/mysql-test/suite/innodb/r/alter_copy.result index 286c5152ded..ac25c6c6c18 100644 --- a/mysql-test/suite/innodb/r/alter_copy.result +++ b/mysql-test/suite/innodb/r/alter_copy.result @@ -73,6 +73,7 @@ FTSBEING_DELETED_CACHE.ibd FTSCONFIG.ibd FTSDELETED.ibd FTSDELETED_CACHE.ibd +db.opt t.frm t.ibd t1.frm @@ -140,6 +141,7 @@ FTSBEING_DELETED_CACHE.ibd FTSCONFIG.ibd FTSDELETED.ibd FTSDELETED_CACHE.ibd +db.opt t.frm t.ibd t1.frm @@ -206,6 +208,7 @@ FTSBEING_DELETED_CACHE.ibd FTSCONFIG.ibd FTSDELETED.ibd FTSDELETED_CACHE.ibd +db.opt t.frm t.ibd t1.frm diff --git a/mysql-test/suite/innodb/r/alter_crash.result b/mysql-test/suite/innodb/r/alter_crash.result index 5bf25cf8592..3c3aaa68b6a 100644 --- a/mysql-test/suite/innodb/r/alter_crash.result +++ b/mysql-test/suite/innodb/r/alter_crash.result @@ -48,6 +48,7 @@ SELECT * FROM information_schema.innodb_sys_tables WHERE table_id = ID; TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE # Files in datadir after manual recovery. +db.opt t1.frm t1.ibd SHOW TABLES; @@ -105,6 +106,7 @@ DROP TABLE t2; CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB; ALTER TABLE t2 ADD PRIMARY KEY (f2, f1); DROP TABLE t2; +db.opt # ------------------------- # End of Testing Scenario 2 # ------------------------- @@ -123,6 +125,7 @@ SELECT * FROM information_schema.innodb_sys_tables WHERE table_id = ID; TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE # Files in datadir after manual recovery. +db.opt t1.frm t1.ibd SHOW TABLES; diff --git a/mysql-test/suite/innodb/r/alter_foreign_crash.result b/mysql-test/suite/innodb/r/alter_foreign_crash.result new file mode 100644 index 00000000000..66ffb5f5411 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_foreign_crash.result @@ -0,0 +1,26 @@ +# +# Bug #20476395 DICT_LOAD_FOREIGNS() FAILED IN +# COMMIT_INPLACE_ALTER_TABLE +# +call mtr.add_suppression("InnoDB: Failed to load table"); +create database bug; +use bug; +create table parent(a serial) engine=innodb; +create table child(a serial, foreign key fk (a) references parent(a))engine=innodb; +insert into parent values(1); +insert into child values(1); +connect con1,localhost,root,,bug; +SET DEBUG_SYNC='innodb_rename_table_ready SIGNAL s1 WAIT_FOR s2 EXECUTE 2'; +ALTER TABLE child ROW_FORMAT=DYNAMIC, ALGORITHM=COPY; +connection default; +SET DEBUG_SYNC='now WAIT_FOR s1'; +SET DEBUG_SYNC='now SIGNAL s2 WAIT_FOR s1'; +disconnect con1; +show tables; +Tables_in_bug +parent +alter table parent row_format=dynamic; +Warnings: +Warning 1088 InnoDB: Could not add foreign key constraints. +drop table parent; +drop database bug; 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/alter_kill.result b/mysql-test/suite/innodb/r/alter_kill.result new file mode 100644 index 00000000000..9b24fddf9ef --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_kill.result @@ -0,0 +1,78 @@ +# +# Bug#16720368 INNODB CRASHES ON BROKEN #SQL*.IBD FILE AT STARTUP +# +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE bug16720368_1 (a INT PRIMARY KEY) ENGINE=InnoDB; +connect con1,localhost,root; +CREATE TABLE bug16720368 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; +INSERT INTO bug16720368 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); +connection default; +# Cleanly shutdown mysqld +disconnect con1; +# Corrupt FIL_PAGE_OFFSET in bug16720368.ibd, +# and update the checksum to the "don't care" value. +# Restart mysqld +# This will succeed after a clean shutdown, due to +# fil_open_single_table_tablespace(check_space_id=FALSE). +SELECT COUNT(*) FROM bug16720368; +COUNT(*) +8 +INSERT INTO bug16720368_1 VALUES(1); +# The table is unaccessible, because after a crash we will +# validate the tablespace header. +SELECT COUNT(*) FROM bug16720368; +ERROR 42S02: Table 'test.bug16720368' doesn't exist in engine +INSERT INTO bug16720368 VALUES(0,1); +ERROR 42S02: Table 'test.bug16720368' doesn't exist in engine +# The table is readable thanks to innodb-force-recovery. +SELECT COUNT(*) FROM bug16720368; +COUNT(*) +8 +INSERT INTO bug16720368 VALUES(0,1); +# Shut down the server cleanly to hide the corruption. +# The table is accessible, because after a clean shutdown we will +# NOT validate the tablespace header. +# We can modify the existing pages, but we cannot allocate or free +# any pages, because that would hit the corruption on page 0. +SELECT COUNT(*) FROM bug16720368; +COUNT(*) +9 +# Shut down the server to uncorrupt the data. +# Restart the server after uncorrupting the file. +INSERT INTO bug16720368 VALUES(9,1); +SELECT COUNT(*) FROM bug16720368; +COUNT(*) +10 +DROP TABLE bug16720368, bug16720368_1; +# +# Bug#16735660 ASSERT TABLE2 == NULL, ROLLBACK OF RESURRECTED TXNS, +# DICT_TABLE_ADD_TO_CACHE +# +SET GLOBAL innodb_file_per_table=1; +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES(42); +connect con1,localhost,root; +CREATE TABLE bug16735660 (a INT PRIMARY KEY) ENGINE=InnoDB; +XA START 'x'; +INSERT INTO bug16735660 VALUES(1),(2),(3); +XA END 'x'; +XA PREPARE 'x'; +connection default; +# Kill the server +disconnect con1; +# Attempt to start without an *.ibd file. +FOUND 1 /\[ERROR\] InnoDB: Tablespace [0-9]+ was not found at .*test.bug16735660.ibd/ in mysqld.1.err +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM bug16735660; +a +1 +2 +3 +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 x +XA ROLLBACK 'x'; +SELECT * FROM bug16735660; +a +DROP TABLE bug16735660; diff --git a/mysql-test/suite/innodb/r/alter_missing_tablespace.result b/mysql-test/suite/innodb/r/alter_missing_tablespace.result index 1517afd1a39..237d0df26ff 100644 --- a/mysql-test/suite/innodb/r/alter_missing_tablespace.result +++ b/mysql-test/suite/innodb/r/alter_missing_tablespace.result @@ -3,8 +3,10 @@ # OR DISCARDED TABLESPACES # SET GLOBAL innodb_file_per_table=1; -CREATE TABLE t(a INT)ENGINE=InnoDB; +CREATE TABLE t(a SERIAL)ENGINE=InnoDB; CREATE TABLE `x..d` (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; +CREATE TABLE t1(a SERIAL)ENGINE=InnoDB; +INSERT INTO t1 VALUES(1),(2),(3); SELECT * FROM t; ERROR 42S02: Table 'test.t' doesn't exist in engine ALTER TABLE t ADD INDEX (a), ALGORITHM=INPLACE; @@ -13,11 +15,16 @@ SHOW WARNINGS; Level Code Message Warning 1812 Tablespace is missing for table 'test/t' Error 1932 Table 'test.t' doesn't exist in engine -ALTER TABLE t1 ADD INDEX (a), ALGORITHM=COPY; -ERROR 42S02: Table 'test.t1' doesn't exist +ALTER TABLE t ADD INDEX (a), ALGORITHM=COPY; +ERROR 42S02: Table 'test.t' doesn't exist in engine SHOW WARNINGS; Level Code Message -Error 1146 Table 'test.t1' doesn't exist +Warning 1812 Tablespace is missing for table 'test/t' +Error 1932 Table 'test.t' doesn't exist in engine +ALTER TABLE t AUTO_INCREMENT=1, ALGORITHM=INPLACE; +ERROR 42S02: Table 'test.t' doesn't exist in engine +ALTER TABLE t AUTO_INCREMENT=1, ALGORITHM=COPY; +ERROR 42S02: Table 'test.t' doesn't exist in engine ALTER TABLE t ALGORITHM=INPLACE, DISCARD TABLESPACE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DISCARD TABLESPACE' at line 1 ALTER TABLE t ALGORITHM=COPY, DISCARD TABLESPACE; @@ -32,3 +39,11 @@ DROP TABLE t; SELECT * FROM `x..d`; ERROR 42S02: Table 'test.x..d' doesn't exist in engine DROP TABLE `x..d`; +ALTER TABLE t1 DISCARD TABLESPACE; +ALTER TABLE t1 AUTO_INCREMENT=1, ALGORITHM=INPLACE; +ERROR HY000: Tablespace has been discarded for table `t1` +ALTER TABLE t1 AUTO_INCREMENT=1, FORCE, ALGORITHM=INPLACE; +ERROR HY000: Tablespace has been discarded for table `t1` +ALTER TABLE t1 AUTO_INCREMENT=1, ALGORITHM=COPY; +ERROR HY000: Tablespace has been discarded for table `t1` +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/alter_not_null.result b/mysql-test/suite/innodb/r/alter_not_null.result new file mode 100644 index 00000000000..8380378593d --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_not_null.result @@ -0,0 +1,88 @@ +set @@sql_mode = 'STRICT_TRANS_TABLES'; +CREATE TABLE t1(f1 INT)ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +f1 +NULL +ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f1 +0 +DROP TABLE t1; +CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +f1 +NULL +ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f1 + +DROP TABLE t1; +CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +f1 +NULL +ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f1 + +DROP TABLE t1; +CREATE TABLE t1(f1 TEXT)ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +f1 +NULL +ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f1 +abc +DROP TABLE t1; +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB; +INSERT INTO t1 VALUES(2, 2, NULL); +SELECT * FROM t1; +f1 f2 f3 +2 2 NULL +ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2), ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY +UPDATE t1 SET f3 = 0; +SELECT * FROM t1; +f1 f2 f3 +2 2 0 +ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2); +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SELECT * FROM t1; +f1 f2 f3 +2 2 0 +DROP TABLE t1; +CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB; +INSERT INTO t1 VALUES(10, NULL); +SELECT * FROM t1; +f1 b +10 NULL +ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0), algorithm=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY +DROP TABLE t1; +CREATE TABLE t1(a INT, v INT AS (a), c INT, d INT NOT NULL, e INT) ENGINE=InnoDB; +ALTER TABLE t1 DROP COLUMN c, CHANGE COLUMN e e INT NOT NULL, ALGORITHM=INPLACE; +DROP TABLE t1; +CREATE TABLE t1 (a INT, v INT AS (a), d INT NOT NULL, e INT) ENGINE=InnoDB; +ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; +DROP TABLE t1; +# +# MDEV-16126 Crash or ASAN heap-buffer-overflow in +# mach_read_from_n_little_endian upon ALTER TABLE with blob +# +CREATE TABLE t1(a INT, v INT AS (a), b INT, c BLOB) ENGINE=InnoDB; +ALTER TABLE t1 ADD PRIMARY KEY(b); +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/alter_not_null_debug.result b/mysql-test/suite/innodb/r/alter_not_null_debug.result new file mode 100644 index 00000000000..788eef6420b --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_not_null_debug.result @@ -0,0 +1,68 @@ +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, NULL); +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; +connect con1,localhost,root; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +INSERT INTO t1 VALUES(2, NULL); +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +ERROR 22004: Invalid use of NULL value +SELECT * FROM t1; +c1 c2 +1 NULL +2 NULL +UPDATE t1 SET c2 = 0 WHERE c1 = 2; +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +# Alter ignore can convert the NULL values from +# CONCURRENT DML to constants +ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +UPDATE t1 SET c2 = NULL WHERE c1 = 2; +INSERT INTO t1 VALUES (3, NULL); +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +SELECT * FROM t1; +c1 c2 +1 2 +2 2 +3 2 +DROP TABLE t1; +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, c3 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, NULL, NULL); +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +# Alter Successfully converts from null to not null +ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +UPDATE t1 SET c2= 2 WHERE c1 = 1; +INSERT INTO t1 VALUES (2, 3, 4); +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +SELECT * FROM t1; +c1 c2 c3 +1 2 NULL +2 3 4 +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +# Alter fails because concurrent dml inserts null value +ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +UPDATE t1 SET c3= 2 WHERE c1 = 2; +INSERT INTO t1 VALUES (4, 3, NULL); +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +ERROR 22004: Invalid use of NULL value +SELECT * FROM t1; +c1 c2 c3 +1 2 NULL +2 3 2 +4 3 NULL +DROP TABLE t1; +disconnect con1; +SET DEBUG_SYNC='RESET'; diff --git a/mysql-test/suite/innodb/r/alter_partitioned.result b/mysql-test/suite/innodb/r/alter_partitioned.result new file mode 100644 index 00000000000..ee8233be5c1 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_partitioned.result @@ -0,0 +1,10 @@ +CREATE TABLE t1(a INT, b VARCHAR(10), INDEX(a))ENGINE=InnoDB +PARTITION BY RANGE(a) +(PARTITION pa VALUES LESS THAN (3), +PARTITION pb VALUES LESS THAN (5)); +CREATE TABLE t2(a INT, FOREIGN KEY(a) REFERENCES t1(a))ENGINE=INNODB +PARTITION BY RANGE(a) +(PARTITION pa VALUES LESS THAN (2), +PARTITION pb VALUES LESS THAN (4)); +ERROR HY000: Foreign key clause is not yet supported in conjunction with partitioning +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/alter_partitioned_debug.result b/mysql-test/suite/innodb/r/alter_partitioned_debug.result new file mode 100644 index 00000000000..d2ec602c6d7 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_partitioned_debug.result @@ -0,0 +1,27 @@ +CREATE TABLE t1 (a INT, b VARCHAR(10)) ENGINE=InnoDB +PARTITION BY RANGE(a) +(PARTITION pa VALUES LESS THAN (3), +PARTITION pb VALUES LESS THAN (5)); +INSERT INTO t1 VALUES(2,'two'),(2,'two'),(4,'four'); +connect ddl,localhost,root,,test; +SET DEBUG_SYNC = 'inplace_after_index_build SIGNAL go WAIT_FOR done'; +ALTER TABLE t1 ADD UNIQUE KEY (a,b(3)); +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR go'; +BEGIN; +SELECT * FROM t1 FOR UPDATE; +a b +2 two +2 two +4 four +SET DEBUG_SYNC = 'now SIGNAL done'; +connection ddl; +ERROR 23000: Duplicate entry '2-two' for key 'a' +connection default; +DELETE FROM t1; +disconnect ddl; +SET DEBUG_SYNC = 'RESET'; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/alter_partitioned_xa.result b/mysql-test/suite/innodb/r/alter_partitioned_xa.result new file mode 100644 index 00000000000..2d3add065b7 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_partitioned_xa.result @@ -0,0 +1,18 @@ +# +# MDEV-14693 XA: Assertion `!clust_index->online_log' failed +# in rollback_inplace_alter_table +# +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB PARTITION BY HASH(a) PARTITIONS 2; +XA START 'xid'; +INSERT INTO t1 VALUES (1,10); +CREATE DATABASE IF NOT EXISTS db; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state +connect con1,localhost,root,,test; +SET innodb_lock_wait_timeout= 1, lock_wait_timeout= 2; +ALTER TABLE t1 FORCE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +disconnect con1; +connection default; +XA END 'xid'; +XA ROLLBACK 'xid'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/alter_rename_files.result b/mysql-test/suite/innodb/r/alter_rename_files.result new file mode 100644 index 00000000000..7df63a051da --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_rename_files.result @@ -0,0 +1,20 @@ +CREATE TABLE t1 (x INT NOT NULL UNIQUE KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(5); +SET GLOBAL innodb_log_checkpoint_now=TRUE; +SET DEBUG_SYNC='commit_cache_rebuild SIGNAL ready WAIT_FOR finish'; +ALTER TABLE t1 ADD PRIMARY KEY(x); +connect con1,localhost,root,,; +SET DEBUG_SYNC='now WAIT_FOR ready'; +SET GLOBAL innodb_log_checkpoint_now=TRUE; +SET DEBUG_SYNC='now SIGNAL finish'; +disconnect con1; +connection default; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) NOT NULL, + PRIMARY KEY (`x`), + UNIQUE KEY `x` (`x`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1; +SET DEBUG_SYNC='RESET'; diff --git a/mysql-test/suite/innodb/r/analyze_table.result b/mysql-test/suite/innodb/r/analyze_table.result new file mode 100644 index 00000000000..a5c25289ad1 --- /dev/null +++ b/mysql-test/suite/innodb/r/analyze_table.result @@ -0,0 +1,25 @@ +CREATE PROCEDURE populate_t1() +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 1000000) DO +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +SET i = i + 1; +END WHILE; +COMMIT; +END| +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB; +SELECT COUNT(*) FROM t1; +COUNT(*) +1000000 +SET GLOBAL innodb_stats_persistent_sample_pages=2000; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +DROP TABLE t1; +DROP PROCEDURE populate_t1; +SET GLOBAL innodb_stats_persistent_sample_pages=default; diff --git a/mysql-test/suite/innodb/r/create_isl_with_direct.result b/mysql-test/suite/innodb/r/create_isl_with_direct.result index 87544e6dcdf..1b830a7d6ce 100644 --- a/mysql-test/suite/innodb/r/create_isl_with_direct.result +++ b/mysql-test/suite/innodb/r/create_isl_with_direct.result @@ -5,6 +5,7 @@ CREATE TABLE t1 (x INT) ENGINE=INNODB, DATA DIRECTORY='MYSQL_TMP_DIR'; # Contents of tmp/test directory containing .ibd file t1.ibd # Contents of the 'test' database directory containing .isl and .frm files +db.opt t1.frm t1.isl DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/dml_purge.result b/mysql-test/suite/innodb/r/dml_purge.result index c6ea5e80f28..95330b80d33 100644 --- a/mysql-test/suite/innodb/r/dml_purge.result +++ b/mysql-test/suite/innodb/r/dml_purge.result @@ -7,13 +7,28 @@ SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; CREATE TABLE t1(a INT PRIMARY KEY, b INT NOT NULL) ROW_FORMAT=REDUNDANT ENGINE=InnoDB; +connect prevent_purge,localhost,root; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; INSERT INTO t1 VALUES(1,2),(3,4); +ALTER TABLE t1 ADD COLUMN c INT; UPDATE t1 SET b=-3 WHERE a=3; +connect con1,localhost,root; +BEGIN; +UPDATE t1 SET b=4 WHERE a=3; +disconnect prevent_purge; +connection default; InnoDB 0 transactions not purged +disconnect con1; FLUSH TABLE t1 FOR EXPORT; Clustered index root page contents: -N_RECS=2; LEVEL=0 -header=0x010000030087 (a=0x696e66696d756d00) +N_RECS=3; LEVEL=0 +header=0x0100000300c6 (a=0x696e66696d756d00) +header=0x1000200b0087 (a=0x80000000, + DB_TRX_ID=0x000000000000, + DB_ROLL_PTR=0x80000000000000, + b=0x80000000, + c=NULL(4 bytes)) header=0x0000100900a6 (a=0x80000001, DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, @@ -22,11 +37,11 @@ header=0x000018090074 (a=0x80000003, DB_TRX_ID=0x000000000000, DB_ROLL_PTR=0x80000000000000, b=0x7ffffffd) -header=0x030008030000 (a=0x73757072656d756d00) +header=0x040008030000 (a=0x73757072656d756d00) UNLOCK TABLES; SELECT * FROM t1; -a b -1 2 -3 -3 +a b c +1 2 NULL +3 -3 NULL DROP TABLE t1; SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result index e569fc7dba7..5838c3a1fd5 100644 --- a/mysql-test/suite/innodb/r/foreign_key.result +++ b/mysql-test/suite/innodb/r/foreign_key.result @@ -306,3 +306,14 @@ id member_id SELECT * FROM payment_method; id member_id cardholder_address_id DROP TABLE payment_method,address,member; +# +# Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY +# PRODUCE BROKEN TABLE (no bug in MariaDB) +# +create table t1(f1 int,f2 int, primary key(f1), key(f2, f1))engine=innodb; +create table t2(f1 int, f2 int as (2) stored, f3 int as (f2) stored, +foreign key(f1) references t1(f2) on update set NULL) +engine=innodb; +insert into t1 values(1, 1); +insert into t2(f1) values(1); +drop table t2, t1; diff --git a/mysql-test/suite/innodb/r/innodb-alter-nullable.result b/mysql-test/suite/innodb/r/innodb-alter-nullable.result index f2c0643f0f1..632f7885b8e 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-nullable.result +++ b/mysql-test/suite/innodb/r/innodb-alter-nullable.result @@ -57,3 +57,7 @@ WHERE NAME='test/t'; TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE # test/t 33 6 # Dynamic 0 Single DROP TABLE t; +CREATE TABLE t1(c1 INT) ENGINE=InnoDB; +ALTER TABLE t1 ADD CONSTRAINT UNIQUE KEY i1(c1); +ALTER TABLE t1 CHANGE c1 c1 INT NOT NULL,ADD KEY(c1); +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result index 9659b03d6b2..b8686d6812e 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result +++ b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result @@ -2,7 +2,7 @@ CREATE TABLE t1 (i1 INT UNSIGNED NULL DEFAULT 42) ENGINE=innodb; INSERT INTO t1 VALUES(NULL); ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(), ALGORITHM=INPLACE; -ERROR 22004: Invalid use of NULL value +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: cannot convert NULL to non-constant DEFAULT. Try ALGORITHM=COPY ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(), ALGORITHM=COPY; ERROR 01000: Data truncated for column 'i1' at row 1 @@ -10,20 +10,20 @@ ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id), ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE; -ERROR 22004: Invalid use of NULL value -ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT, -ADD PRIMARY KEY(id); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 SELECT * FROM t1; id -1 +42 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) -) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 +) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i1 INT UNSIGNED NOT NULL, d1 TIMESTAMP NULL) ENGINE=InnoDB; SHOW CREATE TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result index de69845dddb..afdeac1c22a 100644 --- a/mysql-test/suite/innodb/r/innodb-alter.result +++ b/mysql-test/suite/innodb/r/innodb-alter.result @@ -419,8 +419,6 @@ ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation require ALTER TABLE t1 ADD FULLTEXT INDEX (ct), CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt, ALGORITHM=INPLACE, LOCK=SHARED; -Warnings: -Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; @@ -485,8 +483,6 @@ ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY ALTER TABLE t1o DROP COLUMN FTS_DOC_ID, DROP INDEX ct, ALGORITHM=INPLACE; ALTER TABLE t1o ADD FULLTEXT INDEX(ct), ADD COLUMN cu TEXT; -Warnings: -Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID ALTER TABLE t1o ADD FULLTEXT INDEX(cu), ADD COLUMN FTS_DOC_ID BIGINT, ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY @@ -702,8 +698,6 @@ FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE '%FTS_%' ORDER BY 1, 2; prefix name ALTER TABLE t ADD FULLTEXT INDEX(t); -Warnings: -Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID SELECT sc.pos, sc.NAME FROM information_schema.innodb_sys_columns sc INNER JOIN information_schema.innodb_sys_tables st ON sc.TABLE_ID=st.TABLE_ID @@ -877,6 +871,25 @@ DROP TABLE dest_db.t1; DROP TABLE source_db.t1; DROP DATABASE source_db; DROP DATABASE dest_db; +# +# BUG #26334149 MYSQL CRASHES WHEN FULL TEXT INDEXES IBD FILES ARE +# ORPHANED DUE TO RENAME TABLE +# +CREATE DATABASE db1; +USE db1; +CREATE TABLE notes ( +id int(11) NOT NULL AUTO_INCREMENT, +body text COLLATE utf8_unicode_ci, +PRIMARY KEY (id) +) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 +COLLATE=utf8_unicode_ci +ROW_FORMAT=COMPRESSED; +ALTER TABLE notes ADD FULLTEXT INDEX index_ft_body (body(255)); +DROP INDEX index_ft_body ON notes; +CREATE DATABASE db2; +RENAME TABLE db1.notes TO db2.notes; +DROP DATABASE db1; +DROP DATABASE db2; USE test; # # MDEV-14038 ALTER TABLE does not exit on error with InnoDB + bad default function diff --git a/mysql-test/suite/innodb/r/innodb-isolation.result b/mysql-test/suite/innodb/r/innodb-isolation.result index 2248d25b39a..ce9c530ff44 100644 --- a/mysql-test/suite/innodb/r/innodb-isolation.result +++ b/mysql-test/suite/innodb/r/innodb-isolation.result @@ -1335,11 +1335,17 @@ UPDATE t6 SET b = "updated by client 2"; SELECT * FROM t6; a b aa bb 1 inserted by client 1 1 inserted by client 1 -2 updated by client 2 2 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 3 inserted by client 1 3 inserted by client 1 4 updated by client 2 4 inserted by client 1 5 updated by client 2 NULL NULL 10 updated by client 2 1 inserted by client 1 +SELECT * FROM t6 LOCK IN SHARE MODE; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 updated by client 2 4 inserted by client 1 +5 updated by client 2 NULL NULL +10 updated by client 2 1 inserted by client 1 SELECT COUNT(*) FROM t6; COUNT(*) 6 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..34cc62f0a55 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; @@ -47,3 +47,21 @@ DESCRIBE t1; Field Type Null Key Default Extra a int(11) YES NULL DROP TABLE t1; +# +# Bug #19077964 ASSERT PAGE_SIZE.EQUALS_TO SPACE_PAGE_SIZE +# BTR_COPY_BLOB_PREFIX +# +CREATE TABLE t1(f1 INT PRIMARY KEY, f3 LINESTRING NOT NULL, +SPATIAL KEY(f3))ENGINE=InnoDB ROW_FORMAT=COMPRESSED +KEY_BLOCK_SIZE=1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) NOT NULL, + `f3` linestring NOT NULL, + PRIMARY KEY (`f1`), + SPATIAL KEY `f3` (`f3`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 +INSERT INTO t1 VALUES (1, ST_linefromtext(concat('linestring', '( 0 0, 1 1, 2 2, 3 3, 4 4, 5 5, 6 6, 7 7, 8 8, 9 9, 10 10, 11 11, 12 12, 13 13, 14 14, 15 15, 16 16, 17 17, 18 18, 19 19, 20 20, 21 21, 22 22, 23 23, 24 24, 25 25, 26 26, 27 27, 28 28, 29 29, 30 30, 31 31, 32 32, 33 33, 34 34, 35 35, 36 36, 37 37, 38 38, 39 39, 40 40, 41 41, 42 42, 43 43, 44 44, 45 45, 46 46, 47 47, 48 48, 49 49, 50 50, 51 51, 52 52, 53 53, 54 54, 55 55, 56 56, 57 57, 58 58, 59 59, 60 60, 61 61, 62 62, 63 63, 64 64, 65 65, 66 66, 67 67, 68 68, 69 69, 70 70, 71 71, 72 72, 73 73, 74 74, 75 75, 76 76, 77 77, 78 78, 79 79, 9999 9999)')));; +ALTER TABLE t1 ROW_FORMAT = DYNAMIC, KEY_BLOCK_SIZE=0, ALGORITHM=INPLACE; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-table-online.result b/mysql-test/suite/innodb/r/innodb-table-online.result index 3ac00436e07..d8482ebb23a 100644 --- a/mysql-test/suite/innodb/r/innodb-table-online.result +++ b/mysql-test/suite/innodb/r/innodb-table-online.result @@ -411,9 +411,7 @@ SET @old_sql_mode = @@sql_mode; SET @@sql_mode = 'STRICT_TRANS_TABLES'; ALTER TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)), ALGORITHM = INPLACE; -ERROR 22004: Invalid use of NULL value -ALTER TABLE t1 MODIFY c3 CHAR(255) NOT NULL; -ERROR 22004: Invalid use of NULL value +ERROR 23000: Duplicate entry '' for key 'PRIMARY' SET @@sql_mode = @old_sql_mode; UPDATE t1 SET c3=LEFT(CONCAT(c1,REPEAT('foo',c1)),255) WHERE c3 IS NULL; SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created0 WAIT_FOR ins_done0'; diff --git a/mysql-test/suite/innodb/r/innodb-wl5522.result b/mysql-test/suite/innodb/r/innodb-wl5522.result index 4ef92cdfed9..1ed857af473 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5522.result +++ b/mysql-test/suite/innodb/r/innodb-wl5522.result @@ -31,12 +31,14 @@ a b c 822 Devotion asdfuihknaskdf 821 Cavalry ..asdasdfaeraf CREATE TABLE t2(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT; +db.opt t1.frm t1.ibd t2.frm t2.ibd FLUSH TABLE t1, t2 FOR EXPORT; # List before copying files +db.opt t1.cfg t1.frm t1.ibd @@ -61,12 +63,14 @@ a b c # Restarting server # Done restarting server # List before t1 DISCARD +db.opt t1.frm t1.ibd t2.frm t2.ibd ALTER TABLE t1 DISCARD TABLESPACE; # List after t1 DISCARD +db.opt t1.frm t2.frm t2.ibd @@ -85,6 +89,7 @@ a b c 823 Evolution lsjndofiabsoibeg 822 Devotion asdfuihknaskdf 821 Cavalry ..asdasdfaeraf +db.opt t1.cfg t1.frm t1.ibd @@ -107,6 +112,7 @@ DROP TABLE t1; ALTER TABLE t2 ROW_FORMAT=DYNAMIC; ALTER TABLE t2 DISCARD TABLESPACE; # List after t2 DISCARD +db.opt t2.frm ALTER TABLE t2 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1) @@ -135,6 +141,7 @@ INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; +db.opt t1.frm t1.ibd FLUSH TABLES t1 FOR EXPORT; @@ -142,6 +149,7 @@ SELECT COUNT(*) FROM t1; COUNT(*) 16 backup: t1 +db.opt t1.cfg t1.frm t1.ibd @@ -154,6 +162,7 @@ ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files +db.opt t1.cfg t1.frm t1.ibd @@ -173,6 +182,7 @@ INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; INSERT INTO t1(c2) SELECT c2 FROM t1; +db.opt t1.frm t1.ibd FLUSH TABLES t1 FOR EXPORT; @@ -180,10 +190,12 @@ SELECT COUNT(*) FROM t1; COUNT(*) 16 backup: t1 +db.opt t1.cfg t1.frm t1.ibd UNLOCK TABLES; +db.opt t1.frm t1.ibd INSERT INTO t1(c2) SELECT c2 FROM t1; @@ -196,6 +208,7 @@ ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files +db.opt t1.cfg t1.frm t1.ibd @@ -220,6 +233,7 @@ SELECT COUNT(*) FROM t1 WHERE c2 = 1; COUNT(*) 16 backup: t1 +db.opt t1.cfg t1.frm t1.ibd diff --git a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result index 27866b68303..daa3ffc0a9f 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result +++ b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result @@ -64,6 +64,7 @@ t1 CREATE TABLE `t1` ( KEY `c2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -99,6 +100,7 @@ test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1; ALTER TABLE t1 CHANGE c1 c1 INT FIRST; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -133,6 +135,7 @@ test/t1c2 c2 c2 0 test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE C2 c3 INT; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -167,6 +170,7 @@ test/t1c2 c2 c3 0 test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE c3 C INT; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -201,6 +205,7 @@ test/t1c2 c2 C 0 test/t1c3 c3 c2 0 ALTER TABLE t1 CHANGE C Cöŀumň_TWO INT; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -242,6 +247,7 @@ ALTER TABLE t1 CHANGE cöĿǖmň_two c3 INT; ERROR 42S22: Unknown column 'cöĿǖmň_two' in 't1' ALTER TABLE t1 CHANGE cÖĿUMŇ_two c3 INT, RENAME TO t3; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -289,6 +295,7 @@ ERROR 42000: Identifier name '12345678901234567890123456789012345678901234567890 ALTER TABLE t3 CHANGE c3 `1234567890123456789012345678901234567890123456789012345678901234` INT; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -325,6 +332,7 @@ ALTER TABLE t3 CHANGE `1234567890123456789012345678901234567890123456789012345678901234` `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾ä` INT; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -344,6 +352,7 @@ ALTER TABLE t3 CHANGE `倀倁倂倃倄倅倆倇倈倉倊個倌倍倎倏倐們倒倓倔倕倖倗倘候倚倛倜倝倞借倠倡倢倣値倥倦倧倨倩倪倫倬倭倮倯倰倱倲倳倴倵倶倷倸倹债倻值倽倾Ä` c3 INT; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -365,6 +374,7 @@ ALTER TABLE t3 CHANGE c3 😲 INT; ERROR HY000: Invalid utf8mb4 character string: '\xF0\x9F\x98\xB2' ALTER TABLE t3 RENAME TO t2; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -401,6 +411,7 @@ ON i.TABLE_ID=st.TABLE_ID; NAME NAME test/t1 test/t1 ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -475,6 +486,7 @@ t1c CREATE TABLE `t1c` ( CONSTRAINT `t1c3` FOREIGN KEY (`c3`) REFERENCES `t1p` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -514,6 +526,7 @@ ERROR HY000: Cannot drop index 'c3': needed in a foreign key constraint SET foreign_key_checks=0; ALTER TABLE t1c DROP INDEX C3; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -561,6 +574,7 @@ test/t1c2 c2 c3 0 test/t1c3 c3 c2 0 ALTER TABLE t1c DROP FOREIGN KEY t1C3; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -605,6 +619,7 @@ ID FOR_COL_NAME REF_COL_NAME POS test/t1c2 c2 c3 0 ALTER TABLE t1c DROP INDEX c2, DROP FOREIGN KEY t1C2; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -646,6 +661,7 @@ INNER JOIN sys_foreign sf ON i.ID = sf.ID; ID FOR_COL_NAME REF_COL_NAME POS ALTER TABLE t1 DROP INDEX c2, CHANGE c3 c2 INT; ### files in MYSQL_DATA_DIR/test +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -684,8 +700,6 @@ ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation require ALTER TABLE t1 ADD FULLTEXT INDEX (ct), CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt, ALGORITHM=INPLACE, LOCK=SHARED; -Warnings: -Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID ### files in MYSQL_DATA_DIR/test FTS_AUX_INDEX_1.isl FTS_AUX_INDEX_2.isl @@ -698,6 +712,7 @@ FTS_AUX_BEING_DELETED_CACHE.isl FTS_AUX_CONFIG.isl FTS_AUX_DELETED.isl FTS_AUX_DELETED_CACHE.isl +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -769,8 +784,6 @@ ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY CREATE TABLE t1n LIKE t1o; ALTER TABLE t1n ADD FULLTEXT INDEX(ct); -Warnings: -Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID ### files in MYSQL_DATA_DIR/test FTS_AUX_INDEX_1.isl FTS_AUX_INDEX_2.isl @@ -794,6 +807,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -855,6 +869,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -910,6 +925,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -966,6 +982,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1031,6 +1048,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1099,6 +1117,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1164,6 +1183,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1221,6 +1241,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1276,6 +1297,7 @@ FTS_AUX_BEING_DELETED_CACHE.isl FTS_AUX_CONFIG.isl FTS_AUX_DELETED.isl FTS_AUX_DELETED_CACHE.isl +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1331,6 +1353,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1392,6 +1415,7 @@ FTS_AUX_BEING_DELETED_CACHE.isl FTS_AUX_CONFIG.isl FTS_AUX_DELETED.isl FTS_AUX_DELETED_CACHE.isl +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1463,6 +1487,7 @@ FTS_AUX_BEING_DELETED_CACHE.isl FTS_AUX_CONFIG.isl FTS_AUX_DELETED.isl FTS_AUX_DELETED_CACHE.isl +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1511,6 +1536,7 @@ FTS_AUX_BEING_DELETED_CACHE.ibd FTS_AUX_CONFIG.ibd FTS_AUX_DELETED.ibd FTS_AUX_DELETED_CACHE.ibd +db.opt sys_foreign.frm sys_foreign.ibd sys_indexes.frm @@ -1560,4 +1586,5 @@ ID FOR_COL_NAME REF_COL_NAME POS # DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign; ### files in MYSQL_DATA_DIR/test +db.opt ### files in MYSQL_TMP_DIR/alt_dir/test diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index aafd31ae6f1..86f90c2bd79 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -1701,7 +1701,7 @@ variable_value - @innodb_rows_inserted_orig 964 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; variable_value - @innodb_rows_updated_orig -866 +865 SELECT variable_value - @innodb_row_lock_waits_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_row_lock_waits'; variable_value - @innodb_row_lock_waits_orig 0 diff --git a/mysql-test/suite/innodb/r/innodb_bug27216817.result b/mysql-test/suite/innodb/r/innodb_bug27216817.result new file mode 100644 index 00000000000..0210ced9f91 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug27216817.result @@ -0,0 +1,24 @@ +create table t1 (a int not null, b int not null) engine=innodb; +insert t1 values (1,2),(3,4); +lock table t1 write, t1 tr read; +flush status; +alter table t1 add primary key (b); +show status like 'Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 0 +unlock tables; +alter table t1 drop primary key; +lock table t1 write; +flush status; +alter table t1 add primary key (b); +show status like 'Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 0 +unlock tables; +alter table t1 drop primary key; +flush status; +alter table t1 add primary key (b); +show status like 'Handler_read_rnd_next'; +Variable_name Value +Handler_read_rnd_next 0 +drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb_bug54044.result b/mysql-test/suite/innodb/r/innodb_bug54044.result index 01e8c58f896..29b0127f20b 100644 --- a/mysql-test/suite/innodb/r/innodb_bug54044.result +++ b/mysql-test/suite/innodb/r/innodb_bug54044.result @@ -16,9 +16,3 @@ tmp CREATE TABLE `tmp` ( `NULL` binary(0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE tmp; -CREATE TABLE t1 (a VARCHAR(3)) ENGINE=InnoDB; -INSERT INTO t1 VALUES ('foo'),('bar'); -FLUSH TABLES; -CREATE TEMPORARY TABLE tmp ENGINE=InnoDB AS SELECT VALUE(a) FROM t1; -ERROR HY000: Can't create table `test`.`tmp` (errno: 168 "Unknown (generic) error from engine") -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result index 1580ba29717..a70a3d077e0 100644 --- a/mysql-test/suite/innodb/r/instant_alter.result +++ b/mysql-test/suite/innodb/r/instant_alter.result @@ -440,6 +440,12 @@ SELECT * FROM t1; a b a 1 DROP TABLE t1; +CREATE TABLE t1 (a INT, b VARCHAR(8), PRIMARY KEY(b,a)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (1,'foo'); +ALTER TABLE t1 ADD COLUMN c INT; +UPDATE t1 SET c = 1; +UPDATE t1 SET c = 2; +DROP TABLE t1; CREATE TABLE t1 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -826,6 +832,12 @@ SELECT * FROM t1; a b a 1 DROP TABLE t1; +CREATE TABLE t1 (a INT, b VARCHAR(8), PRIMARY KEY(b,a)) ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 VALUES (1,'foo'); +ALTER TABLE t1 ADD COLUMN c INT; +UPDATE t1 SET c = 1; +UPDATE t1 SET c = 2; +DROP TABLE t1; CREATE TABLE t1 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -1212,10 +1224,16 @@ SELECT * FROM t1; a b a 1 DROP TABLE t1; +CREATE TABLE t1 (a INT, b VARCHAR(8), PRIMARY KEY(b,a)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (1,'foo'); +ALTER TABLE t1 ADD COLUMN c INT; +UPDATE t1 SET c = 1; +UPDATE t1 SET c = 2; +DROP TABLE t1; disconnect analyze; SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants -36 +39 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; diff --git a/mysql-test/suite/innodb/r/instant_alter_crash.result b/mysql-test/suite/innodb/r/instant_alter_crash.result index 2daaf10fa67..d9e57b397f3 100644 --- a/mysql-test/suite/innodb/r/instant_alter_crash.result +++ b/mysql-test/suite/innodb/r/instant_alter_crash.result @@ -101,4 +101,5 @@ t2 CREATE TABLE `t2` ( UNIQUE KEY `c2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT DROP TABLE t1,t2; +db.opt SET GLOBAL innodb_purge_rseg_truncate_frequency=@saved_frequency; diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result index 3aec7553ff0..389a04007e2 100644 --- a/mysql-test/suite/innodb/r/instant_alter_debug.result +++ b/mysql-test/suite/innodb/r/instant_alter_debug.result @@ -32,18 +32,11 @@ pk f CREATE TABLE t4 (pk INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t4 VALUES (0); ALTER TABLE t4 ADD COLUMN b INT; -SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS +SELECT COUNT(*)>0 FROM INFORMATION_SCHEMA.COLUMNS LEFT JOIN t4 ON (NUMERIC_SCALE = pk); -COUNT(*) -1748 -SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL enter WAIT_FOR delete'; +COUNT(*)>0 +1 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; @@ -164,4 +157,28 @@ INSERT INTO t11 () VALUES (); UPDATE t11 SET c22 = 1; InnoDB 0 transactions not purged DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; +# +# MDEV-15060 Assertion in row_log_table_apply_op after instant ADD +# when the table is emptied during subsequent ALTER TABLE +# +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +ALTER TABLE t1 ADD COLUMN b INT NOT NULL; +connect stop_purge,localhost,root; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connect ddl,localhost,root,,test; +DELETE FROM t1; +SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged'; +ALTER TABLE t1 FORCE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR copied'; +BEGIN; +INSERT INTO t1 SET b=1; +ROLLBACK; +disconnect stop_purge; +InnoDB 2 transactions not purged +SET DEBUG_SYNC='now SIGNAL logged'; +disconnect ddl; +DROP TABLE t1; +SET DEBUG_SYNC='RESET'; SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; diff --git a/mysql-test/suite/innodb/r/log_file_name.result b/mysql-test/suite/innodb/r/log_file_name.result index 0d764f65617..99eba0e33e7 100644 --- a/mysql-test/suite/innodb/r/log_file_name.result +++ b/mysql-test/suite/innodb/r/log_file_name.result @@ -98,5 +98,6 @@ FOUND 1 /InnoDB: At LSN: \d+: unable to open file .*u[1-5].ibd for tablespace/ i FOUND 1 /\[Warning\] InnoDB: Tablespace \d+ was not found at .*u[1-5].ibd, and innodb_force_recovery was set. All redo log for this tablespace will be ignored!/ in mysqld.1.err DROP TABLE u1,u2,u3,u6; # List of files: +db.opt SHOW TABLES; Tables_in_test diff --git a/mysql-test/suite/innodb/r/rename_table_debug.result b/mysql-test/suite/innodb/r/rename_table_debug.result index 976b609bdd5..646bd4faf5d 100644 --- a/mysql-test/suite/innodb/r/rename_table_debug.result +++ b/mysql-test/suite/innodb/r/rename_table_debug.result @@ -1,3 +1,4 @@ +FLUSH TABLES; CREATE TABLE t1 (a SERIAL, b INT, c INT, d INT) ENGINE=InnoDB; INSERT INTO t1 () VALUES (); connect con1,localhost,root,,test; diff --git a/mysql-test/suite/innodb/r/row_format_redundant.result b/mysql-test/suite/innodb/r/row_format_redundant.result index 6deecd10ada..63172897927 100644 --- a/mysql-test/suite/innodb/r/row_format_redundant.result +++ b/mysql-test/suite/innodb/r/row_format_redundant.result @@ -77,3 +77,4 @@ ib_buffer_pool ib_logfile0 ib_logfile1 ibdata1 +db.opt diff --git a/mysql-test/suite/innodb/r/stored_fk.result b/mysql-test/suite/innodb/r/stored_fk.result new file mode 100644 index 00000000000..35524d5a88f --- /dev/null +++ b/mysql-test/suite/innodb/r/stored_fk.result @@ -0,0 +1,74 @@ +# Create statement with FK on base column of stored column +create table t1(f1 int, f2 int as(f1) stored, +foreign key(f1) references t2(f1) on delete cascade)engine=innodb; +ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +# adding new stored column during alter table copy operation. +create table t1(f1 int primary key) engine=innodb; +create table t2(f1 int not null, f2 int as (f1) virtual, +foreign key(f1) references t1(f1) on update cascade)engine=innodb; +alter table t2 add column f3 int as (f1) stored, add column f4 int as (f1) virtual; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f1` int(11) NOT NULL, + `f2` int(11) GENERATED ALWAYS AS (`f1`) VIRTUAL, + `f3` int(11) GENERATED ALWAYS AS (`f1`) STORED, + `f4` int(11) GENERATED ALWAYS AS (`f1`) VIRTUAL, + KEY `f1` (`f1`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t2; +# adding foreign key constraint for base columns during alter copy. +create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb; +alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=copy; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f1` int(11) NOT NULL, + `f2` int(11) GENERATED ALWAYS AS (`f1`) STORED, + KEY `f1` (`f1`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t2; +# adding foreign key constraint for base columns during online alter. +create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb; +set foreign_key_checks = 0; +alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=inplace; +ERROR 0A000: Cannot add foreign key on the base column of stored column +drop table t2; +# adding stored column via online alter. +create table t2(f1 int not null, +foreign key(f1) references t1(f1) on update cascade)engine=innodb; +alter table t2 add column f2 int as (f1) stored, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY +drop table t2, t1; +# +# BUG#26731689 FK ON TABLE WITH GENERATED COLS: ASSERTION POS < N_DEF +# +CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED, c INT, +d INT GENERATED ALWAYS AS (0) VIRTUAL, e INT) ENGINE=innodb; +CREATE TABLE t (a INT) ENGINE=innodb; +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null; +ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'c' in the referenced table 't' +ALTER TABLE t ADD PRIMARY KEY(a); +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null; +DROP TABLE s,t; +CREATE TABLE s (a INT GENERATED ALWAYS AS (0) VIRTUAL, +b INT GENERATED ALWAYS AS (0) STORED, c INT) ENGINE=innodb; +CREATE TABLE t (a INT) ENGINE=innodb; +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null; +ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'c' in the referenced table 't' +ALTER TABLE t ADD PRIMARY KEY(a); +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null; +DROP TABLE s,t; +CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED) ENGINE=innodb; +CREATE TABLE t (a INT PRIMARY KEY) ENGINE=innodb; +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; +DROP TABLE s,t; +CREATE TABLE s (a INT, b INT) ENGINE=innodb; +CREATE TABLE t (a INT) ENGINE=innodb; +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; +ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'c' in the referenced table 't' +ALTER TABLE t ADD PRIMARY KEY(a); +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; +DROP TABLE s,t; diff --git a/mysql-test/suite/innodb/r/temporary_table.result b/mysql-test/suite/innodb/r/temporary_table.result index 310741b5798..533d32089c3 100644 --- a/mysql-test/suite/innodb/r/temporary_table.result +++ b/mysql-test/suite/innodb/r/temporary_table.result @@ -638,3 +638,23 @@ t1 CREATE TEMPORARY TABLE `t1` ( `j` int(11) DEFAULT NULL, PRIMARY KEY (`i`) KEY_BLOCK_SIZE=8 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC +DROP TABLE t1; +CREATE TEMPORARY TABLE t1(f1 INT, KEY(f1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +UPDATE t1 SET f1 = 0; +START TRANSACTION; +UPDATE t1 SET f1 = 4; +UPDATE t1 SET f1 = 0; +ROLLBACK; +SELECT * FROM t1; +f1 +0 +DROP TABLE t1; +# +# MDEV-15874 CREATE TABLE creates extra transaction +# +call mtr.add_suppression("Warning 150 Create table `mysqld.1`.`t1` with foreign key constraint failed. Temporary tables can't have foreign key constraints.*"); +SET FOREIGN_KEY_CHECKS = 0; +CREATE TEMPORARY TABLE t1(f1 INT NOT NULL, +FOREIGN KEY(f1) REFERENCES t0(f1))ENGINE=InnoDB; +ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") diff --git a/mysql-test/suite/innodb/r/undo_log.result b/mysql-test/suite/innodb/r/undo_log.result index a40c6b5b3bf..6fe0da3da47 100644 --- a/mysql-test/suite/innodb/r/undo_log.result +++ b/mysql-test/suite/innodb/r/undo_log.result @@ -140,3 +140,16 @@ CHECK TABLE test_tab; Table Op Msg_type Msg_text test.test_tab check status OK DROP TABLE test_tab; +SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; +SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; +CREATE TEMPORARY TABLE t2(i INT)ENGINE=InnoDB; +CREATE TABLE t1(i TEXT NOT NULL) ENGINE=INNODB; +BEGIN; +INSERT t1 SET i=REPEAT('1234567890',840); +UPDATE t1 SET i=''; +INSERT INTO t2 VALUES(2); +ROLLBACK; +InnoDB 0 transactions not purged +DROP TABLE t1; +DROP TABLE t2; +SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; 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_foreign_crash.test b/mysql-test/suite/innodb/t/alter_foreign_crash.test new file mode 100644 index 00000000000..1952a1b30d4 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_foreign_crash.test @@ -0,0 +1,37 @@ +--source include/have_innodb.inc +--source include/have_debug_sync.inc +# The embedded server does not support restarting. +--source include/not_embedded.inc + +--echo # +--echo # Bug #20476395 DICT_LOAD_FOREIGNS() FAILED IN +--echo # COMMIT_INPLACE_ALTER_TABLE +--echo # + +call mtr.add_suppression("InnoDB: Failed to load table"); + +create database bug; +use bug; + +create table parent(a serial) engine=innodb; +create table child(a serial, foreign key fk (a) references parent(a))engine=innodb; + +insert into parent values(1); +insert into child values(1); + +connect (con1,localhost,root,,bug); +SET DEBUG_SYNC='innodb_rename_table_ready SIGNAL s1 WAIT_FOR s2 EXECUTE 2'; +--send ALTER TABLE child ROW_FORMAT=DYNAMIC, ALGORITHM=COPY +connection default; +SET DEBUG_SYNC='now WAIT_FOR s1'; +SET DEBUG_SYNC='now SIGNAL s2 WAIT_FOR s1'; + +--let $shutdown_timeout= 0 +--source include/restart_mysqld.inc +disconnect con1; + +show tables; +alter table parent row_format=dynamic; + +drop table parent; +drop database bug; 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/alter_kill-master.opt b/mysql-test/suite/innodb/t/alter_kill-master.opt new file mode 100644 index 00000000000..e472160c2b7 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_kill-master.opt @@ -0,0 +1 @@ +--innodb-doublewrite=false diff --git a/mysql-test/suite/innodb/t/alter_kill.test b/mysql-test/suite/innodb/t/alter_kill.test new file mode 100644 index 00000000000..922378d2919 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_kill.test @@ -0,0 +1,158 @@ +--source include/have_innodb.inc +# The embedded server does not support restarting in mysql-test-run. +-- source include/not_embedded.inc +-- source include/no_valgrind_without_big.inc + +let MYSQLD_DATADIR=`select @@datadir`; +let PAGE_SIZE=`select @@innodb_page_size`; + +-- disable_query_log +call mtr.add_suppression("InnoDB: innodb_force_recovery is on."); +call mtr.add_suppression("InnoDB: Header page contains inconsistent data in .*bug16720368.ibd"); +call mtr.add_suppression("InnoDB: Checksum mismatch in datafile:.*bug16720368"); +call mtr.add_suppression("InnoDB: Ignoring tablespace for.*bug16720368"); +call mtr.add_suppression("Found 1 prepared XA transactions"); +call mtr.add_suppression("InnoDB: Operating system error.*in a file operation"); +call mtr.add_suppression("InnoDB: \(The error means\|If you are\)"); +call mtr.add_suppression("InnoDB: Ignoring tablespace `test/bug16720368` because it could not be opened"); +call mtr.add_suppression("InnoDB: Tablespace .* was not found at.*bug16735660"); +call mtr.add_suppression("InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace."); +call mtr.add_suppression("InnoDB: Plugin initialization aborted*"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error."); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed."); +-- enable_query_log + +-- echo # +-- echo # Bug#16720368 INNODB CRASHES ON BROKEN #SQL*.IBD FILE AT STARTUP +-- echo # + +SET GLOBAL innodb_file_per_table=1; + +CREATE TABLE bug16720368_1 (a INT PRIMARY KEY) ENGINE=InnoDB; + +connect (con1,localhost,root); +CREATE TABLE bug16720368 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; +INSERT INTO bug16720368 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); + +connection default; + +-- echo # Cleanly shutdown mysqld +-- source include/shutdown_mysqld.inc + +disconnect con1; + +-- echo # Corrupt FIL_PAGE_OFFSET in bug16720368.ibd, +-- echo # and update the checksum to the "don't care" value. +perl; +my $file = "$ENV{MYSQLD_DATADIR}/test/bug16720368.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +print FILE pack("H*","deadbeefc001cafe") || die "Unable to write $file"; +seek(FILE, $ENV{PAGE_SIZE}-8, 0) || die "Unable to seek $file"; +print FILE pack("H*","deadbeef") || die "Unable to write $file"; +close(FILE) || die "Unable to close $file"; +EOF + +-- echo # Restart mysqld +-- source include/start_mysqld.inc + +-- echo # This will succeed after a clean shutdown, due to +-- echo # fil_open_single_table_tablespace(check_space_id=FALSE). +SELECT COUNT(*) FROM bug16720368; + +INSERT INTO bug16720368_1 VALUES(1); + +--let $shutdown_timeout= 0 +--source include/restart_mysqld.inc + +-- echo # The table is unaccessible, because after a crash we will +-- echo # validate the tablespace header. +--error ER_NO_SUCH_TABLE_IN_ENGINE +SELECT COUNT(*) FROM bug16720368; +--error ER_NO_SUCH_TABLE_IN_ENGINE +INSERT INTO bug16720368 VALUES(0,1); + +let $restart_parameters = --innodb-force-recovery=3; +--let $shutdown_timeout= 0 +--source include/restart_mysqld.inc + +-- echo # The table is readable thanks to innodb-force-recovery. +SELECT COUNT(*) FROM bug16720368; +INSERT INTO bug16720368 VALUES(0,1); + +-- echo # Shut down the server cleanly to hide the corruption. +let $shutdown_timeout=; +let $restart_parameters =; +-- source include/restart_mysqld.inc + +-- echo # The table is accessible, because after a clean shutdown we will +-- echo # NOT validate the tablespace header. +-- echo # We can modify the existing pages, but we cannot allocate or free +-- echo # any pages, because that would hit the corruption on page 0. +SELECT COUNT(*) FROM bug16720368; + +-- echo # Shut down the server to uncorrupt the data. +-- source include/shutdown_mysqld.inc + +# Uncorrupt the FIL_PAGE_OFFSET. +perl; +my $file = "$ENV{MYSQLD_DATADIR}/test/bug16720368.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +# Uncorrupt FIL_PAGE_OFFSET. +print FILE pack("H*","deadbeef00000000") || die "Unable to write $file"; +close(FILE) || die "Unable to close $file"; +EOF + +-- echo # Restart the server after uncorrupting the file. +-- source include/start_mysqld.inc + +INSERT INTO bug16720368 VALUES(9,1); +SELECT COUNT(*) FROM bug16720368; +# A debug assertion would fail in buf_block_align_instance() +# if we did not uncorrupt the page number first. +DROP TABLE bug16720368, bug16720368_1; + +-- echo # +-- echo # Bug#16735660 ASSERT TABLE2 == NULL, ROLLBACK OF RESURRECTED TXNS, +-- echo # DICT_TABLE_ADD_TO_CACHE +-- echo # + +SET GLOBAL innodb_file_per_table=1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES(42); + +-- connect (con1,localhost,root) + +CREATE TABLE bug16735660 (a INT PRIMARY KEY) ENGINE=InnoDB; + +XA START 'x'; +INSERT INTO bug16735660 VALUES(1),(2),(3); +XA END 'x'; +XA PREPARE 'x'; + +-- connection default + +-- source include/kill_mysqld.inc +-- disconnect con1 +-- move_file $MYSQLD_DATADIR/test/bug16735660.ibd $MYSQLD_DATADIR/bug16735660.omg + +-- echo # Attempt to start without an *.ibd file. +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err; +--source include/start_mysqld.inc + +let SEARCH_PATTERN= \[ERROR\] InnoDB: Tablespace [0-9]+ was not found at .*test.bug16735660.ibd; +-- source include/search_pattern_in_file.inc + +-- move_file $MYSQLD_DATADIR/bug16735660.omg $MYSQLD_DATADIR/test/bug16735660.ibd + +-- source include/restart_mysqld.inc + +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM bug16735660; + +XA RECOVER; +XA ROLLBACK 'x'; + +SELECT * FROM bug16735660; +DROP TABLE bug16735660; diff --git a/mysql-test/suite/innodb/t/alter_missing_tablespace.test b/mysql-test/suite/innodb/t/alter_missing_tablespace.test index 922013cfe43..bdcbdfb4408 100644 --- a/mysql-test/suite/innodb/t/alter_missing_tablespace.test +++ b/mysql-test/suite/innodb/t/alter_missing_tablespace.test @@ -22,8 +22,10 @@ call mtr.add_suppression("InnoDB: ALTER TABLE `test`.`t` DISCARD TABLESPACE fail let $MYSQLD_DATADIR=`select @@datadir`; SET GLOBAL innodb_file_per_table=1; -CREATE TABLE t(a INT)ENGINE=InnoDB; +CREATE TABLE t(a SERIAL)ENGINE=InnoDB; CREATE TABLE `x..d` (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; +CREATE TABLE t1(a SERIAL)ENGINE=InnoDB; +INSERT INTO t1 VALUES(1),(2),(3); --source include/shutdown_mysqld.inc @@ -42,10 +44,15 @@ SELECT * FROM t; ALTER TABLE t ADD INDEX (a), ALGORITHM=INPLACE; SHOW WARNINGS; ---error ER_NO_SUCH_TABLE -ALTER TABLE t1 ADD INDEX (a), ALGORITHM=COPY; +--error ER_NO_SUCH_TABLE_IN_ENGINE +ALTER TABLE t ADD INDEX (a), ALGORITHM=COPY; SHOW WARNINGS; +--error ER_NO_SUCH_TABLE_IN_ENGINE +ALTER TABLE t AUTO_INCREMENT=1, ALGORITHM=INPLACE; +--error ER_NO_SUCH_TABLE_IN_ENGINE +ALTER TABLE t AUTO_INCREMENT=1, ALGORITHM=COPY; + --error ER_PARSE_ERROR ALTER TABLE t ALGORITHM=INPLACE, DISCARD TABLESPACE; --error ER_PARSE_ERROR @@ -57,3 +64,12 @@ DROP TABLE t; --error ER_NO_SUCH_TABLE_IN_ENGINE SELECT * FROM `x..d`; DROP TABLE `x..d`; + +ALTER TABLE t1 DISCARD TABLESPACE; +--error ER_TABLESPACE_DISCARDED +ALTER TABLE t1 AUTO_INCREMENT=1, ALGORITHM=INPLACE; +--error ER_TABLESPACE_DISCARDED +ALTER TABLE t1 AUTO_INCREMENT=1, FORCE, ALGORITHM=INPLACE; +--error ER_TABLESPACE_DISCARDED +ALTER TABLE t1 AUTO_INCREMENT=1, ALGORITHM=COPY; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_not_null.test b/mysql-test/suite/innodb/t/alter_not_null.test new file mode 100644 index 00000000000..f4606dfa6c5 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_not_null.test @@ -0,0 +1,75 @@ +--source include/have_innodb.inc +set @@sql_mode = 'STRICT_TRANS_TABLES'; + +CREATE TABLE t1(f1 INT)ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +--enable_info +ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +--enable_info +ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +--enable_info +ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 TEXT)ENGINE=INNODB; +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +--enable_info +ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc'; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB; +INSERT INTO t1 VALUES(2, 2, NULL); +SELECT * FROM t1; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2), ALGORITHM=INPLACE; +UPDATE t1 SET f3 = 0; +SELECT * FROM t1; +--enable_info +ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2); +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB; +INSERT INTO t1 VALUES(10, NULL); +SELECT * FROM t1; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0), algorithm=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1(a INT, v INT AS (a), c INT, d INT NOT NULL, e INT) ENGINE=InnoDB; +ALTER TABLE t1 DROP COLUMN c, CHANGE COLUMN e e INT NOT NULL, ALGORITHM=INPLACE; +DROP TABLE t1; + +CREATE TABLE t1 (a INT, v INT AS (a), d INT NOT NULL, e INT) ENGINE=InnoDB; +ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; +DROP TABLE t1; + +--echo # +--echo # MDEV-16126 Crash or ASAN heap-buffer-overflow in +--echo # mach_read_from_n_little_endian upon ALTER TABLE with blob +--echo # + +CREATE TABLE t1(a INT, v INT AS (a), b INT, c BLOB) ENGINE=InnoDB; +ALTER TABLE t1 ADD PRIMARY KEY(b); +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_not_null_debug.test b/mysql-test/suite/innodb/t/alter_not_null_debug.test new file mode 100644 index 00000000000..9c1500dc829 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_not_null_debug.test @@ -0,0 +1,68 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, NULL); +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +send ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; +connect (con1,localhost,root); +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +INSERT INTO t1 VALUES(2, NULL); +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +--error ER_INVALID_USE_OF_NULL +reap; +SELECT * FROM t1; +UPDATE t1 SET c2 = 0 WHERE c1 = 2; +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +--echo # Alter ignore can convert the NULL values from +--echo # CONCURRENT DML to constants +send ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +UPDATE t1 SET c2 = NULL WHERE c1 = 2; +INSERT INTO t1 VALUES (3, NULL); +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +reap; +SELECT * FROM t1; +DROP TABLE t1; + + +CREATE TABLE t1(c1 INT NOT NULL, c2 INT, c3 INT, PRIMARY KEY(c1))ENGINE=INNODB; +INSERT INTO t1 VALUES(1, NULL, NULL); +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +--echo # Alter Successfully converts from null to not null + +send ALTER TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; + +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +UPDATE t1 SET c2= 2 WHERE c1 = 1; +INSERT INTO t1 VALUES (2, 3, 4); +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +reap; +SELECT * FROM t1; + +SET DEBUG_SYNC= 'row_merge_after_scan +SIGNAL opened WAIT_FOR flushed'; +--echo # Alter fails because concurrent dml inserts null value + +send ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL DEFAULT 2, ALGORITHM=INPLACE; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +UPDATE t1 SET c3= 2 WHERE c1 = 2; +INSERT INTO t1 VALUES (4, 3, NULL); +SET DEBUG_SYNC= 'now SIGNAL flushed'; +connection default; +--error ER_INVALID_USE_OF_NULL +reap; +SELECT * FROM t1; +DROP TABLE t1; +disconnect con1; +SET DEBUG_SYNC='RESET'; diff --git a/mysql-test/suite/innodb/t/alter_partitioned.test b/mysql-test/suite/innodb/t/alter_partitioned.test new file mode 100644 index 00000000000..9bf6c8f047a --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_partitioned.test @@ -0,0 +1,15 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +CREATE TABLE t1(a INT, b VARCHAR(10), INDEX(a))ENGINE=InnoDB +PARTITION BY RANGE(a) +(PARTITION pa VALUES LESS THAN (3), +PARTITION pb VALUES LESS THAN (5)); + +--error ER_FOREIGN_KEY_ON_PARTITIONED +CREATE TABLE t2(a INT, FOREIGN KEY(a) REFERENCES t1(a))ENGINE=INNODB +PARTITION BY RANGE(a) +(PARTITION pa VALUES LESS THAN (2), +PARTITION pb VALUES LESS THAN (4)); + +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_partitioned_debug.test b/mysql-test/suite/innodb/t/alter_partitioned_debug.test new file mode 100644 index 00000000000..34565e12036 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_partitioned_debug.test @@ -0,0 +1,34 @@ +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +CREATE TABLE t1 (a INT, b VARCHAR(10)) ENGINE=InnoDB +PARTITION BY RANGE(a) +(PARTITION pa VALUES LESS THAN (3), +PARTITION pb VALUES LESS THAN (5)); + +INSERT INTO t1 VALUES(2,'two'),(2,'two'),(4,'four'); + +connect ddl,localhost,root,,test; +SET DEBUG_SYNC = 'inplace_after_index_build SIGNAL go WAIT_FOR done'; +send ALTER TABLE t1 ADD UNIQUE KEY (a,b(3)); + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR go'; +BEGIN; +SELECT * FROM t1 FOR UPDATE; +SET DEBUG_SYNC = 'now SIGNAL done'; + +connection ddl; +--error ER_DUP_ENTRY +reap; + +connection default; +DELETE FROM t1; +disconnect ddl; + +SET DEBUG_SYNC = 'RESET'; + +CHECK TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_partitioned_xa.test b/mysql-test/suite/innodb/t/alter_partitioned_xa.test new file mode 100644 index 00000000000..f0883802cd6 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_partitioned_xa.test @@ -0,0 +1,31 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +--echo # +--echo # MDEV-14693 XA: Assertion `!clust_index->online_log' failed +--echo # in rollback_inplace_alter_table +--echo # + +# A bug in meta-data locking (MDL) for XA transactions causes +# a bug in InnoDB error handling for ALTER TABLE to be triggered. +CREATE TABLE t1 (a INT, b INT) ENGINE=InnoDB PARTITION BY HASH(a) PARTITIONS 2; +XA START 'xid'; +INSERT INTO t1 VALUES (1,10); +# XA bug: The following releases the MDL on t1! +--error ER_XAER_RMFAIL +CREATE DATABASE IF NOT EXISTS db; + +--connect (con1,localhost,root,,test) +SET innodb_lock_wait_timeout= 1, lock_wait_timeout= 2; +# Here, innodb_lock_wait_timeout would be exceeded, causing the operation +# to roll back when InnoDB is attempting to commit. +# (Instead, lock_wait_timeout should be exceeded!) +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 FORCE; + +# Cleanup +--disconnect con1 +--connection default +XA END 'xid'; +XA ROLLBACK 'xid'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_rename_files.test b/mysql-test/suite/innodb/t/alter_rename_files.test new file mode 100644 index 00000000000..3ed1cb5d9fa --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_rename_files.test @@ -0,0 +1,31 @@ +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_innodb.inc +--source include/count_sessions.inc + +CREATE TABLE t1 (x INT NOT NULL UNIQUE KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(5); + +SET GLOBAL innodb_log_checkpoint_now=TRUE; + +# Start an ALTER TABLE and stop it before renaming the files +SET DEBUG_SYNC='commit_cache_rebuild SIGNAL ready WAIT_FOR finish'; + +--send ALTER TABLE t1 ADD PRIMARY KEY(x) + +connect (con1,localhost,root,,); + +SET DEBUG_SYNC='now WAIT_FOR ready'; + +SET GLOBAL innodb_log_checkpoint_now=TRUE; + +SET DEBUG_SYNC='now SIGNAL finish'; + +disconnect con1; +connection default; +reap; +SHOW CREATE TABLE t1; +DROP TABLE t1; +SET DEBUG_SYNC='RESET'; + +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb/t/analyze_table.test b/mysql-test/suite/innodb/t/analyze_table.test new file mode 100644 index 00000000000..e9db3668f02 --- /dev/null +++ b/mysql-test/suite/innodb/t/analyze_table.test @@ -0,0 +1,42 @@ +# +# BUG#22385442 - INNODB: DIFFICULT TO FIND FREE BLOCKS IN THE BUFFER POOL +# + +--source include/have_innodb.inc +--source include/big_test.inc + +DELIMITER |; +CREATE PROCEDURE populate_t1() +BEGIN + DECLARE i int DEFAULT 1; + + START TRANSACTION; + WHILE (i <= 1000000) DO + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + SET i = i + 1; + END WHILE; + COMMIT; +END| +DELIMITER ;| + +CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) +) ENGINE=InnoDB; + +-- disable_query_log +CALL populate_t1(); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +SET GLOBAL innodb_stats_persistent_sample_pages=2000; + +ANALYZE TABLE t1; + +DROP TABLE t1; + +DROP PROCEDURE populate_t1; + +SET GLOBAL innodb_stats_persistent_sample_pages=default; diff --git a/mysql-test/suite/innodb/t/dml_purge.test b/mysql-test/suite/innodb/t/dml_purge.test index 93b7f56111c..37178982c8d 100644 --- a/mysql-test/suite/innodb/t/dml_purge.test +++ b/mysql-test/suite/innodb/t/dml_purge.test @@ -15,11 +15,25 @@ SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; CREATE TABLE t1(a INT PRIMARY KEY, b INT NOT NULL) ROW_FORMAT=REDUNDANT ENGINE=InnoDB; +--connect (prevent_purge,localhost,root) +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +--connection default INSERT INTO t1 VALUES(1,2),(3,4); +ALTER TABLE t1 ADD COLUMN c INT; UPDATE t1 SET b=-3 WHERE a=3; -# Initiate a full purge, which should reset all DB_TRX_ID. +--connect (con1,localhost,root) +BEGIN; +# For purgeable records, we must record DB_TRX_ID=0 in the undo log! +UPDATE t1 SET b=4 WHERE a=3; +--disconnect prevent_purge + +--connection default +# Initiate a full purge, which should reset the DB_TRX_ID except for a=3. --source include/wait_all_purged.inc +# Initiate a ROLLBACK of the update, which should reset the DB_TRX_ID for a=3. +--disconnect con1 FLUSH TABLE t1 FOR EXPORT; # The following is based on innodb.table_flags: @@ -34,7 +48,7 @@ sysseek(FILE, 3*$ps, 0) || die "Unable to seek $file"; die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; print "N_RECS=", unpack("n", substr($page,38+16,2)); print "; LEVEL=", unpack("n", substr($page,38+26,2)), "\n"; -my @fields=("a","DB_TRX_ID","DB_ROLL_PTR", "b"); +my @fields=qw(a DB_TRX_ID DB_ROLL_PTR b c); for (my $offset= 0x65; $offset; $offset= unpack("n", substr($page,$offset-2,2))) { diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index 862717647b5..b586f3e9406 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -276,4 +276,16 @@ SELECT * FROM payment_method; DROP TABLE payment_method,address,member; +--echo # +--echo # Bug #26958695 INNODB NESTED STORED FIELD WITH CONSTRAINT KEY +--echo # PRODUCE BROKEN TABLE (no bug in MariaDB) +--echo # +create table t1(f1 int,f2 int, primary key(f1), key(f2, f1))engine=innodb; +create table t2(f1 int, f2 int as (2) stored, f3 int as (f2) stored, + foreign key(f1) references t1(f2) on update set NULL) +engine=innodb; +insert into t1 values(1, 1); +insert into t2(f1) values(1); +drop table t2, t1; + --source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb/t/innodb-alter-nullable.test b/mysql-test/suite/innodb/t/innodb-alter-nullable.test index bb5cdee000a..d039459f91f 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-nullable.test +++ b/mysql-test/suite/innodb/t/innodb-alter-nullable.test @@ -71,6 +71,11 @@ WHERE NAME='test/t'; DROP TABLE t; +CREATE TABLE t1(c1 INT) ENGINE=InnoDB; +ALTER TABLE t1 ADD CONSTRAINT UNIQUE KEY i1(c1); +ALTER TABLE t1 CHANGE c1 c1 INT NOT NULL,ADD KEY(c1); +DROP TABLE t1; + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test index d8acc02cbdb..32a54354016 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-timestamp.test +++ b/mysql-test/suite/innodb/t/innodb-alter-timestamp.test @@ -3,7 +3,7 @@ CREATE TABLE t1 (i1 INT UNSIGNED NULL DEFAULT 42) ENGINE=innodb; INSERT INTO t1 VALUES(NULL); --enable_info ---error ER_INVALID_USE_OF_NULL +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 CHANGE i1 i1 INT UNSIGNED NOT NULL DEFAULT rand(), ALGORITHM=INPLACE; --error WARN_DATA_TRUNCATED @@ -12,10 +12,8 @@ ALGORITHM=COPY; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(id), ALGORITHM=INPLACE; ---error ER_INVALID_USE_OF_NULL ALTER TABLE t1 ADD PRIMARY KEY(i1), ALGORITHM=INPLACE; -ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT, -ADD PRIMARY KEY(id); +ALTER TABLE t1 CHANGE i1 id INT UNSIGNED NOT NULL AUTO_INCREMENT; --disable_info SELECT * FROM t1; SHOW CREATE TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-alter.test b/mysql-test/suite/innodb/t/innodb-alter.test index 7a7b4f3a845..e8266c44a90 100644 --- a/mysql-test/suite/innodb/t/innodb-alter.test +++ b/mysql-test/suite/innodb/t/innodb-alter.test @@ -504,6 +504,28 @@ eval ALTER TABLE $source_db.t1 DROP INDEX index2, algorithm=inplace; eval DROP TABLE $source_db.t1; eval DROP DATABASE $source_db; eval DROP DATABASE $dest_db; + +--echo # +--echo # BUG #26334149 MYSQL CRASHES WHEN FULL TEXT INDEXES IBD FILES ARE +--echo # ORPHANED DUE TO RENAME TABLE +--echo # +CREATE DATABASE db1; USE db1; +CREATE TABLE notes ( + id int(11) NOT NULL AUTO_INCREMENT, + body text COLLATE utf8_unicode_ci, + PRIMARY KEY (id) + ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 +COLLATE=utf8_unicode_ci +ROW_FORMAT=COMPRESSED; + +ALTER TABLE notes ADD FULLTEXT INDEX index_ft_body (body(255)); +DROP INDEX index_ft_body ON notes; + +CREATE DATABASE db2; +RENAME TABLE db1.notes TO db2.notes; +DROP DATABASE db1; +DROP DATABASE db2; + USE test; # diff --git a/mysql-test/suite/innodb/t/innodb-isolation.test b/mysql-test/suite/innodb/t/innodb-isolation.test index 56cd668b305..d00fefb9fc6 100644 --- a/mysql-test/suite/innodb/t/innodb-isolation.test +++ b/mysql-test/suite/innodb/t/innodb-isolation.test @@ -301,6 +301,7 @@ SELECT COUNT(*) FROM t5; UPDATE t6 SET b = "updated by client 2"; SELECT * FROM t6; +SELECT * FROM t6 LOCK IN SHARE MODE; SELECT COUNT(*) FROM t6; DELETE FROM t7; diff --git a/mysql-test/suite/innodb/t/innodb-mdev7046.test b/mysql-test/suite/innodb/t/innodb-mdev7046.test index 208dcd52f35..4804e253427 100644 --- a/mysql-test/suite/innodb/t/innodb-mdev7046.test +++ b/mysql-test/suite/innodb/t/innodb-mdev7046.test @@ -40,9 +40,6 @@ ALTER TABLE t1 ENGINE=InnoDB; drop table t1; -let $datadir=`select @@datadir`; ---remove_file $datadir/test/db.opt - --enable_query_log --enable_result_log --enable_warnings 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..1c99c6eeb9d 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; @@ -29,3 +29,34 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; ALTER TABLE t1 ADD COLUMN b LINESTRING DEFAULT POINT(1,1); DESCRIBE t1; DROP TABLE t1; + +--echo # +--echo # Bug #19077964 ASSERT PAGE_SIZE.EQUALS_TO SPACE_PAGE_SIZE +--echo # BTR_COPY_BLOB_PREFIX +--echo # + +CREATE TABLE t1(f1 INT PRIMARY KEY, f3 LINESTRING NOT NULL, + SPATIAL KEY(f3))ENGINE=InnoDB ROW_FORMAT=COMPRESSED + KEY_BLOCK_SIZE=1; +SHOW CREATE TABLE t1; + +let $points = 80; +let $x = 0; +let $y = 0; +let $linestr = (; + +while ($points) +{ + let $linestr = $linestr $x $y,; + dec $points; + inc $x; + inc $y; +} + +let $linestr = $linestr 9999 9999); + +--eval INSERT INTO t1 VALUES (1, ST_linefromtext(concat('linestring', '$linestr'))); + +ALTER TABLE t1 ROW_FORMAT = DYNAMIC, KEY_BLOCK_SIZE=0, ALGORITHM=INPLACE; + +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-table-online.test b/mysql-test/suite/innodb/t/innodb-table-online.test index 1bb4b686b56..538e4b68762 100644 --- a/mysql-test/suite/innodb/t/innodb-table-online.test +++ b/mysql-test/suite/innodb/t/innodb-table-online.test @@ -358,12 +358,10 @@ SET @old_sql_mode = @@sql_mode; # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. # And adding a PRIMARY KEY will also add NOT NULL implicitly! SET @@sql_mode = 'STRICT_TRANS_TABLES'; ---error ER_INVALID_USE_OF_NULL +--error ER_DUP_ENTRY ALTER TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)), ALGORITHM = INPLACE; ---error ER_INVALID_USE_OF_NULL -ALTER TABLE t1 MODIFY c3 CHAR(255) NOT NULL; SET @@sql_mode = @old_sql_mode; UPDATE t1 SET c3=LEFT(CONCAT(c1,REPEAT('foo',c1)),255) WHERE c3 IS NULL; @@ -397,6 +395,7 @@ ROLLBACK; --echo # session con1 connection con1; ALTER TABLE t1 MODIFY c3 CHAR(255) NOT NULL; + SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created WAIT_FOR ins_done'; --send ALTER TABLE t1 DROP PRIMARY KEY, DROP COLUMN c22f, diff --git a/mysql-test/suite/innodb/t/innodb_bug13510739.test b/mysql-test/suite/innodb/t/innodb_bug13510739.test index d2193996d68..f10bcd8e272 100644 --- a/mysql-test/suite/innodb/t/innodb_bug13510739.test +++ b/mysql-test/suite/innodb/t/innodb_bug13510739.test @@ -2,10 +2,6 @@ # Bug#13510739 63775: SERVER CRASH ON HANDLER READ NEXT AFTER DELETE RECORD. # -if (`select plugin_auth_version < "5.6.15" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in InnoDB before 5.6.15 -} -- source include/have_innodb.inc CREATE TABLE bug13510739 (c INTEGER NOT NULL, PRIMARY KEY (c)) ENGINE=INNODB; diff --git a/mysql-test/suite/innodb/t/innodb_bug27216817.test b/mysql-test/suite/innodb/t/innodb_bug27216817.test new file mode 100644 index 00000000000..a93932b4a04 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug27216817.test @@ -0,0 +1,28 @@ +# +# BUG#27216817: INNODB: FAILING ASSERTION: +# PREBUILT->TABLE->N_MYSQL_HANDLES_OPENED == 1 +# + +source include/have_innodb.inc; +create table t1 (a int not null, b int not null) engine=innodb; +insert t1 values (1,2),(3,4); + +lock table t1 write, t1 tr read; +flush status; +alter table t1 add primary key (b); +show status like 'Handler_read_rnd_next'; +unlock tables; +alter table t1 drop primary key; + +lock table t1 write; +flush status; +alter table t1 add primary key (b); +show status like 'Handler_read_rnd_next'; +unlock tables; +alter table t1 drop primary key; + +flush status; +alter table t1 add primary key (b); +show status like 'Handler_read_rnd_next'; + +drop table t1; diff --git a/mysql-test/suite/innodb/t/innodb_bug54044.test b/mysql-test/suite/innodb/t/innodb_bug54044.test index 655e629b61b..cfc6f3c3f0a 100644 --- a/mysql-test/suite/innodb/t/innodb_bug54044.test +++ b/mysql-test/suite/innodb/t/innodb_bug54044.test @@ -16,13 +16,3 @@ CREATE TABLE tmp ENGINE = INNODB AS SELECT COALESCE(NULL, NULL, NULL), GREATEST(NULL, NULL), NULL; SHOW CREATE TABLE tmp; DROP TABLE tmp; - -# These 'create table' operations should fail because of -# using NULL datatype - -CREATE TABLE t1 (a VARCHAR(3)) ENGINE=InnoDB; -INSERT INTO t1 VALUES ('foo'),('bar'); -FLUSH TABLES; ---error 1005 -CREATE TEMPORARY TABLE tmp ENGINE=InnoDB AS SELECT VALUE(a) FROM t1; -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test index d95f412fb37..c414b92f713 100644 --- a/mysql-test/suite/innodb/t/instant_alter.test +++ b/mysql-test/suite/innodb/t/instant_alter.test @@ -311,6 +311,14 @@ INSERT INTO t1 SET a='a'; SELECT * FROM t1; DROP TABLE t1; +# MDEV-16065 Assertion failed in btr_pcur_restore_position_func on UPDATE +eval CREATE TABLE t1 (a INT, b VARCHAR(8), PRIMARY KEY(b,a)) $engine; +INSERT INTO t1 VALUES (1,'foo'); +ALTER TABLE t1 ADD COLUMN c INT; +UPDATE t1 SET c = 1; +UPDATE t1 SET c = 2; +DROP TABLE t1; + dec $format; } disconnect analyze; diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test index 69aab6e2fc1..9d85d281361 100644 --- a/mysql-test/suite/innodb/t/instant_alter_debug.test +++ b/mysql-test/suite/innodb/t/instant_alter_debug.test @@ -1,7 +1,7 @@ --source include/have_innodb.inc --source include/have_debug.inc --source include/have_debug_sync.inc ---source include/have_innodb.inc + SET @save_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency; SET GLOBAL innodb_purge_rseg_truncate_frequency=1; @@ -38,18 +38,9 @@ SELECT * FROM t3; CREATE TABLE t4 (pk INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t4 VALUES (0); ALTER TABLE t4 ADD COLUMN b INT; -SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS +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; @@ -178,4 +169,52 @@ UPDATE t11 SET c22 = 1; --source include/wait_all_purged.inc DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11; +--echo # +--echo # MDEV-15060 Assertion in row_log_table_apply_op after instant ADD +--echo # when the table is emptied during subsequent ALTER TABLE +--echo # + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +ALTER TABLE t1 ADD COLUMN b INT NOT NULL; +connect stop_purge,localhost,root; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connect ddl,localhost,root,,test; +DELETE FROM t1; +SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged'; +send ALTER TABLE t1 FORCE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR copied'; + +BEGIN; +INSERT INTO t1 SET b=1; +ROLLBACK; +disconnect stop_purge; + +# Wait for purge to empty the table. +# (This is based on wait_all_purged.inc, but there are 2 transactions +# from the pending ALTER TABLE t1 FORCE.) + +let $wait_counter= 300; +while ($wait_counter) +{ + --replace_regex /.*History list length ([0-9]+).*/\1/ + let $remaining= `SHOW ENGINE INNODB STATUS`; + if ($remaining == 'InnoDB 2') + { + let $wait_counter= 0; + } + if ($wait_counter) + { + real_sleep 0.1; + dec $wait_counter; + } +} +echo $remaining transactions not purged; + +SET DEBUG_SYNC='now SIGNAL logged'; +disconnect ddl; +DROP TABLE t1; +SET DEBUG_SYNC='RESET'; + SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; diff --git a/mysql-test/suite/innodb/t/rename_table_debug.test b/mysql-test/suite/innodb/t/rename_table_debug.test index df4331cf8bb..c75f2fbca10 100644 --- a/mysql-test/suite/innodb/t/rename_table_debug.test +++ b/mysql-test/suite/innodb/t/rename_table_debug.test @@ -3,6 +3,7 @@ --source include/have_debug_sync.inc --source include/not_embedded.inc +FLUSH TABLES; LET $datadir= `SELECT @@datadir`; CREATE TABLE t1 (a SERIAL, b INT, c INT, d INT) ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb/t/stored_fk.test b/mysql-test/suite/innodb/t/stored_fk.test new file mode 100644 index 00000000000..b9c7c934555 --- /dev/null +++ b/mysql-test/suite/innodb/t/stored_fk.test @@ -0,0 +1,94 @@ +--source include/have_innodb.inc + +--echo # Create statement with FK on base column of stored column +--error ER_CANT_CREATE_TABLE +create table t1(f1 int, f2 int as(f1) stored, + foreign key(f1) references t2(f1) on delete cascade)engine=innodb; + +--echo # adding new stored column during alter table copy operation. +create table t1(f1 int primary key) engine=innodb; +create table t2(f1 int not null, f2 int as (f1) virtual, + foreign key(f1) references t1(f1) on update cascade)engine=innodb; + +# MySQL 5.7 would refuse this +#--error ER_ERROR_ON_RENAME +alter table t2 add column f3 int as (f1) stored, add column f4 int as (f1) virtual; +show create table t2; +drop table t2; + +--echo # adding foreign key constraint for base columns during alter copy. +create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb; +# MySQL 5.7 would refuse this +alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=copy; +show create table t2; +drop table t2; + +--echo # adding foreign key constraint for base columns during online alter. +create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb; +set foreign_key_checks = 0; +--error 138 +alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=inplace; +drop table t2; + +--echo # adding stored column via online alter. +create table t2(f1 int not null, + foreign key(f1) references t1(f1) on update cascade)engine=innodb; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t2 add column f2 int as (f1) stored, algorithm=inplace; +drop table t2, t1; + +--echo # +--echo # BUG#26731689 FK ON TABLE WITH GENERATED COLS: ASSERTION POS < N_DEF +--echo # + +CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED, c INT, + d INT GENERATED ALWAYS AS (0) VIRTUAL, e INT) ENGINE=innodb; + +CREATE TABLE t (a INT) ENGINE=innodb; + +# This would fail. No corresponding index +--error ER_FK_NO_INDEX_PARENT +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null; + +ALTER TABLE t ADD PRIMARY KEY(a); + +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null; + +DROP TABLE s,t; + +CREATE TABLE s (a INT GENERATED ALWAYS AS (0) VIRTUAL, + b INT GENERATED ALWAYS AS (0) STORED, c INT) ENGINE=innodb; + +CREATE TABLE t (a INT) ENGINE=innodb; + +# This would fail. No corresponding index +--error ER_FK_NO_INDEX_PARENT +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null; + +ALTER TABLE t ADD PRIMARY KEY(a); + +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null; + +DROP TABLE s,t; + +CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED) ENGINE=innodb; + +CREATE TABLE t (a INT PRIMARY KEY) ENGINE=innodb; + +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; + +DROP TABLE s,t; + +CREATE TABLE s (a INT, b INT) ENGINE=innodb; + +CREATE TABLE t (a INT) ENGINE=innodb; + +# This would fail. No corresponding index +--error ER_FK_NO_INDEX_PARENT +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; + +ALTER TABLE t ADD PRIMARY KEY(a); + +ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null; + +DROP TABLE s,t; diff --git a/mysql-test/suite/innodb/t/temporary_table.test b/mysql-test/suite/innodb/t/temporary_table.test index 4184daaf064..52334b05999 100644 --- a/mysql-test/suite/innodb/t/temporary_table.test +++ b/mysql-test/suite/innodb/t/temporary_table.test @@ -21,6 +21,7 @@ call mtr.add_suppression("InnoDB: Plugin initialization aborted"); call mtr.add_suppression("innodb_temporary and innodb_system file names seem to be the same"); call mtr.add_suppression("Could not create the shared innodb_temporary"); call mtr.add_suppression("InnoDB: syntax error in file path"); +call mtr.add_suppression("InnoDB: Unable to parse innodb_temp_data_file_path="); --enable_query_log let $MYSQL_TMP_DIR = `select @@tmpdir`; @@ -464,3 +465,23 @@ ALTER TABLE t1 ROW_FORMAT = DYNAMIC; set innodb_strict_mode = ON; ALTER TABLE t1 ADD COLUMN j INT; SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1(f1 INT, KEY(f1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL); +UPDATE t1 SET f1 = 0; +START TRANSACTION; +UPDATE t1 SET f1 = 4; +UPDATE t1 SET f1 = 0; +ROLLBACK; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-15874 CREATE TABLE creates extra transaction +--echo # +call mtr.add_suppression("Warning 150 Create table `mysqld.1`.`t1` with foreign key constraint failed. Temporary tables can't have foreign key constraints.*"); +SET FOREIGN_KEY_CHECKS = 0; +--error ER_CANT_CREATE_TABLE +CREATE TEMPORARY TABLE t1(f1 INT NOT NULL, + FOREIGN KEY(f1) REFERENCES t0(f1))ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb/t/tmpdir.test b/mysql-test/suite/innodb/t/tmpdir.test index 98517a97c7a..b43a02363dc 100644 --- a/mysql-test/suite/innodb/t/tmpdir.test +++ b/mysql-test/suite/innodb/t/tmpdir.test @@ -1,11 +1,6 @@ --source include/have_innodb.inc --source include/count_sessions.inc -if (`select plugin_auth_version <= "5.6.28-MariaDB-76.1" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in XtraDB as of 5.6.28-MariaDB-76.1 or earlier -} - --echo # --echo # Bug #19183565 CREATE DYNAMIC INNODB_TMPDIR VARIABLE TO CONTROL --echo # WHERE INNODB WRITES TEMP FILES diff --git a/mysql-test/suite/innodb/t/undo_log.test b/mysql-test/suite/innodb/t/undo_log.test index c1a98793d91..1f4cf9702d9 100644 --- a/mysql-test/suite/innodb/t/undo_log.test +++ b/mysql-test/suite/innodb/t/undo_log.test @@ -137,3 +137,17 @@ ROLLBACK; SELECT COUNT(*) FROM test_tab; CHECK TABLE test_tab; DROP TABLE test_tab; + +SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; +SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; +CREATE TEMPORARY TABLE t2(i INT)ENGINE=InnoDB; +CREATE TABLE t1(i TEXT NOT NULL) ENGINE=INNODB; +BEGIN; +INSERT t1 SET i=REPEAT('1234567890',840); +UPDATE t1 SET i=''; +INSERT INTO t2 VALUES(2); +ROLLBACK; +--source include/wait_all_purged.inc +DROP TABLE t1; +DROP TABLE t2; +SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; |