Bug #41446 | mysqlimport / Load Data File - partial columns import incorrect ? | ||
---|---|---|---|
Submitted: | 13 Dec 2008 2:21 | Modified: | 15 Dec 2008 7:32 |
Reporter: | Lars Wittmaack | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server | Severity: | S4 (Feature request) |
Version: | 5.0.45 | OS: | Linux (Red Hat 5.0) |
Assigned to: | CPU Architecture: | Any | |
Tags: | LOAD DATA INFILE, mysqlimport |
[13 Dec 2008 2:21]
Lars Wittmaack
[15 Dec 2008 7:32]
Sveta Smirnova
Thank you for the report. According to http://dev.mysql.com/doc/refman/5.0/en/load-data.html: "If you specify REPLACE, input rows replace existing rows." So current behavior is correct. But bug can be verified as feature request "add UPDATE keyword or any other possiblity to replace columns desired"
[2 Jan 2009 15:57]
Sai Tit
Hello Sveta, This appear to be a bug with Mysql version 5 and above that the "Load Data Infile" doesn't work correctly if you have a column with default value and I have managed to reproduce this error on Mysql Version 5.0.51A, 5.0.67 and 5.1.22. It seems that other user has reported the same thing in but it hasn't been acknowledged http://bugs.mysql.com/bug.php?id=27670 TEST CSV file content ================================= 2|test product 1|87|46.35~ 3|test product 2|27|37.10~ 4|test product 3|34|22.30~ ================================== TEST table schema =================================== CREATE TABLE `TestTbl` ( `id` mediumint(4) NOT NULL auto_increment, `product_id` mediumint(4) NOT NULL default '0', `title` varchar(50) default NULL, `country_id` mediumint(8) default NULL, `product_minimum` float(5,2) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ===================================== The load data sql statement works fine on Mysql version 4.1.22 as tested below: mysql> select version(); +-----------------+ | version() | +-----------------+ | 4.1.22-standard | +-----------------+ 1 row in set (0.00 sec) mysql> load data infile "/tmp/test.txt" into table TestTbl fields terminated by "|" LINES TERMINATED BY "~" (product_id, title,country_id, product_minimum); Query OK, 4 rows affected, 4 warnings (0.01 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 4 mysql> show warnings; +---------+------+-------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------+ | Warning | 1265 | Data truncated for column 'product_id' at row 4 | | Warning | 1261 | Row 4 doesn't contain data for all columns | | Warning | 1261 | Row 4 doesn't contain data for all columns | | Warning | 1261 | Row 4 doesn't contain data for all columns | +---------+------+-------------------------------------------------+ 4 rows in set (0.00 sec) mysql> select * from TestTbl; +----+------------+----------------+------------+-----------------+ | id | product_id | title | country_id | product_minimum | +----+------------+----------------+------------+-----------------+ | 1 | 2 | test product 1 | 87 | 46.35 | | 2 | 3 | test product 1 | 27 | 37.10 | | 3 | 4 | test product 1 | 34 | 22.30 | Now retest the same "load data" statement in mysql 5.0.51A and you will notice that the data values in "product_id" get messed up. mysql> select version(); +-------------+ | version() | +-------------+ | 5.0.51a-log | +-------------+ mysql> load data infile "/tmp/test.txt" into table TestTbl fields terminated by "|" LINES TERMINATED BY "~" (product_id, title,country_id, product_minimum); Query OK, 4 rows affected, 6 warnings (0.01 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 3 mysql> show warnings; +---------+------+----------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------+ | Warning | 1261 | Row 4 doesn't contain data for all columns | | Warning | 1261 | Row 4 doesn't contain data for all columns | | Warning | 1261 | Row 4 doesn't contain data for all columns | +---------+------+---------------------------------------------------------------- mysql> select * from TestTbl; +----+------------+----------------+------------+-----------------+ | id | product_id | title | country_id | product_minimum | +----+------------+----------------+------------+-----------------+ | 1 | 2 | test product 1 | 87 | 46.35 | | 2 | 0 | test product 2 | 27 | 37.10 | | 3 | 0 | test product 3 | 34 | 22.30 | | 4 | 0 | NULL | NULL | NULL | +----+------------+----------------+------------+-----------------+ 4 rows in set (0.00 sec) Sai