Bug #75170 | load local data infile change timestamp column null value to 0000-00-00 00:00:00 | ||
---|---|---|---|
Submitted: | 11 Dec 2014 4:13 | Modified: | 11 Dec 2014 8:25 |
Reporter: | Support ICIL | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: General | Severity: | S1 (Critical) |
Version: | MySQL 5.6.19 & 5.7.4 | OS: | Linux (Ubuntu12.04) |
Assigned to: | CPU Architecture: | Any | |
Tags: | load local data infile, timestamp issue |
[11 Dec 2014 4:13]
Support ICIL
[11 Dec 2014 7:02]
MySQL Verification Team
Thank you for the report. Imho this is not a bug, invalid TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00') and use \N (backslash, capital-N) for NULL.. See, below: // mysql> CREATE TABLE `test_timestamp` ( -> `sequence` int(11) NOT NULL, -> `crt_date` timestamp NULL DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql> insert into test_timestamp values(111111,NULL); Query OK, 1 row affected (0.01 sec) mysql> select * from test_timestamp; +----------+----------+ | sequence | crt_date | +----------+----------+ | 111111 | NULL | +----------+----------+ 1 row in set (0.00 sec) mysql> \q Bye // use \N (backslash, capital-N) for NULL [umshastr@localhost]/export/umesh/mysql-5.7.6: cat k.test2 111112{|}$ <-- Warning, and end result as you seen in your test case 111112{|}\N$ <--- should result in NULL // [umshastr@localhost]/export/umesh/mysql-5.7.6: bin/mysql -u root -p -S /tmp/mysql_ushastry.sock .. mysql> LOAD DATA LOCAL INFILE 'k.test2' INTO TABLE test_timestamp FIELDS TERMINATED BY '{|}' LINES TERMINATED BY '$\n'; Query OK, 2 rows affected, 1 warning (0.00 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 1 mysql> show warnings; +---------+------+-----------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------+ | Warning | 1265 | Data truncated for column 'crt_date' at row 1 | +---------+------+-----------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from test_timestamp; +----------+---------------------+ | sequence | crt_date | +----------+---------------------+ | 111111 | NULL | | 111112 | 0000-00-00 00:00:00 | | 111112 | NULL | +----------+---------------------+ 3 rows in set (0.00 sec) Please see - http://dev.mysql.com/doc/refman/5.6/en/load-data.html
[11 Dec 2014 8:25]
Support ICIL
Actually I bcp out the data file from Sybase, if use your way to solve the timestamp data type, I have to update the Null timestamp fields to \N, so that data could be load correctly. Thanks for your information.