Debian Codegraph @

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

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