From a2a2e32c02643a0cec111511220227703fda1cd5 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Mon, 1 Jul 2024 20:15:00 +0200 Subject: Merging upstream version 1:11.4.2. Signed-off-by: Daniel Baumann --- .../suite/innodb/r/alter_algorithm,INPLACE.rdiff | 13 +- .../suite/innodb/r/alter_algorithm,INSTANT.rdiff | 13 +- .../suite/innodb/r/alter_algorithm,NOCOPY.rdiff | 13 +- mysql-test/suite/innodb/r/alter_algorithm.result | 4 +- mysql-test/suite/innodb/r/alter_kill.result | 11 +- .../suite/innodb/r/alter_missing_tablespace.result | 1 - .../suite/innodb/r/alter_rename_existing.result | 6 + mysql-test/suite/innodb/r/alter_table.result | 6 + mysql-test/suite/innodb/r/autoinc_persist.result | 2 +- mysql-test/suite/innodb/r/data_types.result | 2 +- mysql-test/suite/innodb/r/defrag_mdl-9155.result | 18 --- mysql-test/suite/innodb/r/dropdb_cs.result | 4 + mysql-test/suite/innodb/r/gap_locks.result | 2 +- mysql-test/suite/innodb/r/ibuf_delete.result | 53 -------- mysql-test/suite/innodb/r/ibuf_not_empty.result | 21 ---- mysql-test/suite/innodb/r/import.result | 18 +++ mysql-test/suite/innodb/r/import_no_cfg.result | 21 ++++ mysql-test/suite/innodb/r/import_recovery.result | 80 +++++++++++++ mysql-test/suite/innodb/r/import_run_once.result | 104 ++++++++++++++++ .../suite/innodb/r/import_update_stats.result | 12 +- .../suite/innodb/r/index_tree_operation.result | 1 - .../suite/innodb/r/innodb-alter-autoinc.result | 4 +- .../suite/innodb/r/innodb-alter-timestamp.result | 4 +- mysql-test/suite/innodb/r/innodb-alter.result | 24 ++-- mysql-test/suite/innodb/r/innodb-blob.result | 14 +-- mysql-test/suite/innodb/r/innodb-fkcheck.result | 7 +- .../suite/innodb/r/innodb-index-debug.result | 2 +- .../suite/innodb/r/innodb-index-online.result | 5 +- mysql-test/suite/innodb/r/innodb-index.result | 8 +- mysql-test/suite/innodb/r/innodb-isolation.result | 2 +- .../r/innodb-lock-inherit-read_commited.result | 12 +- .../suite/innodb/r/innodb-online-alter-gis.result | 2 +- .../suite/innodb/r/innodb-system-table-view.result | 5 +- .../suite/innodb/r/innodb-table-online.result | 9 +- .../suite/innodb/r/innodb-wl5522-debug.result | 11 -- mysql-test/suite/innodb/r/innodb-wl5522.result | 5 +- .../suite/innodb/r/innodb-wl5980-alter.result | 7 +- mysql-test/suite/innodb/r/innodb.result | 32 +++-- .../suite/innodb/r/innodb_bug14147491.result | 1 - mysql-test/suite/innodb/r/innodb_bug30423.result | 4 +- mysql-test/suite/innodb/r/innodb_bug30919.result | 4 - mysql-test/suite/innodb/r/innodb_bug40360.result | 2 +- mysql-test/suite/innodb/r/innodb_bug49164.result | 4 +- mysql-test/suite/innodb/r/innodb_bug51920.result | 2 - mysql-test/suite/innodb/r/innodb_bug53592.result | 8 +- mysql-test/suite/innodb/r/innodb_bug53756.result | 28 ++--- mysql-test/suite/innodb/r/innodb_bug56947.result | 9 +- mysql-test/suite/innodb/r/innodb_bug57252.result | 2 +- mysql-test/suite/innodb/r/innodb_bug59733.result | 18 --- mysql-test/suite/innodb/r/innodb_bug68148.result | 1 - .../suite/innodb/r/innodb_bulk_create_index.result | 2 - .../innodb/r/innodb_bulk_create_index_debug.result | 4 - .../innodb/r/innodb_bulk_create_index_small.result | 3 - .../suite/innodb/r/innodb_defrag_binlog.result | 31 ----- .../suite/innodb/r/innodb_defrag_concurrent.result | 102 ---------------- .../suite/innodb/r/innodb_defrag_stats.result | 133 --------------------- .../r/innodb_defrag_stats_many_tables.result | 38 ------ mysql-test/suite/innodb/r/innodb_defragment.result | 133 --------------------- .../suite/innodb/r/innodb_defragment_small.result | 40 ------- .../innodb/r/innodb_lock_wait_timeout_1.result | 6 +- mysql-test/suite/innodb/r/innodb_mysql.result | 89 +++++++------- mysql-test/suite/innodb/r/innodb_scrub.result | 4 + .../innodb/r/innodb_skip_innodb_is_tables.result | 16 --- mysql-test/suite/innodb/r/innodb_stats.result | 8 +- .../suite/innodb/r/innodb_stats_fetch.result | 12 +- .../innodb/r/innodb_stats_fetch_corrupted.result | 4 +- .../innodb/r/innodb_stats_fetch_nonexistent.result | 2 +- .../suite/innodb/r/innodb_status_variables.result | 13 -- mysql-test/suite/innodb/r/insert_debug.result | 2 - mysql-test/suite/innodb/r/insert_into_empty.result | 4 +- .../suite/innodb/r/instant_alter_debug.result | 6 - .../suite/innodb/r/instant_alter_limit,16k.rdiff | 4 +- .../suite/innodb/r/instant_alter_limit,32k.rdiff | 4 +- .../suite/innodb/r/instant_alter_limit,4k.rdiff | 4 +- .../suite/innodb/r/instant_alter_limit,64k.rdiff | 4 +- .../suite/innodb/r/instant_alter_limit,8k.rdiff | 4 +- .../suite/innodb/r/log_data_file_size.result | 4 + mysql-test/suite/innodb/r/log_file_name.result | 1 - mysql-test/suite/innodb/r/log_file_size.result | 4 +- .../suite/innodb/r/log_upgrade_101_flags.result | 2 +- .../innodb/r/max_record_size,16k,compact.rdiff | 4 +- .../innodb/r/max_record_size,16k,dynamic.rdiff | 4 +- .../innodb/r/max_record_size,16k,redundant.rdiff | 4 +- .../innodb/r/max_record_size,32k,compact.rdiff | 4 +- .../innodb/r/max_record_size,32k,dynamic.rdiff | 4 +- .../innodb/r/max_record_size,32k,redundant.rdiff | 4 +- .../innodb/r/max_record_size,4k,compact.rdiff | 4 +- .../innodb/r/max_record_size,4k,dynamic.rdiff | 4 +- .../innodb/r/max_record_size,4k,redundant.rdiff | 4 +- .../innodb/r/max_record_size,64k,compact.rdiff | 4 +- .../innodb/r/max_record_size,64k,dynamic.rdiff | 4 +- .../innodb/r/max_record_size,64k,redundant.rdiff | 4 +- .../innodb/r/max_record_size,8k,compact.rdiff | 4 +- .../innodb/r/max_record_size,8k,dynamic.rdiff | 4 +- mysql-test/suite/innodb/r/mdev-14846.result | 14 ++- mysql-test/suite/innodb/r/mdev-15707.result | 24 ---- mysql-test/suite/innodb/r/monitor.result | 16 --- mysql-test/suite/innodb/r/mvcc.result | 3 - mysql-test/suite/innodb/r/page_reorganize.result | 2 +- mysql-test/suite/innodb/r/partition_locking.result | 2 +- .../suite/innodb/r/read_only_recovery.result | 5 + mysql-test/suite/innodb/r/restart,16k.rdiff | 4 +- mysql-test/suite/innodb/r/restart,32k.rdiff | 4 +- mysql-test/suite/innodb/r/restart,4k.rdiff | 4 +- mysql-test/suite/innodb/r/restart,64k.rdiff | 4 +- mysql-test/suite/innodb/r/restart,8k.rdiff | 4 +- .../suite/innodb/r/row_format_redundant.result | 15 ++- mysql-test/suite/innodb/r/scrub.result | 2 + mysql-test/suite/innodb/r/sys_truncate.result | 21 ++++ .../suite/innodb/r/sys_truncate_debug.result | 49 ++++++++ .../suite/innodb/r/sys_truncate_large.result | 22 ++++ .../suite/innodb/r/sys_truncate_shutdown.result | 23 ++++ .../innodb/r/sys_truncate_shutdown_debug.result | 41 +++++++ mysql-test/suite/innodb/r/table_flags.result | 1 - .../suite/innodb/r/table_index_statistics.result | 3 - mysql-test/suite/innodb/r/temp_truncate.result | 46 +++++++ .../suite/innodb/r/temp_truncate_debug.result | 22 ++++ mysql-test/suite/innodb/r/temporary_table.result | 20 ++-- mysql-test/suite/innodb/r/undo_upgrade.result | 24 ++++ 119 files changed, 779 insertions(+), 976 deletions(-) delete mode 100644 mysql-test/suite/innodb/r/defrag_mdl-9155.result delete mode 100644 mysql-test/suite/innodb/r/ibuf_delete.result delete mode 100644 mysql-test/suite/innodb/r/ibuf_not_empty.result create mode 100644 mysql-test/suite/innodb/r/import.result create mode 100644 mysql-test/suite/innodb/r/import_no_cfg.result create mode 100644 mysql-test/suite/innodb/r/import_recovery.result create mode 100644 mysql-test/suite/innodb/r/import_run_once.result delete mode 100644 mysql-test/suite/innodb/r/innodb_bug59733.result delete mode 100644 mysql-test/suite/innodb/r/innodb_defrag_binlog.result delete mode 100644 mysql-test/suite/innodb/r/innodb_defrag_concurrent.result delete mode 100644 mysql-test/suite/innodb/r/innodb_defrag_stats.result delete mode 100644 mysql-test/suite/innodb/r/innodb_defrag_stats_many_tables.result delete mode 100644 mysql-test/suite/innodb/r/innodb_defragment.result delete mode 100644 mysql-test/suite/innodb/r/innodb_defragment_small.result delete mode 100644 mysql-test/suite/innodb/r/mdev-15707.result create mode 100644 mysql-test/suite/innodb/r/sys_truncate.result create mode 100644 mysql-test/suite/innodb/r/sys_truncate_debug.result create mode 100644 mysql-test/suite/innodb/r/sys_truncate_large.result create mode 100644 mysql-test/suite/innodb/r/sys_truncate_shutdown.result create mode 100644 mysql-test/suite/innodb/r/sys_truncate_shutdown_debug.result create mode 100644 mysql-test/suite/innodb/r/temp_truncate.result create mode 100644 mysql-test/suite/innodb/r/temp_truncate_debug.result (limited to 'mysql-test/suite/innodb/r') diff --git a/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff b/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff index a176a9af..6c0b1bad 100644 --- a/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff +++ b/mysql-test/suite/innodb/r/alter_algorithm,INPLACE.rdiff @@ -1,6 +1,6 @@ ---- alter_algorithm.result 2020-04-30 21:39:48.923115511 +0530 -+++ alter_algorithm.reject 2020-04-30 21:45:04.131642093 +0530 -@@ -7,43 +7,43 @@ +--- alter_algorithm.result ++++ alter_algorithm.reject +@@ -7,40 +7,40 @@ INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); SELECT @@alter_algorithm; @@alter_algorithm @@ -56,13 +56,8 @@ +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT; --affected rows: 1 --info: Records: 1 Duplicates: 0 Warnings: 0 -+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, + info: Records: 0 Duplicates: 0 Warnings: 0 @@ -56,22 +56,22 @@ FOREIGN KEY fidx(f1) REFERENCES t1(f1))ENGINE=INNODB; INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); diff --git a/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff b/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff index 414b7dc8..6a09338d 100644 --- a/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff +++ b/mysql-test/suite/innodb/r/alter_algorithm,INSTANT.rdiff @@ -1,6 +1,6 @@ ---- alter_algorithm.result 2020-04-30 21:39:48.923115511 +0530 -+++ alter_algorithm.reject 2020-04-30 21:47:08.245465018 +0530 -@@ -7,43 +7,35 @@ +--- alter_algorithm.result ++++ alter_algorithm.reject +@@ -7,40 +7,32 @@ INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); SELECT @@alter_algorithm; @@alter_algorithm @@ -48,13 +48,8 @@ -info: Records: 1 Duplicates: 0 Warnings: 0 +Got one of the listed errors ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT; --affected rows: 1 --info: Records: 1 Duplicates: 0 Warnings: 0 -+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, + info: Records: 0 Duplicates: 0 Warnings: 0 @@ -56,22 +48,17 @@ FOREIGN KEY fidx(f1) REFERENCES t1(f1))ENGINE=INNODB; INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); diff --git a/mysql-test/suite/innodb/r/alter_algorithm,NOCOPY.rdiff b/mysql-test/suite/innodb/r/alter_algorithm,NOCOPY.rdiff index 2aa8c722..31795863 100644 --- a/mysql-test/suite/innodb/r/alter_algorithm,NOCOPY.rdiff +++ b/mysql-test/suite/innodb/r/alter_algorithm,NOCOPY.rdiff @@ -1,6 +1,6 @@ ---- alter_algorithm.result 2020-04-30 21:39:48.923115511 +0530 -+++ alter_algorithm.reject 2020-04-30 21:52:10.785967739 +0530 -@@ -7,43 +7,35 @@ +--- alter_algorithm.result ++++ alter_algorithm.reject +@@ -7,40 +7,32 @@ INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1); SELECT @@alter_algorithm; @@alter_algorithm @@ -48,13 +48,8 @@ -info: Records: 1 Duplicates: 0 Warnings: 0 +Got one of the listed errors ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT; --affected rows: 1 --info: Records: 1 Duplicates: 0 Warnings: 0 -+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, + info: Records: 0 Duplicates: 0 Warnings: 0 @@ -56,22 +48,22 @@ FOREIGN KEY fidx(f1) REFERENCES t1(f1))ENGINE=INNODB; INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4); diff --git a/mysql-test/suite/innodb/r/alter_algorithm.result b/mysql-test/suite/innodb/r/alter_algorithm.result index 717d31de..8d85aeb1 100644 --- a/mysql-test/suite/innodb/r/alter_algorithm.result +++ b/mysql-test/suite/innodb/r/alter_algorithm.result @@ -42,8 +42,8 @@ ALTER TABLE t1 ENGINE=INNODB; affected rows: 1 info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT; -affected rows: 1 -info: Records: 1 Duplicates: 0 Warnings: 0 +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, diff --git a/mysql-test/suite/innodb/r/alter_kill.result b/mysql-test/suite/innodb/r/alter_kill.result index 209a711d..af23efe9 100644 --- a/mysql-test/suite/innodb/r/alter_kill.result +++ b/mysql-test/suite/innodb/r/alter_kill.result @@ -1,7 +1,10 @@ +SELECT @@innodb_doublewrite; +@@innodb_doublewrite +OFF +SET GLOBAL innodb_doublewrite=fast; # # Bug#16720368 INNODB CRASHES ON BROKEN #SQL*.IBD FILE AT STARTUP # -SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_stats_persistent=0; CREATE TABLE bug16720368_1 (a INT PRIMARY KEY) ENGINE=InnoDB; connect con1,localhost,root; @@ -13,7 +16,10 @@ connection default; disconnect con1; # Corrupt FIL_PAGE_TYPE in bug16720368.ibd, # and recompute innodb_checksum_algorithm=crc32 -# restart +# restart: --innodb-flush-method=O_DIRECT +SELECT @@innodb_doublewrite; +@@innodb_doublewrite +OFF SELECT COUNT(*) FROM bug16720368; ERROR HY000: Table `test`.`bug16720368` is corrupted. Please drop the table and recreate. INSERT INTO bug16720368 VALUES(1); @@ -30,7 +36,6 @@ 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); diff --git a/mysql-test/suite/innodb/r/alter_missing_tablespace.result b/mysql-test/suite/innodb/r/alter_missing_tablespace.result index 3d071584..65b01d89 100644 --- a/mysql-test/suite/innodb/r/alter_missing_tablespace.result +++ b/mysql-test/suite/innodb/r/alter_missing_tablespace.result @@ -2,7 +2,6 @@ # Bug#13955083 ALLOW IN-PLACE DDL OPERATIONS ON MISSING # OR DISCARDED TABLESPACES # -SET GLOBAL innodb_file_per_table=1; 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; diff --git a/mysql-test/suite/innodb/r/alter_rename_existing.result b/mysql-test/suite/innodb/r/alter_rename_existing.result index 1fdc2011..e4fee341 100644 --- a/mysql-test/suite/innodb/r/alter_rename_existing.result +++ b/mysql-test/suite/innodb/r/alter_rename_existing.result @@ -36,6 +36,8 @@ ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 " # Create another t1, but in the system tablespace. # SET GLOBAL innodb_file_per_table=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t1 (a SERIAL, b CHAR(20)) ENGINE=InnoDB; INSERT INTO t1(b) VALUES('one'), ('two'), ('three'); SHOW CREATE TABLE t1; @@ -58,6 +60,8 @@ ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=COPY; # while a blocking t1.ibd file exists. # SET GLOBAL innodb_file_per_table=ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t1 FORCE, ALGORITHM=INPLACE; ERROR HY000: Tablespace for table 'test/#sql-ib' exists. Please DISCARD the tablespace before IMPORT ALTER TABLE t1 FORCE, ALGORITHM=COPY; @@ -93,3 +97,5 @@ a b 2 two 3 three DROP TABLE t1; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/innodb/r/alter_table.result b/mysql-test/suite/innodb/r/alter_table.result index 192584bc..a0778014 100644 --- a/mysql-test/suite/innodb/r/alter_table.result +++ b/mysql-test/suite/innodb/r/alter_table.result @@ -121,10 +121,16 @@ ERROR 42000: Incorrect column specifier for column 'c' # SET @save_file_per_table=@@GLOBAL.innodb_file_per_table; SET GLOBAL innodb_file_per_table=0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t (c INT PRIMARY KEY) ENGINE=INNODB; SET GLOBAL innodb_file_per_table=1; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t page_compressed=1; SET GLOBAL innodb_file_per_table=@save_file_per_table; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SELECT space>0 FROM information_schema.innodb_sys_tables WHERE name='test/t'; space>0 1 diff --git a/mysql-test/suite/innodb/r/autoinc_persist.result b/mysql-test/suite/innodb/r/autoinc_persist.result index 9e5cd4ad..f3e466a8 100644 --- a/mysql-test/suite/innodb/r/autoinc_persist.result +++ b/mysql-test/suite/innodb/r/autoinc_persist.result @@ -626,7 +626,7 @@ CREATE TABLE mdev6076a (b INT) ENGINE=InnoDB; INSERT INTO mdev6076a VALUES(2),(1); CREATE TABLE mdev6076b (b INT) ENGINE=InnoDB; INSERT INTO mdev6076b VALUES(2),(1); -ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, LOCK=NONE; +ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE; ALTER TABLE mdev6076b ADD COLUMN a SERIAL FIRST, AUTO_INCREMENT=100, diff --git a/mysql-test/suite/innodb/r/data_types.result b/mysql-test/suite/innodb/r/data_types.result index 146f9bb0..695059bf 100644 --- a/mysql-test/suite/innodb/r/data_types.result +++ b/mysql-test/suite/innodb/r/data_types.result @@ -82,7 +82,7 @@ t1_VARCHAR_0 VARCHAR(0), t1_VARMYSQL_0 VARCHAR(0) CHARACTER SET utf8 ) ENGINE=InnoDB; Warnings: -Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead +Warning 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead INSERT INTO t1 () VALUES (); SELECT name, diff --git a/mysql-test/suite/innodb/r/defrag_mdl-9155.result b/mysql-test/suite/innodb/r/defrag_mdl-9155.result deleted file mode 100644 index 18b71f42..00000000 --- a/mysql-test/suite/innodb/r/defrag_mdl-9155.result +++ /dev/null @@ -1,18 +0,0 @@ -set global innodb_defragment=1; -create table t1 (a int not null primary key auto_increment, b varchar(256), key second(a, b)) engine=innodb; -insert t1 select null, repeat('a', 256) from seq_1_to_100; -select count(*) from t1; -count(*) -100 -connect con1,localhost,root; -start transaction; -select count(*) from t1; -count(*) -100 -connection default; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -connection con1; -drop table t1; -set global innodb_defragment=default; diff --git a/mysql-test/suite/innodb/r/dropdb_cs.result b/mysql-test/suite/innodb/r/dropdb_cs.result index 59f02c74..3837bf9e 100644 --- a/mysql-test/suite/innodb/r/dropdb_cs.result +++ b/mysql-test/suite/innodb/r/dropdb_cs.result @@ -3,6 +3,8 @@ # SET @save_fpt=@@GLOBAL.innodb_file_per_table; SET GLOBAL innodb_file_per_table=0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE DATABASE Db; CREATE TABLE Db.t1 (c1 INT KEY) ENGINE=InnoDB; CREATE DATABASE DB; @@ -14,3 +16,5 @@ DROP DATABASE Db; SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'D%'; TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE SET GLOBAL innodb_file_per_table=@save_fpt; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release diff --git a/mysql-test/suite/innodb/r/gap_locks.result b/mysql-test/suite/innodb/r/gap_locks.result index cd60b1fa..d98b7198 100644 --- a/mysql-test/suite/innodb/r/gap_locks.result +++ b/mysql-test/suite/innodb/r/gap_locks.result @@ -1,6 +1,6 @@ CREATE TABLE t1(a INT PRIMARY KEY, b VARCHAR(40), c INT, INDEX(b,c)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,'1',1),(2,'2',1); +INSERT INTO t1 VALUES (1,'1',1),(2,'2',1),(3,'3',1); SET @save_locks= @@GLOBAL.innodb_status_output_locks; SET GLOBAL INNODB_STATUS_OUTPUT_LOCKS = 'ON'; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; diff --git a/mysql-test/suite/innodb/r/ibuf_delete.result b/mysql-test/suite/innodb/r/ibuf_delete.result deleted file mode 100644 index 7423d3cf..00000000 --- a/mysql-test/suite/innodb/r/ibuf_delete.result +++ /dev/null @@ -1,53 +0,0 @@ -SET @buffering= @@innodb_change_buffering; -SET GLOBAL innodb_change_buffering= deletes; -Warnings: -Warning 1287 '@@innodb_change_buffering' is deprecated and will be removed in a future release -SET @flush= @@innodb_flush_log_at_trx_commit; -SET GLOBAL innodb_flush_log_at_trx_commit= 0; -CREATE TABLE t1 ( -a varchar(1024), -b varchar(1024), -c varchar(1024), -d varchar(1024), -e varchar(1024), -f varchar(1024), -g varchar(1024), -h varchar(1024), -key (a), -key (b), -key (c), -key (d) -) ENGINE=InnoDB; -INSERT INTO t1 -SELECT REPEAT('x',10), REPEAT('x',13), REPEAT('x',427), REPEAT('x',244), -REPEAT('x',9), REPEAT('x',112), REPEAT('x',814), REPEAT('x',633) -FROM seq_1_to_1024; -CREATE TEMPORARY TABLE t2 ( -a varchar(1024), -b varchar(1024), -c varchar(1024), -d varchar(1024), -e varchar(1024), -f varchar(1024), -g varchar(1024), -h varchar(1024), -i varchar(1024), -j varchar(1024), -k varchar(1024), -l varchar(1024), -m varchar(1024), -key (a), -key (b), -key (c), -key (d), -key (e), -key (f) -) ENGINE=InnoDB; -SET @x=REPEAT('x',512); -INSERT INTO t2 SELECT @x, @x, @x, @x, @x, @x, @x, @x, @x, @x, @x, @x, @x -FROM seq_1_to_768; -DROP TABLE t1, t2; -SET GLOBAL innodb_change_buffering= @buffering; -Warnings: -Warning 1287 '@@innodb_change_buffering' is deprecated and will be removed in a future release -SET GLOBAL innodb_flush_log_at_trx_commit= @flush; diff --git a/mysql-test/suite/innodb/r/ibuf_not_empty.result b/mysql-test/suite/innodb/r/ibuf_not_empty.result deleted file mode 100644 index 81ca907b..00000000 --- a/mysql-test/suite/innodb/r/ibuf_not_empty.result +++ /dev/null @@ -1,21 +0,0 @@ -CREATE TABLE t1( -a INT AUTO_INCREMENT PRIMARY KEY, -b CHAR(1), -c INT, -INDEX(b)) -ENGINE=InnoDB STATS_PERSISTENT=0; -SET GLOBAL innodb_change_buffering_debug = 1; -SET GLOBAL innodb_change_buffering=all; -Warnings: -Warning 1287 '@@innodb_change_buffering' is deprecated and will be removed in a future release -INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_1024; -# restart: --innodb-force-recovery=6 --innodb-change-buffer-dump -check table t1; -Table Op Msg_type Msg_text -test.t1 check Warning InnoDB: Index 'b' contains 990 entries, should be 1024. -test.t1 check error Corrupt -SET GLOBAL innodb_buf_flush_list_now=ON; -# restart: --innodb-force_recovery=0 -SET GLOBAL innodb_fast_shutdown=0; -# restart: --innodb-force_recovery=0 -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/import.result b/mysql-test/suite/innodb/r/import.result new file mode 100644 index 00000000..d1ed0945 --- /dev/null +++ b/mysql-test/suite/innodb/r/import.result @@ -0,0 +1,18 @@ +# +# MDEV-26137 ALTER TABLE IMPORT enhancement +# +CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 42) CREATE_OPTIONS; +INSERT INTO t1() VALUES(); +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) NOT NULL DEFAULT 42, + PRIMARY KEY (`a`) +) CREATE_OPTIONS +SELECT * FROM t2; +a +42 +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/r/import_no_cfg.result b/mysql-test/suite/innodb/r/import_no_cfg.result new file mode 100644 index 00000000..13570db2 --- /dev/null +++ b/mysql-test/suite/innodb/r/import_no_cfg.result @@ -0,0 +1,21 @@ +# +# MDEV-26137 ALTER TABLE IMPORT enhancement +# +CREATE TABLE t1(a INT PRIMARY KEY DEFAULT 42) CREATE_OPTIONS; +INSERT INTO t1() VALUES(); +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; +Warnings: +Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t2.cfg', will attempt to import without schema verification +Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t2.cfg', will attempt to import without schema verification +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) NOT NULL DEFAULT 42, + PRIMARY KEY (`a`) +) CREATE_OPTIONS +SELECT * FROM t2; +a +42 +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/r/import_recovery.result b/mysql-test/suite/innodb/r/import_recovery.result new file mode 100644 index 00000000..5b391ba6 --- /dev/null +++ b/mysql-test/suite/innodb/r/import_recovery.result @@ -0,0 +1,80 @@ +# +# MDEV-26137 ALTER TABLE IMPORT enhancement +# +call mtr.add_suppression('InnoDB: Tablespace for table `test`.`t1` is set as discarded.'); +call mtr.add_suppression('InnoDB: Tablespace for table `test`.`t2` is set as discarded.'); +call mtr.add_suppression('InnoDB: Tablespace for table `test`.`t3` is set as discarded.'); +call mtr.add_suppression('InnoDB: ./test/t3.ibd: Page 0 at offset 0 looks corrupted.'); +call mtr.add_suppression("mariadbd.*: Index for table 't3' is corrupt; try to repair it"); +call mtr.add_suppression("InnoDB: Expected tablespace id \\d+ but found \\d+ in the file ./test/t3.ibd"); +call mtr.add_suppression("InnoDB: Corrupted page \\[page id: space=.*, page number=0\\] of datafile './test/t3.ibd' could not be found in the doublewrite buffer."); +call mtr.add_suppression('InnoDB: Tablespace for table `test`.`t4` is set as discarded.'); +call mtr.add_suppression('InnoDB: ./test/t4.ibd: Page 0 at offset 0 looks corrupted.'); +call mtr.add_suppression("mariadbd.*: Index for table 't4' is corrupt; try to repair it"); +call mtr.add_suppression("InnoDB: Corrupted page \\[page id: space=.*, page number=0\\] of datafile './test/t4.ibd' could not be found in the doublewrite buffer."); +# Recovery from crashes +## t1: Creation of stub succeeds; server crashes; second import attempt succeeds +## t2: Creation of stub succeeds; server crashes; drop table +## t3: Creation of stub succeeds; server crashes; ibd corrupted; second import attempt fails; drop table +## t4: Did not copy .cfg; creation of stub succeeds; server crashes; ibd corrupted; second import attempt fails; drop table +CREATE TABLE t (a int) ENGINE=InnoDB; +INSERT INTO t VALUES(42); +FLUSH TABLES t FOR EXPORT; +UNLOCK TABLES; +SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.0; +SET GLOBAL innodb_max_dirty_pages_pct=0.0; +connect hang1,localhost,root; +SET DEBUG_SYNC='ib_after_create_stub_for_import SIGNAL hung WAIT_FOR ever'; +ALTER TABLE t1 IMPORT TABLESPACE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +connect hang2,localhost,root; +SET DEBUG_SYNC='ib_after_create_stub_for_import SIGNAL hung WAIT_FOR ever'; +ALTER TABLE t2 IMPORT TABLESPACE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +connect hang3,localhost,root; +SET DEBUG_SYNC='ib_after_create_stub_for_import SIGNAL hung WAIT_FOR ever'; +ALTER TABLE t3 IMPORT TABLESPACE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +connect hang4,localhost,root; +SET DEBUG_SYNC='ib_after_create_stub_for_import SIGNAL hung WAIT_FOR ever'; +ALTER TABLE t4 IMPORT TABLESPACE; +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +# corrupting the 0th page +# Restart mysqld after the crash and reconnect. +# restart +ALTER TABLE t1 IMPORT TABLESPACE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT * FROM t1; +a +42 +ALTER TABLE t3 IMPORT TABLESPACE; +ERROR HY000: Internal error: Error importing tablespace for table `test`.`t3` : Data structure corruption +ALTER TABLE t4 IMPORT TABLESPACE; +ERROR HY000: Schema mismatch (Expected FSP_SPACE_FLAGS=0x15, .ibd file contains 0x1010115.) +DROP TABLE t, t1, t2, t3, t4; +# Recovery from corruption only, no server restart +## t5: Recovery from corruption, with cfg +## t6: Recovery from corruption, without cfg +call mtr.add_suppression('InnoDB: ./test/t5.ibd: Page 0 at offset 0 looks corrupted.'); +call mtr.add_suppression("mariadbd.*: Index for table 't5' is corrupt; try to repair it"); +call mtr.add_suppression("InnoDB: Corrupted page \\[page id: space=.*, page number=0\\] of datafile './test/t5.ibd' could not be found in the doublewrite buffer."); +call mtr.add_suppression("InnoDB: Corrupted page \\[page id: space=.*, page number=0\\] of datafile './test/t6.ibd' could not be found in the doublewrite buffer."); +call mtr.add_suppression("mariadbd.*: Index for table 't6' is corrupt; try to repair it"); +CREATE TABLE t (a int) ENGINE=InnoDB; +INSERT INTO t VALUES(42); +FLUSH TABLES t FOR EXPORT; +UNLOCK TABLES; +# corrupting the 0th page +ALTER TABLE t5 IMPORT TABLESPACE; +ERROR HY000: Internal error: Error importing tablespace for table `test`.`t5` : Data structure corruption +ALTER TABLE t6 IMPORT TABLESPACE; +ERROR HY000: Schema mismatch (Expected FSP_SPACE_FLAGS=0x15, .ibd file contains 0x1010115.) +DROP TABLE t, t5, t6; diff --git a/mysql-test/suite/innodb/r/import_run_once.result b/mysql-test/suite/innodb/r/import_run_once.result new file mode 100644 index 00000000..e49340b8 --- /dev/null +++ b/mysql-test/suite/innodb/r/import_run_once.result @@ -0,0 +1,104 @@ +# +# MDEV-26137 ALTER TABLE IMPORT enhancement +# +# drop t1 before importing t2 +CREATE TABLE t1 (a int) ENGINE=InnoDB; +INSERT INTO t1 VALUES(42); +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +DROP TABLE t1; +ALTER TABLE t2 IMPORT TABLESPACE; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT * FROM t2; +a +42 +DROP TABLE t2; +# created t2 but did not discard tablespace +CREATE TABLE t1 (a int) ENGINE=InnoDB; +INSERT INTO t1 VALUES(42); +CREATE TABLE t2 LIKE t1; +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +DROP TABLE t1; +call mtr.add_suppression("InnoDB: Unable to import tablespace"); +ALTER TABLE t2 IMPORT TABLESPACE; +ERROR HY000: Tablespace for table 'test/t2' exists. Please DISCARD the tablespace before IMPORT +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT * FROM t2; +a +DROP TABLE t2; +# attempt to import when there's no tablespace +ALTER TABLE t2 IMPORT TABLESPACE; +ERROR 42S02: Table 'test.t2' doesn't exist +# with index +CREATE TABLE t1 (a int, b varchar(50)) ENGINE=InnoDB; +CREATE UNIQUE INDEX ai ON t1 (a); +INSERT INTO t1 VALUES(42, "hello"); +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `b` varchar(50) DEFAULT NULL, + UNIQUE KEY `ai` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT * FROM t2; +a b +42 hello +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 ai 1 a A 1 NULL NULL YES BTREE NO +SHOW INDEX FROM t2; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 ai 1 a A 1 NULL NULL YES BTREE NO +DROP TABLE t1, t2; +# with virtual column index +CREATE TABLE t1 (a int, b int as (a * a)) ENGINE=InnoDB; +CREATE UNIQUE INDEX ai ON t1 (b); +INSERT INTO t1 VALUES(42, default); +FLUSH TABLES t1 FOR EXPORT; +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL, + `b` int(11) GENERATED ALWAYS AS (`a` * `a`) VIRTUAL, + UNIQUE KEY `ai` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +SELECT * FROM t2; +a b +42 1764 +SELECT b FROM t2 USE INDEX (ai); +b +1764 +SHOW INDEX FROM t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 ai 1 b A 1 NULL NULL YES BTREE NO +SHOW INDEX FROM t2; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t2 0 ai 1 b A 1 NULL NULL YES BTREE NO +CHECK TABLE t2 EXTENDED; +Table Op Msg_type Msg_text +test.t2 check status OK +DROP TABLE t1, t2; +# with auto_increment +CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, i2 INT, i1 INT)ENGINE=INNODB; +INSERT INTO t1 (i2) SELECT 4 FROM seq_1_to_1024; +FLUSH TABLE t1 FOR EXPORT; +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; +CHECK TABLE t2 EXTENDED; +Table Op Msg_type Msg_text +test.t2 check status OK +DROP TABLE t2, t1; diff --git a/mysql-test/suite/innodb/r/import_update_stats.result b/mysql-test/suite/innodb/r/import_update_stats.result index ddf78f18..91e6e33e 100644 --- a/mysql-test/suite/innodb/r/import_update_stats.result +++ b/mysql-test/suite/innodb/r/import_update_stats.result @@ -1,8 +1,3 @@ -SET @old_innodb_file_per_table = @@innodb_file_per_table; -SET GLOBAL innodb_file_per_table = 1; -SELECT @@innodb_file_per_table; -@@innodb_file_per_table -1 CREATE TABLE t1 ( col_1 CHAR (255), col_2 VARCHAR (255) @@ -14,13 +9,9 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t1 1 idx1 1 col_1 A 0 NULL NULL YES BTREE NO t1 1 idx2 1 col_2 A 0 NULL NULL YES BTREE NO INSERT INTO t1 VALUES ("col1_00001", "col2_00001"), ("col1_00002", "col2_00002"); -SHOW INDEXES FROM t1; -Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored -t1 1 idx1 1 col_1 A 2 NULL NULL YES BTREE NO -t1 1 idx2 1 col_2 A 2 NULL NULL YES BTREE NO +SET STATEMENT use_stat_tables=never FOR ANALYZE TABLE t1; Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SHOW INDEXES FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored @@ -69,4 +60,3 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t1 1 idx1 1 col_1 A 2 NULL NULL YES BTREE NO t1 1 idx2 1 col_2 A 2 NULL NULL YES BTREE NO DROP TABLE t1; -SET GLOBAL innodb_file_per_table = @old_innodb_file_per_table; diff --git a/mysql-test/suite/innodb/r/index_tree_operation.result b/mysql-test/suite/innodb/r/index_tree_operation.result index 29660962..92565333 100644 --- a/mysql-test/suite/innodb/r/index_tree_operation.result +++ b/mysql-test/suite/innodb/r/index_tree_operation.result @@ -2,7 +2,6 @@ # Bug#15923864 (Bug#67718): # INNODB DRASTICALLY UNDER-FILLS PAGES IN CERTAIN CONDITIONS # -SET GLOBAL innodb_file_per_table=ON; CREATE TABLE t1 (a BIGINT PRIMARY KEY, b VARCHAR(4096)) ENGINE=InnoDB; INSERT INTO t1 VALUES (0, REPEAT('a', 4096)); INSERT INTO t1 VALUES (1000, REPEAT('a', 4096)); diff --git a/mysql-test/suite/innodb/r/innodb-alter-autoinc.result b/mysql-test/suite/innodb/r/innodb-alter-autoinc.result index 3186b00a..4aea9d14 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-autoinc.result +++ b/mysql-test/suite/innodb/r/innodb-alter-autoinc.result @@ -5,7 +5,7 @@ SET @@sql_mode = 'STRICT_TRANS_TABLES'; ALTER TABLE t1 ADD PRIMARY KEY(a); SET @@sql_mode = @old_sql_mode; ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, -LOCK=NONE; +ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED ALTER TABLE t1 ADD id INT AUTO_INCREMENT; ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key @@ -50,7 +50,7 @@ t1 CREATE TABLE `t1` ( KEY `id` (`id`,`a`) ) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, -DROP COLUMN id, AUTO_INCREMENT = 42, LOCK=NONE; +DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED ALTER TABLE t1 DROP PRIMARY KEY, ADD id INT AUTO_INCREMENT PRIMARY KEY, DROP COLUMN id, AUTO_INCREMENT = 42, ALGORITHM=INPLACE; diff --git a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result index 1a68c540..fc26244d 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-timestamp.result +++ b/mysql-test/suite/innodb/r/innodb-alter-timestamp.result @@ -86,8 +86,8 @@ ALTER TABLE t1 ADD COLUMN d2 TIMESTAMP DEFAULT '2017-05-08 16:23:45', LOCK=NONE; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 -ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED +ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE, LOCK=NONE; +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1, ALGORITHM=INPLACE; ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY ALTER TABLE t1 ADD COLUMN d3 TIMESTAMP DEFAULT d1; diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result index edeebac5..353ad2b3 100644 --- a/mysql-test/suite/innodb/r/innodb-alter.result +++ b/mysql-test/suite/innodb/r/innodb-alter.result @@ -438,28 +438,28 @@ tt CREATE TABLE `tt` ( PRIMARY KEY (`pk`), FULLTEXT KEY `ct` (`ct`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED -ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED +ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE +ALTER TABLE tt ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE CREATE TABLE tu ( pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT, FULLTEXT INDEX(t) ) ENGINE=InnoDB; -ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED -ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED +ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE +ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE DROP TABLE tu; CREATE TABLE tv ( pk INT PRIMARY KEY, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, t TEXT, UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID), FULLTEXT INDEX(t) ) ENGINE=InnoDB; -ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED -ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED +ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE +ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE DROP TABLE tv; ALTER TABLE t1o CHANGE c1 dB_row_Id INT, ALGORITHM=COPY; ERROR 42000: Incorrect column name 'dB_row_Id' diff --git a/mysql-test/suite/innodb/r/innodb-blob.result b/mysql-test/suite/innodb/r/innodb-blob.result index 0be1ca5b..634a3ee9 100644 --- a/mysql-test/suite/innodb/r/innodb-blob.result +++ b/mysql-test/suite/innodb/r/innodb-blob.result @@ -19,7 +19,7 @@ a RIGHT(b,20) 1 aaaaaaaaaaaaaaaaaaaa 2 bbbbbbbbbbbbbbbbbbbb connection default; -SET DEBUG='+d,row_ins_extern_checkpoint'; +SET DEBUG_DBUG='+d,row_ins_extern_checkpoint'; SET DEBUG_SYNC='before_row_ins_extern_latch SIGNAL rec_not_blob WAIT_FOR crash'; ROLLBACK; BEGIN; @@ -27,8 +27,8 @@ INSERT INTO t1 VALUES (3,REPEAT('c',50000)); connection con1; SET DEBUG_SYNC='now WAIT_FOR rec_not_blob'; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-UNCOMMITTED SELECT a, RIGHT(b,20) FROM t1; a RIGHT(b,20) @@ -58,8 +58,8 @@ UPDATE t3 SET c=REPEAT('f',3000) WHERE a=1; connect con1,localhost,root,,; SET DEBUG_SYNC='now WAIT_FOR go_sel'; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-UNCOMMITTED SELECT a, RIGHT(b,20), RIGHT(c,20) FROM t3; a RIGHT(b,20) RIGHT(c,20) @@ -85,7 +85,7 @@ connect con2,localhost,root,,; BEGIN; INSERT INTO t2 VALUES (347); connection default; -SET DEBUG='+d,row_upd_extern_checkpoint'; +SET DEBUG_DBUG='+d,row_upd_extern_checkpoint'; SET DEBUG_SYNC='before_row_upd_extern SIGNAL have_latch WAIT_FOR crash'; UPDATE t3 SET c=REPEAT('i',3000) WHERE a=2; connection con2; @@ -121,7 +121,7 @@ connect con2,localhost,root,,; BEGIN; INSERT INTO t2 VALUES (33101); connection default; -SET DEBUG='+d,row_upd_extern_checkpoint'; +SET DEBUG_DBUG='+d,row_upd_extern_checkpoint'; SET DEBUG_SYNC='after_row_upd_extern SIGNAL have_latch WAIT_FOR crash'; UPDATE t3 SET c=REPEAT('j',3000) WHERE a=2; connection con2; diff --git a/mysql-test/suite/innodb/r/innodb-fkcheck.result b/mysql-test/suite/innodb/r/innodb-fkcheck.result index f86ba505..357d8aa1 100644 --- a/mysql-test/suite/innodb/r/innodb-fkcheck.result +++ b/mysql-test/suite/innodb/r/innodb-fkcheck.result @@ -1,6 +1,3 @@ -set global innodb_file_per_table = 1; -drop table if exists b; -drop database if exists bug_fk; create database bug_fk; use bug_fk; CREATE TABLE b ( @@ -93,5 +90,5 @@ show warnings; Level Code Message b.frm b.ibd -drop table if exists b; -drop database if exists bug_fk; +drop table b; +drop database bug_fk; diff --git a/mysql-test/suite/innodb/r/innodb-index-debug.result b/mysql-test/suite/innodb/r/innodb-index-debug.result index 7167a80f..d881f140 100644 --- a/mysql-test/suite/innodb/r/innodb-index-debug.result +++ b/mysql-test/suite/innodb/r/innodb-index-debug.result @@ -91,7 +91,7 @@ SIGNAL opened WAIT_FOR flushed'; ALTER TABLE t1 FORCE, ADD COLUMN k4 int; connection default; SET DEBUG_SYNC= 'now WAIT_FOR opened'; -SET debug = '+d,row_log_tmpfile_fail'; +SET debug_dbug = '+d,row_log_tmpfile_fail'; INSERT INTO t1 select NULL,'aaa','bbb' from t480; INSERT INTO t1 select NULL,'aaaa','bbbb' from t480; SET DEBUG_SYNC= 'now SIGNAL flushed'; diff --git a/mysql-test/suite/innodb/r/innodb-index-online.result b/mysql-test/suite/innodb/r/innodb-index-online.result index 5a287e58..e4951d33 100644 --- a/mysql-test/suite/innodb/r/innodb-index-online.result +++ b/mysql-test/suite/innodb/r/innodb-index-online.result @@ -1,8 +1,6 @@ SET GLOBAL innodb_monitor_reset_all=all; SET GLOBAL innodb_monitor_reset_all=default; call mtr.add_suppression("InnoDB: Warning: Small buffer pool size"); -SET @global_innodb_file_per_table_orig = @@global.innodb_file_per_table; -SET GLOBAL innodb_file_per_table = on; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 TEXT) ENGINE=InnoDB STATS_PERSISTENT=0; INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,''); @@ -209,7 +207,7 @@ CREATE INDEX c2d ON t1(c2); SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 0 PRIMARY 1 c1 A 80 NULL NULL BTREE NO -t1 1 c2d 1 c2 A 10 NULL NULL YES BTREE NO +t1 1 c2d 1 c2 A 5 NULL NULL YES BTREE NO EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c2d c2d 5 NULL 32 Using where; Using index @@ -544,6 +542,5 @@ Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; SET DEBUG_SYNC = 'RESET'; -SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig; SET GLOBAL innodb_monitor_enable = default; SET GLOBAL innodb_monitor_disable = default; diff --git a/mysql-test/suite/innodb/r/innodb-index.result b/mysql-test/suite/innodb/r/innodb-index.result index a76837b9..8346b61d 100644 --- a/mysql-test/suite/innodb/r/innodb-index.result +++ b/mysql-test/suite/innodb/r/innodb-index.result @@ -853,10 +853,10 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK explain select * from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 explain select * from t1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 drop table t1; SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; @@ -1598,8 +1598,8 @@ create table t1(f1 int not null, f2 int not null, primary key (f1), unique key(f1, f2))engine=innodb; insert into t1 values(1,3), (2,2); alter table t1 drop primary key, lock=none; -alter table t1 drop index f1, lock=none; -ERROR 0A000: LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED +alter table t1 drop index f1, algorithm=inplace, lock=none; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY drop table t1; # # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP diff --git a/mysql-test/suite/innodb/r/innodb-isolation.result b/mysql-test/suite/innodb/r/innodb-isolation.result index b6e512cc..8ff2c7f1 100644 --- a/mysql-test/suite/innodb/r/innodb-isolation.result +++ b/mysql-test/suite/innodb/r/innodb-isolation.result @@ -971,7 +971,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 index NULL k2 5 NULL # Using index EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL # Using where; Using index +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL # Using where 2 SUBQUERY t1 index NULL k2 5 NULL # Using index # # Make all indexes in t2 obsolete to the active repeatable read transaction diff --git a/mysql-test/suite/innodb/r/innodb-lock-inherit-read_commited.result b/mysql-test/suite/innodb/r/innodb-lock-inherit-read_commited.result index ab0427ca..10995515 100644 --- a/mysql-test/suite/innodb/r/innodb-lock-inherit-read_commited.result +++ b/mysql-test/suite/innodb/r/innodb-lock-inherit-read_commited.result @@ -12,8 +12,8 @@ SET GLOBAL innodb_stats_auto_recalc = OFF; connect purge_control,localhost,root; START TRANSACTION WITH CONSISTENT SNAPSHOT; connection default; -SET @old_tx_isolation = @@tx_isolation; -SET GLOBAL tx_isolation = 'READ-COMMITTED'; +SET @old_tx_isolation = @@transaction_isolation; +SET GLOBAL transaction_isolation = 'READ-COMMITTED'; SET @old_innodb_lock_wait_timeout = @@innodb_lock_wait_timeout; SET GLOBAL innodb_lock_wait_timeout = 1; connect con1,localhost,root,,; @@ -58,7 +58,7 @@ Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; SET GLOBAL innodb_stats_auto_recalc = @old_innodb_stats_auto_recalc; -SET GLOBAL tx_isolation = @old_tx_isolation; +SET GLOBAL transaction_isolation = @old_tx_isolation; SET GLOBAL innodb_lock_wait_timeout = @old_innodb_lock_wait_timeout; CREATE TABLE t1 ( a INT NOT NULL, @@ -71,8 +71,8 @@ SET GLOBAL innodb_stats_auto_recalc = OFF; connect purge_control,localhost,root; START TRANSACTION WITH CONSISTENT SNAPSHOT; connection default; -SET @old_tx_isolation = @@tx_isolation; -SET GLOBAL tx_isolation = 'READ-COMMITTED'; +SET @old_tx_isolation = @@transaction_isolation; +SET GLOBAL transaction_isolation = 'READ-COMMITTED'; SET @old_innodb_lock_wait_timeout = @@innodb_lock_wait_timeout; SET GLOBAL innodb_lock_wait_timeout = 1; connect con1,localhost,root,,; @@ -117,5 +117,5 @@ Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; SET GLOBAL innodb_stats_auto_recalc = @old_innodb_stats_auto_recalc; -SET GLOBAL tx_isolation = @old_tx_isolation; +SET GLOBAL transaction_isolation = @old_tx_isolation; SET GLOBAL innodb_lock_wait_timeout = @old_innodb_lock_wait_timeout; 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 6a57be49..59859303 100644 --- a/mysql-test/suite/innodb/r/innodb-online-alter-gis.result +++ b/mysql-test/suite/innodb/r/innodb-online-alter-gis.result @@ -23,7 +23,7 @@ t1 CREATE TABLE `t1` ( SPATIAL KEY `c` (`b`), KEY `d` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci -ALTER ONLINE TABLE t1 ADD PRIMARY KEY(a),DROP INDEX d; +ALTER ONLINE TABLE t1 ADD PRIMARY KEY(a),DROP INDEX d, ALGORITHM=INPLACE; ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED show warnings; Level Code Message diff --git a/mysql-test/suite/innodb/r/innodb-system-table-view.result b/mysql-test/suite/innodb/r/innodb-system-table-view.result index ac966d3f..ab1d6bc8 100644 --- a/mysql-test/suite/innodb/r/innodb-system-table-view.result +++ b/mysql-test/suite/innodb/r/innodb-system-table-view.result @@ -10,7 +10,7 @@ TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE 11 SYS_FOREIGN 0 7 0 Redundant 0 System 12 SYS_FOREIGN_COLS 0 7 0 Redundant 0 System 13 SYS_VIRTUAL 0 6 0 Redundant 0 System -16 mysql/transaction_registry 33 8 3 Dynamic 0 Single +16 mysql/transaction_registry 33 8 6 Dynamic 0 Single SELECT table_id,pos,mtype,prtype,len,name FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE table_id NOT IN (@table_stats_id, @index_stats_id) @@ -77,6 +77,9 @@ test/t_dynamic test/t_dynamic 33 5 Dynamic 0 test/t_redundant test/t_redundant 0 5 Redundant 0 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path +innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001 +innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002 +innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003 test/t_redundant DEFAULT DEFAULT MYSQLD_DATADIR/test/t_redundant.ibd test/t_compact DEFAULT DEFAULT MYSQLD_DATADIR/test/t_compact.ibd test/t_compressed DEFAULT 2048 MYSQLD_DATADIR/test/t_compressed.ibd diff --git a/mysql-test/suite/innodb/r/innodb-table-online.result b/mysql-test/suite/innodb/r/innodb-table-online.result index 76811ced..6948038a 100644 --- a/mysql-test/suite/innodb/r/innodb-table-online.result +++ b/mysql-test/suite/innodb/r/innodb-table-online.result @@ -3,8 +3,6 @@ SET GLOBAL innodb_monitor_reset_all=default; call mtr.add_suppression("InnoDB: Warning: Small buffer pool size"); call mtr.add_suppression("InnoDB: Error: table 'test/t1'"); call mtr.add_suppression("MariaDB is trying to open a table handle but the .ibd file for"); -SET @global_innodb_file_per_table_orig = @@global.innodb_file_per_table; -SET GLOBAL innodb_file_per_table = on; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 CHAR(255) NOT NULL) ENGINE = InnoDB; INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,''); @@ -39,11 +37,11 @@ SET @saved_debug_dbug = @@SESSION.debug_dbug; SET DEBUG_DBUG = '+d,innodb_OOM_prepare_inplace_alter'; ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE; ERROR HY000: Out of memory. -SET SESSION DEBUG = @saved_debug_dbug; -SET SESSION DEBUG = '+d,innodb_OOM_inplace_alter'; +SET SESSION DEBUG_DBUG = @saved_debug_dbug; +SET SESSION DEBUG_DBUG = '+d,innodb_OOM_inplace_alter'; ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE; ERROR HY000: Out of memory. -SET SESSION DEBUG = @saved_debug_dbug; +SET SESSION DEBUG_DBUG = @saved_debug_dbug; ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE; connection default; SHOW CREATE TABLE t1; @@ -492,6 +490,5 @@ test.t1 check status OK DROP TABLE t1; SET DEBUG_SYNC=RESET; disconnect con1; -SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig; SET GLOBAL innodb_monitor_enable = default; SET GLOBAL innodb_monitor_disable = default; diff --git a/mysql-test/suite/innodb/r/innodb-wl5522-debug.result b/mysql-test/suite/innodb/r/innodb-wl5522-debug.result index e7af2d94..1c3b10c0 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5522-debug.result +++ b/mysql-test/suite/innodb/r/innodb-wl5522-debug.result @@ -12,21 +12,18 @@ call mtr.add_suppression("InnoDB: Cannot save statistics for table `test`\\.`t1` call mtr.add_suppression("InnoDB: Database page corruption on disk or a failed read of file '.*ibdata1' page"); call mtr.add_suppression("InnoDB: File '.*ibdata1' is corrupted"); FLUSH TABLES; -SET GLOBAL innodb_file_per_table = 1; CREATE TABLE t1 (c1 INT) ENGINE = InnoDB; INSERT INTO t1 VALUES(1),(2),(3); SET SESSION debug_dbug="+d,ib_discard_before_commit_crash"; ALTER TABLE t1 DISCARD TABLESPACE; ERROR HY000: Lost connection to server during query DROP TABLE t1; -SET GLOBAL innodb_file_per_table = 1; CREATE TABLE t1 (c1 INT) ENGINE = InnoDB; INSERT INTO t1 VALUES(1),(2),(3); SET SESSION debug_dbug="+d,ib_discard_after_commit_crash"; ALTER TABLE t1 DISCARD TABLESPACE; ERROR HY000: Lost connection to server during query DROP TABLE t1; -SET GLOBAL innodb_file_per_table = 1; CREATE TABLE t1 (c1 INT) ENGINE = Innodb; INSERT INTO t1 VALUES (1), (2), (3), (4); FLUSH TABLES t1 FOR EXPORT; @@ -52,8 +49,6 @@ ERROR HY000: Lost connection to server during query unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; -SET @file_per_table= @@innodb_file_per_table; -SET GLOBAL innodb_file_per_table = 1; CREATE TABLE t1 (c1 INT) ENGINE = Innodb; ALTER TABLE t1 DISCARD TABLESPACE; restore: t1 .ibd and .cfg files @@ -459,11 +454,6 @@ ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Got error 42 'Tablespace not found' from ./test/t1.ibd SET SESSION debug_dbug=@saved_debug_dbug; restore: t1 .ibd and .cfg files -SET SESSION debug_dbug="+d,ib_import_check_bitmap_failure"; -ALTER TABLE t1 IMPORT TABLESPACE; -ERROR HY000: Index for table 't1' is corrupt; try to repair it -SET SESSION debug_dbug=@saved_debug_dbug; -restore: t1 .ibd and .cfg files SET SESSION debug_dbug="+d,ib_import_cluster_root_adjust_failure"; ALTER TABLE t1 IMPORT TABLESPACE; ERROR HY000: Index for table 't1' is corrupt; try to repair it @@ -943,4 +933,3 @@ SET SESSION debug_dbug=@saved_debug_dbug; DROP TABLE t1; unlink: t1.ibd unlink: t1.cfg -SET GLOBAL INNODB_FILE_PER_TABLE=@file_per_table; diff --git a/mysql-test/suite/innodb/r/innodb-wl5522.result b/mysql-test/suite/innodb/r/innodb-wl5522.result index 6baa5df9..2733809e 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5522.result +++ b/mysql-test/suite/innodb/r/innodb-wl5522.result @@ -134,7 +134,6 @@ ALTER TABLE t2 IMPORT TABLESPACE; Warnings: Warning 1810 IO Read error: (2, No such file or directory) Error opening './test/t2.cfg', will attempt to import without schema verification DROP TABLE t2; -SET GLOBAL innodb_file_per_table = 1; SELECT @@innodb_file_per_table; @@innodb_file_per_table 1 @@ -299,6 +298,8 @@ c1 c2 unlink: t1.cfg DROP TABLE t1; SET GLOBAL innodb_file_per_table = 0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB; @@ -316,6 +317,8 @@ Warning 1809 Table `test`.`t1` in system tablespace UNLOCK TABLES; DROP TABLE t1; SET GLOBAL innodb_file_per_table = 1; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT, INDEX idx(c2)) ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result index 4d6ac474..5f729118 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5980-alter.result +++ b/mysql-test/suite/innodb/r/innodb-wl5980-alter.result @@ -2,9 +2,7 @@ # This is a copy of innodb-alter.test except using remote tablespaces # and showing those files. # -SET @innodb_file_per_table_orig=@@GLOBAL.innodb_file_per_table; SET default_storage_engine=InnoDB; -SET GLOBAL innodb_file_per_table=ON; SET NAMES utf8mb4; CREATE TABLE t1 ( c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, @@ -1385,8 +1383,8 @@ t1c.ibd t1p.ibd tt.ibd ALTER TABLE t1o CHANGE FTS_DOC_ID foo_id BIGINT UNSIGNED NOT NULL, -LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try LOCK=SHARED +ALGORITHM=INPLACE, LOCK=NONE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot drop or rename FTS_DOC_ID. Try ALGORITHM=COPY SELECT sc.pos FROM information_schema.innodb_sys_columns sc INNER JOIN information_schema.innodb_sys_tables st ON sc.TABLE_ID=st.TABLE_ID @@ -1589,4 +1587,3 @@ 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 -SET GLOBAL innodb_file_per_table = @innodb_file_per_table_orig; diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index e9f70621..f1cf23d6 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -931,7 +931,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort explain select a from t1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index +1 SIMPLE t1 index NULL PRIMARY 4 NULL # explain select b from t1 order by b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL b 4 NULL # Using index @@ -1017,8 +1017,8 @@ SET sql_mode = default; create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb; BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -SELECT @@tx_isolation,@@global.tx_isolation; -@@tx_isolation @@global.tx_isolation +SELECT @@transaction_isolation, @@global.transaction_isolation; +@@transaction_isolation @@global.transaction_isolation SERIALIZABLE REPEATABLE-READ insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); select id, code, name from t1 order by id; @@ -1367,14 +1367,28 @@ PRIMARY KEY (`id`), KEY `id_version` (`id_version`) ) ENGINE=InnoDB; INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9"); +ANALYZE table t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze Warning Engine-independent statistics are not collected for column 'description' +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +explain SELECT t2.id, t1.`label` FROM t2 INNER JOIN +(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl +ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL id_object NULL NULL NULL 6 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.id_object 1 +1 SIMPLE t1 ref id_object id_object 5 test.t1.id_object 1 SELECT t2.id, t1.`label` FROM t2 INNER JOIN (SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); id label -3382 Test 102 Le Pekin (Test) 1794 Test de resto 1822 Test 3 +3382 Test 3524 Societe Test 3525 Fournisseur Test drop table t1,t2; @@ -1676,7 +1690,7 @@ count(*) 0 explain select count(*) from t1 where x > -16; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 2 Using where select count(*) from t1 where x > -16; count(*) 2 @@ -3129,7 +3143,7 @@ CONNECT c1,localhost,root,,; CONNECT c2,localhost,root,,; connection c1; SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; +SET TRANSACTION_ISOLATION='read-committed'; SET AUTOCOMMIT=0; DROP TABLE IF EXISTS t1, t2; Warnings: @@ -3140,7 +3154,7 @@ SELECT * FROM t2; a connection c2; SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; +SET TRANSACTION_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (1); COMMIT; @@ -3154,13 +3168,13 @@ CONNECT c1,localhost,root,,; CONNECT c2,localhost,root,,; connection c1; SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; +SET TRANSACTION_ISOLATION='read-committed'; SET AUTOCOMMIT=0; SELECT * FROM t2; a connection c2; SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; +SET TRANSACTION_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (2); COMMIT; diff --git a/mysql-test/suite/innodb/r/innodb_bug14147491.result b/mysql-test/suite/innodb/r/innodb_bug14147491.result index 9d2b8e1d..77d53183 100644 --- a/mysql-test/suite/innodb/r/innodb_bug14147491.result +++ b/mysql-test/suite/innodb/r/innodb_bug14147491.result @@ -1,7 +1,6 @@ # Ensure that purge will not crash on the table after we corrupt it. SET GLOBAL innodb_fast_shutdown=0; # Create and populate the table to be corrupted -set global innodb_file_per_table=ON; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT) ENGINE=InnoDB; INSERT INTO t1 (b) VALUES ('corrupt me'); INSERT INTO t1 (b) VALUES ('corrupt me'); diff --git a/mysql-test/suite/innodb/r/innodb_bug30423.result b/mysql-test/suite/innodb/r/innodb_bug30423.result index 78604137..8f66ceda 100644 --- a/mysql-test/suite/innodb/r/innodb_bug30423.result +++ b/mysql-test/suite/innodb/r/innodb_bug30423.result @@ -54,7 +54,7 @@ ON orgs.org_id=sa_opportunities.org_id LEFT JOIN bug30243_2 contacts ON orgs.org_id=contacts.org_id ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE orgs index NULL org_id 4 NULL ROWS Using index +1 SIMPLE orgs ALL NULL NULL NULL NULL ROWS 1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id ROWS Using index 1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id ROWS Using index select @@innodb_stats_method; @@ -83,7 +83,7 @@ ON orgs.org_id=sa_opportunities.org_id LEFT JOIN bug30243_2 contacts ON orgs.org_id=contacts.org_id; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE orgs index NULL org_id 4 NULL ROWS Using index +1 SIMPLE orgs ALL NULL NULL NULL NULL ROWS 1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id ROWS Using index 1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id ROWS Using index SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL; diff --git a/mysql-test/suite/innodb/r/innodb_bug30919.result b/mysql-test/suite/innodb/r/innodb_bug30919.result index 0062df3f..42aa4ff3 100644 --- a/mysql-test/suite/innodb/r/innodb_bug30919.result +++ b/mysql-test/suite/innodb/r/innodb_bug30919.result @@ -35,10 +35,6 @@ FROM test.part_tbl; -- debug to show the problem SET del_count = del_count - 2; END WHILE; END| -Warnings: -Level Warning -Code 1287 -Message ' INTO FROM...' instead CALL test.proc_part(); internal_count del_count 999 1000 diff --git a/mysql-test/suite/innodb/r/innodb_bug40360.result b/mysql-test/suite/innodb/r/innodb_bug40360.result index ef4cf463..46456bde 100644 --- a/mysql-test/suite/innodb/r/innodb_bug40360.result +++ b/mysql-test/suite/innodb/r/innodb_bug40360.result @@ -1,4 +1,4 @@ -SET TX_ISOLATION='READ-COMMITTED'; +SET TRANSACTION_ISOLATION='READ-COMMITTED'; CREATE TABLE bug40360 (a INT) engine=innodb; INSERT INTO bug40360 VALUES (1); DROP TABLE bug40360; diff --git a/mysql-test/suite/innodb/r/innodb_bug49164.result b/mysql-test/suite/innodb/r/innodb_bug49164.result index 857cd285..8cac84b4 100644 --- a/mysql-test/suite/innodb/r/innodb_bug49164.result +++ b/mysql-test/suite/innodb/r/innodb_bug49164.result @@ -1,7 +1,7 @@ connect con1,localhost,root,,; connect con2,localhost,root,,; connection con1; -SET tx_isolation = 'READ-COMMITTED'; +SET transaction_isolation = 'READ-COMMITTED'; CREATE TABLE bug49164 (a INT, b BIGINT, c TINYINT, PRIMARY KEY (a, b)) ENGINE=InnoDB; insert into bug49164 values (1,1,1), (2,2,2), (3,3,3); @@ -21,7 +21,7 @@ a b c begin; update bug49164 set c=7; connection con2; -SET tx_isolation = 'READ-COMMITTED'; +SET transaction_isolation = 'READ-COMMITTED'; begin; select * from bug49164; a b c diff --git a/mysql-test/suite/innodb/r/innodb_bug51920.result b/mysql-test/suite/innodb/r/innodb_bug51920.result index 9bc35174..ddb9e29f 100644 --- a/mysql-test/suite/innodb/r/innodb_bug51920.result +++ b/mysql-test/suite/innodb/r/innodb_bug51920.result @@ -11,8 +11,6 @@ connection default; SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE INFO="UPDATE bug51920 SET i=2" INTO @thread_id; -Warnings: -Warning 1287 ' INTO FROM...' instead KILL @thread_id; connection con1; Got one of the listed errors diff --git a/mysql-test/suite/innodb/r/innodb_bug53592.result b/mysql-test/suite/innodb/r/innodb_bug53592.result index 9fea3dea..1d5b0f31 100644 --- a/mysql-test/suite/innodb/r/innodb_bug53592.result +++ b/mysql-test/suite/innodb/r/innodb_bug53592.result @@ -1,6 +1,4 @@ -set old_alter_table=0; -Warnings: -Warning 1287 '@@old_alter_table' is deprecated and will be removed in a future release. Please use '@@alter_algorithm' instead +set alter_algorithm=0; create table bug53592(a int) engine=innodb row_format=compact; alter table bug53592 add column b text charset utf8; alter table bug53592 add column c blob not null; @@ -14,9 +12,7 @@ check table bug53592; Table Op Msg_type Msg_text test.bug53592 check status OK drop table bug53592; -set old_alter_table=1; -Warnings: -Warning 1287 '@@old_alter_table' is deprecated and will be removed in a future release. Please use '@@alter_algorithm' instead +set alter_algorithm=1; create table bug53592(a int) engine=innodb row_format=compact; alter table bug53592 add column b text charset utf8; alter table bug53592 add column c blob not null; diff --git a/mysql-test/suite/innodb/r/innodb_bug53756.result b/mysql-test/suite/innodb/r/innodb_bug53756.result index daa6113f..100d3715 100644 --- a/mysql-test/suite/innodb/r/innodb_bug53756.result +++ b/mysql-test/suite/innodb/r/innodb_bug53756.result @@ -9,8 +9,8 @@ COMMIT; # Start a transaction in the default connection for isolation. START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED SELECT * FROM bug_53756; pk c1 @@ -20,38 +20,38 @@ pk c1 4 44 connect con1,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED DELETE FROM bug_53756 WHERE pk=1; connect con2,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED DELETE FROM bug_53756 WHERE pk=2; connect con3,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED UPDATE bug_53756 SET c1=77 WHERE pk=3; connect con4,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED UPDATE bug_53756 SET c1=88 WHERE pk=4; connect con5,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED INSERT INTO bug_53756 VALUES(5, 55); connect con6,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED INSERT INTO bug_53756 VALUES(6, 66); connection con1; diff --git a/mysql-test/suite/innodb/r/innodb_bug56947.result b/mysql-test/suite/innodb/r/innodb_bug56947.result index aa922776..a4ed3bfc 100644 --- a/mysql-test/suite/innodb/r/innodb_bug56947.result +++ b/mysql-test/suite/innodb/r/innodb_bug56947.result @@ -1,4 +1,7 @@ -SET GLOBAL innodb_file_per_table=0; +SET @save_fpt=@@GLOBAL.innodb_file_per_table; +SET GLOBAL innodb_file_per_table=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release create table bug56947(a int not null) engine = innodb; SET @saved_dbug = @@SESSION.debug_dbug; SET DEBUG_DBUG='+d,ib_rebuild_cannot_rename'; @@ -8,5 +11,7 @@ check table bug56947; Table Op Msg_type Msg_text test.bug56947 check status OK drop table bug56947; -SET @@global.innodb_file_per_table=DEFAULT; +SET GLOBAL innodb_file_per_table=@save_fpt; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SET debug_dbug= @saved_dbug; diff --git a/mysql-test/suite/innodb/r/innodb_bug57252.result b/mysql-test/suite/innodb/r/innodb_bug57252.result index 66183c2c..18e47900 100644 --- a/mysql-test/suite/innodb/r/innodb_bug57252.result +++ b/mysql-test/suite/innodb/r/innodb_bug57252.result @@ -1,5 +1,5 @@ cardinality -2 +1 Table Op Msg_type Msg_text test.bug57252 analyze status Engine-independent statistics collected test.bug57252 analyze status OK diff --git a/mysql-test/suite/innodb/r/innodb_bug59733.result b/mysql-test/suite/innodb/r/innodb_bug59733.result deleted file mode 100644 index c962cdfd..00000000 --- a/mysql-test/suite/innodb/r/innodb_bug59733.result +++ /dev/null @@ -1,18 +0,0 @@ -CREATE TABLE bug59733(a INT AUTO_INCREMENT PRIMARY KEY,b CHAR(1))ENGINE=InnoDB; -INSERT INTO bug59733 VALUES(0,'x'); -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -INSERT INTO bug59733 SELECT 0,b FROM bug59733; -CREATE INDEX b ON bug59733 (b); -DELETE FROM bug59733 WHERE (a%100)=0; -DROP INDEX b ON bug59733; -CREATE INDEX b ON bug59733 (b); -DROP TABLE bug59733; diff --git a/mysql-test/suite/innodb/r/innodb_bug68148.result b/mysql-test/suite/innodb/r/innodb_bug68148.result index 25bf7f58..1c79f881 100644 --- a/mysql-test/suite/innodb/r/innodb_bug68148.result +++ b/mysql-test/suite/innodb/r/innodb_bug68148.result @@ -1,4 +1,3 @@ -set global innodb_file_per_table=1; CREATE TABLE ref_table1 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; CREATE TABLE ref_table2 (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB; CREATE TABLE `main` ( diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index.result index ec7ce044..060e9008 100644 --- a/mysql-test/suite/innodb/r/innodb_bulk_create_index.result +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index.result @@ -532,7 +532,6 @@ END| SELECT @@innodb_fill_factor; @@innodb_fill_factor 100 -SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1( class INT, id INT, @@ -686,7 +685,6 @@ test.t1 check status OK SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; CHAR_LENGTH(b) DROP TABLE t1; -SET GLOBAL innodb_file_per_table=default; DROP PROCEDURE populate_t1; SET GLOBAL innodb_fill_factor=10; CREATE PROCEDURE populate_t1(load_even INT) diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result index 295a9f1b..debb5d0f 100644 --- a/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index_debug.result @@ -407,7 +407,6 @@ SET i = i + 1; END WHILE; COMMIT; END| -SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1( class INT, id INT, @@ -421,7 +420,6 @@ affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 RENAME TABLE t1 TO t0; # Test Blob -SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1( a INT PRIMARY KEY, b TEXT, @@ -467,7 +465,6 @@ class id title SELECT * FROM t1 WHERE title = 'a10010'; class id title DROP TABLE t1; -SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1( class INT, id INT, @@ -501,7 +498,6 @@ SELECT * FROM t1 WHERE title = 'a10010'; class id title DROP TABLE t1; # Test Blob -SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1( a INT PRIMARY KEY, b TEXT, diff --git a/mysql-test/suite/innodb/r/innodb_bulk_create_index_small.result b/mysql-test/suite/innodb/r/innodb_bulk_create_index_small.result index b48207d4..bdc40d1a 100644 --- a/mysql-test/suite/innodb/r/innodb_bulk_create_index_small.result +++ b/mysql-test/suite/innodb/r/innodb_bulk_create_index_small.result @@ -72,8 +72,6 @@ test.t1 check status OK SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; CHAR_LENGTH(b) DROP TABLE t1; -SET GLOBAL innodb_file_per_table=default; -SET GLOBAL innodb_file_per_table=1; CREATE TABLE t1( class INT, id INT, @@ -135,5 +133,4 @@ test.t1 check status OK SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; CHAR_LENGTH(b) DROP TABLE t1; -SET GLOBAL innodb_file_per_table=default; DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/r/innodb_defrag_binlog.result b/mysql-test/suite/innodb/r/innodb_defrag_binlog.result deleted file mode 100644 index b97061cc..00000000 --- a/mysql-test/suite/innodb/r/innodb_defrag_binlog.result +++ /dev/null @@ -1,31 +0,0 @@ -include/master-slave.inc -[connection master] -drop table if exists t1; -create table t1(a int not null primary key auto_increment, b varchar(256), key second(b)) engine=innodb; -insert into t1 values (1, REPEAT("a", 256)); -insert into t1 values (2, REPEAT("a", 256)); -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -drop table t1; -include/show_binlog_events.inc -Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */ -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; create table t1(a int not null primary key auto_increment, b varchar(256), key second(b)) engine=innodb -master-bin.000001 # Gtid # # BEGIN GTID #-#-# -master-bin.000001 # Annotate_rows # # insert into t1 values (1, REPEAT("a", 256)) -master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Gtid # # BEGIN GTID #-#-# -master-bin.000001 # Annotate_rows # # insert into t1 values (2, REPEAT("a", 256)) -master-bin.000001 # Table_map # # table_id: # (test.t1) -master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F -master-bin.000001 # Xid # # COMMIT /* XID */ -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; optimize table t1 -master-bin.000001 # Gtid # # GTID #-#-# -master-bin.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */ -include/rpl_end.inc diff --git a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result b/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result deleted file mode 100644 index 07c96e76..00000000 --- a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result +++ /dev/null @@ -1,102 +0,0 @@ -SET @n_pages= @@GLOBAL.innodb_defragment_n_pages; -SET @accuracy= @@GLOBAL.innodb_defragment_stats_accuracy; -SET @sp= @@GLOBAL.innodb_stats_persistent; -SET GLOBAL innodb_stats_persistent = 0; -set global innodb_defragment_stats_accuracy = 80; -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b VARCHAR(256), -c INT, -g GEOMETRY NOT NULL, -t VARCHAR(256), -KEY second(a, b), -KEY third(c), -SPATIAL gk(g), -FULLTEXT INDEX fti(t)) ENGINE=INNODB; -connect con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connect con2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connect con3,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connect con4,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connection default; -SET @@global.innodb_defragment_n_pages = 20; -CREATE TEMPORARY TABLE tt (a INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; -INSERT INTO tt SELECT 0 FROM seq_1_to_180; -INSERT INTO tt SELECT 5 FROM seq_1_to_160; -INSERT INTO tt SELECT 1 FROM seq_1_to_1000; -OPTIMIZE TABLE tt; -Table Op Msg_type Msg_text -test.tt optimize note Table does not support optimize, doing recreate + analyze instead -test.tt optimize status OK -select count(*) from t1; -count(*) -20000 -select count(*) from t1 force index (second); -count(*) -20000 -select count(*) from t1 force index (third); -count(*) -20000 -select count(*) from t1; -count(*) -15800 -select count(*) from t1 force index (second); -count(*) -15800 -select count(*) from t1 force index (third); -count(*) -15800 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -0 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 -connection con1; -optimize table t1;; -connection default; -INSERT INTO t1 VALUES (400000, REPEAT('A', 256),300000, Point(1,1),'More like a test but different.');; -connection con2; -INSERT INTO t1 VALUES (500000, REPEAT('A', 256),400000, Point(1,1),'Totally different text book.');; -connection con3; -DELETE FROM t1 where a between 1 and 100;; -connection con4; -UPDATE t1 SET c = c + 1 where c between 2000 and 8000;; -connection con1; -connection con2; -connection con3; -connection con4; -connection default; -disconnect con1; -disconnect con2; -disconnect con3; -disconnect con4; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -check table t1 extended; -Table Op Msg_type Msg_text -test.t1 check status OK -select count(*) from t1; -count(*) -15723 -select count(*) from t1 force index (second); -count(*) -15723 -select count(*) from t1 force index (third); -count(*) -15723 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 -drop table t1; -SET GLOBAL innodb_defragment_n_pages = @n_pages; -SET GLOBAL innodb_defragment_stats_accuracy = @accuracy; -SET GLOBAL innodb_stats_persistent = @sp; diff --git a/mysql-test/suite/innodb/r/innodb_defrag_stats.result b/mysql-test/suite/innodb/r/innodb_defrag_stats.result deleted file mode 100644 index c6fd7006..00000000 --- a/mysql-test/suite/innodb/r/innodb_defrag_stats.result +++ /dev/null @@ -1,133 +0,0 @@ -SET GLOBAL innodb_defragment_stats_accuracy = 20; -DELETE FROM mysql.innodb_index_stats; -# Create table. -CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), -KEY SECOND(a, b)) ENGINE=INNODB STATS_PERSISTENT=0; -INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) -FROM seq_1_to_1024; -# Not enough page splits to trigger persistent stats write yet. -SELECT * FROM mysql.innodb_index_stats; -database_name table_name index_name last_update stat_name stat_value sample_size stat_description -INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) -FROM seq_1025_to_1433; -BEGIN; -INSERT INTO t1 SELECT 100*20+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*19+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*18+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*17+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*16+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*15+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*14+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*13+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*12+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*11+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*10+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*9+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*8+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*7+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*6+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*5+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*4+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*3+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*2+seq, REPEAT('A', 256) -FROM seq_70_to_99; -INSERT INTO t1 SELECT 100*1+seq, REPEAT('A', 256) -FROM seq_70_to_99; -ROLLBACK; -SELECT @@GLOBAL.innodb_force_recovery<2 "have background defragmentation"; -have background defragmentation -1 -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t1 PRIMARY n_leaf_pages_defrag -t1 PRIMARY n_leaf_pages_reserved -t1 PRIMARY n_page_split -t1 SECOND n_leaf_pages_defrag -t1 SECOND n_leaf_pages_reserved -t1 SECOND n_page_split -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t1 PRIMARY n_leaf_pages_defrag -t1 PRIMARY n_leaf_pages_reserved -t1 PRIMARY n_page_split -t1 PRIMARY n_pages_freed -t1 SECOND n_leaf_pages_defrag -t1 SECOND n_leaf_pages_reserved -t1 SECOND n_page_split -t1 SECOND n_pages_freed -set global innodb_defragment_stats_accuracy = 40; -INSERT INTO t1 (b) SELECT b from t1; -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t1 PRIMARY n_leaf_pages_defrag -t1 PRIMARY n_leaf_pages_reserved -t1 PRIMARY n_page_split -t1 PRIMARY n_pages_freed -t1 SECOND n_leaf_pages_defrag -t1 SECOND n_leaf_pages_reserved -t1 SECOND n_page_split -t1 SECOND n_pages_freed -INSERT INTO t1 (b) SELECT b from t1; -SELECT stat_name FROM mysql.innodb_index_stats WHERE table_name='t1'; -stat_name -n_leaf_pages_defrag -n_leaf_pages_defrag -n_leaf_pages_reserved -n_leaf_pages_reserved -n_page_split -n_page_split -n_pages_freed -n_pages_freed -# Table rename should cause stats rename. -rename table t1 to t2; -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t2 PRIMARY n_leaf_pages_defrag -t2 PRIMARY n_leaf_pages_reserved -t2 PRIMARY n_page_split -t2 PRIMARY n_pages_freed -t2 SECOND n_leaf_pages_defrag -t2 SECOND n_leaf_pages_reserved -t2 SECOND n_page_split -t2 SECOND n_pages_freed -drop index SECOND on t2; -# -# MDEV-26636: Statistics must not be written for temporary tables -# -SET GLOBAL innodb_defragment_stats_accuracy = 1; -CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL) -ENGINE=InnoDB; -INSERT INTO t SELECT seq, '' FROM seq_1_to_100; -# restart -SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; -table_name index_name stat_name -t2 PRIMARY n_leaf_pages_defrag -t2 PRIMARY n_leaf_pages_reserved -t2 PRIMARY n_page_split -t2 PRIMARY n_pages_freed -# Clean up -ALTER TABLE t2 STATS_PERSISTENT=1; -DROP TABLE t2; -SELECT * FROM mysql.innodb_index_stats; -database_name table_name index_name last_update stat_name stat_value sample_size stat_description diff --git a/mysql-test/suite/innodb/r/innodb_defrag_stats_many_tables.result b/mysql-test/suite/innodb/r/innodb_defrag_stats_many_tables.result deleted file mode 100644 index e668c38e..00000000 --- a/mysql-test/suite/innodb/r/innodb_defrag_stats_many_tables.result +++ /dev/null @@ -1,38 +0,0 @@ -DROP TABLE if exists t1; -SET @start_table_definition_cache = @@global.table_definition_cache; -SET @@global.table_definition_cache = 400; -SET @start_flush_log_at_trx_commit = @@global.innodb_flush_log_at_trx_commit; -SET @@global.innodb_flush_log_at_trx_commit=2; -SET @start_innodb_defragment_stats_accuracy = @@global.innodb_defragment_stats_accuracy; -SET @@global.innodb_defragment_stats_accuracy = 80; -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; -INSERT INTO t1 VALUES(1, REPEAT('A', 256)); -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -INSERT INTO t1 (b) SELECT b from t1; -select stat_value > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name = 'n_page_split'; -stat_value > 0 -Create 505 table to overflow the table cache. -Sleep for a while to make sure t1 is evicted. -select sleep(15); -sleep(15) -0 -Reload t1 to get defrag stats from persistent storage -INSERT INTO t1 (b) SELECT b from t1; -make sure the stats thread will wake up and do the write even if there's a race condition between set and reset. -select sleep(15); -sleep(15) -0 -select stat_value > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name = 'n_page_split'; -stat_value > 0 -SET @@global.innodb_defragment_stats_accuracy = @start_innodb_defragment_stats_accuracy; -SET @@global.table_definition_cache = @start_table_definition_cache; -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_defragment.result b/mysql-test/suite/innodb/r/innodb_defragment.result deleted file mode 100644 index 533a39ee..00000000 --- a/mysql-test/suite/innodb/r/innodb_defragment.result +++ /dev/null @@ -1,133 +0,0 @@ -set global innodb_defragment_stats_accuracy = 80; -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -INSERT INTO t1 VALUES (100000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (200000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (300000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (400000, REPEAT('A', 256)); -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -create procedure defragment() -begin -set @i = 0; -repeat -set @i = @i + 1; -optimize table t1; -until @i = 3 end repeat; -end // -select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) -0 -select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) -2 -select count(stat_value) from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) -2 -select count(*) from t1; -count(*) -10004 -connect con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -connection con1; -call defragment(); -connection default; -connection con1; -connection default; -disconnect con1; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -# restart -select count(*) from t1; -count(*) -7904 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -0 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 -select count(*) from t1 force index (second); -count(*) -7904 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and index_name = 'second' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and index_name = 'second' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and index_name = 'second' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -SET @@global.innodb_defragment_n_pages = 3; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -# restart -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) < 3 -1 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) < 3 -1 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) < 3 -1 -select count(*) from t1; -count(*) -6904 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) < 3 -1 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) < 3 -1 -select count(stat_value) < 3 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) < 3 -1 -select count(*) from t1 force index (second); -count(*) -6904 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and index_name = 'second' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and index_name = 'second' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and index_name = 'second' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -SET @@global.innodb_defragment_n_pages = 10; -optimize table t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -# restart -select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) > 1 -1 -select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 1 -1 -select count(stat_value) > 1 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 1 -1 -select count(*) from t1 force index (second); -count(*) -6904 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and index_name = 'second' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and index_name = 'second' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and index_name = 'second' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -DROP PROCEDURE defragment; -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_defragment_small.result b/mysql-test/suite/innodb/r/innodb_defragment_small.result deleted file mode 100644 index fcb3bf2e..00000000 --- a/mysql-test/suite/innodb/r/innodb_defragment_small.result +++ /dev/null @@ -1,40 +0,0 @@ -SET @innodb_defragment_orig=@@GLOBAL.innodb_defragment; -SET @innodb_optimize_fulltext_orig=@@GLOBAL.innodb_optimize_fulltext_only; -SET GLOBAL innodb_defragment = 1; -SET GLOBAL innodb_optimize_fulltext_only = 0; -# -# MDEV-12198 innodb_defragment=1 crashes server on -# OPTIMIZE TABLE when FULLTEXT index exists -# -CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256), -KEY(a, b), FULLTEXT KEY(b)) ENGINE=INNODB; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -INSERT INTO t1 VALUES (100000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (200000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (300000, REPEAT('A', 256)); -INSERT INTO t1 VALUES (400000, REPEAT('A', 256)); -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -# -# MDEV-15824 innodb_defragment=ON trumps -# innodb_optimize_fulltext_only=ON in OPTIMIZE TABLE -# -SET GLOBAL innodb_optimize_fulltext_only = 1; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -SET GLOBAL innodb_defragment = 0; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -DROP TABLE t1; -CREATE TABLE t1 (c POINT PRIMARY KEY, SPATIAL INDEX(c)) ENGINE=InnoDB; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -DROP TABLE t1; -SET GLOBAL innodb_defragment = @innodb_defragment_orig; -SET GLOBAL innodb_optimize_fulltext_only = @innodb_optimize_fulltext_orig; diff --git a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result index 797c30d7..52b85cf6 100644 --- a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result @@ -87,9 +87,9 @@ drop table t1, t2; # handler::unlock_row() in InnoDB does nothing. # Thus in order to reproduce the condition that led to the # warning, one needs to relax isolation by either -# setting a weaker tx_isolation value, or by turning on +# setting a weaker transaction_isolation value, or by turning on # the unsafe replication switch. -set @@session.tx_isolation="read-committed"; +set @@session.transaction_isolation="read-committed"; # Prepare data. We need a table with a unique index, # for join_read_key to be used. The other column # allows to control what passes WHERE clause filter. @@ -354,7 +354,7 @@ ERROR HY000: Lock wait timeout exceeded; try restarting transaction connection default; commit; disconnect con1; -set @@session.tx_isolation=default; +set @@session.transaction_isolation=default; drop table t1; # # End of 5.1 tests diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 9d8ead7a..99e42e67 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -191,8 +191,8 @@ min(7) 7 explain select min(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1i ALL NULL NULL NULL NULL 0 -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select min(7) from t2i join t1i; min(7) NULL @@ -207,8 +207,8 @@ max(7) 7 explain select max(7) from t2i join t1i; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1i ALL NULL NULL NULL NULL 0 -1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) +1 SIMPLE t2i ALL NULL NULL NULL NULL 1 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) select max(7) from t2i join t1i; max(7) NULL @@ -239,7 +239,7 @@ select 1, max(1) from t1i where 1=99; explain select count(*), min(7), max(7) from t1m, t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1m system NULL NULL NULL NULL 0 Const row not found -1 SIMPLE t1i ALL NULL NULL NULL NULL 0 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 select count(*), min(7), max(7) from t1m, t1i; count(*) min(7) max(7) 0 NULL NULL @@ -253,7 +253,7 @@ count(*) min(7) max(7) explain select count(*), min(7), max(7) from t2m, t1i; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2m system NULL NULL NULL NULL 1 -1 SIMPLE t1i ALL NULL NULL NULL NULL 0 +1 SIMPLE t1i ALL NULL NULL NULL NULL 1 select count(*), min(7), max(7) from t2m, t1i; count(*) min(7) max(7) 0 NULL NULL @@ -346,10 +346,10 @@ insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); alter table t1 drop primary key, add primary key (f2, f1); explain select distinct f1 a, f1 b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary explain select distinct f1, f2 from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 drop table t1; CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), INDEX (name)); @@ -396,7 +396,7 @@ test.t1 analyze status OK flush tables; EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref name name 22 const 2 Using where; Using index +1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept rs5 cs10 @@ -405,7 +405,7 @@ DELETE FROM t1; # Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746). EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref name name 22 const # Using where; Using index +1 SIMPLE t1 range name name 44 NULL # Using where; Using index for group-by SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; name dept DROP TABLE t1; @@ -1185,7 +1185,7 @@ INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL b 5 NULL 128 +1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort @@ -1300,13 +1300,13 @@ EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; id 1 select_type SIMPLE table t1 -type range +type index possible_keys bkey -key bkey -key_len 5 +key PRIMARY +key_len 4 ref NULL rows 32 -Extra Using where; Using index; Using filesort +Extra Using where SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; a b 1 2 @@ -1395,13 +1395,13 @@ EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a; id 1 select_type SIMPLE table t2 -type ref +type index possible_keys bkey -key bkey -key_len 5 -ref const +key PRIMARY +key_len 4 +ref NULL rows 16 -Extra Using where; Using index; Using filesort +Extra Using where SELECT * FROM t2 WHERE b=1 ORDER BY a; a b c 1 1 1 @@ -1617,7 +1617,7 @@ c b d 3 2 40 EXPLAIN SELECT c,b FROM t1 GROUP BY c,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL c 8 NULL 3 Using index +1 SIMPLE t1 index NULL c 8 NULL 3 SELECT c,b FROM t1 GROUP BY c,b; c b 1 1 @@ -1775,7 +1775,7 @@ INSERT INTO t1 VALUES (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort +1 SIMPLE t1 ref_or_null idx idx 5 const 3 Using index condition; Using filesort SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; id type d 191 member 1 @@ -1785,13 +1785,16 @@ DROP TABLE t1; set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; set global innodb_autoextend_increment=8; set global innodb_autoextend_increment=@my_innodb_autoextend_increment; +# +# Bug #37830: ORDER BY ASC/DESC - no difference +# CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b)) ENGINE=InnoDB; -INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); +INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1), (100,2,2); INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1; EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range t1_b t1_b 5 NULL 8 Using where +1 SIMPLE t1 ref t1_b t1_b 5 const 8 Using where SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; a b c 8 1 1 @@ -2286,10 +2289,10 @@ drop table t1, t2; # SET SESSION BINLOG_FORMAT=STATEMENT; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation; +select @@session.sql_log_bin, @@session.binlog_format, @@session.transaction_isolation; @@session.sql_log_bin 1 @@session.binlog_format STATEMENT -@@session.tx_isolation READ-COMMITTED +@@session.transaction_isolation READ-COMMITTED CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; INSERT INTO t1 VALUES(1); DROP TABLE t1; @@ -2330,24 +2333,24 @@ EXPLAIN SELECT c FROM bar WHERE b>2;; id 1 select_type SIMPLE table bar -type ALL +type range possible_keys b -key NULL -key_len NULL +key b +key_len 5 ref NULL -rows 6 -Extra Using where +rows 5 +Extra Using index condition EXPLAIN SELECT c FROM foo WHERE b>2;; id 1 select_type SIMPLE table foo -type ALL +type range possible_keys b -key NULL -key_len NULL +key b +key_len 5 ref NULL -rows 6 -Extra Using where +rows 5 +Extra Using index condition EXPLAIN SELECT c FROM foo2 WHERE b>2;; id 1 select_type SIMPLE @@ -2970,7 +2973,7 @@ NULL 75 EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0 WHERE t2.id=75 AND t1.id IS NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t1 const PRIMARY NULL NULL NULL 0 Impossible ON condition 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1,t2; # @@ -3066,7 +3069,7 @@ f1 f2 f3 f4 EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE ORDER BY f1 DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range f2,f4 f4 1 NULL 22 Using where +1 SIMPLE t1 index f2,f4 PRIMARY 4 NULL 5 Using where DROP TABLE t1; # # Bug#54117 crash in thr_multi_unlock, temporary table @@ -3114,8 +3117,8 @@ select_type SIMPLE table t1 type index possible_keys NULL -key PRIMARY -key_len 8 +key b +key_len 13 ref NULL rows 3 Extra Using index @@ -3127,8 +3130,8 @@ select_type SIMPLE table t1 type index possible_keys NULL -key PRIMARY -key_len 8 +key b +key_len 18 ref NULL rows 3 Extra Using index @@ -3291,7 +3294,7 @@ SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t2 index NULL PRIMARY 4 NULL 1 Using index; FirstMatch(t1); Using join buffer (incremental, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 1 FirstMatch(t1); Using join buffer (incremental, BNL join) SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2); b 1 diff --git a/mysql-test/suite/innodb/r/innodb_scrub.result b/mysql-test/suite/innodb/r/innodb_scrub.result index 475d4e74..eba4984f 100644 --- a/mysql-test/suite/innodb/r/innodb_scrub.result +++ b/mysql-test/suite/innodb/r/innodb_scrub.result @@ -18,9 +18,13 @@ SET @scrub= @@GLOBAL.innodb_immediate_scrub_data_uncompressed; SET GLOBAL innodb_immediate_scrub_data_uncompressed= 1; SET @fpt=@@GLOBAL.innodb_file_per_table; SET GLOBAL innodb_file_per_table=0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t ENGINE=InnoDB AS SELECT 1; DROP TABLE t; SET GLOBAL innodb_file_per_table=@fpt; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TEMPORARY TABLE tmp ENGINE=InnoDB AS SELECT 1; DROP TABLE tmp; SET GLOBAL INNODB_IMMEDIATE_SCRUB_DATA_UNCOMPRESSED= @scrub; diff --git a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result index 94e40ee5..4fa95934 100644 --- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result +++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result @@ -114,12 +114,8 @@ buffer_LRU_unzip_search_num_scan buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL N buffer_LRU_unzip_search_scanned_per_call buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 set_member Page scanned per single LRU unzip search buffer_page_read_index_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Index Leaf Pages read buffer_page_read_index_non_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Index Non-leaf Pages read -buffer_page_read_index_ibuf_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Insert Buffer Index Leaf Pages read -buffer_page_read_index_ibuf_non_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Insert Buffer Index Non-Leaf Pages read buffer_page_read_undo_log buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Undo Log Pages read buffer_page_read_index_inode buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Index Inode Pages read -buffer_page_read_ibuf_free_list buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Insert Buffer Free List Pages read -buffer_page_read_ibuf_bitmap buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Insert Buffer Bitmap Pages read buffer_page_read_system_page buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of System Pages read buffer_page_read_trx_system buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Transaction System Pages read buffer_page_read_fsp_hdr buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of File Space Header Pages read @@ -130,12 +126,8 @@ buffer_page_read_zblob2 buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NU buffer_page_read_other buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of other/unknown (old version of InnoDB) Pages read buffer_page_written_index_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Index Leaf Pages written buffer_page_written_index_non_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Index Non-leaf Pages written -buffer_page_written_index_ibuf_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Insert Buffer Index Leaf Pages written -buffer_page_written_index_ibuf_non_leaf buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Insert Buffer Index Non-Leaf Pages written buffer_page_written_undo_log buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Undo Log Pages written buffer_page_written_index_inode buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Index Inode Pages written -buffer_page_written_ibuf_free_list buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Insert Buffer Free List Pages written -buffer_page_written_ibuf_bitmap buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Insert Buffer Bitmap Pages written buffer_page_written_system_page buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of System Pages written buffer_page_written_trx_system buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Transaction System Pages written buffer_page_written_fsp_hdr buffer_page_io 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of File Space Header Pages written @@ -203,14 +195,6 @@ adaptive_hash_rows_removed adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL adaptive_hash_rows_deleted_no_hash_entry adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of rows deleted that did not have corresponding Adaptive Hash Index entries adaptive_hash_rows_updated adaptive_hash_index 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of Adaptive Hash Index rows updated file_num_open_files file_system 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 value Number of files currently open (innodb_num_open_files) -ibuf_merges_insert change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Number of inserted records merged by change buffering -ibuf_merges_delete_mark change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Number of deleted records merged by change buffering -ibuf_merges_delete change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Number of purge records merged by change buffering -ibuf_merges_discard_insert change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Number of insert merged operations discarded -ibuf_merges_discard_delete_mark change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Number of deleted merged operations discarded -ibuf_merges_discard_delete change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Number of purge merged operations discarded -ibuf_merges change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Number of change buffer merges -ibuf_size change_buffer 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Change buffer size in pages innodb_master_thread_sleeps server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of times (seconds) master thread sleeps innodb_activity_count server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 status_counter Current server activity count innodb_master_active_loops server 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL 0 counter Number of times master thread performs its tasks when server is active diff --git a/mysql-test/suite/innodb/r/innodb_stats.result b/mysql-test/suite/innodb/r/innodb_stats.result index b300af76..cb5247f0 100644 --- a/mysql-test/suite/innodb/r/innodb_stats.result +++ b/mysql-test/suite/innodb/r/innodb_stats.result @@ -151,7 +151,7 @@ INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A -CARDINALITY 2 +CARDINALITY 1 SUB_PART NULL PACKED NULL NULLABLE YES @@ -204,7 +204,7 @@ INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A -CARDINALITY 3 +CARDINALITY 1 SUB_PART NULL PACKED NULL NULLABLE YES @@ -257,7 +257,7 @@ INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A -CARDINALITY 2 +CARDINALITY 1 SUB_PART NULL PACKED NULL NULLABLE YES @@ -522,7 +522,7 @@ INDEX_NAME a_key SEQ_IN_INDEX 1 COLUMN_NAME a COLLATION A -CARDINALITY 10 +CARDINALITY 5 SUB_PART NULL PACKED NULL NULLABLE YES diff --git a/mysql-test/suite/innodb/r/innodb_stats_fetch.result b/mysql-test/suite/innodb/r/innodb_stats_fetch.result index cb205b18..ccc8d205 100644 --- a/mysql-test/suite/innodb/r/innodb_stats_fetch.result +++ b/mysql-test/suite/innodb/r/innodb_stats_fetch.result @@ -131,16 +131,16 @@ FROM information_schema.statistics WHERE table_name = 'test_ps_fetch' ORDER BY index_name, seq_in_index; seq_in_index 1 column_name c -cardinality 6 +cardinality 3 seq_in_index 2 column_name d -cardinality 22 +cardinality 11 seq_in_index 1 column_name a -cardinality 40 +cardinality 20 seq_in_index 2 column_name b -cardinality 200 +cardinality 90 SELECT table_rows, avg_row_length, max_data_length, index_length FROM information_schema.tables WHERE table_name = 'test_ps_fetch'; @@ -177,7 +177,7 @@ DROP TABLE t1; # # MDEV-33462 Disallow LOCK=NONE operation on statistics table # -ALTER TABLE mysql.innodb_table_stats FORCE, LOCK=NONE; +ALTER TABLE mysql.innodb_table_stats FORCE, LOCK=NONE, ALGORITHM=INPLACE; ERROR 0A000: LOCK=NONE is not supported. Reason: innodb_table_stats. Try LOCK=SHARED -ALTER TABLE mysql.innodb_index_stats FORCE, LOCK=NONE; +ALTER TABLE mysql.innodb_index_stats FORCE, LOCK=NONE, ALGORITHM=INPLACE; ERROR 0A000: LOCK=NONE is not supported. Reason: innodb_index_stats. Try LOCK=SHARED diff --git a/mysql-test/suite/innodb/r/innodb_stats_fetch_corrupted.result b/mysql-test/suite/innodb/r/innodb_stats_fetch_corrupted.result index 1f847130..b1e6bb7f 100644 --- a/mysql-test/suite/innodb/r/innodb_stats_fetch_corrupted.result +++ b/mysql-test/suite/innodb/r/innodb_stats_fetch_corrupted.result @@ -24,7 +24,7 @@ FROM information_schema.statistics WHERE table_name = 'test_ps_fetch_corrupted' ORDER BY index_name, seq_in_index; seq_in_index 1 column_name a -cardinality 0 +cardinality 1 SELECT table_rows, avg_row_length, max_data_length, index_length FROM information_schema.tables WHERE table_name = 'test_ps_fetch_corrupted'; table_rows 0 @@ -38,7 +38,7 @@ FROM information_schema.statistics WHERE table_name = 'test_ps_fetch_corrupted' ORDER BY index_name, seq_in_index; seq_in_index 1 column_name a -cardinality 0 +cardinality 1 SELECT table_rows, avg_row_length, max_data_length, index_length FROM information_schema.tables WHERE table_name = 'test_ps_fetch_corrupted'; table_rows 0 diff --git a/mysql-test/suite/innodb/r/innodb_stats_fetch_nonexistent.result b/mysql-test/suite/innodb/r/innodb_stats_fetch_nonexistent.result index 91bb2bf3..a6627417 100644 --- a/mysql-test/suite/innodb/r/innodb_stats_fetch_nonexistent.result +++ b/mysql-test/suite/innodb/r/innodb_stats_fetch_nonexistent.result @@ -22,7 +22,7 @@ FROM information_schema.statistics WHERE table_name = 'test_ps_fetch_nonexistent ORDER BY index_name, seq_in_index; seq_in_index 1 column_name a -cardinality 0 +cardinality 1 SELECT table_rows, avg_row_length, max_data_length, index_length FROM information_schema.tables WHERE table_name = 'test_ps_fetch_nonexistent'; table_rows 0 diff --git a/mysql-test/suite/innodb/r/innodb_status_variables.result b/mysql-test/suite/innodb/r/innodb_status_variables.result index 3a86271c..2eeed4f1 100644 --- a/mysql-test/suite/innodb/r/innodb_status_variables.result +++ b/mysql-test/suite/innodb/r/innodb_status_variables.result @@ -45,16 +45,6 @@ INNODB_DBLWR_PAGES_WRITTEN INNODB_DBLWR_WRITES INNODB_DEADLOCKS INNODB_HISTORY_LIST_LENGTH -INNODB_IBUF_DISCARDED_DELETE_MARKS -INNODB_IBUF_DISCARDED_DELETES -INNODB_IBUF_DISCARDED_INSERTS -INNODB_IBUF_FREE_LIST -INNODB_IBUF_MERGED_DELETE_MARKS -INNODB_IBUF_MERGED_DELETES -INNODB_IBUF_MERGED_INSERTS -INNODB_IBUF_MERGES -INNODB_IBUF_SEGMENT_SIZE -INNODB_IBUF_SIZE INNODB_LOG_WAITS INNODB_LOG_WRITE_REQUESTS INNODB_LOG_WRITES @@ -92,9 +82,6 @@ INNODB_HAVE_LZMA INNODB_HAVE_BZIP2 INNODB_HAVE_SNAPPY INNODB_HAVE_PUNCH_HOLE -INNODB_DEFRAGMENT_COMPRESSION_FAILURES -INNODB_DEFRAGMENT_FAILURES -INNODB_DEFRAGMENT_COUNT INNODB_INSTANT_ALTER_COLUMN INNODB_ONLINEDDL_ROWLOG_ROWS INNODB_ONLINEDDL_ROWLOG_PCT_USED diff --git a/mysql-test/suite/innodb/r/insert_debug.result b/mysql-test/suite/innodb/r/insert_debug.result index 3ac9df58..cafab4aa 100644 --- a/mysql-test/suite/innodb/r/insert_debug.result +++ b/mysql-test/suite/innodb/r/insert_debug.result @@ -2,12 +2,10 @@ # Bug#19904003 INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG=1 # CAUSES INFINITE PAGE SPLIT # -SET GLOBAL innodb_change_buffering_debug=1; SET GLOBAL innodb_limit_optimistic_insert_debug=1; CREATE TABLE t1(c1 INT PRIMARY KEY) ENGINE=InnoDB PARTITION BY HASH (c1) PARTITIONS 15; DROP TABLE t1; -SET GLOBAL innodb_change_buffering_debug=0; SET GLOBAL innodb_limit_optimistic_insert_debug=0; # # Bug#25082593 FOREIGN KEY VALIDATION DOESN'T NEED diff --git a/mysql-test/suite/innodb/r/insert_into_empty.result b/mysql-test/suite/innodb/r/insert_into_empty.result index 78d93494..d4ffaf89 100644 --- a/mysql-test/suite/innodb/r/insert_into_empty.result +++ b/mysql-test/suite/innodb/r/insert_into_empty.result @@ -41,7 +41,7 @@ SET GLOBAL innodb_adaptive_hash_index = @save_ahi; # CREATE TEMPORARY TABLE t (c INT) ENGINE=InnoDB; CREATE TEMPORARY TABLE t2 (c INT) ENGINE=InnoDB; -SET tx_read_only=1; +SET transaction_read_only=1; BEGIN; INSERT INTO t2 VALUES(0); INSERT INTO t VALUES(0); @@ -53,7 +53,7 @@ COMMIT; INSERT INTO t VALUES(0); DROP TEMPORARY TABLE t,t2; ERROR 25006: Cannot execute statement in a READ ONLY transaction -SET tx_read_only=0; +SET transaction_read_only=0; DROP TEMPORARY TABLE t,t2; # # MDEV-24818 Optimize multiple INSERT into empty table diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result index 733a85aa..dd032e1d 100644 --- a/mysql-test/suite/innodb/r/instant_alter_debug.result +++ b/mysql-test/suite/innodb/r/instant_alter_debug.result @@ -277,12 +277,6 @@ SET GLOBAL innodb_limit_optimistic_insert_debug = 2; INSERT INTO t1 VALUES (1),(5),(4),(3),(2); SET GLOBAL innodb_limit_optimistic_insert_debug = @old_limit; ALTER TABLE t1 ADD COLUMN b INT, ALGORITHM=INSTANT; -SET @old_defragment = @@innodb_defragment; -SET GLOBAL innodb_defragment = 1; -OPTIMIZE TABLE t1; -Table Op Msg_type Msg_text -test.t1 optimize status OK -SET GLOBAL innodb_defragment = @old_defragment; ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL; CHECK TABLE t1; Table Op Msg_type Msg_text diff --git a/mysql-test/suite/innodb/r/instant_alter_limit,16k.rdiff b/mysql-test/suite/innodb/r/instant_alter_limit,16k.rdiff index 57d87f71..320df318 100644 --- a/mysql-test/suite/innodb/r/instant_alter_limit,16k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter_limit,16k.rdiff @@ -1,5 +1,5 @@ ---- instant_alter_limit.result 2020-05-26 18:01:27.377946439 +0530 -+++ instant_alter_limit,16k.reject 2020-05-26 20:14:38.452463919 +0530 +--- instant_alter_limit.result ++++ instant_alter_limit,16k.reject @@ -45,3 +45,10 @@ instants 502 diff --git a/mysql-test/suite/innodb/r/instant_alter_limit,32k.rdiff b/mysql-test/suite/innodb/r/instant_alter_limit,32k.rdiff index 8f8cf64b..951f0ce2 100644 --- a/mysql-test/suite/innodb/r/instant_alter_limit,32k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter_limit,32k.rdiff @@ -1,5 +1,5 @@ ---- instant_alter_limit.result 2020-05-26 18:01:27.377946439 +0530 -+++ instant_alter_limit,32k.reject 2020-05-26 19:59:19.743877366 +0530 +--- instant_alter_limit.result ++++ instant_alter_limit,32k.reject @@ -43,5 +43,12 @@ FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; diff --git a/mysql-test/suite/innodb/r/instant_alter_limit,4k.rdiff b/mysql-test/suite/innodb/r/instant_alter_limit,4k.rdiff index dad28218..0ebd590a 100644 --- a/mysql-test/suite/innodb/r/instant_alter_limit,4k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter_limit,4k.rdiff @@ -1,5 +1,5 @@ ---- instant_alter_limit.result 2020-05-26 18:01:27.377946439 +0530 -+++ instant_alter_limit,4k.reject 2020-05-26 20:17:53.314736548 +0530 +--- instant_alter_limit.result ++++ instant_alter_limit,4k.reject @@ -5,6 +5,276 @@ ENGINE=InnoDB; INSERT INTO t VALUES(1,2,3,4,5); diff --git a/mysql-test/suite/innodb/r/instant_alter_limit,64k.rdiff b/mysql-test/suite/innodb/r/instant_alter_limit,64k.rdiff index d7479dbb..7c58fa4a 100644 --- a/mysql-test/suite/innodb/r/instant_alter_limit,64k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter_limit,64k.rdiff @@ -1,5 +1,5 @@ ---- instant_alter_limit.result 2020-05-26 18:01:27.377946439 +0530 -+++ instant_alter_limit,64k.reject 2020-05-26 20:00:22.499711222 +0530 +--- instant_alter_limit.result ++++ instant_alter_limit,64k.reject @@ -43,5 +43,12 @@ FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; diff --git a/mysql-test/suite/innodb/r/instant_alter_limit,8k.rdiff b/mysql-test/suite/innodb/r/instant_alter_limit,8k.rdiff index 1fe3e1a5..d70156f3 100644 --- a/mysql-test/suite/innodb/r/instant_alter_limit,8k.rdiff +++ b/mysql-test/suite/innodb/r/instant_alter_limit,8k.rdiff @@ -1,5 +1,5 @@ ---- instant_alter_limit.result 2020-05-26 18:01:27.377946439 +0530 -+++ instant_alter_limit,8k.reject 2020-05-26 20:19:50.881869095 +0530 +--- instant_alter_limit.result ++++ instant_alter_limit,8k.reject @@ -5,6 +5,28 @@ ENGINE=InnoDB; INSERT INTO t VALUES(1,2,3,4,5); diff --git a/mysql-test/suite/innodb/r/log_data_file_size.result b/mysql-test/suite/innodb/r/log_data_file_size.result index d33752b0..ddb8e694 100644 --- a/mysql-test/suite/innodb/r/log_data_file_size.result +++ b/mysql-test/suite/innodb/r/log_data_file_size.result @@ -1,6 +1,10 @@ SET GLOBAL innodb_file_per_table=0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t(a INT)ENGINE=InnoDB; SET GLOBAL innodb_file_per_table=1; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE ibd4(a INT UNIQUE)ENGINE=InnoDB; CREATE TABLE ibd4f(a INT UNIQUE)ENGINE=InnoDB; CREATE TABLE ibd5(a INT UNIQUE, b INT UNIQUE)ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb/r/log_file_name.result b/mysql-test/suite/innodb/r/log_file_name.result index a7f8cc8c..1839c9bc 100644 --- a/mysql-test/suite/innodb/r/log_file_name.result +++ b/mysql-test/suite/innodb/r/log_file_name.result @@ -1,5 +1,4 @@ call mtr.add_suppression("InnoDB: Header page consists of zero bytes in datafile:"); -SET GLOBAL innodb_file_per_table=ON; FLUSH TABLES; CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB; # restart diff --git a/mysql-test/suite/innodb/r/log_file_size.result b/mysql-test/suite/innodb/r/log_file_size.result index 7b4935e1..71fe06cf 100644 --- a/mysql-test/suite/innodb/r/log_file_size.result +++ b/mysql-test/suite/innodb/r/log_file_size.result @@ -31,7 +31,7 @@ connection default; SELECT * FROM t1; ERROR 42000: Unknown storage engine 'InnoDB' FOUND 1 /syntax error in innodb_log_group_home_dir/ in mysqld.1.err -# restart: --debug=d,innodb_log_abort_1 +# restart: --debug-dbug=d,innodb_log_abort_1 SELECT * FROM t1; ERROR 42000: Unknown storage engine 'InnoDB' FOUND 1 /InnoDB: Starting crash recovery from checkpoint LSN=.*/ in mysqld.1.err @@ -39,7 +39,7 @@ FOUND 1 /InnoDB: Starting crash recovery from checkpoint LSN=.*/ in mysqld.1.err SELECT * FROM t1; ERROR 42000: Unknown storage engine 'InnoDB' FOUND 1 /InnoDB: innodb_read_only prevents crash recovery/ in mysqld.1.err -# restart: --debug=d,innodb_log_abort_5 +# restart: --debug-dbug=d,innodb_log_abort_5 SELECT * FROM t1; ERROR 42000: Unknown storage engine 'InnoDB' FOUND 1 /redo log from 5\.000MiB to [0-9.]*[KMGT]iB/ in mysqld.1.err diff --git a/mysql-test/suite/innodb/r/log_upgrade_101_flags.result b/mysql-test/suite/innodb/r/log_upgrade_101_flags.result index 6ae7c848..2458c51b 100644 --- a/mysql-test/suite/innodb/r/log_upgrade_101_flags.result +++ b/mysql-test/suite/innodb/r/log_upgrade_101_flags.result @@ -1,7 +1,7 @@ call mtr.add_suppression("InnoDB: The change buffer is corrupted"); call mtr.add_suppression("InnoDB: Tablespace size stored in header is 768 pages, but the sum of data file sizes is 384 pages"); call mtr.add_suppression("InnoDB: adjusting FSP_SPACE_FLAGS of file"); -# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_upgrade --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_upgrade --innodb-force-recovery=5 --innodb-log-file-size=4m --innodb_page_size=32k --innodb_buffer_pool_size=10M +# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/log_upgrade --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/log_upgrade --innodb-undo-directory=MYSQLTEST_VARDIR/tmp/log_upgrade --innodb-force-recovery=5 --innodb-log-file-size=4m --innodb_page_size=32k --innodb_buffer_pool_size=10M SELECT COUNT(*) FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'innodb' AND support IN ('YES', 'DEFAULT', 'ENABLED'); diff --git a/mysql-test/suite/innodb/r/max_record_size,16k,compact.rdiff b/mysql-test/suite/innodb/r/max_record_size,16k,compact.rdiff index 118145be..b9f51624 100644 --- a/mysql-test/suite/innodb/r/max_record_size,16k,compact.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,16k,compact.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:51:26.070418078 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -3,45 +3,65 @@ c1 CHAR(255), c2 CHAR(255), c3 CHAR(255), c4 CHAR(255), c5 CHAR(255), c6 CHAR(255), c7 CHAR(255), c8 CHAR(255), diff --git a/mysql-test/suite/innodb/r/max_record_size,16k,dynamic.rdiff b/mysql-test/suite/innodb/r/max_record_size,16k,dynamic.rdiff index 33067866..5effc664 100644 --- a/mysql-test/suite/innodb/r/max_record_size,16k,dynamic.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,16k,dynamic.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:52:54.580956978 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -3,45 +3,207 @@ c1 CHAR(255), c2 CHAR(255), c3 CHAR(255), c4 CHAR(255), c5 CHAR(255), c6 CHAR(255), c7 CHAR(255), c8 CHAR(255), diff --git a/mysql-test/suite/innodb/r/max_record_size,16k,redundant.rdiff b/mysql-test/suite/innodb/r/max_record_size,16k,redundant.rdiff index e50e4d2b..227b64a8 100644 --- a/mysql-test/suite/innodb/r/max_record_size,16k,redundant.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,16k,redundant.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:55:05.258762945 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -3,45 +3,65 @@ c1 CHAR(255), c2 CHAR(255), c3 CHAR(255), c4 CHAR(255), c5 CHAR(255), c6 CHAR(255), c7 CHAR(255), c8 CHAR(255), diff --git a/mysql-test/suite/innodb/r/max_record_size,32k,compact.rdiff b/mysql-test/suite/innodb/r/max_record_size,32k,compact.rdiff index 1bc03a5d..d236e12b 100644 --- a/mysql-test/suite/innodb/r/max_record_size,32k,compact.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,32k,compact.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:27:08.004932026 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -3,45 +3,104 @@ c1 CHAR(255), c2 CHAR(255), c3 CHAR(255), c4 CHAR(255), c5 CHAR(255), c6 CHAR(255), c7 CHAR(255), c8 CHAR(255), diff --git a/mysql-test/suite/innodb/r/max_record_size,32k,dynamic.rdiff b/mysql-test/suite/innodb/r/max_record_size,32k,dynamic.rdiff index 71c4c2e4..ce9462b6 100644 --- a/mysql-test/suite/innodb/r/max_record_size,32k,dynamic.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,32k,dynamic.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:38:41.609328820 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -3,45 +3,351 @@ c1 CHAR(255), c2 CHAR(255), c3 CHAR(255), c4 CHAR(255), c5 CHAR(255), c6 CHAR(255), c7 CHAR(255), c8 CHAR(255), diff --git a/mysql-test/suite/innodb/r/max_record_size,32k,redundant.rdiff b/mysql-test/suite/innodb/r/max_record_size,32k,redundant.rdiff index e42b3de8..53d6f76a 100644 --- a/mysql-test/suite/innodb/r/max_record_size,32k,redundant.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,32k,redundant.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:41:03.319664978 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -3,45 +3,104 @@ c1 CHAR(255), c2 CHAR(255), c3 CHAR(255), c4 CHAR(255), c5 CHAR(255), c6 CHAR(255), c7 CHAR(255), c8 CHAR(255), diff --git a/mysql-test/suite/innodb/r/max_record_size,4k,compact.rdiff b/mysql-test/suite/innodb/r/max_record_size,4k,compact.rdiff index 6f08dab3..37871798 100644 --- a/mysql-test/suite/innodb/r/max_record_size,4k,compact.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,4k,compact.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:56:23.489432164 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -1,47 +1,37 @@ call mtr.add_suppression("Cannot add field `.*` in table `test`.`t1` because after adding it, the row size is"); CREATE TABLE t1 ( diff --git a/mysql-test/suite/innodb/r/max_record_size,4k,dynamic.rdiff b/mysql-test/suite/innodb/r/max_record_size,4k,dynamic.rdiff index bd37d435..b0b6b007 100644 --- a/mysql-test/suite/innodb/r/max_record_size,4k,dynamic.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,4k,dynamic.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:57:38.636143710 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -1,47 +1,103 @@ call mtr.add_suppression("Cannot add field `.*` in table `test`.`t1` because after adding it, the row size is"); CREATE TABLE t1 ( diff --git a/mysql-test/suite/innodb/r/max_record_size,4k,redundant.rdiff b/mysql-test/suite/innodb/r/max_record_size,4k,redundant.rdiff index 93c39ff6..0db6450d 100644 --- a/mysql-test/suite/innodb/r/max_record_size,4k,redundant.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,4k,redundant.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:58:58.318768169 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -1,47 +1,37 @@ call mtr.add_suppression("Cannot add field `.*` in table `test`.`t1` because after adding it, the row size is"); CREATE TABLE t1 ( diff --git a/mysql-test/suite/innodb/r/max_record_size,64k,compact.rdiff b/mysql-test/suite/innodb/r/max_record_size,64k,compact.rdiff index 5e6c6221..ab75975e 100644 --- a/mysql-test/suite/innodb/r/max_record_size,64k,compact.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,64k,compact.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:45:21.760116841 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -3,45 +3,186 @@ c1 CHAR(255), c2 CHAR(255), c3 CHAR(255), c4 CHAR(255), c5 CHAR(255), c6 CHAR(255), c7 CHAR(255), c8 CHAR(255), diff --git a/mysql-test/suite/innodb/r/max_record_size,64k,dynamic.rdiff b/mysql-test/suite/innodb/r/max_record_size,64k,dynamic.rdiff index 2cbf4cd9..3e13a510 100644 --- a/mysql-test/suite/innodb/r/max_record_size,64k,dynamic.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,64k,dynamic.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:48:32.453208310 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -3,45 +3,486 @@ c1 CHAR(255), c2 CHAR(255), c3 CHAR(255), c4 CHAR(255), c5 CHAR(255), c6 CHAR(255), c7 CHAR(255), c8 CHAR(255), diff --git a/mysql-test/suite/innodb/r/max_record_size,64k,redundant.rdiff b/mysql-test/suite/innodb/r/max_record_size,64k,redundant.rdiff index c324969f..a31285a2 100644 --- a/mysql-test/suite/innodb/r/max_record_size,64k,redundant.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,64k,redundant.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 12:50:05.663724193 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -3,45 +3,104 @@ c1 CHAR(255), c2 CHAR(255), c3 CHAR(255), c4 CHAR(255), c5 CHAR(255), c6 CHAR(255), c7 CHAR(255), c8 CHAR(255), diff --git a/mysql-test/suite/innodb/r/max_record_size,8k,compact.rdiff b/mysql-test/suite/innodb/r/max_record_size,8k,compact.rdiff index 4db669a8..cafb5f25 100644 --- a/mysql-test/suite/innodb/r/max_record_size,8k,compact.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,8k,compact.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 13:00:25.037261867 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -12,7 +12,7 @@ c9 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255), c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255) diff --git a/mysql-test/suite/innodb/r/max_record_size,8k,dynamic.rdiff b/mysql-test/suite/innodb/r/max_record_size,8k,dynamic.rdiff index 5f79d77e..2f592fbb 100644 --- a/mysql-test/suite/innodb/r/max_record_size,8k,dynamic.rdiff +++ b/mysql-test/suite/innodb/r/max_record_size,8k,dynamic.rdiff @@ -1,5 +1,5 @@ ---- max_record_size.result 2019-07-03 11:54:44.591421526 +0300 -+++ max_record_size.reject 2019-07-03 13:01:51.215756779 +0300 +--- max_record_size.result ++++ max_record_size.reject @@ -12,7 +12,7 @@ c9 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255), c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255) diff --git a/mysql-test/suite/innodb/r/mdev-14846.result b/mysql-test/suite/innodb/r/mdev-14846.result index a1ccfb6b..b5d8dcbe 100644 --- a/mysql-test/suite/innodb/r/mdev-14846.result +++ b/mysql-test/suite/innodb/r/mdev-14846.result @@ -34,13 +34,25 @@ SET DEBUG_SYNC='now SIGNAL default_dml'; SET DEBUG_SYNC='now SIGNAL con2_dml'; connection default; SET DEBUG_SYNC='now WAIT_FOR default_dml'; -UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h'; +explain UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h'; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL # +1 PRIMARY alias2 ALL NULL NULL NULL NULL # Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL # Using where +UPDATE t3 AS alias1 LEFT JOIN t3 AS alias2 ON ( alias1.f1 <> alias1.f2 ) SET alias1.f3 = 59 WHERE ( EXISTS ( SELECT t1.f3 FROM t1 IGNORE INDEX (f1) WHERE t1.f1 = alias1.f1 ) ) OR alias2.f1 = 'h'; connect con2,localhost,root,,test; set debug_sync='now WAIT_FOR con2_dml'; SET DEBUG_SYNC='now SIGNAL con1_dml2'; disconnect con2; connection con1; SET DEBUG_SYNC='now WAIT_FOR con1_dml2'; +explain UPDATE v4, t1 SET t1.pk = 76 WHERE t1.f2 IN ( SELECT t2.f FROM t2 INNER JOIN t3 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL f1 12 NULL # Using index +1 PRIMARY t1 ALL NULL NULL NULL NULL # +1 PRIMARY eq_ref distinct_key distinct_key 13 func # +2 MATERIALIZED t3 ALL NULL NULL NULL NULL # +2 MATERIALIZED t2 ALL NULL NULL NULL NULL # UPDATE v4, t1 SET t1.pk = 76 WHERE t1.f2 IN ( SELECT t2.f FROM t2 INNER JOIN t3 ); connection default; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction diff --git a/mysql-test/suite/innodb/r/mdev-15707.result b/mysql-test/suite/innodb/r/mdev-15707.result deleted file mode 100644 index 3967ce48..00000000 --- a/mysql-test/suite/innodb/r/mdev-15707.result +++ /dev/null @@ -1,24 +0,0 @@ -CREATE TABLE t1( -a INT AUTO_INCREMENT PRIMARY KEY, -b CHAR(255), -INDEX(b)) -ENGINE=InnoDB; -INSERT INTO t1(b) SELECT UUID(); -BEGIN; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -INSERT INTO t1(b) SELECT UUID() FROM t1; -COMMIT; -UPDATE t1 SET b=UUID(); -DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/monitor.result b/mysql-test/suite/innodb/r/monitor.result index 1834032e..d97f741e 100644 --- a/mysql-test/suite/innodb/r/monitor.result +++ b/mysql-test/suite/innodb/r/monitor.result @@ -79,12 +79,8 @@ buffer_LRU_unzip_search_num_scan disabled buffer_LRU_unzip_search_scanned_per_call disabled buffer_page_read_index_leaf disabled buffer_page_read_index_non_leaf disabled -buffer_page_read_index_ibuf_leaf disabled -buffer_page_read_index_ibuf_non_leaf disabled buffer_page_read_undo_log disabled buffer_page_read_index_inode disabled -buffer_page_read_ibuf_free_list disabled -buffer_page_read_ibuf_bitmap disabled buffer_page_read_system_page disabled buffer_page_read_trx_system disabled buffer_page_read_fsp_hdr disabled @@ -95,12 +91,8 @@ buffer_page_read_zblob2 disabled buffer_page_read_other disabled buffer_page_written_index_leaf disabled buffer_page_written_index_non_leaf disabled -buffer_page_written_index_ibuf_leaf disabled -buffer_page_written_index_ibuf_non_leaf disabled buffer_page_written_undo_log disabled buffer_page_written_index_inode disabled -buffer_page_written_ibuf_free_list disabled -buffer_page_written_ibuf_bitmap disabled buffer_page_written_system_page disabled buffer_page_written_trx_system disabled buffer_page_written_fsp_hdr disabled @@ -168,14 +160,6 @@ adaptive_hash_rows_removed disabled adaptive_hash_rows_deleted_no_hash_entry disabled adaptive_hash_rows_updated disabled file_num_open_files enabled -ibuf_merges_insert enabled -ibuf_merges_delete_mark enabled -ibuf_merges_delete enabled -ibuf_merges_discard_insert enabled -ibuf_merges_discard_delete_mark enabled -ibuf_merges_discard_delete enabled -ibuf_merges enabled -ibuf_size enabled innodb_master_thread_sleeps disabled innodb_activity_count enabled innodb_master_active_loops disabled diff --git a/mysql-test/suite/innodb/r/mvcc.result b/mysql-test/suite/innodb/r/mvcc.result index 215b2165..3e6b0ad3 100644 --- a/mysql-test/suite/innodb/r/mvcc.result +++ b/mysql-test/suite/innodb/r/mvcc.result @@ -1,5 +1,3 @@ -SET @save_per_table= @@GLOBAL.innodb_file_per_table; -SET GLOBAL innodb_file_per_table= 1; # # MDEV-15249 Crash in MVCC read after IMPORT TABLESPACE # @@ -43,4 +41,3 @@ $$ INSERT INTO t1 SET id=1,c294=1; REPLACE t1 SET id=1,c294=1; DROP TABLE t1; -SET GLOBAL innodb_file_per_table= @save_per_table; diff --git a/mysql-test/suite/innodb/r/page_reorganize.result b/mysql-test/suite/innodb/r/page_reorganize.result index fe85926e..c86d0cf9 100644 --- a/mysql-test/suite/innodb/r/page_reorganize.result +++ b/mysql-test/suite/innodb/r/page_reorganize.result @@ -15,7 +15,7 @@ connection default; SET @save_dbug = @@debug_dbug; SET DEBUG_DBUG = '+d,do_page_reorganize,do_lock_reverse_page_reorganize'; insert into t1(f2) values (repeat('+', 100)); -SET DEBUG = @save_dbug; +SET DEBUG_DBUG = @save_dbug; commit; connection con1; f1 diff --git a/mysql-test/suite/innodb/r/partition_locking.result b/mysql-test/suite/innodb/r/partition_locking.result index f25b8a15..b5e96f96 100644 --- a/mysql-test/suite/innodb/r/partition_locking.result +++ b/mysql-test/suite/innodb/r/partition_locking.result @@ -148,7 +148,7 @@ a b c d e 03 03 343 7 03_03_343 03 06 343 8 03_06_343 03 07 343 9 03_07_343 -SELECT a,count(b) FROM t1 GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED; +SELECT a,count(b) FROM t1 force index (a) GROUP BY a ORDER BY a LOCK IN SHARE MODE SKIP LOCKED; a count(b) 01 5 03 3 diff --git a/mysql-test/suite/innodb/r/read_only_recovery.result b/mysql-test/suite/innodb/r/read_only_recovery.result index 2cde5818..78815e56 100644 --- a/mysql-test/suite/innodb/r/read_only_recovery.result +++ b/mysql-test/suite/innodb/r/read_only_recovery.result @@ -29,6 +29,9 @@ UPDATE t SET a=3 WHERE a=1; # Starting with MariaDB 10.2, innodb_read_only implies READ UNCOMMITTED. # In earlier versions, this would return the last committed version # (empty table)! +SHOW VARIABLES LIKE "innodb_read_only"; +Variable_name Value +innodb_read_only ON SELECT * FROM t; a 3 @@ -39,6 +42,8 @@ a SET GLOBAL innodb_max_purge_lag_wait=0; INSERT INTO mysql.innodb_index_stats SELECT * FROM mysql.innodb_index_stats LIMIT 0; +Test Temp tablespace truncate in read only mode +SET GLOBAL innodb_truncate_temporary_tablespace_now=1; # restart SELECT * FROM t; a diff --git a/mysql-test/suite/innodb/r/restart,16k.rdiff b/mysql-test/suite/innodb/r/restart,16k.rdiff index b36ed067..3149b9ae 100644 --- a/mysql-test/suite/innodb/r/restart,16k.rdiff +++ b/mysql-test/suite/innodb/r/restart,16k.rdiff @@ -1,5 +1,5 @@ ---- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 -+++ suite/innodb/r/restart.reject 2022-01-19 08:12:28.602794678 +1100 +--- ./suite/innodb/r/restart.result ++++ suite/innodb/r/restart.reject @@ -32,10 +32,10 @@ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; diff --git a/mysql-test/suite/innodb/r/restart,32k.rdiff b/mysql-test/suite/innodb/r/restart,32k.rdiff index 8fa05781..3f00646c 100644 --- a/mysql-test/suite/innodb/r/restart,32k.rdiff +++ b/mysql-test/suite/innodb/r/restart,32k.rdiff @@ -1,5 +1,5 @@ ---- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 -+++ suite/innodb/r/restart.reject 2022-01-19 08:07:57.402230887 +1100 +--- ./suite/innodb/r/restart.result ++++ suite/innodb/r/restart.reject @@ -32,10 +32,10 @@ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; diff --git a/mysql-test/suite/innodb/r/restart,4k.rdiff b/mysql-test/suite/innodb/r/restart,4k.rdiff index 7d084636..b00c56ef 100644 --- a/mysql-test/suite/innodb/r/restart,4k.rdiff +++ b/mysql-test/suite/innodb/r/restart,4k.rdiff @@ -1,5 +1,5 @@ ---- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 -+++ suite/innodb/r/restart.reject 2022-01-19 08:13:56.397475513 +1100 +--- ./suite/innodb/r/restart.result ++++ suite/innodb/r/restart.reject @@ -32,10 +32,10 @@ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; diff --git a/mysql-test/suite/innodb/r/restart,64k.rdiff b/mysql-test/suite/innodb/r/restart,64k.rdiff index 3ac9f45b..886cbcde 100644 --- a/mysql-test/suite/innodb/r/restart,64k.rdiff +++ b/mysql-test/suite/innodb/r/restart,64k.rdiff @@ -1,5 +1,5 @@ ---- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 -+++ suite/innodb/r/restart.reject 2022-01-19 08:11:32.418759095 +1100 +--- ./suite/innodb/r/restart.result ++++ suite/innodb/r/restart.reject @@ -32,10 +32,10 @@ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; diff --git a/mysql-test/suite/innodb/r/restart,8k.rdiff b/mysql-test/suite/innodb/r/restart,8k.rdiff index 4da55ebf..40a9e1ba 100644 --- a/mysql-test/suite/innodb/r/restart,8k.rdiff +++ b/mysql-test/suite/innodb/r/restart,8k.rdiff @@ -1,5 +1,5 @@ ---- ./suite/innodb/r/restart.result 2022-01-18 20:36:56.054653376 +1100 -+++ suite/innodb/r/restart.reject 2022-01-19 08:13:11.027788852 +1100 +--- ./suite/innodb/r/restart.result ++++ suite/innodb/r/restart.reject @@ -32,10 +32,10 @@ SELECT @@innodb_buffer_pool_size INTO @innodb_buffer_pool_size_orig; SELECT CEILING((256 + 64) * @@innodb_page_size / 1048576) * 1048576 INTO @min_pool_size; diff --git a/mysql-test/suite/innodb/r/row_format_redundant.result b/mysql-test/suite/innodb/r/row_format_redundant.result index e3356c63..3fa0bf8e 100644 --- a/mysql-test/suite/innodb/r/row_format_redundant.result +++ b/mysql-test/suite/innodb/r/row_format_redundant.result @@ -1,11 +1,9 @@ SET GLOBAL innodb_fast_shutdown=0; -# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-data-file-path=ibdata1:1M:autoextend --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 -SET GLOBAL innodb_file_per_table=1; +# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-data-file-path=ibdata1:1M:autoextend --innodb_undo_directory=MYSQLTEST_VARDIR/tmp/undo_dir --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 # # Bug#21644827 - FTS, ASSERT !SRV_READ_ONLY_MODE || M_IMPL.M_LOG_MODE == # MTR_LOG_NO_REDO # -SET GLOBAL innodb_file_per_table=ON; create table t1 (a int not null, d varchar(15) not null, b varchar(198) not null, c char(156)) engine=InnoDB row_format=redundant; @@ -16,6 +14,8 @@ insert into t values(789, 'abcdef', 'jghikl', 'mnop'); insert into t values(134, 'kasdfsdsadf', 'adfjlasdkfjasd', 'adfsadflkasdasdfljasdf'); insert into t1 select a,d,b,c from t, seq_1_to_1024; SET GLOBAL innodb_file_per_table=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release create table t2 (a int not null, d varchar(15) not null, b varchar(198) not null, c char(156), fulltext ftsic(c)) engine=InnoDB row_format=redundant; @@ -25,8 +25,7 @@ c varchar(150), index k1(c(99), b(56)), index k2(b(5), c(10))) engine=InnoDB row_format=redundant; insert into t3 values(444, 'dddd', 'bbbbb', 'aaaaa'); insert into t3 values(555, 'eeee', 'ccccc', 'aaaaa'); -SET GLOBAL innodb_fast_shutdown=0; -# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-data-file-path=ibdata1:1M:autoextend --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 --innodb-read-only +# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-data-file-path=ibdata1:1M:autoextend --innodb_undo_directory=MYSQLTEST_VARDIR/tmp/undo_dir --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 --innodb-read-only SELECT COUNT(*) FROM t1; COUNT(*) 4096 @@ -42,14 +41,14 @@ TRUNCATE TABLE t2; ERROR HY000: Table 't2' is read only TRUNCATE TABLE t3; ERROR HY000: Table 't3' is read only -# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-data-file-path=ibdata1:1M:autoextend --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 --skip-innodb-fast-shutdown +# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-data-file-path=ibdata1:1M:autoextend --innodb_undo_directory=MYSQLTEST_VARDIR/tmp/undo_dir --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 --skip-innodb-fast-shutdown TRUNCATE TABLE t1; TRUNCATE TABLE t2; TRUNCATE TABLE t3; corrupted SYS_TABLES.MIX_LEN for test/t1 corrupted SYS_TABLES.MIX_LEN for test/t2 corrupted SYS_TABLES.MIX_LEN for test/t3 -# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-data-file-path=ibdata1:1M:autoextend --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 --skip-innodb-fast-shutdown +# restart: --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/row_format_redundant --innodb-data-file-path=ibdata1:1M:autoextend --innodb_undo_directory=MYSQLTEST_VARDIR/tmp/undo_dir --innodb-undo-tablespaces=0 --innodb-stats-persistent=0 --skip-innodb-fast-shutdown TRUNCATE TABLE t1; ERROR 42S02: Table 'test.t1' doesn't exist in engine TRUNCATE TABLE t2; @@ -68,7 +67,7 @@ DROP TABLE t1; Warnings: Warning 1932 Table 'test.t1' doesn't exist in engine DROP TABLE t2,t3; -FOUND 5 /\[ERROR\] InnoDB: Table test/t1 in InnoDB data dictionary contains invalid flags\. SYS_TABLES\.TYPE=1 SYS_TABLES\.MIX_LEN=511\b/ in mysqld.1.err +FOUND 1 /\[ERROR\] InnoDB: Table test/t1 in InnoDB data dictionary contains invalid flags\. SYS_TABLES\.TYPE=1 SYS_TABLES\.MIX_LEN=511\b.*/ in mysqld.1.err # restart ib_logfile0 ibdata1 diff --git a/mysql-test/suite/innodb/r/scrub.result b/mysql-test/suite/innodb/r/scrub.result index 4b976c32..5b8dd17d 100644 --- a/mysql-test/suite/innodb/r/scrub.result +++ b/mysql-test/suite/innodb/r/scrub.result @@ -1,6 +1,8 @@ SET GLOBAL innodb_file_per_table=OFF, innodb_immediate_scrub_data_uncompressed=ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT NOT NULL, INDEX(f1), INDEX(f2), INDEX(f3))ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb/r/sys_truncate.result b/mysql-test/suite/innodb/r/sys_truncate.result new file mode 100644 index 00000000..bb8eafbe --- /dev/null +++ b/mysql-test/suite/innodb/r/sys_truncate.result @@ -0,0 +1,21 @@ +SET GLOBAL INNODB_FILE_PER_TABLE= 0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release +SET UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0; +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, +f3 INT NOT NULL, INDEX(f1), +INDEX(f2), INDEX(f3))ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +COMMIT; +DROP TABLE t1; +InnoDB 0 transactions not purged +SELECT NAME, FILE_SIZE FROM information_schema.innodb_sys_tablespaces WHERE SPACE = 0; +NAME FILE_SIZE +innodb_system 77594624 +# restart +SELECT NAME, FILE_SIZE FROM information_schema.innodb_sys_tablespaces WHERE SPACE = 0; +NAME FILE_SIZE +innodb_system 10485760 diff --git a/mysql-test/suite/innodb/r/sys_truncate_debug.result b/mysql-test/suite/innodb/r/sys_truncate_debug.result new file mode 100644 index 00000000..f122e944 --- /dev/null +++ b/mysql-test/suite/innodb/r/sys_truncate_debug.result @@ -0,0 +1,49 @@ +call mtr.add_suppression("InnoDB: Cannot shrink the system 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"); +SET GLOBAL INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG=2; +SET GLOBAL INNODB_FILE_PER_TABLE= 0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release +SET UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0; +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, +f3 INT NOT NULL, INDEX(f1), +INDEX(f2), INDEX(f3))ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +COMMIT; +DROP TABLE t1; +InnoDB 0 transactions not purged +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 0; +NAME FILE_SIZE +innodb_system 540016640 +# restart: --debug_dbug=+d,shrink_buffer_pool_full +FOUND 1 /\[Warning\] InnoDB: Cannot shrink the system tablespace/ in mysqld.1.err +SELECT * FROM INFORMATION_SCHEMA.ENGINES +WHERE engine = 'innodb' +AND support IN ('YES', 'DEFAULT', 'ENABLED'); +ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS +InnoDB YES Supports transactions, row-level locking, foreign keys and encryption for tables YES YES YES +# restart: --debug_dbug=+d,mtr_log_max_size +FOUND 1 /\[ERROR\] InnoDB: Cannot shrink the system tablespace/ in mysqld.1.err +SELECT * FROM INFORMATION_SCHEMA.ENGINES +WHERE engine = 'innodb' +AND support IN ('YES', 'DEFAULT', 'ENABLED'); +ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS +InnoDB YES Supports transactions, row-level locking, foreign keys and encryption for tables YES YES YES +# restart: --debug_dbug=+d,crash_after_sys_truncate +SELECT * FROM INFORMATION_SCHEMA.ENGINES +WHERE engine = 'innodb' +AND support IN ('YES', 'DEFAULT', 'ENABLED'); +ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS +# restart: --innodb_buffer_pool_size=5M +SELECT * FROM INFORMATION_SCHEMA.ENGINES +WHERE engine = 'innodb' +AND support IN ('YES', 'DEFAULT', 'ENABLED'); +ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS +InnoDB YES Supports transactions, row-level locking, foreign keys and encryption for tables YES YES YES +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE=0; +NAME FILE_SIZE +innodb_system 3145728 diff --git a/mysql-test/suite/innodb/r/sys_truncate_large.result b/mysql-test/suite/innodb/r/sys_truncate_large.result new file mode 100644 index 00000000..3b085ec4 --- /dev/null +++ b/mysql-test/suite/innodb/r/sys_truncate_large.result @@ -0,0 +1,22 @@ +set global innodb_file_per_table=0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release +set global innodb_limit_optimistic_insert_debug=2; +set unique_checks=0, foreign_key_checks=0; +create table t1(f1 int not null)engine=innodb; +begin; +insert into t1 select * from seq_1_to_8192; +insert into t1 select * from seq_1_to_8192; +insert into t1 select * from seq_1_to_65536; +commit; +create table t2(f1 int not null)engine=innodb; +insert into t2 select * from seq_1_to_65536; +create table t3(f1 int not null)engine=innodb; +insert into t3 select * from seq_1_to_65536; +CREATE TABLE t4(f1 int not null)engine=innodb; +insert into t4 select * from seq_1_to_65536; +drop table t2; +drop table t4; +InnoDB 0 transactions not purged +# restart +drop table t3, t1; diff --git a/mysql-test/suite/innodb/r/sys_truncate_shutdown.result b/mysql-test/suite/innodb/r/sys_truncate_shutdown.result new file mode 100644 index 00000000..ee567a76 --- /dev/null +++ b/mysql-test/suite/innodb/r/sys_truncate_shutdown.result @@ -0,0 +1,23 @@ +SET GLOBAL innodb_fast_shutdown=0; +# restart +SET GLOBAL INNODB_FILE_PER_TABLE= 0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release +SET UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0; +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, +f3 INT NOT NULL, INDEX(f1), +INDEX(f2), INDEX(f3))ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +COMMIT; +DROP TABLE t1; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 0; +NAME FILE_SIZE +innodb_system 70254592 +SET GLOBAL innodb_fast_shutdown=0; +# restart +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 0; +NAME FILE_SIZE +innodb_system 3145728 diff --git a/mysql-test/suite/innodb/r/sys_truncate_shutdown_debug.result b/mysql-test/suite/innodb/r/sys_truncate_shutdown_debug.result new file mode 100644 index 00000000..0aad5af9 --- /dev/null +++ b/mysql-test/suite/innodb/r/sys_truncate_shutdown_debug.result @@ -0,0 +1,41 @@ +call mtr.add_suppression("InnoDB: Cannot shrink the system 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"); +SET GLOBAL innodb_fast_shutdown=0; +# restart +SET GLOBAL INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG=2; +SET GLOBAL INNODB_FILE_PER_TABLE= 0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release +SET UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0; +CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, +f3 INT NOT NULL, INDEX(f1), +INDEX(f2), INDEX(f3))ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +INSERT INTO t1 SELECT seq, seq, seq FROM seq_1_to_16384; +COMMIT; +DROP TABLE t1; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 0; +NAME FILE_SIZE +innodb_system 540016640 +SET GLOBAL INNODB_FAST_SHUTDOWN=0; +SET GLOBAL DEBUG_DBUG="+d,shrink_buffer_pool_full"; +# restart +FOUND 1 /\[Warning\] InnoDB: Cannot shrink the system tablespace/ in mysqld.1.err +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 0; +NAME FILE_SIZE +innodb_system 540016640 +SET GLOBAL INNODB_FAST_SHUTDOWN=0; +SET GLOBAL DEBUG_DBUG="+d,mtr_log_max_size"; +# restart +FOUND 1 /\[ERROR\] InnoDB: Cannot shrink the system tablespace/ in mysqld.1.err +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 0; +NAME FILE_SIZE +innodb_system 540016640 +SET GLOBAL INNODB_FAST_SHUTDOWN=0; +# restart: --debug_dbug=+d,crash_after_sys_truncate +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE = 0; +NAME FILE_SIZE +innodb_system 3145728 diff --git a/mysql-test/suite/innodb/r/table_flags.result b/mysql-test/suite/innodb/r/table_flags.result index cc32472f..e38eb93e 100644 --- a/mysql-test/suite/innodb/r/table_flags.result +++ b/mysql-test/suite/innodb/r/table_flags.result @@ -1,5 +1,4 @@ # restart: with restart_parameters -SET GLOBAL innodb_file_per_table=1; CREATE TABLE tr(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=REDUNDANT; CREATE TABLE tc(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=COMPACT; CREATE TABLE td(a INT PRIMARY KEY)ENGINE=InnoDB ROW_FORMAT=DYNAMIC; diff --git a/mysql-test/suite/innodb/r/table_index_statistics.result b/mysql-test/suite/innodb/r/table_index_statistics.result index 286c5f93..ddb23afd 100644 --- a/mysql-test/suite/innodb/r/table_index_statistics.result +++ b/mysql-test/suite/innodb/r/table_index_statistics.result @@ -14,13 +14,11 @@ ROWS_READ 10 SELECT ROWS_READ FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='t1'; ROWS_READ -10 FLUSH TABLE_STATISTICS; SELECT ROWS_READ FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_NAME='t1'; ROWS_READ SELECT ROWS_READ FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='t1'; ROWS_READ -10 FLUSH INDEX_STATISTICS; SELECT ROWS_READ FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='t1'; ROWS_READ @@ -32,7 +30,6 @@ ROWS_READ 10 SELECT ROWS_READ FROM INFORMATION_SCHEMA.INDEX_STATISTICS WHERE TABLE_NAME='t1'; ROWS_READ -10 DROP TABLE t1; CREATE TABLE t2 (c1 INT UNSIGNED); ALTER TABLE t2 MODIFY c1 FLOAT; diff --git a/mysql-test/suite/innodb/r/temp_truncate.result b/mysql-test/suite/innodb/r/temp_truncate.result new file mode 100644 index 00000000..2b241feb --- /dev/null +++ b/mysql-test/suite/innodb/r/temp_truncate.result @@ -0,0 +1,46 @@ +# MDEV-33101 Server crashes when starting the server with +# innodb-force-recovery=6 and enabling the +# innodb_truncate_temporary_tablespace_now variable +# restart: --innodb-force-recovery=6 +SHOW VARIABLES LIKE "innodb_read_only"; +Variable_name Value +innodb_read_only ON +SET GLOBAL innodb_truncate_temporary_tablespace_now=1; +# restart +CREATE TEMPORARY TABLE t1(f1 INT NOT NULL, +f2 INT NOT NULL)ENGINE=InnoDB; +INSERT INTO t1 SELECT seq, seq FROM seq_1_to_65536; +DROP TABLE t1; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE=4294967294; +NAME FILE_SIZE +innodb_temporary 72351744 +SET GLOBAL INNODB_TRUNCATE_TEMPORARY_TABLESPACE_NOW= 0; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE=4294967294; +NAME FILE_SIZE +innodb_temporary 72351744 +SET GLOBAL INNODB_TRUNCATE_TEMPORARY_TABLESPACE_NOW= 1; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE= 4294967294; +NAME FILE_SIZE +innodb_temporary 5242880 +CREATE TEMPORARY TABLE t1(f1 INT NOT NULL, +f2 INT NOT NULL)ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 SELECT seq, seq FROM seq_1_to_65536; +connect con1,localhost,root,,,; +CREATE TEMPORARY TABLE t2(f1 INT NOT NULL, +f2 INT NOT NULL)ENGINE=InnoDB; +INSERT INTO t2 SELECT seq, seq FROM seq_1_to_65536; +DROP TABLE t2; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE=4294967294; +NAME FILE_SIZE +innodb_temporary 72351744 +SET GLOBAL INNODB_TRUNCATE_TEMPORARY_TABLESPACE_NOW= 1; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE= 4294967294; +NAME FILE_SIZE +innodb_temporary 7340032 +connection default; +COMMIT; +SELECT COUNT(*) FROM t1; +COUNT(*) +65536 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/temp_truncate_debug.result b/mysql-test/suite/innodb/r/temp_truncate_debug.result new file mode 100644 index 00000000..37809c0a --- /dev/null +++ b/mysql-test/suite/innodb/r/temp_truncate_debug.result @@ -0,0 +1,22 @@ +call mtr.add_suppression("InnoDB: Cannot shrink the temporary tablespace"); +# restart +SET GLOBAL INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG=2; +CREATE TEMPORARY TABLE t1(f1 INT NOT NULL, +f2 INT NOT NULL)ENGINE=InnoDB; +INSERT INTO t1 SELECT seq, seq FROM seq_1_to_65536; +DROP TABLE t1; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE= 4294967294; +NAME FILE_SIZE +innodb_temporary 1146093568 +SET @saved_debug_dbug = @@SESSION.debug_dbug; +SET DEBUG_DBUG="+d,fail_temp_truncate"; +SET GLOBAL INNODB_TRUNCATE_TEMPORARY_TABLESPACE_NOW= 1; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE= 4294967294; +NAME FILE_SIZE +innodb_temporary 1146093568 +SET DEBUG_DBUG=@saved_debug_dbug; +SET GLOBAL INNODB_TRUNCATE_TEMPORARY_TABLESPACE_NOW= 1; +SELECT NAME, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE= 4294967294; +NAME FILE_SIZE +innodb_temporary 5242880 +SET GLOBAL INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG=default; diff --git a/mysql-test/suite/innodb/r/temporary_table.result b/mysql-test/suite/innodb/r/temporary_table.result index ffcee726..e90302da 100644 --- a/mysql-test/suite/innodb/r/temporary_table.result +++ b/mysql-test/suite/innodb/r/temporary_table.result @@ -709,12 +709,12 @@ UPDATE t1 SET a= 2; COMMIT; DROP TABLE t1; CREATE TEMPORARY TABLE t(c INT) ENGINE=InnoDB; -SET SESSION tx_read_only=TRUE; +SET SESSION TRANSACTION_READ_ONLY=TRUE; LOCK TABLE test.t READ; SELECT * FROM t; c INSERT INTO t VALUES(0xADC3); -SET SESSION tx_read_only=FALSE; +SET SESSION TRANSACTION_READ_ONLY=FALSE; DROP TABLE t; CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); @@ -748,27 +748,27 @@ COMMIT; DROP TABLE t1; CREATE TEMPORARY TABLE tmp (a INT) ENGINE=InnoDB; INSERT INTO tmp () VALUES (),(); -SET TX_READ_ONLY= 1; +SET TRANSACTION_READ_ONLY= 1; INSERT INTO tmp SELECT * FROM tmp; -SET TX_READ_ONLY= 0; +SET TRANSACTION_READ_ONLY= 0; DROP TABLE tmp; SET sql_mode=''; -SET GLOBAL tx_read_only=TRUE; +SET GLOBAL TRANSACTION_READ_ONLY=TRUE; CREATE TEMPORARY TABLE t (c INT); -SET SESSION tx_read_only=DEFAULT; +SET SESSION TRANSACTION_READ_ONLY=DEFAULT; INSERT INTO t VALUES(1); INSERT INTO t SELECT * FROM t; -SET SESSION tx_read_only=FALSE; -SET GLOBAL tx_read_only=OFF; +SET SESSION TRANSACTION_READ_ONLY=FALSE; +SET GLOBAL TRANSACTION_READ_ONLY=OFF; DROP TABLE t; CREATE TEMPORARY TABLE t(a INT); -SET SESSION tx_read_only=ON; +SET SESSION TRANSACTION_READ_ONLY=ON; LOCK TABLE t READ; SELECT COUNT(*)FROM t; COUNT(*) 0 INSERT INTO t VALUES (0); -SET SESSION tx_read_only=OFF; +SET SESSION TRANSACTION_READ_ONLY=OFF; DROP TABLE t; CREATE TEMPORARY TABLE t (a INT) ENGINE=InnoDB; INSERT INTO t VALUES (1); diff --git a/mysql-test/suite/innodb/r/undo_upgrade.result b/mysql-test/suite/innodb/r/undo_upgrade.result index f2eca9e2..0f0b72f2 100644 --- a/mysql-test/suite/innodb/r/undo_upgrade.result +++ b/mysql-test/suite/innodb/r/undo_upgrade.result @@ -62,3 +62,27 @@ SELECT @@global.innodb_undo_tablespaces; # Should list 2 undo log tablespaces, not 4 undo001 undo002 +# +# MDEV-34200 InnoDB tries to write to read-only +# system tablespace in buf_dblwr_t::init_or_load_pages() +# +SET GLOBAL innodb_fast_shutdown=0; +# restart: --innodb_undo_tablespaces=4 +# Should list 4 undo log tablespaces +undo001 +undo002 +undo003 +undo004 +set global innodb_fast_shutdown=0; +# restart: --innodb_read_only=1 +set global innodb_fast_shutdown=0; +# restart: --innodb-data-file-path=ibdata1:1M:autoextend --innodb_undo_directory=MYSQLTEST_VARDIR/tmp/undo_dir --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/bugdir --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/bugdir --innodb_undo_tablespaces=3 +# Should list 3 undo log tablespaces +undo001 +undo002 +undo003 +set global innodb_fast_shutdown=0; +# restart: --innodb-data-file-path=ibdata1:1M:autoextend --innodb_undo_directory=MYSQLTEST_VARDIR/tmp/undo_dir --innodb-data-home-dir=MYSQLTEST_VARDIR/tmp/bugdir_1 --innodb-log-group-home-dir=MYSQLTEST_VARDIR/tmp/bugdir_1 --innodb_undo_tablespaces=0 +# Shouldn't list 0 undo log tablespaces +set global innodb_fast_shutdown=0; +# restart -- cgit v1.2.3