MDEV-29986 Set innodb_undo_log_truncate=ON and innodb_undo_tablespaces=3

Starting with commit baf276e6d4a44fe7cdf3b435c0153da0a42af2b6 (MDEV-19229)
the parameter innodb_undo_tablespaces can be increased from its
previous default value 0 while allowing an upgrade from old databases.

We will change the default settings to innodb_undo_tablespaces=3
and innodb_undo_log_truncate=ON, so that the space occupied by
possible bursts of undo log records will be eventually reclaimed.

In --suite=parts, we execute CHECK TABLE t1 instead of
CHECK TABLE t1 EXTENDED because occasionally, a warning (not an error!)
of unpurged history may be issued. This is something related to
commit ab0190101b0587e0e03b2d75a967050b9a85fd1b and will have to be
investigated separately.

13 files changed, 43 insertions, 15 deletions

diff --git a/extra/mariabackup/xtrabackup.cc b/extra/mariabackup/xtrabackup.cc
index 60d0a5ab9ab..8311c26c5cf 100644
--- a/extra/mariabackup/xtrabackup.cc
+++ b/extra/mariabackup/xtrabackup.cc
@@ -1653,7 +1653,7 @@ struct my_option xb_server_options[] =
   {"innodb_undo_tablespaces", OPT_INNODB_UNDO_TABLESPACES,
    "Number of undo tablespaces to use.",
    (G_PTR*)&srv_undo_tablespaces, (G_PTR*)&srv_undo_tablespaces,
-   0, GET_UINT, REQUIRED_ARG, 0, 0, 126, 0, 1, 0},
+   0, GET_UINT, REQUIRED_ARG, 3, 0, 126, 0, 1, 0},
 
   {"innodb_compression_level", OPT_INNODB_COMPRESSION_LEVEL,
    "Compression level used for zlib compression.",
diff --git a/mysql-test/suite/encryption/r/innodb-spatial-index.result b/mysql-test/suite/encryption/r/innodb-spatial-index.result
index 66c3edcd109..6bd22d6c25f 100644
--- a/mysql-test/suite/encryption/r/innodb-spatial-index.result
+++ b/mysql-test/suite/encryption/r/innodb-spatial-index.result
@@ -38,6 +38,9 @@ INSERT INTO t2 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)')
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION > 0;
 NAME
 innodb_system
+innodb_undo001
+innodb_undo002
+innodb_undo003
 mysql/innodb_index_stats
 mysql/innodb_table_stats
 mysql/transaction_registry
diff --git a/mysql-test/suite/innodb/r/innodb-system-table-view.result b/mysql-test/suite/innodb/r/innodb-system-table-view.result
index 580ed08e424..5be25e62ae2 100644
--- a/mysql-test/suite/innodb/r/innodb-system-table-view.result
+++ b/mysql-test/suite/innodb/r/innodb-system-table-view.result
@@ -10,7 +10,7 @@ TABLE_ID	NAME	FLAG	N_COLS	SPACE	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE
 11	SYS_FOREIGN	0	7	0	Redundant	0	System
 12	SYS_FOREIGN_COLS	0	7	0	Redundant	0	System
 13	SYS_VIRTUAL	0	6	0	Redundant	0	System
-16	mysql/transaction_registry	33	8	3	Dynamic	0	Single
+16	mysql/transaction_registry	33	8	6	Dynamic	0	Single
 SELECT table_id,pos,mtype,prtype,len,name
 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
 WHERE table_id NOT IN (@table_stats_id, @index_stats_id)
diff --git a/mysql-test/suite/innodb/t/encryption_threads_shutdown.test b/mysql-test/suite/innodb/t/encryption_threads_shutdown.test
index 5a63df5db86..d6e58a19ad9 100644
--- a/mysql-test/suite/innodb/t/encryption_threads_shutdown.test
+++ b/mysql-test/suite/innodb/t/encryption_threads_shutdown.test
@@ -10,10 +10,20 @@ call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE faile
 --let $MYSQLD_DATADIR=`SELECT @@datadir`
 --let $DATA= $MYSQLD_DATADIR/ibdata1
 --let $DATACOPY=$MYSQLTEST_VARDIR/tmp/ibdata1
+--let $UNDO_1= $MYSQLD_DATADIR/undo001
+--let $UNDO_2= $MYSQLD_DATADIR/undo002
+--let $UNDO_3= $MYSQLD_DATADIR/undo003
+--let $UNDO_1_COPY=$MYSQLTEST_VARDIR/tmp/undo001
+--let $UNDO_2_COPY=$MYSQLTEST_VARDIR/tmp/undo002
+--let $UNDO_3_COPY=$MYSQLTEST_VARDIR/tmp/undo003
+
 CREATE TABLE t(a INT) ENGINE=InnoDB;
 --source include/kill_mysqld.inc
 # Move the file to cause srv_init_abort_low() call from srv_start()
 --move_file $DATA $DATACOPY
+--move_file $UNDO_1 $UNDO_1_COPY
+--move_file $UNDO_2 $UNDO_2_COPY
+--move_file $UNDO_3 $UNDO_3_COPY
 
 # If the bug is not fixed, the server will hang here. Note that the test is
 # unstable because the condition
@@ -28,5 +38,8 @@ CREATE TABLE t(a INT) ENGINE=InnoDB;
 SELECT * FROM t;
 --source include/kill_mysqld.inc
 --move_file $DATACOPY $DATA
+--move_file $UNDO_1_COPY $UNDO_1
+--move_file $UNDO_2_COPY $UNDO_2
+--move_file $UNDO_3_COPY $UNDO_3
 --source include/start_mysqld.inc
 DROP TABLE t;
diff --git a/mysql-test/suite/innodb/t/log_data_file_size.opt b/mysql-test/suite/innodb/t/log_data_file_size.opt
index d9a364a3287..20e2cf0dc40 100644
--- a/mysql-test/suite/innodb/t/log_data_file_size.opt
+++ b/mysql-test/suite/innodb/t/log_data_file_size.opt
@@ -1,2 +1,3 @@
 --loose-innodb-sys-indexes
 --innodb-data-file-path=ibdata1:1M:autoextend
+--innodb-undo-tablespaces=0
diff --git a/mysql-test/suite/innodb/t/row_format_redundant.opt b/mysql-test/suite/innodb/t/row_format_redundant.opt
index c44c611ed60..3147bad4713 100644
--- a/mysql-test/suite/innodb/t/row_format_redundant.opt
+++ b/mysql-test/suite/innodb/t/row_format_redundant.opt
@@ -1 +1,2 @@
 --innodb-checksum-algorithm=crc32
+--innodb-undo-tablespaces=0
diff --git a/mysql-test/suite/innodb/t/table_flags.opt b/mysql-test/suite/innodb/t/table_flags.opt
index bca674950d2..3147bad4713 100644
--- a/mysql-test/suite/innodb/t/table_flags.opt
+++ b/mysql-test/suite/innodb/t/table_flags.opt
@@ -1,2 +1,2 @@
 --innodb-checksum-algorithm=crc32
---skip-innodb-read-only-compressed
+--innodb-undo-tablespaces=0
diff --git a/mysql-test/suite/parts/inc/partition_check.inc b/mysql-test/suite/parts/inc/partition_check.inc
index ed323dfa245..86a3ada5c54 100644
--- a/mysql-test/suite/parts/inc/partition_check.inc
+++ b/mysql-test/suite/parts/inc/partition_check.inc
@@ -1116,7 +1116,16 @@ if ($debug)
 #-------------------------------------------------------------------------------
 # 10   ANALYZE/CHECK/CHECKSUM
 ANALYZE  TABLE t1;
+if ($engine != "'InnoDB'") {
 CHECK    TABLE t1 EXTENDED;
+}
+if ($engine == "'InnoDB'") {
+# Prevent occasional "Unpurged clustered index record" warnings
+--disable_query_log
+--echo CHECK    TABLE t1 EXTENDED;
+CHECK TABLE t1;
+--enable_query_log
+}
 # Checksum depends on @max_row so we have to unify the value
 --replace_column 2 <some_value>
 CHECKSUM TABLE t1 EXTENDED;
diff --git a/mysql-test/suite/sys_vars/r/innodb_undo_log_truncate_basic.result b/mysql-test/suite/sys_vars/r/innodb_undo_log_truncate_basic.result
index eb42f4965ea..4b10e71106f 100644
--- a/mysql-test/suite/sys_vars/r/innodb_undo_log_truncate_basic.result
+++ b/mysql-test/suite/sys_vars/r/innodb_undo_log_truncate_basic.result
@@ -1,7 +1,7 @@
 SET @start_global_value = @@global.innodb_undo_log_truncate;
 SELECT @start_global_value;
 @start_global_value
-0
+1
 '#---------------------BS_STVARS_028_01----------------------#'
 SELECT COUNT(@@GLOBAL.innodb_undo_log_truncate);
 COUNT(@@GLOBAL.innodb_undo_log_truncate)
@@ -66,4 +66,4 @@ ERROR 42S22: Unknown column 'innodb_undo_log_truncate' in 'field list'
 SET @@global.innodb_undo_log_truncate = @start_global_value;
 SELECT @@global.innodb_undo_log_truncate;
 @@global.innodb_undo_log_truncate
-0
+1
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 53c02d96206..e4837d4e15e 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -1641,7 +1641,7 @@ READ_ONLY	YES
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	INNODB_UNDO_LOG_TRUNCATE
 SESSION_VALUE	NULL
-DEFAULT_VALUE	OFF
+DEFAULT_VALUE	ON
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BOOLEAN
 VARIABLE_COMMENT	Enable or Disable Truncate of UNDO tablespace.
@@ -1653,7 +1653,7 @@ READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	INNODB_UNDO_TABLESPACES
 SESSION_VALUE	NULL
-DEFAULT_VALUE	0
+DEFAULT_VALUE	3
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	INT UNSIGNED
 VARIABLE_COMMENT	Number of undo tablespaces to use.
diff --git a/plugin/hashicorp_key_management/mysql-test/vault/t/hashicorp_key_rotation_age.test b/plugin/hashicorp_key_management/mysql-test/vault/t/hashicorp_key_rotation_age.test
index ce99406ab06..c446036aaae 100644
--- a/plugin/hashicorp_key_management/mysql-test/vault/t/hashicorp_key_rotation_age.test
+++ b/plugin/hashicorp_key_management/mysql-test/vault/t/hashicorp_key_rotation_age.test
@@ -33,7 +33,7 @@ let $restart_parameters=$default_parameters --innodb_encryption_threads=5 --inno
 
 --echo # Wait until encryption threads have encrypted all tablespaces
 
---let $tables_count= `select count(*) + 1 from information_schema.tables where engine = 'InnoDB'`
+--let $tables_count= `select count(*) + 1 + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'`
 --let $wait_timeout= 600
 --let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
 --source include/wait_condition.inc
@@ -63,7 +63,7 @@ alter table t33 encryption_key_id=222;
 
 --echo # Wait until encryption threads have encrypted all tablespaces
 
---let $tables_count= `select count(*) from information_schema.tables where engine = 'InnoDB'`
+--let $tables_count= `select count(*) + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'`
 --let $wait_timeout= 600
 --let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
 --source include/wait_condition.inc
@@ -77,7 +77,7 @@ set global innodb_encrypt_tables = OFF;
 
 --echo # Wait until encryption threads to decrypt all encrypted tablespaces
 
---let $tables_count= `select count(*) - 1 from information_schema.tables where engine = 'InnoDB'`
+--let $tables_count= `select count(*) - 1 + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'`
 --let $wait_timeout= 600
 --let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND ROTATING_OR_FLUSHING = 0;
 --source include/wait_condition.inc
@@ -105,7 +105,7 @@ set global innodb_encrypt_tables = ON;
 
 --echo # Wait until encryption threads to encrypt all unencrypted tablespaces
 
---let $tables_count= `select count(*) from information_schema.tables where engine = 'InnoDB'`
+--let $tables_count= `select count(*) + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'`
 --let $wait_timeout= 600
 --let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
 --source include/wait_condition.inc
diff --git a/storage/innobase/buf/buf0buf.cc b/storage/innobase/buf/buf0buf.cc
index a75852b82fd..fcd1f2cfa62 100644
--- a/storage/innobase/buf/buf0buf.cc
+++ b/storage/innobase/buf/buf0buf.cc
@@ -588,7 +588,7 @@ bool buf_page_is_corrupted(bool check_lsn, const byte *read_buf,
 		DBUG_EXECUTE_IF(
 			"page_intermittent_checksum_mismatch", {
 			static int page_counter;
-			if (page_counter++ == 3) {
+			if (page_counter++ == 6) {
 				crc32++;
 			}
 		});
@@ -723,7 +723,8 @@ bool buf_page_is_corrupted(bool check_lsn, const byte *read_buf,
 			DBUG_EXECUTE_IF(
 				"page_intermittent_checksum_mismatch", {
 				static int page_counter;
-				if (page_counter++ == 3) return true;
+				if (page_counter++ == 6)
+					return true;
 			});
 
 			if ((checksum_field1 != crc32
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 582e681468a..a4f5af839d1 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -19313,7 +19313,7 @@ static MYSQL_SYSVAR_UINT(undo_tablespaces, srv_undo_tablespaces,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
   "Number of undo tablespaces to use.",
   NULL, NULL,
-  0L,			/* Default seting */
+  3L,			/* Default seting */
   0L,			/* Minimum value */
   TRX_SYS_MAX_UNDO_SPACES, 0); /* Maximum value */
 
@@ -19334,7 +19334,7 @@ static MYSQL_SYSVAR_ULONG(purge_rseg_truncate_frequency,
 static MYSQL_SYSVAR_BOOL(undo_log_truncate, srv_undo_log_truncate,
   PLUGIN_VAR_OPCMDARG,
   "Enable or Disable Truncate of UNDO tablespace.",
-  NULL, NULL, FALSE);
+  NULL, NULL, TRUE);
 
 static MYSQL_SYSVAR_LONG(autoinc_lock_mode, innobase_autoinc_lock_mode,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,