Bug #39526 sql_mode not retained in binary log for CREATE PROCEDURE

• Submitted: 18 Sep 2008 21:18
• Modified: 18 Mar 16:18
• Reporter: Scott Noyes
• Status: Closed
• Category: Server: Replication
• Severity: S2 (Serious)
• Version: 5.1.28
• OS: Microsoft Windows
• Assigned to: Bugs System
• Target Version: 5.1+
• Triage: Triaged: D2 (Serious)

[18 Sep 2008 21:18] Scott Noyes

Description:
Creating a procedure using ANSI_QUOTES does not pass that sql_mode correctly to the
binary log, resulting in an error on the slave.

How to repeat:
On the master:

SET sql_mode='ANSI_QUOTES';
CREATE TABLE t1 (id int);
CREATE PROCEDURE testProc() SELECT * FROM "t1";

It does not matter if the slave has ansi_quotes set or not, because the binary log
contains the wrong setting. Note how it is correctly set to 4 before the CREATE TABLE,
but then incorrectly set back to 0 before the CREATE PROCEDURE:

# at 580
#080918 14:08:45 server id 5128  end_log_pos 667        Query   thread_id=1    
exec_time=0     error_code=0
SET TIMESTAMP=1221764925/*!*/;
SET @@session.sql_mode=4/*!*/;
CREATE TABLE t1 (id int)
/*!*/;
# at 667
#080918 14:08:59 server id 5128  end_log_pos 805        Query   thread_id=1    
exec_time=0     error_code=0
SET TIMESTAMP=1221764939/*!*/;
SET @@session.sql_mode=0/*!*/;
CREATE DEFINER=`root`@`localhost` PROCEDURE `testProc`()
SELECT * FROM "t1"
/*!*/;

Suggested fix:
Make sure the correct sql_mode is set correctly in the binary log

[12 Feb 11:06] 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/66012

2793 Leonard Zhou	2009-02-12
      Bug#39526 Correct sql_mode in binary log for CREATE PROCEDURE
      
      sql_mode can't correctly write into binary log when creating a procedure.
      
      restore sql_mode before write events into log file when creating a procedure.

[28 Feb 15:08] 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/67927

2825 Leonard Zhou	2009-02-26
      BUG#39526 sql_mode not retained in binary log for CREATE PROCEDURE
      
      Set wrong sql_mode when creating a procedure. 
      So the sql_mode can't be writen into binary log correctly.
      
      Restore the current session sql_mode right before generating the binlog event
      When creating a procedure.

[2 Mar 6:23] 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/67952

2825 Leonard Zhou	2009-02-28
      BUG#39526 sql_mode not retained in binary log for CREATE PROCEDURE
            
      Set wrong sql_mode when creating a procedure. 
      So that the sql_mode can't be writen into binary log correctly.
            
      Restore the current session sql_mode right before generating the binlog event
      when creating a procedure.

[5 Mar 11:35] 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/68340

2833 He Zhenxing	2009-03-05 [merge]
      Auto merge

[5 Mar 11:36] 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/68341

3102 He Zhenxing	2009-03-05 [merge]
      Auto merge

[13 Mar 20:07] Bugs System

Pushed into 5.1.33 (revid:[e-mail]-20090313111355-7bsi1hgkvrg8pdds) (version source
revid:[e-mail]-20090311061050-ihp0g77znonq1tuq) (merge vers: 5.1.33) (pib:6)

[15 Mar 10:16] Jon Stephens

Documented bugfix in the 5.1.33 changelog as follows:

        The server SQL mode in effect when a stored procedure was
        created was not retained in the binary log. This could cause the
        same CREATE PROCEDURE statement to fail on the slave.

Set status to NDI pending merge to 6.0 tree.

[15 Mar 10:30] Jon Stephens

Revised changelog entry:

        The server SQL mode in effect when a stored procedure was
        created was not retained in the binary log. This could cause a
        CREATE PROCEDURE statement that succeeded on the master to fail 
        on the slave.
      
        This issue was first noticed when a stored procedure was created
        when ANSI_QUOTES was in effect on the master, but could possibly 
        cause failed CREATE PROCEDURE statements and other problems on the 
        slave when using other server SQL modes as well.

[18 Mar 14:17] Bugs System

Pushed into 6.0.11-alpha (revid:[e-mail]-20090318122208-1b5kvg6zeb4hxwp9) (version
source revid:[e-mail]-20090310140952-gwtoq87wykhji3zi) (merge vers:
6.0.11-alpha) (pib:6)

[18 Mar 16:18] Jon Stephens

Fix also documented in the 6.0.11 changelog; closed.

[9 May 18:46] Bugs System

Pushed into 5.1.34-ndb-6.2.18 (revid:[e-mail]-20090508185236-p9b3as7qyauybefl)
(version source revid:[e-mail]-20090508100057-30ote4xggi4nq14v) (merge vers:
5.1.33-ndb-6.2.18) (pib:6)

[9 May 19:43] Bugs System

Pushed into 5.1.34-ndb-6.3.25 (revid:[e-mail]-20090509063138-1u3q3v09wnn2txyt)
(version source revid:[e-mail]-20090508175813-s6yele2z3oh6o99z) (merge vers:
5.1.33-ndb-6.3.25) (pib:6)

[9 May 20:40] Bugs System

Pushed into 5.1.34-ndb-7.0.6 (revid:[e-mail]-20090509154927-im9a7g846c6u1hzc)
(version source revid:[e-mail]-20090509073226-09bljakh9eppogec) (merge vers:
5.1.33-ndb-7.0.6) (pib:6)