diff options
| -rw-r--r-- | mysql-test/suite/binlog/r/binlog_stm_unsafe_warning.result | 13 | ||||
| -rw-r--r-- | mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test | 14 | ||||
| -rw-r--r-- | mysql-test/suite/sql_sequence/binlog.result | 24 | ||||
| -rw-r--r-- | mysql-test/suite/sql_sequence/binlog.test | 15 | ||||
| -rw-r--r-- | sql/sql_class.cc | 3 |
5 files changed, 64 insertions, 5 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/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/sql_sequence/binlog.result b/mysql-test/suite/sql_sequence/binlog.result index f01b3234e96..32788c988fc 100644 --- a/mysql-test/suite/sql_sequence/binlog.result +++ b/mysql-test/suite/sql_sequence/binlog.result @@ -12,6 +12,20 @@ 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 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 +44,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 diff --git a/sql/sql_class.cc b/sql/sql_class.cc index bbc46408c04..7f5a6345770 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -6210,7 +6210,8 @@ int THD::decide_logging_format(TABLE_LIST *tables) table->file->ht) multi_write_engine= TRUE; if (share->non_determinstic_insert && - !(sql_command_flags[lex->sql_command] & CF_SCHEMA_CHANGE)) + (sql_command_flags[lex->sql_command] & CF_CAN_GENERATE_ROW_EVENTS + && !(sql_command_flags[lex->sql_command] & CF_SCHEMA_CHANGE))) has_write_tables_with_unsafe_statements= true; trans= table->file->has_transactions(); |