Debian Codegraph @

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

raw
1 ######################################################################
2 # Test restoring backups with lossy conversions on [var]char|binary types
3 ######################################################################
4
5 -- source include/have_ndb.inc
6 -- source suite/ndb/include/backup_restore_setup.inc
7
8 # mysqld's configuration is not relevant to this test
9
10 # Turn off STRICT sql_mode since test intentionally truncates
11 # data when preparing the test data(those offending statements
12 # are within enable/disable_warnings)
13 --source suite/ndb/include/turn_off_strict_sql_mode.inc
14
15 --echo ************************************************************
16 --echo * Creating table with char+binary types
17 --echo ************************************************************
18
19 CREATE TABLE t2(
20 c1 char(2), c2 char(128), c3 char(255),
21 b1 binary(2), b2 binary(128), b3 binary(255),
22 vc1 varchar(2), vc2 varchar(255), vc3 varchar(1024),
23 vb1 varbinary(2), vb2 varbinary(255), vb3 varbinary(1024)
24 ) CHARACTER SET latin1 ENGINE=NDB;
25
26 INSERT INTO t2 VALUES(
27 repeat('a', 2), repeat('b', 128), repeat('c', 255),
28 repeat('d', 2), repeat('e', 128), repeat('f', 255),
29 repeat('g', 2), repeat('h', 255), repeat('i', 1024),
30 repeat('j', 2), repeat('k', 255), repeat('l', 1024)
31 );
32
33 --echo ************************************************************
34 --echo * Backing up table with char+binary types
35 --echo ************************************************************
36
37 --source include/ndb_backup.inc
38
39 # command shortcuts
40 --let $restore_cmd=$NDB_RESTORE -b $the_backup_id
41 --let $restore_cmd=$restore_cmd -r --backup_path=$NDB_BACKUPS-$the_backup_id
42
43 --echo ************************************************************
44 --echo * Restoring table with unchanged char+binary types:
45 --echo * char(2) --> char(2)
46 --echo * char(128) --> char(128)
47 --echo * char(255) --> char(255)
48 --echo * binary(2) --> binary(2)
49 --echo * binary(128) --> binary(128)
50 --echo * binary(255) --> binary(255)
51 --echo * varchar(2) --> varchar(2)
52 --echo * varchar(255) --> varchar(255)
53 --echo * varchar(1024) --> varchar(1024)
54 --echo * varbinary(2) --> varbinary(2)
55 --echo * varbinary(255) --> varbinary(255)
56 --echo * varbinary(1024) --> varbinary(1024)
57 --echo ************************************************************
58
59 # create a MyISAM table from NDB table, against which to compare data
60 CREATE TABLE t2_myisam ENGINE=MYISAM AS SELECT * FROM t2;
61
62 # restore table
63 DELETE FROM t2;
64 # for debugging:
65 # --exec $NDB_RESTORE -b $the_backup_id -n 1 -L -r --print $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
66 # --exec $NDB_RESTORE -b $the_backup_id -n 2 -L -r --print $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
67 --exec $restore_cmd -n 1 --print -L > /dev/null
68 --exec $restore_cmd -n 2 --print -L > /dev/null
69 SELECT
70 length(c1), length(c2), length(c3),
71 length(b1), length(b2), length(b2),
72 length(vc1), length(vc2), length(vc3),
73 length(vb1), length(vb2), length(vb3)
74 FROM t2;
75
76 # verify data
77 SELECT COUNT(*) FROM t2 NATURAL JOIN t2_myisam;
78 DROP TABLE t2_myisam;
79
80 --echo ************************************************************
81 --echo * Restoring table with 1st degree demoted char+binary types:
82 --echo * char(2) --> char(1)
83 --echo * char(128) --> char(2)
84 --echo * char(255) --> char(128)
85 --echo * binary(2) --> binary(1)
86 --echo * binary(128) --> binary(2)
87 --echo * binary(255) --> binary(128)
88 --echo * varchar(2) --> varchar(1)
89 --echo * varchar(255) --> varchar(2)
90 --echo * varchar(1024) --> varchar(512)
91 --echo * varbinary(2) --> varbinary(1)
92 --echo * varbinary(255) --> varbinary(2)
93 --echo * varbinary(1024) --> varbinary(512)
94 --echo ************************************************************
95
96 # demote char+binary type attibutes
97 --disable_warnings
98 ALTER TABLE t2
99 modify c1 char(1), modify c2 char(2), modify c3 char(128),
100 modify b1 binary(1), modify b2 binary(2), modify b3 binary(128),
101 modify vc1 varchar(1), modify vc2 varchar(2), modify vc3 varchar(512),
102 modify vb1 varbinary(1), modify vb2 varbinary(2), modify vb3 varbinary(512);
103 --enable_warnings
104 SELECT
105 length(c1), length(c2), length(c3),
106 length(b1), length(b2), length(b2),
107 length(vc1), length(vc2), length(vc3),
108 length(vb1), length(vb2), length(vb3)
109 FROM t2;
110
111 # create a MyISAM table from NDB table, against which to compare data
112 CREATE TABLE t2_myisam ENGINE=MYISAM AS SELECT * FROM t2;
113
114 # restore table
115 DELETE FROM t2;
116 --exec $restore_cmd -n 1 --print -L > /dev/null
117 --exec $restore_cmd -n 2 --print -L > /dev/null
118 SELECT
119 length(c1), length(c2), length(c3),
120 length(b1), length(b2), length(b2),
121 length(vc1), length(vc2), length(vc3),
122 length(vb1), length(vb2), length(vb3)
123 FROM t2;
124
125 # verify data
126 SELECT COUNT(*) FROM t2 NATURAL JOIN t2_myisam;
127 DROP TABLE t2_myisam;
128
129 --echo ************************************************************
130 --echo * Restoring table with 2nd degree demoted char+binary types:
131 --echo * char(2) --> char(1)
132 --echo * char(128) --> char(1)
133 --echo * char(255) --> char(2)
134 --echo * binary(2) --> binary(1)
135 --echo * binary(128) --> binary(1)
136 --echo * binary(255) --> binary(2)
137 --echo * varchar(2) --> varchar(1)
138 --echo * varchar(255) --> varchar(1)
139 --echo * varchar(1024) --> varchar(255)
140 --echo * varbinary(2) --> varbinary(1)
141 --echo * varbinary(255) --> varbinary(1)
142 --echo * varbinary(1024) --> varbinary(255)
143 --echo ************************************************************
144
145 # demote char+binary type attibutes
146 --disable_warnings
147 ALTER TABLE t2
148 modify c1 char(1), modify c2 char(1), modify c3 char(2),
149 modify b1 binary(1), modify b2 binary(1), modify b3 binary(2),
150 modify vc1 varchar(1), modify vc2 varchar(1), modify vc3 varchar(255),
151 modify vb1 varbinary(1), modify vb2 varbinary(1), modify vb3 varbinary(255);
152 --enable_warnings
153 SELECT
154 length(c1), length(c2), length(c3),
155 length(b1), length(b2), length(b2),
156 length(vc1), length(vc2), length(vc3),
157 length(vb1), length(vb2), length(vb3)
158 FROM t2;
159
160 # create a MyISAM table from NDB table, against which to compare data
161 CREATE TABLE t2_myisam ENGINE=MYISAM AS SELECT * FROM t2;
162
163 # restore table
164 DELETE FROM t2;
165 --exec $restore_cmd -n 1 --print -L > /dev/null
166 --exec $restore_cmd -n 2 --print -L > /dev/null
167 SELECT
168 length(c1), length(c2), length(c3),
169 length(b1), length(b2), length(b2),
170 length(vc1), length(vc2), length(vc3),
171 length(vb1), length(vb2), length(vb3)
172 FROM t2;
173
174 # verify data
175 SELECT COUNT(*) FROM t2 NATURAL JOIN t2_myisam;
176 DROP TABLE t2_myisam;
177
178 --echo ************************************************************
179 --echo * Restoring table with 3rd degree demoted char+binary types:
180 --echo * char(2) --> char(1)
181 --echo * char(128) --> char(1)
182 --echo * char(255) --> char(1)
183 --echo * binary(2) --> binary(1)
184 --echo * binary(128) --> binary(1)
185 --echo * binary(255) --> binary(1)
186 --echo * varchar(2) --> varchar(1)
187 --echo * varchar(255) --> varchar(1)
188 --echo * varchar(1024) --> varchar(1)
189 --echo * varbinary(2) --> varbinary(1)
190 --echo * varbinary(255) --> varbinary(1)
191 --echo * varbinary(1024) --> varbinary(1)
192 --echo ************************************************************
193
194 # demote char+binary type attibutes
195 --disable_warnings
196 ALTER TABLE t2
197 modify c1 char(1), modify c2 char(1), modify c3 char(1),
198 modify b1 binary(1), modify b2 binary(1), modify b3 binary(1),
199 modify vc1 varchar(1), modify vc2 varchar(1), modify vc3 varchar(1),
200 modify vb1 varbinary(1), modify vb2 varbinary(1), modify vb3 varbinary(1);
201 --enable_warnings
202 SELECT
203 length(c1), length(c2), length(c3),
204 length(b1), length(b2), length(b2),
205 length(vc1), length(vc2), length(vc3),
206 length(vb1), length(vb2), length(vb3)
207 FROM t2;
208
209 # create a MyISAM table from NDB table, against which to compare data
210 CREATE TABLE t2_myisam ENGINE=MYISAM AS SELECT * FROM t2;
211
212 # restore table
213 DELETE FROM t2;
214 --exec $restore_cmd -n 1 --print -L > /dev/null
215 --exec $restore_cmd -n 2 --print -L > /dev/null
216 SELECT
217 length(c1), length(c2), length(c3),
218 length(b1), length(b2), length(b2),
219 length(vc1), length(vc2), length(vc3),
220 length(vb1), length(vb2), length(vb3)
221 FROM t2;
222
223 # verify data
224 SELECT COUNT(*) FROM t2 NATURAL JOIN t2_myisam;
225 DROP TABLE t2_myisam;
226
227 --echo ************************************************************
228 --echo * Restoring table with changed var-ness of char+binary types:
229 --echo * char(2) --> varchar(2)
230 --echo * char(128) --> varchar(128)
231 --echo * char(255) --> varchar(255)
232 --echo * binary(2) --> varbinary(2)
233 --echo * binary(128) --> varbinary(128)
234 --echo * binary(255) --> varbinary(255)
235 --echo * varchar(2) --> char(2)
236 --echo * varchar(255) --> char(255)
237 --echo * varchar(1024) --> varchar(1024)
238 --echo * varbinary(2) --> binary(2)
239 --echo * varbinary(255) --> binary(255)
240 --echo * varbinary(1024) --> varbinary(1024)
241 --echo ************************************************************
242
243 # demote char+binary type attibutes
244 --disable_warnings
245 DELETE FROM t2;
246 ALTER TABLE t2
247 modify c1 varchar(2), modify c2 varchar(128), modify c3 varchar(255),
248 modify b1 varbinary(2), modify b2 varbinary(128), modify b3 varbinary(255),
249 modify vc1 char(2), modify vc2 char(255), modify vc3 varchar(1024),
250 modify vb1 binary(2), modify vb2 binary(255), modify vb3 varbinary(1024);
251 INSERT INTO t2 VALUES(
252 repeat('a', 2), repeat('b', 128), repeat('c', 255),
253 repeat('d', 2), repeat('e', 128), repeat('f', 255),
254 repeat('g', 2), repeat('h', 255), repeat('i', 1024),
255 repeat('j', 2), repeat('k', 255), repeat('l', 1024)
256 );
257 --enable_warnings
258 SELECT
259 length(c1), length(c2), length(c3),
260 length(b1), length(b2), length(b2),
261 length(vc1), length(vc2), length(vc3),
262 length(vb1), length(vb2), length(vb3)
263 FROM t2;
264
265 # create a MyISAM table from NDB table, against which to compare data
266 CREATE TABLE t2_myisam ENGINE=MYISAM AS SELECT * FROM t2;
267
268 # restore table
269 DELETE FROM t2;
270 --exec $restore_cmd -n 1 --print -A > /dev/null
271 --exec $restore_cmd -n 2 --print -A > /dev/null
272 SELECT
273 length(c1), length(c2), length(c3),
274 length(b1), length(b2), length(b2),
275 length(vc1), length(vc2), length(vc3),
276 length(vb1), length(vb2), length(vb3)
277 FROM t2;
278
279 # verify data
280 SELECT COUNT(*) FROM t2 NATURAL JOIN t2_myisam;
281 DROP TABLE t2_myisam;
282
283 --echo ************************************************************
284 --echo * Restoring table with changed varness + 1st degree demoted types:
285 --echo * char(2) --> varchar(1)
286 --echo * char(128) --> varchar(2)
287 --echo * char(255) --> varchar(128)
288 --echo * binary(2) --> varbinary(1)
289 --echo * binary(128) --> varbinary(2)
290 --echo * binary(255) --> varbinary(128)
291 --echo * varchar(2) --> char(1)
292 --echo * varchar(255) --> char(2)
293 --echo * varchar(1024) --> char(255)
294 --echo * varbinary(2) --> binary(1)
295 --echo * varbinary(255) --> binary(2)
296 --echo * varbinary(1024) --> binary(255)
297 --echo ************************************************************
298
299 # demote char+binary type attibutes
300 --disable_warnings
301 DELETE FROM t2;
302 ALTER TABLE t2
303 modify c1 varchar(1), modify c2 varchar(2), modify c3 varchar(128),
304 modify b1 varbinary(1), modify b2 varbinary(2), modify b3 varbinary(128),
305 modify vc1 char(1), modify vc2 char(2), modify vc3 char(255),
306 modify vb1 binary(1), modify vb2 binary(2), modify vb3 binary(255);
307 INSERT INTO t2 VALUES(
308 repeat('a', 2), repeat('b', 128), repeat('c', 255),
309 repeat('d', 2), repeat('e', 128), repeat('f', 255),
310 repeat('g', 2), repeat('h', 255), repeat('i', 1024),
311 repeat('j', 2), repeat('k', 255), repeat('l', 1024)
312 );
313 --enable_warnings
314 SELECT
315 length(c1), length(c2), length(c3),
316 length(b1), length(b2), length(b2),
317 length(vc1), length(vc2), length(vc3),
318 length(vb1), length(vb2), length(vb3)
319 FROM t2;
320
321 # create a MyISAM table from NDB table, against which to compare data
322 CREATE TABLE t2_myisam ENGINE=MYISAM AS SELECT * FROM t2;
323
324 # restore table
325 DELETE FROM t2;
326 --exec $restore_cmd -n 1 --print -L > /dev/null
327 --exec $restore_cmd -n 2 --print -L > /dev/null
328 SELECT
329 length(c1), length(c2), length(c3),
330 length(b1), length(b2), length(b2),
331 length(vc1), length(vc2), length(vc3),
332 length(vb1), length(vb2), length(vb3)
333 FROM t2;
334
335 # verify data
336 SELECT COUNT(*) FROM t2 NATURAL JOIN t2_myisam;
337 DROP TABLE t2_myisam;
338
339 --echo ************************************************************
340 --echo * Restoring table with changed varness + 2nd degree demoted types:
341 --echo * char(2) --> varchar(1)
342 --echo * char(128) --> varchar(1)
343 --echo * char(255) --> varchar(2)
344 --echo * binary(2) --> varbinary(1)
345 --echo * binary(128) --> varbinary(1)
346 --echo * binary(255) --> varbinary(2)
347 --echo * varchar(2) --> char(1)
348 --echo * varchar(255) --> char(1)
349 --echo * varchar(1024) --> char(255)
350 --echo * varbinary(2) --> binary(1)
351 --echo * varbinary(255) --> binary(1)
352 --echo * varbinary(1024) --> binary(255)
353 --echo ************************************************************
354
355 # demote char+binary type attibutes
356 --disable_warnings
357 DELETE FROM t2;
358 ALTER TABLE t2
359 modify c1 varchar(1), modify c2 varchar(1), modify c3 varchar(2),
360 modify b1 varbinary(1), modify b2 varbinary(1), modify b3 varbinary(2),
361 modify vc1 char(1), modify vc2 char(1), modify vc3 char(255),
362 modify vb1 binary(1), modify vb2 binary(1), modify vb3 binary(255);
363 INSERT INTO t2 VALUES(
364 repeat('a', 2), repeat('b', 128), repeat('c', 255),
365 repeat('d', 2), repeat('e', 128), repeat('f', 255),
366 repeat('g', 2), repeat('h', 255), repeat('i', 1024),
367 repeat('j', 2), repeat('k', 255), repeat('l', 1024)
368 );
369 --enable_warnings
370 SELECT
371 length(c1), length(c2), length(c3),
372 length(b1), length(b2), length(b2),
373 length(vc1), length(vc2), length(vc3),
374 length(vb1), length(vb2), length(vb3)
375 FROM t2;
376
377 # create a MyISAM table from NDB table, against which to compare data
378 CREATE TABLE t2_myisam ENGINE=MYISAM AS SELECT * FROM t2;
379
380 # restore table
381 DELETE FROM t2;
382 --exec $restore_cmd -n 1 --print -L > /dev/null
383 --exec $restore_cmd -n 2 --print -L > /dev/null
384 SELECT
385 length(c1), length(c2), length(c3),
386 length(b1), length(b2), length(b2),
387 length(vc1), length(vc2), length(vc3),
388 length(vb1), length(vb2), length(vb3)
389 FROM t2;
390
391 # verify data
392 SELECT COUNT(*) FROM t2 NATURAL JOIN t2_myisam;
393 DROP TABLE t2_myisam;
394
395 --echo ************************************************************
396 --echo * Restoring table with changed varness + 3rd degree demoted types:
397 --echo * char(2) --> varchar(1)
398 --echo * char(128) --> varchar(1)
399 --echo * char(255) --> varchar(1)
400 --echo * binary(2) --> varbinary(1)
401 --echo * binary(128) --> varbinary(1)
402 --echo * binary(255) --> varbinary(1)
403 --echo * varchar(2) --> char(1)
404 --echo * varchar(255) --> char(1)
405 --echo * varchar(1024) --> char(1)
406 --echo * varbinary(2) --> binary(1)
407 --echo * varbinary(255) --> binary(1)
408 --echo * varbinary(1024) --> binary(1)
409 --echo ************************************************************
410
411 # demote char+binary type attibutes
412 --disable_warnings
413 DELETE FROM t2;
414 ALTER TABLE t2
415 modify c1 varchar(1), modify c2 varchar(1), modify c3 varchar(1),
416 modify b1 varbinary(1), modify b2 varbinary(1), modify b3 varbinary(1),
417 modify vc1 char(1), modify vc2 char(1), modify vc3 char(1),
418 modify vb1 binary(1), modify vb2 binary(1), modify vb3 binary(1);
419 INSERT INTO t2 VALUES(
420 repeat('a', 2), repeat('b', 128), repeat('c', 255),
421 repeat('d', 2), repeat('e', 128), repeat('f', 255),
422 repeat('g', 2), repeat('h', 255), repeat('i', 1024),
423 repeat('j', 2), repeat('k', 255), repeat('l', 1024)
424 );
425 --enable_warnings
426 SELECT
427 length(c1), length(c2), length(c3),
428 length(b1), length(b2), length(b2),
429 length(vc1), length(vc2), length(vc3),
430 length(vb1), length(vb2), length(vb3)
431 FROM t2;
432
433 # create a MyISAM table from NDB table, against which to compare data
434 CREATE TABLE t2_myisam ENGINE=MYISAM AS SELECT * FROM t2;
435
436 # restore table
437 DELETE FROM t2;
438 --exec $restore_cmd -n 1 --print -L > /dev/null
439 --exec $restore_cmd -n 2 --print -L > /dev/null
440 SELECT
441 length(c1), length(c2), length(c3),
442 length(b1), length(b2), length(b2),
443 length(vc1), length(vc2), length(vc3),
444 length(vb1), length(vb2), length(vb3)
445 FROM t2;
446
447 # verify data
448 SELECT COUNT(*) FROM t2 NATURAL JOIN t2_myisam;
449 DROP TABLE t2_myisam;
450
451 --echo ************************************************************
452 --echo * Deleting table with char+binary types
453 --echo ************************************************************
454
455 DROP TABLE t2;
456
457 --source suite/ndb/include/restore_sql_mode_after_turn_off_strict.inc
458 --source suite/ndb/include/backup_restore_cleanup.inc
459 --remove_file $NDB_TOOLS_OUTPUT