Bug #39526 sql_mode not retained in binary log for CREATE PROCEDURE
Submitted: 18 Sep 2008 19:18 Modified: 18 Mar 2009 15:18
Reporter: Scott Noyes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.28 OS:Microsoft Windows
Assigned to: li zhou
Triage: Triaged: D2 (Serious)

[18 Sep 2008 19: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 2009 10: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 2009 14: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 2009 5: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 2009 10: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 2009 10: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 2009 19:07] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:zhou.li@sun.com-20090311061050-ihp0g77znonq1tuq) (merge vers: 5.1.33) (pib:6)
[15 Mar 2009 9: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 2009 9: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 2009 13:17] Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:matthias.leich@sun.com-20090310140952-gwtoq87wykhji3zi) (merge vers: 6.0.11-alpha) (pib:6)
[18 Mar 2009 15:18] Jon Stephens
Fix also documented in the 6.0.11 changelog; closed.
[9 May 2009 16:46] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:43] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:40] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)