Bug #54903 BINLOG statement toggles session variables

• Submitted: 29 Jun 2010 20:32
• Modified: 4 Jan 2011 4:32
• Reporter: Todd Farmer (OCA)
• Status: Closed
• Category: MySQL Server: Row Based Replication ( RBR )
• Severity: S2 (Serious)
• Version: 5.1.47
• OS: Any
• Assigned to: Libing Song
• CPU Architecture: Any

[29 Jun 2010 20:32] Todd Farmer

Description:
It appears that, when the BINLOG command is executed (usually in the context of doing a PITR), session variables are modified and left as such after completion.  This is particularly noticeable and troublesome when replaying a row-based binary log which relies on setting @@session.foreign_key_checks = OFF in order to create a series of InnoDB tables with foreign key constraints.

How to repeat:
Execute the following, making note of the generated binary log:

SET binlog_format = 'ROW';
FLUSH LOGS;
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
SET @@session.foreign_key_checks = 0;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, INDEX a_idx (a), FOREIGN KEY (a) REFERENCES t3(a)) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (a INT, INDEX a_idx (a), FOREIGN KEY (a) REFERENCES t3(a)) ENGINE = InnoDB;
FLUSH LOGS;

Replay the binary log and observe the error generated:

D:\Documents and Settings\todd>\mysql-5.1.47-win32\bin\mysqlbinlog.exe \mysql-5.1.47-win32\data\US-TF221715-02-bin.000022 > test.sql

D:\Documents and Settings\todd>mysql -uroot -P3309 < test.sql
ERROR 1005 (HY000) at line 65: Can't create table 'test.t2' (errno: 150)

Suggested fix:
Leave session variables in same state after completion of BINLOG statement as they were before.

[10 Nov 2010 20:31] Todd Farmer

test.sql

Attachment: test.sql (application/octet-stream, text), 2.85 KiB.

[17 Nov 2010 0:21] Omer Barnir

triage: setting tag to SR51MRU

[25 Nov 2010 7:00] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124968

[25 Nov 2010 8:37] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124981

3548 [e-mail]	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.

[25 Nov 2010 8:49] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124982

3548 [e-mail]	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.
     @ mysql-test/extra/binlog_tests/binlog.test
        Add test to verify this patch.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Add test to verify this patch.
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Add test to verify this patch.
     @ sql/sql_binlog.cc
        Add code to backup and restore thd->options.

[28 Nov 2010 9:47] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125242

3524 [e-mail]	2010-11-28
      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.
     @ mysql-test/extra/binlog_tests/binlog.test
        Add test to verify this patch.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Add test to verify this patch.
     @ mysql-test/suite/binlog/r/binlog_stm_binlog.result
        Add test to verify this patch.
     @ sql/sql_binlog.cc
        Add code to backup and restore thd->options.

[28 Nov 2010 10:49] Libing Song

Pushed into mysql-5.1-bugteam, merged into mysql-5.5-bugteam and mysql-trunk-bugfixing

[5 Dec 2010 12:44] Bugs System

Pushed into mysql-trunk 5.6.1 (revid:[e-mail]-20101205122447-6x94l4fmslpbttxj) (version source revid:[e-mail]-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)

[14 Dec 2010 6:26] Jon Stephens

Issue doesn't appear in a 5.6 release; waiting for merges to 5.1 and 5.5 trees.

[17 Dec 2010 12:49] Bugs System

Pushed into mysql-5.1 5.1.55 (revid:[e-mail]-20101217124435-9imm43geck5u55qw) (version source revid:[e-mail]-20101201193331-1c07sjno2g7m46ix) (merge vers: 5.1.55) (pib:24)

[17 Dec 2010 12:54] Bugs System

Pushed into mysql-5.5 5.5.9 (revid:[e-mail]-20101217124733-p1ivu6higouawv8l) (version source revid:[e-mail]-20101128100602-joj8e01s1wdrihpq) (merge vers: 5.5.8) (pib:24)

[4 Jan 2011 4:32] Jon Stephens

Documented bugfix in the 5.1.55 and 5.5.9 changelogs as follows:

        The BINLOG statement modified the values of session variables,
        which could lead to problems with operations such a
        point-in-time recovery. One such case occurred when replaying a
        row-based binary log which relied on setting
        @@session.foreign_key_checks = OFF in order to create and
        populate a set of InnoDB tables having foreign key constraints.

Closed.