load data local infile gives weird results

[Douglas Pearless]

Hi,

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:

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, 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 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.

I have read the manual on data types and the syntax for importing and I am stuck!

Help!!

I am using wWin XP, SP2 and v4.1 (latest downloaded yesterday)

Thanks for any ideas, Douglas