--source include/have_ndb.inc --echo # ndb_import tests with auto inc columns: set ndb_autoincrement_prefetch_sz = 1; --let IS_WINDOWS= `select convert(@@version_compile_os using latin1) in ('Win32', 'Win64', 'Windows')` # LOAD DATA line terminators --let $lt_unix='\n' --let $lt_windows='\r\n' if (!$IS_WINDOWS) { --let $lt_default=$lt_unix } if ($IS_WINDOWS) { --let $lt_default=$lt_windows } --write_file $MYSQLTEST_VARDIR/tmp/t10.csv -128|-12888|-20000 0|888|1500 \N|3288|30000 |4388|40000 120|12088|120000 \N|5588|50000 |6688|60000 73|7788|70000 EOF create table t10 ( a tinyint not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; drop table t10, t10ver; create table t10 ( a tinyint unsigned not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" --rejects=1 test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; drop table t10, t10ver; create table t10 ( a smallint not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; drop table t10, t10ver; create table t10 ( a smallint unsigned not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" --rejects=1 test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; drop table t10, t10ver; create table t10 ( a mediumint not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; drop table t10, t10ver; create table t10 ( a mediumint unsigned not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" --rejects=1 test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; drop table t10, t10ver; create table t10 ( a int not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; drop table t10, t10ver; create table t10 ( a int unsigned not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" --rejects=1 test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; drop table t10, t10ver; create table t10 ( a bigint not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; drop table t10, t10ver; create table t10 ( a bigint unsigned not null auto_increment primary key, b int, c bigint ) engine ndb; create table t10ver like t10; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" --rejects=1 test $MYSQLTEST_VARDIR/tmp/t10.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t10.csv' into table t10ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t10"; select count(*) from t10ver; select count(*) from t10; select count(*) from t10 x, t10ver y where x.b = y.b; --echo # Simple hidden-pk test perl; use strict; use Symbol; my $lt = !$ENV{IS_WINDOWS} ? "\n" : "\r\n"; my $vardir = $ENV{MYSQLTEST_VARDIR} or die "need MYSQLTEST_VARDIR"; my $file = "$vardir/tmp/t3.csv"; my $fh = gensym(); open($fh, ">:raw", $file) or die "$file: open for write failed: $!"; for my $i (1..1000) { print $fh 10*$i, $lt; } close($fh) or die "$file: close after write failed: $!"; exit(0); EOF create table t3 ( b int not null, # unique key would turn into pk key bx (b) ) engine=ndb; create table t3ver like t3; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 test $MYSQLTEST_VARDIR/tmp/t3.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data infile '$MYSQLTEST_VARDIR/tmp/t3.csv' into table t3ver lines terminated by $lt_default; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t3ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t3"; select count(*) from t3ver; select count(*) from t3; select count(*) from t3 x, t3ver y where x.b = y.b; --echo Test auto inc PK works when it is the middle column --write_file $MYSQLTEST_VARDIR/tmp/t12.csv 1188|\N|10000 1288||20000 1388|56|50000 1488|\N|30000 1588||40000 1688|89|6000 EOF create table t12 ( a smallint, b mediumint not null auto_increment primary key, c int ) engine ndb; create table t12ver like t12; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" test $MYSQLTEST_VARDIR/tmp/t12.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t12.csv' into table t12ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t12ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t12"; select count(*) from t12ver; select count(*) from t12; select count(*) from t12 x, t12ver y where x.a = y.a; --echo Test auto inc PK works when it is the last column --write_file $MYSQLTEST_VARDIR/tmp/t13.csv a|b|c 1288|20000|\N 1388|30000| 1488|40000|\N 1588|56|50000 1688|50000| 1788|60000|\N 1888|89|6000 EOF create table t13 ( a int, b int, c smallint unsigned not null auto_increment primary key ) engine ndb; create table t13ver like t13; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" --ignore-lines=1 test $MYSQLTEST_VARDIR/tmp/t13.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t13.csv' into table t13ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix IGNORE 1 LINES; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t13ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t13"; select count(*) from t13ver; select count(*) from t13; select count(*) from t13 x, t13ver y where x.a = y.a; --echo Test when all input rows have user-provided auto inc PK. --write_file $MYSQLTEST_VARDIR/tmp/t14.csv a|b 1288|20000 1388|30000 1488|40000 1588|56 1688|50000 1788|60000 1888|89 EOF create table t14 ( a mediumint unsigned not null auto_increment primary key, b int ) engine ndb; create table t14ver like t14; exec $NDB_IMPORT --state-dir=$MYSQLTEST_VARDIR/tmp --keep-state --log-level=1 --csvopt=n --fields-terminated-by="|" --ignore-lines=1 test $MYSQLTEST_VARDIR/tmp/t14.csv >> $NDB_TOOLS_OUTPUT 2>&1; --disable_query_log eval load data local infile '$MYSQLTEST_VARDIR/tmp/t14.csv' into table t14ver FIELDS TERMINATED BY '|' lines terminated by $lt_unix IGNORE 1 LINES; --enable_query_log select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t14ver"; select AUTO_INCREMENT from information_schema.tables where table_schema="test" and table_name="t14"; select * from t14ver order by a; select * from t14 order by a; select count(*) from t14ver; select count(*) from t14; select count(*) from t14 x, t14ver y where x.b = y.b; drop table t10, t10ver, t3, t3ver, t12, t12ver, t13, t13ver, t14, t14ver; --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.csv --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.map --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.rej --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.res --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.sto --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t*.stt --remove_file $NDB_TOOLS_OUTPUT