MDEV-4750 follow-up: Reduce disabling innodb_stats_persistent

This essentially reverts commit 4e89ec6692786bc1cbdce64d43d8e85a5d247dab
and only disables InnoDB persistent statistics for tests where it is
desirable. By design, InnoDB persistent statistics will not be updated
except by ANALYZE TABLE or by STATS_AUTO_RECALC.

The internal transactions that update persistent InnoDB statistics
in background tasks (with innodb_stats_auto_recalc=ON) may cause
nondeterministic query plans or interfere with some tests that deal
with other InnoDB internals, such as the purge of transaction history.

7 files changed, 24 insertions, 4 deletions

diff --git a/mysql-test/suite/parts/r/longname.result b/mysql-test/suite/parts/r/longname.result
index 1b8f823c98f..a54de1e295d 100644
--- a/mysql-test/suite/parts/r/longname.result
+++ b/mysql-test/suite/parts/r/longname.result
@@ -14,6 +14,10 @@ PARTITION test_jfg_partition_name_with_60_chars_1234567890123456789012 VALUES LE
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
 select database_name, table_name, length(table_name) from mysql.innodb_table_stats where database_name = 'mysqltest1';
 database_name	table_name	length(table_name)
+mysqltest1	test_jfg_table_name_with_64_chars_123456789012345678901234567890#P#pmax#SP#pmaxsp0	82
+mysqltest1	test_jfg_table_name_with_64_chars_123456789012345678901234567890#P#pmax#SP#pmaxsp1	82
+mysqltest1	test_jfg_table_name_with_64_chars_123456789012345678901234567890#P#test_jfg_partition_name_with_60_chars_1234567890123456789012#SP#test_jfg_partition_name_with_60_chars_1234567890123456789012sp0	194
+mysqltest1	test_jfg_table_name_with_64_chars_123456789012345678901234567890#P#test_jfg_partition_name_with_60_chars_1234567890123456789012#SP#test_jfg_partition_name_with_60_chars_1234567890123456789012sp1	194
 CREATE TABLE mysqltest1.éééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé (
 id int(10) unsigned NOT NULL,
 id2 int(10) unsigned NOT NULL,
diff --git a/mysql-test/suite/parts/r/partition_alter1_2_innodb.result b/mysql-test/suite/parts/r/partition_alter1_2_innodb.result
index 3f720024f72..a71cae0ca17 100644
--- a/mysql-test/suite/parts/r/partition_alter1_2_innodb.result
+++ b/mysql-test/suite/parts/r/partition_alter1_2_innodb.result
@@ -1,4 +1,6 @@
 SET @max_row = 20;
+SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent=0;
 SET @@session.default_storage_engine = 'InnoDB';
 
 #------------------------------------------------------------------------
@@ -36634,3 +36636,4 @@ DROP TABLE IF EXISTS t1;
 DROP TABLE IF EXISTS t0_aux;
 DROP TABLE IF EXISTS t0_definition;
 DROP TABLE IF EXISTS t0_template;
+SET GLOBAL innodb_stats_persistent=@save_stats_persistent;
diff --git a/mysql-test/suite/parts/r/partition_alter3_innodb.result b/mysql-test/suite/parts/r/partition_alter3_innodb.result
index a859f8200c0..66531c2b699 100644
--- a/mysql-test/suite/parts/r/partition_alter3_innodb.result
+++ b/mysql-test/suite/parts/r/partition_alter3_innodb.result
@@ -1,4 +1,6 @@
 SET @max_row = 20;
+SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent=0;
 SET @@session.default_storage_engine = 'InnoDB';
 
 #------------------------------------------------------------------------
@@ -808,3 +810,4 @@ DROP TABLE IF EXISTS t1;
 DROP TABLE IF EXISTS t0_aux;
 DROP TABLE IF EXISTS t0_definition;
 DROP TABLE IF EXISTS t0_template;
+SET GLOBAL innodb_stats_persistent=@save_stats_persistent;
diff --git a/mysql-test/suite/parts/r/partition_debug_sync_innodb.result b/mysql-test/suite/parts/r/partition_debug_sync_innodb.result
index 4841c93e29f..7388eb73ea8 100644
--- a/mysql-test/suite/parts/r/partition_debug_sync_innodb.result
+++ b/mysql-test/suite/parts/r/partition_debug_sync_innodb.result
@@ -36,7 +36,7 @@ DROP TABLE t1;
 # Bug#50561: ALTER PARTITIONS does not have adequate lock, breaks with
 #            concurrent I_S query
 create table t1 (a int)
-engine = innodb
+engine = innodb stats_persistent=0
 partition by range (a)
 (partition p0 values less than MAXVALUE);
 insert into t1 values (1), (11), (21), (33);
@@ -50,7 +50,7 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0
  PARTITION BY RANGE (`a`)
 (PARTITION `p0` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
 db.opt
@@ -89,7 +89,7 @@ SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) DEFAULT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0
  PARTITION BY RANGE (`a`)
 (PARTITION `p0` VALUES LESS THAN (10) ENGINE = InnoDB,
  PARTITION `p10` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
diff --git a/mysql-test/suite/parts/t/partition_alter1_2_innodb.test b/mysql-test/suite/parts/t/partition_alter1_2_innodb.test
index 71f9d33c72b..5293e547dbf 100644
--- a/mysql-test/suite/parts/t/partition_alter1_2_innodb.test
+++ b/mysql-test/suite/parts/t/partition_alter1_2_innodb.test
@@ -71,6 +71,9 @@ let $do_pk_tests= 1;
 #     and smaller than the maximum value of SIGNED INTEGER
 let $MAX_VALUE= (2147483646);
 
+SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent=0;
+
 # Generate the prerequisites ($variables, @variables, tables) needed
 --source suite/parts/inc/partition.pre
 
@@ -87,3 +90,5 @@ let $MAX_VALUE= (2147483646);
 #------------------------------------------------------------------------------#
 # Cleanup
 --source suite/parts/inc/partition_cleanup.inc
+
+SET GLOBAL innodb_stats_persistent=@save_stats_persistent;
diff --git a/mysql-test/suite/parts/t/partition_alter3_innodb.test b/mysql-test/suite/parts/t/partition_alter3_innodb.test
index c63cf00bc53..d86c0e1122d 100644
--- a/mysql-test/suite/parts/t/partition_alter3_innodb.test
+++ b/mysql-test/suite/parts/t/partition_alter3_innodb.test
@@ -63,6 +63,9 @@ let $do_pk_tests= 1;
 #     and smaller than the maximum value of SIGNED INTEGER
 let $MAX_VALUE= (2147483646);
 
+SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent=0;
+
 # Generate the prerequisites ($variables, @variables, tables) needed
 --source suite/parts/inc/partition.pre
 
@@ -79,3 +82,5 @@ let $MAX_VALUE= (2147483646);
 #------------------------------------------------------------------------------#
 # Cleanup
 --source suite/parts/inc/partition_cleanup.inc
+
+SET GLOBAL innodb_stats_persistent=@save_stats_persistent;
diff --git a/mysql-test/suite/parts/t/partition_debug_sync_innodb.test b/mysql-test/suite/parts/t/partition_debug_sync_innodb.test
index d73333c3b8d..9d2557a64e6 100644
--- a/mysql-test/suite/parts/t/partition_debug_sync_innodb.test
+++ b/mysql-test/suite/parts/t/partition_debug_sync_innodb.test
@@ -45,7 +45,7 @@ DROP TABLE t1;
 --echo # Bug#50561: ALTER PARTITIONS does not have adequate lock, breaks with
 --echo #            concurrent I_S query
 create table t1 (a int)
-engine = innodb
+engine = innodb stats_persistent=0
 partition by range (a)
 (partition p0 values less than MAXVALUE);
 insert into t1 values (1), (11), (21), (33);