Debian Codegraph @

mysql-test/suite/rpl/include/rpl_binlog_max_cache_size.inc

raw
1 ########################################################################################
2 # This test verifies if the binlog is not corrupted when the cache buffer is not
3 # big enough to accommodate the changes and is divided in five steps:
4 #
5 # 1 - Single Statements:
6 # 1.1 - Single statement on transactional table.
7 # 1.2 - Single statement on non-transactional table.
8 # 1.3 - Single statement on both transactional and non-transactional tables.
9 # In both 1.2 and 1.3, an incident event is logged to notify the user that the
10 # master and slave are diverging.
11 #
12 # 2 - Transactions ended by an implicit commit.
13 #
14 # 3 - Transactions ended by a COMMIT.
15 #
16 # 4 - Transactions ended by a ROLLBACK.
17 #
18 # 5 - Transactions with a failing statement that updates a non-transactional
19 # table. In this case, a failure means that the statement does not get into
20 # the cache and an incident event is logged to notify the user that the master
21 # and slave are diverging.
22 #
23 ########################################################################################
24
25 --connection slave
26 --source include/stop_slave.inc
27 CHANGE MASTER TO MASTER_USE_GTID=NO;
28 --source include/start_slave.inc
29 --connection master
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("Write to binary log failed: Multi-row statements required more than .max_binlog_stmt_cache_size");
33 call mtr.add_suppression("Write to binary log failed: Multi-statement transaction required more than .max_binlog_cache_size");
34
35 let $old_max_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_cache_size", Value, 1);
36 let $old_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_cache_size", Value, 1);
37 let $old_max_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_stmt_cache_size", Value, 1);
38 let $old_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_stmt_cache_size", Value, 1);
39
40 SET GLOBAL max_binlog_cache_size = 4096;
41 SET GLOBAL binlog_cache_size = 4096;
42 SET GLOBAL max_binlog_stmt_cache_size = 4096;
43 SET GLOBAL binlog_stmt_cache_size = 4096;
44 disconnect master;
45 connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
46
47 CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb CHARSET=latin1;
48 CREATE TABLE t2(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MyIsam CHARSET=latin1;
49 CREATE TABLE t3(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb CHARSET=latin1;
50
51 let $data = `select concat('"', repeat('a',2000), '"')`;
52
53 --echo ########################################################################################
54 --echo # 1 - SINGLE STATEMENT
55 --echo ########################################################################################
56
57 connection master;
58
59 --echo *** Single statement on transactional table ***
60 --disable_query_log
61 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE, 1534
62 eval INSERT INTO t1 (a, data) VALUES (1,
63 CONCAT($data, $data, $data, $data, $data));
64 --enable_query_log
65
66 --echo *** Single statement on non-transactional table ***
67 --disable_query_log
68 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE, 1534
69 eval INSERT INTO t2 (a, data) VALUES (2,
70 CONCAT($data, $data, $data, $data, $data, $data));
71 --enable_query_log
72
73 # Incident event
74 # 1590=ER_SLAVE_INCIDENT
75 --let $slave_sql_errno= 1590
76 --source include/wait_for_slave_sql_error_and_skip.inc
77
78 --disable_query_log
79 eval INSERT INTO t1 (a, data) VALUES (3, $data);
80 eval INSERT INTO t1 (a, data) VALUES (4, $data);
81 eval INSERT INTO t1 (a, data) VALUES (5, $data);
82 eval INSERT INTO t2 (a, data) VALUES (3, $data);
83 eval INSERT INTO t2 (a, data) VALUES (4, $data);
84 eval INSERT INTO t2 (a, data) VALUES (5, $data);
85 --enable_query_log
86
87 --echo *** Single statement on both transactional and non-transactional tables. ***
88 --disable_query_log
89 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
90 eval UPDATE t2, t1 SET t2.data = CONCAT($data, $data, $data, $data),
91 t1.data = CONCAT($data, $data, $data, $data);
92 --enable_query_log
93
94 # 1590=ER_SLAVE_INCIDENT
95 --let $slave_sql_errno= 1590
96 --let $slave_skip_counter= `SELECT IF(@@binlog_format = 'ROW', 2, 1)`
97 --source include/wait_for_slave_sql_error_and_skip.inc
98
99 --let $diff_tables= master:t1,slave:t1
100 --source include/diff_tables.inc
101
102 --echo ########################################################################################
103 --echo # 2 - BEGIN - IMPLICIT COMMIT by DDL
104 --echo ########################################################################################
105
106 connection master;
107 TRUNCATE TABLE t1;
108 TRUNCATE TABLE t2;
109 TRUNCATE TABLE t3;
110 set default_storage_engine=innodb;
111
112 BEGIN;
113 --disable_query_log
114 --eval INSERT INTO t1 (a, data) VALUES (1, $data);
115 --eval INSERT INTO t1 (a, data) VALUES (2, $data);
116 --eval INSERT INTO t1 (a, data) VALUES (3, $data);
117 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
118 --eval INSERT INTO t1 (a, data) VALUES (4, $data);
119 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
120 --eval INSERT INTO t1 (a, data) VALUES (5, $data);
121 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
122 --eval INSERT INTO t1 (a, data) VALUES (6, $data);
123 --enable_query_log
124 --eval INSERT INTO t1 (a, data) VALUES (7, 's');
125 --eval INSERT INTO t2 (a, data) VALUES (8, 's');
126 --eval INSERT INTO t1 (a, data) VALUES (9, 's');
127
128 ALTER TABLE t3 ADD COLUMN d int;
129
130 --disable_query_log
131 --eval INSERT INTO t2 (a, data) VALUES (10, $data);
132 --eval INSERT INTO t2 (a, data) VALUES (11, $data);
133 --eval INSERT INTO t2 (a, data) VALUES (12, $data);
134 --eval INSERT INTO t2 (a, data) VALUES (13, $data);
135 --enable_query_log
136
137 BEGIN;
138 --disable_query_log
139 --eval INSERT INTO t1 (a, data) VALUES (14, $data);
140 --eval INSERT INTO t1 (a, data) VALUES (15, $data);
141 --eval INSERT INTO t1 (a, data) VALUES (16, $data);
142 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
143 --eval INSERT INTO t1 (a, data) VALUES (17, $data);
144 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
145 --eval INSERT INTO t1 (a, data) VALUES (18, $data);
146 --enable_query_log
147 --eval INSERT INTO t1 (a, data) VALUES (19, 's');
148 --eval INSERT INTO t2 (a, data) VALUES (20, 's');
149 --eval INSERT INTO t1 (a, data) VALUES (21, 's');
150
151 if (`SELECT @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`)
152 {
153 CREATE TABLE t4 SELECT * FROM t1;
154 }
155 if (`SELECT @@binlog_format = 'ROW'`)
156 {
157 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
158 CREATE TABLE t4 SELECT * FROM t1;
159 }
160
161 --disable_query_log
162 --eval INSERT INTO t2 (a, data) VALUES (15, $data);
163 --enable_query_log
164
165 BEGIN;
166 --disable_query_log
167 --eval INSERT INTO t1 (a, data) VALUES (22, $data);
168 --eval INSERT INTO t1 (a, data) VALUES (23, $data);
169 --eval INSERT INTO t1 (a, data) VALUES (24, $data);
170 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
171 --eval INSERT INTO t1 (a, data) VALUES (25, $data);
172 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
173 --eval INSERT INTO t1 (a, data) VALUES (26, $data);
174 --enable_query_log
175 --eval INSERT INTO t1 (a, data) VALUES (27, 's');
176 --eval INSERT INTO t2 (a, data) VALUES (28, 's');
177 --eval INSERT INTO t1 (a, data) VALUES (29, 's');
178
179 CREATE TABLE t5 (a int);
180
181 --sync_slave_with_master
182 --let $diff_tables= master:t1,slave:t1
183 --source include/diff_tables.inc
184
185 --echo ########################################################################################
186 --echo # 3 - BEGIN - COMMIT
187 --echo ########################################################################################
188
189 connection master;
190 TRUNCATE TABLE t1;
191 TRUNCATE TABLE t2;
192 TRUNCATE TABLE t3;
193
194 BEGIN;
195 --disable_query_log
196 --eval INSERT INTO t1 (a, data) VALUES (1, $data);
197 --eval INSERT INTO t1 (a, data) VALUES (2, $data);
198 --eval INSERT INTO t1 (a, data) VALUES (3, $data);
199 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
200 --eval INSERT INTO t1 (a, data) VALUES (4, $data);
201 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
202 --eval INSERT INTO t1 (a, data) VALUES (5, $data);
203 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
204 --eval INSERT INTO t1 (a, data) VALUES (6, $data);
205 --eval INSERT INTO t1 (a, data) VALUES (7, 's');
206 --eval INSERT INTO t2 (a, data) VALUES (8, 's');
207 --eval INSERT INTO t1 (a, data) VALUES (9, 's');
208 --enable_query_log
209 COMMIT;
210
211 --sync_slave_with_master
212 --let $diff_tables= master:t1,slave:t1
213 --source include/diff_tables.inc
214
215 --echo ########################################################################################
216 --echo # 4 - BEGIN - ROLLBACK
217 --echo ########################################################################################
218
219 connection master;
220 TRUNCATE TABLE t1;
221 TRUNCATE TABLE t2;
222 TRUNCATE TABLE t3;
223
224 BEGIN;
225 --disable_query_log
226 --eval INSERT INTO t1 (a, data) VALUES (1, $data);
227 --eval INSERT INTO t1 (a, data) VALUES (2, $data);
228 --eval INSERT INTO t1 (a, data) VALUES (3, $data);
229 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
230 --eval INSERT INTO t1 (a, data) VALUES (4, $data);
231 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
232 --eval INSERT INTO t1 (a, data) VALUES (5, $data);
233 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
234 --eval INSERT INTO t1 (a, data) VALUES (6, $data);
235 --eval INSERT INTO t1 (a, data) VALUES (7, 's');
236 --eval INSERT INTO t2 (a, data) VALUES (8, 's');
237 --eval INSERT INTO t1 (a, data) VALUES (9, 's');
238 --enable_query_log
239 ROLLBACK;
240
241 --sync_slave_with_master
242 --let $diff_tables= master:t1,slave:t1
243 --source include/diff_tables.inc
244
245 --echo ########################################################################################
246 --echo # 5 - PROCEDURE
247 --echo ########################################################################################
248
249 connection master;
250 TRUNCATE TABLE t1;
251 TRUNCATE TABLE t2;
252 TRUNCATE TABLE t3;
253
254 DELIMITER //;
255
256 CREATE PROCEDURE p1(pd VARCHAR(30000) CHARACTER SET latin1)
257 BEGIN
258 INSERT INTO t1 (a, data) VALUES (1, pd);
259 INSERT INTO t1 (a, data) VALUES (2, pd);
260 INSERT INTO t1 (a, data) VALUES (3, pd);
261 INSERT INTO t1 (a, data) VALUES (4, pd);
262 INSERT INTO t1 (a, data) VALUES (5, 's');
263 END//
264
265 DELIMITER ;//
266
267 TRUNCATE TABLE t1;
268
269 --disable_query_log
270 eval CALL p1($data);
271 --enable_query_log
272
273 TRUNCATE TABLE t1;
274
275 BEGIN;
276 --disable_query_log
277 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
278 eval CALL p1($data);
279 --enable_query_log
280 COMMIT;
281
282 TRUNCATE TABLE t1;
283
284 BEGIN;
285 --disable_query_log
286 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
287 eval CALL p1($data);
288 --enable_query_log
289 ROLLBACK;
290
291 --sync_slave_with_master
292 --let $diff_tables= master:t1,slave:t1
293 --source include/diff_tables.inc
294
295 --echo ########################################################################################
296 --echo # 6 - XID
297 --echo ########################################################################################
298
299 connection master;
300 TRUNCATE TABLE t1;
301 TRUNCATE TABLE t2;
302 TRUNCATE TABLE t3;
303
304 BEGIN;
305 --disable_query_log
306 --eval INSERT INTO t1 (a, data) VALUES (1, $data);
307 --eval INSERT INTO t1 (a, data) VALUES (2, $data);
308 --eval INSERT INTO t1 (a, data) VALUES (3, $data);
309 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
310 --eval INSERT INTO t1 (a, data) VALUES (4, $data);
311 SAVEPOINT sv;
312 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
313 --eval INSERT INTO t1 (a, data) VALUES (5, $data);
314 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
315 --eval INSERT INTO t1 (a, data) VALUES (6, $data);
316 --enable_query_log
317 --eval INSERT INTO t1 (a, data) VALUES (7, 's');
318 --eval INSERT INTO t2 (a, data) VALUES (8, 's');
319 --eval INSERT INTO t1 (a, data) VALUES (9, 's');
320 ROLLBACK TO sv;
321 COMMIT;
322
323 --sync_slave_with_master
324 --let $diff_tables= master:t1,slave:t1
325 --source include/diff_tables.inc
326
327 --echo ########################################################################################
328 --echo # 7 - NON-TRANS TABLE
329 --echo ########################################################################################
330
331 connection master;
332 TRUNCATE TABLE t1;
333 TRUNCATE TABLE t2;
334 TRUNCATE TABLE t3;
335
336 BEGIN;
337 --disable_query_log
338 --eval INSERT INTO t1 (a, data) VALUES (1, $data);
339 --eval INSERT INTO t1 (a, data) VALUES (2, $data);
340 --eval INSERT INTO t2 (a, data) VALUES (3, $data);
341 --eval INSERT INTO t1 (a, data) VALUES (4, $data);
342 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
343 --eval INSERT INTO t1 (a, data) VALUES (5, $data);
344 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
345 --eval INSERT INTO t1 (a, data) VALUES (6, $data);
346 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
347 --eval INSERT INTO t1 (a, data) VALUES (7, $data);
348 --eval UPDATE t2 SET data= CONCAT($data, $data);
349 --enable_query_log
350 --eval INSERT INTO t1 (a, data) VALUES (8, 's');
351 --eval INSERT INTO t1 (a, data) VALUES (9, 's');
352 --eval INSERT INTO t2 (a, data) VALUES (10, 's');
353 --eval INSERT INTO t1 (a, data) VALUES (11, 's');
354 COMMIT;
355
356 BEGIN;
357 --disable_query_log
358 --eval INSERT INTO t1 (a, data) VALUES (15, $data);
359 --eval INSERT INTO t1 (a, data) VALUES (16, $data);
360 --eval INSERT INTO t2 (a, data) VALUES (17, $data);
361 --eval INSERT INTO t1 (a, data) VALUES (18, $data);
362 --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE
363 --eval INSERT INTO t1 (a, data) VALUES (19, $data);
364 --enable_query_log
365 COMMIT;
366
367 --sync_slave_with_master
368 --let $diff_tables= master:t1,slave:t1
369 --source include/diff_tables.inc
370
371 --echo ########################################################################
372 --echo # 8 - Bug#55375(Regression Bug) Transaction bigger than
373 --echo # max_binlog_cache_size crashes slave
374 --echo ########################################################################
375
376 --echo # [ On Slave ]
377 SET GLOBAL max_binlog_cache_size = 4096;
378 SET GLOBAL binlog_cache_size = 4096;
379 SET GLOBAL max_binlog_stmt_cache_size = 4096;
380 SET GLOBAL binlog_stmt_cache_size = 4096;
381
382 source include/stop_slave.inc;
383 source include/start_slave.inc;
384 CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage.*");
385 CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_stmt_cache_size' bytes of storage.*");
386 CALL mtr.add_suppression("Writing one row to the row-based binary log failed.*");
387 CALL mtr.add_suppression("Slave SQL.*The incident LOST_EVENTS occurred on the master. Message: error writing to the binary log");
388
389 connection master;
390 TRUNCATE t1;
391
392 sync_slave_with_master;
393 --let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1)
394 --let binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
395
396 connection master;
397 --replace_result $old_max_binlog_cache_size ORIGINAL_VALUE
398 --eval SET GLOBAL max_binlog_cache_size= $old_max_binlog_cache_size
399 --replace_result $old_binlog_cache_size ORIGINAL_VALUE
400 --eval SET GLOBAL binlog_cache_size= $old_binlog_cache_size
401 --replace_result $old_max_binlog_stmt_cache_size ORIGINAL_VALUE
402 --eval SET GLOBAL max_binlog_stmt_cache_size= $old_max_binlog_stmt_cache_size
403 --replace_result $old_binlog_stmt_cache_size ORIGINAL_VALUE
404 --eval SET GLOBAL binlog_stmt_cache_size= $old_binlog_stmt_cache_size
405 disconnect master;
406 connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,);
407
408 --let $n=128
409 BEGIN;
410 --disable_query_log
411 --echo Repeat statement 'INSERT INTO t1 VALUES(\$n, repeat("a", 32))' $n times
412 while ($n)
413 {
414 --eval INSERT INTO t1 VALUES ($n, repeat("a", 32))
415 --dec $n
416 }
417 --enable_query_log
418 COMMIT;
419
420 --connection slave
421 # Multi-statement transaction raised ER_TRANS_CACHE_FULL (4096=128x32)
422 # that will stop SQL slave thread
423 --let $slave_sql_errno= 1197
424 if (`SELECT @@binlog_format = 'ROW'`)
425 {
426 --let $slave_sql_errno= 1534
427 }
428 source include/wait_for_slave_sql_error.inc;
429
430 SELECT count(*) FROM t1;
431 source include/show_binlog_events.inc;
432
433 --replace_result $old_max_binlog_cache_size ORIGINAL_VALUE
434 --eval SET GLOBAL max_binlog_cache_size= $old_max_binlog_cache_size
435 --replace_result $old_binlog_cache_size ORIGINAL_VALUE
436 --eval SET GLOBAL binlog_cache_size= $old_binlog_cache_size
437 --replace_result $old_max_binlog_stmt_cache_size ORIGINAL_VALUE
438 --eval SET GLOBAL max_binlog_stmt_cache_size= $old_max_binlog_stmt_cache_size
439 --replace_result $old_binlog_stmt_cache_size ORIGINAL_VALUE
440 --eval SET GLOBAL binlog_stmt_cache_size= $old_binlog_stmt_cache_size
441
442 # SQL slave is stopped, stop only IO thread
443 source include/stop_slave_io.inc;
444 source include/start_slave.inc;
445
446 connection master;
447 sync_slave_with_master;
448 SELECT count(*) FROM t1;
449
450 --echo ########################################################################################
451 --echo # CLEAN
452 --echo ########################################################################################
453
454 connection master;
455 DROP TABLE t1;
456 DROP TABLE t2;
457 DROP TABLE t3;
458 # t4 exists only if binlog_format!=row, so so a warning is generated
459 # if binog_format=row
460 --disable_warnings
461 DROP TABLE IF EXISTS t4;
462 --enable_warnings
463 DROP TABLE t5;
464 DROP PROCEDURE p1;