Merged fix for BUG#39934 up a few revisions.

NOTE: This undoes changes by BUG#42829 in sql_class.cc:binlog_query().
I will revert the change in a post-push fix (the binlog filter should
be checked in sql_base.cc:decide_logging_format()).

4 files changed, 380 insertions, 9 deletions

diff --git a/mysql-test/extra/rpl_tests/create_recursive_construct.inc b/mysql-test/extra/rpl_tests/create_recursive_construct.inc
new file mode 100644
index 00000000000..66c82de8b99
--- /dev/null
+++ b/mysql-test/extra/rpl_tests/create_recursive_construct.inc
@@ -0,0 +1,364 @@
+# ==== Purpose ====
+#
+# Creates a stored routine, stored function, trigger, view, or
+# prepared statement (commonly referred to as "recursive construct")
+# that invokes a given unsafe statement.
+#
+# Then, it invokes the created recursive construct several times:
+#
+#  - With SQL_LOG_BIN = 1 and binlog_format = STATEMENT, to verify
+#    that it gives a warning.
+#
+#  - With SQL_LOG_BIN = 0 and binlog_format = STATEMENT, to verify that
+#    there is no warning and nothing is logged.
+#
+#  - With SQL_LOG_BIN = 1 and binlog_format = MIXED, to verify that it
+#    writes row events to the binlog.
+#
+#  - If the recursive construct can be invoked so that it has no
+#    side-effects but it returns a value that may be nondeterministic,
+#    then it is invoked in such a way that the return value is
+#    discarded, with SQL_LOG_BIN = 1 and binlog_format = STATEMENT.
+#    In this case, no warning should be given and nothing should be
+#    written to the binlog.
+#
+# This is an auxiliary file particularly targeted to being used by the
+# test binlog_unsafe.  In this context, the purpose is to check how
+# warnings for unsafe statements are propagated in recursive
+# constructs.
+#
+# The statement to invoke ("input") is described using mtr variables,
+# and the resulting recursive construct ("output") is stored in mtr
+# variables in a similar fashion.  To create several levels of nested
+# recursive constructs, source this file once, then copy the values of
+# appropriate output variables to the input variables, and then source
+# this file again.
+#
+#
+# ==== Usage ====
+#
+# See binlog_unsafe for an example of how to use this file.
+#
+# let $CRC_ARG_level= <level>;
+# let $CRC_ARG_type= <type>;
+# let $CRC_ARG_stmt_sidef= <stmt>;
+# let $CRC_ARG_value= <stmt>;
+# let $CRC_ARG_sel_retval= <stmt>;
+# let $CRC_ARG_sel_sidef= <stmt>;
+# let $CRC_ARG_desc= <desc>;
+# source extra/rpl_tests/create_recursive_construct.inc;
+# let $my_stmt_sidef= $CRC_RET_stmt_sidef;
+# let $my_value= $CRC_RET_value;
+# let $my_sel_sidef= $CRC_RET_sel_sidef;
+# let $my_sel_retval= $CRC_RET_sel_retval;
+# let $my_drop= $CRC_RET_drop;
+# let $my_is_toplevel= $CRC_RET_top_is_toplevel;
+# let $my_desc= $CRC_RET_desc;
+#
+# $CRC_ARG_* are used as input parameters (arguments) to this file:
+#
+#   $CRC_ARG_level is the recursion depth: 1 for the innermost
+#   statement created, 2 for a statement that invokes a statement on
+#   level 1, etc.
+#
+#   $CRC_ARG_type is an integer from 0 to 6, indicating what type of
+#   statement shall be created:
+#    0 - Create a stored function where the return value depends on
+#        the value of the given statement.
+#    1 - Create a stored function that invokes the given statement as
+#        a side-effect but may not return a value that depends on it.
+#    2 - Create a stored routine that invokes the given statement.
+#    3 - Create a trigger (on table trigger_table_$CRC_ARG_level) that
+#        invokes the given statement.
+#    4 - Create a view that returns a value that depends on the value
+#        of the given statement.
+#    5 - Create a view that invokes the given statement but may return
+#        a value that does not depend on it.
+#    6 - Create a prepared statement that invokes the given statement.
+#
+#   $CRC_ARG_stmt_sidef is the statement to invoke.  It should be a
+#   statement that can be invoked on its own (not sub-statement),
+#   which causes something unsafe to be written to the binlog.
+#
+#   $CRC_ARG_value is a sub-statement holding the value of the given
+#   statement.  Can be empty if the given statement does not have a
+#   value.  Typically, this is non-empty if the given statement is a
+#   function call or user variable, but not if it is a stored routine
+#   call, INSERT, SELECT, etc (because none of them has a value).
+#   $CRC_ARG_value is used only when $CRC_ARG_type=6.
+#
+#   $CRC_ARG_sel_sidef is a SELECT sub-statement that invokes the
+#   statement as a side-effect, but returns a result set that may not
+#   depend on the statement.  Can be empty if the statement cannot
+#   produce a result set from a SELECT.  $CRC_ARG_sel_sidef is used
+#   only if $CRC_ARG_type=2
+#
+#   $CRC_ARG_sel_retval is a SELECT sub-statement that does not have
+#   side-effects, but returns a result set that depends on the unsafe
+#   statement.  Can be empty if the statement cannot be invoked from a
+#   SELECT.  $CRC_ARG_sel_retval is used only if $CRC_ARG_type=3.
+#
+#   $CRC_ARG_desc is a human-readable description of the statement to
+#   invoke.
+#
+# $CRC_RET_* are used as output parameters (return values) of this
+# file:
+#
+#   $CRC_RET_stmt_sidef is a statement invoking the resulting recursive
+#   construct.
+#
+#   $CRC_RET_value is a sub-statement invoking the resulting recursive
+#   construct and returning the value of the recursive construct.
+#   This is the empty string if the resulting recursive construct does
+#   not have a value.  In particular, this is non-empty only if
+#   $CRC_ARG_value=7.
+#
+#   $CRC_RET_sel_sidef is a SELECT sub-statement that invokes the
+#   resulting recursive construct as a side-effect but where the
+#   result set may not depend on the recursive construct.  This is the
+#   empty string if the recursive construct cannot be invoked from a
+#   SELECT.  In particular, this is non-empty only if $CRC_ARG_value=6
+#   or $CRC_ARG_value=2.
+#
+#   $CRC_RET_sel_retval is a SELECT sub-statement that does not have
+#   side-effects, but returns a result set depending on the unsafe
+#   statement.  This is the empty string if the recursive construct
+#   cannot produce a result set from a SELECT.  In particular, this is
+#   non-empty only if $CRC_ARG_value=7 or $CRC_ARG_value=3.
+#
+#   $CRC_RET_drop is a statement that drops the created object.  I.e.,
+#   it is one of 'DROP FUNCTION <func>', 'DROP PROCEDURE <proc>', etc.
+#
+#   $CRC_RET_top_is_toplevel is 0 normally, or 1 if the resulting
+#   recursive construct can only be called from a top-level statement.
+#   In particular, this is 1 only when $CRC_ARG_value=1, because
+#   prepared statements cannot be invoked from other recursive
+#   constructs.
+#
+#   $CRC_RET_desc is a text string that describes the invokation of
+#   the recursive construct in a human-readable fashion.
+#
+# Assumptions
+#
+#   Before sourcing this file with $CRC_ARG_level=X, you need to
+#   create three tables: tX, taX and trigger_table_X.  These are used
+#   as auxiliary tables.
+
+
+#--echo debug: >>>>ENTER create_recursive_construct
+#--echo debug: level=$CRC_ARG_level
+#--echo debug: type=$CRC_ARG_type
+#--echo debug: stmt_sidef=$CRC_ARG_stmt_sidef
+#--echo debug: value=$CRC_ARG_value
+#--echo debug: sel_retval=$CRC_ARG_sel_retval
+#--echo debug: sel_sidef=$CRC_ARG_sel_sidef
+
+--let $CRC_RET_stmt_sidef=
+--let $CRC_RET_value=
+--let $CRC_RET_sel_retval=
+--let $CRC_RET_sel_sidef=
+--let $CRC_RET_drop=
+--let $CRC_RET_is_toplevel= 1
+--let $CRC_RET_desc=
+--let $CRC_name=
+--let $CRC_create=
+
+######## func_retval ########
+if (`SELECT $CRC_ARG_type = 0 AND '$CRC_ARG_value' != ''`) {
+  # It will be safe to call this function and discard the return
+  # value, but it will be unsafe to use return value (e.g., in
+  # INSERT...SELECT).
+  --let $CRC_name= func_retval_$CRC_ARG_level
+  --let $CRC_create= CREATE FUNCTION $CRC_name() RETURNS VARCHAR(100) BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); RETURN $CRC_ARG_value; END
+  --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_level VALUES ($CRC_name())
+  --let $CRC_RET_value= $CRC_name()
+  --let $CRC_RET_sel_sidef=
+  --let $CRC_RET_sel_retval= SELECT $CRC_name()
+  --let $CRC_RET_drop= DROP FUNCTION $CRC_name
+  --let $CRC_RET_is_toplevel= 0
+  --let $CRC_RET_desc= function $CRC_name returning value from $CRC_ARG_desc
+}
+
+######## func_sidef ########
+if (`SELECT $CRC_ARG_type = 1`) {
+  # It will be unsafe to call func even if you discard return value.
+  --let $CRC_name= func_sidef_$CRC_ARG_level
+  --let $CRC_create= CREATE FUNCTION $CRC_name() RETURNS VARCHAR(100) BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); $CRC_ARG_stmt_sidef; RETURN 0; END
+  --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_level SELECT $CRC_name()
+  --let $CRC_RET_value=
+  --let $CRC_RET_sel_retval=
+  --let $CRC_RET_sel_sidef= SELECT $CRC_name()
+  --let $CRC_RET_drop= DROP FUNCTION $CRC_name
+  --let $CRC_RET_is_toplevel= 0
+  --let $CRC_RET_desc= function $CRC_name invoking $CRC_ARG_desc
+}
+
+######## proc ########
+if (`SELECT $CRC_ARG_type = 2`) {
+  # It will be unsafe to call this procedure.
+  --let $CRC_name= proc_$CRC_ARG_level
+  --let $CRC_create= CREATE PROCEDURE $CRC_name() BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); $CRC_ARG_stmt_sidef; END
+  --let $CRC_RET_stmt_sidef= CALL $CRC_name()
+  --let $CRC_RET_value=
+  --let $CRC_RET_sel_retval=
+  --let $CRC_RET_sel_sidef=
+  --let $CRC_RET_drop= DROP PROCEDURE $CRC_name
+  --let $CRC_RET_is_toplevel= 0
+  --let $CRC_RET_desc= procedure $CRC_name invoking $CRC_ARG_desc
+}
+
+######## trig ########
+if (`SELECT $CRC_ARG_type = 3`) {
+  # It will be unsafe to invoke this trigger.
+  --let $CRC_name= trig_$CRC_ARG_level
+  --let $CRC_create= CREATE TRIGGER $CRC_name BEFORE INSERT ON trigger_table_$CRC_ARG_level FOR EACH ROW BEGIN INSERT INTO ta$CRC_ARG_level VALUES (47); $CRC_ARG_stmt_sidef; END
+  --let $CRC_RET_stmt_sidef= INSERT INTO trigger_table_$CRC_ARG_level VALUES (1)
+  --let $CRC_RET_value=
+  --let $CRC_RET_sel_retval=
+  --let $CRC_RET_sel_sidef=
+  --let $CRC_RET_drop= DROP TRIGGER $CRC_name
+  --let $CRC_RET_is_toplevel= 0
+  --let $CRC_RET_desc= trigger $CRC_name invoking $CRC_ARG_desc
+}
+
+######## view_retval ########
+if (`SELECT $CRC_ARG_type = 4 AND '$CRC_ARG_sel_retval' != ''`) {
+  # It will be safe to select from this view if you discard the result
+  # set, but unsafe to use result set (e.g., in INSERT..SELECT).
+  --let $CRC_name= view_retval_$CRC_ARG_level
+  --let $CRC_create= CREATE VIEW $CRC_name AS $CRC_ARG_sel_retval
+  --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_LEVEL SELECT * FROM $CRC_name
+  --let $CRC_RET_value=
+  --let $CRC_RET_sel_retval= SELECT * FROM $CRC_name
+  --let $CRC_RET_sel_sidef=
+  --let $CRC_RET_drop= DROP VIEW $CRC_name
+  --let $CRC_RET_is_toplevel= 0
+  --let $CRC_RET_desc= view $CRC_name returning value from $CRC_ARG_desc
+}
+
+######## view_sidef ########
+if (`SELECT $CRC_ARG_type = 5 AND '$CRC_ARG_sel_sidef' != ''`) {
+  # It will be unsafe to select from this view, even if you discard
+  # the return value.
+  --let $CRC_name= view_sidef_$CRC_ARG_level
+  --let $CRC_create= CREATE VIEW $CRC_name AS $CRC_ARG_sel_sidef
+  --let $CRC_RET_stmt_sidef= INSERT INTO t$CRC_ARG_level SELECT * FROM $CRC_name
+  --let $CRC_RET_value=
+  --let $CRC_RET_sel_retval=
+  --let $CRC_RET_sel_sidef= SELECT * FROM $CRC_name
+  --let $CRC_RET_drop= DROP VIEW $CRC_name
+  --let $CRC_RET_is_toplevel= 0
+  --let $CRC_RET_desc= view $CRC_name invoking $CRC_ARG_desc
+}
+
+######## prep ########
+if (`SELECT $CRC_ARG_type = 6`) {
+  # It will be unsafe to execute this prepared statement
+  --let $CRC_name= prep_$CRC_ARG_level
+  --let $CRC_create= PREPARE $CRC_name FROM "$CRC_ARG_stmt_sidef"
+  --let $CRC_RET_stmt_sidef= EXECUTE $CRC_name
+  --let $CRC_RET_value=
+  --let $CRC_RET_sel_retval=
+  --let $CRC_RET_sel_sidef=
+  --let $CRC_RET_drop= DROP PREPARE $CRC_name
+  --let $CRC_RET_is_toplevel= 1
+  --let $CRC_RET_desc= prepared statement $CRC_name invoking $CRC_ARG_desc
+}
+
+######## no recursive construct: just return the given statement ########
+if (`SELECT $CRC_ARG_type = 7`) {
+  # CRC_ARG_type=7 is a special case. We just set $CRC_RET_x =
+  # $CRC_ARG_x.  This way, the $CRC_ARG_stmt gets executed directly
+  # (below).  In binlog_unsafe.test, it is used to invoke the unsafe
+  # statement created in the outermost loop directly, without
+  # enclosing it in a recursive construct.
+  --let $CRC_RET_stmt_sidef= $CRC_ARG_stmt_sidef
+  --let $CRC_RET_value= $CRC_ARG_value
+  --let $CRC_RET_sel_retval= $CRC_ARG_sel_retval
+  --let $CRC_RET_sel_sidef= $CRC_ARG_sel_sidef
+  --let $CRC_RET_drop=
+  --let $CRC_RET_is_toplevel= 1
+  --let $CRC_RET_desc= $CRC_ARG_desc
+}
+
+######## execute! ########
+if (`SELECT '$CRC_RET_stmt_sidef' != ''`) {
+  --echo
+  --echo Invoking $CRC_RET_desc.
+  if (`SELECT '$CRC_create' != ''`) {
+    --eval $CRC_create
+  }
+
+  --echo * binlog_format = STATEMENT: expect warning.
+  --eval $CRC_RET_stmt_sidef
+
+  # These queries are run without query log, to make result file more
+  # readable.  Debug info is only printed if something abnormal
+  # happens.
+  --disable_query_log
+
+  --echo * SQL_LOG_BIN = 0: expect nothing logged and no warning.
+  SET SQL_LOG_BIN = 0;
+  RESET MASTER;
+  --eval $CRC_RET_stmt_sidef
+  --let $binlog_event= query_get_value(SHOW BINLOG EVENTS, Event_type, 2)
+  if (`SELECT '$binlog_event' != 'No such row'`) {
+    --enable_query_log
+    --echo Failure! Something was written to the binlog despite SQL_LOG_BIN=0:
+    SHOW BINLOG EVENTS;
+    --die
+  }
+  SET SQL_LOG_BIN = 1;
+
+  --echo * binlog_format = MIXED: expect row events in binlog and no warning.
+  SET binlog_format = MIXED;
+  RESET MASTER;
+  --eval $CRC_RET_stmt_sidef    
+  # The first event is format_description, the second is
+  # Query_event('BEGIN'), and the third should be our Table_map.
+  --let $event_type= query_get_value(SHOW BINLOG EVENTS, Event_type, 3)
+  if (`SELECT '$event_type' != 'Table_map'`) {
+    --enable_query_log
+    --echo Failure! Event number 3 was a '$event_type', not a 'Table_map'.
+
+    # Currently, there is a bug causing some statements to be logged
+    # partially in statement format. Hence, we don't fail here, we
+    # just print the events (masking out nondeterministic components
+    # of the output) and continue. When binloggging works perfectly,
+    # we should instead execute:
+    #--enable_query_log
+    #SHOW BINLOG EVENTS;
+    #--die
+
+    # Here, we should really source
+    # include/show_binlog_events.inc. But due to BUG#41913, that
+    # doesn't work, and we have to inline the entire file here. Sigh
+    # :-(
+    --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 106 <binlog_start>
+    --replace_column 2 # 4 # 5 #
+    --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /file_id=[0-9]+/file_id=#/ /block_len=[0-9]+/block_len=#/
+    --eval SHOW BINLOG EVENTS FROM 106
+    --disable_query_log
+  }
+  SET binlog_format = STATEMENT;
+
+  --enable_query_log
+}
+
+# Invoke created object, discarding the return value. This should not
+# give any warning.
+if (`SELECT '$CRC_RET_sel_retval' != ''`) {
+  --echo * Invoke statement so that return value is dicarded: expect no warning.
+  --disable_result_log
+  --eval $CRC_RET_sel_retval
+  --enable_result_log
+}
+
+#--echo debug: <<<<EXIT create_recursive_construct
+#--echo debug: stmt_sidef=$CRC_RET_stmt_sidef
+#--echo debug: value=$CRC_RET_value
+#--echo debug: sel_retval=$CRC_RET_sel_retval
+#--echo debug: sel_sidef=$CRC_RET_sel_sidef
+#--echo debug: drop=$CRC_RET_drop
+#--echo debug: is_toplevel=$CRC_RET_is_toplevel
+#--echo debug: desc=$CRC_RET_desc
diff --git a/mysql-test/extra/rpl_tests/rpl_foreign_key.test b/mysql-test/extra/rpl_tests/rpl_foreign_key.test
index 8755bf5aa87..ea16237435c 100644
--- a/mysql-test/extra/rpl_tests/rpl_foreign_key.test
+++ b/mysql-test/extra/rpl_tests/rpl_foreign_key.test
@@ -47,7 +47,6 @@ insert into t1 set b=1;
 insert into t2 set a=1, b=1;
 
 set foreign_key_checks=0;
-set @@session.binlog_format=row;
 delete from t1;
 
 --echo must sync w/o a problem (could not with the buggy code)
diff --git a/mysql-test/extra/rpl_tests/rpl_insert_delayed.test b/mysql-test/extra/rpl_tests/rpl_insert_delayed.test
index e492903afad..a2c8c94bb05 100644
--- a/mysql-test/extra/rpl_tests/rpl_insert_delayed.test
+++ b/mysql-test/extra/rpl_tests/rpl_insert_delayed.test
@@ -38,14 +38,14 @@ connection master;
 truncate table t1;
 # first scenario: duplicate on first row
 insert delayed into t1 values(10, "my name");
-if ($binlog_format_statement)
+if  (`SELECT @@session.binlog_format = 'STATEMENT'`)
 {
   # statement below will be converted to non-delayed INSERT and so
   # will stop at first error, guaranteeing replication.
   --error ER_DUP_ENTRY
   insert delayed into t1 values(10, "is Bond"), (20, "James Bond");
 }
-if (!$binlog_format_statement)
+if  (`SELECT @@session.binlog_format != 'STATEMENT'`)
 {
   insert delayed into t1 values(10, "is Bond"), (20, "James Bond");
 }
@@ -59,7 +59,7 @@ select * from t1;
 # second scenario: duplicate on second row
 connection master;
 delete from t1 where id!=10;
-if ($binlog_format_statement)
+if  (`SELECT @@session.binlog_format = 'STATEMENT'`)
 {
   # statement below will be converted to non-delayed INSERT and so
   # will be binlogged with its ER_DUP_ENTRY error code, guaranteeing
@@ -67,7 +67,7 @@ if ($binlog_format_statement)
   --error ER_DUP_ENTRY
   insert delayed into t1 values(20, "is Bond"), (10, "James Bond");
 }
-if (!$binlog_format_statement)
+if  (`SELECT @@session.binlog_format != 'STATEMENT'`)
 {
   insert delayed into t1 values(20, "is Bond"), (10, "James Bond");
 }
@@ -90,7 +90,7 @@ connection master;
 # Bug #29571: INSERT DELAYED IGNORE written to binary log on the master but
 # on the slave
 #
-if  (`SELECT @@global.binlog_format != 'ROW'`)
+if  (`SELECT @@session.binlog_format != 'ROW'`)
 {
   #flush the logs before the test
   connection slave;
@@ -104,20 +104,22 @@ INSERT DELAYED IGNORE INTO t1 VALUES(1);
 INSERT DELAYED IGNORE INTO t1 VALUES(1);
 flush table t1; # to wait for INSERT DELAYED to be done
 
-if  (`SELECT @@global.binlog_format != 'ROW'`)
+if  (`SELECT @@session.binlog_format != 'ROW'`)
 {
   #must show two INSERT DELAYED
   --replace_column 1 x 2 x 3 x 4 x 5 x
+  --replace_regex /table_id: [0-9]+/table_id: #/
   show binlog events in 'master-bin.000002' LIMIT 2,2;
 }
 select * from t1;
 
 sync_slave_with_master;
 echo On slave;
-if  (`SELECT @@global.binlog_format != 'ROW'`)
+if  (`SELECT @@session.binlog_format != 'ROW'`)
 {
   #must show two INSERT DELAYED
   --replace_column 1 x 2 x 3 x 4 x 5 x
+  --replace_regex /table_id: [0-9]+/table_id: #/
   show binlog events in 'slave-bin.000002' LIMIT 2,2;
 }
 select * from t1;
@@ -127,7 +129,7 @@ select * from t1;
 connection master;
 drop table t1;
 sync_slave_with_master;
-if  (`SELECT @@global.binlog_format != 'ROW'`)
+if  (`SELECT @@session.binlog_format != 'ROW'`)
 {
   #flush the logs after the test
   FLUSH LOGS;
diff --git a/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test b/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test
index 4677f6da25d..7ab99ef65dd 100644
--- a/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test
+++ b/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test
@@ -141,7 +141,9 @@ let $run= 5;
 while ($run)
 {
  START TRANSACTION;
+ --disable_warnings
  --eval CALL tpcb.trans($rpl_format);
+ --enable_warnings
  eval SET @my_errno= $mysql_errno;
  let $run_good= `SELECT @my_errno = 0`;
  let $run_bad= `SELECT @my_errno <> 0`;
@@ -190,7 +192,9 @@ let $run= 5;
 while ($run)
 {
  START TRANSACTION;
+ --disable_warnings
  --eval CALL tpcb.trans($rpl_format);
+ --enable_warnings
  eval SET @my_errno= $mysql_errno;
  let $run_good= `SELECT @my_errno = 0`;
  let $run_bad= `SELECT @my_errno <> 0`;
@@ -240,7 +244,9 @@ let $run= 5;
 while ($run)
 {
  START TRANSACTION;
+ --disable_warnings
  --eval CALL tpcb.trans($rpl_format);
+ --enable_warnings
  eval SET @my_errno= $mysql_errno;
  let $run_good= `SELECT @my_errno = 0`;
  let $run_bad= `SELECT @my_errno <> 0`;