On Tue, 22 Jan 2008 14:00:20 +0100, Bob Bedford <bob@bedford.com> wrote:

>
> "Rik Wasmus" <luiheidsgoeroe@hotmail.com> a écrit dans le message de
> news:
> op.t5byb9ks5bnjuv@metallium.lan...
>> On Tue, 22 Jan 2008 12:05:26 +0100, Bob Bedford <bob@bedford.com> wrote:
>>
>>> Hi all, I've this query that works fine on mysql 4.1.22 and not in
>>> 5.0.45
>>>
>>> LOAD DATA INFILE 'C:/myfile.txt' REPLACE INTO TABLE mytable FIELDS
>>> TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES
>>> TERMINATED BY '\n'
>>
>> 1. Do you have the FILE privilige?
>> 2. If so, does the query give you an error?
>> --
>> Rik Wasmus
>>
>
> Hi again,
>
> searching differently, I finally found the solution but I'm scared I'm
> not
> doing things right:
> I had to set the sql_mode to '' as the mysql server was set in strict
> mode.
> So if in strict mode, it fails, if not it works.
>
> I've done this mysql statement:
> set session sql_mode = '';
> LOAD DATA.....;
> LOAD DATA .....;
>
> But It is right ? May I espect wrong datas in my tables ?

Well, MySQL itself advises to use strict. A simple test here seems to
indicate MySQL will recognize the text 'NULL', so if you can change the
creating of the file in question to print the text 'NULL' instead of an
empty string on NULL that would be best IMO. Otherwise, see
http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html =>
ALLOW_INVALID_DATES, and run an update afterwards setting it to NULL again
(datetime fields will be set to 0000-00-00 00:00:00).
--
Rik Wasmus