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
--replace_column 2 # 5 #
45
--replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\//
46
show binlog events in 'master-bin.000001' from 106;
47
--replace_column 2 # 5 #
48
--replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\//
49
show binlog events in 'master-bin.000002' from 106;
53
# Bug#22540 - Incorrect value in column End_log_pos of
54
# SHOW BINLOG EVENTS using InnoDB
57
# the following tests will show that certain queries now return
58
# absolute offsets (from binlog start, rather than relative to
59
# the beginning of the current transaction). under what
60
# conditions it should be allowed / is sensible to put the
61
# slider into the middle of a transaction is not our concern
62
# here; we just guarantee that if and when it's done, the
63
# user has valid offsets to use. if the setter function still
64
# wants to throw a "positioning into middle of transaction"
65
# warning, that's its prerogative and handled elsewhere.
67
set @ac = @@autocommit;
69
# first show this to work for SHOW BINLOG EVENTS
73
create table t1(n int) engine=innodb;
75
insert into t1 values (1);
76
insert into t1 values (2);
77
insert into t1 values (3);
80
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /Server ver: [^,]*,/Server version,/
81
show binlog events from 0;
83
# now show that nothing breaks if we need to read from the cache more
84
# than once, resulting in split event-headers
86
set @bcs = @@binlog_cache_size;
87
set global binlog_cache_size=4096;
90
create table t1 (a int) engine=innodb;
97
eval insert into t1 values( $1 );
103
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /Server ver: [^,]*,/Server version,/
104
show binlog events from 0;
108
set global binlog_cache_size=@bcs;
109
set session autocommit = @ac;
112
# Bug#33798: prepared statements improperly handle large unsigned ints
115
drop table if exists t1;
118
create table t1 (a bigint unsigned, b bigint(20) unsigned);
119
prepare stmt from "insert into t1 values (?,?)";
120
set @a= 9999999999999999;
121
set @b= 14632475938453979136;
122
execute stmt using @a, @b;
123
deallocate prepare stmt;
125
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /Server ver: [^,]*,/Server version,/
126
show binlog events from 0;
130
# Bug #39182: Binary log producing incompatible character set query from
134
CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
136
CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
137
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
141
CREATE PROCEDURE p1()
143
DECLARE s1 VARCHAR(255);
145
CREATE TEMPORARY TABLE tmp1
146
SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
148
COLLATION(NAME_CONST('s1', _utf8'test')) c1,
149
COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
151
COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
152
COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
154
DROP TEMPORARY TABLE tmp1;
160
source include/show_binlog_events.inc;
164
DROP DATABASE bug39182;
168
# Bug#35383: binlog playback and replication breaks due to
169
# name_const substitution
172
CREATE PROCEDURE p1(IN v1 INT)
174
CREATE TABLE t1 SELECT v1;
177
CREATE PROCEDURE p2()
180
CREATE TABLE t1 SELECT v1+1;
183
CREATE PROCEDURE p3(IN v1 INT)
185
CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0;
188
CREATE PROCEDURE p4(IN v1 INT)
191
CREATE TABLE t1 SELECT 1, v1, v2;
193
CREATE TABLE t1 SELECT 1, v1+1, v2;
207
--echo End of 5.0 tests
209
# Test of a too big SET INSERT_ID: see if the truncated value goes
210
# into binlog (right), or the too big value (wrong); we look at the
211
# binlog further down with SHOW BINLOG EVENTS.
213
create table t1 (id tinyint auto_increment primary key);
215
insert into t1 values(null);
220
create table t1 (a int);
221
create table if not exists t2 select * from t1;
224
create temporary table tt1 (a int);
225
create table if not exists t3 like tt1;
227
# BUG#25091 (A DELETE statement to mysql database is not logged with
228
# ROW mode format): Checking that some basic operations on tables in
229
# the mysql database is replicated even when the current database is
234
INSERT INTO user SET host='localhost', user='@#@', password=password('Just a test');
235
UPDATE user SET password=password('Another password') WHERE host='localhost' AND user='@#@';
236
DELETE FROM user WHERE host='localhost' AND user='@#@';
240
source include/show_binlog_events.inc;
241
drop table t1,t2,t3,tt1;
243
-- source extra/binlog_tests/binlog_insert_delayed.test
245
#Bug #26079 max_binlog_size + innodb = not make new binlog and hang server
246
# server should not hang, binlog must rotate in the end
249
drop table if exists t3;
251
create table t3 (a int(11) NOT NULL AUTO_INCREMENT, b text, PRIMARY KEY (a) ) engine=innodb;
256
insert into t3(b) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
259
show master status /* must show new binlog index after rotating */;
263
--echo # Bug #45998: database crashes when running "create as select"
265
CREATE DATABASE test1;
268
CREATE TABLE test.t1(a int);
269
INSERT INTO test.t1 VALUES (1), (2);
270
CREATE TABLE test.t2 SELECT * FROM test.t1;
276
# This test verifies if the server_id stored in the "format
277
# description BINLOG statement" will override the server_id
278
# of the server executing the statements.
281
connect (fresh,localhost,root,,test);
285
CREATE TABLE t1 (a INT PRIMARY KEY);
287
# Format description event, with server_id = 10;
289
3u9kSA8KAAAAZgAAAGoAAAABAAQANS4xLjM1LW1hcmlhLWJldGExLWRlYnVnLWxvZwAAAAAAAAAA
290
AAAAAAAAAAAAAAAAAADe72RIEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
293
# What server_id is logged for a statement? Should be our own, not the
294
# one from the format description event.
295
INSERT INTO t1 VALUES (1);
297
# INSERT INTO t1 VALUES (2), with server_id=20. Check that this is logged
298
# with our own server id, not the 20 from the BINLOG statement.
300
3u9kSBMUAAAAKQAAAJEBAAAAABoAAAAAAAAABHRlc3QAAnQxAAEDAAA=
301
3u9kSBcUAAAAIgAAALMBAAAQABoAAAAAAAEAAf/+AgAAAA==
304
# Show binlog events to check that server ids are correct.
305
--replace_column 1 # 2 # 5 #
306
--replace_regex /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/