Debian Codegraph @

mysql-test/suite/binlog_nogtid/t/binlog_mysqlbinlog_row_trans_myisam.test

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