Debian Codegraph @

mysql-test/suite/binlog/include/binlog.test

raw
1 #
2 # misc binlogging tests that do not require a slave running
3 #
4
5 -- source include/have_log_bin.inc
6 -- source include/not_embedded.inc
7 -- source include/have_innodb.inc
8
9 --disable_warnings
10 drop table if exists t1, t2;
11 --enable_warnings
12 reset master;
13
14 create table t1 (a int) engine=innodb;
15 create table t2 (a int) engine=innodb;
16 begin;
17 insert t1 values (5);
18 commit;
19 begin;
20 insert t2 values (5);
21 commit;
22 # first COMMIT must be Query_log_event, second - Xid_log_event
23 source include/show_binlog_events.inc;
24 drop table t1,t2;
25
26 #
27 # binlog rotation after one big transaction
28 #
29 reset master;
30 let $1=100;
31
32 create table t1 (n int) engine=innodb;
33 begin;
34 --disable_query_log
35 while ($1)
36 {
37 eval insert into t1 values($1 + 4);
38 dec $1;
39 }
40 --enable_query_log
41 commit;
42 # Make sure binlog checkpoint is logged before DROP TABLE to avoid random
43 # result difference.
44 --source include/wait_for_binlog_checkpoint.inc
45 drop table t1;
46 --source include/show_binlog_events.inc
47 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
48 --source include/show_binlog_events.inc
49 --let $binlog_file=
50
51 #
52 # Bug#22540 - Incorrect value in column End_log_pos of
53 # SHOW BINLOG EVENTS using InnoDB
54 #
55
56 # the following tests will show that certain queries now return
57 # absolute offsets (from binlog start, rather than relative to
58 # the beginning of the current transaction). under what
59 # conditions it should be allowed / is sensible to put the
60 # slider into the middle of a transaction is not our concern
61 # here; we just guarantee that if and when it's done, the
62 # user has valid offsets to use. if the setter function still
63 # wants to throw a "positioning into middle of transaction"
64 # warning, that's its prerogative and handled elsewhere.
65
66 set @ac = @@autocommit;
67
68 # first show this to work for SHOW BINLOG EVENTS
69
70 set autocommit= 0;
71 reset master;
72 create table t1(n int) engine=innodb;
73 begin;
74 insert into t1 values (1);
75 insert into t1 values (2);
76 insert into t1 values (3);
77 commit;
78 drop table t1;
79 --source include/show_binlog_events.inc
80
81 # now show that nothing breaks if we need to read from the cache more
82 # than once, resulting in split event-headers
83
84 set @bcs = @@binlog_cache_size;
85 set global binlog_cache_size=4096;
86 reset master;
87
88 create table t1 (a int, b char(255)) engine=innodb;
89
90 flush global status;
91 show status like "binlog_cache_use";
92
93 let $1=100;
94 disable_query_log;
95 begin;
96 while ($1)
97 {
98 eval insert into t1 values( $1, 'just to fill void to make transaction occupying at least two buffers of the trans cache' );
99 dec $1;
100 }
101 commit;
102 --echo *** the following must show the counter value = 1 ***
103 show status like "binlog_cache_use";
104 enable_query_log;
105
106 --source include/show_binlog_events.inc
107
108 drop table t1;
109
110 set global binlog_cache_size=@bcs;
111 set session autocommit = @ac;
112
113 #
114 # Bug#33798: prepared statements improperly handle large unsigned ints
115 #
116 --disable_warnings
117 drop table if exists t1;
118 --enable_warnings
119 reset master;
120 create table t1 (a bigint unsigned, b bigint(20) unsigned);
121 prepare stmt from "insert into t1 values (?,?)";
122 set @a= 9999999999999999;
123 set @b= 14632475938453979136;
124 execute stmt using @a, @b;
125 deallocate prepare stmt;
126 drop table t1;
127 --source include/show_binlog_events.inc
128
129
130 #
131 # Bug #39182: Binary log producing incompatible character set query from
132 # stored procedure.
133 #
134 reset master;
135 CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
136 USE bug39182;
137 CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
138 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
139
140 DELIMITER //;
141
142 CREATE PROCEDURE p1()
143 BEGIN
144 DECLARE s1 VARCHAR(255);
145 SET s1= "test";
146 CREATE TEMPORARY TABLE tmp1
147 SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
148 SELECT
149 COLLATION(NAME_CONST('s1', _utf8'test')) c1,
150 COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
151 COLLATION(s1) c3,
152 COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
153 COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
154 COERCIBILITY(s1) d3;
155 DROP TEMPORARY TABLE tmp1;
156 END//
157
158 DELIMITER ;//
159
160 CALL p1();
161 source include/show_binlog_events.inc;
162
163 DROP PROCEDURE p1;
164 DROP TABLE t1;
165 DROP DATABASE bug39182;
166 USE test;
167
168 #
169 # Bug#35383: binlog playback and replication breaks due to
170 # name_const substitution
171 #
172 DELIMITER //;
173 CREATE PROCEDURE p1(IN v1 INT)
174 BEGIN
175 CREATE TABLE t1 SELECT v1;
176 DROP TABLE t1;
177 END//
178 CREATE PROCEDURE p2()
179 BEGIN
180 DECLARE v1 INT;
181 CREATE TABLE t1 SELECT v1+1;
182 DROP TABLE t1;
183 END//
184 CREATE PROCEDURE p3(IN v1 INT)
185 BEGIN
186 CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0;
187 DROP TABLE t1;
188 END//
189 CREATE PROCEDURE p4(IN v1 INT)
190 BEGIN
191 DECLARE v2 INT;
192 CREATE TABLE t1 SELECT 1, v1, v2;
193 DROP TABLE t1;
194 CREATE TABLE t1 SELECT 1, v1+1, v2;
195 DROP TABLE t1;
196 END//
197 DELIMITER ;//
198
199 CALL p1(1);
200 CALL p2();
201 CALL p3(0);
202 CALL p4(0);
203 DROP PROCEDURE p1;
204 DROP PROCEDURE p2;
205 DROP PROCEDURE p3;
206 DROP PROCEDURE p4;
207
208 --echo End of 5.0 tests
209
210 # Test of a too big SET INSERT_ID.
211 # This should generate an error and should not be put in binlog
212 # We look at the binlog further down with SHOW BINLOG EVENTS.
213
214 reset master;
215 create table t1 (id tinyint auto_increment primary key);
216 insert into t1 values(5);
217 set insert_id=128;
218 --error HA_ERR_AUTOINC_ERANGE
219 insert into t1 values(null) /* Not binlogged */;
220
221 # The followin insert ignore will be put in binlog
222 set insert_id=128;
223 insert ignore into t1 values(null) /* Insert 128 */;
224
225 # Insert with duplicate key error should not go into binglo
226 set insert_id=5;
227 --error ER_DUP_ENTRY
228 insert into t1 values(null) /* Not binlogged */;
229
230 # Insert with autogenerated key + duplicate key error should go into binlog
231 set insert_id=5;
232 insert ignore into t1 values(null) /* Insert 5 */;
233 select * from t1;
234 drop table t1;
235
236 # Same tests but with 2 rows inserted at a time
237
238 create table t1 (id tinyint auto_increment primary key) engine=myisam;
239 set insert_id=128;
240 --error HA_ERR_AUTOINC_ERANGE
241 insert into t1 values(5),(null) /* Insert_id 128 */;
242
243 # The followin insert ignore will be put in binlog
244 set insert_id=128;
245 insert ignore into t1 values (4),(null) /* Insert_id 128 */;
246
247 # Insert with duplicate key error should not go into binglo
248 set insert_id=5;
249 --error ER_DUP_ENTRY
250 insert into t1 values(3),(null) /* Insert_id 5 */;
251
252 # Insert with autogenerated key + duplicate key error should go into binlog
253 set insert_id=5;
254 insert ignore into t1 values(2),(null) /* Insert_id 5 */;
255 select * from t1 order by id;
256 drop table t1;
257
258
259 # bug#22027
260 create table t1 (a int);
261 create table if not exists t2 select * from t1;
262
263 # bug#22762
264 create temporary table tt1 (a int);
265 create table if not exists t3 like tt1;
266
267 # BUG#25091 (A DELETE statement to mysql database is not logged with
268 # ROW mode format): Checking that some basic operations on tables in
269 # the mysql database is replicated even when the current database is
270 # 'mysql'.
271
272 USE mysql;
273 INSERT db SET host='localhost', user='@#@', db='Just a test';
274 UPDATE db SET db='Another db' WHERE host='localhost' AND user='@#@';
275 DELETE FROM db WHERE host='localhost' AND user='@#@';
276
277 use test;
278 source include/show_binlog_events.inc;
279 drop table t1,t2,t3,tt1;
280
281 -- source suite/binlog/include/binlog_insert_delayed.test
282
283 #Bug #26079 max_binlog_size + innodb = not make new binlog and hang server
284 # server should not hang, binlog must rotate in the end
285 reset master;
286 --disable_warnings
287 drop table if exists t3;
288 --enable_warnings
289 create table t3 (a int(11) NOT NULL AUTO_INCREMENT, b text, PRIMARY KEY (a) ) engine=innodb;
290 --let $binlog_file1= query_get_value(SHOW MASTER STATUS, File, 1)
291 --echo File $binlog_file1
292 let $it=4;
293 while ($it)
294 {
295 insert into t3(b) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
296 dec $it;
297 }
298 --let $binlog_file2= query_get_value(SHOW MASTER STATUS, File, 1)
299 --echo *** show new binlog index after rotating ***
300 --echo File $binlog_file2
301 drop table t3;
302
303 --echo #
304 --echo # Bug #45998: database crashes when running "create as select"
305 --echo #
306 CREATE DATABASE test1;
307 USE test1;
308 DROP DATABASE test1;
309 CREATE TABLE test.t1(a int);
310 INSERT INTO test.t1 VALUES (1), (2);
311 CREATE TABLE test.t2 SELECT * FROM test.t1;
312 USE test;
313 DROP TABLES t1, t2;
314
315 #
316 # Bug#46640
317 # This test verifies if the server_id stored in the "format
318 # description BINLOG statement" will override the server_id
319 # of the server executing the statements.
320 #
321
322 connect (fresh,localhost,root,,test);
323 connection fresh;
324
325 RESET MASTER;
326 CREATE TABLE t1 (a INT PRIMARY KEY);
327
328 # Format description event, with server_id = 10;
329 BINLOG '
330 3u9kSA8KAAAAZgAAAGoAAAABAAQANS4xLjM1LW1hcmlhLWJldGExLWRlYnVnLWxvZwAAAAAAAAAA
331 AAAAAAAAAAAAAAAAAADe72RIEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
332 ';
333
334 # What server_id is logged for a statement? Should be our own, not the
335 # one from the format description event.
336 INSERT INTO t1 VALUES (1);
337
338 # INSERT INTO t1 VALUES (2), with server_id=20. Check that this is logged
339 # with our own server id, not the 20 from the BINLOG statement.
340 BINLOG '
341 3u9kSBMUAAAAKQAAAJEBAAAAABoAAAAAAAAABHRlc3QAAnQxAAEDAAA=
342 3u9kSBcUAAAAIgAAALMBAAAQABoAAAAAAAEAAf/+AgAAAA==
343 ';
344
345 # Show binlog events to check that server ids are correct.
346 --replace_column 1 # 2 # 5 #
347 --replace_regex /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/
348 SHOW BINLOG EVENTS;
349
350 DROP TABLE t1;
351
352 --echo
353 --echo # BUG#54903 BINLOG statement toggles session variables
354 --echo # ----------------------------------------------------------------------
355 --echo # This test verify that BINLOG statement doesn't change current session's
356 --echo # variables foreign_key_checks and unique_checks.
357 --echo
358 CREATE TABLE t1 (c1 INT KEY);
359
360 SET @@SESSION.foreign_key_checks= ON;
361 SET @@SESSION.unique_checks= ON;
362
363 --echo # INSERT INTO t1 VALUES (1)
364 --echo # foreign_key_checks=0 and unique_checks=0
365 BINLOG '
366 dfLtTBMBAAAAKQAAANcAAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
367 dfLtTBcBAAAAIgAAAPkAAAAAABcAAAAAAAcAAf/+AQAAAA==
368 ';
369
370 SELECT * FROM t1;
371 --echo # Their values should be ON
372 SHOW SESSION VARIABLES LIKE "foreign_key_checks";
373 SHOW SESSION VARIABLES LIKE "unique_checks";
374
375 --echo
376 SET @@SESSION.foreign_key_checks= OFF;
377 SET @@SESSION.unique_checks= OFF;
378
379 --echo # INSERT INTO t1 VALUES(2)
380 --echo # foreign_key_checks=1 and unique_checks=1
381 BINLOG '
382 dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
383 dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
384 ';
385
386 SELECT * FROM t1;
387 --echo # Their values should be OFF
388 SHOW SESSION VARIABLES LIKE "foreign_key_checks";
389 SHOW SESSION VARIABLES LIKE "unique_checks";
390
391 --echo # INSERT INTO t1 VALUES(2)
392 --echo # foreign_key_checks=1 and unique_checks=1
393 --echo # It should not change current session's variables, even error happens
394 call mtr.add_suppression("BINLOG_BASE64_EVENT.*Could not execute Write_rows_v1 event on table test.t1; Duplicate entry .2. for key .PRIMARY., Error_code: 1062");
395 --error 1062
396 BINLOG '
397 dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
398 dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
399 ';
400
401 SELECT * FROM t1;
402 --echo # Their values should be OFF
403 SHOW SESSION VARIABLES LIKE "foreign_key_checks";
404 SHOW SESSION VARIABLES LIKE "unique_checks";
405
406 DROP TABLE t1;
407
408 disconnect fresh;
409 connection default;
410
411 --echo #
412 --echo # MDEV-25595 DROP part of failed CREATE OR REPLACE is not written into binary log
413 --echo #
414 reset master;
415 --error ER_DUP_FIELDNAME
416 create table t as select 1 as b, 2 as b;
417 create table t (old_table_field int);
418 --error ER_DUP_FIELDNAME
419 create or replace table t as select 1 as b, 2 as b;
420 --error ER_DUP_FIELDNAME
421 create or replace temporary table t as select 1 as b, 2 as b;
422 create table t (new_table_field int);
423
424 --source include/show_binlog_events.inc
425
426 # cleanup
427 drop table t;