1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
|
# ==== Purpose ====
#
# Verify that in statement based replication, no operation update query
# is not logged into binlog if the read_only mode is ON.
#
#==== Requirements ====
#
# Ongoing DML transactions that write the binary log cannot be committed
# in read-only modes, even when not writing to the database.
#
# This shall hold in the following cases:
#
#- super_read_only=1 and user is SUPER
#- read_only=1 and user is non-SUPER
#- transaction contains no-op UPDATE
#- transaction contains SELECT that may use implicit temporary table
#- transaction contains a no-op UPDATE and temporary table INSERT
#
# ==== Implementation ====
#
# Test 1
# 1. Create a table 't1' and insert a row in it.
# 2. Begin a transaction and execute noop UPDATE query on table 't1'.
# 3. Start another connection and set super_read_only mode to ON.
# 4. Commit the transaction in step#2 which should fail with ERROR.
# 5. Check RO transaction still commits
# 6. Check RO txs still can use implicit temporary tables.
# Test 2
# 5. Repeat step 2-4 by executing a noop UPDATE and a temporary table
# INSERT in the transaction
# Test 3
# 6. Repeat Test 1 with non-SUPER user
# Test 4
# 7. Repeat Test 2 with non-SUPER user
# 8. Clean up.
#
# ==== References ====
#
# Bug #29009092 NOOP UPDATE QUERY IS LOGGED TO BINLOG AFTER READ_ONLY FLAG IS SET
# Bug #30810641 CONTRIBUTION BY FACEBOOK: TESTCASE FOR BINLOG WRITES AFTER SUPER_READ_ONLY ...
--source include/have_binlog_format_statement.inc
SET @saved_value_super= @@GLOBAL.SUPER_READ_ONLY;
SET @saved_value= @@GLOBAL.read_only;
--source include/count_sessions.inc
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
--source include/save_binlog_position.inc
# Test-1: Execute only noop UPDATE in the transaction
connect (con1,localhost,root,,test);
BEGIN;
UPDATE t1 SET a = 1;
connection default;
SET GLOBAL SUPER_READ_ONLY=1;
connection con1;
--error ER_OPTION_PREVENTS_STATEMENT
COMMIT;
--let $assert_text = Binlog position did not advance
--let $assert_cond = [SHOW MASTER STATUS, Position, 1] = $binlog_position
--source include/assert.inc
--echo check RO transaction still commits
BEGIN;
SELECT * FROM t1;
COMMIT;
--echo check RO txs still can use implicit temporary tables
BEGIN;
SELECT * FROM t1 UNION SELECT * FROM t1;
COMMIT;
connection default;
SET GLOBAL SUPER_READ_ONLY=0;
# Test-2: Execute a noop UPDATE and a temporary table INSERT in the transaction
connection con1;
CREATE TEMPORARY TABLE t2 (a INT ) ENGINE=INNODB;
connection default;
--source include/save_binlog_position.inc
connection con1;
BEGIN;
UPDATE t1 SET a = 1;
INSERT INTO t2 values(10);
connection default;
SET GLOBAL SUPER_READ_ONLY=1;
connection con1;
--error ER_OPTION_PREVENTS_STATEMENT
COMMIT;
--let $assert_text = Binlog position did not advance
--let $assert_cond = [SHOW MASTER STATUS, Position, 1] = $binlog_position
--source include/assert.inc
DROP TABLE t2;
connection default;
SET GLOBAL SUPER_READ_ONLY=0;
SET GLOBAL READ_ONLY=0;
# READ_ONLY does nothing to SUPER users so we setup a non-SUPER user.
CREATE USER test@localhost;
GRANT CREATE TEMPORARY TABLES, UPDATE, DROP ON *.* TO test@localhost;
--source include/save_binlog_position.inc
connect (con2,localhost,test,,test);
# Test-3: A non-SUPER user execute only noop UPDATE in the transaction.
connection con2;
BEGIN;
UPDATE t1 SET a = 1;
connection default;
SET GLOBAL READ_ONLY=1;
connection con2;
--error ER_OPTION_PREVENTS_STATEMENT
COMMIT;
connection default;
--let $assert_text = Binlog position did not advance
--let $assert_cond = [SHOW MASTER STATUS, Position, 1] = $binlog_position
--source include/assert.inc
SET GLOBAL READ_ONLY=0;
# Test-4: A non-SUPER user execute a noop UPDATE and a temporary table
# INSERT in the transaction
connection con2;
CREATE TEMPORARY TABLE t2 (a INT) ENGINE=INNODB;
connection default;
--source include/save_binlog_position.inc
connection con2;
BEGIN;
UPDATE t1 SET a = 1;
INSERT INTO t2 values(10);
connection default;
SET GLOBAL READ_ONLY=1;
connection con2;
--error ER_OPTION_PREVENTS_STATEMENT
COMMIT;
connection default;
--let $assert_text = Binlog position did not advance
--let $assert_cond = [SHOW MASTER STATUS, Position, 1] = $binlog_position
--source include/assert.inc
connection con2;
DROP TABLE t2;
# Clean up
connection default;
DROP USER test@localhost;
SET GLOBAL SUPER_READ_ONLY= @saved_value_super;
SET GLOBAL read_only= @saved_value;
DROP TABLE t1;
--disconnect con1
--disconnect con2
--source include/wait_until_count_sessions.inc
|
