2 # misc binlogging tests that do not require a slave running
5 -- source include/have_log_bin.inc
6 -- source include/not_embedded.inc
7 -- source include/have_innodb.inc
8 -- source include/have_debug.inc
11 drop table if exists t1, t2;
15 create table t1 (a int) engine=innodb;
16 create table t2 (a int) engine=innodb;
23 # first COMMIT must be Query_log_event, second - Xid_log_event
24 source include/show_binlog_events.inc;
28 # binlog rotation after one big transaction
33 create table t1 (n int) engine=innodb;
38 eval insert into t1 values($1 + 4);
44 --source include/show_binlog_events.inc
45 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
46 --source include/show_binlog_events.inc
50 # Bug#22540 - Incorrect value in column End_log_pos of
51 # SHOW BINLOG EVENTS using InnoDB
54 # the following tests will show that certain queries now return
55 # absolute offsets (from binlog start, rather than relative to
56 # the beginning of the current transaction). under what
57 # conditions it should be allowed / is sensible to put the
58 # slider into the middle of a transaction is not our concern
59 # here; we just guarantee that if and when it's done, the
60 # user has valid offsets to use. if the setter function still
61 # wants to throw a "positioning into middle of transaction"
62 # warning, that's its prerogative and handled elsewhere.
64 set @ac = @@autocommit;
66 # first show this to work for SHOW BINLOG EVENTS
70 create table t1(n int) engine=innodb;
72 insert into t1 values (1);
73 insert into t1 values (2);
74 insert into t1 values (3);
77 --source include/show_binlog_events.inc
79 # now show that nothing breaks if we need to read from the cache more
80 # than once, resulting in split event-headers
82 set @bcs = @@binlog_cache_size;
83 set global binlog_cache_size=4096;
86 create table t1 (a int) engine=innodb;
93 eval insert into t1 values( $1 );
99 --source include/show_binlog_events.inc
103 set global binlog_cache_size=@bcs;
104 set session autocommit = @ac;
107 # Bug#33798: prepared statements improperly handle large unsigned ints
110 drop table if exists t1;
113 create table t1 (a bigint unsigned, b bigint(20) unsigned);
114 prepare stmt from "insert into t1 values (?,?)";
115 set @a= 9999999999999999;
116 set @b= 14632475938453979136;
117 execute stmt using @a, @b;
118 deallocate prepare stmt;
120 --source include/show_binlog_events.inc
124 # Bug #39182: Binary log producing incompatible character set query from
128 CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
130 CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
131 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
135 CREATE PROCEDURE p1()
137 DECLARE s1 VARCHAR(255);
139 CREATE TEMPORARY TABLE tmp1
140 SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
142 COLLATION(NAME_CONST('s1', _utf8'test')) c1,
143 COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
145 COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
146 COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
148 DROP TEMPORARY TABLE tmp1;
154 source include/show_binlog_events.inc;
158 DROP DATABASE bug39182;
162 # Bug#35383: binlog playback and replication breaks due to
163 # name_const substitution
166 CREATE PROCEDURE p1(IN v1 INT)
168 CREATE TABLE t1 SELECT v1;
171 CREATE PROCEDURE p2()
174 CREATE TABLE t1 SELECT v1+1;
177 CREATE PROCEDURE p3(IN v1 INT)
179 CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0;
182 CREATE PROCEDURE p4(IN v1 INT)
185 CREATE TABLE t1 SELECT 1, v1, v2;
187 CREATE TABLE t1 SELECT 1, v1+1, v2;
201 --echo End of 5.0 tests
203 # Test of a too big SET INSERT_ID: see if the truncated value goes
204 # into binlog (right), or the too big value (wrong); we look at the
205 # binlog further down with SHOW BINLOG EVENTS.
207 create table t1 (id tinyint auto_increment primary key);
209 insert into t1 values(null);
214 create table t1 (a int);
215 create table if not exists t2 select * from t1;
218 create temporary table tt1 (a int);
219 create table if not exists t3 like tt1;
221 # BUG#25091 (A DELETE statement to mysql database is not logged with
222 # ROW mode format): Checking that some basic operations on tables in
223 # the mysql database is replicated even when the current database is
228 INSERT INTO user SET host='localhost', user='@#@', password=password('Just a test');
229 UPDATE user SET password=password('Another password') WHERE host='localhost' AND user='@#@';
230 DELETE FROM user WHERE host='localhost' AND user='@#@';
234 source include/show_binlog_events.inc;
235 drop table t1,t2,t3,tt1;
237 -- source extra/binlog_tests/binlog_insert_delayed.test
239 #Bug #26079 max_binlog_size + innodb = not make new binlog and hang server
240 # server should not hang, binlog must rotate in the end
243 drop table if exists t3;
245 create table t3 (a int(11) NOT NULL AUTO_INCREMENT, b text, PRIMARY KEY (a) ) engine=innodb;
246 source include/show_master_status.inc;
250 insert into t3(b) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
253 # must show new binlog index after rotating;
254 source include/show_master_status.inc;
258 --echo # Bug #45998: database crashes when running "create as select"
260 CREATE DATABASE test1;
263 CREATE TABLE test.t1(a int);
264 INSERT INTO test.t1 VALUES (1), (2);
265 CREATE TABLE test.t2 SELECT * FROM test.t1;
271 # This test verifies if the server_id stored in the "format
272 # description BINLOG statement" will override the server_id
273 # of the server executing the statements.
276 connect (fresh,localhost,root,,test);
280 CREATE TABLE t1 (a INT PRIMARY KEY);
282 # Format description event, with server_id = 10;
284 3u9kSA8KAAAAZgAAAGoAAAABAAQANS4xLjM1LW1hcmlhLWJldGExLWRlYnVnLWxvZwAAAAAAAAAA
285 AAAAAAAAAAAAAAAAAADe72RIEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
288 # What server_id is logged for a statement? Should be our own, not the
289 # one from the format description event.
290 INSERT INTO t1 VALUES (1);
292 # INSERT INTO t1 VALUES (2), with server_id=20. Check that this is logged
293 # with our own server id, not the 20 from the BINLOG statement.
295 3u9kSBMUAAAAKQAAAJEBAAAAABoAAAAAAAAABHRlc3QAAnQxAAEDAAA=
296 3u9kSBcUAAAAIgAAALMBAAAQABoAAAAAAAEAAf/+AgAAAA==
299 # Show binlog events to check that server ids are correct.
300 --replace_column 1 # 2 # 5 #
301 --replace_regex /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/
307 --echo # BUG#54903 BINLOG statement toggles session variables
308 --echo # ----------------------------------------------------------------------
309 --echo # This test verify that BINLOG statement doesn't change current session's
310 --echo # variables foreign_key_checks and unique_checks.
312 CREATE TABLE t1 (c1 INT KEY);
314 SET @@SESSION.foreign_key_checks= ON;
315 SET @@SESSION.unique_checks= ON;
317 --echo # INSERT INTO t1 VALUES (1)
318 --echo # foreign_key_checks=0 and unique_checks=0
320 dfLtTBMBAAAAKQAAANcAAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
321 dfLtTBcBAAAAIgAAAPkAAAAAABcAAAAAAAcAAf/+AQAAAA==
325 --echo # Their values should be ON
326 SHOW SESSION VARIABLES LIKE "%_checks";
329 SET @@SESSION.foreign_key_checks= OFF;
330 SET @@SESSION.unique_checks= OFF;
332 --echo # INSERT INTO t1 VALUES(2)
333 --echo # foreign_key_checks=1 and unique_checks=1
335 dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
336 dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
340 --echo # Their values should be OFF
341 SHOW SESSION VARIABLES LIKE "%_checks";
343 --echo # INSERT INTO t1 VALUES(2)
344 --echo # foreign_key_checks=1 and unique_checks=1
345 --echo # It should not change current session's variables, even error happens
346 call mtr.add_suppression("Slave SQL.*Could not execute Write_rows event on table test.t1; Duplicate entry .2. for key .PRIMARY., Error_code: 1062");
349 dfLtTBMBAAAAKQAAAKsBAAAAABcAAAAAAAEABHRlc3QAAnQxAAEDAAE=
350 dfLtTBcBAAAAIgAAAM0BAAAAABcAAAAAAAEAAf/+AgAAAA==
354 --echo # Their values should be OFF
355 SHOW SESSION VARIABLES LIKE "%_checks";