Debian Codegraph @

mysql-test/suite/binlog/t/binlog_mysqlbinlog_row_trans.test

raw
1 # mysqlbinlog_trans.test
2 #
3 # Show that mysqlbinlog work correctly with transactions.
4 #
5
6 #--source include/have_myisam.inc
7 --let $engine_type_nontrans= MyISAM
8 --source include/have_innodb.inc
9 --let $engine_type= InnoDB
10
11 #
12 # The test case would also work with statement based or mixed mode logging.
13 # But this would require different result files. To handle this with the
14 # current test suite, new main test cases are required.
15 #
16 --source include/have_binlog_format_row.inc
17
18 --source include/have_log_bin.inc
19
20 --echo #
21 --echo # Preparatory cleanup.
22 --echo #
23 --disable_warnings
24 DROP TABLE IF EXISTS t1, t2;
25 --enable_warnings
26
27 --echo #
28 --echo # We need a fixed timestamp to avoid varying results.
29 --echo #
30 SET timestamp=1000000000;
31
32 --echo #
33 --echo # Delete all existing binary logs.
34 --echo #
35 RESET MASTER;
36
37 --echo #
38 --echo # Create test tables.
39 --echo #
40 eval CREATE TABLE t1 (
41 c1 INT,
42 c2 VARCHAR(20)
43 ) ENGINE=$engine_type DEFAULT CHARSET latin1;
44 eval CREATE TABLE t2 (
45 c1 INT,
46 c2 VARCHAR(20)
47 ) ENGINE=$engine_type_nontrans DEFAULT CHARSET latin1;
48
49 --echo #
50 --echo # Start transaction #1, transactional table only, commit.
51 --echo #
52 START TRANSACTION;
53
54 --echo #
55 --echo # Do some statements.
56 --echo #
57 INSERT INTO t1 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
58 UPDATE t1 SET c1 = c1 + 10;
59 DELETE FROM t1 WHERE c1 = 12;
60
61 --echo #
62 --echo # Commit transaction.
63 --echo #
64 COMMIT;
65 SELECT * FROM t1;
66 TRUNCATE TABLE t1;
67
68 --echo #
69 --echo # Start transaction #2, transactional table only, rollback.
70 --echo #
71 START TRANSACTION;
72
73 --echo #
74 --echo # Do some statements.
75 --echo #
76 INSERT INTO t1 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
77 UPDATE t1 SET c1 = c1 + 10;
78 DELETE FROM t1 WHERE c1 = 12;
79
80 --echo #
81 --echo # Rollback transaction.
82 --echo #
83 ROLLBACK;
84 SELECT * FROM t1;
85 TRUNCATE TABLE t1;
86
87 --echo #
88 --echo # Start transaction #3, both tables, commit.
89 --echo #
90 START TRANSACTION;
91
92 --echo #
93 --echo # Do some statements on the transactional table.
94 --echo #
95 INSERT INTO t1 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
96 UPDATE t1 SET c1 = c1 + 10;
97 DELETE FROM t1 WHERE c1 = 12;
98
99 --echo #
100 --echo # Do some statements on the non-transactional table.
101 --echo #
102 INSERT INTO t2 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
103 UPDATE t2 SET c1 = c1 + 10;
104 DELETE FROM t2 WHERE c1 = 12;
105
106 --echo #
107 --echo # Commit transaction.
108 --echo #
109 COMMIT;
110 SELECT * FROM t1;
111 SELECT * FROM t2;
112 TRUNCATE TABLE t1;
113 TRUNCATE TABLE t2;
114
115 --echo #
116 --echo # Start transaction #4, both tables, rollback.
117 --echo #
118 START TRANSACTION;
119
120 --echo #
121 --echo # Do some statements on the transactional table.
122 --echo #
123 INSERT INTO t1 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
124 UPDATE t1 SET c1 = c1 + 10;
125 DELETE FROM t1 WHERE c1 = 12;
126
127 --echo #
128 --echo # Do some statements on the non-transactional table.
129 --echo #
130 INSERT INTO t2 VALUES (1,'varchar-1'), (2,'varchar-2'), (3,'varchar-3');
131 UPDATE t2 SET c1 = c1 + 10;
132 DELETE FROM t2 WHERE c1 = 12;
133
134 --echo #
135 --echo # Rollback transaction.
136 --echo #
137 ROLLBACK;
138 SELECT * FROM t1;
139 SELECT * FROM t2;
140 TRUNCATE TABLE t1;
141 TRUNCATE TABLE t2;
142
143 --echo #
144 --echo # Flush all log buffers to the log file.
145 --echo #
146 FLUSH LOGS;
147
148 --echo #
149 --echo # Call mysqlbinlog to display the log file contents.
150 --echo #
151 let $MYSQLD_DATADIR= `select @@datadir`;
152 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
153 --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[a-zA-Z0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/ /xid=\d*/xid=<xid>/
154 --exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
155
156 --echo #
157 --echo # Cleanup.
158 --echo #
159 DROP TABLE t1, t2;