Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 762 insertions, 0 deletions

diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test
new file mode 100644
index 00000000..b637a980
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test
@@ -0,0 +1,762 @@
+# ==== Background ====
+#
+# Some statements may execute differently on master and slave when
+# logged in statement format.  Such statements are called unsafe.
+# Unsafe statements include:
+#
+#  - statements using @@variables (with a small number of exceptions;
+#    see below);
+#  - statements using certain functions, e.g., UUID();
+#  - statements using LIMIT;
+#  - INSERT DELAYED;
+#  - insert into two autoinc columns;
+#  - statements using UDF's.
+#  - statements reading from log tables in the mysql database.
+#  - INSERT ... SELECT ... ON DUPLICATE KEY UPDATE
+#  - REPLACE ... SELECT
+#  - CREATE TABLE [IGNORE/REPLACE] SELECT
+#  - INSERT IGNORE...SELECT
+#  - UPDATE IGNORE
+#  - INSERT... ON DUPLICATE KEY UPDATE on a table with two UNIQUE KEYS
+#
+# Note that statements that use stored functions, stored procedures,
+# triggers, views, or prepared statements that invoke unsafe
+# statements shall also be unsafe.
+#
+# Unsafeness of a statement shall have the following consequences:
+#
+# 1. If the binlogging is on and the unsafe statement is logged:
+#    - If binlog_format=STATEMENT, the statement shall give a warning.
+#    - If binlog_format=MIXED or binlog_format=ROW, the statement shall
+#      be logged in row format.
+#
+# 2. If binlogging is off or the statement is not logged (e.g. SELECT
+#    UUID()), no warning shall be issued and the statement shall not
+#    be logged.
+#
+# Moreover, when a sub-statement of a recursive construct (i.e.,
+# stored function, stored procedure, trigger, view, or prepared
+# statement) is unsafe and binlog_format=STATEMENT, then a warning
+# shall be issued for every recursive construct.  In effect, this
+# creates a stack trace from the top-level statement to the unsafe
+# statement.
+#
+#
+# ==== Purpose ====
+#
+# This test verifies that a warning is generated when it should,
+# according to the rules above.
+#
+# All @@variables should be unsafe, with some exceptions.  Therefore,
+# this test also verifies that the exceptions do *not* generate a
+# warning.
+#
+#
+# ==== Method ====
+#
+# 1. Each type of statements listed above is executed.
+#
+# 2. Each unsafe statement is wrapped in each type of recursive
+#    construct (stored function, stored procedure, trigger, view, or
+#    prepared statement).
+#
+# 3. Each unsafe statement is wrapped in two levels of recursive
+#    constructs (function invoking trigger invoking UUID(), etc).
+#
+# We try to insert the variables that should not be unsafe into a
+# table, and verify that *no* warning is issued.
+#
+# Execute a unsafe statement calling a trigger or stored function
+# or neither when SQL_LOG_BIN is turned ON, a warning/error should be issued
+# Execute a unsafe statement calling a trigger or stored function
+# or neither when @@SQL_LOG_BIN is turned OFF,
+# no warning/error is issued
+#
+#
+# ==== Related bugs and worklogs ====
+#
+# WL#3339: Issue warnings when statement-based replication may fail
+# BUG#31168: @@hostname does not replicate
+# BUG#34732: mysqlbinlog does not print default values for auto_increment variables
+# BUG#34768: nondeterministic INSERT using LIMIT logged in stmt mode if binlog_format=mixed
+# BUG#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0
+# BUG#42640: mysqld crashes when unsafe statements are executed (STRICT_TRANS_TABLES mode)
+# BUG#45825: INSERT DELAYED is not unsafe: logged in statement format
+# BUG#45785: LIMIT in SP does not cause RBL if binlog_format=MIXED
+# BUG#47995: Mark user functions as unsafe
+# BUG#49222: Mark RAND() unsafe
+# BUG#11758262: MARK INSERT...SEL...ON DUP KEY UPD,REPLACE...SEL,CREATE...[IGN|REPL] SEL
+#
+# ==== Related test cases ====
+#
+# rpl.rpl_variables verifies that variables which cannot be replicated
+# safely in statement mode are replicated correctly in mixed or row
+# mode.
+#
+# rpl.rpl_variables_stm tests the small subset of variables that
+# actually can be replicated safely in statement mode.
+#
+--source include/have_udf.inc
+--source include/have_log_bin.inc
+--source include/have_binlog_format_statement.inc
+
+--disable_query_log
+call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
+--enable_query_log
+
+--echo #### Setup tables ####
+
+CREATE TABLE t0 (a CHAR(200));
+CREATE TABLE t1 (a CHAR(200));
+CREATE TABLE t2 (a CHAR(200));
+CREATE TABLE t3 (a CHAR(200));
+CREATE TABLE ta0 (a CHAR(200));
+CREATE TABLE ta1 (a CHAR(200));
+CREATE TABLE ta2 (a CHAR(200));
+CREATE TABLE ta3 (a CHAR(200));
+CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE data_table (a CHAR(200));
+INSERT INTO data_table VALUES ('foo');
+CREATE TABLE trigger_table_1 (a INT);
+CREATE TABLE trigger_table_2 (a INT);
+CREATE TABLE trigger_table_3 (a INT);
+CREATE TABLE double_autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
+
+--DELIMITER |
+CREATE TRIGGER double_autoinc_trig
+BEFORE INSERT ON double_autoinc_table FOR EACH ROW
+BEGIN
+  INSERT INTO autoinc_table VALUES (NULL);
+END|
+
+CREATE FUNCTION multi_unsafe_func() RETURNS INT
+BEGIN
+  INSERT INTO t0 VALUES(CONCAT(@@hostname, @@hostname));
+  INSERT INTO t0 VALUES(0);
+  INSERT INTO t0 VALUES(CONCAT(UUID(), @@hostname));
+  RETURN 1;
+END|
+--DELIMITER ;
+
+--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
+--eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO"
+
+# In each iteration of this loop, we select one method to make the
+# statement unsafe.
+--let $unsafe_type= 0
+while ($unsafe_type < 9) {
+
+  --echo
+
+  if ($unsafe_type == 0) {
+    --echo ==== Testing UUID() unsafeness ====
+    --let $desc_0= unsafe UUID() function
+    --let $stmt_sidef_0= INSERT INTO t0 VALUES (UUID())
+    --let $value_0= UUID()
+    --let $sel_sidef_0=
+    --let $sel_retval_0= SELECT UUID()
+    --let $CRC_ARG_expected_number_of_warnings= 1
+  }
+
+  if ($unsafe_type == 1) {
+    --echo ==== Testing @@hostname unsafeness ====
+    --let $desc_0= unsafe @@hostname variable
+    --let $stmt_sidef_0= INSERT INTO t0 VALUES (@@hostname)
+    --let $value_0= @@hostname
+    --let $sel_sidef_0=
+    # $sel_retval is going to be used in views.  Views cannot execute
+    # statements that refer to @@variables.  Hence, we set $set_retval
+    # to empty instead of SELECT @@hostname.
+    --let $sel_retval_0=
+    --let $CRC_ARG_expected_number_of_warnings= 1
+  }
+
+  if ($unsafe_type == 2) {
+    --echo ==== Testing SELECT...LIMIT unsafeness ====
+    --let $desc_0= unsafe SELECT...LIMIT statement
+    --let $stmt_sidef_0= INSERT INTO t0 SELECT * FROM data_table LIMIT 1
+    --let $value_0=
+    --let $sel_sidef_0=
+    --let $sel_retval_0= SELECT * FROM data_table LIMIT 1
+    --let $CRC_ARG_expected_number_of_warnings= 1
+  }
+
+  if ($unsafe_type == 3) {
+    --echo ==== Testing INSERT DELAYED safeness after BUG#54579 is fixed ====
+    --let $desc_0= unsafe INSERT DELAYED statement
+    --let $stmt_sidef_0= INSERT DELAYED INTO t0 VALUES (1), (2)
+    --let $value_0=
+    --let $sel_sidef_0=
+    --let $sel_retval_0=
+    --let $CRC_ARG_expected_number_of_warnings= 0
+  }
+
+  if ($unsafe_type == 4) {
+    --echo ==== Testing unsafeness of insert of two autoinc values ====
+    --let $desc_0= unsafe update of two autoinc columns
+    --let $stmt_sidef_0= INSERT INTO double_autoinc_table VALUES (NULL)
+    --let $value_0=
+    --let $sel_sidef_0=
+    --let $sel_retval_0=
+    --let $CRC_ARG_expected_number_of_warnings= 1
+  }
+
+  if ($unsafe_type == 5) {
+    --echo ==== Testing unsafeness of UDF's ====
+    --let $desc_0= unsafe UDF
+    --let $stmt_sidef_0= INSERT INTO t0 VALUES (myfunc_int(10))
+    --let $value_0= myfunc_int(10)
+    --let $sel_sidef_0= SELECT myfunc_int(10)
+    --let $sel_retval_0=
+    --let $CRC_ARG_expected_number_of_warnings= 1
+  }
+
+  if ($unsafe_type == 6) {
+    --echo ==== Testing unsafeness of access to mysql.general_log ====
+    --let $desc_0= unsafe use of mysql.general_log
+    --let $stmt_sidef_0= INSERT INTO t0 SELECT COUNT(*) FROM mysql.general_log
+    --let $value_0=
+    --let $sel_sidef_0=
+    --let $sel_retval_0= SELECT COUNT(*) FROM mysql.general_log
+    --let $CRC_ARG_expected_number_of_warnings= 1
+  }
+
+  if ($unsafe_type == 7) {
+    --echo ==== Testing a statement that is unsafe in many ways ====
+    --let $desc_0= statement that is unsafe in many ways
+    # Concatenate three unsafe values, and then concatenate NULL to
+    # that so that the result is NULL and we instead use autoinc.
+    --let $stmt_sidef_0= INSERT DELAYED INTO double_autoinc_table SELECT CONCAT(UUID(), @@hostname, myfunc_int(), NULL) FROM mysql.general_log LIMIT 1
+    --let $value_0=
+    --let $sel_sidef_0=
+    --let $sel_retval_0=
+    --let $CRC_ARG_expected_number_of_warnings= 7
+  }
+
+  if ($unsafe_type == 8) {
+    --echo ==== Testing a statement that is unsafe several times ====
+    --let $desc_0= statement that is unsafe several times
+    --let $stmt_sidef_0= INSERT INTO ta0 VALUES (multi_unsafe_func())
+    --let $value_0=
+    --let $sel_sidef_0= SELECT multi_unsafe_func()
+    --let $sel_retval_0=
+    --let $CRC_ARG_expected_number_of_warnings= 2
+  }
+
+  # In each iteration of the following loop, we select one way to
+  # enclose the unsafe statement as a sub-statement of a recursive
+  # construct (i.e., a function, procedure, trigger, view, or prepared
+  # statement).
+  #
+  # In the last iteration, $call_type_1=7, we don't create a recursive
+  # construct. Instead, we just invoke the unsafe statement directly.
+
+  --let $call_type_1= 0
+  while ($call_type_1 < 8) {
+    #--echo debug: level 1, types $call_type_1 -> $unsafe_type
+    --let $CRC_ARG_level= 1
+    --let $CRC_ARG_type= $call_type_1
+    --let $CRC_ARG_stmt_sidef= $stmt_sidef_0
+    --let $CRC_ARG_value= $value_0
+    --let $CRC_ARG_sel_sidef= $sel_sidef_0
+    --let $CRC_ARG_sel_retval= $sel_retval_0
+    --let $CRC_ARG_desc= $desc_0
+    --source suite/rpl/include/create_recursive_construct.inc
+    --let $stmt_sidef_1= $CRC_RET_stmt_sidef
+    --let $value_1= $CRC_RET_value
+    --let $sel_sidef_1= $CRC_RET_sel_sidef
+    --let $sel_retval_1= $CRC_RET_sel_retval
+    --let $is_toplevel_1= $CRC_RET_is_toplevel
+    --let $drop_1= $CRC_RET_drop
+    --let $desc_1= $CRC_RET_desc
+
+    # Some statements must be top-level statements, i.e., cannot be
+    # called as a sub-statement of any recursive construct.  (One
+    # example is 'EXECUTE prepared_stmt').  When
+    # create_recursive_construct.inc creates a top-level statement, it
+    # sets $CRC_RET_is_toplevel=1.
+
+    if (!$is_toplevel_1) {
+
+      # In each iteration of this loop, we select one way to enclose
+      # the previous recursive construct in another recursive
+      # construct.
+
+      --let $call_type_2= 0
+      while ($call_type_2 < 7) {
+        #--echo debug: level 2, types $call_type_2 -> $call_type_1 -> $unsafe_type
+        --let $CRC_ARG_level= 2
+        --let $CRC_ARG_type= $call_type_2
+        --let $CRC_ARG_stmt_sidef= $stmt_sidef_1
+        --let $CRC_ARG_value= $value_1
+        --let $CRC_ARG_sel_sidef= $sel_sidef_1
+        --let $CRC_ARG_sel_retval= $sel_retval_1
+        --let $CRC_ARG_desc= $desc_1
+        --source suite/rpl/include/create_recursive_construct.inc
+        --let $stmt_sidef_2= $CRC_RET_stmt_sidef
+        --let $value_2= $CRC_RET_value
+        --let $sel_sidef_2= $CRC_RET_sel_sidef
+        --let $sel_retval_2= $CRC_RET_sel_retval
+        --let $is_toplevel_2= $CRC_RET_is_toplevel
+        --let $drop_2= $CRC_RET_drop
+        --let $desc_2= $CRC_RET_desc
+
+        if (!$is_toplevel_2) {
+
+         # Conditioned out since it makes result file really big.
+
+         if (0) {
+
+          # In each iteration of this loop, we select one way to enclose
+          # the previous recursive construct in another recursive
+          # construct.
+
+          --let $call_type_3= 0
+          while ($call_type_3 < 7) {
+            #--echo debug: level 3, types $call_type_2 -> $call_type_2 -> $call_type_1 -> $unsafe_type
+            --let $CRC_ARG_level= 3
+            --let $CRC_ARG_type= $call_type_3
+            --let $CRC_ARG_stmt_sidef= $stmt_sidef_2
+            --let $CRC_ARG_value= $value_2
+            --let $CRC_ARG_sel_sidef= $sel_sidef_2
+            --let $CRC_ARG_sel_retval= $sel_retval_2
+            --let $CRC_ARG_desc= $desc_2
+            --source suite/rpl/include/create_recursive_construct.inc
+
+            # Drop created object.
+            if ($drop_3) {
+              --eval $drop_3
+            }
+            --inc $call_type_3
+          } # while (call_type_3)
+         } # if (0)
+        } # if (!is_toplevel_2)
+
+        # Drop created object.
+        if ($drop_2) {
+          --eval $drop_2
+        }
+        --inc $call_type_2
+      } # while (call_type_2)
+    } # if (!is_toplevel_1)
+
+    # Drop created object.
+    if ($drop_1) {
+      --eval $drop_1
+    }
+    --inc $call_type_1
+  } # while (call_type_1)
+
+  --inc $unsafe_type
+} # while (unsafe_type)
+
+DROP TRIGGER double_autoinc_trig;
+DROP TABLE t0, t1, t2, t3, ta0, ta1, ta2, ta3,
+  autoinc_table, double_autoinc_table,
+  data_table,
+  trigger_table_1, trigger_table_2, trigger_table_3;
+DROP FUNCTION myfunc_int;
+DROP FUNCTION multi_unsafe_func;
+
+
+--echo ==== Special system variables that should *not* be unsafe ====
+
+CREATE TABLE t1 (a VARCHAR(1000));
+CREATE TABLE autoinc_table (a INT PRIMARY KEY AUTO_INCREMENT);
+
+INSERT INTO t1 VALUES (@@session.auto_increment_increment);
+INSERT INTO t1 VALUES (@@session.auto_increment_offset);
+INSERT INTO t1 VALUES (@@session.character_set_client);
+INSERT INTO t1 VALUES (@@session.character_set_connection);
+INSERT INTO t1 VALUES (@@session.character_set_database);
+INSERT INTO t1 VALUES (@@session.character_set_server);
+INSERT INTO t1 VALUES (@@session.collation_connection);
+INSERT INTO t1 VALUES (@@session.collation_database);
+INSERT INTO t1 VALUES (@@session.collation_server);
+INSERT INTO t1 VALUES (@@session.foreign_key_checks);
+INSERT INTO t1 VALUES (@@session.identity);
+INSERT INTO t1 VALUES (@@session.last_insert_id);
+INSERT INTO t1 VALUES (@@session.lc_time_names);
+INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
+INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
+INSERT INTO t1 VALUES (@@session.timestamp);
+INSERT INTO t1 VALUES (@@session.time_zone);
+INSERT INTO t1 VALUES (@@session.unique_checks);
+
+SET @my_var= 4711;
+INSERT INTO t1 VALUES (@my_var);
+
+# using insert_id implicitly should be ok.
+SET insert_id= 12;
+INSERT INTO autoinc_table VALUES (NULL);
+
+# See set_var.cc for explanation.
+--echo The following variables *should* give a warning, despite they are replicated.
+INSERT INTO t1 VALUES (@@session.sql_mode);
+INSERT INTO t1 VALUES (@@session.insert_id);
+
+
+DROP TABLE t1, autoinc_table;
+
+
+#
+# BUG#34768 - nondeterministic INSERT using LIMIT logged in stmt mode if
+#             binlog_format=mixed
+#
+CREATE TABLE t1(a INT, b INT, KEY(a), PRIMARY KEY(b));
+INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
+REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
+UPDATE t1 SET a=1 LIMIT 1;
+DELETE FROM t1 LIMIT 1;
+delimiter |;
+CREATE PROCEDURE p1()
+BEGIN
+  INSERT INTO t1 SELECT * FROM t1 LIMIT 1;
+  REPLACE INTO t1 SELECT * FROM t1 LIMIT 1;
+  UPDATE t1 SET a=1 LIMIT 1;
+  DELETE FROM t1 LIMIT 1;
+END|
+delimiter ;|
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+#
+# Bug#42634: % character in query can cause mysqld signal 11 segfault
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a VARCHAR(200), b VARCHAR(200));
+INSERT INTO t1 VALUES ('a','b');
+UPDATE t1 SET b = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s' WHERE a = 'a' LIMIT 1;
+DROP TABLE t1;
+
+#
+#For bug#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0 
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+CREATE TABLE t1(i INT PRIMARY KEY);
+CREATE TABLE t2(i INT PRIMARY KEY);
+CREATE TABLE t3(i INT, ch CHAR(50));
+
+--echo "Should issue message Statement may not be safe to log in statement format."
+INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
+
+DELIMITER |;
+CREATE FUNCTION func6()
+RETURNS INT
+BEGIN
+  INSERT INTO t1 VALUES (10);
+  INSERT INTO t1 VALUES (11);
+  INSERT INTO t1 VALUES (12);
+  RETURN 0;
+END|
+DELIMITER ;|
+--echo "Should issue message Statement may not be safe to log in statement format only once"
+INSERT INTO t3 VALUES(func6(), UUID());
+
+--echo "Check whether SET @@SQL_LOG_BIN = 0/1 doesn't work in substatements"
+DELIMITER |;
+CREATE FUNCTION fun_check_log_bin() RETURNS INT
+BEGIN
+  SET @@SQL_LOG_BIN = 0;
+  INSERT INTO t1 VALUES(@@global.sync_binlog);
+  RETURN 200;
+END|
+DELIMITER ;|
+--echo "One unsafe warning should be issued in the following statement"
+--error ER_STORED_FUNCTION_PREVENTS_SWITCH_SQL_LOG_BIN
+SELECT fun_check_log_bin();
+--echo "SQL_LOG_BIN should be ON still"
+SHOW VARIABLES LIKE "SQL_LOG_BIN";
+
+set @save_log_bin = @@SESSION.SQL_LOG_BIN;
+set @@SESSION.SQL_LOG_BIN = 0;
+--echo "Should NOT have any warning message issued in the following statements"
+INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
+DROP TABLE t1,t2;
+
+--echo "Should NOT have any warning message issued in the following func7() and trig"
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a TEXT);
+CREATE TABLE trigger_table (a CHAR(7));
+DELIMITER |;
+CREATE FUNCTION func7()
+RETURNS INT
+BEGIN
+  INSERT INTO t1 VALUES (@@global.sync_binlog);
+  INSERT INTO t1 VALUES (@@session.insert_id);
+  INSERT INTO t2 SELECT UUID();
+  INSERT INTO t2 VALUES (@@session.sql_mode);
+  INSERT INTO t2 VALUES (@@global.init_slave);
+  RETURN 0;
+END|
+DELIMITER ;|
+SHOW VARIABLES LIKE "SQL_LOG_BIN";
+SELECT func7();
+
+--echo ---- Insert from trigger ----
+
+DELIMITER |;
+CREATE TRIGGER trig
+BEFORE INSERT ON trigger_table
+FOR EACH ROW
+BEGIN
+  INSERT INTO t1 VALUES (@@global.sync_binlog);
+  INSERT INTO t1 VALUES (@@session.insert_id);
+  INSERT INTO t1 VALUES (@@global.auto_increment_increment);
+  INSERT INTO t2 SELECT UUID();
+  INSERT INTO t2 VALUES (@@session.sql_mode);
+  INSERT INTO t2 VALUES (@@global.init_slave);
+  INSERT INTO t2 VALUES (@@hostname);
+END|
+DELIMITER ;|
+
+INSERT INTO trigger_table VALUES ('bye.');
+
+#clean up
+DROP FUNCTION fun_check_log_bin;
+DROP FUNCTION func6;
+DROP FUNCTION func7;
+DROP TRIGGER  trig;
+DROP TABLE t1, t2, t3, trigger_table;
+set @@SESSION.SQL_LOG_BIN = @save_log_bin;
+
+#
+# For BUG#42640: mysqld crashes when unsafe statements are executed (STRICT_TRANS_TABLES mode)
+#
+SET @save_sql_mode = @@SESSION.SQL_MODE;
+SET @@SESSION.SQL_MODE = STRICT_ALL_TABLES;
+
+CREATE TABLE t1(i INT PRIMARY KEY);
+CREATE TABLE t2(i INT PRIMARY KEY);
+
+INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
+INSERT INTO t1 VALUES(@@global.sync_binlog);
+
+UPDATE t1 SET i = 999 LIMIT 1;
+DELETE FROM t1 LIMIT 1;
+
+DROP TABLE t1, t2;
+SET @@SESSION.SQL_MODE = @save_sql_mode;
+
+#
+# BUG#45825: INSERT DELAYED is not unsafe: logged in statement format
+# BUG#45785: LIMIT in SP does not cause RBL if binlog_format=MIXED
+#
+SET @old_binlog_format = @@session.binlog_format;
+SET binlog_format = MIXED;
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (1), (2);
+
+--DELIMITER |
+CREATE PROCEDURE proc_insert_delayed ()
+BEGIN
+  INSERT DELAYED INTO t1 VALUES (1), (2);
+END|
+
+CREATE FUNCTION func_limit ()
+RETURNS INT
+BEGIN
+  INSERT INTO t1 SELECT * FROM t2 LIMIT 1;
+  RETURN 1;
+END|
+--DELIMITER ;
+
+RESET MASTER;
+CALL proc_insert_delayed();
+--disable_ps2_protocol
+SELECT func_limit();
+--enable_ps2_protocol
+source include/show_binlog_events.inc;
+
+SET @@session.binlog_format = @old_binlog_format;
+DROP TABLE t1, t2;
+DROP PROCEDURE proc_insert_delayed;
+DROP FUNCTION func_limit;
+
+#
+# BUG#45827
+# The test verifies if stmt that have more than one 
+# different tables to update with autoinc columns 
+# will produce unsafe warning
+#
+
+# Test case1: stmt that have more than one different tables 
+#             to update with autoinc columns should produce 
+#             unsafe warning when calling a function
+CREATE TABLE t1 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+
+# The purpose of this function is to insert into t1 so that the second
+# column is auto_increment'ed.
+DELIMITER |;
+CREATE FUNCTION func_modify_t1 ()
+RETURNS INT
+BEGIN
+  INSERT INTO t1 SET a = 1;
+  RETURN 0;
+END|
+DELIMITER ;|
+--echo # The following statement causes auto-incrementation  
+--echo # of both t1 and t2. It is logged in statement format, 
+--echo # so it should produce unsafe warning.
+INSERT INTO t2 SET a = func_modify_t1();
+
+SET SESSION binlog_format = MIXED;
+--echo # Check if the statement is logged in row format.
+let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+INSERT INTO t2 SET a = func_modify_t1();
+--source include/show_binlog_events.inc
+
+# clean up
+DROP TABLE t1,t2;
+DROP FUNCTION func_modify_t1;
+#
+# Test case2: stmt that have more than one different tables 
+#             to update with autoinc columns should produce 
+#             unsafe warning when invoking a trigger 
+SET SESSION binlog_format = STATEMENT;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t3 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+
+# The purpose of this function is to insert into t1 so that the second
+# column is auto_increment'ed.
+delimiter |;
+create trigger tri_modify_two_tables before insert on t1 for each row begin
+    insert into t2(a) values(new.a);
+    insert into t3(a) values(new.a);
+end |
+delimiter ;|
+--echo # The following statement causes auto-incrementation  
+--echo # of both t2 and t3. It is logged in statement format, 
+--echo # so it should produce unsafe warning
+INSERT INTO t1 SET a = 1;
+
+SET SESSION binlog_format = MIXED;
+--echo # Check if the statement is logged in row format.
+let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+INSERT INTO t1 SET a = 2;
+--source include/show_binlog_events.inc
+
+# clean up
+DROP TABLE t1,t2,t3;
+
+#
+# BUG#47995: Mark user functions as unsafe
+# BUG#49222: Mark RAND() unsafe
+#
+# Test that the system functions that are supposed to be marked unsafe
+# generate a warning.  Each INSERT statement below should generate a
+# warning.
+#
+SET SESSION binlog_format = STATEMENT;
+
+CREATE TABLE t1 (a VARCHAR(1000));
+INSERT INTO t1 VALUES (CURRENT_USER());       #marked unsafe before BUG#47995
+INSERT INTO t1 VALUES (FOUND_ROWS());         #marked unsafe before BUG#47995
+INSERT INTO t1 VALUES (GET_LOCK('tmp', 1));   #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (IS_FREE_LOCK('tmp'));  #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (IS_USED_LOCK('tmp'));  #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (LOAD_FILE('../../std_data/words2.dat')); #marked unsafe in BUG#39701
+INSERT INTO t1 VALUES (MASTER_POS_WAIT('dummy arg', 4711, 1));
+INSERT INTO t1 VALUES (RELEASE_LOCK('tmp'));  #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (ROW_COUNT());          #marked unsafe before BUG#47995
+INSERT INTO t1 VALUES (SESSION_USER());       #marked unsafe before BUG#47995
+INSERT INTO t1 VALUES (SLEEP(1));             #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (SYSDATE());            #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (SYSTEM_USER());        #marked unsafe before BUG#47995
+INSERT INTO t1 VALUES (USER());               #marked unsafe before BUG#47995
+INSERT INTO t1 VALUES (UUID());               #marked unsafe before BUG#47995
+INSERT INTO t1 VALUES (UUID_SHORT());         #marked unsafe before BUG#47995
+INSERT INTO t1 VALUES (VERSION());            #marked unsafe in BUG#47995
+INSERT INTO t1 VALUES (RAND());               #marked unsafe in BUG#49222
+INSERT INTO t1 VALUES (RANDOM_BYTES(1000));   #marked unsafe in MDEV-25704
+DELETE FROM t1;
+
+# Since we replicate the TIMESTAMP variable, functions affected by the
+# TIMESTAMP variable are safe to replicate.  So we check that the
+# following following functions that depend on the TIMESTAMP variable
+# are not unsafe and don't generate a warning.
+
+SET TIME_ZONE= '+03:00';
+SET TIMESTAMP=1000000;
+INSERT INTO t1 VALUES
+  (CURDATE()),
+  (CURRENT_DATE()),
+  (CURRENT_TIME()),
+  (CURRENT_TIMESTAMP()),
+  (CURTIME()),
+  (LOCALTIME()),
+  (LOCALTIMESTAMP()),
+  (NOW()),
+  (UNIX_TIMESTAMP()),
+  (UTC_DATE()),
+  (UTC_TIME()),
+  (UTC_TIMESTAMP());
+SELECT * FROM t1;
+
+DROP TABLE t1;
+#
+#BUG#11758262-50439: MARK INSERT...SEL...ON DUP KEY UPD,REPLACE..
+#The following statement may be unsafe when logged in statement format.
+#INSERT IGNORE...SELECT 
+#INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 
+#REPLACE ... SELECT 
+#UPDATE IGNORE 
+#CREATE TABLE... IGNORE SELECT 
+#CREATE TABLE... REPLACE SELECT
+#
+###BUG 11765650 - 58637: MARK UPDATES THAT DEPEND ON ORDER OF TWO KEYS UNSAFE 
+#INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY
+
+#setup tables
+CREATE TABLE filler_table (a INT, b INT);
+INSERT INTO filler_table values (1,1),(1,2);
+CREATE TABLE insert_table (a INT, b INT, PRIMARY KEY(a));
+CREATE TABLE replace_table (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO replace_table values (1,1),(2,2);
+CREATE TABLE update_table (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO update_table values (1,1),(2,2);
+CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY);
+INSERT INTO insert_2_keys values (1, 1);
+
+#INSERT IGNORE... SELECT
+INSERT IGNORE INTO insert_table SELECT * FROM filler_table;
+TRUNCATE TABLE insert_table;
+#INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 
+INSERT INTO insert_table SELECT * FROM filler_table ON DUPLICATE KEY UPDATE a = 1;
+TRUNCATE TABLE insert_table;
+#REPLACE...SELECT
+REPLACE INTO replace_table SELECT * FROM filler_table;
+#UPDATE IGNORE
+UPDATE IGNORE update_table SET a=2;
+#CREATE TABLE [IGNORE/REPLACE] SELECT
+CREATE TABLE create_ignore_test (a INT, b INT, PRIMARY KEY(b)) IGNORE SELECT * FROM filler_table;
+CREATE TABLE create_replace_test (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table;
+#temporary tables should not throw the warning.
+CREATE TEMPORARY TABLE temp1 (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table;
+
+#INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY
+INSERT INTO insert_2_keys VALUES (1, 2) 
+       ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10;
+
+###clean up
+DROP TABLE filler_table;
+DROP TABLE insert_table;
+DROP TABLE update_table;
+DROP TABLE replace_table;
+DROP TABLE create_ignore_test;
+DROP TABLE create_replace_test;
+DROP TABLE insert_2_keys;
+
+--echo "End of tests"