1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
CREATE OR REPLACE TABLE t1 (
a INT,
b INT,
row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
PERIOD FOR SYSTEM_TIME(row_start, row_end)
) WITH SYSTEM VERSIONING ENGINE=INNODB;
CREATE OR REPLACE TABLE t2 (
a INT WITHOUT SYSTEM VERSIONING,
b INT,
row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
PERIOD FOR SYSTEM_TIME(row_start, row_end)
) WITH SYSTEM VERSIONING ENGINE=INNODB;
INSERT INTO t1 VALUES (1,1);
INSERT INTO t2 VALUES (1,1);
SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
# without rebuild
ALTER TABLE t1
CHANGE a a INT WITHOUT SYSTEM VERSIONING,
ALGORITHM=INSTANT;
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE t2
CHANGE a a INT WITH SYSTEM VERSIONING,
ADD PRIMARY KEY pk (a),
ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
# with rebuild
ALTER TABLE t2
CHANGE a a INT WITH SYSTEM VERSIONING,
ADD PRIMARY KEY pk (a);
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 0
UPDATE t1 SET a=2;
SELECT COUNT(*) FROM t1 FOR SYSTEM_TIME ALL;
COUNT(*)
1
UPDATE t2 SET a=2;
SELECT COUNT(*) FROM t2 FOR SYSTEM_TIME ALL;
COUNT(*)
2
DROP TABLE t1, t2;
# rollback ALTER TABLE: nothing should change
CREATE TABLE t (
a INT,
b INT,
row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
PERIOD FOR SYSTEM_TIME(row_start, row_end)
) WITH SYSTEM VERSIONING ENGINE=INNODB;
INSERT INTO t VALUES (1, 1);
SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID
WHERE t.NAME='test/t' AND C.NAME='b';
PRTYPE
50179
SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
SET @SAVED_DEBUG_DBUG = @@SESSION.DEBUG_DBUG;
SET DEBUG_DBUG='+d,ib_commit_inplace_fail_1';
ALTER TABLE t
CHANGE b b INT WITHOUT SYSTEM VERSIONING;
ERROR HY000: Internal error: Injected error!
SET DEBUG_DBUG = @SAVED_DEBUG_DBUG;
SELECT C.PRTYPE FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS C
JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t ON C.TABLE_ID=t.TABLE_ID
WHERE t.NAME='test/t' AND C.NAME='b';
PRTYPE
50179
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`row_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START INVISIBLE,
`row_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END INVISIBLE,
PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
COUNT(*)
1
UPDATE t SET b=11;
SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
COUNT(*)
2
DROP TABLE t;
|
