MySQL Bugs: #68106: NO_BACKSLASH_ESCAPES with replication fails with apostrophes by procedure

Bug #68106	NO_BACKSLASH_ESCAPES with replication fails with apostrophes by procedure
Submitted:	17 Jan 2013 9:35	Modified:	6 Mar 2013 11:43
Reporter:	wei liu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.5.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	apostrophes, NO_BACKSLASH_ESCAPES, PROCEDURE, replication

Description:
Replication fails if the parameter of procedure including of apostrophes on the slave server when both servers have NO_BACKSLASH_ESCAPES enabled globally,
e.g. my.cnf:
sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_BACKSLASH_ESCAPES,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,PIPES_AS_CONCAT'

Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'' COLLATE 'utf8_bin'))' at line 2' on query.

How to repeat:
1) vi my.cnf
sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_BACKSLASH_ESCAPES,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION,PIPES_AS_CONCAT'

2) create test table aqtest:
CREATE TABLE `aqtest` (
  `sender` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `d` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

3) create procedure 
DELIMITER $$

CREATE PROCEDURE `prc_will_backslash_test`(
  IN p_audit_data_MySQL      VARCHAR(4000) )
BEGIN
 Start Transaction;

 insert into aqtest (sender) values (p_audit_data_MySQL);

 COMMIT;
END $$

DELIMITER ;

4) call prc_will_backslash_test('a''b''c');

5) Check the slave status, u will find the syntax error, then check the master and slave log, u will find that the '' become \' transfer by MySQL itself ignoring the 'sql_mode' setting.

BTW, Use the insert sql 'insert into aqtest(sender) values ('a''b''c');' directly will not get the error.

Suggested fix:
Please reconsider the validity of parameter 'NO_BACKSLASH_ESCAPES' when use replication.

Looks like http://bugs.mysql.com/bug.php?id=27552 ?

It's different, my case is repeatable every time if you use 'call procedure', and the mysql version of mine is 5.5.16. The bug #27552 only solved 'insert into', not my case.

Hello Wei,

I repeated the issue on 5.5.16, but not on 5.5.29.
Now I checked internally, and found this:

Bug 12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES IGNORED AND BREAKS REPLICATION

"
Noted in 5.1.63, 5.5.23, 5.6.5 changelogs.

Mishandling of NO_BACKSLASH_ESCAPES SQL mode within stored procedures
on slave servers could cause replication failures.
"

I also found this bug in 'Server version: 5.6.5-m8-log MySQL Community Server (GPL)', it seems haven't solved in 5.6.5 like you said.

It seems OK in Server version: 5.6.10-log MySQL Community Server (GPL).