Debian Codegraph @

mysql-test/suite/binlog/t/binlog_sql_mode.test

raw
1 # ==== Purpose ====
2 #
3 # Test that sql_mode can correct restore before generating the binlog event
4 # when creating CREATEable objects.
5 #
6 # ==== Method ====
7 #
8 # Scan binlog file to check if the sql_mode is still set to 0 before generating binlog event
9 #
10
11 -- source include/have_log_bin.inc
12
13 # BUG#39526 sql_mode not retained in binary log for CREATE PROCEDURE
14
15 SET @old_sql_mode= @@global.sql_mode;
16 SET @old_binlog_format=@@session.binlog_format;
17 let $MYSQLD_DATADIR= `select @@datadir`;
18 SET SESSION sql_mode=8;
19
20 --echo Initialization
21
22 RESET MASTER;
23 CREATE TABLE t1 (id INT);
24
25 CREATE PROCEDURE testProc() SELECT * FROM t1;
26 CREATE VIEW testView as SELECT * from t1;
27
28 DELIMITER |;
29 CREATE FUNCTION testFunc()
30 RETURNS INT
31 BEGIN
32 return 1;
33 END;|
34 DELIMITER ;|
35
36 DELIMITER |;
37 CREATE TRIGGER testTrig BEFORE INSERT ON t1
38 FOR EACH ROW BEGIN
39 UPDATE t1 SET id = id +1;
40 END;|
41 DELIMITER ;|
42
43 DELIMITER |;
44 CREATE EVENT testEvent ON SCHEDULE
45 EVERY 1 DAY
46 DO
47 BEGIN
48 UPDATE t1 SET id = id +1;
49 END;|
50 DELIMITER ;|
51
52 --echo Check Result
53
54 let $MYSQLD_DATADIR= `select @@datadir`;
55 --exec $MYSQL_BINLOG --force-if-open $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug39526.binlog
56 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
57 eval select
58 (@a:=load_file("$MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug39526.binlog"))
59 is not null;
60 let $s_mode_unsigned= `select @a like "%@@session.sql_mode=0%" /* must return 0 */`;
61 echo *** String sql_mode=0 is found: $s_mode_unsigned ***;
62
63 --remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug39526.binlog
64
65 --echo Clean Up
66
67 DROP PROCEDURE testProc;
68 DROP FUNCTION testFunc;
69 DROP TRIGGER testTrig;
70 DROP EVENT testEvent;
71 DROP VIEW testView;
72 DROP TABLE t1;
73
74 SET @@global.sql_mode= @old_sql_mode;
75 SET @@session.binlog_format=@old_binlog_format;
76
77 --echo
78 --echo #
79 --echo # Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES
80 --echo # IGNORED AND BREAKS REPLICATION
81 --echo #
82
83 --disable_warnings
84 DROP DATABASE IF EXISTS mysqltest_db;
85 DROP TABLE IF EXISTS test_table;
86 --enable_warnings
87
88 CREATE DATABASE mysqltest_db;
89 USE mysqltest_db;
90 CREATE TABLE test_table (c1 CHAR(50));
91
92 SET @org_mode=@@sql_mode;
93
94 SET @@sql_mode='';
95 DELIMITER $;
96 CREATE PROCEDURE proc_without_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50))
97 BEGIN
98 DECLARE var1 CHAR(50) DEFAULT param1;
99 DECLARE var2 CHAR(50) DEFAULT param2;
100 DECLARE var3 CHAR(50) DEFAULT 'abcd\bef';
101 DECLARE var4 CHAR(50) DEFAULT 'abcd\nef';
102 DECLARE var5 CHAR(50) DEFAULT 'abcd\ref';
103 DECLARE var6 CHAR(50) DEFAULT 'abcd\tef';
104 DECLARE var7 CHAR(50) DEFAULT 'abcd\\ef';
105 DECLARE var8 CHAR(50) DEFAULT 'abcd\%ef';
106 DECLARE var9 CHAR(50) DEFAULT 'abcd\_ef';
107
108 INSERT INTO test_table VALUES (var1);
109 INSERT INTO test_table VALUES (var2);
110 INSERT INTO test_table VALUES (var3);
111 INSERT INTO test_table VALUES (var4);
112 INSERT INTO test_table VALUES (var5);
113 INSERT INTO test_table VALUES (var6);
114 INSERT INTO test_table VALUES (var7);
115 INSERT INTO test_table VALUES (var8);
116 INSERT INTO test_table VALUES (var9);
117 END
118 $
119
120 SET @@sql_mode='NO_BACKSLASH_ESCAPES'$
121 CREATE PROCEDURE proc_with_sql_mode (IN param1 CHAR(50), IN param2 CHAR(50))
122 BEGIN
123 DECLARE var1 CHAR(50) DEFAULT param1;
124 DECLARE var2 CHAR(50) DEFAULT param2;
125 DECLARE var3 CHAR(50) DEFAULT 'wxyz\bef';
126 DECLARE var4 CHAR(50) DEFAULT 'wxyz\nef';
127 DECLARE var5 CHAR(50) DEFAULT 'wxyz\ref';
128 DECLARE var6 CHAR(50) DEFAULT 'wxyz\tef';
129 DECLARE var7 CHAR(50) DEFAULT 'wxyz\\ef';
130 DECLARE var8 CHAR(50) DEFAULT 'wxyz\%ef';
131 DECLARE var9 CHAR(50) DEFAULT 'wxyz\_ef';
132
133 INSERT INTO test_table VALUES (var1);
134 INSERT INTO test_table VALUES (var2);
135 INSERT INTO test_table VALUES (var3);
136 INSERT INTO test_table VALUES (var4);
137 INSERT INTO test_table VALUES (var5);
138 INSERT INTO test_table VALUES (var6);
139 INSERT INTO test_table VALUES (var7);
140 INSERT INTO test_table VALUES (var8);
141 INSERT INTO test_table VALUES (var9);
142 END
143 $
144
145 DELIMITER ;$
146 SET @@sql_mode='';
147 CALL proc_without_sql_mode('abcd\'ef', 'abcd\"ef');
148 CALL proc_with_sql_mode('wxyz\'ef', 'wxyz\"ef');
149 SELECT * FROM test_table;
150
151 let $MYSQLD_DATADIR= `select @@datadir`;
152 --exec $MYSQL_BINLOG --force-if-open -d mysqltest_db $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog
153
154 --echo "Dropping table test_table"
155 DROP TABLE test_table;
156
157 --exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog"
158
159 --echo #"test_table" content after replaying the binlog
160 SELECT * FROM test_table;
161
162 --echo #Clean up
163 --remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug12601974.binlog
164 DROP DATABASE mysqltest_db;
165 SET @@sql_mode= @org_mode;
166 use test;
167
168 --echo
169 --echo #End of Test for Bug#12601974
170
171