Merge remote-tracking branch 'origin/10.3' into 10.4

17 files changed, 163 insertions, 34 deletions

diff --git a/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result b/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
index e2d503cf134..fa15be0352f 100644
--- a/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
+++ b/mysql-test/suite/gcol/r/innodb_virtual_debug_purge.result
@@ -175,8 +175,38 @@ SET DEBUG_SYNC='now WAIT_FOR halfway';
 COMMIT;
 InnoDB		0 transactions not purged
 SET DEBUG_SYNC='now SIGNAL purged';
-disconnect prevent_purge;
 connection default;
 DROP TABLE t1;
+CREATE TABLE t1 (y YEAR, vy YEAR AS (y) VIRTUAL UNIQUE, pk INT PRIMARY KEY)
+ENGINE=InnoDB;
+INSERT INTO t1 (pk,y) VALUES (1,2022);
+CREATE TABLE t2(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
+SET GLOBAL debug_dbug = '+d,ib_purge_virtual_index_callback';
+BEGIN;
+INSERT INTO t2(f1) VALUES(1);
+connection prevent_purge;
+SET DEBUG_SYNC=RESET;
+start transaction with consistent snapshot;
+connection default;
+COMMIT;
+connect truncate,localhost,root,,;
+REPLACE INTO t1(pk, y) SELECT pk,y FROM t1;
+SET DEBUG_SYNC='row_trunc_before_dict_lock SIGNAL commit WAIT_FOR release';
+TRUNCATE TABLE t1;
+connection prevent_purge;
+SET DEBUG_SYNC='now WAIT_FOR commit';
+COMMIT;
+SET DEBUG_SYNC='now SIGNAL purge_start';
+disconnect prevent_purge;
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR purge_start';
+InnoDB		2 transactions not purged
+SET DEBUG_SYNC='now SIGNAL release';
+SET GLOBAL debug_dbug=@old_dbug;
+connection truncate;
+disconnect truncate;
+connection default;
+InnoDB		0 transactions not purged
+DROP TABLE t1, t2;
 set debug_sync=reset;
 SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
index ad733eee3a7..1f67c8c8491 100644
--- a/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
+++ b/mysql-test/suite/gcol/t/innodb_virtual_debug_purge.test
@@ -124,7 +124,7 @@ SELECT * FROM t1;
 
 DROP TABLE t1;
 
-# Test adding virutal index on existing virtual column
+# Test adding index on existing virtual column
 CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b));
 
 INSERT INTO t1(a, b) VALUES (1, 1), (2, 2), (3, 3), (4, 4);
@@ -156,7 +156,9 @@ INSERT INTO t1(a, b) VALUES (8, 8);
 COMMIT;
 
 --echo # wait for purge to process the deleted/updated records.
+let $wait_all_purged=2;
 --source ../../innodb/include/wait_all_purged.inc
+let $wait_all_purged=0;
 
 SET DEBUG_SYNC= 'now SIGNAL purged';
 
@@ -215,12 +217,54 @@ SET DEBUG_SYNC='now WAIT_FOR halfway';
 COMMIT;
 --source ../../innodb/include/wait_all_purged.inc
 SET DEBUG_SYNC='now SIGNAL purged';
-disconnect prevent_purge;
 
 connection default;
 reap;
 DROP TABLE t1;
 
+CREATE TABLE t1 (y YEAR, vy YEAR AS (y) VIRTUAL UNIQUE, pk INT PRIMARY KEY)
+ENGINE=InnoDB;
+
+INSERT INTO t1 (pk,y) VALUES (1,2022);
+CREATE TABLE t2(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
+
+SET GLOBAL debug_dbug = '+d,ib_purge_virtual_index_callback';
+
+BEGIN;
+INSERT INTO t2(f1) VALUES(1);
+connection prevent_purge;
+SET DEBUG_SYNC=RESET;
+start transaction with consistent snapshot;
+connection default;
+COMMIT;
+
+connect(truncate,localhost,root,,);
+REPLACE INTO t1(pk, y) SELECT pk,y FROM t1;
+SET DEBUG_SYNC='row_trunc_before_dict_lock SIGNAL commit WAIT_FOR release';
+send TRUNCATE TABLE t1;
+
+connection prevent_purge;
+SET DEBUG_SYNC='now WAIT_FOR commit';
+COMMIT;
+SET DEBUG_SYNC='now SIGNAL purge_start';
+disconnect prevent_purge;
+
+connection default;
+SET DEBUG_SYNC='now WAIT_FOR purge_start';
+let $wait_all_purged=2;
+--source ../../innodb/include/wait_all_purged.inc
+let $wait_all_purged=0;
+SET DEBUG_SYNC='now SIGNAL release';
+SET GLOBAL debug_dbug=@old_dbug;
+
+connection truncate;
+reap;
+disconnect truncate;
+
+connection default;
+--source ../../innodb/include/wait_all_purged.inc
+DROP TABLE t1, t2;
+
 --source include/wait_until_count_sessions.inc
 set debug_sync=reset;
 SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency;
diff --git a/mysql-test/suite/innodb/include/wait_all_purged.inc b/mysql-test/suite/innodb/include/wait_all_purged.inc
index 7dbb59a5d32..c7a16888829 100644
--- a/mysql-test/suite/innodb/include/wait_all_purged.inc
+++ b/mysql-test/suite/innodb/include/wait_all_purged.inc
@@ -1,12 +1,18 @@
 # Wait for everything to be purged.
 # The user should have set innodb_purge_rseg_truncate_frequency=1.
 
+if (!$wait_all_purged)
+{
+  let $wait_all_purged= 0;
+}
+let $remaining_expect= `select concat('InnoDB		',$wait_all_purged)`;
+
 let $wait_counter= 300;
 while ($wait_counter)
 {
     --replace_regex /.*History list length ([0-9]+).*/\1/
     let $remaining= `SHOW ENGINE INNODB STATUS`;
-    if ($remaining == 'InnoDB		0')
+    if ($remaining == $remaining_expect)
     {
         let $wait_counter= 0;
     }
diff --git a/mysql-test/suite/innodb/r/innodb_information_schema.result b/mysql-test/suite/innodb/r/innodb_information_schema.result
index c1625f2bc3c..766d5f47c2d 100644
--- a/mysql-test/suite/innodb/r/innodb_information_schema.result
+++ b/mysql-test/suite/innodb/r/innodb_information_schema.result
@@ -45,7 +45,7 @@ trx_last_foreign_key_error	varchar(256)	YES		NULL
 trx_is_read_only	int(1)	NO		0	
 trx_autocommit_non_locking	int(1)	NO		0	
 trx_state	trx_weight	trx_tables_in_use	trx_tables_locked	trx_rows_locked	trx_rows_modified	trx_concurrency_tickets	trx_isolation_level	trx_unique_checks	trx_foreign_key_checks
-RUNNING	4	0	1	7	1	0	REPEATABLE READ	1	1
+RUNNING	3	0	1	5	1	0	REPEATABLE READ	1	1
 trx_isolation_level	trx_unique_checks	trx_foreign_key_checks
 SERIALIZABLE	0	0
 trx_state	trx_isolation_level	trx_last_foreign_key_error
diff --git a/mysql-test/suite/innodb/r/monitor.result b/mysql-test/suite/innodb/r/monitor.result
index f5c1070ff94..4168a94928f 100644
--- a/mysql-test/suite/innodb/r/monitor.result
+++ b/mysql-test/suite/innodb/r/monitor.result
@@ -661,7 +661,21 @@ SELECT NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
 LIKE 'buffer_page_written_index_leaf';
 NAME	COUNT > 0
 buffer_page_written_index_leaf	1
+DROP TABLE t1;
+CREATE TABLE t1(id INT PRIMARY KEY, a INT, b CHAR(1), UNIQUE KEY u(a,b))
+ENGINE=InnoDB;
+SET @start = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
+= 'lock_rec_lock_created');
+BEGIN;
+INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
+DELETE FROM t1 WHERE a = 9999 AND b='b';
+COMMIT;
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
+= 'lock_rec_lock_created');
+SELECT @end - @start;
+@end - @start
+0
+DROP TABLE t1;
 SET GLOBAL innodb_monitor_enable=default;
 SET GLOBAL innodb_monitor_disable=default;
 SET GLOBAL innodb_monitor_reset_all=default;
-DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test
index 9d85d281361..27056089767 100644
--- a/mysql-test/suite/innodb/t/instant_alter_debug.test
+++ b/mysql-test/suite/innodb/t/instant_alter_debug.test
@@ -192,25 +192,9 @@ ROLLBACK;
 disconnect stop_purge;
 
 # Wait for purge to empty the table.
-# (This is based on wait_all_purged.inc, but there are 2 transactions
-# from the pending ALTER TABLE t1 FORCE.)
-
-let $wait_counter= 300;
-while ($wait_counter)
-{
-    --replace_regex /.*History list length ([0-9]+).*/\1/
-    let $remaining= `SHOW ENGINE INNODB STATUS`;
-    if ($remaining == 'InnoDB		2')
-    {
-        let $wait_counter= 0;
-    }
-    if ($wait_counter)
-    {
-        real_sleep 0.1;
-        dec $wait_counter;
-    }
-}
-echo $remaining transactions not purged;
+let $wait_all_purged=2;
+--source include/wait_all_purged.inc
+let $wait_all_purged=0;
 
 SET DEBUG_SYNC='now SIGNAL logged';
 disconnect ddl;
diff --git a/mysql-test/suite/innodb/t/monitor.test b/mysql-test/suite/innodb/t/monitor.test
index dfae93694bf..3535c9c85ad 100644
--- a/mysql-test/suite/innodb/t/monitor.test
+++ b/mysql-test/suite/innodb/t/monitor.test
@@ -422,10 +422,27 @@ UNLOCK TABLES;
 SELECT NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
 LIKE 'buffer_page_written_index_leaf';
 
+DROP TABLE t1;
+
+CREATE TABLE t1(id INT PRIMARY KEY, a INT, b CHAR(1), UNIQUE KEY u(a,b))
+ENGINE=InnoDB;
+
+SET @start = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
+= 'lock_rec_lock_created');
+
+BEGIN;
+INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
+DELETE FROM t1 WHERE a = 9999 AND b='b';
+COMMIT;
+
+SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
+= 'lock_rec_lock_created');
+SELECT @end - @start;
+
+DROP TABLE t1;
+
 --disable_warnings
 SET GLOBAL innodb_monitor_enable=default;
 SET GLOBAL innodb_monitor_disable=default;
 SET GLOBAL innodb_monitor_reset_all=default;
 --enable_warnings
-
-DROP TABLE t1;
diff --git a/mysql-test/suite/mariabackup/incremental_encrypted.result b/mysql-test/suite/mariabackup/incremental_encrypted.result
index e8f81e9fa49..e9525c9c4b7 100644
--- a/mysql-test/suite/mariabackup/incremental_encrypted.result
+++ b/mysql-test/suite/mariabackup/incremental_encrypted.result
@@ -2,6 +2,7 @@ call mtr.add_suppression("InnoDB: New log files created");
 CREATE TABLE t(i INT) ENGINE INNODB ENCRYPTED=YES;
 INSERT INTO t VALUES(1);
 # Create full backup , modify table, then create incremental/differential backup
+SET GLOBAL innodb_flush_log_at_trx_commit = 1;
 INSERT INTO t VALUES(2);
 SELECT * FROM t;
 i
diff --git a/mysql-test/suite/mariabackup/incremental_encrypted.test b/mysql-test/suite/mariabackup/incremental_encrypted.test
index 8bcada493c6..e618ac4f79e 100644
--- a/mysql-test/suite/mariabackup/incremental_encrypted.test
+++ b/mysql-test/suite/mariabackup/incremental_encrypted.test
@@ -20,6 +20,7 @@ echo # Create full backup , modify table, then create incremental/differential b
 exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf  --backup --target-dir=$basedir;
 --enable_result_log
 
+SET GLOBAL innodb_flush_log_at_trx_commit = 1;
 INSERT INTO t VALUES(2);
 SELECT * FROM t;
 
diff --git a/mysql-test/suite/mariabackup/suite.opt b/mysql-test/suite/mariabackup/suite.opt
index 3b5cc4f4c45..de3637814b2 100644
--- a/mysql-test/suite/mariabackup/suite.opt
+++ b/mysql-test/suite/mariabackup/suite.opt
@@ -1 +1 @@
---innodb --loose-changed_page_bitmaps --innodb-sys-tables
+--innodb --loose-changed_page_bitmaps --innodb-sys-tables --innodb-flush-log-at-trx-commit=2
diff --git a/mysql-test/suite/mariabackup/unsupported_redo.result b/mysql-test/suite/mariabackup/unsupported_redo.result
index 543e564d8a8..a1f95c099cd 100644
--- a/mysql-test/suite/mariabackup/unsupported_redo.result
+++ b/mysql-test/suite/mariabackup/unsupported_redo.result
@@ -5,9 +5,6 @@ call mtr.add_suppression("InnoDB: If you are installing InnoDB, remember that yo
 call mtr.add_suppression("InnoDB: Ignoring tablespace for `test`\\.`t21` because it could not be opened");
 call mtr.add_suppression("InnoDB: Cannot open datafile for read-only: ");
 call mtr.add_suppression("Table .* in the InnoDB data dictionary has tablespace id .*, but tablespace with that id or name does not exist");
-SELECT @@GLOBAL.innodb_flush_log_at_trx_commit;
-@@GLOBAL.innodb_flush_log_at_trx_commit
-1
 CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB;
 ALTER TABLE t1 FORCE, ALGORITHM=INPLACE;
 # Fails during full backup
diff --git a/mysql-test/suite/mariabackup/unsupported_redo.test b/mysql-test/suite/mariabackup/unsupported_redo.test
index 9d54c5bbe87..319ee2c7571 100644
--- a/mysql-test/suite/mariabackup/unsupported_redo.test
+++ b/mysql-test/suite/mariabackup/unsupported_redo.test
@@ -10,7 +10,6 @@ call mtr.add_suppression("Table .* in the InnoDB data dictionary has tablespace
 let $basedir=$MYSQLTEST_VARDIR/tmp/backup;
 let $incremental_dir=$MYSQLTEST_VARDIR/tmp/backup_inc1;
 
-SELECT @@GLOBAL.innodb_flush_log_at_trx_commit;
 CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB;
 --source ../../suite/innodb/include/no_checkpoint_start.inc
 ALTER TABLE t1 FORCE, ALGORITHM=INPLACE;
diff --git a/mysql-test/suite/rpl/include/rpl_row_001.test b/mysql-test/suite/rpl/include/rpl_row_001.test
index 8eb684e0dff..97b3a93b4ed 100644
--- a/mysql-test/suite/rpl/include/rpl_row_001.test
+++ b/mysql-test/suite/rpl/include/rpl_row_001.test
@@ -7,8 +7,15 @@ eval LOAD DATA INFILE '$LOAD_FILE' INTO TABLE t1;
 SELECT * FROM t1  ORDER BY word LIMIT 10;
 
 #
+# Save password row for root
+#
+
+create temporary table tmp select * from mysql.user where host="localhost" and user="root";
+
+#
 # Test slave with wrong password
 #
+
 save_master_pos;
 connection slave;
 sync_with_master;
@@ -24,6 +31,7 @@ connection master;
 real_sleep 2;
 SET PASSWORD FOR root@"localhost" = PASSWORD('');
 # Give slave time to connect (will retry every second)
+
 sleep 2;
 
 CREATE TABLE t3(n INT);
@@ -80,4 +88,9 @@ SELECT n FROM t1;
 
 connection master;
 DROP TABLE t1;
+
+# resttore old passwords
+replace into mysql.user select * from tmp;
+drop temporary table tmp;
+
 sync_slave_with_master;
diff --git a/mysql-test/suite/rpl/r/rpl_15867.result b/mysql-test/suite/rpl/r/rpl_15867.result
new file mode 100644
index 00000000000..9cb63266a29
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_15867.result
@@ -0,0 +1,9 @@
+include/master-slave.inc
+[connection master]
+CREATE TEMPORARY TABLE t (i INT);
+CREATE TABLE t AS SELECT * FROM t;
+connection slave;
+connection master;
+DROP TEMPORARY TABLE t;
+DROP TABLE t;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/r/rpl_row_001.result b/mysql-test/suite/rpl/r/rpl_row_001.result
index 94985548f59..b5b2b370e61 100644
--- a/mysql-test/suite/rpl/r/rpl_row_001.result
+++ b/mysql-test/suite/rpl/r/rpl_row_001.result
@@ -15,6 +15,7 @@ Aaron
 Aaron
 Ababa
 Ababa
+create temporary table tmp select * from mysql.user where host="localhost" and user="root";
 connection slave;
 STOP SLAVE;
 connection master;
@@ -65,5 +66,7 @@ n
 3456
 connection master;
 DROP TABLE t1;
+replace into mysql.user select * from tmp;
+drop temporary table tmp;
 connection slave;
 include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_15867.test b/mysql-test/suite/rpl/t/rpl_15867.test
new file mode 100644
index 00000000000..6de39041bb1
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_15867.test
@@ -0,0 +1,11 @@
+--source include/master-slave.inc
+CREATE TEMPORARY TABLE t (i INT);
+CREATE TABLE t AS SELECT * FROM t;
+
+--sync_slave_with_master
+
+# Cleanup
+--connection master
+DROP TEMPORARY TABLE t;
+DROP TABLE t;
+--source include/rpl_end.inc
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index d2153cc560d..e98cf8e41ff 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -1326,9 +1326,9 @@ READ_ONLY	YES
 COMMAND_LINE_ARGUMENT	NONE
 VARIABLE_NAME	INNODB_LOCK_SCHEDULE_ALGORITHM
 SESSION_VALUE	NULL
-GLOBAL_VALUE	vats
+GLOBAL_VALUE	fcfs
 GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	vats
+DEFAULT_VALUE	fcfs
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	ENUM
 VARIABLE_COMMENT	The algorithm Innodb uses for deciding which locks to grant next when a lock is released. Possible values are FCFS grant the locks in First-Come-First-Served order; VATS use the Variance-Aware-Transaction-Scheduling algorithm, which uses an Eldest-Transaction-First heuristic.