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.

1 files changed, 24 insertions, 13 deletions

diff --git a/mysql-test/main/create_or_replace.result b/mysql-test/main/create_or_replace.result
index 1eaf87d190d..19d9f3e65a1 100644
--- a/mysql-test/main/create_or_replace.result
+++ b/mysql-test/main/create_or_replace.result
@@ -1,4 +1,3 @@
-drop table if exists t1,t2,t3;
 CREATE TABLE t2 (a int);
 INSERT INTO t2 VALUES(1),(2),(3);
 #
@@ -259,7 +258,8 @@ Note	1051	Unknown table 'test.t1,mysqltest2.t2'
 create table test.t1 (i int) engine=myisam;
 create table mysqltest2.t2 like test.t1;
 lock table test.t1 write, mysqltest2.t2 write;
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_BACKUP_DML	NULL	Backup lock		
@@ -272,7 +272,8 @@ ERROR 42000: A table must have at least 1 column
 show tables;
 Tables_in_test
 t2
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_BACKUP_DML	NULL	Backup lock		
@@ -281,14 +282,16 @@ THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
 create or replace table mysqltest2.t2;
 ERROR 42000: A table must have at least 1 column
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 create table t1 (i int);
 drop table t1;
 create table test.t1 (i int);
 create table mysqltest2.t2 like test.t1;
 lock table test.t1 write, mysqltest2.t2 write;
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_BACKUP_DML	NULL	Backup lock		
@@ -301,7 +304,8 @@ ERROR 42S21: Duplicate column name 'a'
 show tables;
 Tables_in_test
 t2
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_BACKUP_DML	NULL	Backup lock		
@@ -310,14 +314,16 @@ THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	mysqltest2	t2
 create or replace table mysqltest2.t2 (a int) select 1 as 'a', 2 as 'a';
 ERROR 42S21: Duplicate column name 'a'
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 create table t1 (i int);
 drop table t1;
 create table test.t1 (i int) engine=innodb;
 create table mysqltest2.t2 like test.t1;
 lock table test.t1 write, mysqltest2.t2 write;
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
@@ -329,7 +335,8 @@ drop table test.t1,mysqltest2.t2;
 create table test.t1 (i int) engine=aria transactional=1 checksum=1;
 create table mysqltest2.t2 like test.t1;
 lock table test.t1 write, mysqltest2.t2 write;
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_INTENTION_EXCLUSIVE	NULL	Schema metadata lock	mysqltest2	
@@ -424,7 +431,8 @@ drop view t1;
 #
 create table t1 (a int);
 lock table t1 write, t2 read;
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_BACKUP_DML	NULL	Backup lock		
@@ -432,7 +440,8 @@ THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
 #	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
 create or replace table t1 (i int);
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_BACKUP_DML	NULL	Backup lock		
@@ -440,7 +449,8 @@ THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
 #	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
 create or replace table t1 like t2;
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_BACKUP_DML	NULL	Backup lock		
@@ -448,7 +458,8 @@ THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_SHARED_NO_READ_WRITE	NULL	Table metadata lock	test	t1
 #	MDL_SHARED_READ	NULL	Table metadata lock	test	t2
 create or replace table t1 select 1 as f1;
-select * from information_schema.metadata_lock_info;
+select * from information_schema.metadata_lock_info
+where table_name not like 'innodb_%_stats';
 THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
 #	MDL_BACKUP_DDL	NULL	Backup lock		
 #	MDL_BACKUP_DML	NULL	Backup lock