Merge branch '10.6' into 10.8

20 files changed, 337 insertions, 61 deletions

diff --git a/mysql-test/suite/galera/r/galera_var_retry_autocommit.result b/mysql-test/suite/galera/r/galera_var_retry_autocommit.result
index 56c2c995402..50667b0a4fa 100644
--- a/mysql-test/suite/galera/r/galera_var_retry_autocommit.result
+++ b/mysql-test/suite/galera/r/galera_var_retry_autocommit.result
@@ -20,17 +20,25 @@ DROP TABLE t1;
 connection node_1;
 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB;
 SET SESSION wsrep_retry_autocommit = 1;
+SET GLOBAL debug_dbug = '+d,sync.wsrep_retry_autocommit';
 SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continue';
 INSERT INTO t1 (f1) VALUES (3);
 connection node_1a;
 SET DEBUG_SYNC = 'now WAIT_FOR before_cert';
 connection node_2;
 TRUNCATE TABLE t1;
-connection node_1;
+connection node_1a;
+SET DEBUG_SYNC = 'now WAIT_FOR wsrep_retry_autocommit_reached';
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 0
+SET DEBUG_SYNC = 'now SIGNAL wsrep_retry_autocommit_continue';
+connection node_1;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
 SET DEBUG_SYNC = 'RESET';
+SET GLOBAL debug_dbug = NULL;
 DROP TABLE t1;
 connection node_1;
 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB;
@@ -64,6 +72,8 @@ SET SESSION wsrep_retry_autocommit = 64;
 SET GLOBAL debug_dbug = '+d,sync.wsrep_retry_autocommit';
 SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continue EXECUTE 64';
 INSERT INTO t1 VALUES (5);
+connection node_2;
+connection node_1;
 connection node_1;
 SELECT COUNT(*) FROM t1;
 COUNT(*)
diff --git a/mysql-test/suite/galera/t/galera_var_retry_autocommit.test b/mysql-test/suite/galera/t/galera_var_retry_autocommit.test
index bd10e448e06..c58eba1410e 100644
--- a/mysql-test/suite/galera/t/galera_var_retry_autocommit.test
+++ b/mysql-test/suite/galera/t/galera_var_retry_autocommit.test
@@ -25,6 +25,8 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu
 SET DEBUG_SYNC = 'now WAIT_FOR before_cert';
 
 --connection node_2
+--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'
+--source include/wait_condition.inc
 TRUNCATE TABLE t1;
 
 --connection node_1
@@ -44,6 +46,7 @@ DROP TABLE t1;
 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=InnoDB;
 
 SET SESSION wsrep_retry_autocommit = 1;
+SET GLOBAL debug_dbug = '+d,sync.wsrep_retry_autocommit';
 SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continue';
 --send INSERT INTO t1 (f1) VALUES (3)
 
@@ -51,14 +54,21 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu
 SET DEBUG_SYNC = 'now WAIT_FOR before_cert';
 
 --connection node_2
+--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'
+--source include/wait_condition.inc
 TRUNCATE TABLE t1;
 
+--connection node_1a
+SET DEBUG_SYNC = 'now WAIT_FOR wsrep_retry_autocommit_reached';
+SELECT COUNT(*) FROM t1;
+SET DEBUG_SYNC = 'now SIGNAL wsrep_retry_autocommit_continue';
+
 --connection node_1
---error 0,ER_LOCK_DEADLOCK
 --reap
 SELECT COUNT(*) FROM t1;
 
 SET DEBUG_SYNC = 'RESET';
+SET GLOBAL debug_dbug = NULL;
 DROP TABLE t1;
 
 
@@ -79,6 +89,8 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu
 SET DEBUG_SYNC = 'now WAIT_FOR before_cert';
 
 --connection node_2
+--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'
+--source include/wait_condition.inc
 TRUNCATE TABLE t1;
 
 --connection node_1a
@@ -114,6 +126,11 @@ SET DEBUG_SYNC = 'wsrep_before_certification SIGNAL before_cert WAIT_FOR continu
 
 --send INSERT INTO t1 VALUES (5)
 
+--connection node_2
+--let $wait_condition = SELECT count(*)=1 FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'
+--source include/wait_condition.inc
+
+--connection node_1
 --disable_query_log
 --disable_result_log
 --let $count = 64
diff --git a/mysql-test/suite/galera_sr/r/MDEV-30838.result b/mysql-test/suite/galera_sr/r/MDEV-30838.result
new file mode 100644
index 00000000000..6997b9c4d5d
--- /dev/null
+++ b/mysql-test/suite/galera_sr/r/MDEV-30838.result
@@ -0,0 +1,15 @@
+connection node_2;
+connection node_1;
+CREATE TABLE t1 (f1 INTEGER PRIMARY KEY);
+SET SESSION wsrep_trx_fragment_size=1;
+START TRANSACTION;
+INSERT INTO t1 VALUES(1);
+SET debug_dbug='+d,ib_create_table_fail_too_many_trx';
+INSERT INTO t1 VALUES(2);
+ERROR HY000: Error while appending streaming replication fragment
+COMMIT;
+SELECT * FROM t1;
+f1
+SET debug_dbug='-d,ib_create_table_fail_too_many_trx';
+DROP TABLE t1;
+CALL mtr.add_suppression("Error writing into mysql.wsrep_streaming_log: 177");
diff --git a/mysql-test/suite/galera_sr/t/MDEV-30838.test b/mysql-test/suite/galera_sr/t/MDEV-30838.test
new file mode 100644
index 00000000000..39ca7d2a375
--- /dev/null
+++ b/mysql-test/suite/galera_sr/t/MDEV-30838.test
@@ -0,0 +1,18 @@
+#
+# MDEV-30838 - Assertion `m_thd == _current_thd()' failed in
+# virtual int Wsrep_client_service::bf_rollback()
+#
+--source include/galera_cluster.inc
+--source include/have_debug_sync.inc
+CREATE TABLE t1 (f1 INTEGER PRIMARY KEY);
+SET SESSION wsrep_trx_fragment_size=1;
+START TRANSACTION;
+INSERT INTO t1 VALUES(1);
+SET debug_dbug='+d,ib_create_table_fail_too_many_trx';
+--error ER_ERROR_DURING_COMMIT
+INSERT INTO t1 VALUES(2);
+COMMIT;
+SELECT * FROM t1;
+SET debug_dbug='-d,ib_create_table_fail_too_many_trx';
+DROP TABLE t1;
+CALL mtr.add_suppression("Error writing into mysql.wsrep_streaming_log: 177");
diff --git a/mysql-test/suite/perfschema/r/digest_view.result b/mysql-test/suite/perfschema/r/digest_view.result
index a5996cc0083..2981dfca6a3 100644
--- a/mysql-test/suite/perfschema/r/digest_view.result
+++ b/mysql-test/suite/perfschema/r/digest_view.result
@@ -191,17 +191,17 @@ SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR
 FROM performance_schema.events_statements_summary_by_digest
 ORDER BY DIGEST_TEXT;
 SCHEMA_NAME	DIGEST	DIGEST_TEXT	COUNT_STAR
-test	27a9ab161a63050c84a63c6f77ebeb33	EXPLAIN SELECT * FROM `test` . `v1` 	1
-test	ab0ab27c04f3a294feb86bede4331f86	EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 	1
-test	2e722346a5c2ef820946bcd04ccac208	EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 	1
-test	07baf2264db30b6b25302603436ebe82	EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 	1
-test	3a7dbc963635ab0de6e160e0a4212bce	EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 	1
-test	6b845c2e3a7421997e3b610d14b5c842	SELECT * FROM `test` . `v1` 	1
-test	cb2ee099edbf6e0e5ee6ae14f3b1498a	SELECT * FROM `test` . `v1` WHERE `a` = ? 	1
-test	e2dc5a300d2ba54ebb987a2ca6b90d93	SELECT * FROM `test` . `v1` WHERE `b` > ? 	1
-test	c68e0f99323f7bb7732c7b5cf32c0ec2	SELECT `a` , `b` FROM `test` . `v1` 	1
-test	43c5de955c9a72d2bb6f49db5c0ad3e7	SELECT `b` , `a` FROM `test` . `v1` 	1
-test	1678258ba15f7ccc63fd7b833763914a	TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 	1
+test	e0c034ab2f0273695ef8b19babc4bd32	EXPLAIN SELECT * FROM `test` . `v1` 	1
+test	751b8637d21a37ff572c84fdf1574e51	EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 	1
+test	93ceaf1395bcc52bfc3ec4d6ec817616	EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 	1
+test	51a9abceb7c8cebe749a4a07f7f40169	EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 	1
+test	8df4eaa7c85bdd4fffa6920137c3072a	EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 	1
+test	7ada999dc73fa2ec7f721e5c8e124e7f	SELECT * FROM `test` . `v1` 	1
+test	1efd67df2b28ac47a49938d56094c8c4	SELECT * FROM `test` . `v1` WHERE `a` = ? 	1
+test	9107a99dc94a65bd4d1c92756c2b60a6	SELECT * FROM `test` . `v1` WHERE `b` > ? 	1
+test	c25e5077415cf620b64c739153ce015b	SELECT `a` , `b` FROM `test` . `v1` 	1
+test	9b06d26cce3a78277e5d3b7abbfa7f3e	SELECT `b` , `a` FROM `test` . `v1` 	1
+test	e7f13e133e0bda19894df88606c37ec3	TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 	1
 DROP TABLE test.v1;
 CREATE VIEW test.v1 AS SELECT * FROM test.t1;
 EXPLAIN SELECT * from test.v1;
@@ -248,19 +248,19 @@ SELECT SCHEMA_NAME, DIGEST, DIGEST_TEXT, COUNT_STAR
 FROM performance_schema.events_statements_summary_by_digest
 ORDER BY DIGEST_TEXT;
 SCHEMA_NAME	DIGEST	DIGEST_TEXT	COUNT_STAR
-test	342cd5e41944d5f857f92b1e374857de	CREATE VIEW `test` . `v1` AS SELECT * FROM `test` . `t1` 	1
-test	2653f30030efcb6125121daa8eadf418	DROP TABLE `test` . `v1` 	1
-test	27a9ab161a63050c84a63c6f77ebeb33	EXPLAIN SELECT * FROM `test` . `v1` 	2
-test	ab0ab27c04f3a294feb86bede4331f86	EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 	2
-test	2e722346a5c2ef820946bcd04ccac208	EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 	2
-test	07baf2264db30b6b25302603436ebe82	EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 	2
-test	3a7dbc963635ab0de6e160e0a4212bce	EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 	2
-test	6b845c2e3a7421997e3b610d14b5c842	SELECT * FROM `test` . `v1` 	2
-test	cb2ee099edbf6e0e5ee6ae14f3b1498a	SELECT * FROM `test` . `v1` WHERE `a` = ? 	2
-test	e2dc5a300d2ba54ebb987a2ca6b90d93	SELECT * FROM `test` . `v1` WHERE `b` > ? 	2
-test	a13cfeda6d474d29546719d76dcfa831	SELECT SCHEMA_NAME , `DIGEST` , `DIGEST_TEXT` , `COUNT_STAR` FROM `performance_schema` . `events_statements_summary_by_digest` ORDER BY `DIGEST_TEXT` 	1
-test	c68e0f99323f7bb7732c7b5cf32c0ec2	SELECT `a` , `b` FROM `test` . `v1` 	2
-test	43c5de955c9a72d2bb6f49db5c0ad3e7	SELECT `b` , `a` FROM `test` . `v1` 	2
-test	1678258ba15f7ccc63fd7b833763914a	TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 	1
+test	f584090c72167285107217a72a0b3060	CREATE VIEW `test` . `v1` AS SELECT * FROM `test` . `t1` 	1
+test	67a106b8a88c2880c3455f1c7fa0a4aa	DROP TABLE `test` . `v1` 	1
+test	e0c034ab2f0273695ef8b19babc4bd32	EXPLAIN SELECT * FROM `test` . `v1` 	2
+test	751b8637d21a37ff572c84fdf1574e51	EXPLAIN SELECT * FROM `test` . `v1` WHERE `a` = ? 	2
+test	93ceaf1395bcc52bfc3ec4d6ec817616	EXPLAIN SELECT * FROM `test` . `v1` WHERE `b` > ? 	2
+test	51a9abceb7c8cebe749a4a07f7f40169	EXPLAIN SELECT `a` , `b` FROM `test` . `v1` 	2
+test	8df4eaa7c85bdd4fffa6920137c3072a	EXPLAIN SELECT `b` , `a` FROM `test` . `v1` 	2
+test	7ada999dc73fa2ec7f721e5c8e124e7f	SELECT * FROM `test` . `v1` 	2
+test	1efd67df2b28ac47a49938d56094c8c4	SELECT * FROM `test` . `v1` WHERE `a` = ? 	2
+test	9107a99dc94a65bd4d1c92756c2b60a6	SELECT * FROM `test` . `v1` WHERE `b` > ? 	2
+test	375cc5223952495534c4034549d813e2	SELECT SCHEMA_NAME , `DIGEST` , `DIGEST_TEXT` , `COUNT_STAR` FROM `performance_schema` . `events_statements_summary_by_digest` ORDER BY `DIGEST_TEXT` 	1
+test	c25e5077415cf620b64c739153ce015b	SELECT `a` , `b` FROM `test` . `v1` 	2
+test	9b06d26cce3a78277e5d3b7abbfa7f3e	SELECT `b` , `a` FROM `test` . `v1` 	2
+test	e7f13e133e0bda19894df88606c37ec3	TRUNCATE TABLE `performance_schema` . `events_statements_summary_by_digest` 	1
 DROP VIEW test.v1;
 DROP TABLE test.t1;
diff --git a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
index 13b51569b60..739a652c66a 100644
--- a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
+++ b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result
@@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1
 ####################################
 SELECT event_name, digest, digest_text, sql_text FROM events_statements_history_long;
 event_name	digest	digest_text	sql_text
-statement/sql/select	1fd0cdb6b5aa22bd0809a39b2dc2ac70	SELECT ? + ? + 	SELECT ...
-statement/sql/truncate	0a9c405cebde6df4be315dae86ff398a	TRUNCATE TABLE 	truncat...
+statement/sql/select	dfe1e2b52a48f7294a6385d229770021	SELECT ? + ? + 	SELECT ...
+statement/sql/truncate	f1b70d4cf1464d22965f02ab6d6b511b	TRUNCATE TABLE 	truncat...
diff --git a/mysql-test/suite/rpl/r/rpl_parallel_seq.result b/mysql-test/suite/rpl/r/rpl_parallel_seq.result
new file mode 100644
index 00000000000..ae4041f470d
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_parallel_seq.result
@@ -0,0 +1,85 @@
+include/master-slave.inc
+[connection master]
+connection slave;
+include/stop_slave.inc
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+# MDEV-29621 the sequence engine binlog_row_image-full events
+#            MDL-deadlock on the parallel slave.
+connection master;
+CREATE SEQUENCE s1;
+SET @@session.binlog_row_image=FULL;
+SET @@session.debug_dbug="+d,binlog_force_commit_id";
+SET @commit_id=7;
+SET @@gtid_seq_no=100;
+SELECT NEXT VALUE FOR s1;
+NEXT VALUE FOR s1
+1
+INSERT INTO s1 VALUES(2, 1, 10, 1, 2, 1, 1, 0);
+SET @@session.debug_dbug="";
+connection slave;
+SET @@global.slave_parallel_threads=2;
+SET @@global.slave_parallel_mode=optimistic;
+SET @@global.debug_dbug="+d,hold_worker_on_schedule";
+include/start_slave.inc
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+connection master;
+DROP SEQUENCE s1;
+connection slave;
+include/stop_slave.inc
+# Simulate buggy 10.3.36 master to prove the parallel applier
+# does not deadlock now at replaying the above master load.
+connection master;
+include/rpl_stop_server.inc [server_number=1]
+include/rpl_start_server.inc [server_number=1]
+connection slave;
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+CHANGE MASTER TO master_host='127.0.0.1', master_port=SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos;
+START SLAVE UNTIL MASTER_GTID_POS='0-1-102';
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+# Normal stop is expected
+include/wait_for_slave_to_stop.inc
+# MDEV-31077 ALTER SEQUENCE may end up in optimistic parallel slave binlog out-of-order
+# The test proves ALTER-SEQUENCE binlogs first before the following transaction does so.
+connection slave;
+include/stop_slave.inc
+Warnings:
+Note	1255	Slave already has been stopped
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+SET @@global.gtid_strict_mode=1;
+connection master;
+RESET MASTER;
+CREATE TABLE ti (a INT) ENGINE=innodb;
+CREATE SEQUENCE s2 ENGINE=innodb;
+SET @@gtid_seq_no=100;
+ALTER SEQUENCE s2 restart with 1;
+INSERT INTO ti SET a=1;
+include/save_master_gtid.inc
+SELECT @@global.gtid_binlog_state "Master gtid state";
+Master gtid state
+0-1-101
+connection slave;
+include/start_slave.inc
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "no 100,101 yet in both";
+@@global.gtid_binlog_state	no 100,101 yet in both
+0-1-2	0-1-2
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+# Normal sync with master proves the fixes correct
+include/sync_with_master_gtid.inc
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "all through 101 have been committed";
+@@global.gtid_binlog_state	all through 101 have been committed
+0-1-101	0-1-101
+connection slave;
+include/stop_slave.inc
+SET debug_sync = RESET;
+SET @@global.slave_parallel_threads= 0;
+SET @@global.slave_parallel_mode= optimistic;
+SET @@global.debug_dbug = "";
+SET @@global.gtid_strict_mode=0;
+include/start_slave.inc
+connection master;
+DROP SEQUENCE s2;
+DROP TABLE ti;
+connection slave;
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_binlog_index.test b/mysql-test/suite/rpl/t/rpl_binlog_index.test
index 95c49c3d574..6112affb3c4 100644
--- a/mysql-test/suite/rpl/t/rpl_binlog_index.test
+++ b/mysql-test/suite/rpl/t/rpl_binlog_index.test
@@ -17,10 +17,6 @@
 # BUG#12133 master.index file keeps mysqld from starting if bin log has been moved
 # BUG#42576 Relay logs in relay-log.info&localhost-relay-bin.index not processed after move
 
-source include/master-slave.inc;
-# There is no need to run this test case on all binlog format
-source include/have_binlog_format_row.inc;
-
 # Since this test relies heavily on filesystem operations (like
 # moving files around, backslashes and so forth) we avoid messing
 # around with windows access violations for not cluttering the 
@@ -28,6 +24,10 @@ source include/have_binlog_format_row.inc;
 # it is not 100% compliant.
 --source include/not_windows.inc
 
+source include/master-slave.inc;
+# There is no need to run this test case on all binlog format
+source include/have_binlog_format_row.inc;
+
 connection master;
 --let $master_datadir= `select @@datadir`
 connection slave;
diff --git a/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test b/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test
index 2cf184a3401..38537810906 100644
--- a/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test
+++ b/mysql-test/suite/rpl/t/rpl_gtid_stop_start.test
@@ -2,10 +2,10 @@ if (`SELECT $PS_PROTOCOL != 0`)
 {
    --skip Test temporarily disabled for ps-protocol
 }
+--source include/no_valgrind_without_big.inc
 --let $rpl_topology=1->2
 --source include/rpl_init.inc
 --source include/have_innodb.inc
---source include/no_valgrind_without_big.inc
 
 --echo *** Test normal shutdown/restart of slave server configured as a GTID slave. ***
 
diff --git a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test
index 9c804d8206a..a27a50d0fc4 100644
--- a/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test
+++ b/mysql-test/suite/rpl/t/rpl_lcase_tblnames_rewrite_db.test
@@ -15,8 +15,8 @@
 #
 # (iii) master and slave tables do not differ
 #
--- source include/master-slave.inc
 -- source include/not_windows.inc
+-- source include/master-slave.inc
 
 SET SQL_LOG_BIN=0;
 CREATE DATABASE B37656;
diff --git a/mysql-test/suite/rpl/t/rpl_mdev12179.test b/mysql-test/suite/rpl/t/rpl_mdev12179.test
index c8b9e665747..aa0d05211ff 100644
--- a/mysql-test/suite/rpl/t/rpl_mdev12179.test
+++ b/mysql-test/suite/rpl/t/rpl_mdev12179.test
@@ -1,7 +1,7 @@
+--source include/no_valgrind_without_big.inc
 --source include/have_innodb.inc
 --let $rpl_topology=1->2
 --source include/rpl_init.inc
---source include/no_valgrind_without_big.inc
 
 --connection server_2
 call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase");
diff --git a/mysql-test/suite/rpl/t/rpl_mdev382.test b/mysql-test/suite/rpl/t/rpl_mdev382.test
index 093b7b92413..84e3c84982d 100644
--- a/mysql-test/suite/rpl/t/rpl_mdev382.test
+++ b/mysql-test/suite/rpl/t/rpl_mdev382.test
@@ -1,7 +1,7 @@
+--source include/not_windows.inc #unix shell escaping used for mysqlbinlog
 --source include/have_innodb.inc
 --source include/have_binlog_format_statement.inc
 --source include/master-slave.inc
---source include/not_windows.inc #unix shell escaping used for mysqlbinlog
 
 # MDEV-382: multiple SQL injections in replication code.
 
diff --git a/mysql-test/suite/rpl/t/rpl_parallel_seq.test b/mysql-test/suite/rpl/t/rpl_parallel_seq.test
new file mode 100644
index 00000000000..2a4fd96ff34
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_parallel_seq.test
@@ -0,0 +1,131 @@
+--source include/have_innodb.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/have_binlog_format_row.inc
+--source include/master-slave.inc
+
+--connection slave
+--source include/stop_slave.inc
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+
+--echo # MDEV-29621 the sequence engine binlog_row_image-full events
+--echo #            MDL-deadlock on the parallel slave.
+--connection master
+CREATE SEQUENCE s1;
+SET @@session.binlog_row_image=FULL;
+SET @@session.debug_dbug="+d,binlog_force_commit_id";
+SET @commit_id=7;
+SET @@gtid_seq_no=100;
+SELECT NEXT VALUE FOR s1;
+INSERT INTO s1 VALUES(2, 1, 10, 1, 2, 1, 1, 0);
+SET @@session.debug_dbug="";
+
+--connection slave
+--let $slave_parallel_threads=`select @@global.slave_parallel_threads`
+--let $slave_parallel_mode=`select @@global.slave_parallel_mode`
+SET @@global.slave_parallel_threads=2;
+SET @@global.slave_parallel_mode=optimistic;
+SET @@global.debug_dbug="+d,hold_worker_on_schedule";
+--source include/start_slave.inc
+
+--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to start commit"
+--source include/wait_condition.inc
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+
+--connection master
+DROP SEQUENCE s1;
+--sync_slave_with_master
+--source include/stop_slave.inc
+
+--echo # Simulate buggy 10.3.36 master to prove the parallel applier
+--echo # does not deadlock now at replaying the above master load.
+--connection master
+--let $datadir= `SELECT @@datadir`
+
+--let $rpl_server_number= 1
+--source include/rpl_stop_server.inc
+
+--remove_file $datadir/master-bin.000001
+--copy_file $MYSQL_TEST_DIR/std_data/rpl/master-bin-seq_10.3.36.000001 $datadir/master-bin.000001
+
+--let $rpl_server_number= 1
+--source include/rpl_start_server.inc
+
+--source include/wait_until_connected_again.inc
+--save_master_pos
+
+--connection slave
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+
+--replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1
+eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_use_gtid=slave_pos;
+
+START SLAVE UNTIL MASTER_GTID_POS='0-1-102';
+
+--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to commit"
+--source include/wait_condition.inc
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+
+--echo # Normal stop is expected
+--source include/wait_for_slave_to_stop.inc
+
+--echo # MDEV-31077 ALTER SEQUENCE may end up in optimistic parallel slave binlog out-of-order
+--echo # The test proves ALTER-SEQUENCE binlogs first before the following transaction does so.
+
+--connection slave
+--source include/stop_slave.inc
+RESET MASTER;
+SET @@global.gtid_slave_pos="";
+--let $slave_gtid_strict_mode=`select @@global.gtid_strict_mode`
+SET @@global.gtid_strict_mode=1;
+--connection master
+RESET MASTER;
+
+# Load from master
+CREATE TABLE ti (a INT) ENGINE=innodb;
+CREATE SEQUENCE s2 ENGINE=innodb;
+
+SET @@gtid_seq_no=100;
+ALTER SEQUENCE s2 restart with 1;
+INSERT INTO ti SET a=1;
+--source include/save_master_gtid.inc
+SELECT @@global.gtid_binlog_state "Master gtid state";
+
+--connection slave
+--source include/start_slave.inc
+
+--let $wait_condition= SELECT count(*) = 1 FROM information_schema.processlist WHERE state LIKE "Waiting for prior transaction to commit"
+--source include/wait_condition.inc
+
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "no 100,101 yet in both";
+
+# DEBUG_DBUG extension point of hold_worker_on_schedule is reused
+# (gets deployed) in Sql_cmd_alter_sequence::execute.
+SET DEBUG_SYNC = 'now SIGNAL continue_worker';
+
+--echo # Normal sync with master proves the fixes correct
+--source include/sync_with_master_gtid.inc
+
+SELECT @@global.gtid_binlog_state, @@global.gtid_slave_pos as "all through 101 have been committed";
+
+#
+# MDEV-29621/MDEV-31077 clean up.
+#
+--connection slave
+--source include/stop_slave.inc
+
+SET debug_sync = RESET;
+--eval SET @@global.slave_parallel_threads= $slave_parallel_threads
+--eval SET @@global.slave_parallel_mode= $slave_parallel_mode
+       SET @@global.debug_dbug = "";
+--eval SET @@global.gtid_strict_mode=$slave_gtid_strict_mode
+--source include/start_slave.inc
+
+--connection master
+DROP SEQUENCE s2;
+DROP TABLE ti;
+
+--sync_slave_with_master
+
+--source include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test
index 44c04dd62d3..7decd130a43 100644
--- a/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test
+++ b/mysql-test/suite/rpl/t/rpl_row_lcase_tblnames.test
@@ -3,10 +3,10 @@
 #  For details look into extra/rpl_tests/rpl_lower_case_table_names.test
 #
 
--- source include/master-slave.inc
--- source include/have_innodb.inc
 -- source include/not_windows.inc
+-- source include/have_innodb.inc
 -- source include/have_binlog_format_row.inc
+-- source include/master-slave.inc
 
 -- let $engine=InnoDB
 -- source include/rpl_lower_case_table_names.test
diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_event.test b/mysql-test/suite/rpl/t/rpl_semi_sync_event.test
index 7a7e1c1e074..d4df9b4041b 100644
--- a/mysql-test/suite/rpl/t/rpl_semi_sync_event.test
+++ b/mysql-test/suite/rpl/t/rpl_semi_sync_event.test
@@ -1,7 +1,7 @@
+source include/no_valgrind_without_big.inc;
 source include/not_embedded.inc;
 source include/have_innodb.inc;
 source include/master-slave.inc;
-source include/no_valgrind_without_big.inc;
 
 let $engine_type= InnoDB;
 
diff --git a/mysql-test/suite/rpl/t/rpl_ssl.test b/mysql-test/suite/rpl/t/rpl_ssl.test
index 59a2af9f137..0420a6c8c2d 100644
--- a/mysql-test/suite/rpl/t/rpl_ssl.test
+++ b/mysql-test/suite/rpl/t/rpl_ssl.test
@@ -4,9 +4,9 @@
 # Please check all dependent tests after modifying it
 #
 
+source include/no_valgrind_without_big.inc;
 source include/have_ssl_communication.inc;
 source include/master-slave.inc;
-source include/no_valgrind_without_big.inc;
 
 # create a user for replication that requires ssl encryption
 connection master;
diff --git a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test
index 619b57994c2..3809cd89e4a 100644
--- a/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test
+++ b/mysql-test/suite/rpl/t/rpl_stm_lcase_tblnames.test
@@ -3,10 +3,10 @@
 #  For details look into extra/rpl_tests/rpl_lower_case_table_names.test
 #
 
+-- source include/not_windows.inc
+-- source include/have_innodb.inc
 -- source include/have_binlog_format_mixed_or_statement.inc
 -- source include/master-slave.inc
--- source include/have_innodb.inc
--- source include/not_windows.inc
 
 -- let $engine=InnoDB
 -- source include/rpl_lower_case_table_names.test
diff --git a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
index 80bd2d7af5f..7f2e1c6586c 100644
--- a/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
+++ b/mysql-test/suite/sys_vars/r/optimizer_switch_basic.result
@@ -1,60 +1,60 @@
 set @@global.optimizer_switch=@@optimizer_switch;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off,hash_join_cardinality=off
 set global optimizer_switch=4101;
 set session optimizer_switch=2058;
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 set global optimizer_switch="index_merge_sort_union=on";
 set session optimizer_switch="index_merge=off";
 select @@global.optimizer_switch;
 @@global.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 show global variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+optimizer_switch	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 show session variables like 'optimizer_switch';
 Variable_name	Value
-optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+optimizer_switch	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.global_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 select * from information_schema.session_variables where variable_name='optimizer_switch';
 VARIABLE_NAME	VARIABLE_VALUE
-OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+OPTIMIZER_SWITCH	index_merge=off,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 set session optimizer_switch="default";
 select @@session.optimizer_switch;
 @@session.optimizer_switch
-index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off
+index_merge=on,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=off,orderby_uses_equalities=off,condition_pushdown_for_derived=off,split_materialized=off,condition_pushdown_for_subquery=off,rowid_filter=off,condition_pushdown_from_having=off,not_null_range_scan=off,hash_join_cardinality=off
 set optimizer_switch = replace(@@optimizer_switch, '=off', '=on');
 Warnings:
 Warning	1681	'engine_condition_pushdown=on' is deprecated and will be removed in a future release
 select @@optimizer_switch;
 @@optimizer_switch
-index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=on
+index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=on,hash_join_cardinality=on
 set global optimizer_switch=1.1;
 ERROR 42000: Incorrect argument type to variable 'optimizer_switch'
 set global optimizer_switch=1e1;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 8791d250505..8b8b9d9834c 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -2299,7 +2299,7 @@ VARIABLE_COMMENT	Fine-tune the optimizer behavior
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
-ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,default
+ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,hash_join_cardinality,default
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_TRACE
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index cfe7bd05790..99857a3baf5 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -2469,7 +2469,7 @@ VARIABLE_COMMENT	Fine-tune the optimizer behavior
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
-ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,default
+ENUM_VALUE_LIST	index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,condition_pushdown_from_having,not_null_range_scan,hash_join_cardinality,default
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	OPTIMIZER_TRACE