Merge 10.3 into 10.4

7 files changed, 118 insertions, 4 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result b/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
index 135271d325e..971fae6208f 100644
--- a/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
+++ b/mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result
@@ -104,6 +104,17 @@ Note	1592	Unsafe statement written to the binary log using statement format sinc
 SHOW WARNINGS;
 Level	Code	Message
 Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly
+CREATE TABLE t3 (a INT(11) DEFAULT NULL);
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (a INT(11) DEFAULT NULL, b BIGINT(20) DEFAULT uuid_short()) SELECT * FROM t3;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
+SHOW WARNINGS;
+Level	Code	Message
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave
+CREATE OR REPLACE TABLE t4 (a INT(11) DEFAULT NULL) SELECT * FROM t3;
+SHOW WARNINGS;
+Level	Code	Message
 DROP FUNCTION sf_bug50192;
 DROP TRIGGER tr_bug50192;
-DROP TABLE t1, t2;
+DROP TABLE t1, t2, t3, t4;
diff --git a/mysql-test/suite/binlog/r/innodb_autoinc_lock_mode_binlog.result b/mysql-test/suite/binlog/r/innodb_autoinc_lock_mode_binlog.result
new file mode 100644
index 00000000000..d0132931968
--- /dev/null
+++ b/mysql-test/suite/binlog/r/innodb_autoinc_lock_mode_binlog.result
@@ -0,0 +1,33 @@
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
+select @@innodb_autoinc_lock_mode;
+@@innodb_autoinc_lock_mode
+2
+select @@binlog_format;
+@@binlog_format
+MIXED
+create table t1 (a int not null auto_increment,b int, primary key (a)) engine=InnoDB;
+insert into t1 values (NULL,1);
+include/show_binlog_events.inc
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Query	#	#	use `mtr`; INSERT INTO test_suppressions (pattern) VALUES ( NAME_CONST('pattern',_latin1'Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT' COLLATE 'latin1_swedish_ci'))
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; create table t1 (a int not null auto_increment,b int, primary key (a)) engine=InnoDB
+master-bin.000001	#	Gtid	#	#	BEGIN GTID #-#-#
+master-bin.000001	#	Annotate_rows	#	#	insert into t1 values (NULL,1)
+master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
+master-bin.000001	#	Write_rows_v1	#	#	table_id: # flags: STMT_END_F
+master-bin.000001	#	Xid	#	#	COMMIT /* XID */
+set global binlog_format=STATEMENT;
+connect  con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
+insert into t1 values (NULL,1);
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
+insert into t1 values (NULL,1);
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system variable that may have a different value on the slave
+disconnect con1;
+connection default;
+set global binlog_format=MIXED;
+DROP TABLE t1;
diff --git a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
index 70566da4cfa..578f0ff5fc8 100644
--- a/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
+++ b/mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
@@ -189,8 +189,20 @@ SHOW WARNINGS;
 SELECT sf_bug50192();
 SHOW WARNINGS;
 
+# The test proves MDEV-24617 fixes leave in force
+# unsafe warnings in non-deterministic CREATE..SELECT cases.
+# Below an inserted default value to `b` of the target table is replication
+# unsafe. A warning must be out.
+CREATE TABLE t3 (a INT(11) DEFAULT NULL);
+INSERT INTO t3 VALUES (1);
+CREATE TABLE t4 (a INT(11) DEFAULT NULL, b BIGINT(20) DEFAULT uuid_short()) SELECT * FROM t3;
+SHOW WARNINGS;
+# no warning out of a deterministic "rhs" of SELECT
+CREATE OR REPLACE TABLE t4 (a INT(11) DEFAULT NULL) SELECT * FROM t3;
+SHOW WARNINGS;
+
 # cleanup
 
 DROP FUNCTION sf_bug50192;
 DROP TRIGGER tr_bug50192;
-DROP TABLE t1, t2;
+DROP TABLE t1, t2, t3, t4;
diff --git a/mysql-test/suite/binlog/t/innodb_autoinc_lock_mode_binlog.opt b/mysql-test/suite/binlog/t/innodb_autoinc_lock_mode_binlog.opt
new file mode 100644
index 00000000000..824f656cbd5
--- /dev/null
+++ b/mysql-test/suite/binlog/t/innodb_autoinc_lock_mode_binlog.opt
@@ -0,0 +1 @@
+--innodb_autoinc_lock_mode=2
diff --git a/mysql-test/suite/binlog/t/innodb_autoinc_lock_mode_binlog.test b/mysql-test/suite/binlog/t/innodb_autoinc_lock_mode_binlog.test
new file mode 100644
index 00000000000..a7d43db4c1b
--- /dev/null
+++ b/mysql-test/suite/binlog/t/innodb_autoinc_lock_mode_binlog.test
@@ -0,0 +1,21 @@
+--source include/have_innodb.inc
+--source include/have_binlog_format_mixed.inc
+
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
+
+select @@innodb_autoinc_lock_mode;
+select @@binlog_format;
+
+create table t1 (a int not null auto_increment,b int, primary key (a)) engine=InnoDB;
+insert into t1 values (NULL,1);
+--source include/show_binlog_events.inc
+
+set global binlog_format=STATEMENT;
+--connect (con1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK)
+insert into t1 values (NULL,1);
+insert into t1 values (NULL,1);
+--disconnect con1
+--connection default
+
+set global binlog_format=MIXED;
+DROP TABLE t1;
diff --git a/mysql-test/suite/sql_sequence/binlog.result b/mysql-test/suite/sql_sequence/binlog.result
index f01b3234e96..843cf74c2fa 100644
--- a/mysql-test/suite/sql_sequence/binlog.result
+++ b/mysql-test/suite/sql_sequence/binlog.result
@@ -12,6 +12,21 @@ select next value for s1, minimum_value from s1 where maximum_value> 4;
 next value for s1	minimum_value
 4	1
 alter sequence s1 maxvalue 1000;
+optimize table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	optimize	note	The storage engine for the table doesn't support optimize
+analyze table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	analyze	status	Engine-independent statistics collected
+test.s1	analyze	note	The storage engine for the table doesn't support analyze
+repair table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	repair	status	OK
+check table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	check	note	The storage engine for the table doesn't support check
+rename table s1 to tmp_s;
+rename table tmp_s to s1;
 drop sequence s1;
 include/show_binlog_events.inc
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
@@ -30,4 +45,14 @@ master-bin.000001	#	Query	#	#	COMMIT
 master-bin.000001	#	Gtid	#	#	GTID #-#-#
 master-bin.000001	#	Query	#	#	use `test`; alter sequence s1 maxvalue 1000
 master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; optimize table s1
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; analyze table s1
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; repair table s1
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; rename table s1 to tmp_s
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
+master-bin.000001	#	Query	#	#	use `test`; rename table tmp_s to s1
+master-bin.000001	#	Gtid	#	#	GTID #-#-#
 master-bin.000001	#	Query	#	#	use `test`; DROP SEQUENCE `s1` /* generated by server */
diff --git a/mysql-test/suite/sql_sequence/binlog.test b/mysql-test/suite/sql_sequence/binlog.test
index 5f2d52d7864..6a12e670a1e 100644
--- a/mysql-test/suite/sql_sequence/binlog.test
+++ b/mysql-test/suite/sql_sequence/binlog.test
@@ -1,5 +1,5 @@
---source include/have_udf.inc
---source include/have_log_bin.inc
+--source include/have_sequence.inc
+--source include/have_binlog_format_mixed_or_row.inc
 --source include/binlog_start_pos.inc
 
 #
@@ -21,6 +21,17 @@ select next value for s1, minimum_value from s1 where maximum_value> 4;
 #
 alter sequence s1 maxvalue 1000;
 
+# MDEV-24617 OPTIMIZE on a sequence causes unexpected
+# ER_BINLOG_UNSAFE_STATEMENT The test below verifies no unsafe
+# warnings anymore for any relavant commands that like OPTIMIZE can
+# not produce ROW format events therefore the unsafe warning either.
+optimize table s1;
+analyze table s1;
+repair table s1;
+check table s1;
+rename table s1 to tmp_s;
+rename table tmp_s to s1;
+
 drop sequence s1;
 
 --let $binlog_file = LAST