Debian Codegraph @

mysql-test/suite/innodb/t/multi_value_debug.test

raw
1 --source include/have_debug_sync.inc
2 --source include/have_debug.inc
3 --source include/have_innodb_max_16k.inc
4
5 --source include/count_sessions.inc
6
7 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON);
8 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS DECIMAL(4,2) ARRAY)));
9 CREATE INDEX i2 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
10
11 # Test lock wait on the INSERT
12 set debug = '+d, row_ins_sec_index_entry_lock_wait';
13 INSERT INTO t1(f1) VALUES(CAST('["11.11", "22.22", "33.33", "44.44", "55.55", "66.66", "77.77", "88.88", "99.99"]' AS JSON));
14
15 SELECT * FROM t1;
16 SELECT * FROM t1 WHERE 11.11 MEMBER OF (f1->'$[*]');
17 SELECT * FROM t1 WHERE 22.22 MEMBER OF (f1->'$[*]');
18 SELECT * FROM t1 WHERE 33.33 MEMBER OF (f1->'$[*]');
19 SELECT * FROM t1 WHERE 44.44 MEMBER OF (f1->'$[*]');
20 SELECT * FROM t1 WHERE 55.55 MEMBER OF (f1->'$[*]');
21 SELECT * FROM t1 WHERE 66.66 MEMBER OF (f1->'$[*]');
22 SELECT * FROM t1 WHERE 77.77 MEMBER OF (f1->'$[*]');
23 SELECT * FROM t1 WHERE 88.88 MEMBER OF (f1->'$[*]');
24 SELECT * FROM t1 WHERE 99.99 MEMBER OF (f1->'$[*]');
25
26 TRUNCATE TABLE t1;
27
28 # Test lock wait on the DELETE
29 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21"]' AS JSON));
30 INSERT INTO t1(f1) VALUES(CAST('["15.67","18.55"]' AS JSON));
31
32 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
33 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
34 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
35 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
36
37 SELECT * FROM t1 WHERE "13.33" MEMBER OF (f1->'$[*]');
38 SELECT * FROM t1 WHERE "13.21" MEMBER OF (f1->'$[*]');
39 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
40 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
41
42 set debug = '+d, row_upd_sec_index_entry_lock_wait';
43 DELETE FROM t1;
44
45 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
46 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
47 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
48 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
49
50 SELECT * FROM t1 WHERE "13.33" MEMBER OF (f1->'$[*]');
51 SELECT * FROM t1 WHERE "13.21" MEMBER OF (f1->'$[*]');
52 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
53 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
54
55 # Test lock wait on the delete phase of UPDATE
56 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21","13.45"]' AS JSON));
57 INSERT INTO t1(f1) VALUES(CAST('["15.67","18.55","19.32"]' AS JSON));
58
59 set debug = '-d, row_upd_sec_index_entry_lock_wait';
60 set debug = '+d, delete_one_multi_sec_index_entry_lock_wait';
61 UPDATE t1 SET f1 = CAST('["98.76","87.65","76.54"]' AS JSON) WHERE 13.33 MEMBER OF (f1->'$[*]');
62
63 SELECT * FROM t1;
64
65 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
66 SELECT * FROM t1 WHERE 87.65 MEMBER OF (f1->'$[*]');
67 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
68 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
69 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
70 SELECT * FROM t1 WHERE 19.32 MEMBER OF (f1->'$[*]');
71 SELECT * FROM t1 WHERE 13.45 MEMBER OF (f1->'$[*]');
72 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
73 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
74
75 SELECT * FROM t1 WHERE "98.76" MEMBER OF (f1->'$[*]');
76 SELECT * FROM t1 WHERE "87.65" MEMBER OF (f1->'$[*]');
77 SELECT * FROM t1 WHERE "76.54" MEMBER OF (f1->'$[*]');
78 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
79 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
80 SELECT * FROM t1 WHERE "19.32" MEMBER OF (f1->'$[*]');
81 SELECT * FROM t1 WHERE "13.45" MEMBER OF (f1->'$[*]');
82 SELECT * FROM t1 WHERE "13.21" MEMBER OF (f1->'$[*]');
83 SELECT * FROM t1 WHERE "13.33" MEMBER OF (f1->'$[*]');
84
85 UPDATE t1 SET f1 = CAST('["11.11","22.22","33.33"]' AS JSON) WHERE "18.55" MEMBER OF (f1->'$[*]');
86
87 SELECT * FROM t1;
88
89 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
90 SELECT * FROM t1 WHERE 87.65 MEMBER OF (f1->'$[*]');
91 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
92 SELECT * FROM t1 WHERE 11.11 MEMBER OF (f1->'$[*]');
93 SELECT * FROM t1 WHERE 22.22 MEMBER OF (f1->'$[*]');
94 SELECT * FROM t1 WHERE 33.33 MEMBER OF (f1->'$[*]');
95 SELECT * FROM t1 WHERE 19.32 MEMBER OF (f1->'$[*]');
96 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
97 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
98
99 SELECT * FROM t1 WHERE "98.76" MEMBER OF (f1->'$[*]');
100 SELECT * FROM t1 WHERE "87.65" MEMBER OF (f1->'$[*]');
101 SELECT * FROM t1 WHERE "76.54" MEMBER OF (f1->'$[*]');
102 SELECT * FROM t1 WHERE "11.11" MEMBER OF (f1->'$[*]');
103 SELECT * FROM t1 WHERE "22.22" MEMBER OF (f1->'$[*]');
104 SELECT * FROM t1 WHERE "33.33" MEMBER OF (f1->'$[*]');
105 SELECT * FROM t1 WHERE "19.32" MEMBER OF (f1->'$[*]');
106 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
107 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
108
109 CHECK TABLE t1;
110
111 DELETE FROM t1;
112
113 # Test the lock wait on the insert phase of UPDATE
114 INSERT INTO t1(f1) VALUES(CAST('["13.33","13.21","13.45"]' AS JSON));
115 INSERT INTO t1(f1) VALUES(CAST('["15.67","18.55","19.32"]' AS JSON));
116
117 set debug = '-d, delete_one_multi_sec_index_entry_lock_wait';
118 set debug = '+d, row_ins_sec_index_entry_lock_wait';
119 UPDATE t1 SET f1 = CAST('["98.76","87.65","76.54"]' AS JSON) WHERE 13.33 MEMBER OF (f1->'$[*]');
120
121 SELECT * FROM t1;
122
123 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
124 SELECT * FROM t1 WHERE 87.65 MEMBER OF (f1->'$[*]');
125 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
126 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
127 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
128 SELECT * FROM t1 WHERE 19.32 MEMBER OF (f1->'$[*]');
129 SELECT * FROM t1 WHERE 13.45 MEMBER OF (f1->'$[*]');
130 SELECT * FROM t1 WHERE 13.21 MEMBER OF (f1->'$[*]');
131 SELECT * FROM t1 WHERE 13.33 MEMBER OF (f1->'$[*]');
132
133 SELECT * FROM t1 WHERE "98.76" MEMBER OF (f1->'$[*]');
134 SELECT * FROM t1 WHERE "87.65" MEMBER OF (f1->'$[*]');
135 SELECT * FROM t1 WHERE "76.54" MEMBER OF (f1->'$[*]');
136 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
137 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
138 SELECT * FROM t1 WHERE "19.32" MEMBER OF (f1->'$[*]');
139 SELECT * FROM t1 WHERE "13.45" MEMBER OF (f1->'$[*]');
140 SELECT * FROM t1 WHERE "13.21" MEMBER OF (f1->'$[*]');
141 SELECT * FROM t1 WHERE "13.33" MEMBER OF (f1->'$[*]');
142
143 UPDATE t1 SET f1 = CAST('["11.11","22.22","33.33"]' AS JSON) WHERE "18.55" MEMBER OF (f1->'$[*]');
144
145 SELECT * FROM t1;
146
147 SELECT * FROM t1 WHERE 98.76 MEMBER OF (f1->'$[*]');
148 SELECT * FROM t1 WHERE 87.65 MEMBER OF (f1->'$[*]');
149 SELECT * FROM t1 WHERE 76.54 MEMBER OF (f1->'$[*]');
150 SELECT * FROM t1 WHERE 11.11 MEMBER OF (f1->'$[*]');
151 SELECT * FROM t1 WHERE 22.22 MEMBER OF (f1->'$[*]');
152 SELECT * FROM t1 WHERE 33.33 MEMBER OF (f1->'$[*]');
153 SELECT * FROM t1 WHERE 19.32 MEMBER OF (f1->'$[*]');
154 SELECT * FROM t1 WHERE 15.67 MEMBER OF (f1->'$[*]');
155 SELECT * FROM t1 WHERE 18.55 MEMBER OF (f1->'$[*]');
156
157 SELECT * FROM t1 WHERE "98.76" MEMBER OF (f1->'$[*]');
158 SELECT * FROM t1 WHERE "87.65" MEMBER OF (f1->'$[*]');
159 SELECT * FROM t1 WHERE "76.54" MEMBER OF (f1->'$[*]');
160 SELECT * FROM t1 WHERE "11.11" MEMBER OF (f1->'$[*]');
161 SELECT * FROM t1 WHERE "22.22" MEMBER OF (f1->'$[*]');
162 SELECT * FROM t1 WHERE "33.33" MEMBER OF (f1->'$[*]');
163 SELECT * FROM t1 WHERE "19.32" MEMBER OF (f1->'$[*]');
164 SELECT * FROM t1 WHERE "15.67" MEMBER OF (f1->'$[*]');
165 SELECT * FROM t1 WHERE "18.55" MEMBER OF (f1->'$[*]');
166
167 set debug = '-d, row_ins_sec_index_entry_lock_wait';
168
169 CHECK TABLE t1;
170
171 DELETE FROM t1;
172
173 DROP TABLE t1;
174
175
176
177
178 # Test alter table with adding index, to check the scenario
179 # when the sort buffer can't be fit for one multi-value row.
180
181 CREATE TABLE t1(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, f1 JSON);
182
183 INSERT INTO t1(f1) VALUES (CAST('[1,3]' AS JSON));
184 INSERT INTO t1(f1) VALUES (CAST('[1,3,9,8]' AS JSON));
185 INSERT INTO t1(f1) VALUES (CAST('[1,3,9,8,10,11,20,30]' AS JSON));
186
187 SET DEBUG='+d, ddl_add_multi_value';
188
189 CREATE INDEX idx1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
190
191 SELECT * FROM t1 WHERE 9 MEMBER OF (f1->'$[*]');
192 SELECT * FROM t1 WHERE 11 MEMBER OF (f1->'$[*]');
193 SELECT * FROM t1 WHERE 30 MEMBER OF (f1->'$[*]');
194
195 SET DEBUG='-d, ddl_add_multi_value';
196
197 CHECK TABLE t1;
198 DROP TABLE t1;
199
200
201
202 # Test the aborted index scenario
203 CREATE TABLE t1(j JSON);
204
205 INSERT INTO t1 VALUES ('["test"]'), ('[]'), ('null');
206
207 # Prepare an index which is marked as aborted
208 connect (conn1,localhost,root,,);
209 connect (conn2,localhost,root,,);
210
211 connection default;
212
213 SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL rolling_back WAIT_FOR drop_created';
214 --send CREATE INDEX mv_idx_date ON t1 ((CAST(j->'$' AS UNSIGNED ARRAY)));
215
216 connection conn1;
217 SET DEBUG_SYNC='now WAIT_FOR rolling_back';
218 SET DEBUG_SYNC='row_search_for_mysql_before_return SIGNAL scanning WAIT_FOR continue_scan';
219 --send SELECT * FROM t1;
220
221 connection conn2;
222 SET DEBUG_SYNC='now WAIT_FOR scanning';
223 SET DEBUG_SYNC='now SIGNAL drop_created';
224
225 connection default;
226 --error ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
227 --reap
228
229 connection conn2;
230 SET DEBUG_SYNC='now SIGNAL continue_scan';
231
232 connection conn1;
233 --reap;
234
235 disconnect conn1;
236 disconnect conn2;
237
238 connection default;
239
240 INSERT INTO t1 VALUES ('["Hello"]');
241
242 CHECK TABLE t1;
243
244 DROP TABLE t1;
245
246
247
248 # Test for the scenario that record on clustered index would be inserted
249 # by modify, and the operation has to be rolled back.
250 CREATE TABLE t1 (
251 i INT NOT NULL PRIMARY KEY,
252 j JSON DEFAULT (CAST('["HelloWorld"]' AS JSON)),
253 KEY mv_idx_binary (( CAST(j->'$[*]' AS BINARY(10) ARRAY) )) );
254
255 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON));
256 INSERT INTO t1 VALUES(2, CAST('["HelloMySQL", "Hello"]' AS JSON));
257
258 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
259 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
260
261 SET GLOBAL innodb_purge_stop_now = ON;
262 DELETE FROM t1;
263
264 START TRANSACTION;
265 INSERT INTO t1 VALUES(1, CAST('["HelloMySQL", "Hello"]' AS JSON));
266 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
267 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
268 ROLLBACK;
269
270 SET GLOBAL innodb_purge_run_now=ON;
271
272 SELECT * FROM t1 WHERE "HelloMySQL" MEMBER OF (j->'$[*]');
273 SELECT * FROM t1 WHERE "Hello" MEMBER OF (j->'$[*]');
274
275 CHECK TABLE t1;
276
277 DROP TABLE t1;
278
279
280 # A more complicated case which will re-calculate the virtual column
281 # for several times during INSERT which is actually done by modifying
282 # existing record
283 CREATE TABLE t1 (id INT NOT NULL KEY AUTO_INCREMENT, f1 JSON, f2 JSON, f3 JSON, f4 JSON , f5 JSON, f6 JSON,
284 KEY mv_idx_binary (( CAST(f1->'$[*]' AS CHAR(10) ARRAY) )),
285 KEY mv_idx_binary1 (( CAST(f2->'$[*]' AS CHAR(10) ARRAY) )),
286 KEY mv_idx_binary2 (( CAST(f3->'$[*]' AS CHAR(10) ARRAY) )),
287 KEY mv_idx_binary3 (( CAST(f4->'$[*]' AS CHAR(10) ARRAY) )),
288 KEY mv_idx_binary4 (( CAST(f5->'$[*]' AS CHAR(10) ARRAY) )),
289 KEY mv_idx_binary5 (( CAST(f6->'$[*]' AS CHAR(10) ARRAY) )));
290
291 INSERT INTO t1 VALUES(1, CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON), CAST('["HelloWorld", "Hello"]' AS JSON));
292
293 SET GLOBAL innodb_purge_stop_now = ON;
294 DELETE FROM t1;
295
296 INSERT INTO t1 VALUES(1, CAST('["Helloworl", "hell"]' AS JSON), CAST('["Helloworl", "hell"]' AS JSON), CAST('["helloworl", "hell"]' AS JSON), CAST('["helloworl", "hell"]' AS JSON), CAST('["helloworl", "well"]' AS JSON), CAST('["helloworl", "hell"]' AS JSON));
297
298 CHECK TABLE t1;
299
300 DELETE FROM t1;
301
302 CHECK TABLE t1;
303
304 DROP TABLE t1;
305
306 SET GLOBAL innodb_purge_run_now=ON;
307
308
309
310 # Concurrent DMLs testing
311 # This testing is for the scenario that for one existing multi-value index
312 # record, follow-up insert to its neighbour will lock it for duplicate key
313 # check, and then a delete on it will wait for the lock. Again if the same
314 # key value(with the locked record) is inserted, the implicit lock should be
315 # checked properly
316
317 CREATE TABLE IF NOT EXISTS t1 (
318 i INT NOT NULL PRIMARY KEY,
319 j JSON DEFAULT (CAST('["100"]' AS JSON)),
320 UNIQUE KEY mv_idx_binary (( CAST(j->'$[*]' AS UNSIGNED ARRAY) )) );
321
322 connect (conn1,localhost,root,,);
323 connect (conn2,localhost,root,,);
324
325 connection default;
326
327 INSERT INTO t1 VALUES(1, CAST('["90"]' AS JSON));
328
329 connection conn2;
330 START TRANSACTION;
331 INSERT INTO t1 VALUES(2, CAST('["100"]' AS JSON));
332
333 connection default;
334 START TRANSACTION;
335 SET DEBUG_SYNC='lock_rec_convert_impl_to_expl SIGNAL converting WAIT_FOR go';
336 --send INSERT INTO t1 VALUES(3, CAST('["100"]' AS JSON));
337
338 connection conn2;
339 SET DEBUG_SYNC='now WAIT_FOR converting';
340 COMMIT;
341 SET DEBUG_SYNC='now SIGNAL go';
342
343 connection default;
344 --error ER_DUP_ENTRY
345 --reap
346
347 connection conn1;
348 START TRANSACTION;
349 SET DEBUG_SYNC='row_update_for_mysql_error SIGNAL del_fail WAIT_FOR delete_go';
350 --send DELETE FROM t1;
351
352 connection default;
353 SET DEBUG_SYNC='now WAIT_FOR del_fail';
354 --error ER_DUP_ENTRY
355 INSERT INTO t1 VALUES(4, CAST('["100"]' AS JSON));
356 SET DEBUG_SYNC='now SIGNAL delete_go';
357 COMMIT;
358
359 connection conn1;
360 --reap;
361 COMMIT;
362
363 disconnect conn1;
364 disconnect conn2;
365
366 connection default;
367
368 CHECK TABLE t1;
369
370 SELECT * FROM t1;
371
372 DROP TABLE t1;
373
374
375
376 # Test the multi-value column doens't need to handled during purge
377 # because of the multi-value index has been dropped
378
379 CREATE TABLE t1 (a INT, b INT, f JSON,
380 KEY mv_idx_binary ((( CAST(f->'$[*]' AS BINARY(10) ARRAY ))))
381 ) ENGINE=InnoDB;
382
383 INSERT INTO t1 (a,b,f) VALUES(1, 1, '["foobar"]');
384
385 connect (con1,localhost,root,,);
386 # disable purge
387 CREATE TABLE t0 (a INT) ENGINE=InnoDB;
388 BEGIN; SELECT * FROM t0;
389
390 connection default;
391 # write the problematic update_undo log record
392 UPDATE t1 SET f = NULL;
393
394 ALTER TABLE t1 DROP INDEX mv_idx_binary, ALGORITHM=INPLACE;
395
396 connection con1;
397 # enable purge
398 COMMIT;
399 UPDATE t1 SET a = 1;
400
401 connection default;
402 # wait for purge to process the update_undo record.
403 # Note, the alter table would increase trx_sys->rw_max_trx_id
404 set global innodb_purge_run_now=ON;
405 sleep 1;
406
407 CHECK TABLE t1;
408 SELECT * FROM t1;
409
410 connection con1;
411 disconnect con1;
412
413 connection default;
414
415 DROP TABLE t0, t1;
416
417
418
419 # Test online DDL
420
421 CREATE TABLE t1(a INT PRIMARY KEY, b INT, f1 JSON) ENGINE=InnoDB;
422 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS CHAR(10) ARRAY)));
423
424 INSERT INTO t1 VALUES(1, 1, CAST('["1111", "2222"]' AS JSON));
425 INSERT INTO t1 VALUES(2, 2, CAST('["11111", "2222", "22222", "3333"]' AS JSON));
426 INSERT INTO t1 VALUES(3, 3, CAST('["1111", "2222", "22222", "3333", "444"]' AS JSON));
427 INSERT INTO t1 VALUES(4, 4, CAST('["22222", "3333", "444", "5555"]' AS JSON));
428
429 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL before_apply WAIT_FOR apply';
430 --send OPTIMIZE TABLE t1;
431
432 connect (con1,localhost,root,,);
433 connection con1;
434 --echo connection con1
435
436 SET DEBUG_SYNC='now WAIT_FOR before_apply';
437 INSERT INTO t1 VALUES(5, 5, CAST('["2222", "5555"]' AS JSON));
438 DELETE FROM t1 WHERE "444" MEMBER OF (f1->"$[*]");
439 INSERT INTO t1 VALUES(6, 6, CAST('["1111", "444"]' AS JSON));
440 UPDATE t1 SET f1 = CAST('["11111", "2222"]' AS JSON) WHERE a = 1;
441 UPDATE t1 SET f1 = CAST('["11111", "22222", "33333", "44444"]' AS JSON) WHERE a = 3;
442 UPDATE t1 SET f1 = CAST('["2222", "5555", "6666"]' AS JSON) WHERE a = 5;
443 SET DEBUG_SYNC='now SIGNAL apply';
444
445 connection default;
446 --echo connection default
447 --echo /* reap */ OPTIMIZE TABLE t1;
448 reap;
449
450 CHECK TABLE t1;
451
452 SELECT * FROM t1;
453 SELECT * FROM t1 WHERE "1111" MEMBER OF (f1->"$[*]");
454 SELECT * FROM t1 WHERE "5555" MEMBER OF (f1->"$[*]");
455 SELECT * FROM t1 WHERE "444" MEMBER OF (f1->"$[*]");
456 SELECT * FROM t1 WHERE "11111" MEMBER OF (f1->"$[*]");
457 SELECT * FROM t1 WHERE "22222" MEMBER OF (f1->"$[*]");
458
459
460 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL before_apply_1 WAIT_FOR apply_1';
461 --send OPTIMIZE TABLE t1;
462
463 connection con1;
464 SET DEBUG_SYNC='now WAIT_FOR before_apply_1';
465 INSERT INTO t1 VALUES(7, 7, CAST('["6666", "1111"]' AS JSON));
466 DELETE FROM t1 WHERE "22222" MEMBER OF (f1->"$[*]");
467 INSERT INTO t1 VALUES(8, 8, CAST('["1111", "3333", "444", "6666"]' AS JSON));
468 UPDATE t1 SET f1 = CAST('["5555", "2222", "6666", "777"]' AS JSON) WHERE a = 5;
469 UPDATE t1 SET f1 = CAST('["666", "777", "6666", "7777"]' AS JSON) WHERE a = 6;
470 SET DEBUG_SYNC='now SIGNAL apply_1';
471
472 connection default;
473 --echo connection default
474 --echo /* reap */ OPTIMIZE TABLE t1;
475 reap;
476
477 disconnect con1;
478
479 CHECK TABLE t1;
480
481 SELECT * FROM t1;
482 SELECT * FROM t1 WHERE "1111" MEMBER OF (f1->"$[*]");
483 SELECT * FROM t1 WHERE "5555" MEMBER OF (f1->"$[*]");
484 SELECT * FROM t1 WHERE "444" MEMBER OF (f1->"$[*]");
485 SELECT * FROM t1 WHERE "11111" MEMBER OF (f1->"$[*]");
486 SELECT * FROM t1 WHERE "22222" MEMBER OF (f1->"$[*]");
487 SELECT * FROM t1 WHERE "666" MEMBER OF (f1->"$[*]");
488 SELECT * FROM t1 WHERE "6666" MEMBER OF (f1->"$[*]");
489 SELECT * FROM t1 WHERE "777" MEMBER OF (f1->"$[*]");
490 SELECT * FROM t1 WHERE "7777" MEMBER OF (f1->"$[*]");
491 SELECT * FROM t1 WHERE "3333" MEMBER OF (f1->"$[*]");
492
493 DROP TABLE t1;
494
495
496
497 # Test if the ord_part of virtual columns should be set properly after ALTER
498 # TABLE.
499 # The problem is if there is a failed ADD INDEX, and during dropping the half
500 # done index, the table could be accessed by another thread, thus the in-memory
501 # index could not be deleted, and marked as corrupted, etc
502 # When next DROP INDEX comes in, it will first mark the index as to be dropped,
503 # and then it will try to remove the in-memory index object previously created.
504 # However, the ord_part of related columns are not set properly since the
505 # current to be dropped index is marked as to be dropped.
506 # Just before the COMMIT phase of DROP INDEX, another thread comes in and try
507 # to delete the row, it will fail to store the full row, including virtual
508 # columns, because the ord_part of some columns are not set properly(all are 0
509 # incorrectly).
510
511 CREATE TABLE `t1` (
512 `id` int(11) NOT NULL AUTO_INCREMENT,
513 `j1` JSON DEFAULT (CAST(_utf8mb4'[5,-1,0]' AS JSON)),
514 `j2` JSON DEFAULT (CAST(_utf8mb4'[9,8,0,1]' AS JSON)),
515 `j3` JSON DEFAULT (CAST(_utf8mb4'["foobar"]' AS JSON)),
516 `j4` JSON DEFAULT (CAST(_utf8mb4'["HelloWorld"]' AS JSON)),
517 `j5` JSON DEFAULT (CAST(_utf8mb4'["HelloMySQL"]' AS JSON)),
518 PRIMARY KEY (`id`),
519 KEY `mv_idx_binary` ((JSON_LENGTH(`j3`)), (CAST(JSON_EXTRACT(`j4`,_utf8mb4'$[*]') AS BINARY(10) ARRAY)), (JSON_DEPTH(`j2`)))
520 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
521
522 INSERT INTO t1 VALUES(0, CAST(_utf8mb4'[5,-1,0]' AS JSON), CAST(_utf8mb4'[9,8,0,1]' AS JSON), CAST(_utf8mb4'["foobar"]' AS JSON), CAST(_utf8mb4'["HelloWorld"]' AS JSON), CAST(_utf8mb4'["HelloMySQL"]' AS JSON));
523
524 # This row will fail next ADD INDEX
525 INSERT INTO t1 VALUES(0, CAST(_utf8mb4'[5,-1,0]' AS JSON), CAST(_utf8mb4'[9,8,0,1]' AS JSON), CAST(_utf8mb4'["foobar"]' AS JSON), CAST(_utf8mb4'["HelloWorld"]' AS JSON), '[]');
526
527 SELECT * FROM t1;
528
529 # Prepare an aborted index
530 connect (conn1,localhost,root,,);
531 connect (conn2,localhost,root,,);
532
533 connection default;
534
535 SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL rollback WAIT_FOR try_drop';
536 --send CREATE INDEX mv_idx_date ON t1 ((CAST(j5->'$' AS UNSIGNED ARRAY)));
537
538 connection conn1;
539 SET DEBUG_SYNC='now WAIT_FOR rollback';
540 SET DEBUG_SYNC='row_search_for_mysql_before_return SIGNAL scanning WAIT_FOR continue';
541 --send SELECT * FROM t1;
542
543 connection conn2;
544 SET DEBUG_SYNC='now WAIT_FOR scanning';
545 SET DEBUG_SYNC='now SIGNAL try_drop';
546
547 connection default;
548 --error ER_INVALID_JSON_VALUE_FOR_FUNC_INDEX
549 --reap
550
551 connection conn2;
552 SET DEBUG_SYNC='now SIGNAL continue';
553
554 connection conn1;
555 --reap;
556
557 # Now, start a DROP INDEX, which will first mark the index as
558 # to be dropped and clean above aborted index.
559 connection default;
560
561 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL inplace_done WAIT_FOR upgrade_lock';
562 --send ALTER TABLE t1 DROP INDEX mv_idx_binary;
563
564 connection conn1;
565
566 SET DEBUG_SYNC='now WAIT_FOR inplace_done';
567 SET DEBUG_SYNC='innodb_row_upd_clust_step_enter SIGNAL upgrade_lock';
568 --send DELETE FROM t1;
569
570 # If everything goes well, the DELETE should succeed.
571 --reap
572
573 connection default;
574 --reap
575
576 disconnect conn1;
577 disconnect conn2;
578
579 connection default;
580
581 SELECT * FROM t1;
582
583 DROP TABLE t1;
584
585
586
587 --source include/wait_until_count_sessions.inc
588
589 --echo #
590 --echo # Bug#31474182 THE MULTI-VALUED INDEX RETURNS ERROR WHEN --DEBUG OPTION SPECIFIED.
591 --echo #
592
593 set debug = 'd:t';
594 CREATE TABLE t1 (f1 JSON, id VARCHAR(64) AS (f1->"$[0]") STORED NOT NULL PRIMARY KEY);
595 CREATE INDEX i1 ON t1((CAST(f1->"$[*]" AS UNSIGNED ARRAY)));
596 ANALYZE TABLE t1;
597 SELECT * FROM t1 WHERE JSON_OVERLAPS(f1->'$[*]', '[4,3,7]') ORDER BY id;
598 DROP TABLE t1;
599 set debug = default;
600