Debian Codegraph @

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

raw
1 if (!$TEST_VERSIONING_SO)
2 {
3 --skip needs test_versioning plugin
4 }
5 --source include/have_innodb.inc
6 --source include/have_partition.inc
7 --source include/default_charset.inc
8
9 --disable_query_log
10 --eval install plugin test_versioning soname '$TEST_VERSIONING_SO'
11 --enable_query_log
12
13 set default_storage_engine= innodb;
14
15 create or replace table t1 (
16 x int,
17 sys_trx_start bigint(20) unsigned as row start invisible,
18 sys_trx_end bigint(20) unsigned as row end invisible,
19 period for system_time (sys_trx_start, sys_trx_end)
20 ) with system versioning;
21
22 --enable_prepare_warnings
23
24 --echo # No history inside the transaction
25 start transaction;
26 insert into t1 (x) values (1);
27 update t1 set x= x + 1;
28 update t1 set x= x + 1;
29 commit;
30 select *, sys_trx_start > 1, sys_trx_end from t1 for system_time all;
31
32 --echo # ALTER ADD SYSTEM VERSIONING should write to mysql.transaction_registry
33 set @@system_versioning_alter_history=keep;
34
35 create or replace table t1 (x int);
36 insert into t1 values (1);
37 alter table t1
38 add column s bigint unsigned as row start,
39 add column e bigint unsigned as row end,
40 add period for system_time(s, e),
41 add system versioning,
42 algorithm=inplace;
43 --disable_cursor_protocol
44 select s from t1 into @trx_start;
45 --enable_cursor_protocol
46 select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start;
47
48 create or replace table t1 (x int);
49 --disable_cursor_protocol
50 select count(*) from mysql.transaction_registry into @tmp;
51 --enable_cursor_protocol
52 alter table t1
53 add column s bigint unsigned as row start,
54 add column e bigint unsigned as row end,
55 add period for system_time(s, e),
56 add system versioning,
57 algorithm=inplace;
58 select count(*) = @tmp from mysql.transaction_registry;
59
60 create or replace table t1 (x int);
61 insert into t1 values (1);
62 alter table t1
63 add column s bigint unsigned as row start,
64 add column e bigint unsigned as row end,
65 add period for system_time(s, e),
66 add system versioning,
67 algorithm=copy;
68 --disable_cursor_protocol
69 select s from t1 into @trx_start;
70 --enable_cursor_protocol
71 select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start;
72
73 create or replace table t1 (x int);
74 --disable_cursor_protocol
75 select count(*) from mysql.transaction_registry into @tmp;
76 --enable_cursor_protocol
77 alter table t1
78 add column s bigint unsigned as row start,
79 add column e bigint unsigned as row end,
80 add period for system_time(s, e),
81 add system versioning,
82 algorithm=copy;
83 # With MDEV-14511 the transaction will be registered even for empty tables.
84 select count(*) = @tmp + 1 from mysql.transaction_registry;
85
86 --echo # TRX_ID to TIMESTAMP versioning switch
87 create or replace table t1 (
88 x int,
89 sys_start bigint unsigned as row start invisible,
90 sys_end bigint unsigned as row end invisible,
91 period for system_time (sys_start, sys_end)
92 ) with system versioning;
93 insert into t1 values (1);
94 alter table t1 drop column sys_start, drop column sys_end;
95 select row_end = 18446744073709551615 as transaction_based from t1 for system_time all;
96
97 --echo # Simple vs SEES algorithms
98 create or replace table t1 (
99 x int,
100 sys_start bigint(20) unsigned as row start invisible,
101 sys_end bigint(20) unsigned as row end invisible,
102 period for system_time (sys_start, sys_end)
103 ) with system versioning;
104
105 # MDEV-515 takes X-lock on the table for the first insert
106 # So concurrent DML won't happen on the table
107 INSERT INTO t1 VALUES(100);
108
109 set transaction isolation level read committed;
110 start transaction;
111 insert into t1 values (1);
112 --connect (con1,localhost,root,,test)
113 set transaction isolation level read committed;
114 start transaction;
115 insert into t1 values (2);
116 --connect (con2,localhost,root,,test)
117 set transaction isolation level read committed;
118 start transaction;
119 insert into t1 values (3);
120 commit;
121 --disconnect con2
122 --connection default
123 --sleep 0.01
124 set @ts1= sysdate(6);
125 --connection con1
126 commit;
127 --disconnect con1
128 --connection default
129 --sleep 0.01
130 set @ts2= sysdate(6);
131 commit;
132 --sleep 0.01
133 set @ts3= sysdate(6);
134
135 --disable_cursor_protocol
136 select sys_start from t1 where x = 1 into @trx_id1;
137 select sys_start from t1 where x = 2 into @trx_id2;
138 select sys_start from t1 where x = 3 into @trx_id3;
139 --enable_cursor_protocol
140
141 select @trx_id1 < @trx_id2, @trx_id2 < @trx_id3;
142 select @ts1 < @ts2, @ts2 < @ts3;
143
144 --echo # MVCC is resolved
145 select * from t1 for system_time as of transaction @trx_id1;
146 select * from t1 for system_time as of timestamp @ts1;
147 select * from t1 for system_time as of transaction @trx_id2;
148 select * from t1 for system_time as of timestamp @ts2;
149 select * from t1 for system_time as of transaction @trx_id3;
150 select * from t1 for system_time as of timestamp @ts3;
151
152 --echo #
153 --echo # MDEV-15427 IB: TRX_ID based operations inside transaction generate history
154 --echo #
155 create or replace table t1(
156 x int(10),
157 row_start bigint(20) unsigned as row start,
158 row_end bigint(20) unsigned as row end,
159 period for system_time(row_start, row_end)
160 ) with system versioning;
161
162 begin;
163 insert into t1 (x) values (1);
164 delete from t1;
165 commit;
166 select x from t1 for system_time all;
167
168 insert into t1 (x) values (2);
169 begin;
170 update t1 set x= 3;
171 update t1 set x= 4;
172 commit;
173 select x, row_start < row_end from t1 for system_time all;
174
175 --echo #
176 --echo # MDEV-15951 system versioning by trx id doesn't work with partitioning
177 --echo # currently trx_id does not support partitioning by system_time
178 --echo #
179 --error ER_VERS_FIELD_WRONG_TYPE
180 create or replace table t1(
181 i int,
182 row_start bigint unsigned generated always as row start,
183 row_end bigint unsigned generated always as row end,
184 period for system_time(row_start, row_end)
185 ) engine=InnoDB with system versioning partition by system_time (
186 partition p0 history,
187 partition pn current
188 );
189
190 create or replace table t1(
191 i int,
192 row_start bigint unsigned generated always as row start,
193 row_end bigint unsigned generated always as row end,
194 period for system_time(row_start, row_end)
195 ) engine=InnoDB with system versioning;
196
197 --replace_regex /#sql-[0-9a-f_]*/#sql-temporary/
198 --error ER_VERS_FIELD_WRONG_TYPE
199 alter table t1 partition by system_time (
200 partition p0 history,
201 partition pn current
202 );
203
204 drop table t1;
205
206 --error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
207 create or replace table t (
208 a int primary key,
209 row_start bigint unsigned as row start invisible,
210 row_end bigint unsigned as row end invisible,
211 period for system_time(row_start, row_end)
212 ) engine=innodb with system versioning
213 partition by key() (
214 partition p1,
215 partition p2
216 );
217
218 --error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
219 create or replace table t (
220 a int primary key,
221 row_start bigint unsigned as row start invisible,
222 row_end bigint unsigned as row end invisible,
223 period for system_time(row_start, row_end)
224 ) engine=innodb with system versioning
225 partition by key(a, row_start) (
226 partition p1,
227 partition p2
228 );
229
230 --error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
231 create or replace table t (
232 a int primary key,
233 row_start bigint unsigned as row start invisible,
234 row_end bigint unsigned as row end invisible,
235 period for system_time(row_start, row_end)
236 ) engine=innodb with system versioning
237 partition by hash(a + row_end * 2) (
238 partition p1,
239 partition p2
240 );
241
242 --error ER_VERS_TRX_PART_HISTORIC_ROW_NOT_SUPPORTED
243 create or replace table t (
244 a int primary key,
245 row_start bigint unsigned as row start invisible,
246 row_end bigint unsigned as row end invisible,
247 period for system_time(row_start, row_end)
248 ) engine=innodb with system versioning
249 partition by range columns (a, row_start) (
250 partition p1 values less than (100, 100)
251 );
252
253 --echo #
254 --echo # MDEV-16010 Too many rows with AS OF point_in_the_past_or_NULL
255 --echo #
256 create or replace table t1 (
257 x int,
258 row_start bigint unsigned as row start invisible,
259 row_end bigint unsigned as row end invisible,
260 period for system_time (row_start, row_end)
261 ) with system versioning engine innodb;
262 insert into t1 (x) values (1);
263 delete from t1;
264 select * from t1 for system_time as of timestamp'1990-1-1 00:00';
265 select * from t1 for system_time as of NULL;
266
267 --echo # MDEV-16024 transaction_registry.begin_timestamp is wrong for explicit transactions
268 create or replace table t1 (
269 x int(11) default null,
270 row_start bigint(20) unsigned generated always as row start invisible,
271 row_end bigint(20) unsigned generated always as row end invisible,
272 period for system_time (row_start, row_end)
273 ) engine=innodb with system versioning;
274 begin;
275 set @ts1= now(6);
276 --sleep 0.01
277 insert into t1 values (1);
278 commit;
279
280 --disable_cursor_protocol
281 select row_start from t1 into @trx_id;
282 --enable_cursor_protocol
283 select trt_begin_ts(@trx_id) <= @ts1 as BEGIN_TS_GOOD;
284
285 drop table t1;
286
287 --echo #
288 --echo # MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs
289 --echo #
290
291 CREATE TABLE t1 (
292 x INT,
293 sys_trx_start BIGINT UNSIGNED AS ROW START,
294 sys_trx_end BIGINT UNSIGNED AS ROW END,
295 PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
296 ) WITH SYSTEM VERSIONING ENGINE=INNODB;
297 INSERT INTO t1 (x) VALUES (1);
298 SET @ts= DATE_ADD(NOW(), INTERVAL 1 YEAR);
299 EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION @ts;
300 EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP @ts;
301 EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF @ts;
302 DROP TABLE t1;
303
304
305 --echo #
306 --echo # Testing AS OF with expressions of various kinds and data types
307 --echo #
308
309 CREATE TABLE t1
310 (
311 x INT,
312 sys_trx_start BIGINT UNSIGNED AS ROW START INVISIBLE,
313 sys_trx_end BIGINT UNSIGNED AS ROW END INVISIBLE,
314 PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
315 ) WITH SYSTEM VERSIONING;
316 INSERT INTO t1 VALUES (1);
317
318 CREATE TABLE t2
319 (
320 x INT,
321 sys_trx_start TIMESTAMP(6) AS ROW START INVISIBLE,
322 sys_trx_end TIMESTAMP(6) AS ROW END INVISIBLE,
323 PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
324 ) WITH SYSTEM VERSIONING;
325 INSERT INTO t2 VALUES (1);
326
327 --echo #
328 --echo # ROW is not supported
329 --echo #
330
331 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
332 SELECT * FROM t1 FOR SYSTEM_TIME AS OF (1,1);
333 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
334 SELECT * FROM t2 FOR SYSTEM_TIME AS OF (1,1);
335
336
337 --echo #
338 --echo # DOUBLE is not supported, use explicit CAST
339 --echo #
340
341 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
342 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
343 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
344 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
345 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
346 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
347
348 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
349 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
350 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
351 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
352 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
353 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
354
355
356 --echo #
357 --echo # DECIMAL is not supported, use explicit CAST
358 --echo #
359
360 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
361 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
362 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
363 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
364
365 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
366 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
367 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
368 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
369
370
371 --echo #
372 --echo # YEAR is not supported, use explicit CAST
373 --echo #
374
375 DELIMITER $$;
376 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
377 BEGIN NOT ATOMIC
378 DECLARE var YEAR;
379 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
380 END;
381 $$
382 DELIMITER ;$$
383
384 DELIMITER $$;
385 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
386 BEGIN NOT ATOMIC
387 DECLARE var YEAR;
388 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
389 END;
390 $$
391 DELIMITER ;$$
392
393
394 --echo #
395 --echo # ENUM is not supported, use explicit CAST
396 --echo #
397
398 DELIMITER $$;
399 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
400 BEGIN NOT ATOMIC
401 DECLARE var ENUM('xxx') DEFAULT 'xxx';
402 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
403 END;
404 $$
405 DELIMITER ;$$
406
407
408 DELIMITER $$;
409 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
410 BEGIN NOT ATOMIC
411 DECLARE var ENUM('xxx') DEFAULT 'xxx';
412 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
413 END;
414 $$
415 DELIMITER ;$$
416
417
418 --echo #
419 --echo # SET is not supported, use explicit CAST
420 --echo #
421
422 DELIMITER $$;
423 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
424 BEGIN NOT ATOMIC
425 DECLARE var SET('xxx') DEFAULT 'xxx';
426 SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
427 END;
428 $$
429 DELIMITER ;$$
430
431 DELIMITER $$;
432 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
433 BEGIN NOT ATOMIC
434 DECLARE var SET('xxx') DEFAULT 'xxx';
435 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
436 END;
437 $$
438 DELIMITER ;$$
439
440
441 DELIMITER $$;
442 --error ER_VERS_ENGINE_UNSUPPORTED
443 BEGIN NOT ATOMIC
444 DECLARE var BIT(10);
445 SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
446 END;
447 $$
448 DELIMITER ;$$
449
450
451 --echo #
452 --echo # String literals resolve to TIMESTAMP
453 --echo #
454
455 SELECT * FROM t1 FOR SYSTEM_TIME AS OF '2107-12-30 00:00:00';
456 SELECT * FROM t2 FOR SYSTEM_TIME AS OF '2107-12-30 00:00:00';
457 DROP TABLE t1, t2;
458
459
460 --echo #
461 --echo # MDEV-16094 Crash when using AS OF with a stored function
462 --echo #
463
464 CREATE FUNCTION fts() RETURNS DATETIME RETURN '2001-01-01 10:20:30';
465 CREATE FUNCTION ftx() RETURNS BIGINT UNSIGNED RETURN 1;
466
467 CREATE TABLE ttx
468 (
469 x INT,
470 start_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
471 end_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
472 PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
473 ) ENGINE=InnoDB WITH SYSTEM VERSIONING;
474
475 CREATE TABLE tts
476 (
477 x INT,
478 start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
479 end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
480 PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
481 ) ENGINE=InnoDB WITH SYSTEM VERSIONING;
482
483 SELECT * FROM tts FOR SYSTEM_TIME AS OF fts();
484 --error ER_VERS_ENGINE_UNSUPPORTED
485 SELECT * FROM tts FOR SYSTEM_TIME AS OF TRANSACTION ftx();
486 SELECT * FROM ttx FOR SYSTEM_TIME AS OF fts();
487 SELECT * FROM ttx FOR SYSTEM_TIME AS OF TRANSACTION ftx();
488
489 DROP TABLE tts;
490 DROP TABLE ttx;
491 DROP FUNCTION fts;
492 DROP FUNCTION ftx;
493
494 --echo #
495 --echo # MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute
496 --echo #
497
498 SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
499 CREATE TABLE t (
500 a INT,
501 b INT,
502 row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
503 row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
504 PERIOD FOR SYSTEM_TIME(row_start, row_end)
505 ) WITH SYSTEM VERSIONING ENGINE=INNODB;
506
507 INSERT INTO t VALUES (1,1);
508
509 --echo # without table rebuild
510 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
511 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
512 ON c.table_id=t.table_id
513 WHERE t.name='test/t' AND c.name='a';
514 --enable_info
515 ALTER TABLE t
516 CHANGE a a INT WITHOUT SYSTEM VERSIONING;
517 --disable_info
518 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
519 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
520 ON c.table_id=t.table_id
521 WHERE t.name='test/t' AND c.name='a';
522
523 UPDATE t SET a=11;
524 SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
525
526 --echo # with table rebuild
527 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
528 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
529 ON c.table_id=t.table_id
530 WHERE t.name='test/t' AND c.name='a';
531 --enable_info
532 ALTER TABLE t
533 CHANGE a a INT WITH SYSTEM VERSIONING,
534 ADD PRIMARY KEY pk(a);
535 --disable_info
536 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
537 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
538 ON c.table_id=t.table_id
539 WHERE t.name='test/t' AND c.name='a';
540
541 UPDATE t SET a=1;
542 SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
543
544 SHOW CREATE TABLE t;
545
546 -- echo # handles VIRTUAL columns too
547 CREATE OR REPLACE TABLE t (
548 a INT AS (b + 1),
549 b INT,
550 row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
551 row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
552 PERIOD FOR SYSTEM_TIME(row_start, row_end)
553 ) WITH SYSTEM VERSIONING ENGINE=INNODB;
554
555 INSERT INTO t VALUES (DEFAULT, 1);
556
557 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
558 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
559 ON c.table_id=t.table_id
560 WHERE t.name='test/t' AND c.name='b';
561 --enable_info
562 ALTER TABLE t
563 CHANGE b b INT WITHOUT SYSTEM VERSIONING;
564 --disable_info
565 SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
566 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
567 ON c.table_id=t.table_id
568 WHERE t.name='test/t' AND c.name='b';
569
570 UPDATE t SET b=11;
571 SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
572
573 DROP TABLE t;
574 SET @@SYSTEM_VERSIONING_ALTER_HISTORY=ERROR;
575
576 SELECT count(*) from mysql.transaction_registry where begin_timestamp>=commit_timestamp;
577
578 --echo # MDEV-18875 Assertion `thd->transaction.stmt.ha_list == __null ||
579 --echo # trans == &thd->transaction.stmt' failed or bogus ER_DUP_ENTRY upon
580 --echo # ALTER TABLE with versioning
581 create or replace table t (x int) engine=innodb;
582 set autocommit= 0;
583 alter table t
584 algorithm=copy,
585 add column row_start bigint unsigned as row start,
586 add column row_end bigint unsigned as row end,
587 add period for system_time(row_start,row_end),
588 with system versioning;
589 set autocommit= 1;
590
591 --echo # MDEV-18865 Assertion `t->first->versioned_by_id()'
592 --echo # failed in innodb_prepare_commit_versioned
593
594 create or replace table t (x int) engine=innodb;
595 insert into t values (0);
596 alter table t add `row_start` bigint unsigned as row start,
597 add `row_end` bigint unsigned as row end,
598 add period for system_time(`row_start`,`row_end`),
599 modify x int after row_start,
600 with system versioning;
601 drop table t;
602
603 --echo #
604 --echo # MDEV-20842 Crash using versioning plugin functions after plugin was removed from server
605 --echo #
606 uninstall plugin test_versioning;
607 --error ER_SP_DOES_NOT_EXIST
608 select trt_begin_ts(0);
609
610 --disable_prepare_warnings
611
612 --echo #
613 --echo # MDEV-21650 Non-empty statement transaction on global rollback after TRT update error
614 --echo #
615 create table t1 (s date, e date, period for app(s,e)) engine=innodb;
616 alter table t1
617 add row_start bigint unsigned as row start,
618 add row_end bigint unsigned as row end,
619 add period for system_time(row_start,row_end),
620 with system versioning,
621 add period if not exists for app(x,y);
622
623 set transaction isolation level serializable;
624 start transaction;
625 insert into t1 (s,e) values ('2021-07-04','2024-08-18');
626
627 --connect (con1,localhost,root,,test)
628 start transaction;
629 insert into t1 (s,e) values ('2018-06-01','2021-09-15');
630
631 --connection default
632 --replace_regex /TRX_ID \d+/TRX_ID .../
633 --error ER_VERS_NO_TRX_ID
634 select * from t1 for system_time as of now();
635
636 --connection con1
637 set innodb_lock_wait_timeout= 1, lock_wait_timeout= 1;
638 # can be existing or non-existing table, does not matter
639 --error ER_LOCK_WAIT_TIMEOUT
640 alter table xx;
641
642 # cleanup
643 --disconnect con1
644 --connection default
645 drop table t1;
646
647 --echo # End of 10.4 tests
648
649 --echo #
650 --echo # MDEV-37005 Unexpected ER_TABLE_EXISTS_ERROR on primary or replica upon CREATE OR REPLACE for partitioned table
651 --echo #
652 create table t1 (
653 a int,
654 row_start bigint unsigned generated always as row start,
655 row_end bigint unsigned generated always as row end,
656 period for system_time (row_start, row_end)
657 ) engine=innodb with system versioning
658 partition by hash (a);
659 create or replace table t1 (b int) engine=innodb with system versioning partition by system_time;
660 show create table t1;
661 drop table t1;
662
663 --echo # End of 11.8 tests