Debian Codegraph @

mysql-test/suite/gcol/t/innodb_virtual_debug.test

raw
1 --source include/have_innodb.inc
2 --source include/have_debug_sync.inc
3 --source include/have_debug.inc
4 --source include/count_sessions.inc
5
6 set default_storage_engine=innodb;
7 # Ensure that the history list length will actually be decremented by purge.
8 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
9 SET GLOBAL innodb_stats_persistent = 0;
10
11 CREATE TABLE `t` (
12 `a` VARCHAR(100),
13 `b` VARCHAR(100),
14 `c` VARCHAR(200) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
15 `h` VARCHAR(10) DEFAULT NULL,
16 `i` int
17 ) ENGINE=InnoDB;
18
19 INSERT INTO t VALUES (REPEAT('g', 100), REPEAT('x', 10), DEFAULT, "kk", 1);
20 INSERT INTO t VALUES (REPEAT('a', 100), REPEAT('b', 100), DEFAULT, "mm", 2);
21
22 CREATE INDEX idx ON t(c(100));
23
24 SET session debug_dbug="+d,ib_alter_add_virtual_fail";
25 --error ER_WRONG_KEY_COLUMN
26 ALTER TABLE t ADD COLUMN x VARCHAR(200) GENERATED ALWAYS AS (a) VIRTUAL,
27 ALGORITHM = INPLACE;
28 --error ER_WRONG_KEY_COLUMN
29 ALTER TABLE t DROP COLUMN c, ALGORITHM = INPLACE;
30 SET session debug_dbug="";
31 DROP TABLE t;
32
33 #online test
34 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
35
36 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
37 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
38 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
39 INSERT INTO t VALUES (null, null, DEFAULT, "mx");
40
41 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
42 --send CREATE INDEX idx ON t(c)
43
44 connect (con1,localhost,root,,);
45
46 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
47 update t set a=0 where a = 11;
48 start transaction;
49 update t set a=1 where a = 0;
50 ROLLBACK;
51 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
52
53 connection default;
54 reap;
55
56 SELECT c FROM t;
57 SHOW CREATE TABLE t;
58 SELECT * FROM t;
59
60 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
61 ALTER TABLE t FORCE, ALGORITHM=INPLACE, LOCK=NONE;
62 if (0) {# MDEV-14341 TODO: re-enable this
63 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
64 --send ALTER TABLE t FORCE
65
66 connection con1;
67 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
68 start transaction;
69 update t set a=1 where a = 0;
70 rollback;
71 start transaction;
72 delete from t;
73 insert into t values(1,null,default,null);
74 rollback;
75 start transaction;
76 update t set b=b+1;
77 rollback;
78 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
79
80 connection default;
81 reap;
82
83 check table t;
84 SELECT c FROM t;
85
86 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
87 --send ALTER TABLE t FORCE
88
89 connection con1;
90
91 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
92 start transaction;
93 DELETE FROM t WHERE a = 0;
94 ROLLBACK;
95 DELETE FROM t WHERE a = 0;
96 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
97
98 connection default;
99 reap;
100
101 SELECT c FROM t;
102 }
103
104 disconnect con1;
105 DROP TABLE t;
106
107 SET DEBUG_SYNC = 'RESET';
108
109
110 # Test add virtual column and add index at the same time
111 # introduce some error
112
113 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
114
115 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
116
117 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
118
119 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
120
121 INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
122
123 CREATE INDEX idx_1 on t(c);
124
125 SET @saved_dbug = @@SESSION.debug_dbug;
126 SET debug_dbug = '+d,create_index_fail';
127
128 --enable_info
129 --error ER_DUP_ENTRY
130 ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x),
131 ADD INDEX idcx (c,x);
132
133 UPDATE t SET a=a+1;
134
135 --error ER_DUP_ENTRY
136 ALTER TABLE t ADD INDEX idc(c);
137 SET debug_dbug = @saved_dbug;
138 --disable_info
139
140 UPDATE t SET b=b-1;
141
142 SHOW CREATE TABLE t;
143
144 SELECT c FROM t;
145
146 DROP TABLE t;
147
148 if (0) {# MDEV-14341 TODO: re-enable LOCK=NONE and these tests
149 --echo #
150 --echo # Bug#22018532 ASSERTION WHEN ONLINE REAPPLY REBUILD LOG ON
151 --echo # MULTIPLE INDEXED VIRTUAL COLUMNS
152 --echo #
153
154 create table t (
155 a int as (1) virtual,
156 b int,
157 c int as (1) virtual,
158 unique(b),
159 unique(c),
160 key(a)
161 ) engine=innodb;
162
163 insert ignore into t values();
164
165 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
166 --send optimize table t
167
168 connect (con1,localhost,root,,);
169
170 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
171 insert ignore into t values();
172 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
173
174 connection default;
175 --echo /* connection default */ optimize table t;
176 reap;
177 SELECT c FROM t;
178 SHOW CREATE TABLE t;
179 SELECT * FROM t;
180 DROP TABLE t;
181
182 # Do another test without duplicate error
183
184 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
185
186 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
187 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
188 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
189 INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
190
191 CREATE INDEX idx ON t(c);
192
193 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_rebuild WAIT_FOR go_ahead';
194 --send optimize table t
195
196 connection con1;
197 SET DEBUG_SYNC = 'now WAIT_FOR start_rebuild';
198 INSERT INTO t VALUES (48, 2, DEFAULT, 'xx');
199 INSERT INTO t VALUES (68, 3, DEFAULT, 'sx');
200 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
201
202 connection default;
203 --echo /* connection default */ optimize table t;
204 reap;
205
206 SELECT c FROM t;
207
208 disconnect con1;
209
210 DROP TABLE t;
211
212 --echo #
213 --echo # Bug#22951879 - ASSERTS RELATED TO ONLINE DDL AND GCOL
214 --echo #
215
216 # Create a table with 2 virtual column, one (vbidxcol) is indexed and
217 # the other one (vbcol) is not
218 create table ibstd_14 (a int not null, d int not null, b varchar(198) not null, c char(181), vadcol int as (a+length(d)) stored, vbcol char(2) as (substr(b,2,2)) virtual, vbidxcol char(3) as (substr(b,1,3)) virtual , index(d), index(a), index(vbidxcol), index(a,vbidxcol), index(vbidxcol,d), unique key (b(10), a, d), index(c(99), b(31)), index(b(5), c(10), a) , index(a,d)) engine=InnoDB stats_persistent=1 row_format=dynamic;
219
220 # Do an alter table rebuild table and also create a new index on this
221 # non-indexed virtual column
222 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
223 --send alter table ibstd_14 row_format=compressed key_block_size=4,add key kn3 (d,c,vbcol,b)
224
225 # Do a concurrent insert, and make sure this newly indexed virtual column
226 # is also logged
227 connect (con1,localhost,root);
228 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
229 insert into ibstd_14 (a,d,b,c, vbidxcol, vbcol) values ('118','6',repeat('oacolaarlruoacuroauurloraarucoooarcooauoolacalllaulrruarrrucruuooclacuoouccarrcoocloccorrrrarourcooalloocooccouruolaorlcaocualolc','1'),repeat('lolrrlalcocroraaulauclaaucolcorcuooaolruaooooluooooouaoorlarucorullalcrrloccououaooaorluorraclrcooouuolocoaolcocaaculruoocucoocoooauuolarcoraraocaoolulolarru','1'),default,default);
230
231 insert into ibstd_14 (a,d,b,c, vbidxcol, vbcol) values ('118','6', 'aaaa', 'lll', default, default);
232
233 # Also do an concurrent update, make sure this is performed
234 update ibstd_14 set b='11111' where b='aaaa';
235
236 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
237
238 connection default;
239 reap;
240
241 select * from ibstd_14;
242
243 # This will use the newly added "kn3" index, to check materialized vbcol
244 # after log reapply
245 select d,c,vbcol,b from ibstd_14;
246
247 # check the value is inserted into the index
248 select vbcol from ibstd_14;
249
250 drop table ibstd_14;
251
252 --echo #
253 --echo # Bug#22018745 CORRUPTION IN ONLINE TABLE REBUILD
254 --echo # (ROW_FORMAT=REDUNDANT, INDEXED VIRTUAL COLUMN)
255 --echo #
256
257 CREATE TABLE t (
258 b char(5) PRIMARY KEY,
259 v char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, KEY(v)
260 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;
261
262 connection con1;
263 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL prepared WAIT_FOR apply';
264 --send OPTIMIZE TABLE t
265 connection default;
266
267 SET DEBUG_SYNC='now WAIT_FOR prepared';
268 INSERT INTO t SET b='fubar';
269 BEGIN;
270 DELETE FROM t;
271 ROLLBACK;
272 SET DEBUG_SYNC='now SIGNAL apply';
273
274 connection con1;
275 reap;
276
277 connection default;
278 CHECK TABLE t;
279 SELECT * FROM t;
280 DROP TABLE t;
281
282 disconnect con1;
283 }
284 SET DEBUG_SYNC = 'RESET';
285
286
287 --echo #
288 --echo # Bug#28825718 - ASSERTION FAILURE: TRX0REC.CC:NNN:N_IDX > 0 WHILE DOING REPLACE/INSERT
289 --echo #
290
291 CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT GENERATED ALWAYS AS(b+1) VIRTUAL) ENGINE=InnoDB;
292
293 INSERT INTO t1(a, b) VALUES(1, 1);
294
295 connect (con1,localhost,root,,);
296 SET DEBUG_SYNC = 'row_log_apply_after SIGNAL s1 WAIT_FOR s2';
297 SET lock_wait_timeout = 1;
298 --send ALTER TABLE t1 ADD UNIQUE INDEX(c, b)
299
300 connection default;
301 SET DEBUG_SYNC = 'now WAIT_FOR s1';
302 SET DEBUG_SYNC = 'row_log_insert_handle SIGNAL s2 WAIT_FOR s3';
303 --send INSERT INTO t1(a, b) VALUES(2, 2)
304
305 connection con1;
306 --error ER_LOCK_WAIT_TIMEOUT
307 reap;
308 SET DEBUG_SYNC = 'now SIGNAL s3';
309 disconnect con1;
310 connection default;
311 reap;
312 SET DEBUG_SYNC = 'RESET';
313 ALTER TABLE t1 ADD KEY(b);
314 INSERT INTO t1(a, b) VALUES(3, 3);
315 SELECT * FROM t1;
316 CHECK TABLE t1;
317 DROP TABLE t1;
318
319 --echo #
320 --echo # MDEV-28806 Assertion `flag == 1' failure in
321 --echo # row_build_index_entry_low upon concurrent ALTER and UPDATE
322 --echo #
323
324 CREATE TABLE t1(a CHAR(8), b INT, c INT AS (b), KEY(a)) ENGINE=InnoDB;
325 INSERT INTO t1(b) VALUES (1),(2);
326
327 --connect (con1,localhost,root,,test)
328 SET DEBUG_SYNC="alter_table_inplace_before_lock_upgrade SIGNAL dml_start WAIT_FOR dml_commit";
329 send ALTER TABLE t1 ADD KEY ind (c);
330
331 --connection default
332 SET DEBUG_SYNC="now WAIT_FOR dml_start";
333 UPDATE t1 SET a ='foo';
334 SET DEBUG_SYNC="now SIGNAL dml_commit";
335
336 # Cleanup
337 --connection con1
338 --reap
339 CHECK TABLE t1;
340 DROP TABLE t1;
341 --disconnect con1
342 connection default;
343 SET DEBUG_SYNC=RESET;
344
345 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
346
347 --source include/wait_until_count_sessions.inc