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:
None 
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
Description:
I have large data using 'LOAD DATA LOCAL INFILE' syntax to load to MySQL server.

There's timestamp columns with null value but after load to the MySQL server, it will auto change to value '0000-00-00 00:00:00'. This actually affect the application to get the data of these kind of columns as '0000-00-00 00:00:00' is illegal timestamp.

Both try on amazon EC2 instance with MySQL 5.7.4 and RDS instance with MySQL5.6.19.

I wonder if any setting that can disable the timestamp column auto gen '0000-00-00 00:00:00'. Thanks.

How to repeat:
Create a table test_timestamp

mysql> show create table test_timestamp \G
*************************** 1. row ***************************
       Table: test_timestamp
Create Table: CREATE TABLE `test_timestamp` (
  `sequence` int(11) NOT NULL,
  `crt_date` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Insert a record for comparison with 'insert' syntax.

mysql> insert into test_timestamp
    -> values(111111,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_timestamp;
+----------+----------+
| sequence | crt_date |
+----------+----------+
|   111111 | NULL     |
+----------+----------+
1 row in set (0.00 sec)

Insert a record with 'LOAD DATA LOCAL INFILE' syntax.

ubuntu@icilaws-ubu-tst01:~$ cat test2
111112{|}$

mysql> LOAD DATA LOCAL INFILE 'test2' INTO TABLE test_timestamp FIELDS TERMINATED BY '{|}' LINES TERMINATED BY '$\n';
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> select * from test_timestamp;
+----------+---------------------+
| sequence | crt_date            |
+----------+---------------------+
|   111111 | NULL                |
|   111112 | 0000-00-00 00:00:00 |
+----------+---------------------+
2 rows in set (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'crt_date' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.02 sec)

The value of crt_date for '111112' should be null instead of '0000-00-00 00:00:00'. And I can see the warning that data was truncated.
[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.