1# 2# Some simple test of load data 3# 4--source include/have_utf8.inc 5 6--disable_warnings 7drop table if exists t1, t2; 8--enable_warnings 9 10create table t1 (a date, b date, c date not null, d date); 11load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ','; 12load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES; 13SELECT * from t1; 14truncate table t1; 15 16load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d); 17SELECT * from t1; 18drop table t1; 19 20create table t1 (a text, b text); 21load data infile '../../std_data/loaddata2.dat' into table t1 fields terminated by ',' enclosed by ''''; 22select concat('|',a,'|'), concat('|',b,'|') from t1; 23drop table t1; 24 25create table t1 (a int, b char(10)); 26load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines; 27select * from t1; 28truncate table t1; 29load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; 30 31# The empty line last comes from the end line field in the file 32select * from t1; 33drop table t1; 34 35# 36# Bug #12053 LOAD DATA INFILE ignores NO_AUTO_VALUE_ON_ZERO setting 37# 38SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=NO_AUTO_VALUE_ON_ZERO; 39create table t1(id integer not null auto_increment primary key); 40insert into t1 values(0); 41disable_query_log; 42eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1' from t1; 43delete from t1; 44eval load data infile '$MYSQLTEST_VARDIR/tmp/t1' into table t1; 45enable_query_log; 46select * from t1; 47remove_file $MYSQLTEST_VARDIR/tmp/t1; 48 49disable_query_log; 50eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1' 51FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n' 52FROM t1; 53delete from t1; 54eval load data infile '$MYSQLTEST_VARDIR/tmp/t1' into table t1 55FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n'; 56enable_query_log; 57select * from t1; 58remove_file $MYSQLTEST_VARDIR/tmp/t1; 59SET @@SQL_MODE=@OLD_SQL_MODE; 60drop table t1; 61 62# 63# Bug #11203: LOAD DATA does not accept same characters for ESCAPED and 64# ENCLOSED 65# 66create table t1 (a varchar(20), b varchar(20)); 67load data infile '../../std_data/loaddata_dq.dat' into table t1 fields terminated by ',' enclosed by '"' escaped by '"' (a,b); 68select * from t1; 69drop table t1; 70 71# 72# Bug #29294 SELECT INTO OUTFILE/LOAD DATA INFILE with special 73# characters in the FIELDS ENCLOSED BY clause 74# 75 76CREATE TABLE t1 ( 77 id INT AUTO_INCREMENT PRIMARY KEY, 78 c1 VARCHAR(255) 79); 80 81CREATE TABLE t2 ( 82 id INT, 83 c2 VARCHAR(255) 84); 85 86INSERT INTO t1 (c1) VALUES 87 ('r'), ('rr'), ('rrr'), ('rrrr'), 88 ('.r'), ('.rr'), ('.rrr'), ('.rrrr'), 89 ('r.'), ('rr.'), ('rrr.'), ('rrrr.'), 90 ('.r.'), ('.rr.'), ('.rrr.'), ('.rrrr.'); 91SELECT * FROM t1; 92 93--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 94eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY 'r' FROM t1; 95cat_file $MYSQLTEST_VARDIR/tmp/t1; 96 97--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 98eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t2 FIELDS ENCLOSED BY 'r'; 99SELECT t1.id, c1, c2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE c1 != c2; 100SELECT t1.id, c1, c2 FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE c1 != c2; 101 102remove_file $MYSQLTEST_VARDIR/tmp/t1; 103DROP TABLE t1,t2; 104 105# End of 4.1 tests 106 107# 108# Let us test extended LOAD DATA features 109# 110create table t1 (a int default 100, b int, c varchar(60)); 111# we can do something like this 112load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=concat("b=",@b); 113select * from t1; 114truncate table t1; 115# we can use filled fields in expressions 116# we also assigning NULL value to field with non-NULL default here 117load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set c= if(a is null,"oops",a); 118select * from t1; 119truncate table t1; 120# we even can use variables in set clause, and missed columns will be set 121# with default values 122set @c:=123; 123load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, b) set c= if(@a is null,@c,b); 124select * from t1; 125# let us test side-effect of such load 126load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, @b); 127select * from t1; 128select @a, @b; 129truncate table t1; 130# Reading of all columns with set 131load data infile '../../std_data/rpl_loaddata.dat' into table t1 set c=b; 132select * from t1; 133truncate table t1; 134# now going to test fixed field-row file format 135load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow"; 136select * from t1; 137truncate table t1; 138# this also should work 139load data infile '../../std_data/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)); 140select * from t1; 141# and this should bark 142--error 1409 143load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b); 144 145# Now let us test LOAD DATA with subselect 146create table t2 (num int primary key, str varchar(10)); 147insert into t2 values (10,'Ten'), (15,'Fifteen'); 148truncate table t1; 149load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@dummy,@n) set a= @n, c= (select str from t2 where num=@n); 150select * from t1; 151 152# 153# Bug#18628 mysql-test-run: security problem 154# 155# It should not be possible to load from a file outside of vardir 156 157--error 1238 158set @@secure_file_priv= 0; 159 160# Test "load data" 161truncate table t1; 162--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR 163--error ER_OPTION_PREVENTS_STATEMENT, ER_TEXTFILE_NOT_READABLE 164eval load data infile '$MYSQL_TEST_DIR/t/loaddata.test' into table t1; 165select * from t1; 166 167# Test "load_file" returns NULL 168--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR 169eval select load_file("$MYSQL_TEST_DIR/t/loaddata.test"); 170 171# cleanup 172drop table t1, t2; 173 174# 175# Bug#27586: Wrong autoinc value assigned by LOAD DATA in the 176# NO_AUTO_VALUE_ON_ZERO mode 177# 178create table t1(f1 int); 179insert into t1 values(1),(null); 180create table t2(f2 int auto_increment primary key); 181disable_query_log; 182eval select * into outfile '$MYSQLTEST_VARDIR/tmp/t1' from t1; 183SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=NO_AUTO_VALUE_ON_ZERO; 184eval load data infile '$MYSQLTEST_VARDIR/tmp/t1' into table t2; 185enable_query_log; 186select * from t2; 187remove_file $MYSQLTEST_VARDIR/tmp/t1; 188SET @@SQL_MODE=@OLD_SQL_MODE; 189drop table t1,t2; 190 191# 192# Bug#27670: LOAD DATA does not set CURRENT_TIMESTAMP default value for a 193# TIMESTAMP field when no value has been provided. 194# 195create table t1(f1 int, f2 timestamp not null default current_timestamp); 196create table t2(f1 int); 197insert into t2 values(1),(2); 198disable_query_log; 199eval select * into outfile '$MYSQLTEST_VARDIR/tmp/t2' from t2; 200eval load data infile '$MYSQLTEST_VARDIR/tmp/t2' into table t1; 201enable_query_log; 202select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1; 203remove_file $MYSQLTEST_VARDIR/tmp/t2; 204delete from t1; 205disable_query_log; 206eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t2' 207FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n' 208FROM t2; 209eval load data infile '$MYSQLTEST_VARDIR/tmp/t2' into table t1 210FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n'; 211enable_query_log; 212select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1; 213remove_file $MYSQLTEST_VARDIR/tmp/t2; 214drop table t1,t2; 215 216# 217# Bug#29442: SELECT INTO OUTFILE FIELDS ENCLOSED BY digit, minus sign etc 218# corrupts non-string fields containing this character. 219# 220 221CREATE TABLE t1 (c1 INT, c2 TIMESTAMP, c3 REAL, c4 DOUBLE); 222 223INSERT INTO t1 (c1, c2, c3, c4) VALUES (10, '1970-02-01 01:02:03', 1.1E-100, 1.1E+100); 224SELECT * FROM t1; 225 226--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 227eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1; 228cat_file $MYSQLTEST_VARDIR/tmp/t1; 229echo EOF; 230 231TRUNCATE t1; 232 233--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 234eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t1 FIELDS ENCLOSED BY '-'; 235SELECT * FROM t1; 236 237remove_file $MYSQLTEST_VARDIR/tmp/t1; 238DROP TABLE t1; 239 240########################################################################### 241 242--echo 243--echo # -- 244--echo # -- Bug#35469: server crash with LOAD DATA INFILE to a VIEW. 245--echo # -- 246 247--echo 248--disable_warnings 249DROP TABLE IF EXISTS t1; 250DROP VIEW IF EXISTS v1; 251DROP VIEW IF EXISTS v2; 252DROP VIEW IF EXISTS v3; 253--enable_warnings 254 255--echo 256CREATE TABLE t1(c1 INT, c2 VARCHAR(255)); 257 258--echo 259CREATE VIEW v1 AS SELECT * FROM t1; 260CREATE VIEW v2 AS SELECT 1 + 2 AS c0, c1, c2 FROM t1; 261CREATE VIEW v3 AS SELECT 1 AS d1, 2 AS d2; 262 263--echo 264LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v1 265 FIELDS ESCAPED BY '\\' 266 TERMINATED BY ',' 267 ENCLOSED BY '"' 268 LINES TERMINATED BY '\n' (c1, c2); 269 270--echo 271SELECT * FROM t1; 272 273--echo 274SELECT * FROM v1; 275 276--echo 277DELETE FROM t1; 278 279--echo 280LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v2 281 FIELDS ESCAPED BY '\\' 282 TERMINATED BY ',' 283 ENCLOSED BY '"' 284 LINES TERMINATED BY '\n' (c1, c2); 285 286--echo 287SELECT * FROM t1; 288 289--echo 290SELECT * FROM v2; 291 292--echo 293DELETE FROM t1; 294 295--echo 296--error ER_NONUPDATEABLE_COLUMN 297LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v2 298 FIELDS ESCAPED BY '\\' 299 TERMINATED BY ',' 300 ENCLOSED BY '"' 301 LINES TERMINATED BY '\n' (c0, c2); 302 303--echo 304--error ER_NON_UPDATABLE_TABLE 305LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v3 306 FIELDS ESCAPED BY '\\' 307 TERMINATED BY ',' 308 ENCLOSED BY '"' 309 LINES TERMINATED BY '\n' (d1, d2); 310 311--echo 312DROP TABLE t1; 313DROP VIEW v1; 314DROP VIEW v2; 315DROP VIEW v3; 316 317--echo 318--echo # -- End of Bug#35469. 319 320 321 322########################################################################### 323 324# 325# Bug#37114: sql_mode NO_BACKSLASH_ESCAPES does not work properly with 326# LOAD DATA INFILE 327# 328 329# - For each plain "SELECT id,...", the 1st pair ("before" SELECT...OUTFILE, 330# LOAD...INFILE) and the 2nd pair of lines ("after") in the result should 331# look the same, otherwise we broke the dumpe/restore cycle! 332# 333# - the \r is always { '\\', 'r' } in memory, but on-disk format changes 334# 335# - the \t is { '\t' } or { '\\', 't' } in memory depending on whether \ 336# is magic (that is, NO_BACKSLASH_ESCAPES is not set) at INSERT-time. 337# on-disk format varies. 338# 339# - while INFILE/OUTFILE behaviour changes according to NO_BACKSLASH_ESCAPES, 340# we can override these defaults using ESCAPED BY '...' 341# 1: NO_BACKSLASH_ESCAPES default, \ on-disk: \,t,x,\r 342# 2: NO_BACKSLASH_ESCAPES override, \\ on-disk: \,\,t,x,\,\,r 343# 3: !NO_BACKSLASH_ESCAPES default, \\ on-disk: tab,\,\,r 344# 3: !NO_BACKSLASH_ESCAPES override, \ on-disk: tab,\,r 345 346--echo Bug#37114 347 348SET SESSION character_set_client=latin1; 349SET SESSION character_set_server=latin1; 350SET SESSION character_set_connection=latin1; 351SET @OLD_SQL_MODE=@@SESSION.SQL_MODE; 352 353# 0. test LOAD DATA INFILE first; if that works, all issues in 354# SELECT INTO OUTFILE / LOAD DATA INFILE cycles below are 355# arguably in the saving. 356 357--echo test LOAD DATA INFILE 358 359--let $file=$MYSQLTEST_VARDIR/tmp/bug37114.txt 360--let $file2=$MYSQLTEST_VARDIR/tmp/bug37114_out.txt 361 362SET sql_mode = ''; 363 364--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 365--eval SELECT '1 \\\\aa\n' INTO DUMPFILE '$file' 366 367CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM; 368 369SET sql_mode = 'NO_BACKSLASH_ESCAPES'; 370 371--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 372--eval LOAD DATA INFILE '$file' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' ' 373SELECT * FROM t1; 374 375# show we can write this with OUTFILE, forcing the parameters for now 376--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 377--eval SELECT * INTO OUTFILE '$file2' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 378--diff_files $file $file2 379--remove_file $file2 380 381# now show the OUTFILE defaults are correct with NO_BACKSLASH_ESCAPES 382--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 383--eval SELECT * INTO OUTFILE '$file2' FIELDS TERMINATED BY ' ' FROM t1 384--diff_files $file $file2 385--remove_file $file2 386 387INSERT INTO t1 (id, val1) VALUES (1, '\aa'); 388SELECT * FROM t1; 389 390SET sql_mode=''; 391INSERT INTO t1 (id, val1) VALUES (1, '\aa'); 392SELECT * FROM t1; 393 394DROP TABLE t1; 395 396--remove_file $file 397 398 399 400--echo test SELECT INTO OUTFILE 401 402CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4)); 403CREATE TABLE t2 LIKE t1; 404 405# 1. with NO_BACKSLASH_ESCAPES on 406 407SET sql_mode = ''; 408INSERT INTO t1 (id, val1) VALUES (5, '\ttab'); 409INSERT INTO t1 (id, val1) VALUES (4, '\\r'); 410SET sql_mode = 'NO_BACKSLASH_ESCAPES'; 411INSERT INTO t1 (id, val1) VALUES (3, '\tx'); 412 413--echo 1.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY 414 415--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 416--eval SELECT * INTO OUTFILE '$file' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id 417 418--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 419--eval LOAD DATA INFILE '$file' INTO TABLE t2 FIELDS TERMINATED BY ' ' 420 421SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION 422 SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; 423 424TRUNCATE t2; 425 426--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 427eval SELECT LOAD_FILE("$file"); 428--remove_file $file 429 430 431 432--echo 1.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY 433 434--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 435--eval SELECT * INTO OUTFILE '$file' FIELDS ESCAPED BY '\' TERMINATED BY ' ' FROM t1 ORDER BY id 436 437--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 438--eval LOAD DATA INFILE '$file' INTO TABLE t2 FIELDS ESCAPED BY '\' TERMINATED BY ' ' 439 440SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION 441 SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; 442 443TRUNCATE t2; 444 445--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 446eval SELECT LOAD_FILE("$file"); 447--remove_file $file 448 449 450 451# 2. with NO_BACKSLASH_ESCAPES off 452 453SET sql_mode = ''; 454 455--echo 2.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY 456 457--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 458--eval SELECT * INTO OUTFILE '$file' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id 459 460--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 461--eval LOAD DATA INFILE '$file' INTO TABLE t2 FIELDS TERMINATED BY ' ' 462 463SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION 464 SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; 465 466TRUNCATE t2; 467 468SET sql_mode = 'NO_BACKSLASH_ESCAPES'; 469 470--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 471eval SELECT LOAD_FILE("$file"); 472--remove_file $file 473 474SET sql_mode = ''; 475 476 477 478--echo 2.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY 479 480--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 481--eval SELECT * INTO OUTFILE '$file' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 ORDER BY id 482 483--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 484--eval LOAD DATA INFILE '$file' INTO TABLE t2 FIELDS ESCAPED BY '' TERMINATED BY ' ' 485 486SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION 487 SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; 488 489TRUNCATE t2; 490 491SET sql_mode = 'NO_BACKSLASH_ESCAPES'; 492 493--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 494eval SELECT LOAD_FILE("$file"); 495--remove_file $file 496 497# clean up 498set session sql_mode=@OLD_SQL_MODE; 499DROP TABLE t1,t2; 500 501 502--echo # 503--echo # Bug #51893: crash with certain characters given to load_file 504--echo # function on windows 505--echo # 506 507select load_file(0x0A9FB76C661B409C4BEC88098C5DD71B1072F9691F2E827D7EC8F092B299868A3CE196C04F0FB18CAB4E1557EB72331D812379DE7A75CA21C32E7C722C59E5CC33EF262EF04187B0F0EE756FA984DF2EAD37B1E4ADB064C3C5038F2E3B2D661B1C1150AAEB5425512E14D7506166D92D4533872E662F4B2D1428AAB5CCA72E75AA2EF325E196A5A02E2E8278873C64375845994B0F39BE2FF7B478332A7B0AA5E48877C47B6F513E997848AF8CCB8A899F3393AB35333CF0871E36698193862D486B4B9078B70C0A0A507B8A250F3F876F5A067632D5E65193E4445A1EC3A2C9B4C6F07AC334F0F62BC33357CB502E9B1C19D2398B6972AEC2EF21630F8C9134C4F7DD662D8AD7BDC9E19C46720F334B66C22D4BF32ED275144E20E7669FFCF6FC143667C9F02A577F32960FA9F2371BE1FA90E49CBC69C01531F140556854D588DD0E55E1307D78CA38E975CD999F9AEA604266329EE62BFB5ADDA67F549E211ECFBA906C60063696352ABB82AA782D25B17E872EA587871F450446DB1BAE0123D20404A8F2D2698B371002E986C8FCB969A99FF0E150A2709E2ED7633D02ADA87D5B3C9487D27B2BD9D21E2EC3215DCC3CDCD884371281B95A2E9987AAF82EB499C058D9C3E7DC1B66635F60DB121C72F929622DD47B6B2E69F59FF2AE6B63CC2EC60FFBA20EA50569DBAB5DAEFAEB4F03966C9637AB55662EDD28439155A82D053A5299448EDB2E7BEB0F62889E2F84E6C7F34B3212C9AAC32D521D5AB8480993F1906D5450FAB342A0FA6ED223E178BAC036B81E15783604C32A961EA1EF20BE2EBB93D34ED37BC03142B7583303E4557E48551E4BD7CBDDEA146D5485A5D212C35189F0BD6497E66912D2780A59A53B532E12C0A5ED1EC0445D96E8F2DD825221CFE4A65A87AA21DC8750481B9849DD81694C3357A0ED9B78D608D8EDDE28FAFBEC17844DE5709F41E121838DB55639D77E32A259A416D7013B2EB1259FDE1B498CBB9CAEE1D601DF3C915EA91C69B44E6B72062F5F4B3C73F06F2D5AD185E1692E2E0A01E7DD5133693681C52EE13B2BE42D03BDCF48E4E133CF06662339B778E1C3034F9939A433E157449172F7969ACCE1F5D2F65A4E09E4A5D5611EBEDDDBDB0C0C0A); 508 509 510--echo End of 5.0 tests 511 512 513 514# 515# Bug#12448 LOAD DATA / SELECT INTO OUTFILE 516# doesn't work with multibyte path name 517# 518CREATE TABLE t1 (a int); 519INSERT INTO t1 VALUES (1); 520SET NAMES latin1; 521SET character_set_filesystem=filename; 522select @@character_set_filesystem; 523SELECT * INTO OUTFILE 't-1' FROM t1; 524DELETE FROM t1; 525LOAD DATA INFILE 't-1' INTO TABLE t1; 526SELECT * FROM t1; 527DELETE FROM t1; 528SET character_set_filesystem=latin1; 529select @@character_set_filesystem; 530LOAD DATA INFILE 't@002d1' INTO TABLE t1; 531SELECT * FROM t1; 532DROP TABLE t1; 533let $MYSQLD_DATADIR= `select @@datadir`; 534remove_file $MYSQLD_DATADIR/test/t@002d1; 535SET character_set_filesystem=default; 536select @@character_set_filesystem; 537 538 539--echo # 540--echo # Bug #51850: crash/memory overlap when using load data infile and set 541--echo # col equal to itself! 542--echo # 543 544CREATE TABLE t1(col0 LONGBLOB); 545SELECT 'test' INTO OUTFILE 't1.txt'; 546LOAD DATA INFILE 't1.txt' IGNORE INTO TABLE t1 SET col0=col0; 547SELECT * FROM t1; 548 549DROP TABLE t1; 550let $MYSQLD_DATADIR= `select @@datadir`; 551remove_file $MYSQLD_DATADIR/test/t1.txt; 552 553 554--echo # 555--echo # Bug #52512 : Assertion `! is_set()' in 556--echo # Diagnostics_area::set_ok_status on LOAD DATA 557--echo # 558 559connect (con1,localhost,root,,test); 560 561CREATE TABLE t1 (id INT NOT NULL); 562--send LOAD DATA LOCAL INFILE 'tb.txt' INTO TABLE t1 563# please keep this is a spearate test file : it's important to have no 564# commands after this one 565 566connection default; 567dirty_close con1; 568 569connect (con1,localhost,root,,test); 570DROP TABLE t1; 571connection default; 572disconnect con1; 573 574############################################################################# 575# The below protion is moved to ctype_ucs.test # 576############################################################################# 577#--echo # 578#--echo # Bug #51876 : crash/memory underrun when loading data with ucs2 579#--echo # and reverse() function 580#--echo # 581 582#--echo # Problem # 1 (original report): wrong parsing of ucs2 data 583#SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp.txt'; 584#CREATE TABLE t1(a INT); 585#LOAD DATA INFILE 'tmpp.txt' INTO TABLE t1 CHARACTER SET ucs2 586#(@b) SET a=REVERSE(@b); 587#--echo # should return 2 zeroes (as the value is truncated) 588#SELECT * FROM t1; 589 590#DROP TABLE t1; 591#let $MYSQLD_DATADIR= `select @@datadir`; 592#remove_file $MYSQLD_DATADIR/test/tmpp.txt; 593 594 595#--echo # Problem # 2 : if you write and read ucs2 data to a file they're lost 596#SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp2.txt' CHARACTER SET ucs2; 597#CREATE TABLE t1(a INT); 598#LOAD DATA INFILE 'tmpp2.txt' INTO TABLE t1 CHARACTER SET ucs2 599#(@b) SET a=REVERSE(@b); 600#--echo # should return 0 and 1 (10 reversed) 601#SELECT * FROM t1; 602 603#DROP TABLE t1; 604#let $MYSQLD_DATADIR= `select @@datadir`; 605#remove_file $MYSQLD_DATADIR/test/tmpp2.txt; 606###################################################################################### 607 608 609--echo # 610--echo # Bug#11765139 58069: LOAD DATA INFILE: VALGRIND REPORTS INVALID MEMORY READS AND WRITES WITH U 611--echo # 612 613CREATE TABLE t1(f1 INT); 614EVAL SELECT 0xE1C330 INTO OUTFILE 't1.dat'; 615--disable_warnings 616LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8; 617--enable_warnings 618 619DROP TABLE t1; 620let $MYSQLD_DATADIR= `select @@datadir`; 621remove_file $MYSQLD_DATADIR/test/t1.dat; 622 623--echo # 624--echo # Bug#11765141 - 58072: LOAD DATA INFILE: LEAKS IO CACHE MEMORY 625--echo # WHEN ERROR OCCURS 626--echo # 627 628--let $file=$MYSQLTEST_VARDIR/tmp/bug11735141.txt 629--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 630--eval SELECT '1\n' INTO DUMPFILE '$file' 631 632create table t1(a point); 633--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 634--error ER_CANT_CREATE_GEOMETRY_OBJECT 635--eval LOAD DATA INFILE '$file' INTO TABLE t1 636drop table t1; 637 638--echo End of 5.1 tests 639 640--echo # 641--echo # Bug#11759519 INFINITE HANG WITH 100% CPU USAGE WITH LOAD DATA LOCAL AND IMPORT ERRORS 642--echo # 643SET @old_mode= @@sql_mode; 644CREATE TABLE t1 (fld1 INT); 645--copy_file $EXE_MYSQL $MYSQLTEST_VARDIR/mysql 646 647SET sql_mode='strict_all_tables'; 648 649--echo # Without fix, load data hangs forever. 650--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 651--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD,1000 652eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/mysql' REPLACE INTO TABLE t1 653 FIELDS TERMINATED BY 't' LINES TERMINATED BY ''; 654 655SET @@sql_mode= @old_mode; 656 657--remove_file $MYSQLTEST_VARDIR/mysql 658DROP TABLE t1; 659 660