1# Test that old binlog formats can be read.
2
3# Some previous versions of MySQL use their own binlog format,
4# especially in row-based replication.  This test uses saved binlogs
5# from those old versions to test that we can replicate from old
6# versions to the present version.
7
8# Replicating from old versions to new versions is necessary in an
9# online upgrade scenario, where the .
10
11# The previous versions we currently test are:
12#  - version 5.1.17 and earlier trees
13#  - mysql-5.1-wl2325-xxx trees (AKA alcatel trees)
14#  - mysql-5.1-telco-6.1 trees
15# For completeness, we also test mysql-5.1-new_rpl, which is supposed
16# to be the "correct" version.
17
18# All binlogs were generated with the same commands (listed at the end
19# of this test for reference).  The binlogs contain the following
20# events: Table_map, Write_rows, Update_rows, Delete_rows Query, Xid,
21# User_var, Int_var, Rand, Begin_load, Append_file, Execute_load.
22
23# Related bugs: BUG#27779, BUG#31581, BUG#31582, BUG#31583, BUG#32407
24
25source include/not_embedded.inc;
26
27--echo ==== Read binlog with v2 row events ====
28
29# Read binlog.
30--exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_trunk_row_v2.001 | $MYSQL --local-infile=1
31# Show result.
32SELECT * FROM t1 ORDER BY a;
33SELECT * FROM t2 ORDER BY a;
34SELECT COUNT(*) FROM t3;
35# Reset.
36DROP TABLE t1, t2, t3;
37
38
39--echo ==== Read modern binlog (version 5.1.23) ====
40
41# Read binlog.
42--exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_5_1_23.001 | $MYSQL --local-infile=1
43# Show result.
44SELECT * FROM t1 ORDER BY a;
45SELECT * FROM t2 ORDER BY a;
46SELECT COUNT(*) FROM t3;
47# Reset.
48DROP TABLE t1, t2, t3;
49
50
51--echo ==== Read binlog from version 5.1.17 ====
52
53# Read binlog.
54--exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_5_1_17.001 | $MYSQL --local-infile=1
55# Show result.
56SELECT * FROM t1 ORDER BY a;
57SELECT * FROM t2 ORDER BY a;
58SELECT COUNT(*) FROM t3;
59# Reset.
60DROP TABLE t1, t2, t3;
61
62
63--echo ==== Read binlog from version 4.1 ====
64
65# In this version, neither row-based binlogging nor Xid events
66# existed, so the binlog was generated without the "row-based tests"
67# part and the "get xid event" part, and it does not create table t2.
68
69# Read binlog.
70--exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/binlog_old_version_4_1.000001 | $MYSQL --local-infile=1
71# Show result.
72SELECT * FROM t1 ORDER BY a;
73SELECT COUNT(*) FROM t3;
74# Reset.
75DROP TABLE t1, t3;
76
77
78--echo ==== Read binlog from telco tree (mysql-5.1-telco-6.1) ====
79
80# Read binlog.
81--exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_5_1-telco.001 | $MYSQL --local-infile=1
82# Show resulting tablea.
83SELECT * FROM t1 ORDER BY a;
84SELECT * FROM t2 ORDER BY a;
85SELECT COUNT(*) FROM t3;
86# Reset.
87DROP TABLE t1, t2, t3;
88
89
90#### The following commands were used to generate the binlogs ####
91#
92#source include/master-slave.inc;
93#
94## ==== initialize ====
95#USE test;
96#CREATE TABLE t1 (a int, b char(50)) ENGINE = MyISAM;
97#CREATE TABLE t2 (a int, b char(50)) ENGINE = InnoDB;
98#CREATE TABLE t3 (a char(20));
99#
100#
101## ==== row based tests ====
102#SET BINLOG_FORMAT='row';
103#
104## ---- get write, update, and delete rows events ----
105#INSERT INTO t1 VALUES (0, 'one'), (1, 'two');
106#UPDATE t1 SET a=a+1;
107#DELETE FROM t1 WHERE a=2;
108#
109#
110## ==== statement based tests ====
111#SET BINLOG_FORMAT = 'statement';
112#
113## ---- get xid events ----
114#BEGIN;
115#INSERT INTO t2 VALUES (3, 'first stm in trx');
116#INSERT INTO t1 VALUES (3, 'last stm in trx: next event should be xid');
117#COMMIT;
118#
119## ---- get user var events ----
120#SET @x = 4;
121#INSERT INTO t1 VALUES (@x, 'four');
122#
123## ---- get rand event ----
124#INSERT INTO t1 VALUES (RAND() * 1000000, 'random');
125#
126## ---- get intvar event ----
127#INSERT INTO t1 VALUES (LAST_INSERT_ID(), 'last_insert_id');
128#
129## ---- get begin, append and execute load events ----
130## double the file until we have more than 2^17 bytes, so that the
131## event has to be split and we can use Append_file_log_event.
132#
133#SET SQL_LOG_BIN=0;
134#CREATE TABLE temp (a char(20));
135#LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE temp;
136#INSERT INTO temp SELECT * FROM temp;
137#INSERT INTO temp SELECT * FROM temp;
138#INSERT INTO temp SELECT * FROM temp;
139#INSERT INTO temp SELECT * FROM temp;
140#INSERT INTO temp SELECT * FROM temp;
141#INSERT INTO temp SELECT * FROM temp;
142#INSERT INTO temp SELECT * FROM temp;
143#INSERT INTO temp SELECT * FROM temp;
144#SELECT a FROM temp INTO OUTFILE 'big_file.dat';
145#DROP TABLE temp;
146#SET SQL_LOG_BIN=1;
147#
148#LOAD DATA INFILE 'big_file.dat' INTO TABLE t3;
149#
150#SELECT * FROM t1 ORDER BY a;
151#SELECT * FROM t2 ORDER BY a;
152#SELECT COUNT(*) FROM t3;
153#--source include/rpl_end.inc
154