Debian Codegraph @

mysql-test/suite/ndb_binlog/t/ndb_binlog_variants.test

raw
1 ########################################################
2 # Test binlog variants produced by Ndb
3 #
4 # 1) Updates logged as write_row events
5 # Only primary key and updated columns included in the
6 # event
7 # 2) Updates logged as write_row_events
8 # All columns included in the event
9 # 3) Updates logged as update_row events
10 # Only primary key and updated columns included in the
11 # event
12 # 4) Updates logged as update_row events
13 # All columns included in the event
14 #
15 # Format variant (1) is the Ndb default.
16 #
17 # We use mysqlbinlog --verbose to check that the
18 # generated binlog contents are as expected.
19 #
20 ########################################################
21 -- source include/have_ndb.inc
22 -- source include/have_binlog_format_row.inc
23
24 # Setup connections
25 connect(mysqld1,127.0.0.1,root,,test,$MASTER_MYPORT);
26 connect(mysqld2,127.0.0.1,root,,test,$MASTER_MYPORT1);
27 connect(mysqld3,127.0.0.1,root,,test,$MASTER_MYPORT2);
28 connect(mysqld4,127.0.0.1,root,,test,$MASTER_MYPORT3);
29 connect(mysqld5,127.0.0.1,root,,test,$MASTER_MYPORT4);
30 connect(mysqld6,127.0.0.1,root,,test,$MASTER_MYPORT5);
31
32 connection mysqld1;
33
34 # Create the tables we're going to use
35 create table ab(a1 int, a2 int, a3 int, a4 int, a5 int, primary key(a2,a5)) engine = ndb;
36 create table ba(ks int primary key, st int, lp int) engine = ndb;
37 create table cpk(nonchar int, charone varchar(100), chartwo varchar(100),
38 other int,
39 primary key (nonchar, charone, chartwo)) engine=ndb;
40
41 # Wait for each mysqld to startup binlogging
42 --let $source_server=mysqld1
43 --let $dest_server=mysqld2
44 source suite/ndb_rpl/t/wait_schema_logging.inc;
45
46 --let $source_server=mysqld1
47 --let $dest_server=mysqld3
48 source suite/ndb_rpl/t/wait_schema_logging.inc;
49
50 --let $source_server=mysqld1
51 --let $dest_server=mysqld4
52 source suite/ndb_rpl/t/wait_schema_logging.inc;
53
54 --let $source_server=mysqld1
55 --let $dest_server=mysqld5
56 source suite/ndb_rpl/t/wait_schema_logging.inc;
57
58 --let $source_server=mysqld1
59 --let $dest_server=mysqld6
60 source suite/ndb_rpl/t/wait_schema_logging.inc;
61
62 --disable_query_log
63 connection mysqld1;
64 reset master;
65 connection mysqld2;
66 reset master;
67 connection mysqld3;
68 reset master;
69 connection mysqld4;
70 reset master;
71 connection mysqld5;
72 reset master;
73 connection mysqld6;
74 reset master;
75 --enable_query_log
76
77 connection mysqld1;
78
79 # Now make the inserts and update
80 insert into ab values (1,1,1,1,1), (2,2,2,2,2), (3,3,3,3,3), (4,4,4,4,4);
81 update ab set a3=111 where a2=1;
82 update ab set a1=222, a4=222 where a2=2;
83
84 insert into ba values (1, 1, 1), (2,2,2), (3,3,3), (4,4,4);
85 update ba set lp=40 where ks=4; # Update does not affect all columns
86 delete from ba where ks=2; # Also a delete for fun
87
88 insert into cpk values(1, "ABCD", "WXYZ", 100);
89 insert into cpk values(2, "ABCD", "WXYZ", 100);
90 insert into cpk values(3, "ABCD", "WXYZ", 100);
91 insert into cpk values(4, "ABCD", "WXYZ", 100);
92 insert into cpk values(5, "ABCD", "WXYZ", 100);
93 insert into cpk values(6, "ABCD", "WXYZ", 100);
94
95 --echo Change one char key
96 update cpk set charone="abcd" where charone="ABCD" and nonchar=1 and chartwo="WXYZ";
97 --echo Change other char key
98 update cpk set chartwo="wxyz" where charone="ABCD" and nonchar=2 and chartwo="WXYZ";
99 --echo Change both char keys
100 update cpk set charone="abcd", chartwo="wxyz" where charone="ABCD" and nonchar=3 and chartwo="WXYZ";
101 --echo Null change
102 update cpk set charone="ABCD", chartwo="WXYZ" where charone="ABCD" and nonchar=4 and chartwo="WXYZ";
103 --echo Null change + data change
104 update cpk set charone="ABCD", chartwo="WXYZ", other=200 where charone="ABCD" and nonchar=5 and chartwo="WXYZ";
105 --echo Change both char keys + data
106 update cpk set charone="abcd", chartwo="wxyz", other=200 where charone="ABCD" and nonchar=6 and chartwo="WXYZ";
107
108
109
110 --disable_query_log
111 # Add an event-stream marker
112 create table stream_marker(a int) engine=ndb;
113 drop table stream_marker;
114 --let $wait_binlog_event=stream_marker
115 --enable_query_log
116
117 # Now let's trim the Binlogs on each server
118
119 connection mysqld1;
120 --source include/wait_for_binlog_event.inc
121 flush logs;
122 connection mysqld2;
123 --source include/wait_for_binlog_event.inc
124 flush logs;
125 connection mysqld3;
126 --source include/wait_for_binlog_event.inc
127 flush logs;
128 connection mysqld4;
129 --source include/wait_for_binlog_event.inc
130 flush logs;
131 connection mysqld5;
132 --source include/wait_for_binlog_event.inc
133 flush logs;
134 connection mysqld6;
135 --source include/wait_for_binlog_event.inc
136 flush logs;
137
138 # Empty the table
139 delete from ba;
140
141 # Now let's examine the contents of the first binlog
142 # on each server
143 # We'll also apply the Binlog and check that the
144 # table contents are as expected in each case.
145 # As each server is recording in a new binlog, the
146 # new updates will go there.
147
148 connection mysqld1;
149
150 show variables like 'ndb_log_update%';
151 --source suite/ndb_binlog/t/ndb_binlog_get_binlog_stmts.inc
152
153 --disable_query_log
154 let $MYSQLD_DATADIR= `select @@datadir;`;
155 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
156 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
157
158 --enable_query_log
159 select * from ab order by a2,a4;
160 delete from ab;
161 select * from ba order by ks;
162 delete from ba;
163 select * from cpk order by nonchar;
164 delete from cpk;
165
166 connection mysqld2;
167
168 show variables like 'ndb_log_update%';
169 --source suite/ndb_binlog/t/ndb_binlog_get_binlog_stmts.inc
170
171 --disable_query_log
172 let $MYSQLD_DATADIR= `select @@datadir;`;
173 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
174 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
175
176 --enable_query_log
177 select * from ab order by a2,a4;
178 delete from ab;
179 select * from ba order by ks;
180 delete from ba;
181 select * from cpk order by nonchar;
182 delete from cpk;
183
184 connection mysqld3;
185
186 show variables like 'ndb_log_update%';
187 --source suite/ndb_binlog/t/ndb_binlog_get_binlog_stmts.inc
188
189 --disable_query_log
190 let $MYSQLD_DATADIR= `select @@datadir;`;
191 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
192 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
193
194 --enable_query_log
195 select * from ab order by a2,a4;
196 delete from ab;
197 select * from ba order by ks;
198 delete from ba;
199 select * from cpk order by nonchar;
200 delete from cpk;
201
202 connection mysqld4;
203
204 show variables like 'ndb_log_update%';
205 --source suite/ndb_binlog/t/ndb_binlog_get_binlog_stmts.inc
206
207 --disable_query_log
208 let $MYSQLD_DATADIR= `select @@datadir;`;
209 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
210 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
211
212 --enable_query_log
213 select * from ab order by a2,a4;
214 delete from ab;
215 select * from ba order by ks;
216 delete from ba;
217 select * from cpk order by nonchar;
218 delete from cpk;
219
220 connection mysqld5;
221
222 show variables like 'ndb_log_update%';
223 --source suite/ndb_binlog/t/ndb_binlog_get_binlog_stmts.inc
224
225 --disable_query_log
226 let $MYSQLD_DATADIR= `select @@datadir;`;
227 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
228 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
229
230 --enable_query_log
231 select * from ab order by a2,a4;
232 delete from ab;
233 select * from ba order by ks;
234 delete from ba;
235 select * from cpk order by nonchar;
236 delete from cpk;
237
238 connection mysqld6;
239
240 show variables like 'ndb_log_update%';
241 --source suite/ndb_binlog/t/ndb_binlog_get_binlog_stmts.inc
242
243 --disable_query_log
244 let $MYSQLD_DATADIR= `select @@datadir;`;
245 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
246 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
247
248 --enable_query_log
249 select * from ab order by a2,a4;
250 delete from ab;
251 select * from ba order by ks;
252 delete from ba;
253 select * from cpk order by nonchar;
254 delete from cpk;
255
256 drop table ab;
257 drop table ba;
258 drop table cpk;
259 --disable_query_log
260 flush logs;
261 --enable_query_log
262
263 connection mysqld1;
264 CREATE TABLE mysql.ndb_replication
265 (db VARBINARY(63),
266 table_name VARBINARY(63),
267 server_id INT UNSIGNED,
268 binlog_type INT UNSIGNED,
269 conflict_fn VARBINARY(128),
270 PRIMARY KEY USING HASH (db,table_name,server_id))
271 ENGINE=NDB PARTITION BY KEY(db,table_name);
272
273 insert into mysql.ndb_replication values("test", "t1", 0, 6, NULL);
274 insert into mysql.ndb_replication values("test", "t2", 0, 7, NULL);
275 insert into mysql.ndb_replication values("test", "t3", 0, 8, NULL);
276 insert into mysql.ndb_replication values("test", "t4", 0, 9, NULL);
277
278 create table t1(a1 int, a2 int, a3 int, a4 int, a5 int, primary key(a2,a5)) engine=ndb;
279 create table t2(a1 int, a2 int, a3 int, a4 int, a5 int, primary key(a2,a5)) engine=ndb;
280 create table t3(a1 int, a2 int, a3 int, a4 int, a5 int, primary key(a2,a5)) engine=ndb;
281 create table t4(a1 int, a2 int, a3 int, a4 int, a5 int, primary key(a2,a5)) engine=ndb;
282
283 --disable_query_log
284 reset master;
285 --enable_query_log
286
287 # Now make the inserts and update
288 insert into t1 values (1,1,1,1,1), (2,2,2,2,2), (3,3,3,3,3), (4,4,4,4,4);
289 insert into t2 values (1,1,1,1,1), (2,2,2,2,2), (3,3,3,3,3), (4,4,4,4,4);
290 insert into t3 values (1,1,1,1,1), (2,2,2,2,2), (3,3,3,3,3), (4,4,4,4,4);
291 insert into t4 values (1,1,1,1,1), (2,2,2,2,2), (3,3,3,3,3), (4,4,4,4,4);
292 update t1 set a3=111 where a2=1;
293 update t1 set a1=222, a4=222 where a2=2;
294 update t2 set a3=111 where a2=1;
295 update t2 set a1=222, a4=222 where a2=2;
296 update t3 set a3=111 where a2=1;
297 update t3 set a1=222, a4=222 where a2=2;
298 update t4 set a3=111 where a2=1;
299 update t4 set a1=222, a4=222 where a2=2;
300
301 --disable_query_log
302 # Add an event-stream marker
303 create table stream_marker(a int) engine=ndb;
304 drop table stream_marker;
305 --let $wait_binlog_event=stream_marker
306 --enable_query_log
307
308 # Now let's trim the Binlogs on each server
309 connection mysqld1;
310 --source include/wait_for_binlog_event.inc
311 flush logs;
312
313 --source suite/ndb_binlog/t/ndb_binlog_get_binlog_stmts.inc
314
315 # Clear the table
316 delete from t1;
317 delete from t2;
318 delete from t3;
319 delete from t4;
320
321 --disable_query_log
322 let $MYSQLD_DATADIR= `select @@datadir;`;
323 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
324 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
325
326 --enable_query_log
327 select * from t1 order by a2,a5;
328 select * from t2 order by a2,a5;
329 select * from t3 order by a2,a5;
330 select * from t4 order by a2,a5;
331
332 drop table t1, t2, t3, t4;
333 drop table mysql.ndb_replication;
334
335 # Bug#46662
336 # Replicating changes to tables with unique indexes
337 # The fix to bug#27378 results in the slave using NdbApi's write()
338 # mechanism when applying WRITE_ROW events to tables with unique
339 # indices.
340 #
341 # If this is not done then the slave attempts to partially use SQL
342 # REPLACE semantics when applying WRITE_ROW events to tables with
343 # unique indexes, which is not good and the slave fails with a
344 # duplicate key error on the primary key.
345 #
346 # The fix to Bug#46662 aims to correct this, so that replicated
347 # updates to tables with unique indices can work.
348 # Note that other issues with replicating into tables with unique
349 # indexes remain.
350 #
351
352 connection mysqld1;
353 reset master;
354 show variables like 'ndb_log_update%';
355
356 create table bah (tst int primary key, cvy int, sqs int, unique(sqs)) engine=ndb;
357
358 insert into bah values (1,1,1);
359
360 # Wait for epoch to complete in Binlog
361 --disable_query_log
362 create table dummy (a int primary key) engine=ndb;
363 --enable_query_log
364
365 # Now perform update
366 # This will be logged as WRITE
367 # Without ability to use NdbApi write() for replace, mysqlbinlog
368 # application will fail with duplicate key error on insert.
369 update bah set cvy= 2 where tst=1;
370
371 select * from bah order by tst;
372
373 # Wait for epoch to complete in Binlog
374 --disable_query_log
375 drop table dummy;
376 flush logs;
377 --enable_query_log
378
379 drop table bah;
380
381 # Now let's re-apply the binlog
382 # Without fix, this fails with duplicate PK error
383 --echo Manually applying captured binlog
384 --disable_query_log
385 let $MYSQLD_DATADIR= `select @@datadir;`;
386 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000001 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
387 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
388
389 --enable_query_log
390 select * from bah order by tst;
391
392 drop table bah;
393
394 # Bug #14615095 ERROR 839 'ILLEGAL NULL ATTRIBUTE' WHEN REPLAYING BINLOG
395 # When applying WRITE_ROW events to tables where the rows are missing
396 # any errors should be ignored
397
398 connection mysqld1;
399 reset master;
400 show variables like '%log_update%';
401
402 CREATE TABLE `t1` (
403 `charId` varchar(60) NOT NULL,
404 `enumId` enum('A','B','C') NOT NULL,
405 `val` bigint(20) NOT NULL,
406 `version` int(11) NOT NULL,
407 PRIMARY KEY (`charId`,`enumId`)
408 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
409
410 INSERT INTO t1 VALUES ('', 'A', 0, 1);
411
412 --disable_query_log
413 # Add an event-stream marker
414 create table stream_marker(a int) engine=ndb;
415 drop table stream_marker;
416 --let $wait_binlog_event=stream_marker
417 --enable_query_log
418
419 # Wait until the INSERT statement is confirmed to have made it into the current binary log
420 --source include/wait_for_binlog_event.inc
421 FLUSH LOGS;
422
423 UPDATE t1 SET val = val + 1 WHERE charId = '';
424
425 --disable_query_log
426 # Add an event-stream marker
427 create table stream_marker(a int) engine=ndb;
428 drop table stream_marker;
429 --let $wait_binlog_event=stream_marker
430 --enable_query_log
431
432 # Wait until the UPDATE statement is confirmed to have made it into the current binary log
433 --let $wait_binlog_file=binlog.000002
434 --source include/wait_for_binlog_event.inc
435 FLUSH LOGS;
436
437 DELETE FROM t1 WHERE charId = '';
438
439 --disable_query_log
440 # Add an event-stream marker
441 create table stream_marker(a int) engine=ndb;
442 drop table stream_marker;
443 --let $wait_binlog_event=stream_marker
444 --enable_query_log
445
446 # Wait until the DELETE statement is confirmed to have made it into the current binary log
447 --let $wait_binlog_file=binlog.000003
448 --source include/wait_for_binlog_event.inc
449 FLUSH LOGS;
450
451 # Now let's re-apply the binlog from the UPDATE
452 # Without fix, this fails with 'Illegal null attribute'
453 --echo Manually applying captured binlog
454 --disable_query_log
455 let $MYSQLD_DATADIR= `select @@datadir;`;
456 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000002 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
457 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
458
459 --enable_query_log
460 # Check that the table is still empty
461 select * from t1;
462
463 drop table t1;
464
465 # Bug #14678088 CAN'T FIND RECORD IN
466 # We need to be idempotent when applying binlog
467 # test insert of existing row, update and delete
468 # of non-existing row
469
470 connection mysqld1;
471 reset master;
472 show variables like '%log_update%';
473
474 create table t1 (pk int not null primary key, name varchar(256)) engine = ndb;
475
476 --disable_query_log
477 # Add an event-stream marker
478 create table stream_marker(a int) engine=ndb;
479 drop table stream_marker;
480 --let $wait_binlog_event=stream_marker
481 --enable_query_log
482
483 # Wait until all statements are confirmed to have made it into the current binary log
484 --let $wait_binlog_file=binlog.000001
485 --source include/wait_for_binlog_event.inc
486 FLUSH LOGS;
487
488 insert into t1 values (0, "zero"),(1,"one"),(2,"two"),(3,"three"),(4,"four"),(5,"five"),(6,"six"),(7,"seven"),(8,"eight"),(9,"nine");
489 select * from t1 order by pk;
490
491 update t1 set name = "even" where pk in (0,2,4,6,8);
492 update t1 set name = "odd" where pk in (1,3,5,7,9);
493
494 delete from t1 where name = "odd";
495
496 select * from t1 order by pk;
497
498 --disable_query_log
499 # Add an event-stream marker
500 create table stream_marker(a int) engine=ndb;
501 drop table stream_marker;
502 --let $wait_binlog_event=stream_marker
503 --enable_query_log
504
505 # Wait until all statements are confirmed to have made it into the current binary log
506 --let $wait_binlog_file=binlog.000002
507 --source include/wait_for_binlog_event.inc
508 FLUSH LOGS;
509
510 truncate t1;
511 insert into t1 values (0, "zero"),(1,"one"),(2,"two"),(3,"three"),(4,"four"),(5,"five"),(6,"six"),(7,"seven"),(8,"eight"),(9,"nine");
512 select * from t1 order by pk;
513
514 --disable_query_log
515 # Add an event-stream marker
516 create table stream_marker(a int) engine=ndb;
517 drop table stream_marker;
518 --let $wait_binlog_event=stream_marker
519 --enable_query_log
520
521 # Wait until all statements are confirmed to have made it into the current binary log
522 --let $wait_binlog_file=binlog.000003
523 --source include/wait_for_binlog_event.inc
524 FLUSH LOGS;
525
526 # Now let's re-apply the binlog INSERT,UPDATE,DELETE
527 # Without fix, this fails with 'Illegal null attribute'
528 --echo Manually applying captured binlog
529 --disable_query_log
530 let $MYSQLD_DATADIR= `select @@datadir;`;
531 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/binlog.000002 > $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
532 --exec $MYSQL -uroot < $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
533
534 --enable_query_log
535 select * from t1 order by pk;
536
537 drop table t1;
538 --remove_file $MYSQLTEST_VARDIR/tmp/ndb_binlog_mysqlbinlog.sql
539