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. There are some exceptions: some variables are actually
replicated. Those should *not* be marked as unsafe.
BUG#34732: mysqlbinlog does not print default values for auto_increment variables
Problem: mysqlbinlog does not print default values for some variables,
including auto_increment_increment and others. So if a client executing
the output of mysqlbinlog has different default values, replication will
be wrong.
Fix: Always print default values for all variables that are replicated.
I need to fix the two bugs at the same time, because the test cases would
fail if I only fixed one of them.


include/m_ctype.h:
  Added definition of ILLEGAL_CHARSET_INFO_NUMBER. We just need a symbol
  for a number that will never be used by any charset. ~0U should be safe
  since charset numbers are sequential, starting from 0.
mysql-test/include/commit.inc:
  Upated test to avoid making statements unsafe.
mysql-test/r/commit_1innodb.result:
  Updated test needs updated result file.
mysql-test/r/mysqlbinlog.result:
  Updated result file.
mysql-test/r/mysqlbinlog2.result:
  Updated result file.
mysql-test/r/user_var-binlog.result:
  Updated result file.
mysql-test/suite/binlog/r/binlog_base64_flag.result:
  Updated result file.
mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result:
  Updated result file.
mysql-test/suite/binlog/r/binlog_unsafe.result:
  Modified test file needs modified result file.
mysql-test/suite/binlog/t/binlog_base64_flag.test:
  Need to filter out pseudo_thread_id from result since it is
  nondeterministic.
mysql-test/suite/binlog/t/binlog_unsafe.test:
  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:
  Updated result file.
mysql-test/suite/rpl/r/rpl_skip_error.result:
  Updated result file.
mysql-test/suite/rpl/t/rpl_skip_error.test:
  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/t/mysqlbinlog2.test:
  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:
  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:
  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:
   - 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:
  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:
  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:
  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:
  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.
mysql-test/include/diff_tables.inc:
  New auxiliary test file that tests whether two tables (possibly one on
  master and one on slave) differ.
mysql-test/suite/rpl/r/rpl_variables.result:
  New test case needs new result file.
mysql-test/suite/rpl/r/rpl_variables_stm.result:
  New test file needs new result file.
mysql-test/suite/rpl/t/rpl_variables.test:
  Test that INSERT of @@variables is replicated correctly (by switching to
  row-based mode).
mysql-test/suite/rpl/t/rpl_variables_stm.test:
  Test that replication of @@variables which are replicated explicitly works
  as expected in statement mode (without giving warnings).

1 files changed, 584 insertions, 0 deletions

diff --git a/mysql-test/suite/rpl/r/rpl_variables.result b/mysql-test/suite/rpl/r/rpl_variables.result
new file mode 100644
index 00000000000..73354666b7a
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_variables.result
@@ -0,0 +1,584 @@
+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';
+[on master]
+**** Resetting master and slave ****
+STOP SLAVE;
+RESET SLAVE;
+RESET MASTER;
+START SLAVE;
+[on 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 master:test.tstmt and master:test.tproc
+Comparing tables master:test.tstmt and master:test.tfunc
+Comparing tables master:test.tstmt and master:test.ttrig
+Comparing tables master:test.tstmt and master:test.tprep
+Comparing tables master:test.tstmt and slave:test.tstmt
+Comparing tables master:test.tstmt and slave:test.tproc
+Comparing tables master:test.tstmt and slave:test.tfunc
+Comparing tables master:test.tstmt and slave:test.ttrig
+Comparing tables master:test.tstmt and slave:test.tprep
+==== Clean up ====
+[on master]
+DROP PROCEDURE proc;
+DROP FUNCTION func;
+DROP TRIGGER trig;
+DROP TABLE tstmt, tproc, tfunc, ttrig, tprep, trigger_table;
+SET @@global.default_week_format=  @m_default_week_format;
+SET @@global.init_slave=           @m_init_slave;
+SET @@global.lc_time_names=        @m_lc_time_names;
+SET @@global.low_priority_updates= @m_low_priority_updates;
+SET @@global.relay_log_purge=      @m_relay_log_purge;
+SET @@global.slave_exec_mode=      @m_slave_exec_mode;
+SET @@global.sql_mode=             @m_sql_mode;
+SET @@global.sync_binlog=          @m_sync_binlog;
+[on slave]
+SET @@global.default_week_format=  @s_default_week_format;
+SET @@global.init_slave=           @s_init_slave;
+SET @@global.lc_time_names=        @s_lc_time_names;
+SET @@global.low_priority_updates= @s_low_priority_updates;
+SET @@global.relay_log_purge=      @s_relay_log_purge;
+SET @@global.slave_exec_mode=      @s_slave_exec_mode;
+SET @@global.sql_mode=             @s_sql_mode;
+SET @@global.sync_binlog=          @s_sync_binlog;