MySQL Bugs: #11277: load data local infile skips 5 extra characters after a text timestamp(14) stri

Bug #11277	load data local infile skips 5 extra characters after a text timestamp(14) stri
Submitted:	13 Jun 2005 5:18	Modified:	23 Jun 2005 7:13
Reporter:	Douglas Pearless	Email Updates:
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Microsoft Windows (Win XP + SP2)
Assigned to:	Jim Winstead	Target Version:

Description:
Hi,

I essence, the load local infile import utility reads 19 bytes, not 14 for a column
defined as timestamp(14) or timestamp.  

I have asked on the newbie forum, and searched all of the help and forums on data types
and read the manual and the syntax for importing and I think this is a bug!

How to repeat:
I have created a simple table with a timestamp as the first column:

CREATE TABLE `cust1` (
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`rest` varchar(45) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When I try to import data using the query browser:

load data local infile 'c:/20050605.dat' into table vertigo.cust1 fields terminated by ''
lines terminated by '|';

where c:/20050605.dat contains:
20050605231053123456|

The first 14 bytes are correctly read into the timestamp column as it should, the next 5
bytes are lost, and then only the last byte '6' gets into the next column.

e.g:

mysql> select * from cust1;
+---------------------+------+
| ts | rest |
+---------------------+------+
| 2005-06-05 23:10:53 | 6 |
+---------------------+------+
1 row in set (0.00 sec)

I have tried all sorts of combinations. If any of the 15/16/17/18th bytes are rubbish
(non printing ASCII characters like BELL), then the time stamp gets set to all 0's. It
implies that the routine 'load data local infile' has a bug in it when reading in
timestamp(14) and is trying to read in 18 or 19 characters, perhaps looking for
delimiters like ':' and '-'??.

If I have only one column of varchar(255) then it is all read in correctly.

I have even tried defining ts as timestamp(14) with no luck.

tested in console client. it's not query browser problem, but server.

As of MySQL 4.1, the display width for TIMESTAMP fields is not supported, as documented at
http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html

But it should be added to our documentation that the display width is now fixed at 19
characters, which is explains the behavior here.

Added clarification in documentation about TIMESTAMP display width = 19 (fixed) for 4.1+
versions.