drop table if exists t1, t2; create table t1 (a date, b date, c date not null, d date); load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ','; Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 Warning 1265 Data truncated for column 'd' at row 1 Warning 1265 Data truncated for column 'a' at row 2 Warning 1265 Data truncated for column 'b' at row 2 Warning 1265 Data truncated for column 'd' at row 2 load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES; SELECT * from t1; a b c d 0000-00-00 NULL 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00 2003-03-03 2003-03-03 2003-03-03 NULL 2003-03-03 2003-03-03 2003-03-03 NULL truncate table t1; load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d); Warnings: Warning 1265 Data truncated for column 'c' at row 1 Warning 1265 Data truncated for column 'd' at row 1 Warning 1265 Data truncated for column 'b' at row 2 Warning 1265 Data truncated for column 'd' at row 2 SELECT * from t1; a b c d NULL NULL 0000-00-00 0000-00-00 NULL 0000-00-00 0000-00-00 0000-00-00 NULL 2003-03-03 2003-03-03 NULL drop table t1; create table t1 (a text, b text); load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by ''''; Warnings: Warning 1261 Row 3 doesn't contain data for all columns select concat('|',a,'|'), concat('|',b,'|') from t1; concat('|',a,'|') concat('|',b,'|') |Field A| |Field B| |Field 1| |Field 2' Field 3,'Field 4| |Field 5' ,'Field 6| NULL |Field 6| | 'Field 7'| drop table t1; create table t1 (a int, b char(10)); load data infile '../std_data_ln/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines; Warnings: Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3 Warning 1262 Row 3 was truncated; it contained more data than there were input columns Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 5 Warning 1262 Row 5 was truncated; it contained more data than there were input columns select * from t1; a b 1 row 1 2 row 2 0 1234567890 3 row 3 0 1234567890 truncate table t1; load data infile '../std_data_ln/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; Warnings: Warning 1366 Incorrect integer value: ' ' for column 'a' at row 4 Warning 1261 Row 4 doesn't contain data for all columns select * from t1; a b 1 row 1 2 row 2 3 row 3 0 drop table t1; SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=NO_AUTO_VALUE_ON_ZERO; create table t1(id integer not null auto_increment primary key); insert into t1 values(0); select * from t1; id 0 select * from t1; id 0 SET @@SQL_MODE=@OLD_SQL_MODE; drop table t1; create table t1 (a varchar(20), b varchar(20)); load data infile '../std_data_ln/loaddata_dq.dat' into table t1 fields terminated by ',' enclosed by '"' escaped by '"' (a,b); select * from t1; a b field1 field2 a"b cd"ef a"b c"d"e drop table t1; CREATE TABLE t1 ( id INT AUTO_INCREMENT PRIMARY KEY, c1 VARCHAR(255) ); CREATE TABLE t2 ( id INT, c2 VARCHAR(255) ); INSERT INTO t1 (c1) VALUES ('r'), ('rr'), ('rrr'), ('rrrr'), ('.r'), ('.rr'), ('.rrr'), ('.rrrr'), ('r.'), ('rr.'), ('rrr.'), ('rrrr.'), ('.r.'), ('.rr.'), ('.rrr.'), ('.rrrr.'); SELECT * FROM t1; id c1 1 r 2 rr 3 rrr 4 rrrr 5 .r 6 .rr 7 .rrr 8 .rrrr 9 r. 10 rr. 11 rrr. 12 rrrr. 13 .r. 14 .rr. 15 .rrr. 16 .rrrr. SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY 'r' FROM t1; r1r rrrr r2r rrrrrr r3r rrrrrrrr r4r rrrrrrrrrr r5r r.rrr r6r r.rrrrr r7r r.rrrrrrr r8r r.rrrrrrrrr r9r rrr.r r10r rrrrr.r r11r rrrrrrr.r r12r rrrrrrrrr.r r13r r.rr.r r14r r.rrrr.r r15r r.rrrrrr.r r16r r.rrrrrrrr.r LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t2 FIELDS ENCLOSED BY 'r'; SELECT t1.id, c1, c2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE c1 != c2; id c1 c2 SELECT t1.id, c1, c2 FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE c1 != c2; id c1 c2 DROP TABLE t1,t2; create table t1 (a int default 100, b int, c varchar(60)); load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=concat("b=",@b); select * from t1; a b c NULL 20 b=10 NULL 25 b=15 truncate table t1; load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (a, @b) set c= if(a is null,"oops",a); select * from t1; a b c NULL NULL oops NULL NULL oops truncate table t1; set @c:=123; load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, b) set c= if(@a is null,@c,b); select * from t1; a b c 100 10 123 100 15 123 load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, @b); select * from t1; a b c 100 10 123 100 15 123 100 NULL NULL 100 NULL NULL select @a, @b; @a @b NULL 15 truncate table t1; load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow"; select * from t1; a b c 1 2 Wow 3 4 Wow 5 6 Wow truncate table t1; load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c=concat(a,"+",b,"+",@c,"+",b,"+",if(c is null,"NIL",c)); select * from t1; a b c 1 2 1+2+123+2+NIL 3 4 3+4+123+4+NIL 5 6 5+6+123+6+NIL load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b); ERROR HY000: Can't load value from file with fixed size rows to variable create table t2 (num int primary key, str varchar(10)); insert into t2 values (10,'Ten'), (15,'Fifteen'); truncate table t1; load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@dummy,@n) set a= @n, c= (select str from t2 where num=@n); select * from t1; a b c 10 NULL Ten 15 NULL Fifteen show variables like "secure_file_pri%"; Variable_name Value secure_file_priv MYSQLTEST_VARDIR/ select @@secure_file_priv; @@secure_file_priv MYSQLTEST_VARDIR/ set @@secure_file_priv= 0; ERROR HY000: Variable 'secure_file_priv' is a read only variable truncate table t1; load data infile 'MYSQL_TEST_DIR/t/loaddata.test' into table t1; ERROR HY000: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement select * from t1; a b c select load_file("MYSQL_TEST_DIR/t/loaddata.test"); load_file("MYSQL_TEST_DIR/t/loaddata.test") NULL drop table t1, t2; create table t1(f1 int); insert into t1 values(1),(null); create table t2(f2 int auto_increment primary key); select * from t2; f2 1 2 SET @@SQL_MODE=@OLD_SQL_MODE; drop table t1,t2; create table t1(f1 int, f2 timestamp not null default current_timestamp); create table t2(f1 int); insert into t2 values(1),(2); Warnings: Warning 1261 Row 1 doesn't contain data for all columns Warning 1261 Row 2 doesn't contain data for all columns select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1; f1 1 2 delete from t1; Warnings: Warning 1261 Row 1 doesn't contain data for all columns Warning 1261 Row 2 doesn't contain data for all columns select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1; f1 1 2 drop table t1,t2; CREATE TABLE t1 (c1 INT, c2 TIMESTAMP, c3 REAL, c4 DOUBLE); INSERT INTO t1 (c1, c2, c3, c4) VALUES (10, '1970-02-01 01:02:03', 1.1E-100, 1.1E+100); SELECT * FROM t1; c1 c2 c3 c4 10 1970-02-01 01:02:03 1.1e-100 1.1e+100 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1; -10- -1970\-02\-01 01:02:03- -1.1e\-100- -1.1e+100- EOF TRUNCATE t1; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t1 FIELDS ENCLOSED BY '-'; SELECT * FROM t1; c1 c2 c3 c4 10 1970-02-01 01:02:03 1.1e-100 1.1e+100 DROP TABLE t1; # -- # -- Bug#35469: server crash with LOAD DATA INFILE to a VIEW. # -- DROP TABLE IF EXISTS t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v2; DROP VIEW IF EXISTS v3; CREATE TABLE t1(c1 INT, c2 VARCHAR(255)); CREATE VIEW v1 AS SELECT * FROM t1; CREATE VIEW v2 AS SELECT 1 + 2 AS c0, c1, c2 FROM t1; CREATE VIEW v3 AS SELECT 1 AS d1, 2 AS d2; LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v1 FIELDS ESCAPED BY '\\' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (c1, c2); SELECT * FROM t1; c1 c2 1 "string1" 2 "string2" 3 "string3" SELECT * FROM v1; c1 c2 1 "string1" 2 "string2" 3 "string3" DELETE FROM t1; LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v2 FIELDS ESCAPED BY '\\' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (c1, c2); SELECT * FROM t1; c1 c2 1 "string1" 2 "string2" 3 "string3" SELECT * FROM v2; c0 c1 c2 3 1 "string1" 3 2 "string2" 3 3 "string3" DELETE FROM t1; LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v2 FIELDS ESCAPED BY '\\' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (c0, c2); ERROR HY000: Invalid column reference (v2.c0) in LOAD DATA LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v3 FIELDS ESCAPED BY '\\' TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (d1, d2); ERROR HY000: The target table v3 of the LOAD is not updatable DROP TABLE t1; DROP VIEW v1; DROP VIEW v2; DROP VIEW v3; # -- End of Bug#35469. Bug#37114 SET SESSION character_set_client=latin1; SET SESSION character_set_server=latin1; SET SESSION character_set_connection=latin1; SET @OLD_SQL_MODE=@@SESSION.SQL_MODE; test LOAD DATA INFILE SET sql_mode = ''; SELECT '1 \\aa\n' INTO DUMPFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt'; CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM; SET sql_mode = 'NO_BACKSLASH_ESCAPES'; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' '; SELECT * FROM t1; id val1 1 \aa SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1; SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS TERMINATED BY ' ' FROM t1; INSERT INTO t1 (id, val1) VALUES (1, '\aa'); SELECT * FROM t1; id val1 1 \aa 1 \aa SET sql_mode=''; INSERT INTO t1 (id, val1) VALUES (1, '\aa'); SELECT * FROM t1; id val1 1 \aa 1 \aa 1 aa DROP TABLE t1; test SELECT INTO OUTFILE CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4)); CREATE TABLE t2 LIKE t1; SET sql_mode = ''; INSERT INTO t1 (id, val1) VALUES (5, '\ttab'); INSERT INTO t1 (id, val1) VALUES (4, '\\r'); SET sql_mode = 'NO_BACKSLASH_ESCAPES'; INSERT INTO t1 (id, val1) VALUES (3, '\tx'); 1.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' '; SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; t id val1 hex(val1) before 3 \tx 5C7478 after 3 \tx 5C7478 before 4 \r 5C72 after 4 \r 5C72 before 5 tab 09746162 after 5 tab 09746162 TRUNCATE t2; SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") 3 \tx 4 \r 5 tab 1.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '\' TERMINATED BY ' ' FROM t1 ORDER BY id; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '\' TERMINATED BY ' '; SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; t id val1 hex(val1) before 3 \tx 5C7478 after 3 \tx 5C7478 before 4 \r 5C72 after 4 \r 5C72 before 5 tab 09746162 after 5 tab 09746162 TRUNCATE t2; SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") 3 \\tx 4 \\r 5 tab SET sql_mode = ''; 2.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' '; SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; t id val1 hex(val1) before 3 \tx 5C7478 after 3 \tx 5C7478 before 4 \r 5C72 after 4 \r 5C72 before 5 tab 09746162 after 5 tab 09746162 TRUNCATE t2; SET sql_mode = 'NO_BACKSLASH_ESCAPES'; SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") 3 \\tx 4 \\r 5 tab SET sql_mode = ''; 2.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 ORDER BY id; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '' TERMINATED BY ' '; SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; t id val1 hex(val1) before 3 \tx 5C7478 after 3 \tx 5C7478 before 4 \r 5C72 after 4 \r 5C72 before 5 tab 09746162 after 5 tab 09746162 TRUNCATE t2; SET sql_mode = 'NO_BACKSLASH_ESCAPES'; SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") 3 \tx 4 \r 5 tab set session sql_mode=@OLD_SQL_MODE; DROP TABLE t1,t2; End of 5.0 tests