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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
set default_storage_engine=innodb;
SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
SET GLOBAL innodb_stats_persistent = 0;
CREATE TABLE `t` (
`a` VARCHAR(100),
`b` VARCHAR(100),
`c` VARCHAR(200) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
`h` VARCHAR(10) DEFAULT NULL,
`i` int
) ENGINE=InnoDB;
INSERT INTO t VALUES (REPEAT('g', 100), REPEAT('x', 10), DEFAULT, "kk", 1);
INSERT INTO t VALUES (REPEAT('a', 100), REPEAT('b', 100), DEFAULT, "mm", 2);
CREATE INDEX idx ON t(c(100));
SET session debug_dbug="+d,ib_alter_add_virtual_fail";
ALTER TABLE t ADD COLUMN x VARCHAR(200) GENERATED ALWAYS AS (a) VIRTUAL,
ALGORITHM = INPLACE;
ERROR 42000: The storage engine InnoDB can't index column `x`
ALTER TABLE t DROP COLUMN c, ALGORITHM = INPLACE;
ERROR 42000: The storage engine InnoDB can't index column `c`
SET session debug_dbug="";
DROP TABLE t;
CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
INSERT INTO t VALUES (null, null, DEFAULT, "mx");
SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
CREATE INDEX idx ON t(c);
connect con1,localhost,root,,;
SET DEBUG_SYNC = 'now WAIT_FOR start_create';
update t set a=0 where a = 11;
start transaction;
update t set a=1 where a = 0;
ROLLBACK;
SET DEBUG_SYNC = 'now SIGNAL go_ahead';
connection default;
SELECT c FROM t;
c
NULL
3
19
29
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) GENERATED ALWAYS AS (`a` + `b`) VIRTUAL,
`h` varchar(10) DEFAULT NULL,
KEY `idx` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT * FROM t;
a b c h
0 3 3 mm
18 1 19 mm
28 1 29 mm
NULL NULL NULL mx
ALTER TABLE t FORCE, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED
disconnect con1;
DROP TABLE t;
SET DEBUG_SYNC = 'RESET';
CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
CREATE INDEX idx_1 on t(c);
SET @saved_dbug = @@SESSION.debug_dbug;
SET debug_dbug = '+d,create_index_fail';
ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x),
ADD INDEX idcx (c,x);
ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
UPDATE t SET a=a+1;
affected rows: 3
info: Rows matched: 4 Changed: 3 Warnings: 0
ALTER TABLE t ADD INDEX idc(c);
ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
SET debug_dbug = @saved_dbug;
affected rows: 0
UPDATE t SET b=b-1;
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) GENERATED ALWAYS AS (`a` + `b`) VIRTUAL,
`h` varchar(10) DEFAULT NULL,
KEY `idx_1` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT c FROM t;
c
NULL
14
19
29
DROP TABLE t;
SET DEBUG_SYNC = 'RESET';
#
# Bug#28825718 - ASSERTION FAILURE: TRX0REC.CC:NNN:N_IDX > 0 WHILE DOING REPLACE/INSERT
#
CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT GENERATED ALWAYS AS(b+1) VIRTUAL) ENGINE=InnoDB;
INSERT INTO t1(a, b) VALUES(1, 1);
connect con1,localhost,root,,;
SET DEBUG_SYNC = 'row_log_apply_after SIGNAL s1 WAIT_FOR s2';
SET lock_wait_timeout = 1;
ALTER TABLE t1 ADD UNIQUE INDEX(c, b);
connection default;
SET DEBUG_SYNC = 'now WAIT_FOR s1';
SET DEBUG_SYNC = 'row_log_insert_handle SIGNAL s2 WAIT_FOR s3';
INSERT INTO t1(a, b) VALUES(2, 2);
connection con1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET DEBUG_SYNC = 'now SIGNAL s3';
disconnect con1;
connection default;
SET DEBUG_SYNC = 'RESET';
ALTER TABLE t1 ADD KEY(b);
INSERT INTO t1(a, b) VALUES(3, 3);
SELECT * FROM t1;
a b c
1 1 2
2 2 3
3 3 4
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
DROP TABLE t1;
#
# MDEV-28806 Assertion `flag == 1' failure in
# row_build_index_entry_low upon concurrent ALTER and UPDATE
#
CREATE TABLE t1(a CHAR(8), b INT, c INT AS (b), KEY(a)) ENGINE=InnoDB;
INSERT INTO t1(b) VALUES (1),(2);
connect con1,localhost,root,,test;
SET DEBUG_SYNC="alter_table_inplace_before_lock_upgrade SIGNAL dml_start WAIT_FOR dml_commit";
ALTER TABLE t1 ADD KEY ind (c);
connection default;
SET DEBUG_SYNC="now WAIT_FOR dml_start";
UPDATE t1 SET a ='foo';
SET DEBUG_SYNC="now SIGNAL dml_commit";
connection con1;
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
DROP TABLE t1;
disconnect con1;
connection default;
SET DEBUG_SYNC=RESET;
SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
|
