1SET @old_sql_mode= @@global.sql_mode; 2SET @old_binlog_format=@@session.binlog_format; 3SET SESSION sql_mode=8; 4Initialization 5RESET MASTER; 6CREATE TABLE t1 (id INT); 7CREATE PROCEDURE testProc() SELECT * FROM t1; 8CREATE VIEW testView as SELECT * from t1; 9CREATE FUNCTION testFunc() 10RETURNS INT 11BEGIN 12return 1; 13END;| 14CREATE TRIGGER testTrig BEFORE INSERT ON t1 15FOR EACH ROW BEGIN 16UPDATE t1 SET id = id +1; 17END;| 18CREATE EVENT testEvent ON SCHEDULE 19EVERY 1 DAY 20DO 21BEGIN 22UPDATE t1 SET id = id +1; 23END;| 24Warnings: 25Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 26Check Result 27select 28(@a:=load_file("MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug39526.binlog")) 29is not null; 30(@a:=load_file("MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug39526.binlog")) 31is not null 321 33*** String sql_mode=0 is found: 0 *** 34Clean Up 35DROP PROCEDURE testProc; 36DROP FUNCTION testFunc; 37DROP TRIGGER testTrig; 38DROP EVENT testEvent; 39DROP VIEW testView; 40DROP TABLE t1; 41SET @@global.sql_mode= @old_sql_mode; 42SET @@session.binlog_format=@old_binlog_format; 43 44# 45# Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES 46# IGNORED AND BREAKS REPLICATION 47# 48DROP DATABASE IF EXISTS mysqltest_db; 49DROP TABLE IF EXISTS test_table; 50CREATE DATABASE mysqltest_db; 51USE mysqltest_db; 52CREATE TABLE test_table (c1 CHAR(50)); 53SET @org_mode=@@sql_mode; 54SET @@sql_mode=''; 55CREATE PROCEDURE proc_without_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) 56BEGIN 57DECLARE var1 CHAR(50) DEFAULT param1; 58DECLARE var2 CHAR(50) DEFAULT param2; 59DECLARE var3 CHAR(50) DEFAULT 'abcd\bef'; 60DECLARE var4 CHAR(50) DEFAULT 'abcd\nef'; 61DECLARE var5 CHAR(50) DEFAULT 'abcd\ref'; 62DECLARE var6 CHAR(50) DEFAULT 'abcd\tef'; 63DECLARE var7 CHAR(50) DEFAULT 'abcd\\ef'; 64DECLARE var8 CHAR(50) DEFAULT 'abcd\%ef'; 65DECLARE var9 CHAR(50) DEFAULT 'abcd\_ef'; 66INSERT INTO test_table VALUES (var1); 67INSERT INTO test_table VALUES (var2); 68INSERT INTO test_table VALUES (var3); 69INSERT INTO test_table VALUES (var4); 70INSERT INTO test_table VALUES (var5); 71INSERT INTO test_table VALUES (var6); 72INSERT INTO test_table VALUES (var7); 73INSERT INTO test_table VALUES (var8); 74INSERT INTO test_table VALUES (var9); 75END 76$ 77SET @@sql_mode='NO_BACKSLASH_ESCAPES'$ 78CREATE PROCEDURE proc_with_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50)) 79BEGIN 80DECLARE var1 CHAR(50) DEFAULT param1; 81DECLARE var2 CHAR(50) DEFAULT param2; 82DECLARE var3 CHAR(50) DEFAULT 'wxyz\bef'; 83DECLARE var4 CHAR(50) DEFAULT 'wxyz\nef'; 84DECLARE var5 CHAR(50) DEFAULT 'wxyz\ref'; 85DECLARE var6 CHAR(50) DEFAULT 'wxyz\tef'; 86DECLARE var7 CHAR(50) DEFAULT 'wxyz\\ef'; 87DECLARE var8 CHAR(50) DEFAULT 'wxyz\%ef'; 88DECLARE var9 CHAR(50) DEFAULT 'wxyz\_ef'; 89INSERT INTO test_table VALUES (var1); 90INSERT INTO test_table VALUES (var2); 91INSERT INTO test_table VALUES (var3); 92INSERT INTO test_table VALUES (var4); 93INSERT INTO test_table VALUES (var5); 94INSERT INTO test_table VALUES (var6); 95INSERT INTO test_table VALUES (var7); 96INSERT INTO test_table VALUES (var8); 97INSERT INTO test_table VALUES (var9); 98END 99$ 100SET @@sql_mode=''; 101CALL proc_without_sql_mode('abcd\'ef', 'abcd\"ef'); 102CALL proc_with_sql_mode('wxyz\'ef', 'wxyz\"ef'); 103SELECT * FROM test_table; 104c1 105abcd'ef 106abcd"ef 107abcdef 108abcd 109ef 110abcd 111ef 112abcd ef 113abcd\ef 114abcd\%ef 115abcd\_ef 116wxyz'ef 117wxyz"ef 118wxyz\bef 119wxyz\nef 120wxyz\ref 121wxyz\tef 122wxyz\\ef 123wxyz\%ef 124wxyz\_ef 125"Dropping table test_table" 126DROP TABLE test_table; 127#"test_table" content after replaying the binlog 128SELECT * FROM test_table; 129c1 130abcd'ef 131abcd"ef 132abcdef 133abcd 134ef 135abcd 136ef 137abcd ef 138abcd\ef 139abcd\%ef 140abcd\_ef 141wxyz'ef 142wxyz"ef 143wxyz\bef 144wxyz\nef 145wxyz\ref 146wxyz\tef 147wxyz\\ef 148wxyz\%ef 149wxyz\_ef 150#Clean up 151DROP DATABASE mysqltest_db; 152SET @@sql_mode= @org_mode; 153use test; 154 155#End of Test for Bug#12601974 156