List:Commits« Previous MessageNext Message »
From:Sven Sandberg Date:February 18 2008 12:13pm
Subject:bk commit into 5.1 tree (sven:1.2548) 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-18 13:13:03+01:00, sven@riska.(none) +5 -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.

  mysql-test/suite/binlog/r/binlog_unsafe.result@stripped, 2008-02-18 13:13:02+01:00, sven@riska.(none) +18 -5
    Modified test file needs modified result file.

  mysql-test/suite/binlog/t/binlog_unsafe.test@stripped, 2008-02-18 13:13:02+01:00, sven@riska.(none) +13 -7
    Add tests that using variables is unsafe.

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

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

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

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

  sql/sql_yacc.yy@stripped, 2008-02-18 13:13:02+01:00, sven@riska.(none) +1 -0
    Mark statements that refer to a system variable as "unsafe",
    meaning they will be replicated by row in mixed mode.

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-18 13:13:02 +01:00
@@ -1,13 +1,26 @@
-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;
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t4 (b INT AUTO_INCREMENT PRIMARY KEY);
+CREATE VIEW v1(a, b) AS SELECT a, b FROM t3, t4;
 INSERT INTO t1 SELECT UUID();
 Warnings:
 Warning	1592	Statement is not safe to log in statement format.
+INSERT INTO t1 VALUES (@@global.init_slave);
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+INSERT INTO t1 VALUES (@@hostname);
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+INSERT INTO t2 VALUES (@@global.sync_binlog);
+Warnings:
+Warning	1592	Statement is not safe to log in statement format.
+SET @@last_insert_id = 200000;
+INSERT INTO t2 VALUES (@@last_insert_id);
+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;
+DROP TABLE t1, t2, t3, t4;
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-18 13:13:02 +01:00
@@ -2,17 +2,23 @@
 # executed under statement mode logging.
 
 source include/have_log_bin.inc;
-
-SET BINLOG_FORMAT=STATEMENT;
+source include/have_binlog_format_statement.inc;
 
 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;
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t4 (b INT AUTO_INCREMENT PRIMARY KEY);
+CREATE VIEW v1(a, b) AS SELECT a, b FROM t3, t4;
 
 INSERT INTO t1 SELECT UUID();
-query_vertical SHOW WARNINGS;
 
-DROP TABLE t1,t2,t3;
+INSERT INTO t1 VALUES (@@global.init_slave);
+INSERT INTO t1 VALUES (@@hostname);
+INSERT INTO t2 VALUES (@@global.sync_binlog);
+SET @@last_insert_id = 200000;
+INSERT INTO t2 VALUES (@@last_insert_id);
+
+query_vertical SHOW WARNINGS;
 
+DROP TABLE t1, t2, t3, t4;
 
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-18 13:13:02 +01:00
@@ -0,0 +1,671 @@
+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 ####
+SET @my_default_week_format=  @@global.default_week_format;
+SET @my_init_slave=           @@global.init_slave;
+SET @my_lc_time_names=        @@global.lc_time_names;
+SET @my_low_priority_updates= @@global.low_priority_updates;
+SET @my_relay_log_purge=      @@global.relay_log_purge;
+SET @my_slave_exec_mode=      @@global.slave_exec_mode;
+SET @my_sql_mode=             @@global.sql_mode;
+SET @my_sync_binlog=          @@global.sync_binlog;
+CREATE TABLE ti (id INT AUTO_INCREMENT,
+truth BOOLEAN,
+num INT,
+text VARCHAR(100),
+PRIMARY KEY(id));
+CREATE TABLE tp (id INT AUTO_INCREMENT,
+truth BOOLEAN,
+num INT,
+text VARCHAR(100),
+PRIMARY KEY(id));
+CREATE TABLE tf (id INT AUTO_INCREMENT,
+truth BOOLEAN,
+num INT,
+text VARCHAR(100),
+PRIMARY KEY(id));
+CREATE TABLE tt (id INT AUTO_INCREMENT,
+truth BOOLEAN,
+num INT,
+text VARCHAR(100),
+PRIMARY KEY(id));
+CREATE TABLE trigger_table (text CHAR(4));
+#### Insert variables directly ####
+----  global variables ----
+SET @@global.relay_log_purge = OFF;
+SET @@global.relay_log_purge = ON;
+INSERT INTO ti(truth) VALUES (@@global.relay_log_purge);
+SET @@global.relay_log_purge = OFF;
+INSERT INTO ti(truth) VALUES (@@global.relay_log_purge);
+SET @@global.sync_binlog = 1000000;
+SET @@global.sync_binlog = 2000000;
+INSERT INTO ti(num) VALUES (@@global.sync_binlog);
+SET @@global.sync_binlog = 3000000;
+INSERT INTO ti(num) VALUES (@@global.sync_binlog);
+SET @@global.init_slave = 'ant';
+SET @@global.init_slave = 'bison';
+INSERT INTO ti(text) VALUES (@@global.init_slave);
+SET @@global.init_slave = 'cat';
+INSERT INTO ti(text) VALUES (@@global.init_slave);
+SET @@global.slave_exec_mode = 'STRICT';
+SET @@global.slave_exec_mode = 'IDEMPOTENT';
+INSERT INTO ti(text) VALUES (@@global.slave_exec_mode);
+SET @@global.slave_exec_mode = 'STRICT';
+INSERT INTO ti(text) VALUES (@@global.slave_exec_mode);
+---- session variables ----
+SET @@sql_big_selects = OFF;
+SET @@sql_big_selects = ON;
+INSERT INTO ti(truth) VALUES (@@sql_big_selects);
+SET @@sql_big_selects = OFF;
+INSERT INTO ti(truth) VALUES (@@sql_big_selects);
+SET @@last_insert_id = 10;
+SET @@last_insert_id = 20;
+INSERT INTO ti(num) VALUES (@@last_insert_id);
+SET @@last_insert_id = 30;
+INSERT INTO ti(num) VALUES (@@last_insert_id);
+---- global and session variables ----
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = OFF;
+SET @@global.low_priority_updates = ON;
+SET @@local.low_priority_updates = OFF;
+INSERT INTO ti(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO ti(truth) VALUES (@@local.low_priority_updates);
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = ON;
+INSERT INTO ti(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO ti(truth) VALUES (@@local.low_priority_updates);
+SET @@global.default_week_format = 1;
+SET @@local.default_week_format = 2;
+SET @@global.default_week_format = 3;
+SET @@local.default_week_format = 4;
+INSERT INTO ti(num) VALUES (@@global.default_week_format);
+INSERT INTO ti(num) VALUES (@@local.default_week_format);
+SET @@global.default_week_format = 5;
+SET @@local.default_week_format = 6;
+INSERT INTO ti(num) VALUES (@@global.default_week_format);
+INSERT INTO ti(num) VALUES (@@local.default_week_format);
+SET @@global.lc_time_names = 'zh_HK';
+SET @@local.lc_time_names = 'zh_TW';
+SET @@global.lc_time_names = 'sv_SE';
+SET @@local.lc_time_names = 'sv_FI';
+INSERT INTO ti(text) VALUES (@@global.lc_time_names);
+INSERT INTO ti(text) VALUES (@@local.lc_time_names);
+SET @@global.lc_time_names = 'ar_TN';
+SET @@local.lc_time_names = 'ar_IQ';
+INSERT INTO ti(text) VALUES (@@global.lc_time_names);
+INSERT INTO ti(text) VALUES (@@local.lc_time_names);
+SET @@global.sql_mode = 'ALLOW_INVALID_DATES';
+SET @@local.sql_mode = 'ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE';
+SET @@global.sql_mode = '';
+SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+INSERT INTO ti(text) VALUES (@@global.sql_mode);
+INSERT INTO ti(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 ti(text) VALUES (@@global.sql_mode);
+INSERT INTO ti(text) VALUES (@@local.sql_mode);
+---- user variables ----
+# numeric
+connection slave
+SET @user_num = 10;
+SET @user_num = 20;
+INSERT INTO ti(num) VALUES (@user_num);
+SET @user_num = 30;
+INSERT INTO ti(num) VALUES (@user_num);
+SET @user_text = 'Alunda';
+SET @user_text = 'Bergsbrunna';
+INSERT INTO ti(text) VALUES (@user_text);
+SET @user_text = 'Centrum';
+INSERT INTO ti(text) VALUES (@user_text);
+#### Insert variables from a stored procedure ####
+CREATE PROCEDURE proc()
+BEGIN
+# GLOBAL
+# boolean
+SET @@global.relay_log_purge = ON;
+INSERT INTO tp(truth) VALUES (@@global.relay_log_purge);
+SET @@global.relay_log_purge = OFF;
+INSERT INTO tp(truth) VALUES (@@global.relay_log_purge);
+# numeric
+SET @@global.sync_binlog = 2000000;
+INSERT INTO tp(num) VALUES (@@global.sync_binlog);
+SET @@global.sync_binlog = 3000000;
+INSERT INTO tp(num) VALUES (@@global.sync_binlog);
+# string
+SET @@global.init_slave = 'bison';
+INSERT INTO tp(text) VALUES (@@global.init_slave);
+SET @@global.init_slave = 'cat';
+INSERT INTO tp(text) VALUES (@@global.init_slave);
+# enumeration
+SET @@global.slave_exec_mode = 'IDEMPOTENT';
+INSERT INTO tp(text) VALUES (@@global.slave_exec_mode);
+SET @@global.slave_exec_mode = 'STRICT';
+INSERT INTO tp(text) VALUES (@@global.slave_exec_mode);
+# SESSION
+# boolean
+SET @@sql_big_selects = ON;
+INSERT INTO tp(truth) VALUES (@@sql_big_selects);
+SET @@sql_big_selects = OFF;
+INSERT INTO tp(truth) VALUES (@@sql_big_selects);
+# numeric
+SET @@last_insert_id = 20;
+INSERT INTO tp(num) VALUES (@@last_insert_id);
+SET @@last_insert_id = 30;
+INSERT INTO tp(num) VALUES (@@last_insert_id);
+# BOTH
+# boolean
+SET @@global.low_priority_updates = ON;
+SET @@local.low_priority_updates = OFF;
+INSERT INTO tp(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tp(truth) VALUES (@@local.low_priority_updates);
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = ON;
+INSERT INTO tp(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tp(truth) VALUES (@@local.low_priority_updates);
+# numeric
+SET @@global.default_week_format = 3;
+SET @@local.default_week_format = 4;
+INSERT INTO tp(num) VALUES (@@global.default_week_format);
+INSERT INTO tp(num) VALUES (@@local.default_week_format);
+SET @@global.default_week_format = 5;
+SET @@local.default_week_format = 6;
+INSERT INTO tp(num) VALUES (@@global.default_week_format);
+INSERT INTO tp(num) VALUES (@@local.default_week_format);
+# text
+SET @@global.lc_time_names = 'sv_SE';
+SET @@local.lc_time_names = 'sv_FI';
+INSERT INTO tp(text) VALUES (@@global.lc_time_names);
+INSERT INTO tp(text) VALUES (@@local.lc_time_names);
+SET @@global.lc_time_names = 'ar_TN';
+SET @@local.lc_time_names = 'ar_IQ';
+INSERT INTO tp(text) VALUES (@@global.lc_time_names);
+INSERT INTO tp(text) VALUES (@@local.lc_time_names);
+# enum
+SET @@global.sql_mode = '';
+SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+INSERT INTO tp(text) VALUES (@@global.sql_mode);
+INSERT INTO tp(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 tp(text) VALUES (@@global.sql_mode);
+INSERT INTO tp(text) VALUES (@@local.sql_mode);
+# USER
+# numeric
+SET @user_num = 20;
+INSERT INTO tp(num) VALUES (@user_num);
+SET @user_num = 30;
+INSERT INTO tp(num) VALUES (@user_num);
+# string
+SET @user_text = 'Bergsbrunna';
+INSERT INTO tp(text) VALUES (@user_text);
+SET @user_text = 'Centrum';
+INSERT INTO tp(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 tf(truth) VALUES (@@global.relay_log_purge);
+SET @@global.relay_log_purge = OFF;
+INSERT INTO tf(truth) VALUES (@@global.relay_log_purge);
+# numeric
+SET @@global.sync_binlog = 2000000;
+INSERT INTO tf(num) VALUES (@@global.sync_binlog);
+SET @@global.sync_binlog = 3000000;
+INSERT INTO tf(num) VALUES (@@global.sync_binlog);
+# string
+SET @@global.init_slave = 'bison';
+INSERT INTO tf(text) VALUES (@@global.init_slave);
+SET @@global.init_slave = 'cat';
+INSERT INTO tf(text) VALUES (@@global.init_slave);
+# enumeration
+SET @@global.slave_exec_mode = 'IDEMPOTENT';
+INSERT INTO tf(text) VALUES (@@global.slave_exec_mode);
+SET @@global.slave_exec_mode = 'STRICT';
+INSERT INTO tf(text) VALUES (@@global.slave_exec_mode);
+# SESSION
+# boolean
+SET @@sql_big_selects = ON;
+INSERT INTO tf(truth) VALUES (@@sql_big_selects);
+SET @@sql_big_selects = OFF;
+INSERT INTO tf(truth) VALUES (@@sql_big_selects);
+# numeric
+SET @@last_insert_id = 20;
+INSERT INTO tf(num) VALUES (@@last_insert_id);
+SET @@last_insert_id = 30;
+INSERT INTO tf(num) VALUES (@@last_insert_id);
+# BOTH
+# boolean
+SET @@global.low_priority_updates = ON;
+SET @@local.low_priority_updates = OFF;
+INSERT INTO tf(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tf(truth) VALUES (@@local.low_priority_updates);
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = ON;
+INSERT INTO tf(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tf(truth) VALUES (@@local.low_priority_updates);
+# numeric
+SET @@global.default_week_format = 3;
+SET @@local.default_week_format = 4;
+INSERT INTO tf(num) VALUES (@@global.default_week_format);
+INSERT INTO tf(num) VALUES (@@local.default_week_format);
+SET @@global.default_week_format = 5;
+SET @@local.default_week_format = 6;
+INSERT INTO tf(num) VALUES (@@global.default_week_format);
+INSERT INTO tf(num) VALUES (@@local.default_week_format);
+# text
+SET @@global.lc_time_names = 'sv_SE';
+SET @@local.lc_time_names = 'sv_FI';
+INSERT INTO tf(text) VALUES (@@global.lc_time_names);
+INSERT INTO tf(text) VALUES (@@local.lc_time_names);
+SET @@global.lc_time_names = 'ar_TN';
+SET @@local.lc_time_names = 'ar_IQ';
+INSERT INTO tf(text) VALUES (@@global.lc_time_names);
+INSERT INTO tf(text) VALUES (@@local.lc_time_names);
+# enum
+SET @@global.sql_mode = '';
+SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+INSERT INTO tf(text) VALUES (@@global.sql_mode);
+INSERT INTO tf(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 tf(text) VALUES (@@global.sql_mode);
+INSERT INTO tf(text) VALUES (@@local.sql_mode);
+# USER
+# numeric
+SET @user_num = 20;
+INSERT INTO tf(num) VALUES (@user_num);
+SET @user_num = 30;
+INSERT INTO tf(num) VALUES (@user_num);
+# string
+SET @user_text = 'Bergsbrunna';
+INSERT INTO tf(text) VALUES (@user_text);
+SET @user_text = 'Centrum';
+INSERT INTO tf(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 tt(truth) VALUES (@@global.relay_log_purge);
+SET @@global.relay_log_purge = OFF;
+INSERT INTO tt(truth) VALUES (@@global.relay_log_purge);
+# numeric
+SET @@global.sync_binlog = 2000000;
+INSERT INTO tt(num) VALUES (@@global.sync_binlog);
+SET @@global.sync_binlog = 3000000;
+INSERT INTO tt(num) VALUES (@@global.sync_binlog);
+# string
+SET @@global.init_slave = 'bison';
+INSERT INTO tt(text) VALUES (@@global.init_slave);
+SET @@global.init_slave = 'cat';
+INSERT INTO tt(text) VALUES (@@global.init_slave);
+# enumeration
+SET @@global.slave_exec_mode = 'IDEMPOTENT';
+INSERT INTO tt(text) VALUES (@@global.slave_exec_mode);
+SET @@global.slave_exec_mode = 'STRICT';
+INSERT INTO tt(text) VALUES (@@global.slave_exec_mode);
+# SESSION
+# boolean
+SET @@sql_big_selects = ON;
+INSERT INTO tt(truth) VALUES (@@sql_big_selects);
+SET @@sql_big_selects = OFF;
+INSERT INTO tt(truth) VALUES (@@sql_big_selects);
+# numeric
+SET @@last_insert_id = 20;
+INSERT INTO tt(num) VALUES (@@last_insert_id);
+SET @@last_insert_id = 30;
+INSERT INTO tt(num) VALUES (@@last_insert_id);
+# BOTH
+# boolean
+SET @@global.low_priority_updates = ON;
+SET @@local.low_priority_updates = OFF;
+INSERT INTO tt(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tt(truth) VALUES (@@local.low_priority_updates);
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = ON;
+INSERT INTO tt(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO tt(truth) VALUES (@@local.low_priority_updates);
+# numeric
+SET @@global.default_week_format = 3;
+SET @@local.default_week_format = 4;
+INSERT INTO tt(num) VALUES (@@global.default_week_format);
+INSERT INTO tt(num) VALUES (@@local.default_week_format);
+SET @@global.default_week_format = 5;
+SET @@local.default_week_format = 6;
+INSERT INTO tt(num) VALUES (@@global.default_week_format);
+INSERT INTO tt(num) VALUES (@@local.default_week_format);
+# text
+SET @@global.lc_time_names = 'sv_SE';
+SET @@local.lc_time_names = 'sv_FI';
+INSERT INTO tt(text) VALUES (@@global.lc_time_names);
+INSERT INTO tt(text) VALUES (@@local.lc_time_names);
+SET @@global.lc_time_names = 'ar_TN';
+SET @@local.lc_time_names = 'ar_IQ';
+INSERT INTO tt(text) VALUES (@@global.lc_time_names);
+INSERT INTO tt(text) VALUES (@@local.lc_time_names);
+# enum
+SET @@global.sql_mode = '';
+SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+INSERT INTO tt(text) VALUES (@@global.sql_mode);
+INSERT INTO tt(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 tt(text) VALUES (@@global.sql_mode);
+INSERT INTO tt(text) VALUES (@@local.sql_mode);
+# USER
+# numeric
+SET @user_num = 20;
+INSERT INTO tt(num) VALUES (@user_num);
+SET @user_num = 30;
+INSERT INTO tt(num) VALUES (@user_num);
+# string
+SET @user_text = 'Bergsbrunna';
+INSERT INTO tt(text) VALUES (@user_text);
+SET @user_text = 'Centrum';
+INSERT INTO tt(text) VALUES (@user_text);
+END|
+INSERT INTO trigger_table VALUES ('bye.');
+#### Results on master ####
+SELECT * FROM ti 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
+SELECT * FROM tp 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
+SELECT * FROM tf 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
+SELECT * FROM tt 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
+#### Results on slave ####
+SELECT * FROM ti 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
+SELECT * FROM tp 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
+SELECT * FROM tf 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
+SELECT * FROM tt 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
+#### Clean up ####
+DROP PROCEDURE proc;
+DROP FUNCTION func;
+DROP TRIGGER trig;
+DROP TABLE ti, tp, tf, tt, trigger_table;
+SET @@global.default_week_format=  @my_default_week_format;
+SET @@global.init_slave=           @my_init_slave;
+SET @@global.lc_time_names=        @my_lc_time_names;
+SET @@global.low_priority_updates= @my_low_priority_updates;
+SET @@global.relay_log_purge=      @my_relay_log_purge;
+SET @@global.slave_exec_mode=      @my_slave_exec_mode;
+SET @@global.sql_mode=             @my_sql_mode;
+SET @@global.sync_binlog=          @my_sync_binlog;
diff -Nrup a/mysql-test/suite/rpl/t/rpl_variables.test b/mysql-test/suite/rpl/t/rpl_variables.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/suite/rpl/t/rpl_variables.test	2008-02-18 13:13:02 +01:00
@@ -0,0 +1,584 @@
+# Test that queries referencing variables are replicated correctly.
+# 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
+#
+# 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.
+#
+# See BUG#31168: @@hostname does not replicate
+
+source include/master-slave.inc;
+source include/have_binlog_format_mixed_or_row.inc;
+
+
+--echo #### Initialization ####
+
+connection master;
+
+# Backup the values of global variables so that they can be restored
+# later.
+SET @my_default_week_format=  @@global.default_week_format;
+SET @my_init_slave=           @@global.init_slave;
+SET @my_lc_time_names=        @@global.lc_time_names;
+SET @my_low_priority_updates= @@global.low_priority_updates;
+SET @my_relay_log_purge=      @@global.relay_log_purge;
+SET @my_slave_exec_mode=      @@global.slave_exec_mode;
+SET @my_sql_mode=             @@global.sql_mode;
+SET @my_sync_binlog=          @@global.sync_binlog;
+
+# Tables where everything happens.
+CREATE TABLE ti (id INT AUTO_INCREMENT,
+                 truth BOOLEAN,
+                 num INT,
+                 text VARCHAR(100),
+                 PRIMARY KEY(id));
+CREATE TABLE tp (id INT AUTO_INCREMENT,
+                 truth BOOLEAN,
+                 num INT,
+                 text VARCHAR(100),
+                 PRIMARY KEY(id));
+CREATE TABLE tf (id INT AUTO_INCREMENT,
+                 truth BOOLEAN,
+                 num INT,
+                 text VARCHAR(100),
+                 PRIMARY KEY(id));
+CREATE TABLE tt (id INT AUTO_INCREMENT,
+                 truth BOOLEAN,
+                 num INT,
+                 text VARCHAR(100),
+                 PRIMARY KEY(id));
+
+# Table on which we put a trigger.
+CREATE TABLE trigger_table (text CHAR(4));
+
+
+--echo #### Insert variables directly ####
+
+--echo ----  global variables ----
+
+# boolean
+connection slave;
+SET @@global.relay_log_purge = OFF;
+connection master;
+SET @@global.relay_log_purge = ON;
+INSERT INTO ti(truth) VALUES (@@global.relay_log_purge);
+SET @@global.relay_log_purge = OFF;
+INSERT INTO ti(truth) VALUES (@@global.relay_log_purge);
+
+# numeric
+connection slave;
+SET @@global.sync_binlog = 1000000;
+connection master;
+SET @@global.sync_binlog = 2000000;
+INSERT INTO ti(num) VALUES (@@global.sync_binlog);
+SET @@global.sync_binlog = 3000000;
+INSERT INTO ti(num) VALUES (@@global.sync_binlog);
+
+# string
+connection slave;
+SET @@global.init_slave = 'ant';
+connection master;
+SET @@global.init_slave = 'bison';
+INSERT INTO ti(text) VALUES (@@global.init_slave);
+SET @@global.init_slave = 'cat';
+INSERT INTO ti(text) VALUES (@@global.init_slave);
+
+# enumeration
+connection slave;
+SET @@global.slave_exec_mode = 'STRICT';
+connection master;
+SET @@global.slave_exec_mode = 'IDEMPOTENT';
+INSERT INTO ti(text) VALUES (@@global.slave_exec_mode);
+SET @@global.slave_exec_mode = 'STRICT';
+INSERT INTO ti(text) VALUES (@@global.slave_exec_mode);
+
+
+--echo ---- session variables ----
+
+# boolean
+connection slave;
+SET @@sql_big_selects = OFF;
+connection master;
+SET @@sql_big_selects = ON;
+INSERT INTO ti(truth) VALUES (@@sql_big_selects);
+SET @@sql_big_selects = OFF;
+INSERT INTO ti(truth) VALUES (@@sql_big_selects);
+
+# numeric
+connection slave;
+SET @@last_insert_id = 10;
+connection master;
+SET @@last_insert_id = 20;
+INSERT INTO ti(num) VALUES (@@last_insert_id);
+SET @@last_insert_id = 30;
+INSERT INTO ti(num) VALUES (@@last_insert_id);
+
+--echo ---- global and session variables ----
+
+# boolean
+connection slave;
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = OFF;
+connection master;
+SET @@global.low_priority_updates = ON;
+SET @@local.low_priority_updates = OFF;
+INSERT INTO ti(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO ti(truth) VALUES (@@local.low_priority_updates);
+SET @@global.low_priority_updates = OFF;
+SET @@local.low_priority_updates = ON;
+INSERT INTO ti(truth) VALUES (@@global.low_priority_updates);
+INSERT INTO ti(truth) VALUES (@@local.low_priority_updates);
+
+# numeric
+connection slave;
+SET @@global.default_week_format = 1;
+SET @@local.default_week_format = 2;
+connection master;
+SET @@global.default_week_format = 3;
+SET @@local.default_week_format = 4;
+INSERT INTO ti(num) VALUES (@@global.default_week_format);
+INSERT INTO ti(num) VALUES (@@local.default_week_format);
+SET @@global.default_week_format = 5;
+SET @@local.default_week_format = 6;
+INSERT INTO ti(num) VALUES (@@global.default_week_format);
+INSERT INTO ti(num) VALUES (@@local.default_week_format);
+
+# string
+connection slave;
+SET @@global.lc_time_names = 'zh_HK';
+SET @@local.lc_time_names = 'zh_TW';
+connection master;
+SET @@global.lc_time_names = 'sv_SE';
+SET @@local.lc_time_names = 'sv_FI';
+INSERT INTO ti(text) VALUES (@@global.lc_time_names);
+INSERT INTO ti(text) VALUES (@@local.lc_time_names);
+SET @@global.lc_time_names = 'ar_TN';
+SET @@local.lc_time_names = 'ar_IQ';
+INSERT INTO ti(text) VALUES (@@global.lc_time_names);
+INSERT INTO ti(text) VALUES (@@local.lc_time_names);
+
+# enum
+connection slave;
+SET @@global.sql_mode = 'ALLOW_INVALID_DATES';
+SET @@local.sql_mode = 'ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE';
+connection master;
+SET @@global.sql_mode = '';
+SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+INSERT INTO ti(text) VALUES (@@global.sql_mode);
+INSERT INTO ti(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 ti(text) VALUES (@@global.sql_mode);
+INSERT INTO ti(text) VALUES (@@local.sql_mode);
+
+echo ---- user variables ----
+
+# numeric
+connection slave;
+SET @user_num = 10;
+connection master;
+SET @user_num = 20;
+INSERT INTO ti(num) VALUES (@user_num);
+SET @user_num = 30;
+INSERT INTO ti(num) VALUES (@user_num);
+
+# string
+connection slave;
+SET @user_text = 'Alunda';
+connection master;
+SET @user_text = 'Bergsbrunna';
+INSERT INTO ti(text) VALUES (@user_text);
+SET @user_text = 'Centrum';
+INSERT INTO ti(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 tp(truth) VALUES (@@global.relay_log_purge);
+  SET @@global.relay_log_purge = OFF;
+  INSERT INTO tp(truth) VALUES (@@global.relay_log_purge);
+
+  # numeric
+  SET @@global.sync_binlog = 2000000;
+  INSERT INTO tp(num) VALUES (@@global.sync_binlog);
+  SET @@global.sync_binlog = 3000000;
+  INSERT INTO tp(num) VALUES (@@global.sync_binlog);
+
+  # string
+  SET @@global.init_slave = 'bison';
+  INSERT INTO tp(text) VALUES (@@global.init_slave);
+  SET @@global.init_slave = 'cat';
+  INSERT INTO tp(text) VALUES (@@global.init_slave);
+
+  # enumeration
+  SET @@global.slave_exec_mode = 'IDEMPOTENT';
+  INSERT INTO tp(text) VALUES (@@global.slave_exec_mode);
+  SET @@global.slave_exec_mode = 'STRICT';
+  INSERT INTO tp(text) VALUES (@@global.slave_exec_mode);
+
+  # SESSION
+
+  # boolean
+  SET @@sql_big_selects = ON;
+  INSERT INTO tp(truth) VALUES (@@sql_big_selects);
+  SET @@sql_big_selects = OFF;
+  INSERT INTO tp(truth) VALUES (@@sql_big_selects);
+
+  # numeric
+  SET @@last_insert_id = 20;
+  INSERT INTO tp(num) VALUES (@@last_insert_id);
+  SET @@last_insert_id = 30;
+  INSERT INTO tp(num) VALUES (@@last_insert_id);
+
+  # BOTH
+
+  # boolean
+  SET @@global.low_priority_updates = ON;
+  SET @@local.low_priority_updates = OFF;
+  INSERT INTO tp(truth) VALUES (@@global.low_priority_updates);
+  INSERT INTO tp(truth) VALUES (@@local.low_priority_updates);
+  SET @@global.low_priority_updates = OFF;
+  SET @@local.low_priority_updates = ON;
+  INSERT INTO tp(truth) VALUES (@@global.low_priority_updates);
+  INSERT INTO tp(truth) VALUES (@@local.low_priority_updates);
+
+  # numeric
+  SET @@global.default_week_format = 3;
+  SET @@local.default_week_format = 4;
+  INSERT INTO tp(num) VALUES (@@global.default_week_format);
+  INSERT INTO tp(num) VALUES (@@local.default_week_format);
+  SET @@global.default_week_format = 5;
+  SET @@local.default_week_format = 6;
+  INSERT INTO tp(num) VALUES (@@global.default_week_format);
+  INSERT INTO tp(num) VALUES (@@local.default_week_format);
+
+  # text
+  SET @@global.lc_time_names = 'sv_SE';
+  SET @@local.lc_time_names = 'sv_FI';
+  INSERT INTO tp(text) VALUES (@@global.lc_time_names);
+  INSERT INTO tp(text) VALUES (@@local.lc_time_names);
+  SET @@global.lc_time_names = 'ar_TN';
+  SET @@local.lc_time_names = 'ar_IQ';
+  INSERT INTO tp(text) VALUES (@@global.lc_time_names);
+  INSERT INTO tp(text) VALUES (@@local.lc_time_names);
+
+  # enum
+  SET @@global.sql_mode = '';
+  SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+  INSERT INTO tp(text) VALUES (@@global.sql_mode);
+  INSERT INTO tp(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 tp(text) VALUES (@@global.sql_mode);
+  INSERT INTO tp(text) VALUES (@@local.sql_mode);
+
+  # USER
+
+  # numeric
+  SET @user_num = 20;
+  INSERT INTO tp(num) VALUES (@user_num);
+  SET @user_num = 30;
+  INSERT INTO tp(num) VALUES (@user_num);
+
+  # string
+  SET @user_text = 'Bergsbrunna';
+  INSERT INTO tp(text) VALUES (@user_text);
+  SET @user_text = 'Centrum';
+  INSERT INTO tp(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 tf(truth) VALUES (@@global.relay_log_purge);
+  SET @@global.relay_log_purge = OFF;
+  INSERT INTO tf(truth) VALUES (@@global.relay_log_purge);
+
+  # numeric
+  SET @@global.sync_binlog = 2000000;
+  INSERT INTO tf(num) VALUES (@@global.sync_binlog);
+  SET @@global.sync_binlog = 3000000;
+  INSERT INTO tf(num) VALUES (@@global.sync_binlog);
+
+  # string
+  SET @@global.init_slave = 'bison';
+  INSERT INTO tf(text) VALUES (@@global.init_slave);
+  SET @@global.init_slave = 'cat';
+  INSERT INTO tf(text) VALUES (@@global.init_slave);
+
+  # enumeration
+  SET @@global.slave_exec_mode = 'IDEMPOTENT';
+  INSERT INTO tf(text) VALUES (@@global.slave_exec_mode);
+  SET @@global.slave_exec_mode = 'STRICT';
+  INSERT INTO tf(text) VALUES (@@global.slave_exec_mode);
+
+  # SESSION
+
+  # boolean
+  SET @@sql_big_selects = ON;
+  INSERT INTO tf(truth) VALUES (@@sql_big_selects);
+  SET @@sql_big_selects = OFF;
+  INSERT INTO tf(truth) VALUES (@@sql_big_selects);
+
+  # numeric
+  SET @@last_insert_id = 20;
+  INSERT INTO tf(num) VALUES (@@last_insert_id);
+  SET @@last_insert_id = 30;
+  INSERT INTO tf(num) VALUES (@@last_insert_id);
+
+  # BOTH
+
+  # boolean
+  SET @@global.low_priority_updates = ON;
+  SET @@local.low_priority_updates = OFF;
+  INSERT INTO tf(truth) VALUES (@@global.low_priority_updates);
+  INSERT INTO tf(truth) VALUES (@@local.low_priority_updates);
+  SET @@global.low_priority_updates = OFF;
+  SET @@local.low_priority_updates = ON;
+  INSERT INTO tf(truth) VALUES (@@global.low_priority_updates);
+  INSERT INTO tf(truth) VALUES (@@local.low_priority_updates);
+
+  # numeric
+  SET @@global.default_week_format = 3;
+  SET @@local.default_week_format = 4;
+  INSERT INTO tf(num) VALUES (@@global.default_week_format);
+  INSERT INTO tf(num) VALUES (@@local.default_week_format);
+  SET @@global.default_week_format = 5;
+  SET @@local.default_week_format = 6;
+  INSERT INTO tf(num) VALUES (@@global.default_week_format);
+  INSERT INTO tf(num) VALUES (@@local.default_week_format);
+
+  # text
+  SET @@global.lc_time_names = 'sv_SE';
+  SET @@local.lc_time_names = 'sv_FI';
+  INSERT INTO tf(text) VALUES (@@global.lc_time_names);
+  INSERT INTO tf(text) VALUES (@@local.lc_time_names);
+  SET @@global.lc_time_names = 'ar_TN';
+  SET @@local.lc_time_names = 'ar_IQ';
+  INSERT INTO tf(text) VALUES (@@global.lc_time_names);
+  INSERT INTO tf(text) VALUES (@@local.lc_time_names);
+
+  # enum
+  SET @@global.sql_mode = '';
+  SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+  INSERT INTO tf(text) VALUES (@@global.sql_mode);
+  INSERT INTO tf(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 tf(text) VALUES (@@global.sql_mode);
+  INSERT INTO tf(text) VALUES (@@local.sql_mode);
+
+  # USER
+
+  # numeric
+  SET @user_num = 20;
+  INSERT INTO tf(num) VALUES (@user_num);
+  SET @user_num = 30;
+  INSERT INTO tf(num) VALUES (@user_num);
+
+  # string
+  SET @user_text = 'Bergsbrunna';
+  INSERT INTO tf(text) VALUES (@user_text);
+  SET @user_text = 'Centrum';
+  INSERT INTO tf(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 tt(truth) VALUES (@@global.relay_log_purge);
+  SET @@global.relay_log_purge = OFF;
+  INSERT INTO tt(truth) VALUES (@@global.relay_log_purge);
+
+  # numeric
+  SET @@global.sync_binlog = 2000000;
+  INSERT INTO tt(num) VALUES (@@global.sync_binlog);
+  SET @@global.sync_binlog = 3000000;
+  INSERT INTO tt(num) VALUES (@@global.sync_binlog);
+
+  # string
+  SET @@global.init_slave = 'bison';
+  INSERT INTO tt(text) VALUES (@@global.init_slave);
+  SET @@global.init_slave = 'cat';
+  INSERT INTO tt(text) VALUES (@@global.init_slave);
+
+  # enumeration
+  SET @@global.slave_exec_mode = 'IDEMPOTENT';
+  INSERT INTO tt(text) VALUES (@@global.slave_exec_mode);
+  SET @@global.slave_exec_mode = 'STRICT';
+  INSERT INTO tt(text) VALUES (@@global.slave_exec_mode);
+
+  # SESSION
+
+  # boolean
+  SET @@sql_big_selects = ON;
+  INSERT INTO tt(truth) VALUES (@@sql_big_selects);
+  SET @@sql_big_selects = OFF;
+  INSERT INTO tt(truth) VALUES (@@sql_big_selects);
+
+  # numeric
+  SET @@last_insert_id = 20;
+  INSERT INTO tt(num) VALUES (@@last_insert_id);
+  SET @@last_insert_id = 30;
+  INSERT INTO tt(num) VALUES (@@last_insert_id);
+
+  # BOTH
+
+  # boolean
+  SET @@global.low_priority_updates = ON;
+  SET @@local.low_priority_updates = OFF;
+  INSERT INTO tt(truth) VALUES (@@global.low_priority_updates);
+  INSERT INTO tt(truth) VALUES (@@local.low_priority_updates);
+  SET @@global.low_priority_updates = OFF;
+  SET @@local.low_priority_updates = ON;
+  INSERT INTO tt(truth) VALUES (@@global.low_priority_updates);
+  INSERT INTO tt(truth) VALUES (@@local.low_priority_updates);
+
+  # numeric
+  SET @@global.default_week_format = 3;
+  SET @@local.default_week_format = 4;
+  INSERT INTO tt(num) VALUES (@@global.default_week_format);
+  INSERT INTO tt(num) VALUES (@@local.default_week_format);
+  SET @@global.default_week_format = 5;
+  SET @@local.default_week_format = 6;
+  INSERT INTO tt(num) VALUES (@@global.default_week_format);
+  INSERT INTO tt(num) VALUES (@@local.default_week_format);
+
+  # text
+  SET @@global.lc_time_names = 'sv_SE';
+  SET @@local.lc_time_names = 'sv_FI';
+  INSERT INTO tt(text) VALUES (@@global.lc_time_names);
+  INSERT INTO tt(text) VALUES (@@local.lc_time_names);
+  SET @@global.lc_time_names = 'ar_TN';
+  SET @@local.lc_time_names = 'ar_IQ';
+  INSERT INTO tt(text) VALUES (@@global.lc_time_names);
+  INSERT INTO tt(text) VALUES (@@local.lc_time_names);
+
+  # enum
+  SET @@global.sql_mode = '';
+  SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER';
+  INSERT INTO tt(text) VALUES (@@global.sql_mode);
+  INSERT INTO tt(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 tt(text) VALUES (@@global.sql_mode);
+  INSERT INTO tt(text) VALUES (@@local.sql_mode);
+
+  # USER
+
+  # numeric
+  SET @user_num = 20;
+  INSERT INTO tt(num) VALUES (@user_num);
+  SET @user_num = 30;
+  INSERT INTO tt(num) VALUES (@user_num);
+
+  # string
+  SET @user_text = 'Bergsbrunna';
+  INSERT INTO tt(text) VALUES (@user_text);
+  SET @user_text = 'Centrum';
+  INSERT INTO tt(text) VALUES (@user_text);
+END|
+DELIMITER ;|
+
+INSERT INTO trigger_table VALUES ('bye.');
+
+
+--echo #### Results on master ####
+
+SELECT * FROM ti ORDER BY id;
+SELECT * FROM tp ORDER BY id;
+SELECT * FROM tf ORDER BY id;
+SELECT * FROM tt ORDER BY id;
+
+
+--echo #### Results on slave ####
+
+sync_slave_with_master;
+SELECT * FROM ti ORDER BY id;
+SELECT * FROM tp ORDER BY id;
+SELECT * FROM tf ORDER BY id;
+SELECT * FROM tt ORDER BY id;
+
+
+--echo #### Clean up ####
+
+connection master;
+DROP PROCEDURE proc;
+DROP FUNCTION func;
+DROP TRIGGER trig;
+DROP TABLE ti, tp, tf, tt, trigger_table;
+
+SET @@global.default_week_format=  @my_default_week_format;
+SET @@global.init_slave=           @my_init_slave;
+SET @@global.lc_time_names=        @my_lc_time_names;
+SET @@global.low_priority_updates= @my_low_priority_updates;
+SET @@global.relay_log_purge=      @my_relay_log_purge;
+SET @@global.slave_exec_mode=      @my_slave_exec_mode;
+SET @@global.sql_mode=             @my_sql_mode;
+SET @@global.sync_binlog=          @my_sync_binlog;
+
+sync_slave_with_master;
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2008-01-25 18:15:00 +01:00
+++ b/sql/sql_yacc.yy	2008-02-18 13:13:02 +01:00
@@ -7396,6 +7396,7 @@ variable_aux:
             }
             if (!($$= get_system_var(YYTHD, $2, $3, $4)))
               MYSQL_YYABORT;
+            Lex->set_stmt_unsafe();
           }
         ;
 
Thread
bk commit into 5.1 tree (sven:1.2548) BUG#31168Sven Sandberg18 Feb