Debian Codegraph @

mysql-test/suite/versioning/r/rpl.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection slave;
4 set @@session.time_zone='+00:00';
5 connection master;
6 CREATE TABLE t1 (x int) with system versioning;
7 insert into t1 values (1);
8 select * from t1 order by x;
9 x
10 1
11 delete from t1;
12 select * from t1 order by x;
13 x
14 select * from t1 for system_time all order by row_end, x;
15 x
16 1
17 connection slave;
18 select * from t1 order by x;
19 x
20 select * from t1 for system_time all order by row_end, x;
21 x
22 1
23 connection master;
24 insert into t1 values (2);
25 connection slave;
26 select * from t1 order by x;
27 x
28 2
29 connection master;
30 update t1 set x = 3;
31 connection slave;
32 select * from t1 order by x;
33 x
34 3
35 select * from t1 for system_time all order by row_end, x;
36 x
37 1
38 2
39 3
40 # check unversioned -> versioned replication
41 connection master;
42 create or replace table t1 (x int primary key);
43 connection slave;
44 alter table t1 with system versioning;
45 connection master;
46 insert into t1 values (1);
47 connection slave;
48 select * from t1 order by x;
49 x
50 1
51 select * from t1 for system_time all order by row_end, x;
52 x
53 1
54 connection master;
55 update t1 set x= 2 where x = 1;
56 connection slave;
57 select * from t1 order by x;
58 x
59 2
60 select * from t1 for system_time all order by row_end, x;
61 x
62 1
63 2
64 connection master;
65 delete from t1;
66 connection slave;
67 select * from t1 order by x;
68 x
69 select * from t1 for system_time all order by row_end, x;
70 x
71 1
72 2
73 # same thing (UPDATE, DELETE), but without PK
74 connection master;
75 create or replace table t1 (x int);
76 connection slave;
77 alter table t1 with system versioning;
78 connection master;
79 insert into t1 values (1);
80 update t1 set x= 2 where x = 1;
81 connection slave;
82 select * from t1 order by x;
83 x
84 2
85 select * from t1 for system_time all order by row_end, x;
86 x
87 1
88 2
89 connection master;
90 delete from t1;
91 connection slave;
92 select * from t1 order by x;
93 x
94 select * from t1 for system_time all order by row_end, x;
95 x
96 1
97 2
98 # multi-update
99 connection master;
100 create or replace table t1 (x int) with system versioning;
101 create or replace table t2 (x int) with system versioning;
102 insert into t1 values (1);
103 insert into t2 values (2);
104 update t1, t2 set t1.x=11, t2.x=22;
105 connection slave;
106 select * from t1 order by x;
107 x
108 11
109 select * from t2 order by x;
110 x
111 22
112 select * from t1 for system_time all order by row_end, x;
113 x
114 1
115 11
116 select * from t2 for system_time all order by row_end, x;
117 x
118 2
119 22
120 # MDEV-14767 system_versioning_alter_history breaks ALTER replication
121 ## Case 1: KEEP on the master, ALTER will work on the slave
122 connection master;
123 create or replace table t1 (a int) with system versioning;
124 set system_versioning_alter_history= KEEP;
125 alter table t1 add column b int;
126 connection slave;
127 show create table t1;
128 Table Create Table
129 t1 CREATE TABLE `t1` (
130 `a` int(11) DEFAULT NULL,
131 `b` int(11) DEFAULT NULL
132 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
133 ## Case 2: ERROR on the master, it'll fail on the master, the slave won't see it
134 connection master;
135 set system_versioning_alter_history= ERROR;
136 alter table t1 drop column b;
137 ERROR HY000: Not allowed for system-versioned `test`.`t1`. Change @@system_versioning_alter_history to proceed with ALTER.
138 connection slave;
139 show create table t1;
140 Table Create Table
141 t1 CREATE TABLE `t1` (
142 `a` int(11) DEFAULT NULL,
143 `b` int(11) DEFAULT NULL
144 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
145 ## Case 3: table is not versioned on the master, ALTER will work on the slave
146 connection master;
147 create or replace table t1 (a int);
148 connection slave;
149 create or replace table t1 (a int) with system versioning;
150 connection master;
151 alter table t1 add column b int;
152 connection slave;
153 show create table t1;
154 Table Create Table
155 t1 CREATE TABLE `t1` (
156 `a` int(11) DEFAULT NULL,
157 `b` int(11) DEFAULT NULL
158 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
159 connection master;
160 drop table t1, t2;
161 create table t1 (i int) with system versioning partition by system_time limit 8 ( partition p1 history, partition p2 history, partition pn current );
162 insert into t1 values (1);
163 update t1 set i = 1;
164 update t1 set i = 0;
165 connection slave;
166 connection master;
167 drop table t1;
168 # check versioned -> versioned replication without any keys on duplicate records
169 connection master;
170 create table t1 (a INT) with system versioning;
171 insert into t1 values (1);
172 insert into t1 values (1);
173 delete from t1;
174 connection slave;
175 include/diff_tables.inc [master:test.t1,slave:test.t1]
176 connection master;
177 drop table t1;
178 connection slave;
179 # check unversioned -> versioned replication with non-unique keys on duplicate records
180 connection master;
181 set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b));
182 connection slave;
183 set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b)) with system versioning;
184 connection master;
185 insert into t1 values (1,1);
186 insert into t1 values (1,1);
187 delete from t1;
188 connection slave;
189 include/diff_tables.inc [master:test.t1,slave:test.t1]
190 connection master;
191 drop table t1;
192 #
193 # MDEV-31313 SYSTEM VERSIONING and FOREIGN KEY CASCADE create orphan rows on replica
194 #
195 create table parent (
196 id int(11) not null auto_increment,
197 processdate datetime default null,
198 primary key (id)
199 ) engine=innodb with system versioning;
200 set timestamp= unix_timestamp('2000-01-01 00:00:00');
201 insert into parent values (1, now());
202 create table child (
203 id int(11) not null auto_increment,
204 ch_name varchar(30),
205 andreid int(11) default null,
206 primary key (id),
207 key andreid (andreid),
208 constraint fk_andreid foreign key (andreid) references parent (id) on delete cascade
209 ) engine=innodb with system versioning;
210 set timestamp= unix_timestamp('2000-01-01 00:00:01');
211 insert into child values (null, 'vimtomar', 1);
212 set timestamp= unix_timestamp('2000-01-01 00:00:02');
213 delete from parent where id = 1;
214 select check_row(row_start, row_end) from parent for system_time all;
215 check_row(row_start, row_end)
216 HISTORICAL ROW
217 select check_row(row_start, row_end) from child for system_time all;
218 check_row(row_start, row_end)
219 HISTORICAL ROW
220 select * from child;
221 id ch_name andreid
222 select * from parent;
223 id processdate
224 connection slave;
225 select check_row_slave(row_start, row_end) from parent for system_time all;
226 check_row_slave(row_start, row_end)
227 HISTORICAL ROW
228 select check_row_slave(row_start, row_end) from child for system_time all;
229 check_row_slave(row_start, row_end)
230 HISTORICAL ROW
231 select * from child;
232 id ch_name andreid
233 select * from parent;
234 id processdate
235 connection master;
236 set timestamp= default;
237 drop table child;
238 drop table parent;
239 connection slave;
240 connection master;
241 #
242 # MDEV-17554 Auto-create new partition for system versioned tables
243 # with history partitioned by INTERVAL/LIMIT
244 #
245 set timestamp= unix_timestamp('2000-01-01 00:00:00');
246 create or replace table t1 (x int) with system versioning
247 partition by system_time interval 1 hour auto;
248 insert t1 values ();
249 set timestamp= unix_timestamp('2000-01-01 01:00:00');
250 delete from t1;
251 show create table t1;
252 Table Create Table
253 t1 CREATE TABLE `t1` (
254 `x` int(11) DEFAULT NULL
255 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
256 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
257 PARTITIONS 3
258 connection slave;
259 show create table t1;
260 Table Create Table
261 t1 CREATE TABLE `t1` (
262 `x` int(11) DEFAULT NULL
263 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
264 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
265 PARTITIONS 3
266 connection master;
267 drop table t1;
268 set timestamp= default;
269 #
270 # MDEV-25477 Auto-create breaks replication when triggering event was not replicated
271 #
272 set timestamp= unix_timestamp('2001-01-01 01:00:00');
273 # ROLLBACK
274 create table t (a int) with system versioning
275 partition by system_time interval 1 hour auto;
276 insert into t values (1), (2);
277 set @@timestamp= @@timestamp + 3601;
278 start transaction;
279 delete from t;
280 rollback;
281 show create table t;
282 Table Create Table
283 t CREATE TABLE `t` (
284 `a` int(11) DEFAULT NULL
285 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
286 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 01:00:00' AUTO
287 PARTITIONS 3
288 connection slave;
289 show create table t;
290 Table Create Table
291 t CREATE TABLE `t` (
292 `a` int(11) DEFAULT NULL
293 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
294 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 01:00:00' AUTO
295 PARTITIONS 3
296 connection master;
297 alter table t drop partition p0;
298 connection slave;
299 # INSERT .. ODKU
300 connection master;
301 create or replace table t (a int primary key) with system versioning
302 partition by system_time interval 1 hour auto;
303 insert into t values (1), (2);
304 set @@timestamp= @@timestamp + 3601;
305 insert into t values (1) on duplicate key update a= a;
306 show create table t;
307 Table Create Table
308 t CREATE TABLE `t` (
309 `a` int(11) NOT NULL,
310 PRIMARY KEY (`a`)
311 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
312 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 02:00:00' AUTO
313 PARTITIONS 3
314 connection slave;
315 show create table t;
316 Table Create Table
317 t CREATE TABLE `t` (
318 `a` int(11) NOT NULL,
319 PRIMARY KEY (`a`)
320 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
321 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 02:00:00' AUTO
322 PARTITIONS 3
323 connection master;
324 alter table t drop partition p0;
325 connection slave;
326 # INSERT .. SELECT .. ODKU
327 connection master;
328 create or replace table t (a int primary key) with system versioning
329 partition by system_time interval 1 hour auto;
330 insert into t values (1), (2);
331 set @@timestamp= @@timestamp + 3601;
332 call mtr.add_suppression("Unsafe statement written to the binary log");
333 insert t select a from t where a = 1 limit 0 on duplicate key update a= 1;
334 show create table t;
335 Table Create Table
336 t CREATE TABLE `t` (
337 `a` int(11) NOT NULL,
338 PRIMARY KEY (`a`)
339 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
340 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 03:00:00' AUTO
341 PARTITIONS 3
342 connection slave;
343 show create table t;
344 Table Create Table
345 t CREATE TABLE `t` (
346 `a` int(11) NOT NULL,
347 PRIMARY KEY (`a`)
348 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
349 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 03:00:00' AUTO
350 PARTITIONS 3
351 connection master;
352 alter table t drop partition p0;
353 connection slave;
354 # UPDATE
355 connection master;
356 create or replace table t (a int) with system versioning
357 partition by system_time interval 1 hour auto;
358 insert into t values (1), (2);
359 set @@timestamp= @@timestamp + 3601;
360 update t set a= 3 limit 0;
361 show create table t;
362 Table Create Table
363 t CREATE TABLE `t` (
364 `a` int(11) DEFAULT NULL
365 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
366 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 04:00:00' AUTO
367 PARTITIONS 3
368 connection slave;
369 show create table t;
370 Table Create Table
371 t CREATE TABLE `t` (
372 `a` int(11) DEFAULT NULL
373 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
374 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 04:00:00' AUTO
375 PARTITIONS 3
376 connection master;
377 alter table t drop partition p0;
378 connection slave;
379 # DELETE
380 connection master;
381 create or replace table t (a int) with system versioning
382 partition by system_time interval 1 hour auto;
383 insert into t values (1), (2);
384 set @@timestamp= @@timestamp + 3601;
385 delete from t limit 0;
386 show create table t;
387 Table Create Table
388 t CREATE TABLE `t` (
389 `a` int(11) DEFAULT NULL
390 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
391 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 05:00:00' AUTO
392 PARTITIONS 3
393 connection slave;
394 show create table t;
395 Table Create Table
396 t CREATE TABLE `t` (
397 `a` int(11) DEFAULT NULL
398 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
399 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 05:00:00' AUTO
400 PARTITIONS 3
401 connection master;
402 alter table t drop partition p0;
403 connection slave;
404 # Multi-update
405 connection master;
406 create or replace table t (a int) with system versioning
407 partition by system_time interval 1 hour auto;
408 create or replace table t2 (b int);
409 insert into t values (0), (1);
410 insert into t2 values (10), (20);
411 set @@timestamp= @@timestamp + 3601;
412 update t left join t2 on a > b set a= 4;
413 show create table t;
414 Table Create Table
415 t CREATE TABLE `t` (
416 `a` int(11) DEFAULT NULL
417 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
418 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 06:00:00' AUTO
419 PARTITIONS 3
420 connection slave;
421 show create table t;
422 Table Create Table
423 t CREATE TABLE `t` (
424 `a` int(11) DEFAULT NULL
425 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
426 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 06:00:00' AUTO
427 PARTITIONS 3
428 connection master;
429 alter table t drop partition p0;
430 connection slave;
431 # Multi-delete
432 connection master;
433 create or replace table t (a int) with system versioning
434 partition by system_time interval 1 hour auto;
435 create or replace table t2 (b int);
436 insert into t values (0), (1);
437 insert into t2 values (10), (20);
438 set @@timestamp= @@timestamp + 3601;
439 delete t, t2 from t join t2 where a > b;
440 show create table t;
441 Table Create Table
442 t CREATE TABLE `t` (
443 `a` int(11) DEFAULT NULL
444 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
445 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 07:00:00' AUTO
446 PARTITIONS 3
447 connection slave;
448 show create table t;
449 Table Create Table
450 t CREATE TABLE `t` (
451 `a` int(11) DEFAULT NULL
452 ) ENGINE=ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
453 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 07:00:00' AUTO
454 PARTITIONS 3
455 connection master;
456 alter table t drop partition p0;
457 connection slave;
458 connection master;
459 drop tables t, t2;
460 set timestamp= default;
461 #
462 # MDEV-16546 System versioning setting to allow history modification
463 #
464 create table t1(x int) with system versioning;
465 insert into t1(x) values (1);
466 insert into t1(x, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
467 ERROR 42S22: Unknown column 'row_start' in 'INSERT INTO'
468 set @@system_versioning_insert_history= 1;
469 insert into t1(x, row_start, row_end) values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
470 update t1 set x= x + 1;
471 connection slave;
472 set @@session.time_zone='+00:00';
473 select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x;
474 x check_row_ts(row_start, row_end)
475 1 HISTORICAL ROW
476 2 CURRENT ROW
477 3 HISTORICAL ROW
478 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;
479 row_start = '1980-01-01 00:00:00' row_end = '1980-01-01 00:00:01'
480 1 1
481 ## INSERT..SELECT
482 connection master;
483 create or replace table t2 like t1;
484 set @@system_versioning_insert_history= 1;
485 insert into t2 (x, row_start, row_end) select x, row_start, row_end from t1 for system_time all;
486 connection slave;
487 select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
488 x check_row_ts(row_start, row_end)
489 1 HISTORICAL ROW
490 2 CURRENT ROW
491 3 HISTORICAL ROW
492 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;
493 row_start = '1980-01-01 00:00:00' row_end = '1980-01-01 00:00:01'
494 1 1
495 # LOAD DATA
496 connection master;
497 select x, row_start, row_end into outfile 'DATAFILE' from t1 for system_time all;
498 create or replace table t3 like t1;
499 set @@system_versioning_insert_history= 1;
500 load data infile 'DATAFILE' into table t3 (x, row_start, row_end);
501 connection slave;
502 select x, check_row_ts(row_start, row_end) from t3 for system_time all order by x;
503 x check_row_ts(row_start, row_end)
504 1 HISTORICAL ROW
505 2 CURRENT ROW
506 3 HISTORICAL ROW
507 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;
508 row_start = '1980-01-01 00:00:00' row_end = '1980-01-01 00:00:01'
509 1 1
510 # why a slave cannot have system_versioning_insert_history always on
511 connection master;
512 set @@system_versioning_insert_history= 0;
513 set sql_mode='';
514 create or replace table t1 (a int,
515 rs timestamp(6) as row start, re timestamp(6) as row end,
516 period for system_time (rs,re)) with system versioning;
517 insert t1 values (1, '2000-01-01 02:03:04', '2001-01-01 02:03.04');
518 Warnings:
519 Warning 1906 The value specified for generated column 'rs' in table 't1' has been ignored
520 Warning 1906 The value specified for generated column 're' in table 't1' has been ignored
521 select a,check_row_ts(rs,re) from t1 for system_time all;
522 a check_row_ts(rs,re)
523 1 CURRENT ROW
524 connection slave;
525 select a,check_row_ts(rs,re) from t1 for system_time all;
526 a check_row_ts(rs,re)
527 1 CURRENT ROW
528 set sql_mode=default;
529 connection master;
530 drop tables t1, t2, t3;
531 #
532 # MDEV-33330 Server crash or assertion failure in
533 # binlog_get_pending_rows_event
534 #
535 create or replace table t (x int) with system versioning
536 partition by system_time interval 1 minute auto partitions 3;
537 insert into t values (1);
538 connect con1,localhost,root,,;
539 set timestamp= @@timestamp+120;
540 update t set x= x + 1;
541 connection default;
542 disconnect con1;
543 drop table t;
544 include/rpl_end.inc