Debian Codegraph @

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

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