Debian Codegraph @

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

raw
1 --source include/have_ndb.inc
2 -- source suite/ndb/include/backup_restore_setup.inc
3
4 --echo # manual ndb_import tests
5
6 --let IS_WINDOWS= `select convert(@@version_compile_os using latin1) in ('Win32', 'Win64', 'Windows')`
7
8 # LOAD DATA line terminators
9 --let $lt_unix='\n'
10 --let $lt_windows='\r\n'
11 if (!$IS_WINDOWS) {
12 --let $lt_default=$lt_unix
13 }
14 if ($IS_WINDOWS) {
15 --let $lt_default=$lt_windows
16 }
17
18 --echo # database argument is required
19 --error 1
20 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
21 >> $NDB_TOOLS_OUTPUT 2>&1;
22
23 --echo # no args is ok
24 --error 0
25 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
26 test >> $NDB_TOOLS_OUTPUT 2>&1;
27
28 --echo # table does not exist
29 --error 1
30 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
31 test '/foo/data/t1.csv' >> $NDB_TOOLS_OUTPUT 2>&1;
32
33 create table t1 (
34 a int primary key,
35 b int not null
36 ) engine=ndb;
37
38 --echo # csv file does not exist
39 --error 1
40 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
41 test '/foo/data/t1.csv' >> $NDB_TOOLS_OUTPUT 2>&1;
42
43 # Most CSV files are written in perl. On windows perl automatically
44 # pushes :crlf layer. We prefer :raw and set line terminator explicitly.
45
46 perl;
47 use strict;
48 use Symbol;
49 my $lt = !$ENV{IS_WINDOWS} ? "\n" : "\r\n";
50 my $vardir = $ENV{MYSQLTEST_VARDIR}
51 or die "need MYSQLTEST_VARDIR";
52 my $file = "$vardir/tmp/t1.csv";
53 my $fh = gensym();
54 open($fh, ">:raw", $file)
55 or die "$file: open for write failed: $!";
56 for my $i (0..999) {
57 print $fh $i, "\t", $i*10, $lt;
58 }
59 close($fh)
60 or die "$file: close after write failed: $!";
61 exit(0)
62 EOF
63
64 --echo # bad state dir
65 --error 1
66 exec $NDB_IMPORT --state-dir=/foo/state --keep-state --log-level=1
67 test $MYSQLTEST_VARDIR/tmp/t1.csv >> $NDB_TOOLS_OUTPUT 2>&1;
68
69 --echo # normal import
70 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
71 --input-type=csv --output-type=ndb
72 test $MYSQLTEST_VARDIR/tmp/t1.csv >> $NDB_TOOLS_OUTPUT 2>&1;
73 select count(*) from t1;
74
75 --echo # invalid --input-type
76 --error 1
77 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
78 --input-type=xxx --output-type=ndb
79 test $MYSQLTEST_VARDIR/tmp/t1.csv >> $NDB_TOOLS_OUTPUT 2>&1;
80
81 --echo # invalid --output-type
82 --error 1
83 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
84 --input-type=csv --output-type=xxx
85 test $MYSQLTEST_VARDIR/tmp/t1.csv >> $NDB_TOOLS_OUTPUT 2>&1;
86
87 --echo # test --connections and --ndb-nodeid
88 --echo # needs consecutive api nodes 230,231,232
89
90 delete from t1;
91
92 create table t1ver like t1;
93
94 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
95 --input-type=csv --output-type=ndb
96 --connections=3 --ndb-nodeid=230
97 test $MYSQLTEST_VARDIR/tmp/t1.csv >> $NDB_TOOLS_OUTPUT 2>&1;
98
99 --disable_query_log
100 eval load data infile '$MYSQLTEST_VARDIR/tmp/t1.csv'
101 into table t1ver
102 lines terminated by $lt_default;
103 --enable_query_log
104
105 select count(*) from t1 x, t1ver y
106 where x.a = y.a and x.b = y.b;
107
108 --echo # simple utf8 test
109
110 perl;
111 use strict;
112 use Symbol;
113 my $lt = !$ENV{IS_WINDOWS} ? "\n" : "\r\n";
114 my $vardir = $ENV{MYSQLTEST_VARDIR}
115 or die "need MYSQLTEST_VARDIR";
116 my $file = "$vardir/tmp/t2.csv";
117 my $fh = gensym();
118 open($fh, ">:raw:utf8", $file)
119 or die "$file: open for write failed: $!";
120 my $c1 = chr(0x00e4);
121 my $c2 = chr(0x263a);
122 my $c3 = chr(0x2665);
123 print $fh 0, "\t", '\N', $lt;
124 print $fh 1, "\t", $c1, $lt;
125 print $fh 2, "\t", $c1, $c2, $lt;
126 print $fh 3, "\t", $c1, $c2, $c3, $lt;
127 close($fh)
128 or die "$file: close after write failed: $!";
129 exit(0);
130 EOF
131
132 create table t2 (
133 a int primary key,
134 b char(3)
135 ) charset utf8mb4
136 engine=ndb;
137
138 create table t2ver (
139 a int primary key,
140 b char(3)
141 ) charset utf8mb4
142 engine=ndb;
143
144 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
145 test $MYSQLTEST_VARDIR/tmp/t2.csv >> $NDB_TOOLS_OUTPUT 2>&1;
146
147 --disable_query_log
148 eval load data infile '$MYSQLTEST_VARDIR/tmp/t2.csv'
149 into table t2ver
150 character set utf8mb4
151 lines terminated by $lt_default;
152 --enable_query_log
153
154 select count(*) from t2 x, t2ver y
155 where x.a = y.a
156 and (x.b = y.b or (x.b is null and y.b is null));
157
158 --echo # test with rejects and no --keep-state
159
160 perl;
161 use strict;
162 use Symbol;
163 my $lt = !$ENV{IS_WINDOWS} ? "\n" : "\r\n";
164 my $vardir = $ENV{MYSQLTEST_VARDIR}
165 or die "need MYSQLTEST_VARDIR";
166 my $file = "$vardir/tmp/t4.csv";
167 my $fh = gensym();
168 open($fh, ">:raw", $file)
169 or die "$file: open for write failed: $!";
170 for my $i (0..999) {
171 if ($i == 333) {
172 print $fh $i, "\t", $i*10, "\t", 333, $lt;
173 } elsif ($i == 666) {
174 print $fh 111, "\t", $i*10, $lt;
175 } elsif ($i == 999) {
176 print $fh $i, "\t", "abcde", $lt;
177 } else {
178 print $fh $i, "\t", $i*10, $lt;
179 }
180 }
181 close($fh)
182 or die "$file: close after write failed: $!";
183 exit(0)
184 EOF
185
186 create table t4 (
187 a int primary key,
188 b int not null
189 ) engine=ndb;
190
191 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --log-level=1
192 --rejects=3
193 test $MYSQLTEST_VARDIR/tmp/t4.csv >> $NDB_TOOLS_OUTPUT 2>&1;
194 select count(*) from t4;
195
196 --error 1
197 --file_exists $MYSQLTEST_VARDIR/tmp/t4.res
198 --error 0
199 --file_exists $MYSQLTEST_VARDIR/tmp/t4.rej
200 --error 1
201 --file_exists $MYSQLTEST_VARDIR/tmp/t4.map
202 --error 1
203 --file_exists $MYSQLTEST_VARDIR/tmp/t4.stt
204
205 --echo # test with rejects and --stats
206
207 delete from t4;
208
209 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --log-level=1
210 --rejects=3 --stats
211 test $MYSQLTEST_VARDIR/tmp/t4.csv >> $NDB_TOOLS_OUTPUT 2>&1;
212 select count(*) from t4;
213
214 --error 1
215 --file_exists $MYSQLTEST_VARDIR/tmp/t4.res
216 --error 0
217 --file_exists $MYSQLTEST_VARDIR/tmp/t4.rej
218 --error 1
219 --file_exists $MYSQLTEST_VARDIR/tmp/t4.map
220 --error 0
221 --file_exists $MYSQLTEST_VARDIR/tmp/t4.stt
222
223 --echo # test --continue option with missing table
224
225 create table t5a (a int primary key) engine=ndb;
226 create table t5c like t5a;
227
228 write_file $MYSQLTEST_VARDIR/tmp/t5a.csv;
229 111
230 222
231 EOF
232
233 write_file $MYSQLTEST_VARDIR/tmp/t5b.csv;
234 111
235 111
236 EOF
237
238 write_file $MYSQLTEST_VARDIR/tmp/t5c.csv;
239 111
240 222
241 EOF
242
243 # files have '\n' terminator so add --csvopt=n
244
245 --error 1
246 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --log-level=1
247 --continue --csvopt=n
248 test
249 $MYSQLTEST_VARDIR/tmp/t5a.csv
250 $MYSQLTEST_VARDIR/tmp/t5b.csv
251 $MYSQLTEST_VARDIR/tmp/t5c.csv >> $NDB_TOOLS_OUTPUT 2>&1;
252 select count(*) from t5a;
253 select count(*) from t5c;
254
255 --echo # test --continue option with rejects
256
257 delete from t5a;
258 delete from t5c;
259 create table t5b like t5a;
260
261 --error 1
262 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --log-level=1
263 --continue --csvopt=n
264 test
265 $MYSQLTEST_VARDIR/tmp/t5a.csv
266 $MYSQLTEST_VARDIR/tmp/t5b.csv
267 $MYSQLTEST_VARDIR/tmp/t5c.csv >> $NDB_TOOLS_OUTPUT 2>&1;
268 select count(*) from t5a;
269 select count(*) from t5b;
270 select count(*) from t5c;
271
272 --echo # test quoting and escapes
273
274 create table t6 (
275 a int primary key,
276 b char(5) not null
277 ) engine=ndb;
278
279 create table t6ver like t6;
280
281 write_file $MYSQLTEST_VARDIR/tmp/t6.csv;
282 1,abc
283 2,"abc"
284 3,"a""bc"
285 4,\\\"\\
286 5,"\\\"\\"
287 6,\0\b\r\n\t
288 7,"\0\b\r\n\t"
289 EOF
290
291 --error 0
292 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --log-level=1
293 --csvopt=cqn
294 test $MYSQLTEST_VARDIR/tmp/t6.csv >> $NDB_TOOLS_OUTPUT 2>&1;
295
296 --disable_query_log
297 eval load data infile '$MYSQLTEST_VARDIR/tmp/t6.csv'
298 into table t6ver
299 fields terminated by ',' enclosed by '"' escaped by '\\\\'
300 lines terminated by $lt_unix;
301 --enable_query_log
302
303 select count(*) from t6 x, t6ver y
304 where x.a = y.a and x.b = y.b;
305
306 select a from t6
307 where b like '%"%'
308 order by a;
309
310 --echo # test CR-LF line terminator
311
312 perl;
313 use strict;
314 use Symbol;
315 my $lt = "\r\n";
316 my $vardir = $ENV{MYSQLTEST_VARDIR}
317 or die "need MYSQLTEST_VARDIR";
318 my $file = "$vardir/tmp/t7.csv";
319 my $fh = gensym();
320 open($fh, ">:raw", $file)
321 or die "$file: open for write failed: $!";
322 for my $i (1..1000) {
323 my $s;
324 if (int(rand(5)) == 0) {
325 $s = "\\N";
326 } else {
327 my $n = int(rand(20+1));
328 my $prevk = 0;
329 for (1..$n) {
330 my $c;
331 my $k = int(rand(256));
332 if ($k == 0) {
333 $c = int(rand(2)) ? chr($k) : int(rand(2)) ? "\\".chr($k) : "\\0";
334 } elsif ($k == 0x09) {
335 # TAB is field separator
336 $c = "\\t";
337 } elsif ($k == 0x5c) {
338 # backslash is escape
339 $c = "\\\\";
340 } elsif ($k == 0x0d) {
341 $c = chr($k);
342 } else {
343 if ($prevk == 0x0d && $k == 0xa) {
344 # avoid CR-LF
345 $k = 0x61;
346 }
347 $c = chr($k);
348 }
349 $s .= $c;
350 $prevk = $k;
351 }
352 }
353 print $fh $i, "\t", $s, $lt;
354 }
355 close($fh)
356 or die "$file: close after write failed: $!";
357 exit(0);
358 EOF
359
360 create table t7 (
361 a int primary key,
362 b varbinary(20)
363 ) engine=ndb;
364
365 create table t7ver like t7;
366
367 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --log-level=1
368 --csvopt=r --keep-state
369 test $MYSQLTEST_VARDIR/tmp/t7.csv >> $NDB_TOOLS_OUTPUT 2>&1;
370
371 --disable_query_log
372 eval load data infile '$MYSQLTEST_VARDIR/tmp/t7.csv'
373 into table t7ver
374 character set binary
375 lines terminated by $lt_windows;
376 --enable_query_log
377
378 select count(*) from t7 x, t7ver y
379 where x.a = y.a
380 and (x.b = y.b or (x.b is null and y.b is null));
381
382 --echo # test windows directory separator
383
384 # on windows mysqltest converts "/" to "\" in recognized paths
385 # on windows file i/o functions also convert "/" to "\"
386
387 --mkdir $MYSQLTEST_VARDIR/tmp/imp0
388 --copy_file $MYSQLTEST_VARDIR/tmp/t7.csv $MYSQLTEST_VARDIR/tmp/imp0/t7.csv
389
390 delete from t7;
391
392 if (!$IS_WINDOWS)
393 {
394 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp/imp0 --log-level=1
395 --csvopt=r --keep-state
396 test $MYSQLTEST_VARDIR/tmp/imp0/t7.csv >> $NDB_TOOLS_OUTPUT 2>&1;
397 }
398
399 if ($IS_WINDOWS)
400 {
401 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR\tmp\imp0 --log-level=1
402 --csvopt=r --keep-state
403 test $MYSQLTEST_VARDIR\tmp\imp0\t7.csv >> $NDB_TOOLS_OUTPUT 2>&1;
404 }
405
406 select count(*) from t7;
407
408 --list_files $MYSQLTEST_VARDIR/tmp/imp0
409
410 --echo # test NUL byte
411
412 perl;
413 use strict;
414 use Symbol;
415 my $lt = "\n";
416 my $vardir = $ENV{MYSQLTEST_VARDIR}
417 or die "need MYSQLTEST_VARDIR";
418 my $file = "$vardir/tmp/t8.csv";
419 my $fh = gensym();
420 open($fh, ">:raw", $file)
421 or die "$file: open for write failed: $!";
422 for my $i (1..1000) {
423 my $s;
424 if (int(rand(5)) == 0) {
425 $s = "\\N";
426 } else {
427 my $n = int(rand(20+1));
428 for (1..$n) {
429 my $c;
430 my $k = int(rand(256));
431 if ($k == 0) {
432 $c = int(rand(2)) ? chr($k) : int(rand(2)) ? "\\".chr($k) : "\\0";
433 } elsif ($k == 0x09) {
434 # TAB is field separator
435 $c == "\\t";
436 } elsif ($k == 0x0a) {
437 # newline is line terminator
438 $c = "\\n";
439 } elsif ($k == 0x5c) {
440 # backslash is escape
441 $c = "\\\\";
442 } else {
443 $c = chr($k);
444 }
445 $s .= $c;
446 }
447 }
448 print $fh $i, "\t", $s, $lt;
449 }
450 close($fh)
451 or die "$file: close after write failed: $!";
452 exit(0)
453 EOF
454
455 create table t8 (
456 a int primary key,
457 b varbinary(20)
458 ) engine=ndb;
459
460 create table t8ver like t8;
461
462 --disable_query_log
463 eval load data infile '$MYSQLTEST_VARDIR/tmp/t8.csv'
464 into table t8ver
465 character set binary
466 lines terminated by $lt_unix;
467 --enable_query_log
468
469 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --log-level=1
470 --csvopt=n --keep-state
471 test $MYSQLTEST_VARDIR/tmp/t8.csv >> $NDB_TOOLS_OUTPUT 2>&1;
472
473 select count(*) from t8 x, t8ver y
474 where x.a = y.a
475 and (x.b = y.b or (x.b is null and y.b is null));
476
477 --echo # test long field and line terminators
478
479 perl;
480 use strict;
481 use Symbol;
482 my $vardir = $ENV{MYSQLTEST_VARDIR}
483 or die "need MYSQLTEST_VARDIR";
484 my $file = "$vardir/tmp/t9.csv";
485 my $fh = gensym();
486 open($fh, ">:raw", $file)
487 or die "$file: open for write failed: $!";
488 my $d = '-';
489 print $fh 0, "::", "123", "::", "abc", "===_";
490 print $fh 1, "::", ":456=", "::", ":foo=", "===_";
491 print $fh 2, "::", "789===", "::", "bar===", "===_";
492 print $fh 3, "::", $d."123".$d, "::", "abc", "===_";
493 print $fh 4, "::", ":456=", "::", $d.":foo=".$d, "===_";
494 print $fh 5, "::", $d."789===".$d, "::", $d."bar===".$d, "===_";
495 close($fh)
496 or die "$file: close after write failed: $!";
497 exit(0);
498 EOF
499
500 create table t9 (
501 a int primary key,
502 b char(10),
503 c char(10)
504 ) engine=ndb;
505
506 create table t9ver like t9;
507
508 --disable_query_log
509 eval load data infile '$MYSQLTEST_VARDIR/tmp/t9.csv'
510 into table t9ver
511 fields terminated by '::' optionally enclosed by '-'
512 lines terminated by '===_';
513 --enable_query_log
514 select * from t9ver order by a;
515
516 if (!$IS_WINDOWS)
517 {
518 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --log-level=1
519 --keep-state
520 --fields-terminated-by='::'
521 --fields-optionally-enclosed-by='-'
522 --lines-terminated-by='===_'
523 test $MYSQLTEST_VARDIR/tmp/t9.csv >> $NDB_TOOLS_OUTPUT 2>&1;
524 }
525
526 if ($IS_WINDOWS)
527 {
528 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --log-level=1
529 --keep-state
530 --fields-terminated-by=::
531 --fields-optionally-enclosed-by=-
532 --lines-terminated-by====_
533 test $MYSQLTEST_VARDIR/tmp/t9.csv >> $NDB_TOOLS_OUTPUT 2>&1;
534 }
535
536 select count(*) from t9 x, t9ver y
537 where x.a = y.a
538 and (x.b = y.b or (x.b is null and y.b is null))
539 and (x.c = y.c or (x.c is null and y.c is null));
540
541 --echo # run backup
542 --source include/ndb_backup.inc
543
544 let BACKUP_SRC=$NDB_BACKUPS-$the_backup_id;
545 let BACKUP_DST=$MYSQLTEST_VARDIR/tmp/BACKUP-$the_backup_id;
546
547 perl;
548 use strict;
549 use Symbol;
550 use File::Copy;
551 my $src = $ENV{BACKUP_SRC};
552 my $dst = $ENV{BACKUP_DST};
553 mkdir $dst
554 or die "mkdir $dst failed: $!\n";
555 my $dh = gensym();
556 opendir($dh, $src)
557 or die "opendir $src failed: $!\n";
558 my $ent;
559 while (defined($ent = readdir($dh))) {
560 my $file = "$src/$ent";
561 if (-f $file) {
562 copy($file, $dst)
563 or die "copy $file to $dst failed: $!\n";
564 }
565 }
566 closedir($dh)
567 or "close dir $src failed: $!\n";
568 EOF
569
570 #### Bug 30434663 Trailing Field Separator
571 # (1) Valid input with trailing field terminator
572 --write_file $MYSQLTEST_VARDIR/tmp/t6.pipeterm.csv
573 31|xyz|
574 32|Rfc|
575 33||
576 34|
577 EOF
578
579 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
580 --fields-terminated-by="|" --csvopt=n
581 test $MYSQLTEST_VARDIR/tmp/t6.pipeterm.csv >> $NDB_TOOLS_OUTPUT 2>&1;
582
583 # (2) Trailing field sep, but too many fields
584 --remove_file $MYSQLTEST_VARDIR/tmp/t6.pipeterm.csv
585 --write_file $MYSQLTEST_VARDIR/tmp/t6.pipeterm.csv
586 33|Bach|JS|
587 EOF
588
589 --error 1
590 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
591 --fields-terminated-by="|" --csvopt=n
592 test $MYSQLTEST_VARDIR/tmp/t6.pipeterm.csv >> $NDB_TOOLS_OUTPUT 2>&1;
593
594 # (3) Final field is not empty
595 --remove_file $MYSQLTEST_VARDIR/tmp/t6.pipeterm.csv
596 --write_file $MYSQLTEST_VARDIR/tmp/t6.pipeterm.csv
597 34|Bach|JC
598 EOF
599
600 --error 1
601 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
602 --fields-terminated-by="|" --csvopt=n
603 test $MYSQLTEST_VARDIR/tmp/t6.pipeterm.csv >> $NDB_TOOLS_OUTPUT 2>&1;
604
605 SELECT * FROM t6 WHERE a > 30 order by a;
606
607 # Bug#30839144 NDB_IMPORT FAILED REQUIRE
608 # CSV parser error can lead to failed require in movetail().
609 # Repeating this error without a large data file requires using a very
610 # small pagesize and pagebuffer.
611
612 CREATE TABLE tpersons (
613 id INT NOT NULL PRIMARY KEY,
614 date_born DATE NOT NULL,
615 first_name VARCHAR(14) NOT NULL COLUMN_FORMAT FIXED,
616 last_name VARCHAR(16) NOT NULL COLUMN_FORMAT FIXED,
617 gender CHAR(1) NOT NULL,
618 date_died DATE NOT NULL
619 ) engine=ndb CHARACTER SET latin1;
620
621 --write_file $MYSQLTEST_VARDIR/tmp/tpersons.csv
622 1,"1760-03-02","Camille","Desmoulins","M","1794-04-05"
623 2,"1758-05-06","Maximilien","Robespierre","M","1794-07-28"
624 3,"1712-06-28","Jean-Jacques","Rousseau","M","1778-07-02"
625 4,"1713-10-05","Denis","Diderot","M","1784-07-31"
626 5,"1716-11-27","Sophie","Volland","F","1784-02-22");
627 6,"1759-10-26","Georges","Danton","M","1794-04-05"
628 7,"1759-05-28","William","Pitt","M","1806-01-23"
629 8,"1769-08-15","Napoleon","Boneparte","M","1821-05-05"
630 9,"1743-05-20","Toussaint","Louverture","M","1803-04-07"
631 10,"1758-09-20","Jean-Jacques","Dessalines","M","1806-10-17"
632 11,"1732-02-22","George","Washington","M","1799-12-14"
633 12,"1751-03-16","James","Madison","M","1836-06-28"
634 13,"1706-01-17","Benjamin","Franklin","M","1790-04-17"
635 14,"1620-01-01","Mary","Folger","F","1704-01-01"
636 15,"1819-08-01","Herman","Melville","M","1891-09-28"
637 16,"1804-07-04","Nathaniel","Hawthorne","M","1864-05-19"
638 17,"1799-11-29","Bronson","Alcott","M","1888-03-04"
639 18,"1810-05-23","Margaret","Fuller","F","1850-07-19"
640 19,"1805-06-22","Guiseppe","Mazzini","M","1872-03-10"
641 20,"1804-01-01","Giuditta","Sidoli","F","1871-03-28"
642 21,"1806-03-06","Elizabeth","Barrett","F","1861-06-29"
643 22,"1759-04-27","Mary","Wollstonecraft","F","1797-09-10"
644 23,"1797-08-30","Mary","Shelley","F","1851-02-01"
645 24,"1798-04-27","Claire","Clairmont","F","1879-03-19"
646 25,"1805-12-12","Frederic","Hedge","M","1890-08-21"
647 26,"1830-10-09","Annis","Wister","F","1908-11-15");
648 27,"1802-04-20","William","Furness","M","1896-01-30"
649 28,"1800-05-09","John","Brown","M","1859-12-02"
650 29,"1811-01-06","Charles","Sumner","M","1874-03-11"
651 30,"1764-04-01","Henry","Ware","M","1845-07-12"
652 EOF
653
654 --error 1
655 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
656 --csvopt=cqn --pagesize=256 --pagebuffer=512
657 test $MYSQLTEST_VARDIR/tmp/tpersons.csv >> $NDB_TOOLS_OUTPUT 2>&1;
658
659 # Look for the specific error message from ndb_import
660 --let $assert_file=$NDB_TOOLS_OUTPUT
661 --let $assert_only_after=import test.tpersons
662 --let $assert_text=Require CSV parser to fail with syntax error
663 --let $assert_select=syntax error, unexpected T_DATA
664 --let $assert_count=1
665 --source include/assert_grep.inc
666
667 create table t10 (
668 a int primary key,
669 b int not null,
670 c varchar(16) not null
671 ) engine=ndb CHARACTER SET latin1;
672
673 --write_file $MYSQLTEST_VARDIR/tmp/t10_no_match.csv
674 1,180,"Maximilien"
675 2,175,"Jean-Jacques"
676 3,168,"Denis"
677 4,175,"Georges"
678 5,174,"William"
679 EOF
680
681 --error 1
682 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
683 --csvopt=cn
684 test $MYSQLTEST_VARDIR/tmp/t10_no_match.csv >> $NDB_TOOLS_OUTPUT 2>&1;
685
686 # Look for the specific error message from ndb_import
687 --let $assert_file=$NDB_TOOLS_OUTPUT
688 --let $assert_text=No --table argument and csv filename does not match any table name
689 --let $assert_select=No such table existed
690 --let $assert_count=1
691 --source include/assert_grep.inc
692
693 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
694 --csvopt=cnq
695 test $MYSQLTEST_VARDIR/tmp/t10_no_match.csv --table=t10 >> $NDB_TOOLS_OUTPUT 2>&1;
696
697 delete from t10;
698
699 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
700 --csvopt=cnq
701 test $MYSQLTEST_VARDIR/tmp/t10_no_match.csv -t t10 >> $NDB_TOOLS_OUTPUT 2>&1;
702
703 create table t10ver like t10;
704
705 --disable_query_log
706 eval load data infile '$MYSQLTEST_VARDIR/tmp/t10_no_match.csv'
707 into table t10ver
708 fields terminated by ',' enclosed by '"'
709 lines terminated by $lt_unix;
710 --enable_query_log
711
712 select count(*) from t10 x, t10ver y
713 where x.a = y.a and x.b = y.b and x.c = y.c;
714
715 delete from t10;
716
717 --error 1
718 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
719 --csvopt=r
720 test $MYSQLTEST_VARDIR/tmp/t10_no_match.csv -t t10 >> $NDB_TOOLS_OUTPUT 2>&1;
721
722 --echo # check .rej file exists and match the input csv filename
723 --file_exists $MYSQLTEST_VARDIR/tmp/t10_no_match.rej
724
725 --echo # check .rej file matching the table name does not exists
726 --error 1
727 --file_exists $MYSQLTEST_VARDIR/tmp/t10.rej
728
729
730 delete from t10;
731
732 --echo # check order matters when using --csvopt and --fields-terminated-by
733 --error 1
734 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
735 --csvopt=cqn
736 --fields-terminated-by=':'
737 test $MYSQLTEST_VARDIR/tmp/t10_no_match.csv -t t10 >> $NDB_TOOLS_OUTPUT 2>&1;
738
739 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
740 --fields-terminated-by=':'
741 --csvopt=cqn
742 test $MYSQLTEST_VARDIR/tmp/t10_no_match.csv -t t10 >> $NDB_TOOLS_OUTPUT 2>&1;
743
744 # Verify that different ways of specify an empty or null column in a csv file
745 # for a single column table works.
746
747 create table t11 (
748 a varchar(16) null
749 ) engine=ndb CHARACTER SET latin1;
750
751 --write_file $MYSQLTEST_VARDIR/tmp/t11.csv
752
753 ,
754 ""
755 "",
756 \N
757 \N,
758 EOF
759
760 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
761 --csvopt=cnq
762 test $MYSQLTEST_VARDIR/tmp/t11.csv --table=t11 >> $NDB_TOOLS_OUTPUT 2>&1;
763
764 # 6 total 2 null and 4 empty
765 SELECT COUNT(*), SUM(a IS NULL), SUM(a = '') FROM t11 ORDER BY a;
766
767 delete from t11;
768
769 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
770 --csvopt=cnq
771 test $MYSQLTEST_VARDIR/tmp/t11.csv -t t11 >> $NDB_TOOLS_OUTPUT 2>&1;
772
773 create table t11ver like t11;
774
775 --disable_query_log
776 eval load data infile '$MYSQLTEST_VARDIR/tmp/t11.csv'
777 into table t11ver
778 fields terminated by ',' enclosed by '"'
779 lines terminated by $lt_unix;
780 --enable_query_log
781
782 # 6 total 2 null and 4 empty
783 SELECT COUNT(*), SUM(a IS NULL), SUM(a = '') FROM t11ver ORDER BY a;
784
785 # 20 total 2x2 null and 4x4 empty
786 select count(*) from t11 x, t11ver y
787 where ifnull(x.a,'NULL') = ifnull(y.a,'NULL');
788
789 delete from t11;
790
791 # Verify that the t11.csv produce the same result if one add a auto_increment
792 # primary key column that both ndb_import and load data will generate values
793 # for.
794
795 create table t12 (
796 a int auto_increment primary key,
797 b varchar(16) null
798 ) engine=ndb CHARACTER SET latin1;
799
800 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
801 --csvopt=cnq --missing-ai-column
802 test $MYSQLTEST_VARDIR/tmp/t11.csv --table=t12 >> $NDB_TOOLS_OUTPUT 2>&1;
803
804 # 6 total 2 null and 4 empty
805 SELECT COUNT(*), SUM(b IS NULL), SUM(b = '') FROM t12 ORDER BY b;
806
807 delete from t12;
808
809 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
810 --csvopt=cnq --missing-ai-column
811 test $MYSQLTEST_VARDIR/tmp/t11.csv -t t12 >> $NDB_TOOLS_OUTPUT 2>&1;
812
813 create table t12ver like t12;
814
815 --disable_query_log
816 eval load data infile '$MYSQLTEST_VARDIR/tmp/t11.csv'
817 into table t12ver
818 fields terminated by ',' enclosed by '"'
819 lines terminated by $lt_unix (b);
820 --enable_query_log
821
822 # 6 total 2 null and 4 empty
823 SELECT COUNT(*), SUM(b IS NULL), SUM(b = '') FROM t12ver ORDER BY b;
824
825 # 20 total 2x2 null and 4x4 empty
826 select count(*) from t12 x, t12ver y
827 where ifnull(x.b,'NULL') = ifnull(y.b,'NULL');
828
829 delete from t12;
830
831 # 20 total 2x2 null and 4x4 empty
832 select count(*) from t11ver x, t12ver y
833 where ifnull(x.a,'NULL') = ifnull(y.b,'NULL');
834
835 create table t13 (
836 a varchar(127) collate utf8mb4_unicode_ci,
837 b binary(255),
838 primary key(a,b)
839 ) engine=ndb;
840
841 --echo # errins-type=bug34917498, use small computeHash scratch buffer
842 error 1;
843 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
844 --input-type=csv --output-type=ndb --errins-type=bug34917498
845 test $MYSQLTEST_VARDIR/tmp/t1.csv -t t13 >> $NDB_TOOLS_OUTPUT 2>&1;
846
847 create table t14 (
848 a varchar(767) collate utf8mb4_unicode_ci primary key,
849 b int not null
850 ) engine=ndb;
851
852 --echo # errins-type=bug34917498, use small computeHash scratch buffer
853 error 1;
854 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1
855 --input-type=csv --output-type=ndb --errins-type=bug34917498
856 test $MYSQLTEST_VARDIR/tmp/t1.csv -t t14 >> $NDB_TOOLS_OUTPUT 2>&1;
857 --let $assert_text= Found expected error ndb-4278
858 --let $assert_select= error.ndb
859 --let $assert_match= ndb-4278
860 --let $assert_file= $NDB_TOOLS_OUTPUT
861 --let $assert_count=
862 --source include/assert_grep.inc
863
864 # END t11
865
866 # Check that it is not possible to import negative values into an unsigned int column.
867
868 --write_file $MYSQLTEST_VARDIR/tmp/t15.csv
869 1,1
870 7,7
871 2,-1
872 3,168
873 4,222
874 5,174
875 EOF
876
877 create table t15a (
878 a int primary key,
879 b tinyint unsigned
880 ) engine=ndb CHARACTER SET latin1;
881
882 create table t15b (
883 a int primary key,
884 b smallint unsigned
885 ) engine=ndb CHARACTER SET latin1;
886
887 create table t15c (
888 a int primary key,
889 b mediumint unsigned
890 ) engine=ndb CHARACTER SET latin1;
891
892 create table t15d (
893 a int primary key,
894 b int unsigned
895 ) engine=ndb CHARACTER SET latin1;
896
897 create table t15e (
898 a int primary key,
899 b bigint unsigned
900 ) engine=ndb CHARACTER SET latin1;
901
902 # Try to import negative values into a tinyint unsigned column
903 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
904 --csvopt=cn
905 --rejects=1
906 test $MYSQLTEST_VARDIR/tmp/t15.csv -t t15a > $NDB_TOOLS_OUTPUT 2>&1;
907
908 --let $assert_file=$NDB_TOOLS_OUTPUT
909 --let $assert_text=Rows containing negative values for t15a.b are rejected
910 --let $assert_select=rejected 1
911 --let $assert_match= rejected 1
912 --source include/assert_grep.inc
913
914 # Verify the rows from the csv file are imported correctly, except the -1
915 # given for the unsigned int column
916 select * from t15a order by a;
917
918 # Try to import negative values into a smallint unsigned column
919 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
920 --csvopt=cn
921 --rejects=1
922 test $MYSQLTEST_VARDIR/tmp/t15.csv -t t15b > $NDB_TOOLS_OUTPUT 2>&1;
923
924 --let $assert_file=$NDB_TOOLS_OUTPUT
925 --let $assert_text=Rows containing negative values for t15b.b are rejected
926 --let $assert_select=rejected 1
927 --let $assert_match= rejected 1
928 --source include/assert_grep.inc
929
930 # Verify the rows from the csv file are imported correctly, except the -1
931 # given for the unsigned int column
932 select * from t15b order by a;
933
934 # Try to import negative values into a mediumint unsigned column
935 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
936 --csvopt=cn
937 --rejects=1
938 test $MYSQLTEST_VARDIR/tmp/t15.csv -t t15c > $NDB_TOOLS_OUTPUT 2>&1;
939
940 --let $assert_file=$NDB_TOOLS_OUTPUT
941 --let $assert_text=Rows containing negative values for t15c.b are rejected
942 --let $assert_select=rejected 1
943 --let $assert_match= rejected 1
944 --source include/assert_grep.inc
945
946 # Verify the rows from the csv file are imported correctly, except the -1
947 # given for the unsigned int column
948 select * from t15c order by a;
949
950 # Try to import negative values into a int unsigned column
951 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
952 --csvopt=cn
953 --rejects=1
954 test $MYSQLTEST_VARDIR/tmp/t15.csv -t t15d > $NDB_TOOLS_OUTPUT 2>&1;
955
956 --let $assert_file=$NDB_TOOLS_OUTPUT
957 --let $assert_text=Rows containing negative values for t15d.b are rejected
958 --let $assert_select=rejected 1
959 --let $assert_match= rejected 1
960 --source include/assert_grep.inc
961
962 # Verify the rows from the csv file are imported correctly, except the -1
963 # given for the unsigned int column
964 select * from t15d order by a;
965
966 # Try to import negative values into a bigint unsigned column
967 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
968 --csvopt=cn
969 --rejects=1
970 test $MYSQLTEST_VARDIR/tmp/t15.csv -t t15e > $NDB_TOOLS_OUTPUT 2>&1;
971
972 --let $assert_file=$NDB_TOOLS_OUTPUT
973 --let $assert_text=Rows containing negative values for t15e.b are rejected
974 --let $assert_select=rejected 1
975 --let $assert_match= rejected 1
976 --source include/assert_grep.inc
977
978 # Verify the rows from the csv file are imported correctly, except the -1
979 # given for the unsigned int column
980 select * from t15e order by a;
981
982 drop table tpersons;
983
984 drop table t1, t1ver, t2, t2ver, t4, t5a, t5b, t5c,
985 t6, t6ver, t7, t7ver, t8, t8ver, t9, t9ver, t10, t10ver,
986 t11, t11ver, t12, t12ver, t13, t14, t15a, t15b, t15c, t15d, t15e;
987
988
989 --source suite/ndb/include/backup_restore_cleanup.inc
990 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.csv
991 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.map
992 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.rej
993 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.res
994 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.sto
995 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.stt
996 --force-rmdir $BACKUP_DST
997 --force-rmdir $MYSQLTEST_VARDIR/tmp/imp0
998 --remove_file $NDB_TOOLS_OUTPUT