Debian Codegraph @

mysql-test/suite/ndb/t/ndb_blob_size.test

raw
1 --source include/have_ndb.inc
2 --result_format 2
3
4 # WL#15044 Configurable BLOB inline size
5 ## CREATE and INSERT definitions of extended Blob feature BLOB_INLINE_SIZE
6
7 CREATE TABLE test.defs(
8 a int primary key,
9 b blob,
10 bm mediumblob,
11 bl longblob,
12 t text,
13 tm mediumtext,
14 tl longtext,
15 j json) engine=ndb;
16
17 ## Verify inline and partition sizes of test.defs
18 SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'defs';
19
20 DROP TABLE test.defs;
21
22 ## Create table with inline values of 0
23 CREATE TABLE test.zeroinline(
24 a int primary key,
25 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=0",
26 bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=0",
27 bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=0",
28 t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=0",
29 tm mediumtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=0",
30 tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=0",
31 j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=0") engine=ndb;
32
33 ## Verify inline and partition sizes of test.zeroinline
34 SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'zeroinline';
35
36 DROP TABLE test.zeroinline;
37
38 ## Create table with less than default inline values
39 CREATE TABLE test.shortinline(
40 a int primary key,
41 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
42 bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
43 bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
44 t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
45 tm mediumtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
46 tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
47 j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=10") engine=ndb;
48
49 ## Verify inline and partition sizes of test.shortinline
50 SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'shortinline';
51
52 ## Insert and validate test.shortinline
53 INSERT INTO test.shortinline VALUES (
54 1,
55 repeat('J',60000),
56 repeat('J',60000),
57 repeat('J',60000),
58 repeat(_utf32 0x1F47E,15000),
59 repeat(_utf32 0x1F47E,60000),
60 repeat(_utf32 0x1F47E,60000),
61 "{\"f\": \"12\"}");
62
63 SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.shortinline;
64 DROP TABLE test.shortinline;
65
66 ## Create table with large inline values
67 CREATE TABLE test.longinline(
68 a int primary key,
69 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000",
70 bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000",
71 bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000",
72 t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000",
73 tm mediumtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000",
74 tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000",
75 j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=6000") engine=ndb;
76
77 ## Verify inline and partition sizes of test.longinline
78 SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'longinline';
79
80 ## Insert and validate
81 INSERT INTO test.longinline values (
82 1,
83 repeat('J',60000),
84 repeat('J',60000),
85 repeat('J',60000),
86 repeat(_utf32 0x1F47E,15000),
87 repeat(_utf32 0x1F47E,60000),
88 repeat(_utf32 0x1F47E,60000),
89 "{\"f\": \"12\"}");
90
91 SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.longinline;
92 DROP TABLE test.longinline;
93
94 ## Create table with some inline values below their default
95 CREATE TABLE test.mixedinline(
96 a int primary key,
97 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=20000",
98 bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=25",
99 bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=288",
100 t text,
101 tm mediumtext comment "Random other comment",
102 tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=233",
103 j json) engine=ndb;
104
105 ## Verify inline and partition sizes of test.mixedinline
106 SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'mixedinline';
107
108 ## Insert and validate test.mixedinline
109 INSERT INTO test.mixedinline VALUES (
110 1,
111 repeat('J',60000),
112 repeat('J',60000),
113 repeat('J',60000),
114 repeat(_utf32 0x1F47E,15000),
115 repeat(_utf32 0x1F47E,60000),
116 repeat(_utf32 0x1F47E,60000),
117 "{\"f\": \"12\"}");
118
119 SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.mixedinline;
120
121 DROP TABLE test.mixedinline;
122
123 ## Create table with larger inline values and maximum partition size (= 13948)
124 CREATE TABLE test.withmaxparts(
125 a int primary key,
126 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=20000,MAX_BLOB_PART_SIZE=1",
127 bm mediumblob comment "NDB_COLUMN=MAX_BLOB_PART_SIZE=1,BLOB_INLINE_SIZE=320",
128 bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=320",
129 t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=320,MAX_BLOB_PART_SIZE",
130 tm mediumtext comment "NDB_COLUMN=MAX_BLOB_PART_SIZE=0",
131 tl longtext comment "NDB_COLUMN=MAX_BLOB_PART_SIZE=1",
132 j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=8192") engine=ndb;
133
134 ## Verify inline and partition sizes of test.withmaxparts
135 SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'withmaxparts';
136
137 ## Insert and validate test.withmaxparts
138 INSERT INTO test.withmaxparts VALUES (
139 1,
140 repeat('J',60000),
141 repeat('J',60000),
142 repeat('J',60000),
143 repeat(_utf32 0x1F47E,15000),
144 repeat(_utf32 0x1F47E,60000),
145 repeat(_utf32 0x1F47E,60000),
146 "{\"f\": \"12\"}");
147
148 SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.withmaxparts;
149
150 DROP TABLE test.withmaxparts;
151
152 ## Create tables with bad values
153
154 CREATE TABLE test.neglen(
155 a int primary key,
156 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=-20") engine=ndb;
157 CREATE TABLE test.badchar(
158 a int primary key,
159 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=ten") engine=ndb;
160
161 ## Verify inline_size
162 SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'neglen';
163 SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'badchar';
164
165 DROP TABLE test.neglen;
166 DROP TABLE test.badchar;
167
168 --error ER_CANT_CREATE_TABLE
169 CREATE TABLE test.badlen(
170 a int primary key,
171 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=40000") engine=ndb;
172 SHOW WARNINGS;
173
174 --error ER_CANT_CREATE_TABLE
175 CREATE TABLE test.maxlen(
176 a int primary key,
177 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=29981") engine=ndb;
178 SHOW WARNINGS;
179
180 ## Max len for inline Blob is 30000 - (4 + 16[header])
181 CREATE TABLE test.maxlen(
182 a int primary key,
183 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=29980") engine=ndb;
184 ## Verify inline of test.maxlen
185 SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'maxlen';
186
187 DROP TABLE test.maxlen;
188
189 ## TINYBLOB columns ignore the BLOB_INLINE_SIZE option
190 CREATE TABLE test.bigtiny(
191 a int primary key,
192 tb tinyblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=512") engine=ndb;
193 CREATE TABLE test.smalltiny(
194 a int primary key,
195 tb tinyblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=128") engine=ndb;
196
197 DROP TABLE test.bigtiny;
198 DROP TABLE test.smalltiny;
199 --echo
200
201 # WL#15044 Configurable BLOB inline size
202 ## ALTER definitions of extended Blob feature BLOB_INLINE_SIZE
203
204 CREATE TABLE test.inline(
205 a int primary key,
206 b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
207 t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
208 j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=10") engine=ndb;
209
210 ## Verify inline and partition sizes of test.inline
211 SELECT inline_size, part_size FROM ndbinfo.blobs WHERE table_name = 'inline';
212
213 ## ALTER with new blob inline size
214 ALTER TABLE test.inline
215 drop column b,
216 drop column t,
217 drop column j,
218 add column bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=512",
219 add column tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=512",
220 add column js json comment "NDB_COLUMN=BLOB_INLINE_SIZE=8192";
221
222 ## Verify updated test.inline with new BLOB_INLINE_SIZE
223 SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline';
224
225 ## SHOW CREATE TABLE for new BLOB_INLINE_SIZE
226 SHOW CREATE TABLE test.inline;
227 --echo
228
229 ## In-place ALTER TABLE rename column (same comment)
230 ALTER TABLE test.inline ALGORITHM=inplace,
231 change column bl b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=512",
232 change column tl t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=512",
233 change column js j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=8192";
234
235 ## In-place ALTER TABLE add column (non blob due to prevention of online partitioning)
236 ALTER TABLE test.inline ALGORITHM=inplace,
237 add column c char(32);
238
239 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
240 ## In-place ALTER TABLE change column BLOB_INLINE_SIZE will err
241 ALTER TABLE test.inline ALGORITHM=inplace,
242 change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=1000";
243 SHOW WARNINGS;
244
245 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
246 ## In-place ALTER TABLE remove configured BLOB_INLINE_SIZE will err
247 ALTER TABLE test.inline ALGORITHM=inplace,
248 change column b b longblob;
249 SHOW WARNINGS;
250
251 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
252 ## In-place ALTER TABLE change column comment variations for NDB_COLUMN will err
253 ALTER TABLE test.inline ALGORITHM=inplace,
254 change column b b longblob comment "randomNDB_COLUMN=BLOB_INLINE_SIZE=1000";
255 SHOW WARNINGS;
256 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
257 ALTER TABLE test.inline ALGORITHM=inplace,
258 change column b b longblob comment "NDB_COLUMNrandom= BLOB_INLINE_SIZE=1000";
259 SHOW WARNINGS;
260
261 ## Insert some data and verify that read data remains the same
262 INSERT INTO test.inline (a, b, t, j) VALUES (1,
263 repeat(0x424C4F, 512),
264 repeat(0x424C4F, 512),
265 "{\"a\": \"42\"}");
266 SELECT sha1(b), sha1(t), sha1(j) FROM test.inline;
267
268 ## Copy ALTER TABLE change column BLOB_INLINE_SIZE changes inline_blob_size
269 ALTER TABLE test.inline algorithm=copy,
270 change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=1000",
271 change column t t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=1000",
272 change column j j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=10000";
273
274 ## Verify updated test.inline
275 SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline';
276 ## Verify data
277 SELECT sha1(b), sha1(t), sha1(j) FROM test.inline;
278
279 ## Copy ALTER TABLE change back BLOB_INLINE_SIZE to less
280 ALTER TABLE test.inline algorithm=copy,
281 change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
282 change column t t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=10",
283 change column j j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=25";
284
285 ## Verify updated test.inline with reduced BLOB_INLINE_SIZE
286 SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline';
287 ## Verify data
288 SELECT sha1(b), sha1(t), sha1(j) FROM test.inline;
289
290 ## Copy ALTER TABLE change to zero inline size
291 ALTER TABLE test.inline algorithm=copy,
292 change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=0",
293 change column t t longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=0",
294 change column j j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=0";
295
296 ## Verify updated test.inline with zero inline size
297 SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline';
298 ## Verify data
299 SELECT sha1(b), sha1(t), sha1(j) FROM test.inline;
300
301 ## Copy ALTER TABLE too big BLOB_INLINE_SIZE
302 --error ER_CANT_CREATE_TABLE
303 ALTER TABLE test.inline algorithm=copy,
304 change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=27949",
305 change column t t longtext comment "NDB_COLUMM=BLOB_INLINE_SIZE=1000",
306 change column j j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=27949";
307 SHOW WARNINGS;
308
309 ## ALTER TABLE random characters on COMMENT before NDB_COLUMN
310 ALTER TABLE test.inline change column b b longblob comment "randomNDB_COLUMN=BLOB_INLINE_SIZE=15";
311
312 ## Verify altered test.inline
313 SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'inline';
314
315 --error ER_ILLEGAL_HA_CREATE_OPTION
316 ## ALTER TABLE random characters on COMMENT after NDB_COLUMN
317 ALTER TABLE test.inline change column b b longblob comment "NDB_COLUMN=randomBLOB_INLINE_SIZE=25";
318 SHOW WARNINGS LIMIT 2;
319
320 ## ALTER TABLE on table for TINYBLOB
321 --error ER_DATA_TOO_LONG
322 ALTER TABLE test.inline change column b tb tinyblob;
323 ## Length value on b (1536) is still larger than what can fit in TINYBLOB length (255)
324 ALTER TABLE test.inline change column b b longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=255";
325 --error ER_DATA_TOO_LONG
326 ALTER TABLE test.inline change column b tb tinyblob;
327
328 ## Must update data to allow to fit TINYBLOB
329 UPDATE test.inline SET b = repeat(0x424C4F, 64) WHERE a = 1;
330 ALTER TABLE test.inline change column b tb tinyblob;
331 SELECT a, length(tb) FROM test.inline;
332
333 CREATE TABLE test.tiny (
334 a int primary key,
335 tb tinyblob) engine = ndb;
336 ## Always ignored for TINYBLOB
337 ALTER TABLE test.tiny change column tb tb tinyblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=128";
338
339 DROP TABLE test.tiny;
340 DROP TABLE test.inline;
341 --echo
342
343 # WL#15044 Configurable BLOB inline size
344 ## Show Backup and Restore across BLOB inline sizes
345
346 CREATE TABLE test.withmaxparts(
347 a int primary key,
348 b blob comment "NDB_COLUMN=BLOB_INLINE_SIZE=20000,MAX_BLOB_PART_SIZE=1",
349 bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=320",
350 bl longblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=320",
351 t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=320",
352 tm mediumtext comment "irrelevant",
353 tl longtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=320",
354 j json comment "NDB_COLUMN=BLOB_INLINE_SIZE=8192") engine=ndb;
355
356 INSERT INTO test.withmaxparts VALUES (
357 1,
358 repeat('J',60000),
359 repeat('J',60000),
360 repeat('J',60000),
361 repeat(_utf32 0x1F47E,15000),
362 repeat(_utf32 0x1F47E,60000),
363 repeat(_utf32 0x1F47E,60000),
364 "{\"f\": \"12\"}");
365
366 SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.withmaxparts;
367
368 --source suite/ndb/include/backup_restore_setup.inc
369 --source include/ndb_backup.inc
370
371 DROP TABLE test.withmaxparts;
372
373 # CREATE TABLE with diff sizes
374 CREATE TABLE test.withmaxparts(
375 a int primary key,
376 b blob,
377 bm mediumblob comment "NDB_COLUMN=BLOB_INLINE_SIZE=6000,MAX_BLOB_PART_SIZE=1",
378 bl longblob,
379 t text comment "NDB_COLUMN=BLOB_INLINE_SIZE=10000,MAX_BLOB_PART_SIZE",
380 tm mediumtext comment "NDB_COLUMN=BLOB_INLINE_SIZE=3000",
381 tl longtext,
382 j json comment "irrelevant") engine=ndb;
383
384 --echo # restore data from backup
385 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --lossy-conversions --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
386 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --lossy-conversions --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
387
388 SELECT a, sha1(b), sha1(bm), sha1(bl), sha1(t), sha1(tm), sha1(tl), sha1(j) FROM test.withmaxparts;
389
390 ## Verify that inline of test.withmaxparts are the new sizes
391 SELECT inline_size FROM ndbinfo.blobs WHERE table_name = 'withmaxparts';
392
393 DROP TABLE test.withmaxparts;
394
395 --source suite/ndb/include/backup_restore_cleanup.inc
396 --remove_file $NDB_TOOLS_OUTPUT