Debian Codegraph @

mysql-test/suite/gcol/r/innodb_virtual_purge.result

raw
1 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
2 SET GLOBAL innodb_stats_persistent = 0;
3 #
4 # Bug#21869656 UNDO LOG DOES NOT CONTAIN ENOUGH INFORMATION
5 # ON INDEXED VIRTUAL COLUMNS
6 #
7 CREATE TABLE t1 (a INT, b INT,
8 a1 INT GENERATED ALWAYS AS (a) VIRTUAL, INDEX(a1)
9 ) ENGINE=InnoDB;
10 INSERT INTO t1 (a,b) VALUES(1,1);
11 connect con1,localhost,root,,;
12 CREATE TABLE t0 (a INT) ENGINE=InnoDB;
13 BEGIN;
14 SELECT * FROM t0;
15 a
16 connection default;
17 UPDATE t1 SET a=0;
18 ALTER TABLE t1 DROP COLUMN a1, ALGORITHM=INPLACE;
19 ALTER TABLE t1 ADD COLUMN b1 INT GENERATED ALWAYS AS (b) VIRTUAL, ADD
20 INDEX(b1),
21 ALGORITHM=INPLACE;
22 connection con1;
23 COMMIT;
24 UPDATE t1 SET a=1;
25 connection default;
26 InnoDB 0 transactions not purged
27 CHECK TABLE t1 EXTENDED;
28 Table Op Msg_type Msg_text
29 test.t1 check status OK
30 SELECT b1 FROM t1;
31 b1
32 1
33 ALTER TABLE t1
34 ADD COLUMN a1 INT GENERATED ALWAYS AS (a) VIRTUAL,
35 ADD COLUMN a2 INT GENERATED ALWAYS AS (a + b) VIRTUAL,
36 ADD COLUMN a3 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
37 ADD COLUMN a4 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
38 ADD INDEX(a1), ADD INDEX(a2), ADD INDEX(a3), ALGORITHM=INPLACE;
39 CREATE TABLE t2 (
40 a BLOB,
41 b BLOB,
42 c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
43 h VARCHAR(10) DEFAULT NULL
44 ) ENGINE=InnoDB;
45 INSERT INTO t2 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, 'kk');
46 INSERT INTO t2 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
47 CREATE INDEX idx ON t2(c(100));
48 INSERT INTO t1 (a, b) VALUES(1,1);
49 connection con1;
50 BEGIN;
51 SELECT * FROM t0;
52 a
53 connection default;
54 UPDATE t1 SET a=0;
55 affected rows: 2
56 info: Rows matched: 2 Changed: 2 Warnings: 0
57 UPDATE t1 SET b=0;
58 affected rows: 2
59 info: Rows matched: 2 Changed: 2 Warnings: 0
60 ALTER TABLE t1 DROP COLUMN a3, ALGORITHM=INPLACE;
61 affected rows: 0
62 info: Records: 0 Duplicates: 0 Warnings: 0
63 UPDATE t1 SET a=2;
64 affected rows: 2
65 info: Rows matched: 2 Changed: 2 Warnings: 0
66 ALTER TABLE t1 DROP COLUMN a2, ALGORITHM=INPLACE;
67 affected rows: 0
68 info: Records: 0 Duplicates: 0 Warnings: 0
69 UPDATE t1 SET b=3;
70 affected rows: 2
71 info: Rows matched: 2 Changed: 2 Warnings: 0
72 ALTER TABLE t1 ADD COLUMN b2 INT GENERATED ALWAYS AS (b) VIRTUAL,
73 ADD INDEX(b2), ALGORITHM=INPLACE;
74 affected rows: 0
75 info: Records: 0 Duplicates: 0 Warnings: 0
76 UPDATE t1 SET b=9;
77 affected rows: 2
78 info: Rows matched: 2 Changed: 2 Warnings: 0
79 ALTER TABLE t1 ADD COLUMN b3 INT GENERATED ALWAYS AS (a) VIRTUAL,
80 ADD INDEX(b3), ALGORITHM=INPLACE;
81 affected rows: 0
82 info: Records: 0 Duplicates: 0 Warnings: 0
83 UPDATE t1 SET b=10;
84 affected rows: 2
85 info: Rows matched: 2 Changed: 2 Warnings: 0
86 ALTER TABLE t2 DROP COLUMN c;
87 affected rows: 0
88 info: Records: 0 Duplicates: 0 Warnings: 0
89 UPDATE t2 SET a = REPEAT('s', 6000) WHERE a like 'aaa%';
90 affected rows: 1
91 info: Rows matched: 1 Changed: 1 Warnings: 0
92 ALTER TABLE t2 ADD COLUMN x1 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
93 ADD COLUMN x2 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
94 ADD INDEX(x1(100), x2(120)), ADD INDEX (x1(20));
95 affected rows: 0
96 info: Records: 0 Duplicates: 0 Warnings: 0
97 UPDATE t1 SET a=5;
98 affected rows: 2
99 info: Rows matched: 2 Changed: 2 Warnings: 0
100 UPDATE t2 SET a = REPEAT('m', 16000) WHERE a like 'sss%';
101 affected rows: 1
102 info: Rows matched: 1 Changed: 1 Warnings: 0
103 ALTER TABLE t1 DROP COLUMN b2, ALGORITHM=INPLACE;
104 affected rows: 0
105 info: Records: 0 Duplicates: 0 Warnings: 0
106 UPDATE t1 SET a=6;
107 affected rows: 2
108 info: Rows matched: 2 Changed: 2 Warnings: 0
109 ALTER TABLE t2 DROP COLUMN x1, DROP COLUMN x2, ALGORITHM=INPLACE;
110 affected rows: 0
111 info: Records: 0 Duplicates: 0 Warnings: 0
112 UPDATE t2 SET a = REPEAT('x', 1000) WHERE a like 'mmm%';
113 affected rows: 1
114 info: Rows matched: 1 Changed: 1 Warnings: 0
115 ALTER TABLE t1 DROP INDEX b3;
116 affected rows: 0
117 info: Records: 0 Duplicates: 0 Warnings: 0
118 UPDATE t1 SET a=100;
119 affected rows: 2
120 info: Rows matched: 2 Changed: 2 Warnings: 0
121 connection con1;
122 COMMIT;
123 disconnect con1;
124 connection default;
125 InnoDB 0 transactions not purged
126 CHECK TABLE t1 EXTENDED;
127 Table Op Msg_type Msg_text
128 test.t1 check status OK
129 SELECT b1 FROM t1;
130 b1
131 10
132 10
133 SELECT * FROM t1;
134 a b b1 a1 a4 b3
135 100 10 10 100 90 100
136 100 10 10 100 90 100
137 CHECK TABLE t2 EXTENDED;
138 Table Op Msg_type Msg_text
139 test.t2 check status OK
140 DROP TABLE t2, t1, t0;
141 CREATE TABLE t1 (a VARCHAR(30), b INT, a2 VARCHAR(30) GENERATED ALWAYS AS (a) VIRTUAL);
142 CREATE INDEX idx ON t1(a2(10), b, a2(20));
143 ERROR 42S21: Duplicate column name 'a2'
144 DROP TABLE t1;
145 #
146 # MDEV-17540 Server crashes in row_purge after TRUNCATE TABLE
147 #
148 CREATE TABLE t1 (a BIT(14)) ENGINE=InnoDB;
149 INSERT INTO t1 VALUES
150 (b'01110110101011'),(b'01100111111000'),(b'00001011110100'),
151 (b'01110110111010'),(b'10001010101011'),(b'01100111001111');
152 CREATE TABLE t2 (
153 pk INT DEFAULT 1,
154 b YEAR,
155 c BIT(14),
156 d YEAR AS (b),
157 e BIT(14) AS (c),
158 UNIQUE(pk),
159 KEY(e)
160 ) ENGINE=InnoDB;
161 DROP TABLE t1, t2;
162 #
163 # MDEV-30024 InnoDB: tried to purge non-delete-marked record
164 # of an index on a virtual column prefix
165 #
166 CREATE TABLE t(a BINARY(8), b CHAR(8) AS (a) VIRTUAL, KEY(b(4)))
167 CHARACTER SET utf8 ENGINE=InnoDB;
168 INSERT INTO t (a) VALUES (''),('');
169 UPDATE t SET a = 'x';
170 UPDATE t SET a = '';
171 SET GLOBAL innodb_max_purge_lag_wait=0;
172 CHECK TABLE t EXTENDED;
173 Table Op Msg_type Msg_text
174 test.t check status OK
175 DROP TABLE t;
176 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;