Debian Codegraph @

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

raw
1 --source suite/versioning/engines.inc
2 --source include/have_partition.inc
3 --source suite/versioning/common.inc
4 --source include/master-slave.inc
5
6 #BUG#12662190 - COM_COMMIT IS NOT INCREMENTED FROM THE BINARY LOGS ON SLAVE, COM_BEGIN IS
7 #Testing command counters -BEFORE.
8 #Storing the before counts of Slave
9 connection slave;
10 --source suite/versioning/common.inc
11 let $slave_com_commit_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_commit', Value, 1);
12 let $slave_com_insert_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_insert', Value, 1);
13 let $slave_com_delete_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_delete', Value, 1);
14 let $slave_com_update_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_update', Value, 1);
15 set @@session.time_zone='+00:00';
16
17 connection master;
18 CREATE TABLE t1 (x int) with system versioning;
19 insert into t1 values (1);
20 select * from t1 order by x;
21 delete from t1;
22 select * from t1 order by x;
23 select * from t1 for system_time all order by row_end, x;
24 sync_slave_with_master;
25 select * from t1 order by x;
26 select * from t1 for system_time all order by row_end, x;
27
28 connection master;
29 insert into t1 values (2);
30 sync_slave_with_master;
31 select * from t1 order by x;
32
33 connection master;
34 update t1 set x = 3;
35 sync_slave_with_master;
36 select * from t1 order by x;
37 select * from t1 for system_time all order by row_end, x;
38
39 --echo # check unversioned -> versioned replication
40 connection master;
41 create or replace table t1 (x int primary key);
42 sync_slave_with_master;
43 alter table t1 with system versioning;
44
45 connection master;
46 insert into t1 values (1);
47 sync_slave_with_master;
48 select * from t1 order by x;
49 select * from t1 for system_time all order by row_end, x;
50
51 connection master;
52 update t1 set x= 2 where x = 1;
53 sync_slave_with_master;
54 select * from t1 order by x;
55 select * from t1 for system_time all order by row_end, x;
56
57 connection master;
58 delete from t1;
59 sync_slave_with_master;
60 select * from t1 order by x;
61 select * from t1 for system_time all order by row_end, x;
62
63 --echo # same thing (UPDATE, DELETE), but without PK
64 connection master;
65 create or replace table t1 (x int);
66 sync_slave_with_master;
67 alter table t1 with system versioning;
68
69 connection master;
70 insert into t1 values (1);
71 update t1 set x= 2 where x = 1;
72 sync_slave_with_master;
73 select * from t1 order by x;
74 select * from t1 for system_time all order by row_end, x;
75
76 connection master;
77 delete from t1;
78 sync_slave_with_master;
79 select * from t1 order by x;
80 select * from t1 for system_time all order by row_end, x;
81
82 --echo # multi-update
83 connection master;
84 create or replace table t1 (x int) with system versioning;
85 create or replace table t2 (x int) with system versioning;
86 insert into t1 values (1);
87 insert into t2 values (2);
88 update t1, t2 set t1.x=11, t2.x=22;
89 sync_slave_with_master;
90 select * from t1 order by x;
91 select * from t2 order by x;
92 select * from t1 for system_time all order by row_end, x;
93 select * from t2 for system_time all order by row_end, x;
94
95 --echo # MDEV-14767 system_versioning_alter_history breaks ALTER replication
96 --echo ## Case 1: KEEP on the master, ALTER will work on the slave
97 connection master;
98 create or replace table t1 (a int) with system versioning;
99 set system_versioning_alter_history= KEEP;
100 alter table t1 add column b int;
101 sync_slave_with_master;
102 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
103 show create table t1;
104
105 --echo ## Case 2: ERROR on the master, it'll fail on the master, the slave won't see it
106 connection master;
107 set system_versioning_alter_history= ERROR;
108 --error ER_VERS_ALTER_NOT_ALLOWED
109 alter table t1 drop column b;
110 sync_slave_with_master;
111 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
112 show create table t1;
113
114 --echo ## Case 3: table is not versioned on the master, ALTER will work on the slave
115 connection master;
116 create or replace table t1 (a int);
117 sync_slave_with_master;
118 create or replace table t1 (a int) with system versioning;
119 connection master;
120 alter table t1 add column b int;
121 sync_slave_with_master;
122 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
123 show create table t1;
124
125 connection master;
126 drop table t1, t2;
127
128 #
129 # MDEV-15395 Wrong result or Assertion `old_part_id == m_last_part' failed in ha_partition::update_row on slave
130 #
131 create table t1 (i int) with system versioning partition by system_time limit 8 ( partition p1 history, partition p2 history, partition pn current );
132 insert into t1 values (1);
133 update t1 set i = 1;
134 update t1 set i = 0;
135 sync_slave_with_master;
136 connection master;
137 drop table t1;
138
139
140 #
141 # MDEV-30430: Enabling system versioning on tables without primary key breaks replication
142 # Note that bugs are only present with row binlog format
143 #
144 --echo # check versioned -> versioned replication without any keys on duplicate records
145 connection master;
146 create table t1 (a INT) with system versioning;
147 insert into t1 values (1);
148 insert into t1 values (1);
149 delete from t1;
150 sync_slave_with_master;
151 --let $diff_tables= master:test.t1,slave:test.t1
152 --source include/diff_tables.inc
153 connection master;
154 drop table t1;
155 sync_slave_with_master;
156
157 --echo # check unversioned -> versioned replication with non-unique keys on duplicate records
158 connection master;
159 set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b));
160 connection slave;
161 set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b)) with system versioning;
162 connection master;
163 insert into t1 values (1,1);
164 insert into t1 values (1,1);
165 delete from t1;
166 sync_slave_with_master;
167 --let $diff_tables= master:test.t1,slave:test.t1
168 --source include/diff_tables.inc
169
170 connection master;
171 drop table t1;
172
173 --echo #
174 --echo # MDEV-31313 SYSTEM VERSIONING and FOREIGN KEY CASCADE create orphan rows on replica
175 --echo #
176 create table parent (
177 id int(11) not null auto_increment,
178 processdate datetime default null,
179 primary key (id)
180 ) engine=innodb with system versioning;
181
182 set timestamp= unix_timestamp('2000-01-01 00:00:00');
183 insert into parent values (1, now());
184
185 create table child (
186 id int(11) not null auto_increment,
187 ch_name varchar(30),
188 andreid int(11) default null,
189 primary key (id),
190 key andreid (andreid),
191 constraint fk_andreid foreign key (andreid) references parent (id) on delete cascade
192 ) engine=innodb with system versioning;
193
194 set timestamp= unix_timestamp('2000-01-01 00:00:01');
195 insert into child values (null, 'vimtomar', 1);
196
197 set timestamp= unix_timestamp('2000-01-01 00:00:02');
198 delete from parent where id = 1;
199
200 select check_row(row_start, row_end) from parent for system_time all;
201 select check_row(row_start, row_end) from child for system_time all;
202 select * from child;
203 select * from parent;
204
205 sync_slave_with_master;
206
207 # Annoying tweaking of microseconds in slave row_end, so row_end can be <= row_start
208 select check_row_slave(row_start, row_end) from parent for system_time all;
209 select check_row_slave(row_start, row_end) from child for system_time all;
210 select * from child;
211 select * from parent;
212
213 # Cleanup
214 --source suite/versioning/common_finish.inc
215 --connection master
216 set timestamp= default;
217 drop table child;
218 drop table parent;
219
220 sync_slave_with_master;
221 connection master;
222
223 --source suite/versioning/common_finish.inc
224 --echo #
225 --echo # MDEV-17554 Auto-create new partition for system versioned tables
226 --echo # with history partitioned by INTERVAL/LIMIT
227 --echo #
228 set timestamp= unix_timestamp('2000-01-01 00:00:00');
229 create or replace table t1 (x int) with system versioning
230 partition by system_time interval 1 hour auto;
231 insert t1 values ();
232 set timestamp= unix_timestamp('2000-01-01 01:00:00');
233 delete from t1;
234 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
235 show create table t1;
236 --sync_slave_with_master
237 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
238 show create table t1;
239 --connection master
240 drop table t1;
241 set timestamp= default;
242
243 --echo #
244 --echo # MDEV-25477 Auto-create breaks replication when triggering event was not replicated
245 --echo #
246
247 set timestamp= unix_timestamp('2001-01-01 01:00:00');
248 --echo # ROLLBACK
249 create table t (a int) with system versioning
250 partition by system_time interval 1 hour auto;
251 insert into t values (1), (2);
252 set @@timestamp= @@timestamp + 3601;
253
254 start transaction;
255 delete from t;
256 --disable_warnings
257 rollback;
258 # Warning: Some non-transactional changed tables couldn't be rolled back
259 --enable_warnings
260
261 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
262 show create table t;
263 --sync_slave_with_master
264 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
265 show create table t;
266 --connection master
267 alter table t drop partition p0;
268 --sync_slave_with_master
269
270 --echo # INSERT .. ODKU
271 --connection master
272 create or replace table t (a int primary key) with system versioning
273 partition by system_time interval 1 hour auto;
274 insert into t values (1), (2);
275
276 set @@timestamp= @@timestamp + 3601;
277 insert into t values (1) on duplicate key update a= a;
278 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
279 show create table t;
280 --sync_slave_with_master
281 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
282 show create table t;
283 --connection master
284 alter table t drop partition p0;
285 --sync_slave_with_master
286
287 --echo # INSERT .. SELECT .. ODKU
288 --connection master
289 create or replace table t (a int primary key) with system versioning
290 partition by system_time interval 1 hour auto;
291 insert into t values (1), (2);
292
293 set @@timestamp= @@timestamp + 3601;
294 --disable_warnings
295 call mtr.add_suppression("Unsafe statement written to the binary log");
296 insert t select a from t where a = 1 limit 0 on duplicate key update a= 1;
297 --enable_warnings
298 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
299 show create table t;
300 --sync_slave_with_master
301 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
302 show create table t;
303 --connection master
304 alter table t drop partition p0;
305 --sync_slave_with_master
306
307 --echo # UPDATE
308 --connection master
309 create or replace table t (a int) with system versioning
310 partition by system_time interval 1 hour auto;
311 insert into t values (1), (2);
312
313 set @@timestamp= @@timestamp + 3601;
314 update t set a= 3 limit 0;
315 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
316 show create table t;
317 --sync_slave_with_master
318 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
319 show create table t;
320 --connection master
321 alter table t drop partition p0;
322 --sync_slave_with_master
323
324 --echo # DELETE
325 --connection master
326 create or replace table t (a int) with system versioning
327 partition by system_time interval 1 hour auto;
328 insert into t values (1), (2);
329
330 set @@timestamp= @@timestamp + 3601;
331 delete from t limit 0;
332 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
333 show create table t;
334 --sync_slave_with_master
335 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
336 show create table t;
337 --connection master
338 alter table t drop partition p0;
339 --sync_slave_with_master
340
341 --echo # Multi-update
342 --connection master
343 create or replace table t (a int) with system versioning
344 partition by system_time interval 1 hour auto;
345 create or replace table t2 (b int);
346
347 insert into t values (0), (1);
348 insert into t2 values (10), (20);
349 set @@timestamp= @@timestamp + 3601;
350 # Note: limit 0 is not important for multi-update/delete because they work
351 # via mysql_select(). OTOH limit 0 makes unwanted "unsafe" warning.
352 update t left join t2 on a > b set a= 4;
353
354 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
355 show create table t;
356 --sync_slave_with_master
357 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
358 show create table t;
359 --connection master
360 alter table t drop partition p0;
361 --sync_slave_with_master
362
363 --echo # Multi-delete
364 --connection master
365 create or replace table t (a int) with system versioning
366 partition by system_time interval 1 hour auto;
367 create or replace table t2 (b int);
368
369 insert into t values (0), (1);
370 insert into t2 values (10), (20);
371 set @@timestamp= @@timestamp + 3601;
372 delete t, t2 from t join t2 where a > b;
373
374 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
375 show create table t;
376 --sync_slave_with_master
377 --replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
378 show create table t;
379 --connection master
380 alter table t drop partition p0;
381 --sync_slave_with_master
382
383 --connection master
384 drop tables t, t2;
385 set timestamp= default;
386
387
388 --source suite/versioning/common.inc
389 --echo #
390 --echo # MDEV-16546 System versioning setting to allow history modification
391 --echo #
392 create table t1(x int) with system versioning;
393 insert into t1(x) values (1);
394 --error ER_BAD_FIELD_ERROR
395 insert into t1(x, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
396 set @@system_versioning_insert_history= 1;
397 insert into t1(x, row_start, row_end) values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
398 update t1 set x= x + 1;
399 sync_slave_with_master;
400 set @@session.time_zone='+00:00';
401 select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x;
402 select row_start = '1980-01-01 00:00:00', row_end = '1980-01-01 00:00:01' from t1 for system_time all where x = 3;
403
404 --echo ## INSERT..SELECT
405 connection master;
406 create or replace table t2 like t1;
407 set @@system_versioning_insert_history= 1;
408 insert into t2 (x, row_start, row_end) select x, row_start, row_end from t1 for system_time all;
409 sync_slave_with_master;
410 select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
411 select row_start = '1980-01-01 00:00:00', row_end = '1980-01-01 00:00:01' from t2 for system_time all where x = 3;
412
413 --echo # LOAD DATA
414 connection master;
415 --let DATAFILE= $MYSQLTEST_VARDIR/tmp/test_versioning_t3.data
416 --replace_result $DATAFILE DATAFILE
417 eval select x, row_start, row_end into outfile '$DATAFILE' from t1 for system_time all;
418 create or replace table t3 like t1;
419 set @@system_versioning_insert_history= 1;
420 --replace_result $DATAFILE DATAFILE
421 eval load data infile '$DATAFILE' into table t3 (x, row_start, row_end);
422 sync_slave_with_master;
423 select x, check_row_ts(row_start, row_end) from t3 for system_time all order by x;
424 select row_start = '1980-01-01 00:00:00', row_end = '1980-01-01 00:00:01' from t3 for system_time all where x = 3;
425 --remove_file $DATAFILE
426
427 --echo # why a slave cannot have system_versioning_insert_history always on
428 connection master;
429 set @@system_versioning_insert_history= 0;
430 set sql_mode='';
431 create or replace table t1 (a int,
432 rs timestamp(6) as row start, re timestamp(6) as row end,
433 period for system_time (rs,re)) with system versioning;
434 insert t1 values (1, '2000-01-01 02:03:04', '2001-01-01 02:03.04');
435 select a,check_row_ts(rs,re) from t1 for system_time all;
436 sync_slave_with_master;
437 select a,check_row_ts(rs,re) from t1 for system_time all;
438 set sql_mode=default;
439
440 connection master;
441 drop tables t1, t2, t3;
442
443
444 --echo #
445 --echo # MDEV-33330 Server crash or assertion failure in
446 --echo # binlog_get_pending_rows_event
447 --echo #
448
449 create or replace table t (x int) with system versioning
450 partition by system_time interval 1 minute auto partitions 3;
451 insert into t values (1);
452
453 connect (con1,localhost,root,,);
454 set timestamp= @@timestamp+120;
455 update t set x= x + 1;
456 connection default;
457
458 # Cleanup
459 disconnect con1;
460 drop table t;
461
462 #
463 # End of 11.2 tests
464 #
465
466 --source suite/versioning/common_finish.inc
467 --source include/rpl_end.inc