MySQL Bugs: #39526: sql_mode not retained in binary log for CREATE PROCEDURE

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:	Windows
Assigned to:	li zhou	CPU Architecture:	Any

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

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.

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.

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.

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

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

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)

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.

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.

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)

Fix also documented in the 6.0.11 changelog; closed.

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)

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)

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)