Hi Sven!
I have some comments on the tests below. I also noticed that you have
submitted a new result file and test file for rpl_variables, so I won't
comment on the things I saw in that one.
Just my few cents,
Mats Kindahl
Sven Sandberg wrote:
> 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-15 11:16:48+00: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-15 11:16:46+00:00,
> sven@riska.(none) +18 -5
> Modified test file needs modified result file.
>
> mysql-test/suite/binlog/t/binlog_unsafe.test@stripped, 2008-02-15 11:16:46+00:00,
> sven@riska.(none) +13 -7
> Add tests that using variables is unsafe.
>
> mysql-test/suite/rpl/r/rpl_variables.result@stripped, 2008-02-15 11:16:47+00:00,
> sven@riska.(none) +676 -0
> New test case needs new result file.
>
> mysql-test/suite/rpl/r/rpl_variables.result@stripped, 2008-02-15 11:16:47+00:00,
> sven@riska.(none) +0 -0
>
> mysql-test/suite/rpl/t/rpl_variables.test@stripped, 2008-02-15 11:16:47+00:00,
> sven@riska.(none) +585 -0
> Test that INSERT of @@variables is replicated correctly.
>
> mysql-test/suite/rpl/t/rpl_variables.test@stripped, 2008-02-15 11:16:47+00:00,
> sven@riska.(none) +0 -0
>
> sql/sql_yacc.yy@stripped, 2008-02-15 11:16:47+00: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 12:27:13 +00:00
> +++ b/mysql-test/suite/binlog/r/binlog_unsafe.result 2008-02-15 11:16:46 +00: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;
>
Changing names of tables like this just invites merge trouble. You could
have sticked with t2 and just added new tables with the definitions that
were missing.
> 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 12:27:03 +00:00
> +++ b/mysql-test/suite/binlog/t/binlog_unsafe.test 2008-02-15 11:16:46 +00: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;
>
See above...
>
> 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;
>
I don't think the SHOW WARNINGS is needed since the warnings are printed
inline.
Could you please add all variables that you have tests for below, even
the ones that don't generate a warning since they are replication safe.
It is good to see in the test if that changes, since it could be an
indication of trouble.
Could you also add a trigger, a stored function, and a procedure
containing a variable that should generate a warning and demonstrate
that it does, unless there already are such things in the test (I don't
see any).
>
> +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-15 11:16:47 +00:00
> @@ -0,0 +1,676 @@
> +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';
> +ItNSERT 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;
>
Could you please make this a vertical display. A single change to a row
can be hard to see what it is if the display is horizontal.
> +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
> +#### Verify that we get a warning in statement mode ####
> +SET @@binlog_format = 'STATEMENT';
> +INSERT INTO ti(num) VALUES (@@global.sync_binlog);
> +Warnings:
> +Warning 1592 Statement is not safe to log in statement format.
> +#### 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-15 11:16:47 +00:00
> @@ -0,0 +1,585 @@
> +# 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;
> +source extra/rpl_binlog_variables.test;
> +
> +
> +--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 17:15:00 +00:00
> +++ b/sql/sql_yacc.yy 2008-02-15 11:16:47 +00:00
> @@ -7396,6 +7396,7 @@ variable_aux:
> }
> if (!($$= get_system_var(YYTHD, $2, $3, $4)))
> MYSQL_YYABORT;
> + Lex->set_stmt_unsafe();
> }
> ;
>
>
>
--
Mats Kindahl
Lead Software Developer
Replication Team
MySQL AB, www.mysql.com
