Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2 -- source include/not_windows.inc
3 -- source suite/ndb/include/backup_restore_setup.inc
4
5 --echo ***********************
6 --echo Test binary conversions
7 --echo ***********************
8
9 create table test.t1 (
10 a int unsigned primary key,
11 b binary(20),
12 c varbinary(200),
13 d varbinary(2000),
14 e blob,
15 f tinyblob,
16 g mediumblob,
17 h longblob
18 ) engine=ndb;
19
20 insert into test.t1 values (1,
21 '1111111111', # binary
22 repeat('Pattern', 20), # varbinary
23 repeat('LongPattern', 100), # longvarbinary
24 repeat('Tartan', 4096), # blob
25 repeat('Tartan', 40), # tinyblob
26 repeat('Tartan', 4096), # mediumblob
27 repeat('Tartan', 4096)); # longblob
28 insert into test.t1 select a+1, repeat(a+1, 10), c, d, e, f, g, h from test.t1;
29 insert into test.t1 select a+2, repeat(a+2, 10), c, d, e, f, g, h from test.t1;
30 insert into test.t1 select a+4, repeat(a+4, 10), c, d, e, f, g, h from test.t1;
31
32 --echo Source data
33 select a,
34 hex(b),
35 sha1(c),
36 length(c),
37 sha1(d),
38 length(d),
39 sha1(e),
40 length(e),
41 sha1(f),
42 length(f),
43 sha1(g),
44 length(g),
45 sha1(h),
46 length(h) from test.t1 order by a;
47
48 --echo Run backup
49 --source include/ndb_backup.inc
50
51 rename table test.t1 to test.t1_orig;
52
53 --echo ********************
54 --echo Test type promotions
55 --echo ********************
56 --echo Have one of each we are interested in to start with
57 --echo Repeatedly 'shift left' onto wider types
58 --echo longblob <- mediumblob <- blob <- longvarbinary <- tinyblob <- varbinary <- binary
59
60 create table test.t1 (
61 a int unsigned primary key,
62 b varbinary(20), # binary -> varbinary
63 c varbinary(2000), # varbinary -> longvarbinary
64 d blob, # longvarbinary -> blob
65 e mediumblob, # blob -> mediumblob
66 f blob, # tinyblob -> blob
67 g longblob, # mediumblob -> longblob
68 h longblob # longblob -> longblob
69 ) engine=ndb;
70
71 --echo # run restore
72 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
73 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
74
75 --echo Expect no data differences between orig and restored
76 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
77 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
78 from t1_orig o join t1 r
79 on o.a = r.a
80 where LEFT(o.b,10) != r.b or # ignore Binary padding
81 length(r.b) != 10 or
82 o.c != r.c or
83 o.d != r.d or
84 o.e != r.e or
85 o.f != r.f or
86 o.g != r.g or
87 o.h != r.h;
88 drop table test.t1;
89
90 create table test.t1 (
91 a int unsigned primary key,
92 b varbinary(2000), # binary -> longvarbinary
93 c tinyblob, # varbinary -> tinyblob
94 d mediumblob, # longvarbinary -> mediumblob
95 e longblob, # blob -> longblob
96 f mediumblob, # tinyblob -> mediumblob
97 g longblob, # mediumblob -> longblob
98 h longblob # longblob -> longblob
99 ) engine=ndb;
100
101 --echo # run restore
102 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
103 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
104
105 --echo Expect no data differences between orig and restored
106 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
107 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
108 from t1_orig o join t1 r
109 on o.a = r.a
110 where LEFT(o.b,10) != r.b or # ignore Binary padding
111 length(r.b) != 10 or
112 o.c != r.c or
113 o.d != r.d or
114 o.e != r.e or
115 o.f != r.f or
116 o.g != r.g or
117 o.h != r.h;
118 drop table test.t1;
119
120
121 create table test.t1 (
122 a int unsigned primary key,
123 b tinyblob, # binary -> tinyblob
124 c blob, # varbinary -> blob
125 d longblob, # longvarbinary -> longblob
126 e longblob, # blob -> longblob
127 f longblob, # tinyblob -> longblob
128 g longblob, # mediumblob -> longblob
129 h longblob # longblob -> longblob
130 ) engine=ndb;
131
132 --echo # run restore
133 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
134 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
135
136 --echo Expect no data differences between orig and restored
137 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
138 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
139 from t1_orig o join t1 r
140 on o.a = r.a
141 where LEFT(o.b,10) != r.b or # ignore Binary padding
142 length(r.b) != 10 or
143 o.c != r.c or
144 o.d != r.d or
145 o.e != r.e or
146 o.f != r.f or
147 o.g != r.g or
148 o.h != r.h;
149 drop table test.t1;
150
151 create table test.t1 (
152 a int unsigned primary key,
153 b blob, # binary -> blob
154 c mediumblob, # varbinary -> mediumblob
155 d longblob, # longvarbinary -> longblob
156 e longblob, # blob -> longblob
157 f longblob, # tinyblob -> longblob
158 g longblob, # mediumblob -> longblob
159 h longblob # longblob -> longblob
160 ) engine=ndb;
161
162 --echo # run restore
163 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
164 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
165
166 --echo Expect no data differences between orig and restored
167 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
168 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
169 from t1_orig o join t1 r
170 on o.a = r.a
171 where LEFT(o.b,10) != r.b or # ignore Binary padding
172 length(r.b) != 10 or
173 o.c != r.c or
174 o.d != r.d or
175 o.e != r.e or
176 o.f != r.f or
177 o.g != r.g or
178 o.h != r.h;
179 drop table test.t1;
180
181 create table test.t1 (
182 a int unsigned primary key,
183 b mediumblob, # binary -> mediumblob
184 c longblob, # varbinary -> longblob
185 d longblob, # longvarbinary -> longblob
186 e longblob, # blob -> longblob
187 f longblob, # tinyblob -> longblob
188 g longblob, # mediumblob -> longblob
189 h longblob # longblob -> longblob
190 ) engine=ndb;
191
192 --echo # run restore
193 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
194 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
195
196 --echo Expect no data differences between orig and restored
197 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
198 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
199 from t1_orig o join t1 r
200 on o.a = r.a
201 where LEFT(o.b,10) != r.b or # ignore Binary padding
202 length(r.b) != 10 or
203 o.c != r.c or
204 o.d != r.d or
205 o.e != r.e or
206 o.f != r.f or
207 o.g != r.g or
208 o.h != r.h;
209 drop table test.t1;
210
211 create table test.t1 (
212 a int unsigned primary key,
213 b longblob, # binary -> longblob
214 c longblob, # varbinary -> longblob
215 d longblob, # longvarbinary -> longblob
216 e longblob, # blob -> longblob
217 f longblob, # tinyblob -> longblob
218 g longblob, # mediumblob -> longblob
219 h longblob # longblob -> longblob
220 ) engine=ndb;
221
222 --echo # run restore
223 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
224 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
225
226
227 --echo Expect no data differences between orig and restored
228 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
229 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
230 from t1_orig o join t1 r
231 on o.a = r.a
232 where LEFT(o.b,10) != r.b or # ignore Binary padding
233 length(r.b) != 10 or
234 o.c != r.c or
235 o.d != r.d or
236 o.e != r.e or
237 o.f != r.f or
238 o.g != r.g or
239 o.h != r.h;
240 drop table test.t1;
241
242 --echo *******************
243 --echo Test type demotions
244 --echo *******************
245 --echo Have one of each we are interested in to start with
246 --echo Repeatedly 'shift right' onto smaller types
247 --echo longblob -> mediumblob -> blob -> longvarbinary -> tinyblob -> varbinary -> binary
248
249 create table test.t1 (
250 a int unsigned primary key,
251 b binary(20), # binary -> binary
252 c binary(200), # varbinary -> binary
253 d tinyblob, # longvarbinary -> tinyblob
254 e varbinary(2000), # blob -> longvarbinary
255 f varbinary(200), # tinyblob -> varbinary
256 g blob, # mediumblob -> blob
257 h mediumblob # longblob -> mediumblob
258 ) engine=ndb;
259
260 --echo # run restore
261 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
262 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
263
264 --echo Expect no unexpected differences between orig and restored
265 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
266 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
267 from t1_orig o join t1 r
268 on o.a = r.a
269 where o.b != r.b # b as is
270 or o.c != left(r.c, 140)
271 or length(o.c) != 140 # c expanded to 200
272 or left(o.d, 255) != r.d # d truncated to 255
273 or left(o.e,2000) != r.e
274 or length(r.e) != 2000 # e truncated to 2000
275 or left(o.f,200) != r.f
276 or length(r.f) != 200 # f truncated to 200
277 or o.g != r.g # g as-is
278 or o.h != r.h # h as-is
279 ;
280 drop table test.t1;
281
282 create table test.t1 (
283 a int unsigned primary key,
284 b binary(20), # binary -> binary
285 c binary(200), # varbinary -> binary
286 d varbinary(200), # longvarbinary -> varbinary
287 e tinyblob, # blob -> tinyblob
288 f binary(200), # tinyblob -> binary
289 g varbinary(2000), # mediumblob -> longvarbinary
290 h blob # longblob -> blob
291 ) engine=ndb;
292
293 --echo # run restore
294 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
295 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
296
297 --echo Expect no unexpected differences between orig and restored
298 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
299 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
300 from t1_orig o join t1 r
301 on o.a = r.a
302 where o.b != r.b # b as is
303 or o.c != left(r.c, 140)
304 or length(o.c) != 140 # c expanded to 200
305 or left(o.d, 200) != r.d # d truncated to 200
306 or left(o.e, 255) != r.e
307 or length(r.e) != 255 # e truncated to 255
308 or left(o.f, 200) != r.f
309 or length(r.f) != 200 # f truncated to 200
310 or left(o.g, 2000) != r.g
311 or length(r.g) != 2000 # g truncated to 2000
312 or o.h != r.h # h as-is
313 ;
314 drop table test.t1;
315
316
317 create table test.t1 (
318 a int unsigned primary key,
319 b binary(20), # binary -> binary
320 c binary(200), # varbinary -> binary
321 d binary(200), # longvarbinary -> binary
322 e varbinary(200), # blob -> varbinary
323 f binary(200), # tinyblob -> binary
324 g tinyblob, # mediumblob -> tinyblob
325 h varbinary(2000) # longblob -> longvarbinary
326 ) engine=ndb;
327
328 --echo # run restore
329 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
330 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
331
332 --echo Expect no unexpected differences between orig and restored
333 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
334 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
335 from t1_orig o join t1 r
336 on o.a = r.a
337 where o.b != r.b # b as is
338 or o.c != left(r.c, 140)
339 or length(o.c) != 140 # c expanded to 200
340 or left(o.d, 200) != r.d # d truncated to 200
341 or left(o.e, 200) != r.e
342 or length(r.e) != 200 # e truncated to 200
343 or left(o.f, 200) != r.f
344 or length(r.f) != 200 # f truncated to 200
345 or left(o.g, 255) != r.g
346 or length(r.g) != 255 # g truncated to 255
347 or left(o.h, 2000) != r.h
348 or length(r.h) != 2000 # h truncated to 2000
349 ;
350 drop table test.t1;
351
352
353 create table test.t1 (
354 a int unsigned primary key,
355 b binary(20), # binary -> binary
356 c binary(200), # varbinary -> binary
357 d binary(200), # longvarbinary -> binary
358 e binary(200), # blob -> binary
359 f binary(200), # tinyblob -> binary
360 g varbinary(200), # mediumblob -> varbinary
361 h tinyblob # longblob -> tinyblob
362 ) engine=ndb;
363
364 --echo # run restore
365 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
366 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
367
368 --echo Expect no unexpected differences between orig and restored
369 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
370 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
371 from t1_orig o join t1 r
372 on o.a = r.a
373 where o.b != r.b # b as is
374 or o.c != left(r.c, 140)
375 or length(o.c) != 140 # c expanded to 200
376 or left(o.d, 200) != r.d # d truncated to 200
377 or left(o.e, 200) != r.e
378 or length(r.e) != 200 # e truncated to 200
379 or left(o.f, 200) != r.f
380 or length(r.f) != 200 # f truncated to 200
381 or left(o.g, 200) != r.g
382 or length(r.g) != 200 # g truncated to 200
383 or left(o.h, 255) != r.h
384 or length(r.h) != 255 # h truncated to 255
385 ;
386 drop table test.t1;
387
388
389 create table test.t1 (
390 a int unsigned primary key,
391 b binary(20), # binary -> binary
392 c binary(200), # varbinary -> binary
393 d binary(200), # longvarbinary -> binary
394 e binary(200), # blob -> binary
395 f binary(200), # tinyblob -> binary
396 g binary(200), # mediumblob -> binary
397 h varbinary(200) # longblob -> varbinary
398 ) engine=ndb;
399
400 --echo # run restore
401 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
402 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
403
404 --echo Expect no unexpected differences between orig and restored
405 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
406 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
407 from t1_orig o join t1 r
408 on o.a = r.a
409 where o.b != r.b # b as is
410 or o.c != left(r.c, 140)
411 or length(o.c) != 140 # c expanded to 200
412 or left(o.d, 200) != r.d # d truncated to 200
413 or left(o.e, 200) != r.e
414 or length(r.e) != 200 # e truncated to 200
415 or left(o.f, 200) != r.f
416 or length(r.f) != 200 # f truncated to 200
417 or left(o.g, 200) != r.g
418 or length(r.g) != 200 # g truncated to 200
419 or left(o.h, 200) != r.h
420 or length(r.h) != 200 # h truncated to 200
421 ;
422 drop table test.t1;
423
424 create table test.t1 (
425 a int unsigned primary key,
426 b binary(20), # binary -> binary
427 c binary(200), # varbinary -> binary
428 d binary(200), # longvarbinary -> binary
429 e binary(200), # blob -> binary
430 f binary(200), # tinyblob -> binary
431 g binary(200), # mediumblob -> binary
432 h binary(200) # longblob -> binary
433 ) engine=ndb;
434
435 --echo # run restore
436 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
437 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
438
439 --echo Expect no unexpected differences between orig and restored
440 select o.a, o.b, sha1(o.c), length(o.c), sha1(o.d), length(o.d), sha1(o.e), length(o.e), sha1(o.f), length(o.f), sha1(o.g), length(o.g), sha1(o.h), length(o.h),
441 r.a, r.b, sha1(r.c), length(r.c), sha1(r.d), length(r.d), sha1(r.e), length(r.e), sha1(r.f), length(r.f), sha1(r.g), length(r.g), sha1(r.h), length(r.h)
442 from t1_orig o join t1 r
443 on o.a = r.a
444 where o.b != r.b # b as is
445 or o.c != left(r.c, 140)
446 or length(o.c) != 140 # c expanded to 200
447 or left(o.d, 200) != r.d # d truncated to 200
448 or left(o.e, 200) != r.e
449 or length(r.e) != 200 # e truncated to 200
450 or left(o.f, 200) != r.f
451 or length(r.f) != 200 # f truncated to 200
452 or left(o.g, 200) != r.g
453 or length(r.g) != 200 # g truncated to 200
454 or left(o.h, 200) != r.h
455 or length(r.h) != 200 # h truncated to 200
456 ;
457 drop table test.t1;
458 drop table test.t1_orig;
459
460 --echo ********************************
461 --echo Text to blob conversions
462 --echo ********************************
463
464 create table test.t2 (
465 a int unsigned primary key,
466 tinytext1 tinytext,
467 tinytext2 tinytext,
468 tinytext3 tinytext,
469 tinytext4 tinytext,
470 text1 text,
471 text2 text,
472 text3 text,
473 text4 text,
474 mediumtext1 mediumtext,
475 mediumtext2 mediumtext,
476 mediumtext3 mediumtext,
477 mediumtext4 mediumtext,
478 longtext1 longtext,
479 longtext2 longtext,
480 longtext3 longtext,
481 longtext4 longtext
482 ) engine=ndb character set UTF8MB4;
483
484 insert into test.t2 values (1,
485 repeat('Tartan', 40), # tinytext
486 repeat('Tartan', 40),
487 repeat('Tartan', 40),
488 repeat('Tartan', 40),
489 repeat('Tartan', 4096), # text
490 repeat('Tartan', 4096),
491 repeat('Tartan', 4096),
492 repeat('Tartan', 4096),
493 repeat('Tartan', 4096), # mediumtext
494 repeat('Tartan', 4096),
495 repeat('Tartan', 4096),
496 repeat('Tartan', 4096),
497 repeat('Tartan', 4096), # longtext
498 repeat('Tartan', 4096),
499 repeat('Tartan', 4096),
500 repeat('Tartan', 4096));
501
502 insert into test.t2 values (0,
503 NULL, NULL, NULL, NULL,
504 NULL, NULL, NULL, NULL,
505 NULL, NULL, NULL, NULL,
506 NULL, NULL, NULL, NULL);
507
508 --echo Source data
509 select a,
510 sha(tinytext1), sha(tinytext2), sha(tinytext3), sha(tinytext4),
511 length(tinytext1), length(tinytext2), length(tinytext3), length(tinytext4),
512 sha(text1), sha(text2), sha(text3), sha(text4),
513 length(text1), length(text2), length(text3), length(text4),
514 sha(mediumtext1), sha(mediumtext2), sha(mediumtext3), sha(mediumtext4),
515 length(mediumtext1), length(mediumtext2), length(mediumtext3), length(mediumtext4),
516 sha(longtext1), sha(longtext2), sha(longtext3), sha(longtext4),
517 length(longtext1), length(longtext2), length(longtext3), length(longtext4) from test.t2 order by a;
518
519 --echo Run backup
520 --source include/ndb_backup.inc
521
522 rename table test.t2 to test.t2_orig;
523
524 create table test.t2 (
525 a int unsigned primary key,
526 tinytext1 tinyblob, # tinytext -> tinyblob
527 tinytext2 blob, # tinytext -> blob
528 tinytext3 mediumblob, # tinytext -> mediumblob
529 tinytext4 longblob, # tinytext -> longblob
530 text1 tinyblob, # text -> tinyblob
531 text2 blob, # text -> blob
532 text3 mediumblob, # text -> mediumblob
533 text4 longblob, # text -> longblob
534 mediumtext1 tinyblob, # mediumtext -> tinyblob
535 mediumtext2 blob, # mediumtext -> blob
536 mediumtext3 mediumblob,# mediumtext -> mediumblob
537 mediumtext4 longblob, # mediumtext -> longblob
538 longtext1 tinyblob, # longtext -> tinyblob
539 longtext2 blob, # longtext -> blob
540 longtext3 mediumblob, # longtext -> mediumblob
541 longtext4 longblob # longtext -> longblob
542 ) engine=ndb;
543
544 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
545 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
546
547 select count(*) from test.t2;
548
549 select a,
550 sha(tinytext1), sha(tinytext2), sha(tinytext3), sha(tinytext4),
551 length(tinytext1), length(tinytext2), length(tinytext3), length(tinytext4),
552 sha(text1), sha(text2), sha(text3), sha(text4),
553 length(text1), length(text2), length(text3), length(text4),
554 sha(mediumtext1), sha(mediumtext2), sha(mediumtext3), sha(mediumtext4),
555 length(mediumtext1), length(mediumtext2), length(mediumtext3), length(mediumtext4),
556 sha(longtext1), sha(longtext2), sha(longtext3), sha(longtext4),
557 length(longtext1), length(longtext2), length(longtext3), length(longtext4) from test.t2 order by a;
558
559 drop table test.t2;
560 drop table test.t2_orig;
561
562 --echo ********************************
563 --echo Blob to text conversions
564 --echo ********************************
565
566 create table test.t2 (
567 a int unsigned primary key,
568 tinyblob1 tinyblob,
569 tinyblob2 tinyblob,
570 tinyblob3 tinyblob,
571 tinyblob4 tinyblob,
572 blob1 blob,
573 blob2 blob,
574 blob3 blob,
575 blob4 blob,
576 mediumblob1 mediumblob,
577 mediumblob2 mediumblob,
578 mediumblob3 mediumblob,
579 mediumblob4 mediumblob,
580 longblob1 longblob,
581 longblob2 longblob,
582 longblob3 longblob,
583 longblob4 longblob
584 ) engine=ndb character set UTF8MB4;
585
586 insert into test.t2 values (1,
587 repeat('Tartan', 40), # tinyblob
588 repeat('Tartan', 40),
589 repeat('Tartan', 40),
590 repeat('Tartan', 40),
591 repeat('Tartan', 4096), # blob
592 repeat('Tartan', 4096),
593 repeat('Tartan', 4096),
594 repeat('Tartan', 4096),
595 repeat('Tartan', 4096), # mediumblob
596 repeat('Tartan', 4096),
597 repeat('Tartan', 4096),
598 repeat('Tartan', 4096),
599 repeat('Tartan', 4096), # longblob
600 repeat('Tartan', 4096),
601 repeat('Tartan', 4096),
602 repeat('Tartan', 4096));
603
604 insert into test.t2 values (0,
605 NULL, NULL, NULL, NULL,
606 NULL, NULL, NULL, NULL,
607 NULL, NULL, NULL, NULL,
608 NULL, NULL, NULL, NULL);
609
610 --echo Source data
611 select a,
612 sha(tinyblob1), sha(tinyblob2), sha(tinyblob3), sha(tinyblob4),
613 length(tinyblob1), length(tinyblob2), length(tinyblob3), length(tinyblob4),
614 sha(blob1), sha(blob2), sha(blob3), sha(blob4),
615 length(blob1), length(blob2), length(blob3), length(blob4),
616 sha(mediumblob1), sha(mediumblob2), sha(mediumblob3), sha(mediumblob4),
617 length(mediumblob1), length(mediumblob2), length(mediumblob3), length(mediumblob4),
618 sha(longblob1), sha(longblob2), sha(longblob3), sha(longblob4),
619 length(longblob1), length(longblob2), length(longblob3), length(longblob4) from test.t2 order by a;
620
621 --echo Run backup
622 --source include/ndb_backup.inc
623
624 rename table test.t2 to test.t2_orig;
625
626 create table test.t2 (
627 a int unsigned primary key,
628 tinyblob1 tinytext, # tinyblob -> tinytext
629 tinyblob2 text, # tinyblob -> text
630 tinyblob3 mediumtext, # tinyblob -> mediumtext
631 tinyblob4 longtext, # tinyblob -> longtext
632 blob1 tinytext, # blob -> tinytext
633 blob2 text, # blob -> text
634 blob3 mediumtext, # blob -> mediumtext
635 blob4 longtext, # blob -> longtext
636 mediumblob1 tinytext, # mediumblob -> tinytext
637 mediumblob2 text, # mediumblob -> text
638 mediumblob3 mediumtext,# mediumblob -> mediumtext
639 mediumblob4 longtext, # mediumblob -> longtext
640 longblob1 tinytext, # longblob -> tinytext
641 longblob2 text, # longblob -> text
642 longblob3 mediumtext, # longblob -> mediumtext
643 longblob4 longtext # longblob -> longtext
644 ) engine=ndb;
645
646 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
647 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
648
649 select count(*) from test.t2;
650
651 select a,
652 sha(tinyblob1), sha(tinyblob2), sha(tinyblob3), sha(tinyblob4),
653 length(tinyblob1), length(tinyblob2), length(tinyblob3), length(tinyblob4),
654 sha(blob1), sha(blob2), sha(blob3), sha(blob4),
655 length(blob1), length(blob2), length(blob3), length(blob4),
656 sha(mediumblob1), sha(mediumblob2), sha(mediumblob3), sha(mediumblob4),
657 length(mediumblob1), length(mediumblob2), length(mediumblob3), length(mediumblob4),
658 sha(longblob1), sha(longblob2), sha(longblob3), sha(longblob4),
659 length(longblob1), length(longblob2), length(longblob3), length(longblob4) from test.t2 order by a;
660
661 drop table test.t2;
662 drop table test.t2_orig;
663
664 --echo *****************************************************************************
665 --echo Charset-aware truncation in blob -> tinytext and text ->tinyblob conversions
666 --echo *****************************************************************************
667
668 --echo # ndb_restore TEXT -> TINYBLOB and BLOB->TINYTEXT truncation with UTF8MB4
669 set character_set_client=UTF8MB4;
670 create table t2(id int primary key, text_val text character set UTF8MB4, blob_val blob)engine=ndb;
671 insert into t2 values(11111, REPEAT(UNHEX("E38182"), 100), REPEAT(UNHEX("E38182"), 100));
672 insert into t2 values(22222, CONCAT("AA",REPEAT(UNHEX("E38182"), 100)), CONCAT("AA",REPEAT(UNHEX("E38182"), 100)));
673 select length(text_val), char_length(text_val), length(blob_val), char_length(blob_val) from t2 order by id;
674 --echo # run backup
675 --source include/ndb_backup.inc
676
677 drop table t2;
678 create table t2(id int primary key, text_val tinyblob, blob_val tinytext character set UTF8MB4)engine=ndb;
679
680 --echo # run restore
681 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
682 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --promote-attributes --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
683
684 --echo # run ndb_select_all to see charset-aware truncation in ndb
685 --echo # text_val = TINYBLOB, truncation on byte boundary, mid-character
686 --echo # last char E38182 truncated to E3
687 --echo # blob_val = TINYTEXT, truncation on well-formed char boundary
688 --echo # last char E38182 not truncated
689 --exec $NDB_SELECT_ALL -d test t2 -o PRIMARY
690
691 --echo # run mysql select to see charset-aware truncation in mysqld
692 --echo # blob_val = TINYTEXT, truncation on well-formed char boundary
693 --echo # length = well-formed char length * sizeof(UTF8MB4 char)
694 --echo # 1. pk-based read
695 select length(blob_val), char_length(blob_val) from t2 where id = 11111;
696 select length(blob_val), char_length(blob_val) from t2 where id = 22222;
697 --echo # 2. scan
698 select length(blob_val), char_length(blob_val) from t2 order by id;
699
700 --echo # text_val = TINYBLOB, truncation on byte boundary, mid-character
701 --echo # length = well-formed char length
702 --echo # 1. pk-based read
703 select length(text_val), char_length(text_val) from t2 where id = 11111;
704 select length(text_val), char_length(text_val) from t2 where id = 22222;
705 --echo # 2. scan
706 select length(text_val), char_length(text_val) from t2 order by id;
707
708 drop table t2;
709 --source suite/ndb/include/backup_restore_cleanup.inc
710 --remove_file $NDB_TOOLS_OUTPUT