3
# Verifies that empty transactions are generated when they are
4
# supposed to. There are three cases:
6
# 1. Transaction is filtered out on slave
8
# 2. Transaction does not have any effect, gets logged and assigned a
9
# GTID on master because master uses binlog_format=statement, but
10
# does not get logged on slave because slave uses binlog_format=row.
12
# 3. Transaction is explicitly made empty on master by setting
13
# gtid_next and executing a COMMIT.
15
# Moreover, verify that no empty transaction is generated for rolled
18
# ==== Implementation ====
20
# For case 1, we use a filter (specified in the .cnf file) to filter
21
# out table t_ignore, and verify that empty transaction is generated
22
# on slave. Do this both for (1A) base tables and for (1B) temporary
25
# For case 2, we use binlog_format=statement on master and
26
# binlog_format=row on the first slave. We test two cases of
27
# transactions that are not logged in row format: (2A) temporary table
28
# DDL and DML and (2B) an UPDATE statement that matches no row. We
29
# use a second slave where the effect of the statement would be seen,
30
# if it was not logged as an empty statement. In case (2A) we check
31
# that slave_open_temp_tables is still 0 on the second slave. In case
32
# (2B) we do an UPDATE on master that matches no rows on master, but
33
# that would match a row on the second slave.
35
# For case 3, we just execute an explicit empty transaction on master
36
# and verify that it gets assigned a GTID and gets replicated to the
39
# To verify nothing is logged for rollback transactions, we execute a
40
# DDL statement that generates an error, a DML statement that
41
# generates an error, and an explicit ROLLBACK transaction, and verify
42
# that nothing got logged for the three statements.
44
# ==== References ====
46
# WL#3584 - Global Transaction Identifiers (GTIDs)
47
# - The base worklog implementing empty transactions.
48
# BUG#18145032 - NO EMPTY TRANSACTION IS CREATED FOR A FILTERED CREATE TEMPORARY TABLE WITH GTIDS
49
# - Addressing the special cases for CREATE/ALTER/DROP TEMPORARY.
50
# BUG#18095502 - RESTART OF SLAVE INSTANCE FAIL IN GTID REPLICATION IF WE USE REPLICATE-IGNORE-DB
51
# - Addressing database filters in the applier thread.
53
--let $rpl_topology= 1->2->3
54
--let $rpl_gtid_utils= 1
55
--source include/rpl_init.inc
56
--source include/rpl_default_connections.inc
57
--source include/not_embedded.inc
58
--source include/have_binlog_format_mixed.inc
59
--source include/have_gtid.inc
61
--source include/rpl_reset.inc
63
--echo ==== Case 1A: Transaction filtered out ====
65
--source include/gtid_step_reset.inc
67
CREATE TABLE t_ignore(id INT);
68
INSERT INTO t_ignore VALUES (1);
71
CREATE TABLE t_ignore_wild(id INT);
72
INSERT INTO t_ignore_wild VALUES (1);
73
DROP TABLE t_ignore_wild;
75
# Verify that 6 GTIDs were generated.
76
--let $gtid_step_count= 6
77
--source include/gtid_step_assert.inc
79
# Verify that the GTIDs make it to every slave
80
--source include/rpl_sync.inc
82
--echo ==== Case 1B: CREATE/ALTER/DROP TEMPORARY filtered out ====
84
CREATE TEMPORARY TABLE t_ignore(a INT);
85
ALTER TABLE t_ignore ADD COLUMN b INT;
86
INSERT INTO t_ignore VALUES (1, 2);
87
DROP TEMPORARY TABLE t_ignore;
89
CREATE TEMPORARY TABLE t_ignore_wild(a INT);
90
ALTER TABLE t_ignore_wild ADD COLUMN b INT;
91
INSERT INTO t_ignore_wild VALUES (1, 2);
92
DROP TEMPORARY TABLE t_ignore_wild;
94
# Verify that 8 GTIDs were generated.
95
--let $gtid_step_count= 8
96
--source include/gtid_step_assert.inc
98
# Verify that the GTIDs make it to every slave.
99
--source include/rpl_sync.inc
101
--echo ==== Case 1C: database filters on slave applier ====
103
--source include/gtid_step_reset.inc
105
# These two will be ignored on first slave.
106
CREATE DATABASE db_ignore;
108
CREATE TABLE t1 (a INT);
109
INSERT INTO t1 VALUES (1);
112
# Verify 2 GTIDs were generated
113
--let $gtid_step_count= 3
114
--source include/gtid_step_assert.inc
116
--source include/rpl_sync.inc
118
--let $assert_text= db_ignore should not be created on slave
119
--let $assert_cond= "[slave:SHOW DATABASES LIKE "db_ignore"]" = ""
120
--source include/assert.inc
122
--let $assert_text= db_ignore should not be created on second slave
123
--let $assert_cond= "[server_3:SHOW DATABASES LIKE "db_ignore"]" = ""
124
--source include/assert.inc
126
DROP DATABASE db_ignore;
127
--source include/rpl_sync.inc
129
--echo ==== Case 1D: database filters on slave binary log ====
131
--source include/gtid_step_reset.inc
133
# These two will be ignored on first slave.
134
CREATE DATABASE db_binlog_ignore;
135
USE db_binlog_ignore;
136
CREATE TABLE t1 (a INT);
137
INSERT INTO t1 VALUES (1);
140
# Verify 2 GTIDs were generated
141
--let $gtid_step_count= 3
142
--source include/gtid_step_assert.inc
144
--source include/rpl_sync.inc
146
--let $assert_text= db_binlog_ignore should not be created on slave
147
--let $assert_cond= "[slave:SHOW DATABASES LIKE "db_binlog_ignore"]" = "db_binlog_ignore"
148
--source include/assert.inc
150
--let $assert_text= db_binlog_ignore should not be created on second slave
151
--let $assert_cond= "[server_3:SHOW DATABASES LIKE "db_binlog_ignore"]" = ""
152
--source include/assert.inc
154
DROP DATABASE db_binlog_ignore;
155
--source include/rpl_sync.inc
157
--echo ==== Case 2A: temp table transaction not logged in row format ====
159
--echo ---- Initialize ----
162
SET @save.binlog_format= @@global.binlog_format;
163
SET @@global.binlog_format= row;
164
source include/restart_slave_sql.inc;
166
--echo ---- Test ----
168
--source include/gtid_step_reset.inc
171
# - Master will log these statements and assign them GTIDs, since
172
# master uses binlog_format=statement.
174
# - First slave will not log any change since it uses row format, but
175
# it should log empty transactions with GTIDs.
177
# - Second slave should also not log any change, since it receives an
178
# empty transaction from the first slave. Thus
179
# Slave_open_temp_tables should remain 0 on the second slave.
180
CREATE TEMPORARY TABLE t2 (a INT);
181
ALTER TABLE t2 ADD COLUMN b INT;
182
INSERT INTO t2 VALUES (1, 2);
184
# assert exactly 3 GTIDs were generated
185
--let $gtid_step_count= 3
186
--source include/gtid_step_assert.inc
188
# Verify that the GTID gets replicated everywhere.
190
--source include/rpl_sync.inc
192
# Verify temp tables on first slave but not on second slave
193
--let $assert_text= First slave should not have created any temp table
194
--let $assert_cond= [slave:SHOW GLOBAL STATUS LIKE 'Slave_open_temp_tables'] = 1
196
--let $assert_text= Second slave should not have created any temp table
197
--let $assert_cond= [server_3:SHOW GLOBAL STATUS LIKE 'Slave_open_temp_tables'] = 0
199
--echo ---- Clean up ----
201
DROP TEMPORARY TABLE t2;
202
--source include/rpl_sync.inc
205
SET @@global.binlog_format= @save.binlog_format;
208
--echo ==== Case 2B: transaction with no effect not logged in row format ====
210
--echo ---- Initialize ----
213
SET @save.binlog_format= @@global.binlog_format;
214
SET @@global.binlog_format= row;
215
source include/restart_slave_sql.inc;
218
SET @@session.binlog_format= statement;
219
CREATE TABLE t1(id INT) ENGINE = InnoDB;
220
--source include/rpl_sync.inc
221
--connection server_3
222
INSERT INTO t1 VALUES (0);
225
--echo ---- Test ----
227
# - Master will log this and assign it a GTID despite it has no
228
# effect, since master uses binlog_format=statement.
230
# - First slave will not log any change since it uses row format, but
231
# it should log an empty transaction with a GTID.
233
# - Second slave should also not log any change, since it receives an
234
# empty transaction from the first slave. It should also not update
235
# the table despite the original SQL statement would match a row on
236
# the second slave, since it only receives the empty transaction
237
# from the first slave.
238
UPDATE t1 SET id= 1 WHERE id = 0;
240
# Verify that the GTID gets replicated everywhere.
242
--source include/rpl_sync.inc
244
--let $assert_text= Second slave should not have done any update
245
--let $assert_cond= [server_3:SELECT * FROM t1] = 0
247
-- echo ---- Clean up ----
250
--source include/rpl_sync.inc
253
SET @@global.binlog_format= @save.binlog_format;
256
--echo ==== Case 3: explicit empty transaction on master ====
258
--source include/gtid_step_reset.inc
259
eval SET @@SESSION.GTID_NEXT = '$uuida:1';
261
SET @@SESSION.GTID_NEXT = 'AUTOMATIC';
263
# Verify exactly one GTID was generated
264
--let $gtid_step_count= 1
265
--let $gtid_step_only_count= 1
266
--source include/gtid_step_assert.inc
267
--let $gtid_step_only_count= 0
269
# Verify the GTID makes it to every slave.
270
--source include/rpl_sync.inc
272
--echo ==== Case 4: Nothing logged for ROLLBACK transactions ====
274
# Test that ROLLBACK does not generate any GTID
276
--echo ---- Initialize ----
278
CREATE TABLE t1 (a INT) ENGINE = InnoDB;
280
--echo ---- Test ----
282
--source include/gtid_step_reset.inc
284
--error ER_BAD_TABLE_ERROR
287
--error ER_WRONG_VALUE_COUNT_ON_ROW
288
INSERT INTO t1 VALUES (1, 1);
291
INSERT INTO t1 VALUES (2);
294
--let $gtid_step_count= 0
295
--source include/gtid_step_assert.inc
297
--echo ---- Clean Up ----
301
--source include/rpl_end.inc
added
removed
mysql-test/suite/rpl/t/rpl_gtid_empty_transaction.test
