Debian Codegraph @

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

raw
1 --source include/have_ndb.inc
2
3 --echo # ndb_import tests with auto inc columns:
4
5 set ndb_autoincrement_prefetch_sz = 1;
6
7 --let IS_WINDOWS= `select convert(@@version_compile_os using latin1) in ('Win32', 'Win64', 'Windows')`
8
9 # LOAD DATA line terminators
10 --let $lt_unix='\n'
11 --let $lt_windows='\r\n'
12 if (!$IS_WINDOWS) {
13 --let $lt_default=$lt_unix
14 }
15 if ($IS_WINDOWS) {
16 --let $lt_default=$lt_windows
17 }
18
19 --write_file $MYSQLTEST_VARDIR/tmp/t10.csv
20 -128|-12888|-20000
21 0|888|1500
22 \N|3288|30000
23 |4388|40000
24 120|12088|120000
25 \N|5588|50000
26 |6688|60000
27 73|7788|70000
28 EOF
29
30 create table t10 (
31 a tinyint not null auto_increment primary key,
32 b int,
33 c bigint
34 ) engine ndb;
35
36 create table t10ver like t10;
37
38 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
39 --keep-state --log-level=1 --csvopt=n
40 --fields-terminated-by="|"
41 test
42 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
43
44
45 --disable_query_log
46 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
47 FIELDS TERMINATED BY '|'
48 lines terminated by $lt_unix;
49 --enable_query_log
50
51 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
52 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
53 select count(*) from t10ver;
54 select count(*) from t10;
55
56 select count(*) from t10 x, t10ver y
57 where x.b = y.b;
58
59 drop table t10, t10ver;
60
61
62 create table t10 (
63 a tinyint unsigned not null auto_increment primary key,
64 b int,
65 c bigint
66 ) engine ndb;
67
68 create table t10ver like t10;
69
70 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
71 --keep-state --log-level=1 --csvopt=n
72 --fields-terminated-by="|" --rejects=1
73 test
74 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
75
76
77 --disable_query_log
78 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
79 FIELDS TERMINATED BY '|'
80 lines terminated by $lt_unix;
81 --enable_query_log
82
83 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
84 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
85 select count(*) from t10ver;
86 select count(*) from t10;
87
88 select count(*) from t10 x, t10ver y
89 where x.b = y.b;
90
91 drop table t10, t10ver;
92
93
94 create table t10 (
95 a smallint not null auto_increment primary key,
96 b int,
97 c bigint
98 ) engine ndb;
99
100 create table t10ver like t10;
101
102 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
103 --keep-state --log-level=1 --csvopt=n
104 --fields-terminated-by="|"
105 test
106 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
107
108
109 --disable_query_log
110 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
111 FIELDS TERMINATED BY '|'
112 lines terminated by $lt_unix;
113 --enable_query_log
114
115 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
116 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
117 select count(*) from t10ver;
118 select count(*) from t10;
119
120 select count(*) from t10 x, t10ver y
121 where x.b = y.b;
122
123 drop table t10, t10ver;
124
125
126 create table t10 (
127 a smallint unsigned not null auto_increment primary key,
128 b int,
129 c bigint
130 ) engine ndb;
131
132 create table t10ver like t10;
133
134 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
135 --keep-state --log-level=1 --csvopt=n
136 --fields-terminated-by="|" --rejects=1
137 test
138 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
139
140
141 --disable_query_log
142 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
143 FIELDS TERMINATED BY '|'
144 lines terminated by $lt_unix;
145 --enable_query_log
146
147 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
148 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
149 select count(*) from t10ver;
150 select count(*) from t10;
151
152 select count(*) from t10 x, t10ver y
153 where x.b = y.b;
154
155 drop table t10, t10ver;
156
157
158 create table t10 (
159 a mediumint not null auto_increment primary key,
160 b int,
161 c bigint
162 ) engine ndb;
163
164 create table t10ver like t10;
165
166 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
167 --keep-state --log-level=1 --csvopt=n
168 --fields-terminated-by="|"
169 test
170 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
171
172
173 --disable_query_log
174 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
175 FIELDS TERMINATED BY '|'
176 lines terminated by $lt_unix;
177 --enable_query_log
178
179 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
180 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
181 select count(*) from t10ver;
182 select count(*) from t10;
183
184 select count(*) from t10 x, t10ver y
185 where x.b = y.b;
186
187 drop table t10, t10ver;
188
189
190 create table t10 (
191 a mediumint unsigned not null auto_increment primary key,
192 b int,
193 c bigint
194 ) engine ndb;
195
196 create table t10ver like t10;
197
198 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
199 --keep-state --log-level=1 --csvopt=n
200 --fields-terminated-by="|" --rejects=1
201 test
202 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
203
204
205 --disable_query_log
206 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
207 FIELDS TERMINATED BY '|'
208 lines terminated by $lt_unix;
209 --enable_query_log
210
211 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
212 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
213 select count(*) from t10ver;
214 select count(*) from t10;
215
216 select count(*) from t10 x, t10ver y
217 where x.b = y.b;
218
219 drop table t10, t10ver;
220
221
222 create table t10 (
223 a int not null auto_increment primary key,
224 b int,
225 c bigint
226 ) engine ndb;
227
228 create table t10ver like t10;
229
230 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
231 --keep-state --log-level=1 --csvopt=n
232 --fields-terminated-by="|"
233 test
234 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
235
236
237 --disable_query_log
238 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
239 FIELDS TERMINATED BY '|'
240 lines terminated by $lt_unix;
241 --enable_query_log
242
243 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
244 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
245 select count(*) from t10ver;
246 select count(*) from t10;
247
248 select count(*) from t10 x, t10ver y
249 where x.b = y.b;
250
251 drop table t10, t10ver;
252
253
254 create table t10 (
255 a int unsigned not null auto_increment primary key,
256 b int,
257 c bigint
258 ) engine ndb;
259
260 create table t10ver like t10;
261
262 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
263 --keep-state --log-level=1 --csvopt=n
264 --fields-terminated-by="|" --rejects=1
265 test
266 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
267
268
269 --disable_query_log
270 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
271 FIELDS TERMINATED BY '|'
272 lines terminated by $lt_unix;
273 --enable_query_log
274
275 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
276 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
277 select count(*) from t10ver;
278 select count(*) from t10;
279
280 select count(*) from t10 x, t10ver y
281 where x.b = y.b;
282
283 drop table t10, t10ver;
284
285
286 create table t10 (
287 a bigint not null auto_increment primary key,
288 b int,
289 c bigint
290 ) engine ndb;
291
292 create table t10ver like t10;
293
294 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
295 --keep-state --log-level=1 --csvopt=n
296 --fields-terminated-by="|"
297 test
298 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
299
300
301 --disable_query_log
302 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
303 FIELDS TERMINATED BY '|'
304 lines terminated by $lt_unix;
305 --enable_query_log
306
307 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
308 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
309 select count(*) from t10ver;
310 select count(*) from t10;
311
312 select count(*) from t10 x, t10ver y
313 where x.b = y.b;
314
315 drop table t10, t10ver;
316
317
318 create table t10 (
319 a bigint unsigned not null auto_increment primary key,
320 b int,
321 c bigint
322 ) engine ndb;
323
324 create table t10ver like t10;
325
326 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
327 --keep-state --log-level=1 --csvopt=n
328 --fields-terminated-by="|" --rejects=1
329 test
330 $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1;
331
332
333 --disable_query_log
334 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver
335 FIELDS TERMINATED BY '|'
336 lines terminated by $lt_unix;
337 --enable_query_log
338
339 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver";
340 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10";
341 select count(*) from t10ver;
342 select count(*) from t10;
343
344 select count(*) from t10 x, t10ver y
345 where x.b = y.b;
346
347
348 --echo # Simple hidden-pk test
349
350 perl;
351 use strict;
352 use Symbol;
353 my $lt = !$ENV{IS_WINDOWS} ? "\n" : "\r\n";
354 my $vardir = $ENV{MYSQLTEST_VARDIR}
355 or die "need MYSQLTEST_VARDIR";
356 my $file = "$vardir/tmp/t3.csv";
357 my $fh = gensym();
358 open($fh, ">:raw", $file)
359 or die "$file: open for write failed: $!";
360 for my $i (1..1000) {
361 print $fh 10*$i, $lt;
362 }
363 close($fh)
364 or die "$file: close after write failed: $!";
365 exit(0);
366 EOF
367
368 create table t3 (
369 b int not null,
370 # unique key would turn into pk
371 key bx (b)
372 ) engine=ndb;
373
374 create table t3ver like t3;
375
376 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state
377 --log-level=1
378 test $MYSQLTEST_VARDIR/tmp/t3.csv >> $NDB_TOOLS_OUTPUT 2>&1;
379
380 --disable_query_log
381 eval load data infile '$MYSQLTEST_VARDIR/tmp/t3.csv'
382 into table t3ver
383 lines terminated by $lt_default;
384 --enable_query_log
385
386 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t3ver";
387 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t3";
388 select count(*) from t3ver;
389 select count(*) from t3;
390
391 select count(*) from t3 x, t3ver y
392 where x.b = y.b;
393
394
395 --echo Test auto inc PK works when it is the middle column
396
397 --write_file $MYSQLTEST_VARDIR/tmp/t12.csv
398 1188|\N|10000
399 1288||20000
400 1388|56|50000
401 1488|\N|30000
402 1588||40000
403 1688|89|6000
404 EOF
405
406 create table t12 (
407 a smallint,
408 b mediumint not null auto_increment primary key,
409 c int
410 ) engine ndb;
411
412 create table t12ver like t12;
413
414 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
415 --keep-state --log-level=1 --csvopt=n
416 --fields-terminated-by="|"
417 test
418 $MYSQLTEST_VARDIR/tmp/t12.csv >> $NDB_TOOLS_OUTPUT 2>&1;
419
420
421 --disable_query_log
422 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t12.csv' into table t12ver
423 FIELDS TERMINATED BY '|'
424 lines terminated by $lt_unix;
425 --enable_query_log
426
427 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t12ver";
428 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t12";
429 select count(*) from t12ver;
430 select count(*) from t12;
431
432 select count(*) from t12 x, t12ver y
433 where x.a = y.a;
434
435
436 --echo Test auto inc PK works when it is the last column
437
438 --write_file $MYSQLTEST_VARDIR/tmp/t13.csv
439 a|b|c
440 1288|20000|\N
441 1388|30000|
442 1488|40000|\N
443 1588|56|50000
444 1688|50000|
445 1788|60000|\N
446 1888|89|6000
447 EOF
448
449 create table t13 (
450 a int,
451 b int,
452 c smallint unsigned not null auto_increment primary key
453 ) engine ndb;
454
455 create table t13ver like t13;
456
457 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
458 --keep-state --log-level=1 --csvopt=n
459 --fields-terminated-by="|" --ignore-lines=1
460 test
461 $MYSQLTEST_VARDIR/tmp/t13.csv >> $NDB_TOOLS_OUTPUT 2>&1;
462
463 --disable_query_log
464 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t13.csv' into table t13ver
465 FIELDS TERMINATED BY '|'
466 lines terminated by $lt_unix
467 IGNORE 1 LINES;
468 --enable_query_log
469
470 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t13ver";
471 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t13";
472 select count(*) from t13ver;
473 select count(*) from t13;
474
475 select count(*) from t13 x, t13ver y
476 where x.a = y.a;
477
478
479 --echo Test when all input rows have user-provided auto inc PK.
480
481 --write_file $MYSQLTEST_VARDIR/tmp/t14.csv
482 a|b
483 1288|20000
484 1388|30000
485 1488|40000
486 1588|56
487 1688|50000
488 1788|60000
489 1888|89
490 EOF
491
492 create table t14 (
493 a mediumint unsigned not null auto_increment primary key,
494 b int
495 ) engine ndb;
496
497 create table t14ver like t14;
498
499 exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp
500 --keep-state --log-level=1 --csvopt=n
501 --fields-terminated-by="|" --ignore-lines=1
502 test
503 $MYSQLTEST_VARDIR/tmp/t14.csv >> $NDB_TOOLS_OUTPUT 2>&1;
504
505 --disable_query_log
506 eval load data local infile '$MYSQLTEST_VARDIR/tmp/t14.csv' into table t14ver
507 FIELDS TERMINATED BY '|'
508 lines terminated by $lt_unix
509 IGNORE 1 LINES;
510 --enable_query_log
511
512 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t14ver";
513 select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t14";
514 select * from t14ver order by a;
515 select * from t14 order by a;
516 select count(*) from t14ver;
517 select count(*) from t14;
518
519 select count(*) from t14 x, t14ver y
520 where x.b = y.b;
521
522 drop table t10, t10ver, t3, t3ver, t12, t12ver, t13, t13ver, t14, t14ver;
523
524 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.csv
525 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.map
526 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.rej
527 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.res
528 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.sto
529 --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.stt
530 --remove_file $NDB_TOOLS_OUTPUT