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
|
set default_storage_engine=innodb;
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=latin1 COLLATE=latin1_swedish_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, 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=latin1 COLLATE=latin1_swedish_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_ins_sec_index_enter 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;
|
