Hi Libing,
Well done, very nice solution, patch approved!
Li-Bing.Song@stripped wrote:
> #At file:///home/anders/Work/bzrwork/wt1/mysql-5.1-bugteam/ based on
> revid:anitha.gopi@stripped
>
> 3548 Li-Bing.Song@stripped 2010-11-25
> BUG#54903 BINLOG statement toggles session variables
>
> When using BINLOG statement to execute rows log events, session variables
> foreign_key_checks and unique_checks are changed temporarily. As each rows
> log event has their own special session environment and its own
> foreign_key_checks and unique_checks can be different from current session
> which executing the BINLOG statement. But these variables are not restored
> correctly after BINLOG statement. This problem will cause that the following
> statements fail or generate unexpected data.
>
> In this patch, code is added to backup and restore these two variables.
> So BINLOG statement will not affect current session's variables again.
>
> modified:
> mysql-test/extra/binlog_tests/binlog.test
> mysql-test/suite/binlog/r/binlog_row_binlog.result
> sql/sql_binlog.cc
> === modified file 'mysql-test/extra/binlog_tests/binlog.test'
> --- a/mysql-test/extra/binlog_tests/binlog.test 2010-05-24 13:54:08 +0000
> +++ b/mysql-test/extra/binlog_tests/binlog.test 2010-11-25 08:34:33 +0000
> @@ -302,5 +302,58 @@ BINLOG '
> SHOW BINLOG EVENTS;
>
> DROP TABLE t1;
> +
> +--echo
> +--echo # BUG#54903 BINLOG statement toggles session variables
> +--echo # ----------------------------------------------------------------------
> +--echo # This test verify that BINLOG statement doesn't change current session's
> +--echo # variables foreign_key_checks and unique_checks.
> +--echo
> +CREATE TABLE t1 (c1 INT KEY);
> +
> +SET @@SESSION.foreign_key_checks= ON;
> +SET @@SESSION.unique_checks= ON;
> +
> +--echo # INSERT INTO t1 VALUES (1)
> +--echo # foreign_key_checks=0 and unique_checks=0
> +BINLOG '
> +dfLtTBMBAAAAKQAAANcAAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
> +dfLtTBcBAAAAIgAAAPkAAAAAABcAAAAAAAcAAf/+AQAAAA==
> +';
> +
> +SELECT * FROM t1;
> +--echo # Their values should be ON
> +SHOW SESSION VARIABLES LIKE "%_checks";
> +
> +--echo
> +SET @@SESSION.foreign_key_checks= OFF;
> +SET @@SESSION.unique_checks= OFF;
> +
> +--echo # INSERT INTO t1 VALUES(2)
> +--echo # foreign_key_checks=1 and unique_checks=1
> +BINLOG '
> +dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
> +dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
> +';
> +
> +SELECT * FROM t1;
> +--echo # Their values should be OFF
> +SHOW SESSION VARIABLES LIKE "%_checks";
> +
> +--echo # INSERT INTO t1 VALUES(2)
> +--echo # foreign_key_checks=1 and unique_checks=1
> +--echo # It should not change current session's variables, even error happens
> +--error 1062
> +BINLOG '
> +dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
> +dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
> +';
> +
> +SELECT * FROM t1;
> +--echo # Their values should be OFF
> +SHOW SESSION VARIABLES LIKE "%_checks";
> +
> +DROP TABLE t1;
> +
> disconnect fresh;
>
>
> === modified file 'mysql-test/suite/binlog/r/binlog_row_binlog.result'
> --- a/mysql-test/suite/binlog/r/binlog_row_binlog.result 2010-05-24 13:54:08 +0000
> +++ b/mysql-test/suite/binlog/r/binlog_row_binlog.result 2010-11-25 08:34:33 +0000
> @@ -1330,3 +1330,62 @@ Log_name Pos Event_type Server_id End_lo
> # # Write_rows 1 # table_id: # flags: STMT_END_F
> # # Query 1 # COMMIT
> DROP TABLE t1;
> +
> +# BUG#54903 BINLOG statement toggles session variables
> +# ----------------------------------------------------------------------
> +# This test verify that BINLOG statement doesn't change current session's
> +# variables foreign_key_checks and unique_checks.
> +
> +CREATE TABLE t1 (c1 INT KEY);
> +SET @@SESSION.foreign_key_checks= ON;
> +SET @@SESSION.unique_checks= ON;
> +# INSERT INTO t1 VALUES (1)
> +# foreign_key_checks=0 and unique_checks=0
> +BINLOG '
> +dfLtTBMBAAAAKQAAANcAAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
> +dfLtTBcBAAAAIgAAAPkAAAAAABcAAAAAAAcAAf/+AQAAAA==
> +';
> +SELECT * FROM t1;
> +c1
> +1
> +# Their values should be ON
> +SHOW SESSION VARIABLES LIKE "%_checks";
> +Variable_name Value
> +foreign_key_checks ON
> +unique_checks ON
> +
> +SET @@SESSION.foreign_key_checks= OFF;
> +SET @@SESSION.unique_checks= OFF;
> +# INSERT INTO t1 VALUES(2)
> +# foreign_key_checks=1 and unique_checks=1
> +BINLOG '
> +dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
> +dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
> +';
> +SELECT * FROM t1;
> +c1
> +1
> +2
> +# Their values should be OFF
> +SHOW SESSION VARIABLES LIKE "%_checks";
> +Variable_name Value
> +foreign_key_checks OFF
> +unique_checks OFF
> +# INSERT INTO t1 VALUES(2)
> +# foreign_key_checks=1 and unique_checks=1
> +# It should not change current session's variables, even error happens
> +BINLOG '
> +dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
> +dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
> +';
> +ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
> +SELECT * FROM t1;
> +c1
> +1
> +2
> +# Their values should be OFF
> +SHOW SESSION VARIABLES LIKE "%_checks";
> +Variable_name Value
> +foreign_key_checks OFF
> +unique_checks OFF
> +DROP TABLE t1;
>
> === modified file 'sql/sql_binlog.cc'
> --- a/sql/sql_binlog.cc 2010-06-18 17:32:23 +0000
> +++ b/sql/sql_binlog.cc 2010-11-25 08:34:33 +0000
> @@ -51,6 +51,13 @@ void mysql_client_binlog_statement(THD*
> size_t decoded_len= base64_needed_decoded_length(coded_len);
>
> /*
> + thd->options will be changed when applying the event. But we don't expect
> + it be changed permanently after BINLOG statement, so backup it first.
> + It will be restored at the end of this function.
> + */
> + ulonglong thd_options= thd->options;
> +
> + /*
> Allocation
> */
>
> @@ -236,6 +243,7 @@ void mysql_client_binlog_statement(THD*
> my_ok(thd);
>
> end:
> + thd->options= thd_options;
> rli->clear_tables_to_lock();
> my_free(buf, MYF(MY_ALLOW_ZERO_PTR));
> DBUG_VOID_RETURN;
