Debian Codegraph @

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

raw
1 #
2 # Bug #29846292 ROLLBACK OF BIG TRANSACTION DUE TO CONFLICT REMAINS IN HUNG STATE
3 #
4 # This file is based on blob_partial_update.test, with following modifications:
5 # - the json's members being changed are longer than LOB_SMALL_CHANGE_THRESHOLD,
6 # so that the "small change" optimization (which stores modifications inside
7 # undo log record itself instead of creating new versions of blob via partial
8 # updates) does not kick in, and the partial update logic is really tested
9 # - each query is run twice - once with ROLLBACK, once with COMMIT - so that we
10 # can check the behaviour of rollback()
11 # - we mostly modify item [1] instead of item [0] to force rollback() logic to
12 # apply changes on the middle of the blob (instead of the front), which helped
13 # to expose wrong assumptions about loop invariants
14 --source include/have_innodb_max_16k.inc
15 --source include/have_debug.inc
16
17 USE test;
18
19 CREATE TABLE tab(j1 JSON);
20
21 --disable_query_log
22 set @chars128 = repeat('x',128);
23 set @data_1 = repeat('abcdefghijklmnopqrstuvwxyz1234', 6006);
24 set @json_doc_1 = concat('["', @data_1, '", "Sven', @chars128 ,'", "', @data_1, '"]');
25 INSERT INTO tab VALUES
26 (@json_doc_1);
27 ANALYZE TABLE tab;
28 --echo # Sven
29 select json_extract(j1, '$[1]') from tab;
30
31 let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Viswa',@chars128));
32 --source suite/innodb/include/explain_then_rollback_and_commit.inc
33
34 --echo # Show Viswa
35 select json_extract(j1, '$[1]') from tab;
36 let $sql_to_do=UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Nath',@chars128));
37 --source suite/innodb/include/explain_then_rollback_and_commit.inc
38
39 --echo # Show Nath
40 select json_extract(j1, '$[1]') from tab;
41
42 --echo # Modify column type from JSON to LONGTEXT
43
44 ALTER TABLE tab MODIFY j1 LONGTEXT ;
45
46
47 let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setlongtext',@chars128));
48 --source suite/innodb/include/explain_then_rollback_and_commit.inc
49
50 ANALYZE TABLE tab;
51
52 --echo # Check JSON functions works on TEXT datatype
53 SELECT JSON_STORAGE_SIZE(j1) FROM tab;
54
55 SELECT JSON_STORAGE_FREE(j1) FROM tab;
56
57 --echo # Show Setlongtext
58 SELECT json_extract(j1, '$[1]') FROM tab;
59
60
61 let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Replongtext',@chars128));
62 --source suite/innodb/include/explain_then_rollback_and_commit.inc
63
64
65 ANALYZE TABLE tab;
66
67 --echo # Check JSON functions works on TEXT datatype
68 SELECT JSON_STORAGE_SIZE(j1) FROM tab;
69
70 SELECT JSON_STORAGE_FREE(j1) FROM tab;
71
72 --echo # Show Replongtext
73 SELECT json_extract(j1, '$[1]') from tab;
74
75 --echo When column modify from LONGTEXT to JSON
76 ALTER TABLE tab MODIFY j1 JSON ;
77
78
79 let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setjson',@chars128));
80 --source suite/innodb/include/explain_then_rollback_and_commit.inc
81
82
83 ANALYZE TABLE tab;
84
85 --echo # Show Setjson
86 SELECT json_extract(j1, '$[1]') FROM tab;
87
88
89 let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repjson',@chars128));
90 --source suite/innodb/include/explain_then_rollback_and_commit.inc
91
92
93 ANALYZE TABLE tab;
94
95 --echo # Show Repjson
96 SELECT json_extract(j1, '$[1]') FROM tab;
97
98 --echo To COMPRESSED
99 ALTER TABLE tab ROW_FORMAT = COMPRESSED ;
100
101 SHOW CREATE TABLE tab;
102
103
104 let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setcomp',@chars128));
105 --source suite/innodb/include/explain_then_rollback_and_commit.inc
106
107 ANALYZE TABLE tab;
108
109 --echo # Show Setcomp
110 SELECT json_extract(j1, '$[1]') FROM tab;
111
112
113 let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repcomp',@chars128));
114 --source suite/innodb/include/explain_then_rollback_and_commit.inc
115
116 --echo # Show Repcomp
117 SELECT json_extract(j1, '$[1]') FROM tab;
118
119 --echo COMPRESSED to DYNAMIC
120 ALTER TABLE tab ROW_FORMAT = DYNAMIC;
121
122 ANALYZE TABLE tab;
123
124 SHOW CREATE TABLE tab;
125
126
127 let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setdyna',@chars128));
128 --source suite/innodb/include/explain_then_rollback_and_commit.inc
129
130 ANALYZE TABLE tab;
131
132 --echo # Show Setdyna
133 SELECT json_extract(j1, '$[1]') FROM tab;
134
135
136 let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repdyna',@chars128));
137 --source suite/innodb/include/explain_then_rollback_and_commit.inc
138
139 ANALYZE TABLE tab;
140
141 --echo # Show Repdyna
142 SELECT json_extract(j1, '$[1]') FROM tab;
143
144 --echo DYNAMIC to REDUNDANT
145 ALTER TABLE tab ROW_FORMAT = REDUNDANT ;
146
147 SHOW CREATE TABLE tab;
148
149
150 let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setredu',@chars128));
151 --source suite/innodb/include/explain_then_rollback_and_commit.inc
152
153 ANALYZE TABLE tab;
154
155 --echo # Show Setredu
156 SELECT json_extract(j1, '$[1]') FROM tab;
157
158
159 let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repredu',@chars128));
160 --source suite/innodb/include/explain_then_rollback_and_commit.inc
161
162 --echo # Show Repredu
163 SELECT json_extract(j1, '$[1]') FROM tab;
164
165 --echo REDUNDANT to COMPACT
166 ALTER TABLE tab ROW_FORMAT = COMPACT ;
167
168 SHOW CREATE TABLE tab;
169
170 ANALYZE TABLE tab;
171
172
173 let $sql_to_do= UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('Setcomp',@chars128));
174 --source suite/innodb/include/explain_then_rollback_and_commit.inc
175
176 ANALYZE TABLE tab;
177
178 --echo # Show Setcomp
179 SELECT json_extract(j1, '$[1]') FROM tab;
180
181
182 let $sql_to_do= UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repcomp',@chars128));
183 --source suite/innodb/include/explain_then_rollback_and_commit.inc
184
185 --echo # Show Repcomp
186 SELECT json_extract(j1, '$[1]') FROM tab;
187
188 --echo # Create a view on JSON column
189 CREATE VIEW view_tab AS SELECT * FROM tab;
190
191
192 let $sql_to_do= UPDATE view_tab SET j1 = JSON_SET(j1, '$[1]', concat('Setview',@chars128));
193 --source suite/innodb/include/explain_then_rollback_and_commit.inc
194
195 ANALYZE TABLE tab;
196
197 --echo # Check JSON functions works on views
198 SELECT JSON_STORAGE_SIZE(j1) FROM view_tab;
199
200 SELECT JSON_STORAGE_FREE(j1) FROM view_tab;
201
202 --echo # Show Setview
203 SELECT json_extract(j1, '$[1]') FROM view_tab;
204
205
206 let $sql_to_do= UPDATE view_tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Repview',@chars128));
207 --source suite/innodb/include/explain_then_rollback_and_commit.inc
208
209 --echo # Show Repview
210 SELECT json_extract(j1, '$[1]') FROM view_tab;
211
212 ANALYZE TABLE tab;
213
214 --echo # Check JSON functions works on views
215 SELECT JSON_STORAGE_SIZE(j1) FROM view_tab;
216
217 SELECT JSON_STORAGE_FREE(j1) FROM view_tab;
218
219 --echo # Modify Compression to ZLIB
220 ALTER TABLE tab COMPRESSION = 'ZLIB';
221
222 SHOW CREATE TABLE tab;
223
224 delimiter |;
225
226 --echo # Check partial update through procedure
227 CREATE PROCEDURE proc_json_update()
228 begin
229
230 UPDATE tab SET j1 = JSON_REPLACE(j1, '$[1]', concat('Reppro',@chars128));
231
232 end|
233
234 delimiter ;|
235
236 CALL proc_json_update();
237
238 ANALYZE TABLE tab;
239
240 --echo # Show Reppro
241 SELECT json_extract(j1, '$[1]') FROM tab;
242
243 --echo # Check JSON functions on views
244 SELECT JSON_STORAGE_SIZE(j1) FROM view_tab;
245
246 SELECT JSON_STORAGE_FREE(j1) FROM view_tab;
247
248 DROP PROCEDURE proc_json_update;
249
250 --echo # Modify Compression to 'LZ4'
251 ALTER TABLE tab COMPRESSION = 'LZ4';
252
253 SHOW CREATE TABLE tab;
254
255 delimiter |;
256
257 --echo # Check partial update through procedure
258 CREATE PROCEDURE proc_json_update()
259 begin
260 START TRANSACTION;
261 UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('SetLZ',@chars128));
262 ROLLBACK;
263 START TRANSACTION;
264 UPDATE tab SET j1 = JSON_SET(j1, '$[1]', concat('SetLZ',@chars128));
265 COMMIT;
266 end|
267
268 delimiter ;|
269
270 set debug = '+d,lob_print_partial_update_hit';
271
272 CALL proc_json_update();
273
274 ANALYZE TABLE tab;
275
276 --echo # Show SetLZ
277 SELECT json_extract(j1, '$[1]') FROM tab;
278
279 --echo # Check pattern for LOB partial update exist
280 let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
281
282 let SEARCH_PATTERN= LOB partial update;
283 --source include/search_pattern.inc
284
285 --echo # Check JSON functions does not work on BLOB type
286 CREATE TABLE tab1(id INT PRIMARY KEY, j1 JSON);
287
288 INSERT INTO tab1 VALUES(1, '{"a":"a","b":"b"}');
289
290 INSERT INTO tab1 VALUES(2, NULL);
291
292 ALTER TABLE tab1 modify j1 BLOB;
293
294 SHOW CREATE TABLE tab1;
295
296 --error ER_INVALID_JSON_CHARSET
297 UPDATE tab1 SET j1 = JSON_SET(j1, '$.a', 'x');
298
299 --error ER_INVALID_JSON_CHARSET
300 SELECT JSON_STORAGE_SIZE(j1) FROM tab1;
301
302 --error ER_INVALID_JSON_CHARSET
303 SELECT JSON_STORAGE_FREE(j1) FROM tab1;
304
305 --echo # Check partial update with Generated columns
306 CREATE TABLE tab2(id INT PRIMARY KEY, c1 JSON, c2 JSON GENERATED ALWAYS AS (c1) STORED);
307
308 INSERT INTO tab2 VALUES(1, '{"a":"a","b":"b"}', DEFAULT);
309
310 ANALYZE TABLE tab2;
311
312 --skip_if_hypergraph # Depends on the query plan.
313 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
314 EXPLAIN FORMAT=JSON UPDATE tab2 SET c2 = JSON_SET(c2, '$.a', 'x');
315
316 --echo # Check partial update with partition table
317 CREATE TABLE tab3 (a INT, j1 JSON, j2 JSON) PARTITION BY LINEAR HASH(a) PARTITIONS 3;
318
319 SHOW CREATE TABLE tab3;
320
321 INSERT INTO tab3 VALUES(1, '{"a":"a","b":"b"}', '{"a":"a","b":"b"}');
322
323 INSERT INTO tab3 VALUES(2, NULL, NULL);
324
325 INSERT INTO tab3 VALUES(1, '{"a":"aa","b":"bb"}', '{"a":"aa","b":"bb"}');
326
327 INSERT INTO tab3 VALUES(2, '["Sachin","Tendulkar"]','["Virat","Kohili"]');
328
329 INSERT INTO tab3 VALUES(3, '[100 ,{"A1": "Amarnath","B1": "Binni"}]','[200,{"A2": "Azar","S1": "Srikanth"}]');
330
331 INSERT INTO tab3 VALUES(3, '{"Chetan": [300,400],"Sasri": [500,600]}','{"Dhoni": [700,800],"Sidhu": [900,100]}');
332
333 SELECT * FROM tab3 order by a;
334
335 --echo Check with JSON_SET
336
337 let $sql_to_do= UPDATE tab3 SET j1 = JSON_SET(j1, '$.a', 'x'),
338 j2 = JSON_SET(j2, '$.a', 'y'),
339 j1 = JSON_SET(j1, '$.b', 'z');
340 --source suite/innodb/include/explain_then_rollback_and_commit.inc
341
342 SELECT * FROM tab3 order by a;
343
344 --echo Check with JSON_REPLACE
345
346 let $sql_to_do= UPDATE tab3 SET j1 = JSON_REPLACE(j1,'$[1].A1', 'PartRe') ;
347 --source suite/innodb/include/explain_then_rollback_and_commit.inc
348
349 --echo # show PartRe
350 SELECT json_extract(j1, '$[1].A1') FROM tab3;
351
352 SELECT * FROM tab3 order by a;
353
354 # clean up
355 DROP TABLE tab2;
356
357 DROP TABLE tab3;
358
359 DROP PROCEDURE proc_json_update;
360
361 DROP VIEW view_tab;
362
363 DROP TABLE tab;
364
365 DROP TABLE tab1;
366 set debug = '-d,lob_print_partial_update_hit';