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

Analysis:
========================
sql_mode "NO_BACKSLASH_ESCAPES": When user want to use backslash as character input,
instead of escape character in a string literal then sql_mode can be set to 
"NO_BACKSLASH_ESCAPES". With this mode enabled, backslash becomes an ordinary 
character like any other. 

SQL_MODE set applies to the current client session. And while creating the stored 
procedure, MySQL stores the current sql_mode and always executes the stored 
procedure in sql_mode stored with the Procedure, regardless of the server SQL 
mode in effect when the routine is invoked.  

In the scenario (for which bug is reported), the routine is created with 
sql_mode=NO_BACKSLASH_ESCAPES. And routine is executed with the invoker sql_mode
is "" (NOT SET) by executing statement "call testp('Axel\'s')".
Since invoker sql_mode is "" (NOT_SET), the '\' in 'Axel\'s'(argument to function)
is considered as escape character and column "a" (of table "t1") values are 
updated with "Axel's". The binary log generated for above update operation is as below,

  set sql_mode=XXXXXX (for no_backslash_escapes)
  update test.t1 set a= NAME_CONST('var',_latin1'Axel\'s' COLLATE 'latin1_swedish_ci');

While logging stored procedure statements, the local variables (params) used in
statements are replaced with the NAME_CONST(var_name, var_value) (Internal function) 
(http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_name-const)

On slave, these logs are applied. NAME_CONST is parsed to get the variable and its
value. Since, stored procedure is created with sql_mode="NO_BACKSLASH_ESCAPES", the sql_mode
is also logged in. So that at slave this sql_mode is set before executing the statements
of routine.  So at slave, sql_mode is set to "NO_BACKSLASH_ESCAPES" and then while
parsing NAME_CONST of string variable, '\' is considered as NON ESCAPE character
and parsing reported error for "'" (as we have only one "'" no backslash). 

At slave, parsing was proper with sql_mode "NO_BACKSLASH_ESCAPES".
But above error reported while writing bin log, "'" (of Axel's) is escaped with
"\" character. Actually, all special characters (n, r, ', ", \, 0...) are escaped
while writing NAME_CONST for string variable(param, local variable) in bin log 
Airrespective of "NO_BACKSLASH_ESCAPES" sql_mode. So, basically, the problem is 
that logging string parameter does not take into account sql_mode value.

Fix:
========================
So when sql_mode is set to "NO_BACKSLASH_ESCAPES", escaping  characters as 
(n, r, ', ", \, 0...) should be avoided. To do so, added a check to not to
escape such characters while writing NAME_CONST for string variables in bin 
log. 
And when sql_mode is set to NO_BACKSLASH_ESCAPES, quote character "'" is
represented as ''.
http://dev.mysql.com/doc/refman/5.6/en/string-literals.html (There are several 
ways to include quote characters within a string: )

2 files changed, 207 insertions, 0 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_sql_mode.result b/mysql-test/suite/binlog/r/binlog_sql_mode.result
index 4477c94a95e..1aea77c4a4b 100644
--- a/mysql-test/suite/binlog/r/binlog_sql_mode.result
+++ b/mysql-test/suite/binlog/r/binlog_sql_mode.result
@@ -38,3 +38,114 @@ DROP VIEW testView;
 DROP TABLE t1;
 SET @@global.sql_mode= @old_sql_mode;
 SET @@session.binlog_format=@old_binlog_format;
+
+#
+# Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES
+# IGNORED AND BREAKS REPLICATION
+#
+DROP DATABASE IF EXISTS mysqltest_db;
+DROP TABLE IF EXISTS test_table;
+CREATE DATABASE mysqltest_db;
+USE mysqltest_db;
+CREATE TABLE test_table (c1 CHAR(50));
+SET @org_mode=@@sql_mode;
+SET @@sql_mode='';
+CREATE PROCEDURE proc_without_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50))
+BEGIN
+DECLARE var1 CHAR(50) DEFAULT param1;
+DECLARE var2 CHAR(50) DEFAULT param2;
+DECLARE var3 CHAR(50) DEFAULT 'abcd\bef';
+DECLARE var4 CHAR(50) DEFAULT 'abcd\nef';
+DECLARE var5 CHAR(50) DEFAULT 'abcd\ref';
+DECLARE var6 CHAR(50) DEFAULT 'abcd\tef';
+DECLARE var7 CHAR(50) DEFAULT 'abcd\\ef';
+DECLARE var8 CHAR(50) DEFAULT 'abcd\%ef';
+DECLARE var9 CHAR(50) DEFAULT 'abcd\_ef';
+INSERT INTO test_table VALUES (var1);
+INSERT INTO test_table VALUES (var2);
+INSERT INTO test_table VALUES (var3);
+INSERT INTO test_table VALUES (var4);
+INSERT INTO test_table VALUES (var5);
+INSERT INTO test_table VALUES (var6);
+INSERT INTO test_table VALUES (var7);
+INSERT INTO test_table VALUES (var8);
+INSERT INTO test_table VALUES (var9);
+END
+$
+SET @@sql_mode='NO_BACKSLASH_ESCAPES'$
+CREATE PROCEDURE proc_with_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50))
+BEGIN
+DECLARE var1 CHAR(50) DEFAULT param1;
+DECLARE var2 CHAR(50) DEFAULT param2;
+DECLARE var3 CHAR(50) DEFAULT 'wxyz\bef';
+DECLARE var4 CHAR(50) DEFAULT 'wxyz\nef';
+DECLARE var5 CHAR(50) DEFAULT 'wxyz\ref';
+DECLARE var6 CHAR(50) DEFAULT 'wxyz\tef';
+DECLARE var7 CHAR(50) DEFAULT 'wxyz\\ef';
+DECLARE var8 CHAR(50) DEFAULT 'wxyz\%ef';
+DECLARE var9 CHAR(50) DEFAULT 'wxyz\_ef';
+INSERT INTO test_table VALUES (var1);
+INSERT INTO test_table VALUES (var2);
+INSERT INTO test_table VALUES (var3);
+INSERT INTO test_table VALUES (var4);
+INSERT INTO test_table VALUES (var5);
+INSERT INTO test_table VALUES (var6);
+INSERT INTO test_table VALUES (var7);
+INSERT INTO test_table VALUES (var8);
+INSERT INTO test_table VALUES (var9);
+END
+$
+SET @@sql_mode='';
+CALL proc_without_sql_mode('abcd\'ef', 'abcd\"ef');
+CALL proc_with_sql_mode('wxyz\'ef', 'wxyz\"ef');
+SELECT * FROM test_table;
+c1
+abcd'ef
+abcd"ef
+abcdef
+abcd
+ef
+abcd
ef
+abcd	ef
+abcd\ef
+abcd\%ef
+abcd\_ef
+wxyz'ef
+wxyz"ef
+wxyz\bef
+wxyz\nef
+wxyz\ref
+wxyz\tef
+wxyz\\ef
+wxyz\%ef
+wxyz\_ef
+"Dropping table test_table"
+DROP TABLE test_table;
+#"test_table" content after replaying the binlog
+SELECT * FROM test_table;
+c1
+abcd'ef
+abcd"ef
+abcdef
+abcd
+ef
+abcd
ef
+abcd	ef
+abcd\ef
+abcd\%ef
+abcd\_ef
+wxyz'ef
+wxyz"ef
+wxyz\bef
+wxyz\nef
+wxyz\ref
+wxyz\tef
+wxyz\\ef
+wxyz\%ef
+wxyz\_ef
+#Clean up
+DROP DATABASE mysqltest_db;
+SET @@sql_mode= @org_mode;
+use test;
+
+#End of Test for Bug#12601974 
diff --git a/mysql-test/suite/binlog/t/binlog_sql_mode.test b/mysql-test/suite/binlog/t/binlog_sql_mode.test
index ab4f6450543..167c8f5a96d 100644
--- a/mysql-test/suite/binlog/t/binlog_sql_mode.test
+++ b/mysql-test/suite/binlog/t/binlog_sql_mode.test
@@ -73,3 +73,99 @@ DROP TABLE t1;
 
 SET @@global.sql_mode= @old_sql_mode;
 SET @@session.binlog_format=@old_binlog_format;
+
+--echo 
+--echo #
+--echo # Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES
+--echo # IGNORED AND BREAKS REPLICATION
+--echo #
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db;
+DROP TABLE IF EXISTS test_table;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db;
+USE mysqltest_db;
+CREATE TABLE test_table (c1 CHAR(50));
+
+SET @org_mode=@@sql_mode;
+
+SET @@sql_mode='';
+DELIMITER $;
+CREATE PROCEDURE proc_without_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50))
+BEGIN
+  DECLARE var1 CHAR(50) DEFAULT param1;
+  DECLARE var2 CHAR(50) DEFAULT param2;
+  DECLARE var3 CHAR(50) DEFAULT 'abcd\bef';
+  DECLARE var4 CHAR(50) DEFAULT 'abcd\nef';
+  DECLARE var5 CHAR(50) DEFAULT 'abcd\ref';
+  DECLARE var6 CHAR(50) DEFAULT 'abcd\tef';
+  DECLARE var7 CHAR(50) DEFAULT 'abcd\\ef';
+  DECLARE var8 CHAR(50) DEFAULT 'abcd\%ef';
+  DECLARE var9 CHAR(50) DEFAULT 'abcd\_ef';
+
+  INSERT INTO test_table VALUES (var1);
+  INSERT INTO test_table VALUES (var2);
+  INSERT INTO test_table VALUES (var3);
+  INSERT INTO test_table VALUES (var4);
+  INSERT INTO test_table VALUES (var5);
+  INSERT INTO test_table VALUES (var6);
+  INSERT INTO test_table VALUES (var7);
+  INSERT INTO test_table VALUES (var8);
+  INSERT INTO test_table VALUES (var9);
+END
+$
+
+SET @@sql_mode='NO_BACKSLASH_ESCAPES'$
+CREATE PROCEDURE proc_with_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50))
+BEGIN
+  DECLARE var1 CHAR(50) DEFAULT param1;
+  DECLARE var2 CHAR(50) DEFAULT param2;
+  DECLARE var3 CHAR(50) DEFAULT 'wxyz\bef';
+  DECLARE var4 CHAR(50) DEFAULT 'wxyz\nef';
+  DECLARE var5 CHAR(50) DEFAULT 'wxyz\ref';
+  DECLARE var6 CHAR(50) DEFAULT 'wxyz\tef';
+  DECLARE var7 CHAR(50) DEFAULT 'wxyz\\ef';
+  DECLARE var8 CHAR(50) DEFAULT 'wxyz\%ef';
+  DECLARE var9 CHAR(50) DEFAULT 'wxyz\_ef';
+
+  INSERT INTO test_table VALUES (var1);
+  INSERT INTO test_table VALUES (var2);
+  INSERT INTO test_table VALUES (var3);
+  INSERT INTO test_table VALUES (var4);
+  INSERT INTO test_table VALUES (var5);
+  INSERT INTO test_table VALUES (var6);
+  INSERT INTO test_table VALUES (var7);
+  INSERT INTO test_table VALUES (var8);
+  INSERT INTO test_table VALUES (var9);
+END
+$
+
+DELIMITER ;$
+SET @@sql_mode='';
+CALL proc_without_sql_mode('abcd\'ef', 'abcd\"ef');
+CALL proc_with_sql_mode('wxyz\'ef', 'wxyz\"ef');
+SELECT * FROM test_table;
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+--exec $MYSQL_BINLOG --force-if-open -d mysqltest_db $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog
+
+--echo "Dropping table test_table"
+DROP TABLE test_table;
+
+--exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog"
+
+--echo #"test_table" content after replaying the binlog
+SELECT * FROM test_table;
+
+--echo #Clean up
+--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog
+DROP DATABASE mysqltest_db;
+SET @@sql_mode= @org_mode;
+use test;
+
+--echo 
+--echo #End of Test for Bug#12601974 
+
+