I'm bringing up a new server which runs CentOS 6 with MySQL 5.1.52.
Something seems to have changed between that version and
"5.1.47community" related to data format for LOAD DATA LOCAL.
I have something that's working on "5.1.47community" and fails
on "5.1.52."

Here's the LOAD DATA LOCAL statement:

LOAD DATA LOCAL INFILE 'tmpYpaMdx.txt' REPLACE INTO TABLE companyindex
FIELDS ENCLOSED BY '"' ESCAPED BY '\\' TERMINATED BY ','
(conformed_company_name, domain, street_number, street_name,
parser_name, location, state, postal_code, country_code, database_name,
record_id)

This is executed by a Python program {Python 2.7 in all cases),
and the following warnings appear:

InfoCompanyDb.py:295: Warning: Data truncated for column 'parser_name'
at row 1
outcursor.execute(s)
InfoCompanyDb.py:295: Warning: Data truncated for column 'state' at row 1
outcursor.execute(s)
InfoCompanyDb.py:295: Warning: Row 1 doesn't contain data for all columns
outcursor.execute(s)
.... same for more rows

This can be reproduced with the "mysql" command line program, so it's
not a Python issue.

Here's the beginning of the data:

"ABT UTILITY INCOME FUND INC",NULL,"340","ROYAL
PALM","internal_english","PALM BEACH","FL","33480","US","edgaraddr","430966"

"ABT GROWTH & INCOME TRUST",NULL,"340","ROYAL
PALM","internal_english","PALM BEACH","FL","33480","US","edgaraddr","430968"

"KEYSTONE AMERICA TAX FREE MONEY MARKET
FUND",NULL,"200","BERKELEY","internal_english","BO STON","MA","02116","US","edgaraddr","430970"

"KEYSTONE AMERICA FUND OF GROWTH
STOCKS",NULL,"200","BERKLEY","internal_english","B OSTON","MA","02116","US","edgaraddr","430972"

"STROME INVESTMENT MANAGEMENT
LP",NULL,"100","WILSHIRE","internal_english","SANT A
MONICA","CA","90401","US","edgaraddr","430974"

(Those are actually on one line each, with no blank lines.)

Clearly the fields got out of sync somehow.

Note the use of NULL. That's correct, according to
the MySQL documentation. "If FIELDS ENCLOSED BY is not empty, a field
containing the literal word NULL as its value is read as a NULL value.
This differs from the word NULL enclosed within FIELDS ENCLOSED BY
characters, which is read as the string 'NULL'."

Ref: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Did handling of NULL chnage at some point?

Here's the table definition:

SHOW CREATE TABLE companyindex;

CREATE TABLE `companyindex` (
`domain` varchar(255) DEFAULT NULL,
`conformed_company_name` varchar(120) NOT NULL,
`location` varchar(50) DEFAULT NULL,
`state` varchar(2) DEFAULT NULL,
`postal_code` varchar(10) DEFAULT NULL,
`country_code` varchar(2) DEFAULT NULL,
`database_name` varchar(10) NOT NULL,
`record_id` bigint(20) NOT NULL,
`street_number` varchar(10) DEFAULT NULL,
`street_name` varchar(50) DEFAULT NULL,
`parser_name`
enum('no_address','no_parser','internal_english',' usc_deterministic','google_geocoder')
DEFAULT NULL,
UNIQUE KEY `record_id` (`record_id`,`database_name`),
KEY `domain` (`domain`),
KEY `conformed_company_name` (`conformed_company_name`),
KEY `location` (`location`),
KEY `state` (`state`),
KEY `postal_code` (`postal_code`),
KEY `country_code` (`country_code`),
KEY `parser_name` (`parser_name`),
KEY `street_number`
(`street_number`,`street_name`,`postal_code`,`coun try_code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

On CentOS, where this fails:
mysql> show variables like "%VERSION%";
+-------------------------+---------------------+

On Windows 7, where it works:
mysql> show variables like "%VERSION%";
+-------------------------+------------------------------+

John Nagle

John Nagle <nagle (AT) animats (DOT) com> wrote:

[snip]

This is very likely not MySQL versions, but end-of-line encoding.


XL

In your data, you don't seem to have all the fields terminated by ','.
(in particular, what seems to be the last field of a record).
You also didn't specify what terminates a line, so exactly where does
the field containing 430966 end, and why?

This might also involve UNIX vs. Windows line endings.

On 1/20/2012 12:53 AM, Axel Schwenke wrote:
No, it was working on MySQL 5.0.27 on Linux as well:

So it's not a Windows vs. Linux issue.

John Nagle

On 1/20/2012 5:07 AM, Gordon Burditt wrote:
That's an interesting point. Is a trailing comma required?
Looking around at SQL implementations, IBM DB no, MS DB yes,
MySQL unclear. So I added code to finish each line with a
comma before the end of line. Now lines look like this:

"ABT UTILITY INCOME FUND INC",NULL,"340","ROYAL
PALM","internal_english","PALM
BEACH","FL","33480","US","edgaraddr","430966",

Works on Windows, fails on Linux, as before.

Sorry, the mail program wrapped the data text. There's one line per
row, ended with "\n". The MySQL default is "LINES TERMINATED BY '\n'",
so that's OK.
No, see previous message. Here's a dump of the head of the file:

-sh-4.1$ od -c *.txt | head
0000000 " A B T U T I L I T Y I N C
0000020 O M E F U N D I N C " , N U
0000040 L L , " 3 4 0 " , " R O Y A L
0000060 P A L M " , " i n t e r n a l _
0000100 e n g l i s h " , " P A L M B
0000120 E A C H " , " F L " , " 3 3 4 8
0000140 0 " , " U S " , " e d g a r a d
0000160 d r " , " 4 3 0 9 6 6 " \n " A B
0000200 T G R O W T H & I N C O M
0000220 E T R U S T " , N U L L , " 3

Nothing funny there.

....
Same line as dumped above.

NULL may still be the problem.

John Nagle

On Thu, 19 Jan 2012 23:12:31 -0800, John Nagle wrote:
You're showing one table create and it has a first column `domain`.
The first column in the data looks like a company name, and the second
column in the data is NULL, while the second column in the CREATE TABLE
is listed as NOT NULL. Let's take this from the top again...

--
40. I will be neither chivalrous nor sporting. If I have an unstoppable
superweapon, I will use it as early and as often as possible instead
of keeping it in reserve.
--Peter Anspach's list of things to do as an Evil Overlord

On 1/21/2012 10:06 AM, Peter H. Coffin wrote:
The field order in the data file is determined by the field list
in the LOAD DATA INFILE, which is

That NULL in "domain" seems to be causing trouble. But that's
what the manual says to use when FIELDS ENCLOSED BY is specified.

John Nagle

On 1/21/2012 11:50 AM, John Nagle wrote:
table is in UTF-8. But the database default wasn't UTF-8, and that's
apparently the default encoding for a LOAD DATA LOCAL INFILE.

John Nagle