List:Commits« Previous MessageNext Message »
From:Sven Sandberg Date:February 27 2008 3:44pm
Subject:bk commit into 5.1 tree (sven:1.2535) BUG#31168
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of sven.  When sven does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-02-27 15:44:25+01:00, sven@riska.(none) +25 -0
  BUG#31168: @@hostname does not replicate
  Problem: in mixed and statement mode, a query that refers to a
  system variable will use the slave's value when replayed on
  slave. So if the value of a system variable is inserted into a
  table, the slave will differ from the master.
  Fix: mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode and produce a warning
  in statement mode.

  include/m_ctype.h@stripped, 2008-02-27 15:44:19+01:00, sven@riska.(none) +1 -0
    Added definition of ILLEGAL_CHARSET_INFO_NUMBER. We just need a symbol
    for a number that will never be used by any charset. ~0 should be safe
    since charset numbers are sequential, starting from 0.

  mysql-test/include/diff_tables.inc@stripped, 2008-02-27 15:44:21+01:00, sven@riska.(none)
+121 -0
    New auxiliary test file that test whether two tables (possibly one on
    master and one on slave) differ.

  mysql-test/include/diff_tables.inc@stripped, 2008-02-27 15:44:21+01:00, sven@riska.(none) +0
-0

  mysql-test/r/mysqlbinlog.result@stripped, 2008-02-27 15:44:19+01:00, sven@riska.(none) +40
-0
    Updated result file.

  mysql-test/r/mysqlbinlog2.result@stripped, 2008-02-27 15:44:19+01:00, sven@riska.(none) +140
-26
    Updated result file.

  mysql-test/r/user_var-binlog.result@stripped, 2008-02-27 15:44:19+01:00, sven@riska.(none)
+4 -0
    Updated result file.

  mysql-test/suite/binlog/r/binlog_base64_flag.result@stripped, 2008-02-27 15:44:19+01:00,
sven@riska.(none) +6 -3
    Updated result file.

  mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result@stripped, 2008-02-27 15:44:19+01:00,
sven@riska.(none) +4 -0
    Updated result file.

  mysql-test/suite/binlog/r/binlog_unsafe.result@stripped, 2008-02-27 15:44:19+01:00,
sven@riska.(none) +179 -11
    Modified test file needs modified result file.

  mysql-test/suite/binlog/t/binlog_base64_flag.test@stripped, 2008-02-27 15:44:19+01:00,
sven@riska.(none) +1 -1
    Need to filter out pseudo_thread_id from result since it is
    nondeterministic.

  mysql-test/suite/binlog/t/binlog_unsafe.test@stripped, 2008-02-27 15:44:19+01:00,
sven@riska.(none) +219 -13
    Add tests that using variables is unsafe. The 'CREATE VIEW' tests didn't
    make sense, so I removed them. SHOW WARNINGS is not necessary either,
    because we get warnings for each statement in the result file.

  mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result@stripped, 2008-02-27 15:44:19+01:00,
sven@riska.(none) +16 -0
    Updated result file.

  mysql-test/suite/rpl/r/rpl_skip_error.result@stripped, 2008-02-27 15:44:19+01:00,
sven@riska.(none) +15 -12
    Updated result file.

  mysql-test/suite/rpl/r/rpl_variables.result@stripped, 2008-02-26 18:46:00+01:00,
sven@riska.(none) +582 -0
    New test case needs new result file.

  mysql-test/suite/rpl/r/rpl_variables.result@stripped, 2008-02-26 18:46:00+01:00,
sven@riska.(none) +0 -0

  mysql-test/suite/rpl/r/rpl_variables_stm.result@stripped, 2008-02-26 18:46:08+01:00,
sven@riska.(none) +558 -0
    BitKeeper file
/home/sven/bk/b31168-hostname_variable_replication/5.1-new-rpl/mysql-test/suite/rpl/r/rpl_variables_stm.result

  mysql-test/suite/rpl/r/rpl_variables_stm.result@stripped, 2008-02-26 18:46:08+01:00,
sven@riska.(none) +0 -0

  mysql-test/suite/rpl/t/rpl_skip_error.test@stripped, 2008-02-27 15:44:19+01:00,
sven@riska.(none) +42 -33
    The test used @@server_id, which is not safe to replicate, so it would
    have given a warning. The way it used @@server_id was hackish (issue a
    query on master that removes rows only on master), so I replaced it by a
    more robust way to do the same thing (connect to slave and insert the
    rows only there).
    Also clarified what the test case does.

  mysql-test/suite/rpl/t/rpl_variables.test@stripped, 2008-02-26 18:45:53+01:00,
sven@riska.(none) +726 -0
    Test that INSERT of @@variables is replicated correctly.

  mysql-test/suite/rpl/t/rpl_variables.test@stripped, 2008-02-26 18:45:53+01:00,
sven@riska.(none) +0 -0

  mysql-test/suite/rpl/t/rpl_variables_stm.test@stripped, 2008-02-26 18:46:15+01:00,
sven@riska.(none) +672 -0
    BitKeeper file
/home/sven/bk/b31168-hostname_variable_replication/5.1-new-rpl/mysql-test/suite/rpl/t/rpl_variables_stm.test

  mysql-test/suite/rpl/t/rpl_variables_stm.test@stripped, 2008-02-26 18:46:15+01:00,
sven@riska.(none) +0 -0

  mysql-test/t/mysqlbinlog2.test@stripped, 2008-02-27 15:44:19+01:00, sven@riska.(none) +1 -2
    Use --short-form instead of manually filtering out nondeterministic stuff
    from mysqlbinlog (because we added the nondeterministic @@pseudo_thread_id
    to the output).

  sql/item_func.cc@stripped, 2008-02-27 15:44:20+01:00, sven@riska.(none) +6 -0
    Added method of Item_func_get_system_var that indicates whether the given
    system variable will be written to the binlog or not.

  sql/item_func.h@stripped, 2008-02-27 15:44:20+01:00, sven@riska.(none) +9 -0
    Added method of Item_func_get_system_var that indicates whether the given
    system variable will be written to the binlog or not.

  sql/log_event.cc@stripped, 2008-02-27 15:44:20+01:00, sven@riska.(none) +79 -45
     - auto_increment_offset was not written to the binlog if
    auto_increment_increment=1
     - mysqlbinlog did not output default values for some variables
    (BUG#34732). In st_print_event_info, we remember for each variable whether
    it has been printed or not. This is achieved in different ways for
    different variables:
        - For auto_increment_*, lc_time_names, charset_database_number,
          we set the default values in st_print_event_info to something
          illegal, so that it will look like they have changed the first time
          they are seen.
        - For charset, sql_mode, pseudo_thread_id, we add a flag to
          st_print_event_info which indicates whether the variable has been
          printed.
        - Since pseudo_thread_id is now printed more often, and its value is
          not guaranteed to be constant across different runs of the same
          test script, I replaced it by a constant if --short-form is used.
     - Moved st_print_event_info constructor from log_event.h to log_event.cc,
    since it now depends on ILLEGAL_CHARSET_NUMBER, which is defined in
    m_ctype.h, which is better to include from a .cc file than from a header
    file.

  sql/log_event.h@stripped, 2008-02-27 15:44:20+01:00, sven@riska.(none) +7 -22
    Added fields to st_print_event_info that indicate whether some of the
    variables have been written or not. Since the initialization of
    charset_database_number now depends on ILLEGAL_CHARSET_INFO_NUMBER, which
    is defined in a header file, which we'd better not include from this
    header file -- I moved the constructor from here to log_event.cc.

  sql/set_var.cc@stripped, 2008-02-27 15:44:20+01:00, sven@riska.(none) +94 -42
    System variables now have a flag binlog_status, which indicates if they
    are written to the binlog. If nothing is specified, all variables are
    marked as not written to the binlog (NOT_IN_BINLOG) when created. In this
    file, the variables that are written to the binlog are marked with
    SESSION_VARIABLE_IN_BINLOG.

  sql/set_var.h@stripped, 2008-02-27 15:44:20+01:00, sven@riska.(none) +91 -58
    Added flag binlog_status to class sys_var. Added a getter and a
    constructor parameter that sets it.
    Since I had to change sys_var_thd_enum constructor anyways, I simplified
    it to use default values of arguments instead of three copies of the
    constructor.

  sql/sql_yacc.yy@stripped, 2008-02-27 15:44:20+01:00, sven@riska.(none) +3 -0
    Mark statements that refer to a system variable as "unsafe",
    meaning they will be replicated by row in mixed mode. Added comment to
    explain strange piece of code just above.

diff -Nrup a/include/m_ctype.h b/include/m_ctype.h
--- a/include/m_ctype.h	2007-08-13 21:39:23 +02:00
+++ b/include/m_ctype.h	2008-02-27 15:44:19 +01:00
@@ -282,6 +282,7 @@ typedef struct charset_info_st
   MY_COLLATION_HANDLER *coll;
   
 } CHARSET_INFO;
+#define ILLEGAL_CHARSET_INFO_NUMBER (~0)
 
 
 extern CHARSET_INFO my_charset_bin;
diff -Nrup a/mysql-test/include/diff_tables.inc b/mysql-test/include/diff_tables.inc
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/include/diff_tables.inc	2008-02-27 15:44:21 +01:00
@@ -0,0 +1,121 @@
+# ==== Purpose ====
+#
+# Check if the two given tables (possibly residing on different
+# master/slave servers) are equal.
+#
+# ==== Usage ====
+#
+# The tables to check are given by the test language variables
+# $diff_table_1 and $diff_table_2.  They must be of the
+# following form:
+#
+#  [m:|s:]database.table
+#
+# I.e., both database and table must be speicified.  Optionally, you
+# can prefix the name with m: (to read the table on master) or with s:
+# (to read the table on slave).  If no prefix is given, reads the
+# table from the current connection.  If one of these variables has a
+# prefix, both should have a prefix.
+#
+# ==== Side effects ====
+#
+# - Prints "Comparing tables $diff_table_1 and $diff_tables_2".
+#
+# - If the tables are different, prints the difference in a
+#   system-specific format (unified diff if supported) and generates
+#   an error.
+#
+# - If $diff_table_1 begins with 's:' or 'm:', connects to that
+#   host.  Otherwise, if $diff_table_2 begins with 's:' or 'm:',
+#   connects to that host.
+#
+# ==== Bugs ====
+#
+# - It is currently not possible to use this for tables that are
+#   supposed to be different, because if the files are different:
+#    - 'diff' produces system-dependent output,
+#    - the output includes the absolute path of the compared files,
+#    - the output includes a timestamp.
+#   To fix that, we'd probably have to use SQL to compute the
+#   symmetric difference between the tables.  I'm not sure how to do
+#   that efficiently.  If we implement this, it would be nice to
+#   compare the table definitions too.
+#
+# - It actually compares the result of "SELECT * FROM table ORDER BY
+#   col1, col2, ..., colN INTO OUTFILE 'file'".  Hence, it is assumed
+#   that the comparison order for both tables are equal.
+
+
+# ==== Save both tables to file ====
+
+--echo Comparing tables $diff_table_1 and $diff_table_2
+disable_query_log;
+
+--error 0,1
+--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_1
+--error 0,1
+--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_2
+
+let $_diff_table=$diff_table_2;
+let $_diff_i=2;
+while ($_diff_i) {
+
+  # Parse out any leading "m:" or "s:" from the table specification
+  # and connect the appropriate server.
+  let $_diff_colon=`SELECT SUBSTR('$_diff_table', 2, 1) = ':'`;
+  if ($_diff_colon) {
+    let $_diff_conn_master=`SELECT SUBSTR('$_diff_table', 1, 1) = 'm'`;
+    if ($_diff_conn_master) {
+      connection master;
+    }
+
+    # (Actually, if the table matches /^[^m]:/, then it is treated as
+    # /^s:/.)
+    if (!$_diff_conn_master) {
+      connection slave;
+    }
+    let $_diff_table=`SELECT SUBSTR('$_diff_table', 3)`;
+  }
+
+  # Sanity-check the input.
+  let $_diff_error= SELECT '$_diff_table' NOT LIKE '_%._%';
+  if ($_diff_error) {
+    --echo !!!ERROR IN TEST: \$diff_table_$_diff_i is not in the correct form.
+  }
+
+  # We need the output files to be sorted (so that diff_files does not
+  # think the files are different just because they are differently
+  # ordered).  To this end, we first generate a query that sorts the
+  # table by all columns.  Since ORDER BY accept column indices, we
+  # just generate a comma-separated list of all numbers from 1 to the
+  # number of columns in the table.
+  let $_diff_column_index=`SELECT MAX(ordinal_position)
+                           FROM information_schema.columns
+                           WHERE CONCAT(table_schema, '.', table_name) =
+                                 '$_diff_table'`;
+  let $_diff_column_list=$_diff_column_index;
+  dec $_diff_column_index;
+  while ($_diff_column_index) {
+    let $_diff_column_list=$_diff_column_index, $_diff_column_list;
+    dec $_diff_column_index;
+  }
+
+  # Now that we have the comma-separated list of columns, we can write
+  # the table to a file.
+  eval SELECT * FROM $_diff_table ORDER BY $_diff_column_list
+              INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/diff_table_$_diff_i';
+
+  # Do the same for $diff_table_1.
+  dec $_diff_i;
+  let $_diff_table=$diff_table_1;
+}
+
+
+# ==== Compare the generated files ====
+
+diff_files $MYSQLTEST_VARDIR/tmp/diff_table_1 $MYSQLTEST_VARDIR/tmp/diff_table_2;
+
+--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_1
+--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_2
+
+enable_query_log;
diff -Nrup a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result
--- a/mysql-test/r/mysqlbinlog.result	2008-02-01 17:51:22 +01:00
+++ b/mysql-test/r/mysqlbinlog.result	2008-02-27 15:44:19 +01:00
@@ -19,10 +19,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 drop table if exists t1,t2,t3,t4,t5,t03,t04
 /*!*/;
 SET TIMESTAMP=1000000000/*!*/;
@@ -61,10 +65,14 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values ("Alas")
 /*!*/;
 DELIMITER ;
@@ -89,10 +97,14 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values ("Alas")
 /*!*/;
 DELIMITER ;
@@ -107,10 +119,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 drop table if exists t1,t2,t3,t4,t5,t03,t04
 /*!*/;
 SET TIMESTAMP=1000000000/*!*/;
@@ -149,10 +165,14 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values ("Alas")
 /*!*/;
 DELIMITER ;
@@ -177,10 +197,14 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values ("Alas")
 /*!*/;
 DELIMITER ;
@@ -195,6 +219,10 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1108844556/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET TIMESTAMP=1108844555/*!*/;
@@ -209,6 +237,10 @@ ROLLBACK /* added by mysqlbinlog */;
 DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1108844556/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
 SET TIMESTAMP=1108844555/*!*/;
@@ -265,10 +297,14 @@ ERROR 42000: PROCEDURE test.p1 does not 
 DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 CREATE DEFINER=`root`@`localhost` procedure p1()
 begin
 select 1;
@@ -311,10 +347,14 @@ flush logs;
 DELIMITER /*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a varchar(64) character set utf8)
 /*!*/;
 SET TIMESTAMP=1000000000/*!*/;
diff -Nrup a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result
--- a/mysql-test/r/mysqlbinlog2.result	2008-01-30 20:29:01 +01:00
+++ b/mysql-test/r/mysqlbinlog2.result	2008-02-27 15:44:19 +01:00
@@ -18,61 +18,39 @@ insert into t1 values(null, "f");
 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos} 	Start: binlog v #, server v ##
created {yymmdd} {HH:MM:SS} at startup
 ROLLBACK/*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos}
	Query	thread_id={integer}	exec_time={integer}	error_code=0
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos} 	Intvar
 SET INSERT_ID=1/*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos}
	Query	thread_id={integer}	exec_time={integer}	error_code=0
 SET TIMESTAMP=1579609942/*!*/;
 insert into t1 values(null, "a")
 /*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos} 	Intvar
 SET INSERT_ID=2/*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos}
	Query	thread_id={integer}	exec_time={integer}	error_code=0
 SET TIMESTAMP=1579609942/*!*/;
 insert into t1 values(null, "b")
 /*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos} 	Intvar
 SET INSERT_ID=3/*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos}
	Query	thread_id={integer}	exec_time={integer}	error_code=0
 SET TIMESTAMP=1579609944/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos} 	Intvar
 SET INSERT_ID=4/*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos}
	Query	thread_id={integer}	exec_time={integer}	error_code=0
 SET TIMESTAMP=1579609946/*!*/;
 insert into t1 values(null, "d")
 /*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos} 	Intvar
 SET INSERT_ID=5/*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos}
	Query	thread_id={integer}	exec_time={integer}	error_code=0
 SET TIMESTAMP=1579609946/*!*/;
 insert into t1 values(null, "e")
 /*!*/;
-# at {pos}
-#{yymmdd} {HH:MM:SS} server id 1  end_log_pos {pos} 	Rotate to master-bin.000002  pos:
{pos}
 DELIMITER ;
 # End of log file
 ROLLBACK /* added by mysqlbinlog */;
@@ -86,10 +64,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=1/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "a")
 /*!*/;
 SET INSERT_ID=2/*!*/;
@@ -121,10 +103,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "d")
 /*!*/;
 SET INSERT_ID=5/*!*/;
@@ -143,10 +129,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -174,10 +164,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "d")
 /*!*/;
 DELIMITER ;
@@ -193,10 +187,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=3/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
 SET INSERT_ID=4/*!*/;
@@ -219,10 +217,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -246,10 +248,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -277,10 +283,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
 DELIMITER ;
@@ -296,10 +306,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=1/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "a")
 /*!*/;
 SET INSERT_ID=2/*!*/;
@@ -323,10 +337,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
 DELIMITER ;
@@ -342,10 +360,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "d")
 /*!*/;
 SET INSERT_ID=5/*!*/;
@@ -357,10 +379,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
 DELIMITER ;
@@ -375,10 +401,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -417,10 +447,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=3/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
 SET INSERT_ID=4/*!*/;
@@ -436,10 +470,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
 DELIMITER ;
@@ -454,10 +492,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -480,10 +522,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -519,10 +565,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=1/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "a")
 /*!*/;
 SET INSERT_ID=2/*!*/;
@@ -553,10 +603,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "d")
 /*!*/;
 SET INSERT_ID=5/*!*/;
@@ -575,10 +629,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -605,10 +663,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "d")
 /*!*/;
 DELIMITER ;
@@ -624,10 +686,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=3/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
 SET INSERT_ID=4/*!*/;
@@ -650,10 +716,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -676,10 +746,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -707,10 +781,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
 DELIMITER ;
@@ -726,10 +804,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=1/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "a")
 /*!*/;
 SET INSERT_ID=2/*!*/;
@@ -753,10 +835,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
 DELIMITER ;
@@ -771,10 +857,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=4/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609946/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "d")
 /*!*/;
 SET INSERT_ID=5/*!*/;
@@ -786,10 +876,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
 DELIMITER ;
@@ -804,10 +898,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -846,10 +944,14 @@ ROLLBACK/*!*/;
 SET INSERT_ID=3/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609944/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "c")
 /*!*/;
 SET INSERT_ID=4/*!*/;
@@ -865,10 +967,14 @@ DELIMITER /*!*/;
 SET INSERT_ID=6/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609943/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t1 values(null, "f")
 /*!*/;
 DELIMITER ;
@@ -883,10 +989,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
@@ -909,10 +1019,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1579609942/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int auto_increment not null primary key, b char(3))
 /*!*/;
 SET INSERT_ID=1/*!*/;
diff -Nrup a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result
--- a/mysql-test/r/user_var-binlog.result	2007-12-19 11:35:14 +01:00
+++ b/mysql-test/r/user_var-binlog.result	2008-02-27 15:44:19 +01:00
@@ -21,10 +21,14 @@ ROLLBACK/*!*/;
 SET @`a b`:=_latin1 0x68656C6C6F COLLATE `latin1_swedish_ci`/*!*/;
 use test/*!*/;
 SET TIMESTAMP=10000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 INSERT INTO t1 VALUES(@`a b`)
 /*!*/;
 SET @`var1`:=_latin1 0x273B616161 COLLATE `latin1_swedish_ci`/*!*/;
diff -Nrup a/mysql-test/suite/binlog/r/binlog_base64_flag.result
b/mysql-test/suite/binlog/r/binlog_base64_flag.result
--- a/mysql-test/suite/binlog/r/binlog_base64_flag.result	2008-02-08 18:16:56 +01:00
+++ b/mysql-test/suite/binlog/r/binlog_base64_flag.result	2008-02-27 15:44:19 +01:00
@@ -32,14 +32,17 @@ a
 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 DELIMITER /*!*/;
 # at 4
-#ROLLBACK/*!*/;
+<#>ROLLBACK/*!*/;
 # at 102
-#use test/*!*/;
+<#>use test/*!*/;
 SET TIMESTAMP=1196959712/*!*/;
-SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
+<#>SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 create table t1 (a int) engine= myisam
 /*!*/;
 # at 203
diff -Nrup a/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
b/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result
--- a/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result	2007-12-17 14:13:20 +01:00
+++ b/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result	2008-02-27 15:44:19 +01:00
@@ -15,10 +15,14 @@ ROLLBACK/*!*/;
 SET @`v`:=_ucs2 0x006100620063 COLLATE `ucs2_general_ci`/*!*/;
 use test/*!*/;
 SET TIMESTAMP=10000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 insert into t2 values (@v)
 /*!*/;
 DELIMITER ;
diff -Nrup a/mysql-test/suite/binlog/r/binlog_unsafe.result
b/mysql-test/suite/binlog/r/binlog_unsafe.result
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result	2008-01-11 13:27:13 +01:00
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result	2008-02-27 15:44:19 +01:00
@@ -1,13 +1,181 @@
-SET BINLOG_FORMAT=STATEMENT;
-CREATE TABLE t1 (a CHAR(40));
-CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY);
-CREATE TABLE t3 (b INT AUTO_INCREMENT PRIMARY KEY);
-CREATE VIEW v1(a,b) AS SELECT a,b FROM t2,t3;
-INSERT INTO t1 SELECT UUID();
+==== Setup tables ====
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a CHAR(40));
+CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE trigger_table (a CHAR(7));
+CREATE TABLE trigger_table2 (a INT);
+==== Non-deterministic statements ====
+INSERT DELAYED INTO t1 VALUES (5);
+==== Some variables that *should* be unsafe ====
+---- Insert directly ----
+INSERT INTO t1 VALUES (@@global.sync_binlog);
 Warnings:
 Warning	1592	Statement is not safe to log in statement format.
-SHOW WARNINGS;
-Level	Warning
-Code	1592
-Message	Statement is not safe to log in statement format.
-DROP TABLE t1,t2,t3;
+INSERT INTO t1 VALUES (@@session.insert_id);
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+INSERT INTO t1 VALUES (@@global.auto_increment_increment);
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+INSERT INTO t2 SELECT UUID();
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+INSERT INTO t2 VALUES (@@global.init_slave);
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+INSERT INTO t2 VALUES (@@hostname);
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+---- Insert from stored procedure ----
+CREATE PROCEDURE proc()
+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 (@@global.init_slave);
+INSERT INTO t2 VALUES (@@hostname);
+END|
+CALL proc();
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+---- Insert from stored function ----
+CREATE FUNCTION func()
+RETURNS INT
+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 (@@global.init_slave);
+INSERT INTO t2 VALUES (@@hostname);
+RETURN 0;
+END|
+SELECT func();
+func()
+0
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+---- Insert from trigger ----
+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 (@@global.init_slave);
+INSERT INTO t2 VALUES (@@hostname);
+END|
+INSERT INTO trigger_table VALUES ('bye.');
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+---- Insert from prepared statement ----
+PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)';
+PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)';
+PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)';
+PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()';
+PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)';
+PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@hostname)';
+EXECUTE p1;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+EXECUTE p2;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+EXECUTE p3;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+EXECUTE p4;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+EXECUTE p5;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+EXECUTE p6;
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+---- Insert from nested call of triggers / functions / procedures ----
+CREATE PROCEDURE proc1()
+INSERT INTO trigger_table VALUES ('ha!')|
+CREATE FUNCTION func2()
+RETURNS INT
+BEGIN
+CALL proc1();
+RETURN 0;
+END|
+CREATE TRIGGER trig3
+BEFORE INSERT ON trigger_table2
+FOR EACH ROW
+BEGIN
+DECLARE tmp INT;
+SELECT func2() INTO tmp;
+END|
+CREATE PROCEDURE proc4()
+INSERT INTO trigger_table2 VALUES (1)|
+CREATE FUNCTION func5()
+RETURNS INT
+BEGIN
+CALL proc4;
+RETURN 0;
+END|
+PREPARE prep6 FROM 'SELECT func5()'|
+EXECUTE prep6;
+func5()
+0
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+Warning	1592	Statement is not safe to log in statement format.
+==== Variables that should *not* be unsafe ====
+INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
+INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
+INSERT INTO t1 VALUES (@@session.foreign_key_checks);
+INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
+INSERT INTO t1 VALUES (@@session.unique_checks);
+INSERT INTO t2 VALUES (@@session.sql_mode);
+INSERT INTO t1 VALUES (@@session.auto_increment_increment);
+INSERT INTO t1 VALUES (@@session.auto_increment_offset);
+INSERT INTO t2 VALUES (@@session.character_set_client);
+INSERT INTO t2 VALUES (@@session.collation_connection);
+INSERT INTO t2 VALUES (@@session.collation_server);
+INSERT INTO t2 VALUES (@@session.time_zone);
+INSERT INTO t2 VALUES (@@session.lc_time_names);
+INSERT INTO t2 VALUES (@@session.collation_database);
+INSERT INTO t2 VALUES (@@session.timestamp);
+INSERT INTO t2 VALUES (@@session.last_insert_id);
+SET @my_var= 4711;
+INSERT INTO t1 VALUES (@my_var);
+SET insert_id=12;
+INSERT INTO t3 VALUES (NULL);
+==== Clean up ====
+DROP PROCEDURE proc;
+DROP FUNCTION  func;
+DROP TRIGGER   trig;
+DROP PROCEDURE proc1;
+DROP FUNCTION  func2;
+DROP TRIGGER   trig3;
+DROP PROCEDURE proc4;
+DROP FUNCTION  func5;
+DROP PREPARE   prep6;
+DROP TABLE t1, t2, t3, trigger_table, trigger_table2;
diff -Nrup a/mysql-test/suite/binlog/t/binlog_base64_flag.test
b/mysql-test/suite/binlog/t/binlog_base64_flag.test
--- a/mysql-test/suite/binlog/t/binlog_base64_flag.test	2008-02-09 19:43:31 +01:00
+++ b/mysql-test/suite/binlog/t/binlog_base64_flag.test	2008-02-27 15:44:19 +01:00
@@ -70,7 +70,7 @@ select * from t1;
 --echo ==== Test --base64-output=never on a binlog with row events ====
 
 # mysqlbinlog should fail
---replace_regex /#[0-9][0-9][0-9][0-9][0-9][0-9] .*/#/
+--replace_regex /#[0-9][0-9][0-9][0-9][0-9][0-9] .*/<#>/   /SET
\@\@session.pseudo_thread_id.*/<#>/
 error 1;
 exec $MYSQL_BINLOG --base64-output=never suite/binlog/std_data/bug32407.001;
 # the above line should output the query log event and then stop
diff -Nrup a/mysql-test/suite/binlog/t/binlog_unsafe.test
b/mysql-test/suite/binlog/t/binlog_unsafe.test
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test	2008-01-11 13:27:03 +01:00
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test	2008-02-27 15:44:19 +01:00
@@ -1,18 +1,224 @@
-# Test to check that a warning is generated for unsafe statements
-# executed under statement mode logging.
+# ==== Purpose ====
+#
+# Some statements can not be written to the binlog in a safe manner
+# with statement-based replication, either because they rely on
+# features that are local to the server they are replicated from
+# (e.g., @@variables), or because they include nondeterministic
+# queries (e.g., LIMIT), or because the time at which the query is
+# executed cannot be determined (e.g., INSERT DELAYED).  Such
+# statements should be marked unsafe.  All unsafe statements should
+# give a warning.
+#
+# This test verifies that a warning is generated for statements that
+# should be unsafe, when they are executed under statement mode
+# logging.
+#
+# All variables should be unsafe, with some exceptions.  Therefore,
+# this test also verifies that the exceptions do *not* generare a
+# warning.
+#
+#
+# ==== Method ====
+#
+# We try an INSERT DELAYED statement and verify that a warning is
+# issued.
+#
+# We try to insert unsafe variables into a table in several ways:
+# directly with an INSERT statement, from a stored procedure, from a
+# stored function, from a trigger, from a prepared statement, and from
+# a complicated nesting of triggers, functions, procedures, and
+# prepared statements.  In all cases, a warning should be issued.
+#
+# We try to insert the variables that should not be unsafe into a
+# table, and verify that *no* warning 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
+#
+#
+# ==== Todo ====
+#
+# There are several other ways to create unsafe statements: see, e.g.,
+# WL#3339, BUG#34768.
 
 source include/have_log_bin.inc;
+source include/have_binlog_format_statement.inc;
 
-SET BINLOG_FORMAT=STATEMENT;
-
-CREATE TABLE t1 (a CHAR(40));
-CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY);
-CREATE TABLE t3 (b INT AUTO_INCREMENT PRIMARY KEY);
-CREATE VIEW v1(a,b) AS SELECT a,b FROM t2,t3;
-
-INSERT INTO t1 SELECT UUID();
-query_vertical SHOW WARNINGS;
-
-DROP TABLE t1,t2,t3;
 
+--echo ==== Setup tables ====
 
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a CHAR(40));
+CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE trigger_table (a CHAR(7));
+CREATE TABLE trigger_table2 (a INT);
+
+
+--echo ==== Non-deterministic statements ====
+
+INSERT DELAYED INTO t1 VALUES (5);
+
+
+--echo ==== Some variables that *should* be unsafe ====
+
+--echo ---- Insert directly ----
+
+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 (@@global.init_slave);
+INSERT INTO t2 VALUES (@@hostname);
+
+--echo ---- Insert from stored procedure ----
+
+DELIMITER |;
+CREATE PROCEDURE proc()
+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 (@@global.init_slave);
+  INSERT INTO t2 VALUES (@@hostname);
+END|
+DELIMITER ;|
+
+CALL proc();
+
+--echo ---- Insert from stored function ----
+
+DELIMITER |;
+CREATE FUNCTION func()
+RETURNS INT
+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 (@@global.init_slave);
+  INSERT INTO t2 VALUES (@@hostname);
+  RETURN 0;
+END|
+DELIMITER ;|
+
+SELECT func();
+
+--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 (@@global.init_slave);
+  INSERT INTO t2 VALUES (@@hostname);
+END|
+DELIMITER ;|
+
+INSERT INTO trigger_table VALUES ('bye.');
+
+--echo ---- Insert from prepared statement ----
+
+PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)';
+PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)';
+PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)';
+PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()';
+PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)';
+PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@hostname)';
+
+EXECUTE p1; EXECUTE p2; EXECUTE p3; EXECUTE p4; EXECUTE p5; EXECUTE p6;
+
+--echo ---- Insert from nested call of triggers / functions / procedures ----
+
+DELIMITER |;
+
+# proc1: cause trigger 'trig' above to be triggered.
+CREATE PROCEDURE proc1()
+  INSERT INTO trigger_table VALUES ('ha!')|
+
+# func2: call proc1 above.
+CREATE FUNCTION func2()
+RETURNS INT
+BEGIN
+  CALL proc1();
+  RETURN 0;
+END|
+
+# trig3: call func2 above
+CREATE TRIGGER trig3
+BEFORE INSERT ON trigger_table2
+FOR EACH ROW
+BEGIN
+  DECLARE tmp INT;
+  SELECT func2() INTO tmp;
+END|
+
+# proc4: cause trig3 above to be triggered.
+CREATE PROCEDURE proc4()
+  INSERT INTO trigger_table2 VALUES (1)|
+
+# func5: call proc4 above.
+CREATE FUNCTION func5()
+RETURNS INT
+BEGIN
+  CALL proc4;
+  RETURN 0;
+END|
+
+# prep6: call func5() above.
+PREPARE prep6 FROM 'SELECT func5()'|
+
+DELIMITER ;|
+
+# try a complicated call path to trigger 'trig'.
+EXECUTE prep6;
+
+
+--echo ==== Variables that should *not* be unsafe ====
+
+INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
+INSERT INTO t1 VALUES (@@session.pseudo_thread_id);
+INSERT INTO t1 VALUES (@@session.foreign_key_checks);
+INSERT INTO t1 VALUES (@@session.sql_auto_is_null);
+INSERT INTO t1 VALUES (@@session.unique_checks);
+INSERT INTO t2 VALUES (@@session.sql_mode);
+INSERT INTO t1 VALUES (@@session.auto_increment_increment);
+INSERT INTO t1 VALUES (@@session.auto_increment_offset);
+INSERT INTO t2 VALUES (@@session.character_set_client);
+INSERT INTO t2 VALUES (@@session.collation_connection);
+INSERT INTO t2 VALUES (@@session.collation_server);
+INSERT INTO t2 VALUES (@@session.time_zone);
+INSERT INTO t2 VALUES (@@session.lc_time_names);
+INSERT INTO t2 VALUES (@@session.collation_database);
+INSERT INTO t2 VALUES (@@session.timestamp);
+INSERT INTO t2 VALUES (@@session.last_insert_id);
+SET @my_var= 4711;
+INSERT INTO t1 VALUES (@my_var);
+
+# using insert_id implicitly should be ok.
+SET insert_id=12;
+INSERT INTO t3 VALUES (NULL);
+
+
+--echo ==== Clean up ====
+
+DROP PROCEDURE proc;
+DROP FUNCTION  func;
+DROP TRIGGER   trig;
+DROP PROCEDURE proc1;
+DROP FUNCTION  func2;
+DROP TRIGGER   trig3;
+DROP PROCEDURE proc4;
+DROP FUNCTION  func5;
+DROP PREPARE   prep6;
+DROP TABLE t1, t2, t3, trigger_table, trigger_table2;
diff -Nrup a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result
--- a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result	2007-12-17 14:21:19 +01:00
+++ b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result	2008-02-27 15:44:19 +01:00
@@ -158,10 +158,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5
FLOAT)
 /*!*/;
 DELIMITER ;
@@ -176,10 +180,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 DROP TABLE IF EXISTS t1,t2,t3
 /*!*/;
 SET TIMESTAMP=1000000000/*!*/;
@@ -281,10 +289,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 DROP TABLE IF EXISTS t1,t2,t3
 /*!*/;
 SET TIMESTAMP=1000000000/*!*/;
@@ -308,10 +320,14 @@ DELIMITER /*!*/;
 ROLLBACK/*!*/;
 use test/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
+SET @@session.pseudo_thread_id=999999999/*!*/;
 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1,
@@session.unique_checks=1/*!*/;
 SET @@session.sql_mode=0/*!*/;
+SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 /*!\C latin1 *//*!*/;
 SET
@@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
+SET @@session.lc_time_names=0/*!*/;
+SET @@session.collation_database=DEFAULT/*!*/;
 CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5
FLOAT)
 /*!*/;
 DELIMITER ;
diff -Nrup a/mysql-test/suite/rpl/r/rpl_skip_error.result
b/mysql-test/suite/rpl/r/rpl_skip_error.result
--- a/mysql-test/suite/rpl/r/rpl_skip_error.result	2008-01-14 08:37:56 +01:00
+++ b/mysql-test/suite/rpl/r/rpl_skip_error.result	2008-02-27 15:44:19 +01:00
@@ -4,31 +4,33 @@ reset master;
 reset slave;
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 start slave;
+==== Test Without sql_mode=strict_trans_tables ====
+[on master]
 create table t1 (n int not null primary key);
+[on slave]
 insert into t1 values (1);
+[on master]
 insert into t1 values (1);
 insert into t1 values (2),(3);
+[on slave]
 select * from t1 ORDER BY n;
 n
 1
 2
 3
-drop table t1;
-create table t1(a int primary key);
-insert into t1 values (1),(2);
-delete from t1 where @@server_id=1;
+==== Test With sql_mode=strict_trans_tables ====
+insert into t1 values (7), (8);
+[on master]
 set sql_mode=strict_trans_tables;
-select @@server_id;
-@@server_id
-1
-insert into t1 values (1),(2),(3);
-select @@server_id;
-@@server_id
-2
+insert into t1 values (7), (8), (9);
+[on slave]
 select * from t1;
-a
+n
 1
 2
+3
+7
+8
 SHOW SLAVE STATUS;
 Slave_IO_State	#
 Master_Host	127.0.0.1
@@ -68,4 +70,5 @@ Last_IO_Errno	#
 Last_IO_Error	#
 Last_SQL_Errno	0
 Last_SQL_Error	
+==== Clean Up ====
 drop table t1;
diff -Nrup a/mysql-test/suite/rpl/r/rpl_variables.result
b/mysql-test/suite/rpl/r/rpl_variables.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/suite/rpl/r/rpl_variables.result	2008-02-26 18:46:00 +01:00
@@ -0,0 +1,582 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+==== Initialization ====
+[on master]
+SET @m_default_week_format=  @@global.default_week_format;
+SET @m_init_slave=           @@global.init_slave;
+SET @m_lc_time_names=        @@global.lc_time_names;
+SET @m_low_priority_updates= @@global.low_priority_updates;
+SET @m_relay_log_purge=      @@global.relay_log_purge;
+SET @m_slave_exec_mode=      @@global.slave_exec_mode;
+SET @m_sql_mode=             @@global.sql_mode;
+SET @m_sync_binlog=          @@global.sync_binlog;
+[on slave]
+SET @s_default_week_format=  @@global.default_week_format;
+SET @s_init_slave=           @@global.init_slave;
+SET @s_lc_time_names=        @@global.lc_time_names;
+SET @s_low_priority_updates= @@global.low_priority_updates;
+SET @s_relay_log_purge=      @@global.relay_log_purge;
+SET @s_slave_exec_mode=      @@global.slave_exec_mode;
+SET @s_sql_mode=             @@global.sql_mode;
+SET @s_sync_binlog=          @@global.sync_binlog;
+SET @@global.relay_log_purge = OFF;
+SET @@global.sync_binlog = 1000000;
+SET @@global.slave_exec_mode = 'STRICT';
+SET @@sql_big_selects = OFF;
+SET @@last_insert_id = 10;
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = OFF;
+SET @@global.default_week_format = 1;
+SET @@local.default_week_format = 2;
+SET @@global.lc_time_names = 'zh_HK';
+SET @@local.lc_time_names = 'zh_TW';
+SET @@global.sql_mode = 'ALLOW_INVALID_DATES';
+SET @@local.sql_mode = 'ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE';
+SET @user_num = 10;
+SET @user_text = 'Alunda';
+**** Resetting master and slave ****
+STOP SLAVE;
+RESET SLAVE;
+RESET MASTER;
+START SLAVE;
+SET @@global.init_slave = 'ant';
+[on master]
+CREATE TABLE tstmt (id INT AUTO_INCREMENT PRIMARY KEY,
+truth BOOLEAN,
+num INT,
+text VARCHAR(100));
+CREATE TABLE tproc LIKE tstmt;
+CREATE TABLE tfunc LIKE tstmt;
+CREATE TABLE ttrig LIKE tstmt;
+CREATE TABLE tprep LIKE tstmt;
+CREATE TABLE trigger_table (text CHAR(4));
+==== Insert variables directly ====
+----  global variables ----
+SET @@global.relay_log_purge = ON;
+INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge);
+SET @@global.relay_log_purge = OFF;
+INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge);
+SET @@global.sync_binlog = 2000000;
+INSERT INTO tstmt(num) VALUES (@@global.sync_binlog);
+SET @@global.sync_binlog = 3000000;
+INSERT INTO tstmt(num) VALUES (@@global.sync_binlog);
+SET @@global.init_slave = 'bison';
+INSERT INTO tstmt(text) VALUES (@@global.init_slave);
+SET @@global.init_slave = 'cat';
+INSERT INTO tstmt(text) VALUES (@@global.init_slave);
+SET @@global.slave_exec_mode = 'IDEMPOTENT';
+INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode);
+SET @@global.slave_exec_mode = 'STRICT';
+INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode);
+---- session variables ----
+SET @@sql_big_selects = ON;
+INSERT INTO tstmt(truth) VALUES (@@sql_big_selects);
+SET @@sql_big_selects = OFF;
+INSERT INTO tstmt(truth) VALUES (@@sql_big_selects);
+SET @@last_insert_id = 20;
+INSERT INTO tstmt(num) VALUES (@@last_insert_id);
+SET @@last_insert_id = 30;
+INSERT INTO tstmt(num) VALUES (@@last_insert_id);
+---- global and session variables ----
+SET @@global.low_priority_updates = ON;
+SET @@local.low_priority_updates = OFF;
+INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates);
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = ON;
+INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates);
+SET @@global.default_week_format = 3;
+SET @@local.default_week_format = 4;
+INSERT INTO tstmt(num) VALUES (@@global.default_week_format);
+INSERT INTO tstmt(num) VALUES (@@local.default_week_format);
+SET @@global.default_week_format = 5;
+SET @@local.default_week_format = 6;
+INSERT INTO tstmt(num) VALUES (@@global.default_week_format);
+INSERT INTO tstmt(num) VALUES (@@local.default_week_format);
+SET @@global.lc_time_names = 'sv_SE';
+SET @@local.lc_time_names = 'sv_FI';
+INSERT INTO tstmt(text) VALUES (@@global.lc_time_names);
+INSERT INTO tstmt(text) VALUES (@@local.lc_time_names);
+SET @@global.lc_time_names = 'ar_TN';
+SET @@local.lc_time_names = 'ar_IQ';
+INSERT INTO tstmt(text) VALUES (@@global.lc_time_names);
+INSERT INTO tstmt(text) VALUES (@@local.lc_time_names);
+SET @@global.sql_mode = '';
+SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+INSERT INTO tstmt(text) VALUES (@@global.sql_mode);
+INSERT INTO tstmt(text) VALUES (@@local.sql_mode);
+SET @@global.sql_mode =
'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION';
+SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
+INSERT INTO tstmt(text) VALUES (@@global.sql_mode);
+INSERT INTO tstmt(text) VALUES (@@local.sql_mode);
+---- user variables ----
+SET @user_num = 20;
+INSERT INTO tstmt(num) VALUES (@user_num);
+SET @user_num = 30;
+INSERT INTO tstmt(num) VALUES (@user_num);
+SET @user_text = 'Bergsbrunna';
+INSERT INTO tstmt(text) VALUES (@user_text);
+SET @user_text = 'Centrum';
+INSERT INTO tstmt(text) VALUES (@user_text);
+==== Insert variables from a stored procedure ====
+CREATE PROCEDURE proc()
+BEGIN
+# GLOBAL
+# boolean
+SET @@global.relay_log_purge = ON;
+INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge);
+SET @@global.relay_log_purge = OFF;
+INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge);
+# numeric
+SET @@global.sync_binlog = 2000000;
+INSERT INTO tproc(num) VALUES (@@global.sync_binlog);
+SET @@global.sync_binlog = 3000000;
+INSERT INTO tproc(num) VALUES (@@global.sync_binlog);
+# string
+SET @@global.init_slave = 'bison';
+INSERT INTO tproc(text) VALUES (@@global.init_slave);
+SET @@global.init_slave = 'cat';
+INSERT INTO tproc(text) VALUES (@@global.init_slave);
+# enumeration
+SET @@global.slave_exec_mode = 'IDEMPOTENT';
+INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode);
+SET @@global.slave_exec_mode = 'STRICT';
+INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode);
+# SESSION
+# boolean
+SET @@sql_big_selects = ON;
+INSERT INTO tproc(truth) VALUES (@@sql_big_selects);
+SET @@sql_big_selects = OFF;
+INSERT INTO tproc(truth) VALUES (@@sql_big_selects);
+# numeric
+SET @@last_insert_id = 20;
+INSERT INTO tproc(num) VALUES (@@last_insert_id);
+SET @@last_insert_id = 30;
+INSERT INTO tproc(num) VALUES (@@last_insert_id);
+# BOTH
+# boolean
+SET @@global.low_priority_updates = ON;
+SET @@local.low_priority_updates = OFF;
+INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates);
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = ON;
+INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates);
+# numeric
+SET @@global.default_week_format = 3;
+SET @@local.default_week_format = 4;
+INSERT INTO tproc(num) VALUES (@@global.default_week_format);
+INSERT INTO tproc(num) VALUES (@@local.default_week_format);
+SET @@global.default_week_format = 5;
+SET @@local.default_week_format = 6;
+INSERT INTO tproc(num) VALUES (@@global.default_week_format);
+INSERT INTO tproc(num) VALUES (@@local.default_week_format);
+# text
+SET @@global.lc_time_names = 'sv_SE';
+SET @@local.lc_time_names = 'sv_FI';
+INSERT INTO tproc(text) VALUES (@@global.lc_time_names);
+INSERT INTO tproc(text) VALUES (@@local.lc_time_names);
+SET @@global.lc_time_names = 'ar_TN';
+SET @@local.lc_time_names = 'ar_IQ';
+INSERT INTO tproc(text) VALUES (@@global.lc_time_names);
+INSERT INTO tproc(text) VALUES (@@local.lc_time_names);
+# enum
+SET @@global.sql_mode = '';
+SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+INSERT INTO tproc(text) VALUES (@@global.sql_mode);
+INSERT INTO tproc(text) VALUES (@@local.sql_mode);
+SET @@global.sql_mode =
'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION';
+SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
+INSERT INTO tproc(text) VALUES (@@global.sql_mode);
+INSERT INTO tproc(text) VALUES (@@local.sql_mode);
+# USER
+# numeric
+SET @user_num = 20;
+INSERT INTO tproc(num) VALUES (@user_num);
+SET @user_num = 30;
+INSERT INTO tproc(num) VALUES (@user_num);
+# string
+SET @user_text = 'Bergsbrunna';
+INSERT INTO tproc(text) VALUES (@user_text);
+SET @user_text = 'Centrum';
+INSERT INTO tproc(text) VALUES (@user_text);
+END|
+CALL proc();
+==== Insert variables from a stored function ====
+CREATE FUNCTION func()
+RETURNS INT
+BEGIN
+# GLOBAL
+# boolean
+SET @@global.relay_log_purge = ON;
+INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge);
+SET @@global.relay_log_purge = OFF;
+INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge);
+# numeric
+SET @@global.sync_binlog = 2000000;
+INSERT INTO tfunc(num) VALUES (@@global.sync_binlog);
+SET @@global.sync_binlog = 3000000;
+INSERT INTO tfunc(num) VALUES (@@global.sync_binlog);
+# string
+SET @@global.init_slave = 'bison';
+INSERT INTO tfunc(text) VALUES (@@global.init_slave);
+SET @@global.init_slave = 'cat';
+INSERT INTO tfunc(text) VALUES (@@global.init_slave);
+# enumeration
+SET @@global.slave_exec_mode = 'IDEMPOTENT';
+INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode);
+SET @@global.slave_exec_mode = 'STRICT';
+INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode);
+# SESSION
+# boolean
+SET @@sql_big_selects = ON;
+INSERT INTO tfunc(truth) VALUES (@@sql_big_selects);
+SET @@sql_big_selects = OFF;
+INSERT INTO tfunc(truth) VALUES (@@sql_big_selects);
+# numeric
+SET @@last_insert_id = 20;
+INSERT INTO tfunc(num) VALUES (@@last_insert_id);
+SET @@last_insert_id = 30;
+INSERT INTO tfunc(num) VALUES (@@last_insert_id);
+# BOTH
+# boolean
+SET @@global.low_priority_updates = ON;
+SET @@local.low_priority_updates = OFF;
+INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates);
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = ON;
+INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates);
+# numeric
+SET @@global.default_week_format = 3;
+SET @@local.default_week_format = 4;
+INSERT INTO tfunc(num) VALUES (@@global.default_week_format);
+INSERT INTO tfunc(num) VALUES (@@local.default_week_format);
+SET @@global.default_week_format = 5;
+SET @@local.default_week_format = 6;
+INSERT INTO tfunc(num) VALUES (@@global.default_week_format);
+INSERT INTO tfunc(num) VALUES (@@local.default_week_format);
+# text
+SET @@global.lc_time_names = 'sv_SE';
+SET @@local.lc_time_names = 'sv_FI';
+INSERT INTO tfunc(text) VALUES (@@global.lc_time_names);
+INSERT INTO tfunc(text) VALUES (@@local.lc_time_names);
+SET @@global.lc_time_names = 'ar_TN';
+SET @@local.lc_time_names = 'ar_IQ';
+INSERT INTO tfunc(text) VALUES (@@global.lc_time_names);
+INSERT INTO tfunc(text) VALUES (@@local.lc_time_names);
+# enum
+SET @@global.sql_mode = '';
+SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+INSERT INTO tfunc(text) VALUES (@@global.sql_mode);
+INSERT INTO tfunc(text) VALUES (@@local.sql_mode);
+SET @@global.sql_mode =
'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION';
+SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
+INSERT INTO tfunc(text) VALUES (@@global.sql_mode);
+INSERT INTO tfunc(text) VALUES (@@local.sql_mode);
+# USER
+# numeric
+SET @user_num = 20;
+INSERT INTO tfunc(num) VALUES (@user_num);
+SET @user_num = 30;
+INSERT INTO tfunc(num) VALUES (@user_num);
+# string
+SET @user_text = 'Bergsbrunna';
+INSERT INTO tfunc(text) VALUES (@user_text);
+SET @user_text = 'Centrum';
+INSERT INTO tfunc(text) VALUES (@user_text);
+RETURN 0;
+END|
+SELECT func();
+func()
+0
+==== Insert variables from a trigger ====
+CREATE TRIGGER trig
+BEFORE INSERT ON trigger_table
+FOR EACH ROW
+BEGIN
+# GLOBAL
+# boolean
+SET @@global.relay_log_purge = ON;
+INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge);
+SET @@global.relay_log_purge = OFF;
+INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge);
+# numeric
+SET @@global.sync_binlog = 2000000;
+INSERT INTO ttrig(num) VALUES (@@global.sync_binlog);
+SET @@global.sync_binlog = 3000000;
+INSERT INTO ttrig(num) VALUES (@@global.sync_binlog);
+# string
+SET @@global.init_slave = 'bison';
+INSERT INTO ttrig(text) VALUES (@@global.init_slave);
+SET @@global.init_slave = 'cat';
+INSERT INTO ttrig(text) VALUES (@@global.init_slave);
+# enumeration
+SET @@global.slave_exec_mode = 'IDEMPOTENT';
+INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode);
+SET @@global.slave_exec_mode = 'STRICT';
+INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode);
+# SESSION
+# boolean
+SET @@sql_big_selects = ON;
+INSERT INTO ttrig(truth) VALUES (@@sql_big_selects);
+SET @@sql_big_selects = OFF;
+INSERT INTO ttrig(truth) VALUES (@@sql_big_selects);
+# numeric
+SET @@last_insert_id = 20;
+INSERT INTO ttrig(num) VALUES (@@last_insert_id);
+SET @@last_insert_id = 30;
+INSERT INTO ttrig(num) VALUES (@@last_insert_id);
+# BOTH
+# boolean
+SET @@global.low_priority_updates = ON;
+SET @@local.low_priority_updates = OFF;
+INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates);
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = ON;
+INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates);
+# numeric
+SET @@global.default_week_format = 3;
+SET @@local.default_week_format = 4;
+INSERT INTO ttrig(num) VALUES (@@global.default_week_format);
+INSERT INTO ttrig(num) VALUES (@@local.default_week_format);
+SET @@global.default_week_format = 5;
+SET @@local.default_week_format = 6;
+INSERT INTO ttrig(num) VALUES (@@global.default_week_format);
+INSERT INTO ttrig(num) VALUES (@@local.default_week_format);
+# text
+SET @@global.lc_time_names = 'sv_SE';
+SET @@local.lc_time_names = 'sv_FI';
+INSERT INTO ttrig(text) VALUES (@@global.lc_time_names);
+INSERT INTO ttrig(text) VALUES (@@local.lc_time_names);
+SET @@global.lc_time_names = 'ar_TN';
+SET @@local.lc_time_names = 'ar_IQ';
+INSERT INTO ttrig(text) VALUES (@@global.lc_time_names);
+INSERT INTO ttrig(text) VALUES (@@local.lc_time_names);
+# enum
+SET @@global.sql_mode = '';
+SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+INSERT INTO ttrig(text) VALUES (@@global.sql_mode);
+INSERT INTO ttrig(text) VALUES (@@local.sql_mode);
+SET @@global.sql_mode =
'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION';
+SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS';
+INSERT INTO ttrig(text) VALUES (@@global.sql_mode);
+INSERT INTO ttrig(text) VALUES (@@local.sql_mode);
+# USER
+# numeric
+SET @user_num = 20;
+INSERT INTO ttrig(num) VALUES (@user_num);
+SET @user_num = 30;
+INSERT INTO ttrig(num) VALUES (@user_num);
+# string
+SET @user_text = 'Bergsbrunna';
+INSERT INTO ttrig(text) VALUES (@user_text);
+SET @user_text = 'Centrum';
+INSERT INTO ttrig(text) VALUES (@user_text);
+END|
+INSERT INTO trigger_table VALUES ('bye.');
+==== Insert variables from a prepared statement ====
+PREPARE p1 FROM 'SET @@global.relay_log_purge = ON';
+PREPARE p2 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)';
+PREPARE p3 FROM 'SET @@global.relay_log_purge = OFF';
+PREPARE p4 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)';
+PREPARE p5 FROM 'SET @@global.sync_binlog = 2000000';
+PREPARE p6 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)';
+PREPARE p7 FROM 'SET @@global.sync_binlog = 3000000';
+PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)';
+PREPARE p9 FROM 'SET @@global.init_slave = \'bison\'';
+PREPARE p10 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';
+PREPARE p11 FROM 'SET @@global.init_slave = \'cat\'';
+PREPARE p12 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';
+PREPARE p13 FROM 'SET @@global.slave_exec_mode = \'IDEMPOTENT\'';
+PREPARE p14 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)';
+PREPARE p15 FROM 'SET @@global.slave_exec_mode = \'STRICT\'';
+PREPARE p16 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)';
+PREPARE p17 FROM 'SET @@sql_big_selects = ON';
+PREPARE p18 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)';
+PREPARE p19 FROM 'SET @@sql_big_selects = OFF';
+PREPARE p20 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)';
+PREPARE p21 FROM 'SET @@last_insert_id = 20';
+PREPARE p22 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)';
+PREPARE p23 FROM 'SET @@last_insert_id = 30';
+PREPARE p24 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)';
+PREPARE p25 FROM 'SET @@global.low_priority_updates = ON';
+PREPARE p26 FROM 'SET @@local.low_priority_updates = OFF';
+PREPARE p27 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)';
+PREPARE p28 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)';
+PREPARE p29 FROM 'SET @@global.low_priority_updates = OFF';
+PREPARE p30 FROM 'SET @@local.low_priority_updates = ON';
+PREPARE p31 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)';
+PREPARE p32 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)';
+PREPARE p33 FROM 'SET @@global.default_week_format = 3';
+PREPARE p34 FROM 'SET @@local.default_week_format = 4';
+PREPARE p35 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)';
+PREPARE p36 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)';
+PREPARE p37 FROM 'SET @@global.default_week_format = 5';
+PREPARE p38 FROM 'SET @@local.default_week_format = 6';
+PREPARE p39 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)';
+PREPARE p40 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)';
+PREPARE p41 FROM 'SET @@global.lc_time_names = \'sv_SE\'';
+PREPARE p42 FROM 'SET @@local.lc_time_names = \'sv_FI\'';
+PREPARE p43 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)';
+PREPARE p44 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)';
+PREPARE p45 FROM 'SET @@global.lc_time_names = \'ar_TN\'';
+PREPARE p46 FROM 'SET @@local.lc_time_names = \'ar_IQ\'';
+PREPARE p47 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)';
+PREPARE p48 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)';
+PREPARE p49 FROM 'SET @@global.sql_mode = \'\'';
+PREPARE p50 FROM 'SET @@local.sql_mode = \'IGNORE_SPACE,NO_AUTO_CREATE_USER\'';
+PREPARE p51 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)';
+PREPARE p52 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)';
+PREPARE p53 FROM 'SET @@global.sql_mode =
\'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION\'';
+PREPARE p54 FROM 'SET @@local.sql_mode =
\'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS\'';
+PREPARE p55 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)';
+PREPARE p56 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)';
+PREPARE p57 FROM 'SET @user_num = 20';
+PREPARE p58 FROM 'INSERT INTO tprep(num) VALUES (@user_num)';
+PREPARE p59 FROM 'SET @user_num = 30';
+PREPARE p60 FROM 'INSERT INTO tprep(num) VALUES (@user_num)';
+PREPARE p61 FROM 'SET @user_text = \'Bergsbrunna\'';
+PREPARE p62 FROM 'INSERT INTO tprep(text) VALUES (@user_text)';
+PREPARE p63 FROM 'SET @user_text = \'Centrum\'';
+PREPARE p64 FROM 'INSERT INTO tprep(text) VALUES (@user_text)';
+EXECUTE p1;
+EXECUTE p2;
+EXECUTE p3;
+EXECUTE p4;
+EXECUTE p5;
+EXECUTE p6;
+EXECUTE p7;
+EXECUTE p8;
+EXECUTE p9;
+EXECUTE p10;
+EXECUTE p11;
+EXECUTE p12;
+EXECUTE p13;
+EXECUTE p14;
+EXECUTE p15;
+EXECUTE p16;
+EXECUTE p17;
+EXECUTE p18;
+EXECUTE p19;
+EXECUTE p20;
+EXECUTE p21;
+EXECUTE p22;
+EXECUTE p23;
+EXECUTE p24;
+EXECUTE p25;
+EXECUTE p26;
+EXECUTE p27;
+EXECUTE p28;
+EXECUTE p29;
+EXECUTE p30;
+EXECUTE p31;
+EXECUTE p32;
+EXECUTE p33;
+EXECUTE p34;
+EXECUTE p35;
+EXECUTE p36;
+EXECUTE p37;
+EXECUTE p38;
+EXECUTE p39;
+EXECUTE p40;
+EXECUTE p41;
+EXECUTE p42;
+EXECUTE p43;
+EXECUTE p44;
+EXECUTE p45;
+EXECUTE p46;
+EXECUTE p47;
+EXECUTE p48;
+EXECUTE p49;
+EXECUTE p50;
+EXECUTE p51;
+EXECUTE p52;
+EXECUTE p53;
+EXECUTE p54;
+EXECUTE p55;
+EXECUTE p56;
+EXECUTE p57;
+EXECUTE p58;
+EXECUTE p59;
+EXECUTE p60;
+EXECUTE p61;
+EXECUTE p62;
+EXECUTE p63;
+EXECUTE p64;
+==== Results ====
+SELECT * FROM tstmt ORDER BY id;
+id	truth	num	text
+1	1	NULL	NULL
+2	0	NULL	NULL
+3	NULL	2000000	NULL
+4	NULL	3000000	NULL
+5	NULL	NULL	bison
+6	NULL	NULL	cat
+7	NULL	NULL	IDEMPOTENT
+8	NULL	NULL	STRICT
+9	1	NULL	NULL
+10	0	NULL	NULL
+11	NULL	20	NULL
+12	NULL	30	NULL
+13	1	NULL	NULL
+14	0	NULL	NULL
+15	0	NULL	NULL
+16	1	NULL	NULL
+17	NULL	3	NULL
+18	NULL	4	NULL
+19	NULL	5	NULL
+20	NULL	6	NULL
+21	NULL	NULL	sv_SE
+22	NULL	NULL	sv_FI
+23	NULL	NULL	ar_TN
+24	NULL	NULL	ar_IQ
+25	NULL	NULL	
+26	NULL	NULL	IGNORE_SPACE,NO_AUTO_CREATE_USER
+27	NULL	NULL	NO_DIR_IN_CREATE,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_ENGINE_SUBSTITUTION
+28	NULL	NULL	NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
+29	NULL	20	NULL
+30	NULL	30	NULL
+31	NULL	NULL	Bergsbrunna
+32	NULL	NULL	Centrum
+Comparing tables m:test.tstmt and m:test.tproc
+Comparing tables m:test.tstmt and m:test.tfunc
+Comparing tables m:test.tstmt and m:test.ttrig
+Comparing tables m:test.tstmt and m:test.tprep
+Comparing tables m:test.tstmt and s:test.tstmt
+Comparing tables m:test.tstmt and s:test.tproc
+Comparing tables m:test.tstmt and s:test.tfunc