path: root/mysql-test/suite/rpl/t/rpl_variables.test

# ==== Purpose ====
#
# Test that queries referencing variables are replicated correctly in
# mixed and row-based logging mode.
#
#
# ==== Method ====
#
# The test simply does a lot of "INSERT INTO t1 VALUES (@@variable)"
# and checks the result on the slave.
#
# Statements referencing a variable only replicate correctly in mixed
# and row mode: in row mode, the values inserted are replicated.  In
# mixed mode, statements referencing a variable are marked as unsafe,
# meaning they will be replicated by row.  In statement mode, the
# slave's value will be used and replication will break. (Except in a
# small number of special cases: random seeds, insert_id, and
# auto_increment are replicated).
#
# We test the following variable scopes:
#  - server system variables
#  - server session variables
#  - server "both" variables
#  - user variables
#
# For each scope, we use variables of the following types if they
# exist:
#  - boolean
#  - numeric
#  - string
#  - enumeration variables
#
# We use these types of variables in the following contexts:
#  - directly
#  - from a stored procedure
#  - from a stored function
#  - from a trigger
#  - from a prepared statement
#
# For all variables where it is possible, we set the variable to one
# value on slave, and insert it on the master with two distinct
# values.
#
# The same insertions are made in four different tables using direct
# insert, stored procedure, stored function, or trigger.  Then all
# eight resulting tables on master and slave are compared.
#
#
# ==== Related bugs ====
#
# BUG#31168: @@hostname does not replicate
#
#
# ==== Related test cases ====
#
# binlog.binlog_unsafe tests that a warning is issued if system
# variables are replicated in statement mode.
#
# rpl.rpl_variables_stm tests the small subset of variables that
# actually can be replicated safely in statement mode.


source include/master-slave.inc;
source include/have_binlog_format_mixed_or_row.inc;


--echo ==== Initialization ====

# Backup the values of global variables so that they can be restored
# later.
--echo [on master]
connection 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;

--echo [on slave]
connection 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 variables on slave to something different than on master.
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';

# Stop slave so that we get a fresh sql thread, reading the slave's
# global values of variables into its local copies.
--source include/rpl_reset.inc

# We would have wanted to set this together with the other variables
# above, but can't because it affects how the slave works.
--echo [on slave]
connection slave;
SET @@global.init_slave = 'SELECT 1';


--echo [on master]
connection master;

# Tables where everything happens.
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;

# Table on which we put a trigger.
CREATE TABLE trigger_table (text CHAR(4));


--echo ==== Insert variables directly ====

--echo ----  global variables ----

# boolean
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);

# numeric
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);

# string
SET @@global.init_slave = 'SELECT 2';
INSERT INTO tstmt(text) VALUES (@@global.init_slave);
SET @@global.init_slave = 'SELECT 3';
INSERT INTO tstmt(text) VALUES (@@global.init_slave);

# enumeration
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);


--echo ---- session variables ----

# boolean
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);

# numeric
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);

--echo ---- global and session variables ----

# boolean
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);

# numeric
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);

# string
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);

# enum
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);

--echo ---- user variables ----

# numeric
SET @user_num = 20;
INSERT INTO tstmt(num) VALUES (@user_num);
SET @user_num = 30;
INSERT INTO tstmt(num) VALUES (@user_num);

# string
SET @user_text = 'Bergsbrunna';
INSERT INTO tstmt(text) VALUES (@user_text);
SET @user_text = 'Centrum';
INSERT INTO tstmt(text) VALUES (@user_text);


--echo ==== Insert variables from a stored procedure ====

DELIMITER |;
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 = 'SELECT 2';
  INSERT INTO tproc(text) VALUES (@@global.init_slave);
  SET @@global.init_slave = 'SELECT 3';
  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|
DELIMITER ;|

CALL proc();


--echo ==== Insert variables from a stored function ====

DELIMITER |;
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 = 'SELECT 2';
  INSERT INTO tfunc(text) VALUES (@@global.init_slave);
  SET @@global.init_slave = 'SELECT 3';
  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|
DELIMITER ;|

SELECT func();


--echo ==== Insert variables from a trigger ====

DELIMITER |;
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 = 'SELECT 2';
  INSERT INTO ttrig(text) VALUES (@@global.init_slave);
  SET @@global.init_slave = 'SELECT 3';
  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|
DELIMITER ;|

INSERT INTO trigger_table VALUES ('bye.');


--echo ==== Insert variables from a prepared statement ====

# GLOBAL

# boolean
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)';

# numeric
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)';

# string
PREPARE p9 FROM 'SET @@global.init_slave = \'SELECT 2\'';
PREPARE p10 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';
PREPARE p11 FROM 'SET @@global.init_slave = \'SELECT 3\'';
PREPARE p12 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)';

# enumeration
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)';

# SESSION

# boolean
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)';

# numeric
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)';

# BOTH

# boolean
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)';

# numeric
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)';

# text
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)';

# enum
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)';

# USER

# numeric
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)';

# string
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;


--echo ==== Results ====

# Show the result in table test.tstmt on master...
SELECT * FROM tstmt ORDER BY id;

--sync_slave_with_master

# ... then compare test.tstmt on master to the other tables on master and slave.
let $diff_tables= master:tstmt,tproc,tfunc,ttrig,tprep, slave:tstmt,tproc,tfunc,ttrig,tprep;
source include/diff_tables.inc;


--echo ==== Clean up ====

--echo [on master]
connection 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;

--echo [on slave]
connection 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;

connection master;
sync_slave_with_master;
--source include/rpl_end.inc