Debian Codegraph @

mysql-test/suite/rpl/t/rpl_slow_query_log.test

raw
1 #
2 # BUG#23300: Slow query log on slave does not log slow replicated statements
3 #
4 # Description:
5 # The slave should log slow queries replicated from master when
6 # --log-slow-replica-statements is used.
7 #
8 # Test is implemented as follows:
9 # i) stop slave
10 # ii) On slave, set long_query_time to a small value.
11 # ii) start slave so that long_query_time variable is picked by sql thread
12 # iii) On master, do one short time query and one long time query, on slave
13 # and check that slow query is logged to slow query log but fast query
14 # is not.
15 # iv) On slave, check that slow queries go into the slow log and fast dont,
16 # when issued through a regular client connection
17 # v) On slave, check that slow queries go into the slow log and fast dont
18 # when we use SET TIMESTAMP= 1 on a regular client connection.
19 # vi) check that when setting slow_query_log= OFF in a connection 'extra2'
20 # prevents logging slow queries in a connection 'extra'
21 #
22 # OBS:
23 # This test only runs for statement binlogging format because on row
24 # format slow queries do not get slow query logged. Due to the
25 # sleep() command, the insert would be binlogged in row format if
26 # binlog_format=MIXED. Hence, we do not use binlog_format=MIXED.
27
28 source include/master-slave.inc;
29 source include/have_binlog_format_statement.inc;
30
31 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
32 call mtr.add_suppression("Replica SQL.*Request to stop replica SQL Thread received while applying a group that has non-transactional changes; waiting for completion of the group");
33
34 # Prepare slave for different long_query_time we need to stop the slave
35 # and restart it as long_query_time variable is dynamic and, after
36 # setting it, it only takes effect on new connections.
37 #
38 # Reference:
39 # http://dev.mysql.com/doc/refman/6.0/en/set-option.html
40 connection slave;
41
42 source include/stop_slave.inc;
43
44 SET @old_log_output= @@log_output;
45 SET GLOBAL log_output= 'TABLE';
46 SET @old_long_query_time= @@long_query_time;
47 SET GLOBAL long_query_time= 2;
48 TRUNCATE mysql.slow_log;
49
50 source include/start_slave.inc;
51
52 connection master;
53 CREATE TABLE t1 (a int, b int);
54
55 # test:
56 # check that slave logs the slow query to the slow log, but not the fast one.
57
58 let $slow_query= INSERT INTO t1 values(1, sleep(3));
59 let $fast_query= INSERT INTO t1 values(1, 1);
60
61 eval $fast_query;
62 --disable_warnings
63 eval $slow_query;
64 --enable_warnings
65 --source include/sync_slave_sql_with_master.inc
66
67 let $found_fast_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$fast_query'`;
68 let $found_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
69
70 if ($found_fast_query)
71 {
72 SELECT * FROM mysql.slow_log;
73 die "Assertion failed! Fast query FOUND in slow query log. Bailing out!";
74 }
75
76 if (!$found_slow_query)
77 {
78 SELECT * FROM mysql.slow_log;
79 die "Assertion failed! Slow query NOT FOUND in slow query log. Bailing out!";
80 }
81 TRUNCATE mysql.slow_log;
82
83 # regular checks for slow query log (using a new connection - 'extra' - to slave)
84
85 # test:
86 # when using direct connections to the slave, check that slow query is logged
87 # but not the fast one.
88
89 connect(extra,127.0.0.1,root,,test,$SLAVE_MYPORT);
90 connection extra;
91
92 let $fast_query= SELECT 1;
93 let $slow_query= SELECT 1, sleep(3);
94
95 eval $slow_query;
96 eval $fast_query;
97
98 let $found_fast_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$fast_query'`;
99 let $found_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
100
101 if ($found_fast_query)
102 {
103 SELECT * FROM mysql.slow_log;
104 die "Assertion failed! Fast query FOUND in slow query log. Bailing out!";
105 }
106
107 if (!$found_slow_query)
108 {
109 SELECT * FROM mysql.slow_log;
110 die "Assertion failed! Slow query NOT FOUND in slow query log. Bailing out!";
111 }
112 TRUNCATE mysql.slow_log;
113
114 # test:
115 # when using direct connections to the slave, check that when setting timestamp to 1 the
116 # slow query is logged but the fast one is not.
117
118 let $fast_query= SELECT 2;
119 let $slow_query= SELECT 2, sleep(3);
120
121 SET TIMESTAMP= 1;
122 eval $slow_query;
123 eval $fast_query;
124
125 let $found_fast_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$fast_query'`;
126 let $found_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
127
128 if ($found_fast_query)
129 {
130 SELECT * FROM mysql.slow_log;
131 die "Assertion failed! Fast query FOUND in slow query log. Bailing out!";
132 }
133
134 if (!$found_slow_query)
135 {
136 SELECT * FROM mysql.slow_log;
137 die "Assertion failed! Slow query NOT FOUND in slow query log. Bailing out!";
138 }
139 TRUNCATE mysql.slow_log;
140
141 # test:
142 # check that when setting the slow_query_log= OFF on connection 'extra2'
143 # prevents connection 'extra' from logging to slow query log.
144
145 let $fast_query= SELECT 3;
146 let $slow_query= SELECT 3, sleep(3);
147
148 connect(extra2,127.0.0.1,root,,test,$SLAVE_MYPORT);
149 connection extra2;
150
151 SET @old_slow_query_log= @@slow_query_log;
152 SET GLOBAL slow_query_log= 'OFF';
153
154 connection extra;
155
156 eval $slow_query;
157 eval $fast_query;
158
159 let $found_fast_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$fast_query'`;
160 let $found_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
161
162 if ($found_fast_query)
163 {
164 SELECT * FROM mysql.slow_log;
165 die "Assertion failed! Fast query FOUND in slow query log when slow_query_log= OFF. Bailing out!";
166 }
167
168 if ($found_slow_query)
169 {
170 SELECT * FROM mysql.slow_log;
171 die "Assertion failed! Slow query FOUND in slow query log when slow_query_log= OFF. Bailing out!";
172 }
173 TRUNCATE mysql.slow_log;
174
175 # clean up: drop tables, reset the variables back to the previous value,
176 # disconnect extra connections
177 connection extra2;
178
179 SET GLOBAL slow_query_log= @old_slow_query_log;
180
181 connection master;
182 DROP TABLE t1;
183 --source include/sync_slave_sql_with_master.inc
184
185 source include/stop_slave.inc;
186
187 SET GLOBAL long_query_time= @old_long_query_time;
188 SET GLOBAL log_output= @old_log_output;
189
190 source include/start_slave.inc;
191
192 disconnect extra;
193 disconnect extra2;
194
195 #
196 # BUG#50620: Adding an index to a table prevents slave from logging into slow log
197 #
198
199 --source include/rpl_reset.inc
200
201 -- connection master
202 SET @old_log_output= @@log_output;
203 SET GLOBAL log_output= 'TABLE';
204 SET GLOBAL long_query_time= 2;
205 SET @old_long_query_time= @@long_query_time;
206 SET SESSION long_query_time= 2;
207 TRUNCATE mysql.slow_log;
208 -- connection slave
209
210 -- source include/stop_slave.inc
211 SET @old_log_output= @@log_output;
212 SET GLOBAL log_output= 'TABLE';
213 SET @old_long_query_time= @@long_query_time;
214 SET GLOBAL long_query_time= 2;
215 TRUNCATE mysql.slow_log;
216 -- source include/start_slave.inc
217
218 let $slow_query= INSERT INTO t1 values(1, sleep(3));
219
220 -- connection master
221 CREATE TABLE t1 (a int, b int);
222
223 -- echo ********************************************************************
224 -- echo **** INSERT one row that exceeds long_query_time
225 -- echo **** Outcome: query ends up in both master and slave slow log
226 -- echo ********************************************************************
227
228 -- disable_warnings
229 -- eval $slow_query
230 -- enable_warnings
231
232 let $master_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
233 --source include/sync_slave_sql_with_master.inc
234 let $slave_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
235
236 if ($master_slow_query != $slave_slow_query)
237 {
238 -- connection master
239 -- echo ***********************************************
240 -- echo ** DUMPING MASTER SLOW LOG CONTENTS
241 -- echo ***********************************************
242 SELECT * FROM mysql.slow_log;
243
244 -- connection slave
245 -- echo ***********************************************
246 -- echo ** DUMPING SLAVE SLOW LOG CONTENTS
247 -- echo ***********************************************
248 SELECT * FROM mysql.slow_log;
249
250 -- die "Assertion failed! Master and slave slow log contents differ. Bailing out!"
251 }
252
253 if ($master_slow_query == $slave_slow_query)
254 {
255 -- echo ### Assertion is good. Both Master and Slave exhibit the
256 -- echo ### same number of queries in slow log: $master_slow_query
257 }
258
259 TRUNCATE mysql.slow_log;
260 -- connection master
261 TRUNCATE mysql.slow_log;
262
263 -- echo ********************************************************************
264 -- echo **** Now do inserts again, but first add an index to the table.
265 -- echo **** Outcome: Note that the slave contains the same one entry (as
266 -- echo **** the master does) whereas before the patch it did not.
267 -- echo ********************************************************************
268
269 ALTER TABLE t1 ADD INDEX id1(a);
270
271 -- disable_warnings
272 -- eval $slow_query
273 -- enable_warnings
274
275 let $master_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
276 --source include/sync_slave_sql_with_master.inc
277 let $slave_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
278
279 if ($master_slow_query != $slave_slow_query)
280 {
281 -- connection master
282 -- echo ***********************************************
283 -- echo ** DUMPING MASTER SLOW LOG CONTENTS
284 -- echo ***********************************************
285 SELECT * FROM mysql.slow_log;
286
287 -- connection slave
288 -- echo ***********************************************
289 -- echo ** DUMPING SLAVE SLOW LOG CONTENTS
290 -- echo ***********************************************
291 SELECT * FROM mysql.slow_log;
292
293 -- die "Assertion failed! Master and slave slow log contents differ. Bailing out!"
294 }
295
296 if ($master_slow_query == $slave_slow_query)
297 {
298 -- echo ### Assertion is good. Both Master and Slave exhibit the
299 -- echo ### same number of queries in slow log: $master_slow_query
300 }
301
302 -- echo ********************************************************************
303 -- echo **** TRUNCATE the slow log then check whether runtime changes of
304 -- echo **** log_slow_replica_statements work without slave restart.
305 -- echo ********************************************************************
306
307 SET @old_log_slow_replica_statements= @@global.log_slow_replica_statements;
308 SET @@global.log_slow_replica_statements = off;
309 TRUNCATE mysql.slow_log;
310
311 -- connection master
312
313 --disable_warnings
314 -- eval $slow_query;
315 --enable_warnings
316
317 --source include/sync_slave_sql_with_master.inc
318 -- connection slave
319
320 let $slave_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
321 if ($slave_slow_query)
322 {
323 SELECT * FROM mysql.slow_log;
324 die "Assertion failed! Slow query FOUND in slow query log. Bailing out!";
325 }
326
327 SET @@global.log_slow_replica_statements = on;
328
329 -- connection master
330
331 --disable_warnings
332 -- eval $slow_query;
333 --enable_warnings
334
335 --source include/sync_slave_sql_with_master.inc
336 -- connection slave
337
338 let $slave_slow_query= `SELECT count(*) = 1 FROM mysql.slow_log WHERE sql_text like '$slow_query'`;
339 if (!$slave_slow_query)
340 {
341 SELECT * FROM mysql.slow_log;
342 die "Assertion failed! Slow query NOT FOUND in slow query log. Bailing out!";
343 }
344
345 -- connection master
346 SET @@global.log_output= @old_log_output;
347 SET @@global.long_query_time= @old_long_query_time;
348 TRUNCATE TABLE mysql.slow_log;
349 TRUNCATE TABLE mysql.general_log;
350 DROP TABLE t1;
351
352 --source include/sync_slave_sql_with_master.inc
353 SET @@global.log_output= @old_log_output;
354 TRUNCATE TABLE mysql.general_log;
355 SET @@global.long_query_time= @old_long_query_time;
356 SET @@global.log_slow_replica_statements= @old_log_slow_replica_statements;
357 --source include/rpl_end.inc