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.

13 files changed, 7 insertions, 103 deletions

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;