Debian Codegraph @

mysql-test/suite/binlog_nogtid/t/binlog_row_binlog_myisam.test

raw
1 #
2 # misc binlogging tests that do not require a slave running
3 #
4
5 -- source include/have_binlog_format_row.inc
6 -- source include/have_log_bin.inc
7 -- source include/have_debug.inc
8 -- source include/have_myisam.inc
9 RESET MASTER;
10
11 #
12 # Bug#33798: prepared statements improperly handle large unsigned ints
13 #
14 --disable_warnings
15 drop table if exists t1;
16 --enable_warnings
17 reset master;
18 create table t1 (a bigint unsigned, b bigint(20) unsigned);
19 prepare stmt from "insert into t1 values (?,?)";
20 set @a= 9999999999999999;
21 set @b= 14632475938453979136;
22 execute stmt using @a, @b;
23 deallocate prepare stmt;
24 drop table t1;
25 --source include/show_binlog_events.inc
26
27
28 #
29 # Bug #39182: Binary log producing incompatible character set query from
30 # stored procedure.
31 #
32 reset master;
33 CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
34 USE bug39182;
35 CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
36 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
37
38 DELIMITER //;
39
40 CREATE PROCEDURE p1()
41 BEGIN
42 DECLARE s1 VARCHAR(255);
43 SET s1= "test";
44 CREATE TEMPORARY TABLE tmp1
45 SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
46 SELECT
47 COLLATION(NAME_CONST('s1', _utf8'test')) c1,
48 COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
49 COLLATION(s1) c3,
50 COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
51 COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
52 COERCIBILITY(s1) d3;
53 DROP TEMPORARY TABLE tmp1;
54 END//
55
56 DELIMITER ;//
57
58 CALL p1();
59 source include/show_binlog_events.inc;
60
61 DROP PROCEDURE p1;
62 DROP TABLE t1;
63 DROP DATABASE bug39182;
64 USE test;
65
66 #
67 # Bug#35383: binlog playback and replication breaks due to
68 # name_const substitution
69 #
70 DELIMITER //;
71 CREATE PROCEDURE p1(IN v1 INT)
72 BEGIN
73 CREATE TABLE t1 SELECT v1;
74 DROP TABLE t1;
75 END//
76 CREATE PROCEDURE p2()
77 BEGIN
78 DECLARE v1 INT;
79 CREATE TABLE t1 SELECT v1+1;
80 DROP TABLE t1;
81 END//
82 CREATE PROCEDURE p3(IN v1 INT)
83 BEGIN
84 CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0;
85 DROP TABLE t1;
86 END//
87 CREATE PROCEDURE p4(IN v1 INT)
88 BEGIN
89 DECLARE v2 INT;
90 CREATE TABLE t1 SELECT 1, v1, v2;
91 DROP TABLE t1;
92 CREATE TABLE t1 SELECT 1, v1+1, v2;
93 DROP TABLE t1;
94 END//
95 DELIMITER ;//
96
97 CALL p1(1);
98 CALL p2();
99 CALL p3(0);
100 CALL p4(0);
101 DROP PROCEDURE p1;
102 DROP PROCEDURE p2;
103 DROP PROCEDURE p3;
104 DROP PROCEDURE p4;
105
106 --echo End of 5.0 tests
107
108 # Test of a too big SET INSERT_ID: see if the truncated value goes
109 # into binlog (right), or the too big value (wrong); we look at the
110 # binlog further down with SHOW BINLOG EVENTS.
111 reset master;
112 create table t1 (id tinyint auto_increment primary key);
113 set insert_id=128;
114 insert ignore into t1 values(null);
115 select * from t1;
116 drop table t1;
117
118 # bug#22027
119 create table t1 (a int);
120 create table if not exists t2 select * from t1;
121
122 # bug#22762
123 create temporary table tt1 (a int);
124 create table if not exists t3 like tt1;
125
126 # BUG#25091 (A DELETE statement to mysql database is not logged with
127 # ROW mode format): Checking that some basic operations on tables in
128 # the mysql database is replicated even when the current database is
129 # 'mysql'.
130
131 --disable_warnings
132 USE mysql;
133 INSERT IGNORE INTO user SET host='localhost', user='@#@', authentication_string='*1111111111111111111111111111111111111111';
134 UPDATE user SET authentication_string='*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' WHERE host='localhost' AND user='@#@';
135 DELETE FROM user WHERE host='localhost' AND user='@#@';
136 --enable_warnings
137
138 use test;
139
140 # Show binlog events on a different connection because it calls
141 # `SELECT UUID()` internally, which is marked unsafe. Since there is
142 # an open temporary table, this causes the current connection to log
143 # subsequent statements in row format.
144 connect (other,localhost,root,,test);
145 connection other;
146 source include/show_binlog_events.inc;
147 connection default;
148 disconnect other;
149
150 drop table t1,t2,t3,tt1;
151
152 --echo #
153 --echo # Bug #45998: database crashes when running "create as select"
154 --echo #
155 CREATE DATABASE test1;
156 USE test1;
157 DROP DATABASE test1;
158 CREATE TABLE test.t1(a int);
159 INSERT INTO test.t1 VALUES (1), (2);
160 CREATE TABLE test.t2 SELECT * FROM test.t1;
161 USE test;
162 DROP TABLES t1, t2;
163
164 #
165 # Bug#46640
166 # This test verifies if the server_id stored in the "format
167 # description BINLOG statement" will override the server_id
168 # of the server executing the statements.
169 #
170
171 connect (fresh,localhost,root,,test);
172 connection fresh;
173
174 RESET MASTER;
175 CREATE TABLE t1 (a INT PRIMARY KEY);
176
177 # Format description event, with server_id = 10;
178 BINLOG '
179 3u9kSA8KAAAAZgAAAGoAAAABAAQANS4xLjM1LW1hcmlhLWJldGExLWRlYnVnLWxvZwAAAAAAAAAA
180 AAAAAAAAAAAAAAAAAADe72RIEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
181 ';
182
183 # What server_id is logged for a statement? Should be our own, not the
184 # one from the format description event.
185 INSERT INTO t1 VALUES (1);
186
187 # INSERT INTO t1 VALUES (2), with server_id=20. Check that this is logged
188 # with our own server id, not the 20 from the BINLOG statement.
189 BINLOG '
190 3u9kSBMUAAAAKQAAAJEBAAAAABoAAAAAAAAABHRlc3QAAnQxAAEDAAA=
191 3u9kSBcUAAAAIgAAALMBAAAQABoAAAAAAAEAAf/+AgAAAA==
192 ';
193
194 # Show binlog events to check that server ids are correct.
195 --let $show_binlog_events_mask_columns= 1,2,5
196 --source include/show_binlog_events.inc
197
198 DROP TABLE t1;
199
200 --echo
201 --echo # BUG#54903 BINLOG statement toggles session variables
202 --echo # ----------------------------------------------------------------------
203 --echo # This test verify that BINLOG statement doesn't change current session's
204 --echo # variables foreign_key_checks and unique_checks.
205 --echo
206 CREATE TABLE t1 (c1 INT KEY);
207
208 SET @@SESSION.foreign_key_checks= ON;
209 SET @@SESSION.unique_checks= ON;
210
211 --echo # INSERT INTO t1 VALUES (1)
212 --echo # foreign_key_checks=0 and unique_checks=0
213 BINLOG '
214 dfLtTBMBAAAAKQAAANcAAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
215 dfLtTBcBAAAAIgAAAPkAAAAAABcAAAAAAAcAAf/+AQAAAA==
216 ';
217
218 SELECT * FROM t1;
219 --echo # Their values should be ON
220 SHOW SESSION VARIABLES LIKE "%_checks";
221
222 --echo
223 SET @@SESSION.foreign_key_checks= OFF;
224 SET @@SESSION.unique_checks= OFF;
225
226 --echo # INSERT INTO t1 VALUES(2)
227 --echo # foreign_key_checks=1 and unique_checks=1
228 BINLOG '
229 dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
230 dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
231 ';
232
233 SELECT * FROM t1;
234 --echo # Their values should be OFF
235 SHOW SESSION VARIABLES LIKE "%_checks";
236
237 --echo # INSERT INTO t1 VALUES(2)
238 --echo # foreign_key_checks=1 and unique_checks=1
239 --echo # It should not change current session's variables, even error happens
240 call mtr.add_suppression("Replica SQL.*Could not execute Write_rows_v1 event on table test.t1; Duplicate entry .2. for key .t1.PRIMARY., Error_code: 1062");
241 --error 1062
242 BINLOG '
243 dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
244 dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
245 ';
246
247 SELECT * FROM t1;
248 --echo # Their values should be OFF
249 SHOW SESSION VARIABLES LIKE "%_checks";
250
251 DROP TABLE t1;
252
253 disconnect fresh;
254