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'

CREATE TABLE `mytable` (
a char(2) NOT NULL default '',
b tinyint(3) unsigned NOT NULL default '0',
c mediumint(8) unsigned NOT NULL default '0',
d char(13) NOT NULL default '',
e mediumint(8) unsigned NOT NULL default '0',
f date NOT NULL default '0000-00-00',
g date default NULL,
h char(3) default NULL,
i float(8,2) unsigned default NULL,
j float(8,2) unsigned default NULL,
k float(3,2) unsigned default NULL,
l float(8,2) unsigned default NULL,
m float(8,2) unsigned default NULL,
n tinyint(3) unsigned default NULL,
o tinyint(3) unsigned default NULL,
p tinyint(3) unsigned default NULL,
q tinyint(3) unsigned default NULL,
r tinyint(3) unsigned default NULL,
s date default NULL,
u tinyint(3) unsigned default NULL,
v tinyint(3) unsigned default NULL,
v tinyint(3) unsigned default NULL,
x float unsigned default NULL,
PRIMARY KEY (a,b,c,d),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

the myfile.txt
CH 10 1073757 102161427 21150 19970401 CHF 0 0 0 0 0 0 0 0 0 0 20061221 0 0
0 0
CH 10 1073765 102161427 27860 19970401 CHF 0 0 0 0 0 0 0 0 0 0 20061221 0 0
0 0

Thanks for helping.

Bob

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

"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?

Hello Rik, thanks for your reply

1- Yes I've.
2- I've 2 servers. The first is on Mysql 4.1.22 and it's ok. On the second
(5.0.45) I've this error using a third party tool: 1292 - Incorrect date
value: '' for column 'g' at row 1

For what I've seen, this data is empty on the first line (and other lines as
well).
CH 10 1073757 102161427 21150 19970401 [MISSING FIELD HERE] CHF 0 0 0 0 0 0
0 0 0 0 20061221 0 0 0 0

In 4.1.22 the LOAD DATA INFILE just created a NULL value, in 5.0.45 it seems
it is not able to take this value as empty and seems to need it.
Why ? It is a 5.0.45 bug ? Also how to fix it ?

Please note that the separator of the fields are tabs and not spaces.

I have to put this data online as soon as possible and I can't find any
information on how to fix it !!!! I've many files to treat and the problem
is on all files. As soon as there is a blank field, it stops to work. It did
work very well on 4.1.22 but I can't go back as it's on my ISP server and
I've no control on the mysql version !

"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 ?

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