Debian Codegraph @

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

raw
1 set system_versioning_alter_history=keep;
2 create or replace table t (a int);
3 alter table t add system versioning, lock=none;
4 alter table t drop system versioning;
5 alter table t add system versioning, algorithm=inplace;
6 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Not implemented for system-versioned timestamp tables. Try ALGORITHM=COPY
7 alter table t add system versioning, lock=shared;
8 alter table t add column b int, change column a a int without system versioning, lock=none;
9 alter table t drop system versioning, lock=none;
10 ERROR 0A000: LOCK=NONE is not supported. Reason: DROP SYSTEM VERSIONING. Try LOCK=SHARED
11 alter table t drop system versioning, algorithm=inplace;
12 create or replace table t (
13 a int, b int,
14 row_start SYS_DATATYPE as row start invisible,
15 row_end SYS_DATATYPE as row end invisible,
16 period for system_time (row_start, row_end)
17 ) with system versioning;
18 insert into t values (1, 0);
19 insert into t values (2, 0);
20 delete from t where a = 2;
21 alter table t drop column b, lock=none;
22 alter table t add index idx(a), lock=none;
23 select a, check_row(row_start, row_end) from t for system_time all order by a;
24 a check_row(row_start, row_end)
25 1 CURRENT ROW
26 2 HISTORICAL ROW
27 # MDEV-17038 ALTER TABLE CHANGE COLUMN c1 c1 bigint NOT NULL -
28 # generates error if table uses SYSTEM VERSIONING [tempesta-tech/mariadb#540]
29 create or replace table t1 (a int, key(a)) with system versioning;
30 create or replace table t2 like t;
31 alter table t2 add foreign key(a) references t1(a);
32 alter table t2 modify column a int not null, lock=none;
33 drop table t2;
34 drop table t1;
35 # MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute
36 create or replace table t1 (
37 a int,
38 b int,
39 row_start SYS_DATATYPE as row start invisible,
40 row_end SYS_DATATYPE as row end invisible,
41 period for system_time(row_start, row_end)
42 ) with system versioning;
43 create or replace table t2 (
44 a int without system versioning,
45 b int,
46 row_start SYS_DATATYPE as row start invisible,
47 row_end SYS_DATATYPE as row end invisible,
48 period for system_time(row_start, row_end)
49 ) with system versioning;
50 insert into t1 values (1,1);
51 insert into t2 values (1,1);
52 set @@system_versioning_alter_history=keep;
53 # without rebuild
54 alter table t1
55 change a a int without system versioning,
56 algorithm=instant;
57 affected rows: 0
58 info: Records: 0 Duplicates: 0 Warnings: 0
59 alter table t2
60 change a a int with system versioning,
61 add primary key pk (a),
62 algorithm=instant;
63 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
64 # with rebuild
65 alter table t2
66 change a a int with system versioning,
67 add primary key pk (a);
68 affected rows: 0
69 info: Records: 0 Duplicates: 0 Warnings: 1
70 Warnings:
71 Warning 1280 Name 'pk' ignored for PRIMARY key.
72 # restart
73 update t1 set a=2;
74 select count(*) from t1 for system_time all;
75 count(*)
76 1
77 update t2 set a=2;
78 select count(*) from t2 for system_time all;
79 count(*)
80 2
81 drop table t1, t2;
82 # rollback ALTER TABLE: nothing should change
83 create or replace table t (
84 a int,
85 b int,
86 row_start SYS_DATATYPE as row start invisible,
87 row_end SYS_DATATYPE as row end invisible,
88 period for system_time(row_start, row_end)
89 ) with system versioning;
90 insert into t values (1, 1);
91 select c.prtype from information_schema.innodb_sys_columns as c
92 join information_schema.innodb_sys_tables as t on c.table_id=t.table_id
93 where t.name='test/t' and c.name='b';
94 prtype
95 50179
96 set @@system_versioning_alter_history=keep;
97 select c.prtype from information_schema.innodb_sys_columns as c
98 join information_schema.innodb_sys_tables as t on c.table_id=t.table_id
99 where t.name='test/t' and c.name='b';
100 prtype
101 50179
102 show create table t;
103 Table Create Table
104 t CREATE TABLE `t` (
105 `a` int(11) DEFAULT NULL,
106 `b` int(11) DEFAULT NULL,
107 `row_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START INVISIBLE,
108 `row_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END INVISIBLE,
109 PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
110 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
111 select count(*) from t for system_time all;
112 count(*)
113 1
114 update t set b=11;
115 select count(*) from t for system_time all;
116 count(*)
117 2
118 drop table t;
119 # Start of 10.4 tests
120 create or replace table t (a int, b int) engine=innodb;
121 alter table t
122 add s bigint unsigned as row start,
123 add e bigint unsigned as row end,
124 add period for system_time(s, e),
125 add system versioning;
126 alter table t drop column b, algorithm=instant;
127 alter table t add index idx(a), lock=none;
128 alter table t drop column s, drop column e;
129 alter table t drop system versioning, lock=none;
130 ERROR 0A000: LOCK=NONE is not supported. Reason: DROP SYSTEM VERSIONING. Try LOCK=SHARED
131 #
132 # MDEV-17697 Broken versioning info after instant drop column
133 #
134 set @@system_versioning_alter_history= keep;
135 create or replace table t1 (a int, b int) with system versioning;
136 insert into t1 values (1, 1);
137 alter table t1 drop column b, algorithm=instant;
138 alter table t1 drop system versioning;
139 create or replace table t1 (a int, b int) with system versioning;
140 insert into t1 values (1, 1);
141 alter table t1 drop system versioning;
142 #
143 # MDEV-18173 Assertion `o->ind == vers_end' or `o->ind == vers_start' failed in dict_table_t::instant_column
144 #
145 set @@system_versioning_alter_history= keep;
146 create or replace table t1 (pk integer primary key, a int, b int, v int as (a))
147 with system versioning;
148 alter table t1 force;
149 alter table t1 drop column b;
150 #
151 # MDEV-18122 Assertion `table->versioned() == m_prebuilt->table->versioned()' failed in ha_innobase::open
152 #
153 create or replace table t1 (
154 x int,
155 v int as (x) virtual,
156 y int
157 ) with system versioning;
158 alter table t1 drop system versioning;
159 drop tables t, t1;