problem with LOAD DATA INFILE and TIMESTAMP default

[Robert Huff]

1) I'm running 5.1.26, with "sql strict" mode.
2) I have an existing table 'p':

mysql> desc p;
+----------------+-------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null |
Key | Default | Extra |
+----------------+-------------------------------------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO |
PRI | NULL | auto_increment |
| full | varchar(32) | NO |
| | |
| use | varchar(32) | NO |
| | |
| serial | int(11) | YES | | 0
| |
| status | enum('active','retired','dead','unknown') | YES |
| active | |
| notes | text | YES |
| NULL | |
| last_updated | timestamp | NO |
| CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+-------------------------------------------+------+-----+-------------------+-----------------------------+

3) I would like to load data into this table using:

LOAD DATA INFILE '/home/huff/code/sql/data.new'
INTO TABLE p
FIELDS TERMINATED BY '|';

4) When I use that LOAD DATA command with this data:

0|Suka|Suka|0|active||

I get this:

ERROR 1292 (22007): Incorrect datetime value: '' for column
'last_updated' at row 1

5) If I use

LOAD DATA INFILE '/home/huff/code/sql/names.new'
INTO TABLE p set last_updated=NOW()
FIELDS TERMINATED BY '|';

I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'FIELDS TERMINATED BY '|'' at line 1

I've read the 5.1 docs (several times), Googled various parts of the
error messages, and searched the mailing list and newsgroup archives ...
... and still managed to miss the essential clue.

Help, please?
Respectfully,

Robert Huff

[Jonathan]

Robert Huff wrote:
> 5) If I use
>
> LOAD DATA INFILE '/home/huff/code/sql/names.new'
> INTO TABLE p set last_updated=NOW()
> FIELDS TERMINATED BY '|';
>
> I get:
>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual that corresponds to your MySQL server version for the right
> syntax to use near 'FIELDS TERMINATED BY '|'' at line 1

Robert,

I am not sure if the order is important here but according to the manual
the override using SET is listed at the end of the command, perhaps that
might be your issue. How about when you rewrite it to this?

LOAD DATA INFILE '/home/huff/code/sql/names.new'
INTO TABLE p

FIELDS TERMINATED BY '|'
SET last_updated=NOW();

Kind regards,

Jonathan

[Robert Huff]

Tried that; didn't work.
Still want to know how to out default timestamp in an INFILL, but for
the moment have settled for using a fixed and legal-but-nonsensical
date time.
Thanks for the suggestion, and sorry for being so long on the reply.

Robert Huff