diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2022-12-14 14:42:20 +0200 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2023-01-11 17:55:56 +0200 |
| commit | e581396b7aea94485580d2c9edaa9c5df647f2b7 (patch) | |
| tree | 7fe8c75c705fcffd00cfbfeb738c6403f0eda163 | |
| parent | ae79cedf4b155a028655c5144e178e3993abdd94 (diff) | |
| download | mariadb-git-e581396b7aea94485580d2c9edaa9c5df647f2b7.tar.gz | |
MDEV-29983 Deprecate innodb_file_per_table
Before commit 6112853cdab2770e92f9cfefdfef9c0a14b71cb7 in MySQL 4.1.1
introduced the parameter innodb_file_per_table, all InnoDB data was
written to the InnoDB system tablespace (often named ibdata1).
A serious design problem is that once the system tablespace has grown to
some size, it cannot shrink even if the data inside it has been deleted.
There are also other design problems, such as the server hang MDEV-29930
that should only be possible when using innodb_file_per_table=0 and
innodb_undo_tablespaces=0 (storing both tables and undo logs in the
InnoDB system tablespace).
The parameter innodb_change_buffering was deprecated
in commit b5852ffbeebc3000982988383daeefb0549e058a.
Starting with commit baf276e6d4a44fe7cdf3b435c0153da0a42af2b6
(MDEV-19229) the number of innodb_undo_tablespaces can be increased,
so that the undo logs can be moved out of the system tablespace
of an existing installation.
If all these things (tables, undo logs, and the change buffer) are
removed from the InnoDB system tablespace, the only variable-size
data structure inside it is the InnoDB data dictionary.
DDL operations on .ibd files was optimized in
commit 86dc7b4d4cfe15a2d37f8b5f60c4fce5dba9491d (MDEV-24626).
That should have removed any thinkable performance advantage of
using innodb_file_per_table=0.
Since there should be no benefit of setting innodb_file_per_table=0,
the parameter should be deprecated. Starting with MySQL 5.6 and
MariaDB Server 10.0, the default value is innodb_file_per_table=1.
136 files changed, 170 insertions, 419 deletions
diff --git a/mysql-test/main/bug46760-master.opt b/mysql-test/main/bug46760-master.opt index 2d7be7fb9b1..5b4331aa7a6 100644 --- a/mysql-test/main/bug46760-master.opt +++ b/mysql-test/main/bug46760-master.opt @@ -1,2 +1 @@ --loose-innodb-lock-wait-timeout=2 ---loose-innodb-file-per-table diff --git a/mysql-test/main/column_compression_parts.test b/mysql-test/main/column_compression_parts.test index 4c77a7308f7..8dfb304173b 100644 --- a/mysql-test/main/column_compression_parts.test +++ b/mysql-test/main/column_compression_parts.test @@ -141,7 +141,6 @@ CREATE TABLE t1 (a BLOB COMPRESSED) CREATE TABLE t1 (a VARCHAR(200) COMPRESSED) PARTITION BY KEY(a) partitions 30; ALTER TABLE t1 COALESCE PARTITION 20; -#ALTER TABLE t1 ADD PARTITION (PARTITION pm TABLESPACE = `innodb_file_per_table`); --mdev MDEV-13584 ALTER TABLE t1 ADD PARTITION (PARTITION pm); CREATE TABLE t2 like t1; ALTER TABLE t2 REMOVE PARTITIONING; diff --git a/mysql-test/main/crash_commit_before-master.opt b/mysql-test/main/crash_commit_before-master.opt index f464a1013d7..d1bf57fe820 100644 --- a/mysql-test/main/crash_commit_before-master.opt +++ b/mysql-test/main/crash_commit_before-master.opt @@ -1,3 +1,2 @@ --loose-skip-stack-trace --skip-core-file --default-storage-engine=MyISAM ---loose-skip-innodb-file-per-table diff --git a/mysql-test/main/flush-innodb.result b/mysql-test/main/flush-innodb.result index 2c886e4f9fc..5df79e4cc0c 100644 --- a/mysql-test/main/flush-innodb.result +++ b/mysql-test/main/flush-innodb.result @@ -8,9 +8,6 @@ DROP TABLE t1; # WL#6168: FLUSH TABLES ... FOR EXPORT -- parser # -# Requires innodb_file_per_table -SET @old_innodb_file_per_table= @@GLOBAL.innodb_file_per_table; -SET GLOBAL innodb_file_per_table= 1; # new "EXPORT" keyword is a valid user variable name: SET @export = 10; # new "EXPORT" keyword is a valid SP parameter name: @@ -300,6 +297,4 @@ DROP TABLE t1; connection con1; disconnect con1; connection default; -# Reset innodb_file_per_table -SET GLOBAL innodb_file_per_table= @old_innodb_file_per_table; # End of 5.6 tests diff --git a/mysql-test/main/flush-innodb.test b/mysql-test/main/flush-innodb.test index 7665ae5e077..fcb0608373e 100644 --- a/mysql-test/main/flush-innodb.test +++ b/mysql-test/main/flush-innodb.test @@ -18,10 +18,6 @@ DROP TABLE t1; --echo # --echo ---echo # Requires innodb_file_per_table -SET @old_innodb_file_per_table= @@GLOBAL.innodb_file_per_table; -SET GLOBAL innodb_file_per_table= 1; - --echo # new "EXPORT" keyword is a valid user variable name: SET @export = 10; @@ -432,7 +428,4 @@ DROP TABLE t1; --source include/wait_until_disconnected.inc --connection default ---echo # Reset innodb_file_per_table -SET GLOBAL innodb_file_per_table= @old_innodb_file_per_table; - --echo # End of 5.6 tests diff --git a/mysql-test/main/partition_exchange.result b/mysql-test/main/partition_exchange.result index b7c9be3480e..f68267c0f55 100644 --- a/mysql-test/main/partition_exchange.result +++ b/mysql-test/main/partition_exchange.result @@ -1231,7 +1231,7 @@ DROP TABLE t, t2, tp; # failed during EXCHANGE PARTITION with different TABLESPACE. # CREATE TABLE t1 (a VARCHAR(200)) PARTITION BY KEY(a) partitions 10; -ALTER TABLE t1 ADD PARTITION (PARTITION pm TABLESPACE = `innodb_file_per_table`); +ALTER TABLE t1 ADD PARTITION (PARTITION pm); CREATE TABLE t2 like t1; ALTER TABLE t2 REMOVE PARTITIONING; ALTER TABLE t1 EXCHANGE PARTITION pm WITH TABLE t2; diff --git a/mysql-test/main/partition_exchange.test b/mysql-test/main/partition_exchange.test index 82ccc29e00e..e35d8e1f9d1 100644 --- a/mysql-test/main/partition_exchange.test +++ b/mysql-test/main/partition_exchange.test @@ -525,7 +525,7 @@ DROP TABLE t, t2, tp; --echo # failed during EXCHANGE PARTITION with different TABLESPACE. --echo # CREATE TABLE t1 (a VARCHAR(200)) PARTITION BY KEY(a) partitions 10; -ALTER TABLE t1 ADD PARTITION (PARTITION pm TABLESPACE = `innodb_file_per_table`); +ALTER TABLE t1 ADD PARTITION (PARTITION pm); CREATE TABLE t2 like t1; ALTER TABLE t2 REMOVE PARTITIONING; # The following works as table spaces are not checked anymore diff --git a/mysql-test/main/partition_innodb_plugin.result b/mysql-test/main/partition_innodb_plugin.result index 8211f0aac89..25b69212e5b 100644 --- a/mysql-test/main/partition_innodb_plugin.result +++ b/mysql-test/main/partition_innodb_plugin.result @@ -42,6 +42,8 @@ SET @old_innodb_file_per_table = @@global.innodb_file_per_table; SET @old_innodb_strict_mode = @@global.innodb_strict_mode; SET @@global.innodb_file_per_table = ON, @@global.innodb_strict_mode = ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release connect con1,localhost,root,,; CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, @@ -62,6 +64,8 @@ t1 CREATE TABLE `t1` ( PARTITION BY HASH (`id`) PARTITIONS 1 SET GLOBAL innodb_file_per_table = OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release disconnect con1; connect con2,localhost,root,,; LOCK TABLE t1 WRITE; @@ -104,6 +108,8 @@ disconnect con2; connection default; SET @@global.innodb_strict_mode = @old_innodb_strict_mode; SET @@global.innodb_file_per_table = @old_innodb_file_per_table; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SET @save_detect= @@GLOBAL.innodb_deadlock_detect; SET @save_report= @@GLOBAL.innodb_deadlock_report; SET GLOBAL innodb_deadlock_detect=ON; diff --git a/mysql-test/suite/binlog_encryption/rpl_sync-master.opt b/mysql-test/suite/binlog_encryption/rpl_sync-master.opt index 04b06bfa0f2..96f0ce3f36c 100644 --- a/mysql-test/suite/binlog_encryption/rpl_sync-master.opt +++ b/mysql-test/suite/binlog_encryption/rpl_sync-master.opt @@ -1,2 +1 @@ --default-storage-engine=MyISAM ---loose-innodb-file-per-table=0 diff --git a/mysql-test/suite/binlog_encryption/rpl_sync-slave.opt b/mysql-test/suite/binlog_encryption/rpl_sync-slave.opt index 795330535c1..d1a481cd37d 100644 --- a/mysql-test/suite/binlog_encryption/rpl_sync-slave.opt +++ b/mysql-test/suite/binlog_encryption/rpl_sync-slave.opt @@ -1,2 +1,2 @@ ---sync-relay-log-info=1 --relay-log-recovery=1 --default-storage-engine=MyISAM --loose-innodb-file-per-table=0 +--sync-relay-log-info=1 --relay-log-recovery=1 --default-storage-engine=MyISAM --skip-core-file --skip-slave-start diff --git a/mysql-test/suite/encryption/r/encrypt_and_grep.result b/mysql-test/suite/encryption/r/encrypt_and_grep.result index 72d612eeac8..e52ec0e453f 100644 --- a/mysql-test/suite/encryption/r/encrypt_and_grep.result +++ b/mysql-test/suite/encryption/r/encrypt_and_grep.result @@ -1,4 +1,3 @@ -SET GLOBAL innodb_file_per_table = ON; create table t1 (a varchar(255)) engine=innodb encrypted=yes; create table t2 (a varchar(255)) engine=innodb; show warnings; diff --git a/mysql-test/suite/encryption/r/innochecksum.result b/mysql-test/suite/encryption/r/innochecksum.result index 7c68164e52a..dc0bf63a71e 100644 --- a/mysql-test/suite/encryption/r/innochecksum.result +++ b/mysql-test/suite/encryption/r/innochecksum.result @@ -1,4 +1,3 @@ -SET GLOBAL innodb_file_per_table = ON; set global innodb_compression_algorithm = 1; # Create and populate a tables CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=4; diff --git a/mysql-test/suite/encryption/r/innodb-bad-key-change.result b/mysql-test/suite/encryption/r/innodb-bad-key-change.result index e2034f14e31..eb114fcf6fc 100644 --- a/mysql-test/suite/encryption/r/innodb-bad-key-change.result +++ b/mysql-test/suite/encryption/r/innodb-bad-key-change.result @@ -9,7 +9,6 @@ call mtr.add_suppression("File '.*mysql-test.std_data.keysbad3\\.txt' not found" call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); # Start server with keys2.txt # restart: --file-key-management-filename=MYSQL_TEST_DIR/std_data/keys2.txt -SET GLOBAL innodb_file_per_table = ON; CREATE TABLE t1 (c VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=2; INSERT INTO t1 VALUES ('foobar'); ALTER TABLE t1 ADD COLUMN c2 INT; diff --git a/mysql-test/suite/encryption/r/innodb-bad-key-change2.result b/mysql-test/suite/encryption/r/innodb-bad-key-change2.result index 3cda2bd537b..ab67b6fedad 100644 --- a/mysql-test/suite/encryption/r/innodb-bad-key-change2.result +++ b/mysql-test/suite/encryption/r/innodb-bad-key-change2.result @@ -8,7 +8,6 @@ call mtr.add_suppression("InnoDB: Cannot delete tablespace .* because it is not call mtr.add_suppression("InnoDB: ALTER TABLE `test`\\.`t1` DISCARD TABLESPACE failed to find tablespace"); call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); # restart: --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=MYSQL_TEST_DIR/std_data/keys2.txt -SET GLOBAL innodb_file_per_table = ON; CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=4; INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); diff --git a/mysql-test/suite/encryption/r/innodb-bad-key-change4.result b/mysql-test/suite/encryption/r/innodb-bad-key-change4.result index e808d50b544..9983c26c9d7 100644 --- a/mysql-test/suite/encryption/r/innodb-bad-key-change4.result +++ b/mysql-test/suite/encryption/r/innodb-bad-key-change4.result @@ -5,7 +5,6 @@ call mtr.add_suppression("Couldn't load plugins from 'file_key_management"); call mtr.add_suppression("Table .*t1.* is corrupted"); call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); # restart: --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=MYSQL_TEST_DIR/std_data/keys2.txt -SET GLOBAL innodb_file_per_table = ON; CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=4; INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); diff --git a/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result b/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result index 7b97eb6b5bd..f88f18d0928 100644 --- a/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result +++ b/mysql-test/suite/encryption/r/innodb-checksum-algorithm.result @@ -1,8 +1,6 @@ -SET @saved_file_per_table = @@global.innodb_file_per_table; SET @saved_encrypt_tables = @@global.innodb_encrypt_tables; SET @saved_encryption_threads = @@global.innodb_encryption_threads; SET @saved_encryption_key_id = @@global.innodb_default_encryption_key_id; -SET GLOBAL innodb_file_per_table = ON; SET GLOBAL innodb_encrypt_tables = ON; SET GLOBAL innodb_encryption_threads = 4; SET GLOBAL innodb_default_encryption_key_id=4; @@ -92,7 +90,6 @@ test.t check status OK test.tpe check status OK test.tp check status OK DROP TABLE tce, tc, te, t, tpe, tp; -SET GLOBAL innodb_file_per_table = @saved_file_per_table; SET GLOBAL innodb_encrypt_tables = @saved_encrypt_tables; SET GLOBAL innodb_encryption_threads = @saved_encryption_threads; SET GLOBAL innodb_default_encryption_key_id = @saved_encryption_key_id; diff --git a/mysql-test/suite/encryption/r/innodb-compressed-blob.result b/mysql-test/suite/encryption/r/innodb-compressed-blob.result index 0dc873b99ab..bb87d171601 100644 --- a/mysql-test/suite/encryption/r/innodb-compressed-blob.result +++ b/mysql-test/suite/encryption/r/innodb-compressed-blob.result @@ -4,7 +4,6 @@ call mtr.add_suppression("InnoDB: Unable to decompress ..test.t[1-3]\\.ibd\\[pag call mtr.add_suppression("InnoDB: Table `test`\\.`t[12]` is corrupted"); # Restart mysqld --file-key-management-filename=keys2.txt # restart: --file-key-management-filename=MYSQL_TEST_DIR/std_data/keys2.txt -SET GLOBAL innodb_file_per_table = ON; set GLOBAL innodb_default_encryption_key_id=4; create table t1(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed; create table t2(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes; diff --git a/mysql-test/suite/encryption/r/innodb-force-corrupt.result b/mysql-test/suite/encryption/r/innodb-force-corrupt.result index ad75df952b3..219dbd7cfc3 100644 --- a/mysql-test/suite/encryption/r/innodb-force-corrupt.result +++ b/mysql-test/suite/encryption/r/innodb-force-corrupt.result @@ -2,7 +2,6 @@ call mtr.add_suppression("InnoDB: Table `test`\\.`t[13]` (has an unreadable root call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=\\d+, page number=[36]\\] in file .*test.t[123]\\.ibd looks corrupted; key_version="); call mtr.add_suppression("\\[ERROR\\] InnoDB: We detected index corruption in an InnoDB type table"); call mtr.add_suppression("\\[ERROR\\] (mysqld|mariadbd).*: Index for table 't2' is corrupt; try to repair it"); -SET GLOBAL innodb_file_per_table = ON; set global innodb_compression_algorithm = 1; # Create and populate tables to be corrupted CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT,c char(200)) ENGINE=InnoDB encrypted=yes; diff --git a/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result b/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result index 4e816bea43b..4eb203d2787 100644 --- a/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result +++ b/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result @@ -1,4 +1,3 @@ -SET GLOBAL innodb_file_per_table = ON; set global innodb_compression_algorithm = 1; create database enctests; use enctests; diff --git a/mysql-test/suite/encryption/r/innodb-redo-badkey.result b/mysql-test/suite/encryption/r/innodb-redo-badkey.result index 34fd043a7bd..6b8f6aa11da 100644 --- a/mysql-test/suite/encryption/r/innodb-redo-badkey.result +++ b/mysql-test/suite/encryption/r/innodb-redo-badkey.result @@ -14,7 +14,6 @@ call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE faile call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); # restart: --file-key-management-filename=MYSQL_TEST_DIR/std_data/keys2.txt # Wait max 10 min for key encryption threads to encrypt all spaces -SET GLOBAL innodb_file_per_table = ON; create table t1(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes encryption_key_id=4; create table t2(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb row_format=compressed; create table t3(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb encrypted=yes encryption_key_id=4; diff --git a/mysql-test/suite/encryption/r/innodb-redo-nokeys.result b/mysql-test/suite/encryption/r/innodb-redo-nokeys.result index 9f9cc852898..fd6ce1d979a 100644 --- a/mysql-test/suite/encryption/r/innodb-redo-nokeys.result +++ b/mysql-test/suite/encryption/r/innodb-redo-nokeys.result @@ -11,7 +11,6 @@ call mtr.add_suppression("InnoDB: Missing FILE_CHECKPOINT"); call mtr.add_suppression("InnoDB: Log scan aborted at LSN"); call mtr.add_suppression("InnoDB: Set innodb_force_recovery=1 to ignore corruption"); # restart: --file-key-management-filename=MYSQL_TEST_DIR/std_data/keys2.txt -SET GLOBAL innodb_file_per_table = ON; create table t1(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes encryption_key_id=20; create table t2(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes; create table t3(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb encrypted=yes encryption_key_id=20; diff --git a/mysql-test/suite/encryption/r/innodb-remove-encryption.result b/mysql-test/suite/encryption/r/innodb-remove-encryption.result index e241d213c8e..22207f8184a 100644 --- a/mysql-test/suite/encryption/r/innodb-remove-encryption.result +++ b/mysql-test/suite/encryption/r/innodb-remove-encryption.result @@ -1,4 +1,6 @@ set global innodb_file_per_table=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release call mtr.add_suppression("(mysqld|mariadbd).*: file-key-management-filename is not set"); call mtr.add_suppression("Plugin 'file_key_management' init function returned error."); call mtr.add_suppression("Plugin 'file_key_management' registration as a ENCRYPTION failed."); diff --git a/mysql-test/suite/encryption/r/innodb_onlinealter_encryption.result b/mysql-test/suite/encryption/r/innodb_onlinealter_encryption.result index d6e32989c09..3a28082751b 100644 --- a/mysql-test/suite/encryption/r/innodb_onlinealter_encryption.result +++ b/mysql-test/suite/encryption/r/innodb_onlinealter_encryption.result @@ -41,7 +41,6 @@ NOT FOUND /mangled/ in t6.ibd # t7 ... on expecting NOT FOUND NOT FOUND /mysql/ in t7.ibd # restart -SET GLOBAL innodb_file_per_table = ON; ALTER TABLE t1 ADD COLUMN b int default 2; ALTER TABLE t2 ADD COLUMN b int default 2; ALTER TABLE t7 ADD COLUMN b int default 2; diff --git a/mysql-test/suite/encryption/t/encrypt_and_grep.test b/mysql-test/suite/encryption/t/encrypt_and_grep.test index 687f14e8a55..485a3eb2ec8 100644 --- a/mysql-test/suite/encryption/t/encrypt_and_grep.test +++ b/mysql-test/suite/encryption/t/encrypt_and_grep.test @@ -12,8 +12,6 @@ --let t3_IBD = $MYSQLD_DATADIR/test/t3.ibd --let SEARCH_RANGE = 10000000 -SET GLOBAL innodb_file_per_table = ON; - create table t1 (a varchar(255)) engine=innodb encrypted=yes; create table t2 (a varchar(255)) engine=innodb; show warnings; diff --git a/mysql-test/suite/encryption/t/innochecksum.test b/mysql-test/suite/encryption/t/innochecksum.test index 516bc0733d9..358a6a0fcca 100644 --- a/mysql-test/suite/encryption/t/innochecksum.test +++ b/mysql-test/suite/encryption/t/innochecksum.test @@ -26,7 +26,6 @@ call mtr.add_suppression("InnoDB: Crash recovery is broken due to insufficient i --enable_query_log let $checksum_algorithm = `SELECT @@innodb_checksum_algorithm`; -SET GLOBAL innodb_file_per_table = ON; # zlib set global innodb_compression_algorithm = 1; diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change.test b/mysql-test/suite/encryption/t/innodb-bad-key-change.test index 05a3b5f4d06..5eefded9ae1 100644 --- a/mysql-test/suite/encryption/t/innodb-bad-key-change.test +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change.test @@ -23,8 +23,6 @@ call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space=" -- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt -- source include/restart_mysqld.inc -SET GLOBAL innodb_file_per_table = ON; - CREATE TABLE t1 (c VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=2; INSERT INTO t1 VALUES ('foobar'); ALTER TABLE t1 ADD COLUMN c2 INT; diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change2.test b/mysql-test/suite/encryption/t/innodb-bad-key-change2.test index 21a9ddb217d..a6cc581e448 100644 --- a/mysql-test/suite/encryption/t/innodb-bad-key-change2.test +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change2.test @@ -23,8 +23,6 @@ call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space=" --let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt --source include/restart_mysqld.inc -SET GLOBAL innodb_file_per_table = ON; - CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=4; INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change4.test b/mysql-test/suite/encryption/t/innodb-bad-key-change4.test index c37eb365e7c..b041c988851 100644 --- a/mysql-test/suite/encryption/t/innodb-bad-key-change4.test +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change4.test @@ -19,8 +19,6 @@ call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space=" --let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt --source include/restart_mysqld.inc -SET GLOBAL innodb_file_per_table = ON; - CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=4; INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); diff --git a/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test b/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test index 157fb25b185..2de7c171981 100644 --- a/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test +++ b/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test @@ -2,12 +2,10 @@ -- source include/innodb_page_size.inc -- source include/have_file_key_management_plugin.inc -SET @saved_file_per_table = @@global.innodb_file_per_table; SET @saved_encrypt_tables = @@global.innodb_encrypt_tables; SET @saved_encryption_threads = @@global.innodb_encryption_threads; SET @saved_encryption_key_id = @@global.innodb_default_encryption_key_id; -SET GLOBAL innodb_file_per_table = ON; SET GLOBAL innodb_encrypt_tables = ON; SET GLOBAL innodb_encryption_threads = 4; @@ -77,7 +75,6 @@ update tp set b=substr(b,1); CHECK TABLE tce, tc, te, t, tpe, tp; DROP TABLE tce, tc, te, t, tpe, tp; -SET GLOBAL innodb_file_per_table = @saved_file_per_table; SET GLOBAL innodb_encrypt_tables = @saved_encrypt_tables; SET GLOBAL innodb_encryption_threads = @saved_encryption_threads; SET GLOBAL innodb_default_encryption_key_id = @saved_encryption_key_id; diff --git a/mysql-test/suite/encryption/t/innodb-compressed-blob.test b/mysql-test/suite/encryption/t/innodb-compressed-blob.test index 12d061a852f..d3d53e2c41a 100644 --- a/mysql-test/suite/encryption/t/innodb-compressed-blob.test +++ b/mysql-test/suite/encryption/t/innodb-compressed-blob.test @@ -13,8 +13,6 @@ call mtr.add_suppression("InnoDB: Table `test`\\.`t[12]` is corrupted"); -- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt -- source include/restart_mysqld.inc -SET GLOBAL innodb_file_per_table = ON; - set GLOBAL innodb_default_encryption_key_id=4; create table t1(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed; create table t2(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes; diff --git a/mysql-test/suite/encryption/t/innodb-force-corrupt.test b/mysql-test/suite/encryption/t/innodb-force-corrupt.test index 51771f1e14b..73fa0cde0cf 100644 --- a/mysql-test/suite/encryption/t/innodb-force-corrupt.test +++ b/mysql-test/suite/encryption/t/innodb-force-corrupt.test @@ -12,7 +12,6 @@ call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=\\d+, page nu call mtr.add_suppression("\\[ERROR\\] InnoDB: We detected index corruption in an InnoDB type table"); call mtr.add_suppression("\\[ERROR\\] (mysqld|mariadbd).*: Index for table 't2' is corrupt; try to repair it"); -SET GLOBAL innodb_file_per_table = ON; set global innodb_compression_algorithm = 1; --echo # Create and populate tables to be corrupted diff --git a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test index 96b62f7c05b..0139b9fb817 100644 --- a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test +++ b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test @@ -4,7 +4,6 @@ -- source include/not_embedded.inc let $encryption = `SELECT @@innodb_encrypt_tables`; -SET GLOBAL innodb_file_per_table = ON; # zlib set global innodb_compression_algorithm = 1; diff --git a/mysql-test/suite/encryption/t/innodb-redo-badkey.test b/mysql-test/suite/encryption/t/innodb-redo-badkey.test index 393ca4ad375..de6d7f2f253 100644 --- a/mysql-test/suite/encryption/t/innodb-redo-badkey.test +++ b/mysql-test/suite/encryption/t/innodb-redo-badkey.test @@ -31,8 +31,6 @@ call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space=" --let $wait_condition=SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 --source include/wait_condition.inc -SET GLOBAL innodb_file_per_table = ON; - create table t1(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes encryption_key_id=4; create table t2(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb row_format=compressed; create table t3(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb encrypted=yes encryption_key_id=4; diff --git a/mysql-test/suite/encryption/t/innodb-redo-nokeys.test b/mysql-test/suite/encryption/t/innodb-redo-nokeys.test index 0aaed201d43..2624bb6ff9c 100644 --- a/mysql-test/suite/encryption/t/innodb-redo-nokeys.test +++ b/mysql-test/suite/encryption/t/innodb-redo-nokeys.test @@ -19,8 +19,6 @@ call mtr.add_suppression("InnoDB: Set innodb_force_recovery=1 to ignore corrupti -- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt -- source include/restart_mysqld.inc -SET GLOBAL innodb_file_per_table = ON; - create table t1(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes encryption_key_id=20; create table t2(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes; create table t3(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb encrypted=yes encryption_key_id=20; diff --git a/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test b/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test index 9f61bf118aa..dc6d1e6f93c 100644 --- a/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test +++ b/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test @@ -75,8 +75,6 @@ set autocommit=1; -- source include/start_mysqld.inc -SET GLOBAL innodb_file_per_table = ON; - ALTER TABLE t1 ADD COLUMN b int default 2; ALTER TABLE t2 ADD COLUMN b int default 2; ALTER TABLE t7 ADD COLUMN b int default 2; diff --git a/mysql-test/suite/galera/t/galera_ist_MDEV-28423.cnf b/mysql-test/suite/galera/t/galera_ist_MDEV-28423.cnf index 691e52208b1..3d43138f3c7 100644 --- a/mysql-test/suite/galera/t/galera_ist_MDEV-28423.cnf +++ b/mysql-test/suite/galera/t/galera_ist_MDEV-28423.cnf @@ -3,7 +3,6 @@ [mysqld.1] # server-id=101 #wsrep-debug=1 -innodb_file_per_table innodb_autoinc_lock_mode=2 #wsrep_sst_method=rsync wsrep_sst_method=mariabackup @@ -20,7 +19,6 @@ log_bin=binlog [mysqld.2] # server-id=102 #wsrep-debug=1 -innodb_file_per_table innodb_autoinc_lock_mode=2 #wsrep_sst_method=rsync wsrep_sst_method=mariabackup diff --git a/mysql-test/suite/galera/t/galera_ist_MDEV-28583.cnf b/mysql-test/suite/galera/t/galera_ist_MDEV-28583.cnf index 3835cd02a41..6fc6e3421cb 100644 --- a/mysql-test/suite/galera/t/galera_ist_MDEV-28583.cnf +++ b/mysql-test/suite/galera/t/galera_ist_MDEV-28583.cnf @@ -3,7 +3,6 @@ [mysqld.1] # server-id=101 #wsrep-debug=1 -innodb_file_per_table innodb_autoinc_lock_mode=2 wsrep_sst_method=rsync #wsrep_sst_method=mariabackup @@ -20,7 +19,6 @@ log_bin=binlog [mysqld.2] # server-id=102 #wsrep-debug=1 -innodb_file_per_table innodb_autoinc_lock_mode=2 wsrep_sst_method=rsync #wsrep_sst_method=mariabackup diff --git a/mysql-test/suite/galera/t/galera_load_data.cnf b/mysql-test/suite/galera/t/galera_load_data.cnf index 43cc352c020..3385fbac1f6 100644 --- a/mysql-test/suite/galera/t/galera_load_data.cnf +++ b/mysql-test/suite/galera/t/galera_load_data.cnf @@ -2,7 +2,6 @@ [mysqld.1] secure-file-priv = "" -innodb_file_per_table = ON innodb_stats_persistent=ON innodb_stats_auto_recalc=ON innodb_stats_persistent_sample_pages=20 @@ -11,7 +10,6 @@ skip-innodb-read-only-compressed [mysqld.2] secure-file-priv = "" -innodb_file_per_table = ON innodb_stats_persistent=ON innodb_stats_auto_recalc=ON innodb_stats_persistent_sample_pages=20 diff --git a/mysql-test/suite/galera/t/galera_sst_encrypted.cnf b/mysql-test/suite/galera/t/galera_sst_encrypted.cnf index e10c3e9f215..596bb95ac6a 100644 --- a/mysql-test/suite/galera/t/galera_sst_encrypted.cnf +++ b/mysql-test/suite/galera/t/galera_sst_encrypted.cnf @@ -7,7 +7,6 @@ loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys.txt loose-file-key-management-encryption-algorithm=aes_cbc wsrep-debug=1 innodb_encryption_threads = 4 -innodb_file_per_table=1 wsrep_sst_method=rsync [mysqld.1] diff --git a/mysql-test/suite/galera/t/galera_sst_mariabackup_table_options.cnf b/mysql-test/suite/galera/t/galera_sst_mariabackup_table_options.cnf index d3fff4fcb0b..857a4101406 100644 --- a/mysql-test/suite/galera/t/galera_sst_mariabackup_table_options.cnf +++ b/mysql-test/suite/galera/t/galera_sst_mariabackup_table_options.cnf @@ -4,7 +4,6 @@ wsrep_sst_method=mariabackup wsrep_sst_auth="root:" wsrep_debug=1 -innodb-file-per-table=ON [mysqld.1] wsrep_provider_options='base_port=@mysqld.1.#galera_port;gcache.size=1;pc.ignore_sb=true' diff --git a/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc b/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc index 3c10517933f..438b7b17d51 100644 --- a/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc +++ b/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc @@ -35,8 +35,6 @@ if ($row_format != 'COMPRESSED') if ($row_format == 'COMPRESSED') { - SET GLOBAL innodb_file_per_table=1; - eval CREATE TABLE t1( class INT, id INT, @@ -176,10 +174,4 @@ SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; DROP TABLE t1; -# Restore global variables -if ($row_format == 'COMPRESSED') -{ - SET GLOBAL innodb_file_per_table=default; -} - DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc b/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc index 85466e5e4ae..dbdadd0b7f9 100644 --- a/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc +++ b/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc @@ -38,8 +38,6 @@ if ($row_format != 'COMPRESSED') if ($row_format == 'COMPRESSED') { - SET GLOBAL innodb_file_per_table=1; - eval CREATE TABLE t1( class INT, id INT, @@ -69,8 +67,6 @@ if ($row_format != 'COMPRESSED') { } if ($row_format == 'COMPRESSED') { - SET GLOBAL innodb_file_per_table=1; - eval CREATE TABLE t1( a INT PRIMARY KEY, b TEXT, @@ -124,8 +120,6 @@ if ($row_format != 'COMPRESSED') if ($row_format == 'COMPRESSED') { - SET GLOBAL innodb_file_per_table=1; - eval CREATE TABLE t1( class INT, id INT, @@ -174,8 +168,6 @@ if ($row_format != 'COMPRESSED') { } if ($row_format == 'COMPRESSED') { - SET GLOBAL innodb_file_per_table=1; - eval CREATE TABLE t1( a INT PRIMARY KEY, b TEXT, diff --git a/mysql-test/suite/innodb/include/innodb_merge_threshold_delete.inc b/mysql-test/suite/innodb/include/innodb_merge_threshold_delete.inc index 8c60cd6e230..68f34978f23 100644 --- a/mysql-test/suite/innodb/include/innodb_merge_threshold_delete.inc +++ b/mysql-test/suite/innodb/include/innodb_merge_threshold_delete.inc @@ -1,6 +1,6 @@ # # Test to cause merge of the pages (by deleting) -# test/tab1 should be created already with innodb_file_per_table=ON +# test/tab1 should be created already # The definition is intended to be based on # "create table tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB;" # diff --git a/mysql-test/suite/innodb/include/innodb_merge_threshold_secondary.inc b/mysql-test/suite/innodb/include/innodb_merge_threshold_secondary.inc index 8e821365e3f..d49272c4087 100644 --- a/mysql-test/suite/innodb/include/innodb_merge_threshold_secondary.inc +++ b/mysql-test/suite/innodb/include/innodb_merge_threshold_secondary.inc @@ -1,6 +1,6 @@ # # Test to cause merge of the pages (at secondary index by deleting) -# test/tab1 should be created already with innodb_file_per_table=ON +# test/tab1 should be created already # The definition is intended to be based on # "create table tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;" # "create index index1 on tab1(b(750));" diff --git a/mysql-test/suite/innodb/include/innodb_merge_threshold_update.inc b/mysql-test/suite/innodb/include/innodb_merge_threshold_update.inc index 61e961ac6ae..ae720e0488d 100644 --- a/mysql-test/suite/innodb/include/innodb_merge_threshold_update.inc +++ b/mysql-test/suite/innodb/include/innodb_merge_threshold_update.inc @@ -1,6 +1,6 @@ # # Test to cause merge of the pages (by updating to smaller) -# test/tab1 should be created already with innodb_file_per_table=ON +# test/tab1 should be created already # The definition is intended to be based on # "create table tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB;" # diff --git a/mysql-test/suite/innodb/r/alter_kill.result b/mysql-test/suite/innodb/r/alter_kill.result index 0283d1079e1..037b06fffbc 100644 --- a/mysql-test/suite/innodb/r/alter_kill.result +++ b/mysql-test/suite/innodb/r/alter_kill.result @@ -1,7 +1,6 @@ # # Bug#16720368 INNODB CRASHES ON BROKEN #SQL*.IBD FILE AT STARTUP # -SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_purge_rseg_truncate_frequency=1; CREATE TABLE bug16720368_1 (a INT PRIMARY KEY) ENGINE=InnoDB; connect con1,localhost,root; @@ -30,7 +29,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 3d071584256..65b01d89d91 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 1fdc2011a24..e4fee341964 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/dropdb_cs.result b/mysql-test/suite/innodb/r/dropdb_cs.result index 59f02c74a04..3837bf9e3f5 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/index_tree_operation.result b/mysql-test/suite/innodb/r/index_tree_operation.result index 29660962e0c..92565333a93 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-fkcheck.result b/mysql-test/suite/innodb/r/innodb-fkcheck.result index f86ba50597f..357d8aa11c1 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-online.result b/mysql-test/suite/innodb/r/innodb-index-online.result index 7138c5d7a18..6404a3812b3 100644 --- a/mysql-test/suite/innodb/r/innodb-index-online.result +++ b/mysql-test/suite/innodb/r/innodb-index-online.result @@ -1,6 +1,4 @@ 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,''); @@ -540,6 +538,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-table-online.result b/mysql-test/suite/innodb/r/innodb-table-online.result index 5a8b3d24d75..8ac0d4d41fa 100644 --- a/mysql-test/suite/innodb/r/innodb-table-online.result +++ b/mysql-test/suite/innodb/r/innodb-table-online.result @@ -1,8 +1,6 @@ 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,''); @@ -472,6 +470,5 @@ DROP TABLE t1; connection default; 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 e7af2d9469e..ff0f8b2acc8 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 @@ -943,4 +938,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 7bcc57c5bd5..819ca949c88 100644 --- a/mysql-test/suite/innodb/r/innodb-wl5522.result +++ b/mysql-test/suite/innodb/r/innodb-wl5522.result @@ -120,7 +120,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 @@ -285,6 +284,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; @@ -302,6 +303,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 4d6ac474da8..84f3d7811c2 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, @@ -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_bug14147491.result b/mysql-test/suite/innodb/r/innodb_bug14147491.result index 533eaeafeb8..b33b7f0d587 100644 --- a/mysql-test/suite/innodb/r/innodb_bug14147491.result +++ b/mysql-test/suite/innodb/r/innodb_bug14147491.result @@ -2,7 +2,6 @@ SET GLOBAL innodb_purge_rseg_truncate_frequency = 1; 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_bug56947.result b/mysql-test/suite/innodb/r/innodb_bug56947.result index aa922776f7b..a4ed3bfcfaa 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_bug68148.result b/mysql-test/suite/innodb/r/innodb_bug68148.result index 25bf7f58eb9..1c79f881bef 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 ec7ce044cb7..060e9008f3f 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 295a9f1bed8..debb5d0f14a 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 b48207d4497..bdc40d1a070 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/log_data_file_size.result b/mysql-test/suite/innodb/r/log_data_file_size.result index d33752b089c..ddb8e694e40 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 766122959ab..7ac212916de 100644 --- a/mysql-test/suite/innodb/r/log_file_name.result +++ b/mysql-test/suite/innodb/r/log_file_name.result @@ -1,4 +1,3 @@ -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/mvcc.result b/mysql-test/suite/innodb/r/mvcc.result index 215b2165b8c..3e6b0ad35db 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/row_format_redundant.result b/mysql-test/suite/innodb/r/row_format_redundant.result index b798832e96f..e7e15983dfd 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; # # 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; diff --git a/mysql-test/suite/innodb/r/table_flags.result b/mysql-test/suite/innodb/r/table_flags.result index 779990351c6..e421126d2eb 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/t/alter_kill.test b/mysql-test/suite/innodb/t/alter_kill.test index 277d9b4e71b..fdff21e1ec0 100644 --- a/mysql-test/suite/innodb/t/alter_kill.test +++ b/mysql-test/suite/innodb/t/alter_kill.test @@ -25,7 +25,6 @@ call mtr.add_suppression("Table .*bug16720368.* is corrupted"); -- echo # Bug#16720368 INNODB CRASHES ON BROKEN #SQL*.IBD FILE AT STARTUP -- echo # -SET GLOBAL innodb_file_per_table=1; SET GLOBAL innodb_purge_rseg_truncate_frequency=1; CREATE TABLE bug16720368_1 (a INT PRIMARY KEY) ENGINE=InnoDB; @@ -129,8 +128,6 @@ DROP TABLE bug16720368, bug16720368_1; -- echo # DICT_TABLE_ADD_TO_CACHE -- echo # -SET GLOBAL innodb_file_per_table=1; - CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; BEGIN; INSERT INTO t1 VALUES(42); diff --git a/mysql-test/suite/innodb/t/alter_missing_tablespace.test b/mysql-test/suite/innodb/t/alter_missing_tablespace.test index 9742adc30a6..ff93ec5f9c6 100644 --- a/mysql-test/suite/innodb/t/alter_missing_tablespace.test +++ b/mysql-test/suite/innodb/t/alter_missing_tablespace.test @@ -21,7 +21,6 @@ call mtr.add_suppression("InnoDB: ALTER TABLE `test`.`t` DISCARD TABLESPACE fail --enable_query_log let $MYSQLD_DATADIR=`select @@datadir`; -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/t/index_tree_operation.test b/mysql-test/suite/innodb/t/index_tree_operation.test index b9695db9346..e7958b2955d 100644 --- a/mysql-test/suite/innodb/t/index_tree_operation.test +++ b/mysql-test/suite/innodb/t/index_tree_operation.test @@ -9,12 +9,6 @@ # Otherwise, the follwing records 999,998,997 cause each page per record. # ---disable_query_log -SET @old_innodb_file_per_table = @@innodb_file_per_table; ---enable_query_log - -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)); @@ -68,7 +62,3 @@ WHERE s1.space = s2.space AND name = 'test/t1' AND page_type = "INDEX" ORDER BY page_number; DROP TABLE t1; - ---disable_query_log -SET GLOBAL innodb_file_per_table = @old_innodb_file_per_table; ---enable_query_log diff --git a/mysql-test/suite/innodb/t/innodb-bug-14068765.test b/mysql-test/suite/innodb/t/innodb-bug-14068765.test index c2446e0fecf..4435ac68021 100644 --- a/mysql-test/suite/innodb/t/innodb-bug-14068765.test +++ b/mysql-test/suite/innodb/t/innodb-bug-14068765.test @@ -2,7 +2,7 @@ let MYSQLD_DATADIR =`SELECT @@datadir`; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance CREATE DATABASE testdb_wl5522; CREATE TABLE testdb_wl5522.t1(col1 bit(1) , col2 boolean,col3 tinyint , col4 smallint , col5 mediumint ,col6 int , col7 bigint , col8 float (14,3) ,col9 double (14,3), col10 VARCHAR(20) CHARACTER SET utf8 , col11 TEXT CHARACTER SET binary , col12 ENUM('a','b','c') CHARACTER SET binary ,col13 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs ,col14 CHAR(20) , col15 VARBINARY (400) , col16 BINARY(40), col17 BLOB (400) , col18 int not null primary key,col19 DATE ,col20 DATETIME , col21 TIMESTAMP ,col22 TIME , col23 YEAR ) ENGINE = Innodb; diff --git a/mysql-test/suite/innodb/t/innodb-bug-14084530.test b/mysql-test/suite/innodb/t/innodb-bug-14084530.test index f27fbbe31d7..2285b86c21a 100644 --- a/mysql-test/suite/innodb/t/innodb-bug-14084530.test +++ b/mysql-test/suite/innodb/t/innodb-bug-14084530.test @@ -4,7 +4,7 @@ let MYSQLD_DATADIR =`SELECT @@datadir`; SET AUTOCOMMIT = 0; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance CREATE DATABASE testdb_wl5522; CREATE TABLE testdb_wl5522.t1 (c1 int ) engine = Innodb; diff --git a/mysql-test/suite/innodb/t/innodb-enlarge-blob.opt b/mysql-test/suite/innodb/t/innodb-enlarge-blob.opt index 2f9bf78f82e..1251c5f2aed 100644 --- a/mysql-test/suite/innodb/t/innodb-enlarge-blob.opt +++ b/mysql-test/suite/innodb/t/innodb-enlarge-blob.opt @@ -1,4 +1,3 @@ ---innodb-file-per-table --innodb-buffer-pool-size=32M --innodb-page-size=64k --innodb-strict-mode=OFF diff --git a/mysql-test/suite/innodb/t/innodb-fkcheck.test b/mysql-test/suite/innodb/t/innodb-fkcheck.test index 5ff3533fce1..bec4de7b670 100644 --- a/mysql-test/suite/innodb/t/innodb-fkcheck.test +++ b/mysql-test/suite/innodb/t/innodb-fkcheck.test @@ -4,17 +4,6 @@ # # MDEV-10083: Orphan ibd file when playing with foreign keys # ---disable_query_log -SET @start_global_fpt = @@global.innodb_file_per_table; -SET @start_global_fkc = @@global.foreign_key_checks; ---enable_query_log - -set global innodb_file_per_table = 1; - ---disable_warnings -drop table if exists b; -drop database if exists bug_fk; ---enable_warnings let $MYSQLD_DATADIR = `select @@datadir`; @@ -114,12 +103,6 @@ show warnings; # # Cleanup # ---disable_query_log -SET @@global.innodb_file_per_table = @start_global_fpt; -SET @@global.foreign_key_checks = @start_global_fkc; ---enable_query_log - ---disable_warnings -drop table if exists b; -drop database if exists bug_fk; ---enable_warnings + +drop table b; +drop database bug_fk; diff --git a/mysql-test/suite/innodb/t/innodb-index-online.test b/mysql-test/suite/innodb/t/innodb-index-online.test index ab4f5a965da..827941817f6 100644 --- a/mysql-test/suite/innodb/t/innodb-index-online.test +++ b/mysql-test/suite/innodb/t/innodb-index-online.test @@ -9,10 +9,6 @@ SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl call mtr.add_suppression("InnoDB: Warning: Small buffer pool size"); -# DISCARD TABLESPACE needs file-per-table -SET @global_innodb_file_per_table_orig = @@global.innodb_file_per_table; -SET GLOBAL innodb_file_per_table = on; - # Save the initial number of concurrent sessions. --source include/count_sessions.inc @@ -515,7 +511,6 @@ SET DEBUG_SYNC = 'RESET'; # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc -SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig; --disable_warnings SET GLOBAL innodb_monitor_enable = default; SET GLOBAL innodb_monitor_disable = default; diff --git a/mysql-test/suite/innodb/t/innodb-table-online.test b/mysql-test/suite/innodb/t/innodb-table-online.test index 45b1bc1ec8e..5f0562e11ed 100644 --- a/mysql-test/suite/innodb/t/innodb-table-online.test +++ b/mysql-test/suite/innodb/t/innodb-table-online.test @@ -11,10 +11,6 @@ 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"); -# DISCARD TABLESPACE needs file-per-table -SET @global_innodb_file_per_table_orig = @@global.innodb_file_per_table; -SET GLOBAL innodb_file_per_table = on; - # Save the initial number of concurrent sessions. --source include/count_sessions.inc @@ -447,7 +443,6 @@ disconnect con1; # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc -SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig; --disable_warnings SET GLOBAL innodb_monitor_enable = default; SET GLOBAL innodb_monitor_disable = default; diff --git a/mysql-test/suite/innodb/t/innodb-wl5522-debug.test b/mysql-test/suite/innodb/t/innodb-wl5522-debug.test index efa2b1ea403..16c5713cc7b 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5522-debug.test +++ b/mysql-test/suite/innodb/t/innodb-wl5522-debug.test @@ -35,7 +35,6 @@ FLUSH TABLES; let MYSQLD_DATADIR =`SELECT @@datadir`; let $strerrfix=/ (\(.+\))//; -SET GLOBAL innodb_file_per_table = 1; CREATE TABLE t1 (c1 INT) ENGINE = InnoDB; INSERT INTO t1 VALUES(1),(2),(3); @@ -52,8 +51,6 @@ ALTER TABLE t1 DISCARD TABLESPACE; DROP TABLE t1; -SET GLOBAL innodb_file_per_table = 1; - CREATE TABLE t1 (c1 INT) ENGINE = InnoDB; INSERT INTO t1 VALUES(1),(2),(3); @@ -66,8 +63,6 @@ ALTER TABLE t1 DISCARD TABLESPACE; DROP TABLE t1; -SET GLOBAL innodb_file_per_table = 1; - # Create the table that we will use for crash recovery (during IMPORT) CREATE TABLE t1 (c1 INT) ENGINE = Innodb; INSERT INTO t1 VALUES (1), (2), (3), (4); @@ -130,9 +125,6 @@ EOF 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; @@ -1397,5 +1389,3 @@ call mtr.add_suppression("Index for table 't1' is corrupt; try to repair it"); #cleanup --remove_file $MYSQLTEST_VARDIR/tmp/t1.cfg --remove_file $MYSQLTEST_VARDIR/tmp/t1.ibd - -SET GLOBAL INNODB_FILE_PER_TABLE=@file_per_table; diff --git a/mysql-test/suite/innodb/t/innodb-wl5522.test b/mysql-test/suite/innodb/t/innodb-wl5522.test index 19652d8e8fd..c5c61ded651 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5522.test +++ b/mysql-test/suite/innodb/t/innodb-wl5522.test @@ -98,7 +98,6 @@ if ($checksum_algorithm == "strict_full_crc32") { ALTER TABLE t2 IMPORT TABLESPACE; DROP TABLE t2; -SET GLOBAL innodb_file_per_table = 1; SELECT @@innodb_file_per_table; let MYSQLD_DATADIR =`SELECT @@datadir`; @@ -114,7 +113,7 @@ ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1; DROP TABLE t1; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB; @@ -158,7 +157,7 @@ SELECT COUNT(*) FROM t1; DROP TABLE t1; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance # Insert some more records to move the LSN forward and then drop the # table and restore CREATE TABLE t1( @@ -204,7 +203,7 @@ SELECT COUNT(*) FROM t1; DROP TABLE t1; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance # Insert some more records to move the LSN forward and then drop the # table and restore, this time the table has a secondary index too. CREATE TABLE t1( @@ -245,7 +244,7 @@ SELECT COUNT(*) FROM t1 WHERE c2 = 1; DROP TABLE t1; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance # Insert some more records to move the LSN forward and then drop the # table and restore, this time the table has a secondary index too. # Rename the index on the create so that the IMPORT fails, drop index diff --git a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test index 09c54db644f..6627b77ed9c 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5980-alter.test +++ b/mysql-test/suite/innodb/t/innodb-wl5980-alter.test @@ -6,7 +6,6 @@ --source include/have_innodb.inc --source include/have_symlink.inc -SET @innodb_file_per_table_orig=@@GLOBAL.innodb_file_per_table; LET $regexp=/FTS_[0-9a-f_]+([A-Z0-9_]+)\.([islbd]{3})/FTS_AUX_\1.\2/; # Set up some variables @@ -14,7 +13,6 @@ LET $MYSQL_DATA_DIR = `select @@datadir`; LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir'; SET default_storage_engine=InnoDB; -SET GLOBAL innodb_file_per_table=ON; SET NAMES utf8mb4; @@ -706,5 +704,3 @@ DROP TABLE tt, t1o, sys_tables, sys_indexes, sys_foreign; --list_files $MYSQL_TMP_DIR/alt_dir/test --rmdir $MYSQL_TMP_DIR/alt_dir/test --rmdir $MYSQL_TMP_DIR/alt_dir - -SET GLOBAL innodb_file_per_table = @innodb_file_per_table_orig; diff --git a/mysql-test/suite/innodb/t/innodb_bug14147491-master.opt b/mysql-test/suite/innodb/t/innodb_bug14147491-master.opt index 614dd9356f1..8434ff04c6c 100644 --- a/mysql-test/suite/innodb/t/innodb_bug14147491-master.opt +++ b/mysql-test/suite/innodb/t/innodb_bug14147491-master.opt @@ -1,4 +1,3 @@ ---innodb_file_per_table=1 --loose-skip-stack-trace --skip-core-file --loose-innodb_buffer_pool_load_at_startup=OFF diff --git a/mysql-test/suite/innodb/t/innodb_bug14147491.test b/mysql-test/suite/innodb/t/innodb_bug14147491.test index 3c37f1b7cce..4f4810f84ae 100644 --- a/mysql-test/suite/innodb/t/innodb_bug14147491.test +++ b/mysql-test/suite/innodb/t/innodb_bug14147491.test @@ -21,8 +21,6 @@ SET GLOBAL innodb_fast_shutdown=0; --echo # 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'); --disable_query_log diff --git a/mysql-test/suite/innodb/t/innodb_bug39438-master.opt b/mysql-test/suite/innodb/t/innodb_bug39438-master.opt deleted file mode 100644 index 0746d13d1c0..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug39438-master.opt +++ /dev/null @@ -1 +0,0 @@ ---loose-innodb-file-per-table=1 diff --git a/mysql-test/suite/innodb/t/innodb_bug39438.test b/mysql-test/suite/innodb/t/innodb_bug39438.test index 635d65f045b..04275197e13 100644 --- a/mysql-test/suite/innodb/t/innodb_bug39438.test +++ b/mysql-test/suite/innodb/t/innodb_bug39438.test @@ -3,10 +3,6 @@ # Bug#39438 Testcase for Bug#39436 crashes on 5.1 in fil_space_get_latch # http://bugs.mysql.com/39438 # -# This test must be run with innodb_file_per_table=1 because the crash -# only occurs if that option is turned on and DISCARD TABLESPACE only -# works with innodb_file_per_table. -# SET default_storage_engine=InnoDB; diff --git a/mysql-test/suite/innodb/t/innodb_bug56947.test b/mysql-test/suite/innodb/t/innodb_bug56947.test index ce64f1a8322..3e85e614812 100644 --- a/mysql-test/suite/innodb/t/innodb_bug56947.test +++ b/mysql-test/suite/innodb/t/innodb_bug56947.test @@ -4,7 +4,8 @@ -- source include/have_innodb.inc -- source include/have_debug.inc -SET GLOBAL innodb_file_per_table=0; +SET @save_fpt=@@GLOBAL.innodb_file_per_table; +SET GLOBAL innodb_file_per_table=OFF; create table bug56947(a int not null) engine = innodb; SET @saved_dbug = @@SESSION.debug_dbug; @@ -15,5 +16,5 @@ alter table bug56947 add unique index (a); check table bug56947; drop table bug56947; -SET @@global.innodb_file_per_table=DEFAULT; +SET GLOBAL innodb_file_per_table=@save_fpt; SET debug_dbug= @saved_dbug; diff --git a/mysql-test/suite/innodb/t/innodb_bug68148.test b/mysql-test/suite/innodb/t/innodb_bug68148.test index ab4e0311656..432e8fbf03e 100644 --- a/mysql-test/suite/innodb/t/innodb_bug68148.test +++ b/mysql-test/suite/innodb/t/innodb_bug68148.test @@ -6,8 +6,6 @@ # MDEV-8845: Table disappear after modifying FK # -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; diff --git a/mysql-test/suite/innodb/t/innodb_bulk_create_index_small.test b/mysql-test/suite/innodb/t/innodb_bulk_create_index_small.test index d04dd59f7e7..75e011a5094 100644 --- a/mysql-test/suite/innodb/t/innodb_bulk_create_index_small.test +++ b/mysql-test/suite/innodb/t/innodb_bulk_create_index_small.test @@ -80,11 +80,6 @@ SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; DROP TABLE t1; -SET GLOBAL innodb_file_per_table=default; - -# Test Compressed Table -SET GLOBAL innodb_file_per_table=1; - CREATE TABLE t1( class INT, id INT, @@ -143,6 +138,4 @@ SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; DROP TABLE t1; -SET GLOBAL innodb_file_per_table=default; - DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/t/innodb_defragment.opt b/mysql-test/suite/innodb/t/innodb_defragment.opt index aea3d480c24..12b046be786 100644 --- a/mysql-test/suite/innodb/t/innodb_defragment.opt +++ b/mysql-test/suite/innodb/t/innodb_defragment.opt @@ -1,5 +1,4 @@ --loose-innodb-buffer-pool-stats --loose-innodb-buffer-page --loose-innodb-buffer-page-lru ---innodb-file-per-table ---innodb-defragment=1
\ No newline at end of file +--innodb-defragment=1 diff --git a/mysql-test/suite/innodb/t/log_file_name.test b/mysql-test/suite/innodb/t/log_file_name.test index eb616d7f835..1945be89599 100644 --- a/mysql-test/suite/innodb/t/log_file_name.test +++ b/mysql-test/suite/innodb/t/log_file_name.test @@ -7,7 +7,6 @@ # Embedded server does not support crashing --source include/not_embedded.inc -SET GLOBAL innodb_file_per_table=ON; FLUSH TABLES; CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb/t/monitor.test b/mysql-test/suite/innodb/t/monitor.test index 9b093d41892..7d41a5507e8 100644 --- a/mysql-test/suite/innodb/t/monitor.test +++ b/mysql-test/suite/innodb/t/monitor.test @@ -258,7 +258,7 @@ drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; -# Counters are unpredictable when innodb-file-per-table is on +# Counters are unpredictable --replace_column 2 # 3 # 4 # 5 # 6 # 7 # select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, diff --git a/mysql-test/suite/innodb/t/mvcc.test b/mysql-test/suite/innodb/t/mvcc.test index 7c37718c28a..e0baf7f83d3 100644 --- a/mysql-test/suite/innodb/t/mvcc.test +++ b/mysql-test/suite/innodb/t/mvcc.test @@ -1,9 +1,6 @@ --source include/have_innodb.inc --source include/have_sequence.inc -SET @save_per_table= @@GLOBAL.innodb_file_per_table; -SET GLOBAL innodb_file_per_table= 1; - let MYSQLD_DATADIR =`SELECT @@datadir`; --echo # @@ -68,5 +65,3 @@ DELIMITER ;$$ 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/t/row_format_redundant.test b/mysql-test/suite/innodb/t/row_format_redundant.test index 6de7597e983..6ab1eabe15b 100644 --- a/mysql-test/suite/innodb/t/row_format_redundant.test +++ b/mysql-test/suite/innodb/t/row_format_redundant.test @@ -26,14 +26,11 @@ let bugdir= $MYSQLTEST_VARDIR/tmp/row_format_redundant; SET GLOBAL innodb_fast_shutdown=0; --source include/restart_mysqld.inc -SET GLOBAL innodb_file_per_table=1; - --echo # --echo # Bug#21644827 - FTS, ASSERT !SRV_READ_ONLY_MODE || M_IMPL.M_LOG_MODE == --echo # MTR_LOG_NO_REDO --echo # -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; diff --git a/mysql-test/suite/innodb/t/table_flags.test b/mysql-test/suite/innodb/t/table_flags.test index 6b2e6ee3ea6..1c3efa1fbba 100644 --- a/mysql-test/suite/innodb/t/table_flags.test +++ b/mysql-test/suite/innodb/t/table_flags.test @@ -40,7 +40,6 @@ let bugdir= $MYSQLTEST_VARDIR/tmp/table_flags; --let $restart_parameters=$d --innodb-stats-persistent=0 --source include/restart_mysqld.inc -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_fts/r/crash_recovery.result b/mysql-test/suite/innodb_fts/r/crash_recovery.result index 83e5ddaea90..52a58c7034d 100644 --- a/mysql-test/suite/innodb_fts/r/crash_recovery.result +++ b/mysql-test/suite/innodb_fts/r/crash_recovery.result @@ -161,6 +161,8 @@ DROP TABLE mdev19073, mdev19073_2; # MDEV-28706 Redundant InnoDB table fails during alter # 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 ( col_int INTEGER, col_text TEXT, col_text_1 TEXT diff --git a/mysql-test/suite/innodb_fts/r/fulltext.result b/mysql-test/suite/innodb_fts/r/fulltext.result index 32dc87daa04..cb615069067 100644 --- a/mysql-test/suite/innodb_fts/r/fulltext.result +++ b/mysql-test/suite/innodb_fts/r/fulltext.result @@ -721,6 +721,8 @@ DROP TABLE t1; # SET @save = @@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 t1 ( col_int INTEGER, col_text TEXT, col_int_g INTEGER GENERATED ALWAYS AS (col_int) @@ -730,6 +732,8 @@ ALTER TABLE t1 DROP KEY `ftidx` ; INSERT INTO t1 (col_int, col_text) VALUES ( 1255, NULL); DROP TABLE t1; SET @@global.innodb_file_per_table = @save; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release # # MDEV-20797 FULLTEXT search with apostrophe, # and mandatory words diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result index 52cbede7314..6d7a5647a63 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result @@ -466,7 +466,8 @@ ROLLBACK; SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops'); s1 s2 DROP TABLE t2 , t1; -set global innodb_file_per_table=1; +SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; +SET GLOBAL innodb_read_only_compressed=OFF; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), @@ -649,7 +650,7 @@ id a b 6 MYSQL SECURITY when configured properly, mysql ... 7 TEST QUERY EXPANSION for database ... DROP TABLE t1; -SET GLOBAL innodb_file_per_table=1; +SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result b/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result index d67981e0851..0cbe1090e89 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result @@ -1,4 +1,3 @@ -drop table if exists t1; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), @@ -133,7 +132,6 @@ AGAINST ('"xyz blob"@3' IN BOOLEAN MODE); count(*) 2 DROP TABLE t1; -set global innodb_file_per_table=1; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a TEXT, @@ -214,4 +212,3 @@ AGAINST ('"very blob"@3' IN BOOLEAN MODE); count(*) 1 DROP TABLE t1; -SET GLOBAL innodb_file_per_table=1; diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test b/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test index 4eaf5b2e0bd..c0836372e9e 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test @@ -427,15 +427,9 @@ DROP TABLE t2 , t1; # FTS index with compressed row format #------------------------------------------------------------------------------ -# Save innodb variables -let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; - -set global innodb_file_per_table=1; - ---disable_query_log SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; SET GLOBAL innodb_read_only_compressed=OFF; ---enable_query_log + # Create FTS table CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, @@ -583,10 +577,7 @@ SELECT * FROM t1 WHERE MATCH (a,b) SELECT * FROM t1 ORDER BY id; DROP TABLE t1; ---disable_query_log SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; ---enable_query_log -eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; #------------------------------------------------------------------------------ # FTS index with utf8 character testcase diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test b/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test index e3d8eb0c13b..25ad4f0355f 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test @@ -5,14 +5,6 @@ # Functional testing with FTS proximity search using '@' # and try search default words ---disable_warnings -drop table if exists t1; ---enable_warnings - ---disable_query_log -let $innodb_file_per_table_orig = `select @@innodb_file_per_table`; ---enable_query_log - # Create FTS table CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, @@ -161,8 +153,6 @@ SELECT count(*) FROM t1 DROP TABLE t1; -set global innodb_file_per_table=1; - # Test fts with externally stored long column CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, @@ -257,5 +247,3 @@ SELECT count(*) FROM t1 AGAINST ('"very blob"@3' IN BOOLEAN MODE); DROP TABLE t1; - -eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; diff --git a/mysql-test/suite/innodb_zip/r/bug36169.result b/mysql-test/suite/innodb_zip/r/bug36169.result index 7e165e0f7d4..c7e234b5ba1 100644 --- a/mysql-test/suite/innodb_zip/r/bug36169.result +++ b/mysql-test/suite/innodb_zip/r/bug36169.result @@ -1 +1 @@ -SET GLOBAL innodb_file_per_table=ON; +DROP TABLE IF EXISTS table6; diff --git a/mysql-test/suite/innodb_zip/r/bug53591.result b/mysql-test/suite/innodb_zip/r/bug53591.result index 779064d054f..0c6386df022 100644 --- a/mysql-test/suite/innodb_zip/r/bug53591.result +++ b/mysql-test/suite/innodb_zip/r/bug53591.result @@ -1,9 +1,4 @@ call mtr.add_suppression('InnoDB: Cannot add field.*because after adding it, the row size is'); -SET GLOBAL innodb_file_per_table=on; -SET GLOBAL innodb_strict_mode=on; -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 CREATE TABLE bug53591(a text charset utf8 not null) ENGINE=InnoDB KEY_BLOCK_SIZE=1; ALTER TABLE bug53591 ADD PRIMARY KEY(a(220)); @@ -12,5 +7,3 @@ SHOW WARNINGS; Level Code Message Error 1118 Row size too large. The maximum row size for the used table type, not counting BLOBs, is {checked_valid}. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs DROP TABLE bug53591; -SET GLOBAL innodb_file_per_table=1; -SET GLOBAL innodb_strict_mode=DEFAULT; diff --git a/mysql-test/suite/innodb_zip/r/bug56680.result b/mysql-test/suite/innodb_zip/r/bug56680.result index 1a1a5b40707..3c325351c3a 100644 --- a/mysql-test/suite/innodb_zip/r/bug56680.result +++ b/mysql-test/suite/innodb_zip/r/bug56680.result @@ -1,5 +1,4 @@ SET GLOBAL tx_isolation='REPEATABLE-READ'; -SET GLOBAL innodb_file_per_table=on; CREATE TABLE bug56680( a INT AUTO_INCREMENT PRIMARY KEY, b CHAR(1), diff --git a/mysql-test/suite/innodb_zip/r/cmp_drop_table.result b/mysql-test/suite/innodb_zip/r/cmp_drop_table.result index e1f63268d0b..530932e99fb 100644 --- a/mysql-test/suite/innodb_zip/r/cmp_drop_table.result +++ b/mysql-test/suite/innodb_zip/r/cmp_drop_table.result @@ -1,4 +1,3 @@ -set global innodb_file_per_table=on; create table t1(a text) engine=innodb key_block_size=4; SELECT page_size FROM information_schema.innodb_cmpmem WHERE pages_used > 0; page_size diff --git a/mysql-test/suite/innodb_zip/r/create_options.result b/mysql-test/suite/innodb_zip/r/create_options.result index a678d7a85f3..0f4c1239d73 100644 --- a/mysql-test/suite/innodb_zip/r/create_options.result +++ b/mysql-test/suite/innodb_zip/r/create_options.result @@ -1,5 +1,4 @@ SET default_storage_engine=InnoDB; -SET GLOBAL innodb_file_per_table=ON; SET SESSION innodb_strict_mode = ON; # Test 1) StrictMode=ON, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 # KEY_BLOCK_SIZE=0 means 'no KEY_BLOCK_SIZE is specified' @@ -280,6 +279,8 @@ Level Code Message # innodb_file_per_table=OFF and that they can be set to default # values during strict mode. SET GLOBAL innodb_file_per_table=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release DROP TABLE t1; CREATE TABLE t1 ( i INT ) KEY_BLOCK_SIZE=1; Got one of the listed errors @@ -354,9 +355,13 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME ROW_FORMAT CREATE_OPTIONS t1 Dynamic SET GLOBAL innodb_file_per_table=ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release DROP TABLE t1; CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; SET GLOBAL innodb_file_per_table=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t1 ADD COLUMN f1 INT; SHOW WARNINGS; Level Code Message @@ -367,6 +372,8 @@ ALTER TABLE t1 ADD COLUMN f2 INT; SHOW WARNINGS; Level Code Message SET GLOBAL innodb_file_per_table=ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ################################################## SET SESSION innodb_strict_mode = OFF; # Test 9) StrictMode=OFF, CREATE and ALTER with each ROW_FORMAT & KEY_BLOCK_SIZE=0 @@ -722,6 +729,8 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME ROW_FORMAT CREATE_OPTIONS t1 Compressed row_format=COMPRESSED key_block_size=2 SET GLOBAL innodb_file_per_table=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t1 ADD COLUMN f1 INT; SHOW WARNINGS; Level Code Message @@ -729,6 +738,8 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME ROW_FORMAT CREATE_OPTIONS t1 Compressed row_format=COMPRESSED key_block_size=2 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 ADD COLUMN f2 INT; SHOW WARNINGS; Level Code Message @@ -743,6 +754,8 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME ROW_FORMAT CREATE_OPTIONS t1 Dynamic row_format=DYNAMIC SET GLOBAL innodb_file_per_table=OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t1 ADD COLUMN f1 INT; SHOW WARNINGS; Level Code Message @@ -750,6 +763,8 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME ROW_FORMAT CREATE_OPTIONS t1 Dynamic row_format=DYNAMIC 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 ADD COLUMN f2 INT; SHOW WARNINGS; Level Code Message diff --git a/mysql-test/suite/innodb_zip/r/index_large_prefix.result b/mysql-test/suite/innodb_zip/r/index_large_prefix.result index ce302327713..81bdcdeb740 100644 --- a/mysql-test/suite/innodb_zip/r/index_large_prefix.result +++ b/mysql-test/suite/innodb_zip/r/index_large_prefix.result @@ -1,5 +1,4 @@ SET default_storage_engine=InnoDB; -set global innodb_file_per_table=1; ### Test 1 ### create table worklog5743(a TEXT not null, primary key (a(1000))) ROW_FORMAT=DYNAMIC; show warnings; @@ -449,4 +448,3 @@ create index idx on worklog5743(a(768)); ERROR HY000: Index column size too large. The maximum column size is 767 bytes create index idx2 on worklog5743(a(767)); drop table worklog5743; -SET GLOBAL innodb_file_per_table=1; diff --git a/mysql-test/suite/innodb_zip/r/innodb-zip.result b/mysql-test/suite/innodb_zip/r/innodb-zip.result index 0a3119f48a4..c44eefcbbb0 100644 --- a/mysql-test/suite/innodb_zip/r/innodb-zip.result +++ b/mysql-test/suite/innodb_zip/r/innodb-zip.result @@ -6,7 +6,10 @@ WHERE table_schema='mysqltest_innodb_zip'; table_name row_format data_length index_length SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata; set session innodb_strict_mode=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 SET @@global.innodb_stats_on_metadata=ON; create table t0(a int primary key) engine=innodb row_format=compressed; Warnings: @@ -32,6 +35,8 @@ Warnings: Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=1. set global innodb_file_per_table=on; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release create table t6(a int primary key) engine=innodb key_block_size=1 row_format=redundant; Warnings: @@ -153,7 +158,6 @@ count(*) 1 update t1 set c3 = repeat('E', 20000) where c1 = 1; drop table t1; -set global innodb_file_per_table = on; set innodb_strict_mode = off; create table t1 (id int primary key) engine = innodb key_block_size = 0; drop table t1; @@ -247,6 +251,8 @@ Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' and table_schema != 'sys' order by table_name; table_schema table_name row_format data_length index_length 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 (id int primary key) engine = innodb key_block_size = 1; ERROR HY000: Can't create table `mysqltest_innodb_zip`.`t1` (errno: 140 "Wrong create options") show warnings; @@ -286,4 +292,8 @@ mysqltest_innodb_zip t7 Dynamic {valid} 0 mysqltest_innodb_zip t8 Compact {valid} 0 mysqltest_innodb_zip t9 Redundant {valid} 0 drop table t7, t8, t9; +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 @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata; DROP DATABASE mysqltest_innodb_zip; diff --git a/mysql-test/suite/innodb_zip/r/large_blob.result b/mysql-test/suite/innodb_zip/r/large_blob.result index 7070d610f58..55bb90aab5c 100644 --- a/mysql-test/suite/innodb_zip/r/large_blob.result +++ b/mysql-test/suite/innodb_zip/r/large_blob.result @@ -1,8 +1,9 @@ # # This tests the use of large blobs in InnoDB. # -call mtr.add_suppression("InnoDB: Warning: a long semaphore wait"); SET GLOBAL innodb_file_per_table = OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release # # System tablespace, Row Format = Redundant # @@ -26,6 +27,8 @@ INSERT INTO t1 VALUES (1, ''); UPDATE t1 SET c2=@longblob; DROP TABLE t1; SET GLOBAL innodb_file_per_table = ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release # # Separate tablespace, Row Format = Redundant # diff --git a/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff b/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff index 494da214f2e..0ba8a299ab9 100644 --- a/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff +++ b/mysql-test/suite/innodb_zip/r/page_size,4k.rdiff @@ -21,9 +21,9 @@ -test/t3 DEFAULT 8192 MYSQLD_DATADIR/test/t3.ibd +test/t3 DEFAULT 2048 MYSQLD_DATADIR/test/t3.ibd test/t4 DEFAULT DEFAULT MYSQLD_DATADIR/test/t4.ibd + innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1 DROP TABLE t1, t2, t3, t4; - # Test 4) The maximum row size is dependent upon the page size. -@@ -51,141 +51,90 @@ +@@ -52,141 +52,90 @@ SET SESSION innodb_strict_mode = ON; CREATE TABLE t1 ( c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), @@ -211,7 +211,7 @@ ALTER TABLE t1 KEY_BLOCK_SIZE=4; SHOW WARNINGS; Level Code Message -@@ -217,15 +166,21 @@ +@@ -218,15 +167,21 @@ DROP TABLE t1; SET SESSION innodb_strict_mode = OFF; CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; @@ -233,7 +233,7 @@ SELECT table_name, row_format, create_options FROM information_schema.tables WHERE table_name = 't1'; table_name row_format create_options -@@ -269,6 +224,7 @@ +@@ -272,6 +227,7 @@ ERROR HY000: Can't create table `test`.`t4` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message @@ -241,7 +241,7 @@ Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table `test`.`t4` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB -@@ -276,105 +232,11 @@ +@@ -279,107 +235,13 @@ ERROR HY000: Can't create table `test`.`t5` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message @@ -250,6 +250,8 @@ Error 1005 Can't create table `test`.`t5` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB SET GLOBAL innodb_file_per_table = ON; + Warnings: + Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release -# Test 7) This series of tests were moved from innodb-index to here -# because the second alter table t1 assumes a 16k page size. -# Moving the test allows the rest of innodb-index to be run on all @@ -348,7 +350,7 @@ # Test 8) Test creating a table that could lead to undo log overflow. CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob, h blob,i blob,j blob,k blob,l blob,m blob,n blob, -@@ -389,10 +251,6 @@ +@@ -394,10 +256,6 @@ UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b, k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b; CREATE INDEX t1a ON t1 (a(767)); @@ -359,7 +361,7 @@ UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c, k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c; CREATE INDEX t1f ON t1 (f(767)); -@@ -407,37 +265,15 @@ +@@ -412,37 +270,15 @@ COMMIT; CREATE INDEX t1g ON t1 (g(767)); UPDATE t1 SET g=@e; @@ -401,7 +403,7 @@ SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( -@@ -463,28 +299,12 @@ +@@ -468,28 +304,12 @@ `t` blob DEFAULT NULL, `u` blob DEFAULT NULL, KEY `t1a` (`a`(767)), @@ -432,7 +434,7 @@ ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC DROP TABLE t1; # Bug#12547647 UPDATE LOGGING COULD EXCEED LOG PAGE SIZE -@@ -569,27 +389,25 @@ +@@ -574,27 +394,25 @@ DROP TABLE t1; CREATE TABLE t1( c text NOT NULL, d text NOT NULL, diff --git a/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff b/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff index a22fa9e9b95..0d215684e2f 100644 --- a/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff +++ b/mysql-test/suite/innodb_zip/r/page_size,8k.rdiff @@ -21,9 +21,9 @@ -test/t3 DEFAULT 8192 MYSQLD_DATADIR/test/t3.ibd +test/t3 DEFAULT 4096 MYSQLD_DATADIR/test/t3.ibd test/t4 DEFAULT DEFAULT MYSQLD_DATADIR/test/t4.ibd + innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1 DROP TABLE t1, t2, t3, t4; - # Test 4) The maximum row size is dependent upon the page size. -@@ -53,133 +53,97 @@ +@@ -54,133 +54,97 @@ c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200), c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200), c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200), @@ -199,7 +199,7 @@ SHOW WARNINGS; Level Code Message SELECT table_name, row_format, create_options -@@ -217,8 +181,11 @@ +@@ -218,8 +182,11 @@ DROP TABLE t1; SET SESSION innodb_strict_mode = OFF; CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16; @@ -211,7 +211,7 @@ SELECT table_name, row_format, create_options FROM information_schema.tables WHERE table_name = 't1'; table_name row_format create_options -@@ -276,105 +243,11 @@ +@@ -279,107 +246,13 @@ ERROR HY000: Can't create table `test`.`t5` (errno: 140 "Wrong create options") SHOW WARNINGS; Level Code Message @@ -220,6 +220,8 @@ Error 1005 Can't create table `test`.`t5` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB SET GLOBAL innodb_file_per_table = ON; + Warnings: + Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release -# Test 7) This series of tests were moved from innodb-index to here -# because the second alter table t1 assumes a 16k page size. -# Moving the test allows the rest of innodb-index to be run on all @@ -318,7 +320,7 @@ # Test 8) Test creating a table that could lead to undo log overflow. CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob, h blob,i blob,j blob,k blob,l blob,m blob,n blob, -@@ -389,10 +262,6 @@ +@@ -394,10 +267,6 @@ UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b, k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b; CREATE INDEX t1a ON t1 (a(767)); @@ -329,7 +331,7 @@ UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c, k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c; CREATE INDEX t1f ON t1 (f(767)); -@@ -407,30 +276,6 @@ +@@ -412,30 +281,6 @@ COMMIT; CREATE INDEX t1g ON t1 (g(767)); UPDATE t1 SET g=@e; @@ -360,7 +362,7 @@ CREATE INDEX t1t ON t1 (t(767)); BEGIN; UPDATE t1 SET t=@e; -@@ -463,24 +308,8 @@ +@@ -468,24 +313,8 @@ `t` blob DEFAULT NULL, `u` blob DEFAULT NULL, KEY `t1a` (`a`(767)), @@ -385,7 +387,7 @@ KEY `t1t` (`t`(767)), KEY `t1u` (`u`(767)), KEY `t1ut` (`u`(767),`t`(767)), -@@ -572,14 +401,14 @@ +@@ -577,14 +406,14 @@ PRIMARY KEY (c(767),d(767))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; Warnings: @@ -402,7 +404,7 @@ DROP TABLE t1; CREATE TABLE t1( c text NOT NULL, d text NOT NULL, -@@ -589,7 +418,7 @@ +@@ -594,7 +423,7 @@ CREATE TABLE t1(c text, PRIMARY KEY (c(440))) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII; Warnings: diff --git a/mysql-test/suite/innodb_zip/r/page_size.result b/mysql-test/suite/innodb_zip/r/page_size.result index 47effe06884..71e8e5461d3 100644 --- a/mysql-test/suite/innodb_zip/r/page_size.result +++ b/mysql-test/suite/innodb_zip/r/page_size.result @@ -263,6 +263,8 @@ DROP TABLE t1; # Test 6) KEY_BLOCK_SIZE with innodb_file_per_table=OFF SET SESSION innodb_strict_mode = ON; SET GLOBAL innodb_file_per_table = OFF; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SHOW VARIABLES LIKE 'innodb_file_per_table'; Variable_name Value innodb_file_per_table OFF @@ -281,6 +283,8 @@ Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. Error 1005 Can't create table `test`.`t5` (errno: 140 "Wrong create options") Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB SET GLOBAL innodb_file_per_table = ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release # Test 7) This series of tests were moved from innodb-index to here # because the second alter table t1 assumes a 16k page size. # Moving the test allows the rest of innodb-index to be run on all diff --git a/mysql-test/suite/innodb_zip/r/restart.result b/mysql-test/suite/innodb_zip/r/restart.result index eb1bfe67c5d..b57fe1e89e6 100644 --- a/mysql-test/suite/innodb_zip/r/restart.result +++ b/mysql-test/suite/innodb_zip/r/restart.result @@ -3,7 +3,6 @@ SET default_storage_engine=InnoDB; # A series of tests to make sure tables are opened after restart. # Bug#13357607 Compressed file-per-table tablespaces fail to open # -set global innodb_file_per_table=on; # # Create and insert records into a REDUNDANT row formatted table. # @@ -265,9 +264,6 @@ t7_restart#p#p1#sp#s3.ibd # Start the server and show that tables are still visible and accessible. # # restart -SHOW VARIABLES LIKE 'innodb_file_per_table'; -Variable_name Value -innodb_file_per_table ON SHOW CREATE TABLE t1_restart; Table Create Table t1_restart CREATE TABLE `t1_restart` ( @@ -522,9 +518,6 @@ t7_restart#p#p1#sp#s3.ibd # Start the server and show the tablespaces. # # restart -SHOW VARIABLES LIKE 'innodb_file_per_table'; -Variable_name Value -innodb_file_per_table ON === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd @@ -723,9 +716,6 @@ t77_restart#p#p1#sp#s3.ibd # Restart the server # # restart -SHOW VARIABLES LIKE 'innodb_file_per_table'; -Variable_name Value -innodb_file_per_table ON === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === Space_Name Page_Size Zip_Size Path test/t4_restart DEFAULT DEFAULT MYSQLD_DATADIR/test/t4_restart.ibd diff --git a/mysql-test/suite/innodb_zip/r/wl5522_zip.result b/mysql-test/suite/innodb_zip/r/wl5522_zip.result index 1b044d68680..a7cc00cb1fd 100644 --- a/mysql-test/suite/innodb_zip/r/wl5522_zip.result +++ b/mysql-test/suite/innodb_zip/r/wl5522_zip.result @@ -263,6 +263,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; @@ -280,6 +282,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_zip/r/wl6347_comp_indx_stat.result b/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result index 05b4793eb4d..f46678340e2 100644 --- a/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result +++ b/mysql-test/suite/innodb_zip/r/wl6347_comp_indx_stat.result @@ -8,7 +8,6 @@ # check the size and compression stats of the table tab5 #****************************************************************** # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_cmp_per_index_enabled=ON; SET GLOBAL innodb_compression_level=0; #****************************************************************** @@ -318,7 +317,6 @@ The size of the tab5.ibd file: 5242880 # set the flag on (default off) SET GLOBAL innodb_cmp_per_index_enabled=ON; # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=0; # fetch the compressed page and check the stats # The stats figure may be different/same for each restart. @@ -655,7 +653,6 @@ The size of the tab5.ibd file: 2097152 # set the flag on (default off) SET GLOBAL innodb_cmp_per_index_enabled=ON; # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=0; # fetch the compressed page and check the stats # The stats figure may be different/same for each restart. @@ -1615,7 +1612,6 @@ DROP TABLE tab5; SET GLOBAL innodb_cmp_per_index_enabled=0; SET GLOBAL innodb_cmp_per_index_enabled=1; # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=9; #****************************************************************** # Test 2-1K: innodb_cmp_per_index_enabled=ON and innodb_compression_level=9 with page size 1K @@ -1926,7 +1922,6 @@ The size of the tab5.ibd file: 65536 # set the flag on (default off) SET GLOBAL innodb_cmp_per_index_enabled=ON; # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=9; # fetch the compressed page and check the stats # The stats figure may be different/same for each restart. @@ -2265,7 +2260,6 @@ The size of the tab5.ibd file: 65536 # set the flag on (default off) SET GLOBAL innodb_cmp_per_index_enabled=ON; # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=9; # fetch the compressed page and check the stats # The stats figure may be different/same for each restart. @@ -4710,8 +4704,6 @@ SELECT @@innodb_cmp_per_index_enabled; @@innodb_cmp_per_index_enabled 1 SELECT @@innodb_compression_failure_threshold_pct; @@innodb_compression_failure_threshold_pct 0 -SELECT @@innodb_file_per_table; -@@innodb_file_per_table 1 SELECT @@innodb_compression_level; @@innodb_compression_level 6 #****************************************************************** @@ -5022,7 +5014,6 @@ The size of the tab5.ibd file: 65536 SET GLOBAL innodb_cmp_per_index_enabled=ON; # set the flags SET GLOBAL innodb_compression_failure_threshold_pct=0; -SET GLOBAL innodb_file_per_table=on; # fetch the compressed page and check the stats # The stats figure may be different/same for each restart. =============== @@ -6289,7 +6280,6 @@ DROP TABLE tab5; SET GLOBAL innodb_cmp_per_index_enabled=OFF; SET GLOBAL innodb_cmp_per_index_enabled=ON; # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_cmp_per_index_enabled=ON; SET GLOBAL innodb_compression_failure_threshold_pct=10; SET GLOBAL innodb_compression_level=Default; @@ -6298,8 +6288,6 @@ SELECT @@innodb_cmp_per_index_enabled; @@innodb_cmp_per_index_enabled 1 SELECT @@innodb_compression_failure_threshold_pct; @@innodb_compression_failure_threshold_pct 10 -SELECT @@innodb_file_per_table; -@@innodb_file_per_table 1 SELECT @@innodb_compression_level; @@innodb_compression_level 6 #****************************************************************** @@ -6610,8 +6598,6 @@ The size of the tab5.ibd file: 65536 SET GLOBAL innodb_cmp_per_index_enabled=ON; # set the flags SET GLOBAL innodb_compression_failure_threshold_pct=10; -SET GLOBAL innodb_file_per_table=on; -SET GLOBAL innodb_compression_failure_threshold_pct=10; # fetch the compressed page and check the stats # The stats figure may be different/same for each restart. =============== @@ -7874,7 +7860,6 @@ DROP TABLE tab5; # reset the stat table before starting next testcase SET GLOBAL innodb_cmp_per_index_enabled=0; SET GLOBAL innodb_cmp_per_index_enabled=1; -SET GLOBAL innodb_file_per_table=ON; SET GLOBAL innodb_compression_level=default; SET GLOBAL innodb_compression_failure_threshold_pct=default; # create a table page size=1K @@ -7923,6 +7908,5 @@ compress_ops_ok 1 DROP TABLE tab5, test.tab5; DROP DATABASE sb; # reset the flags -SET GLOBAL innodb_file_per_table=default; SET GLOBAL innodb_cmp_per_index_enabled=default; SET GLOBAL innodb_compression_failure_threshold_pct=default; diff --git a/mysql-test/suite/innodb_zip/t/bug36169.test b/mysql-test/suite/innodb_zip/t/bug36169.test index 07566b204bd..8ab970f67b2 100644 --- a/mysql-test/suite/innodb_zip/t/bug36169.test +++ b/mysql-test/suite/innodb_zip/t/bug36169.test @@ -5,9 +5,6 @@ -- source include/innodb_page_size_small.inc -let $file_per_table=`select @@innodb_file_per_table`; -SET GLOBAL innodb_file_per_table=ON; - # # The following is copied from http://bugs.mysql.com/36169 # (http://bugs.mysql.com/file.php?id=9121) @@ -1155,7 +1152,5 @@ DROP TABLE IF EXISTS table2; DROP TABLE IF EXISTS table3; DROP TABLE IF EXISTS table4; DROP TABLE IF EXISTS table5; +-- enable_query_log DROP TABLE IF EXISTS table6; - -EVAL SET GLOBAL innodb_file_per_table=$file_per_table; -SET sql_mode = default; diff --git a/mysql-test/suite/innodb_zip/t/bug53591.test b/mysql-test/suite/innodb_zip/t/bug53591.test index 17c79e0f6f8..ef4d12ad078 100644 --- a/mysql-test/suite/innodb_zip/t/bug53591.test +++ b/mysql-test/suite/innodb_zip/t/bug53591.test @@ -2,13 +2,6 @@ call mtr.add_suppression('InnoDB: Cannot add field.*because after adding it, the row size is'); -let $file_per_table=`select @@innodb_file_per_table`; - -SET GLOBAL innodb_file_per_table=on; -SET GLOBAL innodb_strict_mode=on; - -set old_alter_table=0; - CREATE TABLE bug53591(a text charset utf8 not null) ENGINE=InnoDB KEY_BLOCK_SIZE=1; -- replace_result 8126 {checked_valid} 4030 {checked_valid} 1982 {checked_valid} @@ -18,6 +11,3 @@ ALTER TABLE bug53591 ADD PRIMARY KEY(a(220)); SHOW WARNINGS; DROP TABLE bug53591; - -EVAL SET GLOBAL innodb_file_per_table=$file_per_table; -SET GLOBAL innodb_strict_mode=DEFAULT; diff --git a/mysql-test/suite/innodb_zip/t/bug56680.test b/mysql-test/suite/innodb_zip/t/bug56680.test index 54cbc7ca798..0d441dea0f5 100644 --- a/mysql-test/suite/innodb_zip/t/bug56680.test +++ b/mysql-test/suite/innodb_zip/t/bug56680.test @@ -16,7 +16,6 @@ SET GLOBAL innodb_change_buffering_debug = 1; -- enable_query_log SET GLOBAL tx_isolation='REPEATABLE-READ'; -SET GLOBAL innodb_file_per_table=on; CREATE TABLE bug56680( a INT AUTO_INCREMENT PRIMARY KEY, diff --git a/mysql-test/suite/innodb_zip/t/cmp_drop_table.test b/mysql-test/suite/innodb_zip/t/cmp_drop_table.test index ae6bfd9fb59..e64514fc9fb 100644 --- a/mysql-test/suite/innodb_zip/t/cmp_drop_table.test +++ b/mysql-test/suite/innodb_zip/t/cmp_drop_table.test @@ -3,12 +3,8 @@ # scans through pages -- source include/not_encrypted.inc -let $per_table=`select @@innodb_file_per_table`; - -- let $query_i_s = SELECT page_size FROM information_schema.innodb_cmpmem WHERE pages_used > 0 -set global innodb_file_per_table=on; - create table t1(a text) engine=innodb key_block_size=4; -- disable_query_log @@ -50,10 +46,3 @@ while ($i) -- eval $query_i_s drop table t2; - -# -# restore environment to the state it was before this test execution -# - --- disable_query_log -eval set global innodb_file_per_table=$per_table; diff --git a/mysql-test/suite/innodb_zip/t/create_options.test b/mysql-test/suite/innodb_zip/t/create_options.test index fce64060df3..9840a28a2ce 100644 --- a/mysql-test/suite/innodb_zip/t/create_options.test +++ b/mysql-test/suite/innodb_zip/t/create_options.test @@ -59,11 +59,6 @@ --source include/innodb_page_size_small.inc SET default_storage_engine=InnoDB; -# These values can change during the test -LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`; - -SET GLOBAL innodb_file_per_table=ON; - # The first half of these tests are with strict mode ON. SET SESSION innodb_strict_mode = ON; @@ -481,7 +476,3 @@ SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE --echo # Cleanup DROP TABLE t1; - ---disable_query_log -EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; ---enable_query_log diff --git a/mysql-test/suite/innodb_zip/t/index_large_prefix.test b/mysql-test/suite/innodb_zip/t/index_large_prefix.test index a254c4e61ee..5c1e31a2db7 100644 --- a/mysql-test/suite/innodb_zip/t/index_large_prefix.test +++ b/mysql-test/suite/innodb_zip/t/index_large_prefix.test @@ -10,10 +10,6 @@ SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; SET GLOBAL innodb_read_only_compressed=OFF; --enable_query_log -let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; - -set global innodb_file_per_table=1; - -- echo ### Test 1 ### # Create a table of DYNAMIC format, with a primary index of 1000 bytes in # size @@ -408,5 +404,3 @@ create index idx2 on worklog5743(a(767)); SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; --enable_query_log drop table worklog5743; - -eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; diff --git a/mysql-test/suite/innodb_zip/t/index_large_prefix_4k.test b/mysql-test/suite/innodb_zip/t/index_large_prefix_4k.test index 04f7ac65edf..8ada226779a 100644 --- a/mysql-test/suite/innodb_zip/t/index_large_prefix_4k.test +++ b/mysql-test/suite/innodb_zip/t/index_large_prefix_4k.test @@ -10,10 +10,6 @@ SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; SET GLOBAL innodb_read_only_compressed=OFF; --enable_query_log -let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; - -set global innodb_file_per_table=1; - -- echo ### Test 1 ### # Create a table of DYNAMIC format, with a primary index of 768 bytes in # size @@ -380,5 +376,3 @@ create index idx2 on worklog5743(a(767)); SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; --enable_query_log drop table worklog5743; - -eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; diff --git a/mysql-test/suite/innodb_zip/t/index_large_prefix_8k.test b/mysql-test/suite/innodb_zip/t/index_large_prefix_8k.test index c9cd9574a95..e20de5e3e93 100644 --- a/mysql-test/suite/innodb_zip/t/index_large_prefix_8k.test +++ b/mysql-test/suite/innodb_zip/t/index_large_prefix_8k.test @@ -10,10 +10,6 @@ SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed; SET GLOBAL innodb_read_only_compressed=OFF; --enable_query_log -let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; - -set global innodb_file_per_table=1; - -- echo ### Test 1 ### # Create a table of DYNAMIC format, with a primary index of 1000 bytes in # size @@ -402,5 +398,3 @@ create index idx2 on worklog5743(a(767)); SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed; --enable_query_log drop table worklog5743; - -eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig; diff --git a/mysql-test/suite/innodb_zip/t/innodb-zip.test b/mysql-test/suite/innodb_zip/t/innodb-zip.test index 507900aa88c..47867f3fa3e 100644 --- a/mysql-test/suite/innodb_zip/t/innodb-zip.test +++ b/mysql-test/suite/innodb_zip/t/innodb-zip.test @@ -6,12 +6,12 @@ SELECT table_name, row_format, data_length, index_length FROM information_schema.tables WHERE table_schema='mysqltest_innodb_zip'; -let $per_table=`select @@innodb_file_per_table`; SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata; --let $query_i_s = SELECT table_schema, table_name, row_format, data_length, index_length FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql' and table_schema != 'sys' order by table_name set session innodb_strict_mode=0; +SET @save_fpt=@@GLOBAL.innodb_file_per_table; set global innodb_file_per_table=off; SET @@global.innodb_stats_on_metadata=ON; @@ -123,8 +123,6 @@ select count(*) from t1 where c4 = repeat('C', 20000); update t1 set c3 = repeat('E', 20000) where c1 = 1; drop table t1; -set global innodb_file_per_table = on; - set innodb_strict_mode = off; create table t1 (id int primary key) engine = innodb key_block_size = 0; drop table t1; @@ -254,9 +252,7 @@ drop table t7, t8, t9; # restore environment to the state it was before this test execution # --- disable_query_log -eval set global innodb_file_per_table=$per_table; +SET GLOBAL innodb_file_per_table=@save_fpt; SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata; ---enable_query_log DROP DATABASE mysqltest_innodb_zip; diff --git a/mysql-test/suite/innodb_zip/t/large_blob.test b/mysql-test/suite/innodb_zip/t/large_blob.test index dd208129d08..d8163737a17 100644 --- a/mysql-test/suite/innodb_zip/t/large_blob.test +++ b/mysql-test/suite/innodb_zip/t/large_blob.test @@ -7,9 +7,6 @@ --source include/big_test.inc --disable_query_log -# These values can change during the test -let $innodb_file_per_table_orig = `SELECT @@innodb_file_per_table`; - # Create a 20MB blob that does not compress easily. # 1000 Random characters is enough to keep compression low. set @alphabet="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"; @@ -27,8 +24,6 @@ while ($1 > 1) set @longblob=repeat(@blob,200000); --enable_query_log -call mtr.add_suppression("InnoDB: Warning: a long semaphore wait"); - SET GLOBAL innodb_file_per_table = OFF; --echo # @@ -123,7 +118,3 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES (1, ''); UPDATE t1 SET c2=@longblob; DROP TABLE t1; - ---disable_query_log -EVAL SET GLOBAL innodb_file_per_table = $innodb_file_per_table_orig; ---enable_query_log diff --git a/mysql-test/suite/innodb_zip/t/restart.test b/mysql-test/suite/innodb_zip/t/restart.test index c442b919d71..644496d70d6 100644 --- a/mysql-test/suite/innodb_zip/t/restart.test +++ b/mysql-test/suite/innodb_zip/t/restart.test @@ -34,12 +34,8 @@ call mtr.add_suppression("\\[ERROR\\] InnoDB: The error means the system cannot --disable_query_log let $MYSQL_DATA_DIR= `select @@datadir`; let $data_directory = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir'; - -let $innodb_file_per_table_orig=`select @@innodb_file_per_table`; --enable_query_log -set global innodb_file_per_table=on; - --echo # --echo # Create and insert records into a REDUNDANT row formatted table. --echo # @@ -181,7 +177,6 @@ SELECT count(*) FROM t7_restart; --echo # --source include/start_mysqld.inc -SHOW VARIABLES LIKE 'innodb_file_per_table'; SHOW CREATE TABLE t1_restart; SHOW CREATE TABLE t2_restart; SHOW CREATE TABLE t3_restart; @@ -274,8 +269,6 @@ SHOW CREATE TABLE t7_restart; --echo # --source include/start_mysqld.inc -SHOW VARIABLES LIKE 'innodb_file_per_table'; - --source suite/innodb/include/show_i_s_tablespaces.inc SELECT count(*) FROM t5_restart; @@ -364,7 +357,6 @@ SHOW CREATE TABLE t77_restart; --echo # Restart the server --echo # --source include/restart_mysqld.inc -SHOW VARIABLES LIKE 'innodb_file_per_table'; --source suite/innodb/include/show_i_s_tablespaces.inc @@ -599,8 +591,3 @@ DROP TABLE t77_restart; --rmdir $MYSQL_TMP_DIR/alt_dir --rmdir $MYSQL_TMP_DIR/new_dir/test --rmdir $MYSQL_TMP_DIR/new_dir - --- disable_query_log -eval set global innodb_file_per_table=$innodb_file_per_table_orig; --- enable_query_log - diff --git a/mysql-test/suite/innodb_zip/t/wl5522_zip.test b/mysql-test/suite/innodb_zip/t/wl5522_zip.test index dbb698cc545..f223d36a6de 100644 --- a/mysql-test/suite/innodb_zip/t/wl5522_zip.test +++ b/mysql-test/suite/innodb_zip/t/wl5522_zip.test @@ -83,7 +83,7 @@ ALTER TABLE t1 IMPORT TABLESPACE; SELECT * FROM t1; DROP TABLE t1; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance CREATE TABLE t1( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; @@ -127,7 +127,7 @@ SELECT COUNT(*) FROM t1; DROP TABLE t1; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance # Insert some more records to move the LSN forward and then drop the # table and restore CREATE TABLE t1( @@ -174,7 +174,7 @@ SELECT COUNT(*) FROM t1; DROP TABLE t1; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance # Insert some more records to move the LSN forward and then drop the # table and restore, this time the table has a secondary index too. CREATE TABLE t1( @@ -217,7 +217,7 @@ SELECT COUNT(*) FROM t1 WHERE c2 = 1; DROP TABLE t1; -# Export/import on the same instance, with --innodb-file-per-table=1 +# Export/import on the same instance # Insert some more records to move the LSN forward and then drop the # table and restore, this time the table has a secondary index too. # Rename the index on the create so that the IMPORT fails, drop index diff --git a/mysql-test/suite/innodb_zip/t/wl6347_comp_indx_stat.test b/mysql-test/suite/innodb_zip/t/wl6347_comp_indx_stat.test index be9a05b36ce..ae32b03a786 100644 --- a/mysql-test/suite/innodb_zip/t/wl6347_comp_indx_stat.test +++ b/mysql-test/suite/innodb_zip/t/wl6347_comp_indx_stat.test @@ -20,7 +20,6 @@ let MYSQLD_DATADIR=`SELECT @@datadir`; let $innodb_compression_level = `SELECT @@global.innodb_compression_level`; --echo # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_cmp_per_index_enabled=ON; SET GLOBAL innodb_compression_level=0; --disable_query_log @@ -73,7 +72,6 @@ SET GLOBAL innodb_read_only_compressed=OFF; --enable_query_log --echo # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=0; --echo # fetch the compressed page and check the stats @@ -135,7 +133,6 @@ SET @inl_val=2; SET GLOBAL innodb_cmp_per_index_enabled=ON; --echo # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=0; --echo # fetch the compressed page and check the stats @@ -296,7 +293,6 @@ SET GLOBAL innodb_cmp_per_index_enabled=0; SET GLOBAL innodb_cmp_per_index_enabled=1; --echo # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=9; @@ -345,7 +341,6 @@ SET @inl_val=2; SET GLOBAL innodb_cmp_per_index_enabled=ON; --echo # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=9; @@ -413,7 +408,6 @@ SET @inl_val=2; SET GLOBAL innodb_cmp_per_index_enabled=ON; --echo # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_compression_level=9; @@ -801,7 +795,6 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; --echo # check the flags SELECT @@innodb_cmp_per_index_enabled; SELECT @@innodb_compression_failure_threshold_pct; -SELECT @@innodb_file_per_table; SELECT @@innodb_compression_level; --echo #****************************************************************** @@ -848,8 +841,6 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; --echo # set the flags SET GLOBAL innodb_compression_failure_threshold_pct=0; -SET GLOBAL innodb_file_per_table=on; - --echo # fetch the compressed page and check the stats --echo # The stats figure may be different/same for each restart. @@ -1053,7 +1044,6 @@ SET GLOBAL innodb_cmp_per_index_enabled=OFF; SET GLOBAL innodb_cmp_per_index_enabled=ON; --echo # set the flags -SET GLOBAL innodb_file_per_table=on; SET GLOBAL innodb_cmp_per_index_enabled=ON; SET GLOBAL innodb_compression_failure_threshold_pct=10; SET GLOBAL innodb_compression_level=Default; @@ -1062,7 +1052,6 @@ SET GLOBAL innodb_compression_level=Default; --echo # check the flags SELECT @@innodb_cmp_per_index_enabled; SELECT @@innodb_compression_failure_threshold_pct; -SELECT @@innodb_file_per_table; SELECT @@innodb_compression_level; --echo #****************************************************************** @@ -1109,9 +1098,6 @@ SET GLOBAL innodb_cmp_per_index_enabled=ON; --echo # set the flags SET GLOBAL innodb_compression_failure_threshold_pct=10; -SET GLOBAL innodb_file_per_table=on; -SET GLOBAL innodb_compression_failure_threshold_pct=10; - --echo # fetch the compressed page and check the stats --echo # The stats figure may be different/same for each restart. @@ -1312,7 +1298,6 @@ DROP TABLE tab5; SET GLOBAL innodb_cmp_per_index_enabled=0; SET GLOBAL innodb_cmp_per_index_enabled=1; -SET GLOBAL innodb_file_per_table=ON; SET GLOBAL innodb_compression_level=default; SET GLOBAL innodb_compression_failure_threshold_pct=default; @@ -1351,7 +1336,6 @@ DROP TABLE tab5, test.tab5; DROP DATABASE sb; --echo # reset the flags -eval SET GLOBAL innodb_file_per_table=default; eval SET GLOBAL innodb_cmp_per_index_enabled=default; --disable_query_log eval SET GLOBAL innodb_compression_level=$innodb_compression_level; diff --git a/mysql-test/suite/mariabackup/mdev-14447.result b/mysql-test/suite/mariabackup/mdev-14447.result index 16d3ab561f6..5db4693d75f 100644 --- a/mysql-test/suite/mariabackup/mdev-14447.result +++ b/mysql-test/suite/mariabackup/mdev-14447.result @@ -1,5 +1,7 @@ call mtr.add_suppression("InnoDB: New log files created"); 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 varchar(40) PRIMARY KEY, b varchar(40), c varchar(40), d varchar(40), index(b,c,d)) ENGINE INNODB; # Create full backup , modify table, then create incremental/differential backup SET debug_dbug='+d,skip_page_checksum',foreign_key_checks=0,unique_checks=0; diff --git a/mysql-test/suite/mariabackup/xb_compressed_encrypted.opt b/mysql-test/suite/mariabackup/xb_compressed_encrypted.opt index 4105fecfe58..52ef47f3fca 100644 --- a/mysql-test/suite/mariabackup/xb_compressed_encrypted.opt +++ b/mysql-test/suite/mariabackup/xb_compressed_encrypted.opt @@ -5,4 +5,3 @@ --loose-file-key-management --loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/logkey.txt --innodb_strict_mode ---innodb_file_per_table diff --git a/mysql-test/suite/mariabackup/xb_fulltext_encrypted.opt b/mysql-test/suite/mariabackup/xb_fulltext_encrypted.opt index b3ef366361a..e3a9e5a437f 100644 --- a/mysql-test/suite/mariabackup/xb_fulltext_encrypted.opt +++ b/mysql-test/suite/mariabackup/xb_fulltext_encrypted.opt @@ -1,6 +1,5 @@ --plugin-load-add=$FILE_KEY_MANAGEMENT_SO --innodb_strict_mode ---innodb_file_per_table --innodb-encryption-rotate-key-age=2 --innodb-encryption-threads=4 --innodb-tablespaces-encryption diff --git a/mysql-test/suite/parts/r/alter_data_directory_innodb.result b/mysql-test/suite/parts/r/alter_data_directory_innodb.result index 8c07093f127..e8f976372d6 100644 --- a/mysql-test/suite/parts/r/alter_data_directory_innodb.result +++ b/mysql-test/suite/parts/r/alter_data_directory_innodb.result @@ -40,6 +40,8 @@ t CREATE TABLE `t` ( PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) SET @TMP = @@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 ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; Warnings: Warning 1280 Name 'pk' ignored for PRIMARY key. @@ -53,6 +55,8 @@ t CREATE TABLE `t` ( (PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) SET GLOBAL INNODB_FILE_PER_TABLE=@TMP; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB diff --git a/mysql-test/suite/parts/r/longname.result b/mysql-test/suite/parts/r/longname.result index eb850d6f16c..8524eb8499d 100644 --- a/mysql-test/suite/parts/r/longname.result +++ b/mysql-test/suite/parts/r/longname.result @@ -31,6 +31,8 @@ PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB); ERROR HY000: The path specified for @0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@0n@... is too long SET @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 mysqltest1.t1 (a INT) ENGINE=INNODB PARTITION BY RANGE (a) SUBPARTITION BY HASH(a) (PARTITION `$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$` @@ -41,6 +43,8 @@ SUBPARTITION `0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef`) ); SET GLOBAL innodb_file_per_table=@file_per_table; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SHOW CREATE TABLE mysqltest1.t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result b/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result index 9d05d04ccca..6d361bb1465 100644 --- a/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result +++ b/mysql-test/suite/parts/r/partition_basic_symlink_innodb.result @@ -8,6 +8,8 @@ SET SESSION innodb_strict_mode = ON; # InnoDB only supports DATA DIRECTORY with innodb_file_per_table=ON # 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 (c1 INT) ENGINE = InnoDB PARTITION BY HASH (c1) ( PARTITION p0 @@ -36,6 +38,8 @@ Error 6 Error on delete of 'MYSQLD_DATADIR/test/t1.par' (Errcode: 2 "No such fil # SET SESSION innodb_strict_mode = OFF; SET GLOBAL innodb_file_per_table = ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t1 (c1 INT) ENGINE = InnoDB PARTITION BY HASH (c1) (PARTITION p0 @@ -134,6 +138,8 @@ DROP TABLE t1; # properly when used with DATA DIRECTORY # SET GLOBAL innodb_file_per_table = ON; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release CREATE TABLE t1 ( myid INT(11) NOT NULL, @@ -326,4 +332,6 @@ DROP TABLE t1, t2; # Cleanup # SET GLOBAL innodb_file_per_table=@file_per_table; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SET SESSION innodb_strict_mode=@strict_mode; diff --git a/mysql-test/suite/parts/r/reorganize_partition_innodb.result b/mysql-test/suite/parts/r/reorganize_partition_innodb.result index b56cb6bbaeb..b2233781219 100644 --- a/mysql-test/suite/parts/r/reorganize_partition_innodb.result +++ b/mysql-test/suite/parts/r/reorganize_partition_innodb.result @@ -40,6 +40,8 @@ t CREATE TABLE `t` ( PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) SET @TMP = @@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 ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE; Warnings: Warning 1280 Name 'pk' ignored for PRIMARY key. @@ -53,6 +55,8 @@ t CREATE TABLE `t` ( (PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB, PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB) SET GLOBAL INNODB_FILE_PER_TABLE=@TMP; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO ( PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB, PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB diff --git a/mysql-test/suite/parts/t/debug_innodb_crash-master.opt b/mysql-test/suite/parts/t/debug_innodb_crash-master.opt index b7f94e14e12..d8813652cd6 100644 --- a/mysql-test/suite/parts/t/debug_innodb_crash-master.opt +++ b/mysql-test/suite/parts/t/debug_innodb_crash-master.opt @@ -1 +1 @@ ---loose-innodb-file-per-table=1 --loose-skip-stack-trace --skip-core-file --loose-innodb-buffer-pool-size=32M +--loose-skip-stack-trace --skip-core-file --loose-innodb-buffer-pool-size=32M diff --git a/mysql-test/suite/parts/t/partition_debug_sync_innodb-master.opt b/mysql-test/suite/parts/t/partition_debug_sync_innodb-master.opt deleted file mode 100644 index 115a0ba2cc8..00000000000 --- a/mysql-test/suite/parts/t/partition_debug_sync_innodb-master.opt +++ /dev/null @@ -1 +0,0 @@ ---loose-innodb_file_per_table diff --git a/mysql-test/suite/sys_vars/r/innodb_file_per_table_basic.result b/mysql-test/suite/sys_vars/r/innodb_file_per_table_basic.result index 9780357e69f..9d7555335a7 100644 --- a/mysql-test/suite/sys_vars/r/innodb_file_per_table_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_file_per_table_basic.result @@ -9,18 +9,26 @@ COUNT(@@GLOBAL.innodb_file_per_table) 1 Expected '#---------------------BS_STVARS_028_02----------------------#' SET @@global.innodb_file_per_table = 0; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SELECT @@global.innodb_file_per_table; @@global.innodb_file_per_table 0 SET @@global.innodb_file_per_table ='On' ; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SELECT @@global.innodb_file_per_table; @@global.innodb_file_per_table 1 SET @@global.innodb_file_per_table ='Off' ; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SELECT @@global.innodb_file_per_table; @@global.innodb_file_per_table 0 SET @@global.innodb_file_per_table = 1; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SELECT @@global.innodb_file_per_table; @@global.innodb_file_per_table 1 @@ -64,6 +72,8 @@ COUNT(@@GLOBAL.innodb_file_per_table) SELECT innodb_file_per_table = @@SESSION.innodb_file_per_table; ERROR 42S22: Unknown column 'innodb_file_per_table' in 'field list' SET @@global.innodb_file_per_table = @start_global_value; +Warnings: +Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release SELECT @@global.innodb_file_per_table; @@global.innodb_file_per_table 1 diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 08f55baf1d3..86fc747faed 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -18846,7 +18846,7 @@ static MYSQL_SYSVAR_UINT(fast_shutdown, srv_fast_shutdown, fast_shutdown_validate, NULL, 1, 0, 3, 0); static MYSQL_SYSVAR_BOOL(file_per_table, srv_file_per_table, - PLUGIN_VAR_NOCMDARG, + PLUGIN_VAR_NOCMDARG | PLUGIN_VAR_DEPRECATED, "Stores each InnoDB table to an .ibd file in the database dir.", NULL, NULL, TRUE); |