LOAD DATA doesn't seem to work with CHARACTER SET. Is that featurein released MySQL 5.0?

[Database Administrator]

Put in this MySQL command, via Python and MySQLdb:

LOAD DATA LOCAL INFILE "c:\\docume~1\\nagle\\locals~1\\temp\\tmpi16zz2.txt" REPLACE
INTO TABLE companyindex CHARACTER SET utf8 FIELDS ENCLOSED BY '"' ESCAPED BY
'\\' TERMINATED
BY ',' (conformed_company_name, domain, location, state, postal_code,
country_code, database_name, record_id)

Received:

> _mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL se
> rver version for the right syntax to use near \'CHARACTER SET utf8 FIELDS ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' TERMINATED BY \',\'
> (con\' at line 1')
> Press any key to continue

If I remove the "CHARACTER SET utf8", SQL accepts it, FIELDS
and all, and loads ASCII data correctly.

I get exactly the same error by pasting the command above into the
MySQL query browser, so it's not a Python problem.

That should be the right syntax, per

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

Here's my MySQL installation:

MySQL version: 5.0.27.
character_set_client: utf8
character_set_connection: utf8
character_set_database: utf8
chacter_set_results: utf8
character_set_server: utf8
character_set_system: utf8

so everything is Unicode-enabled.

But, according to this bug report:

http://bugs.mysql.com/bug.php?id=15126

[16 Mar 11:29] Alexander Barkov

Dear doc team,

Can you please also document that the CHARACTER SET
clause was added into LOAD DATA syntax:

LOAD DATA INFILE 'loaddata6.dat' INTO TABLE t1 CHARACTER SET koi8r;

the MySQL 5.0 documentation was recently updated to show this feature.
But it looks like that feature actually went into MySQL 5.1 in this patch

http://lists.mysql.com/commits/16915

on December 13, 2006, and isn't in MySQL 5.0.

In fact, if I paste the LOAD DATA INFILE statement from the bug
report into the MySQL query browser, I get a syntax error near
"'CHARACTER SET koi8r' at line 1". So it really isn't implemented
in MySQL 5.0.27.

Is that correct?

Given that, what's the correct way to tell MySQL that it
is reading a UTF8 file? (And does the Python interface to
the MySQL client need to do something about this?)

John Nagle

[Database Administrator]

John Nagle wrote:
>
> LOAD DATA LOCAL INFILE "c:\\docume~1\\nagle\\locals~1\\temp\\tmpi16zz2.txt" REPLACE
> INTO TABLE companyindex CHARACTER SET utf8 FIELDS ENCLOSED BY '"' ESCAPED BY
> '\\' TERMINATED
> BY ',' (conformed_company_name, domain, location, state, postal_code,
> country_code, database_name, record_id)
>
>> _mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL se
>> rver version for the right syntax to use near \'CHARACTER SET utf8 FIELDS ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' TERMINATED BY \',\'
>> (con\' at line 1')
>
> If I remove the "CHARACTER SET utf8", SQL accepts it, FIELDS
> and all, and loads ASCII data correctly.

> That should be the right syntax, per
> http://dev.mysql.com/doc/refman/5.0/en/load-data.html
>
> Here's my MySQL installation:
> MySQL version: 5.0.27.

Maybe this was changed recently, but the manual page you reference
says:

"Beginning with MySQL 5.0.38, if the contents of the input file use
a character set that differs from the default, it is possible (and
usually preferable) to use the CHARACTER SET clause to specify the
character set of the file."

So your MySQL is too old for that feature.


> Given that, what's the correct way to tell MySQL that it
> is reading a UTF8 file?

From the same manual page:

"The character set indicated by the character_set_database system
variable is used to interpret the information in the file. SET NAMES
and the setting of character_set_client do not affect interpretation
of input."



XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

[Database Administrator]

Axel Schwenke wrote:
> Maybe this was changed recently
Yes, the manual page changed since yesterday. Compare

http://web.archive.org/web/200604281...load-data.html

> but the manual page you reference says:
>
> "Beginning with MySQL 5.0.38, if the contents of the input file use
> a character set that differs from the default, it is possible (and
> usually preferable) to use the CHARACTER SET clause to specify the
> character set of the file."
>
> So your MySQL is too old for that feature.

"Too old?" 5.0.38 isn't even released yet. 5.0.37 is the latest download.

John Nagle

[Database Administrator]

John Nagle wrote:
> Axel Schwenke wrote:
>> Maybe this was changed recently
> Yes, the manual page changed since yesterday. Compare
>
> http://web.archive.org/web/200604281...load-data.html
>
>> but the manual page you reference says:
>>
>> "Beginning with MySQL 5.0.38, if the contents of the input file use
>> a character set that differs from the default, it is possible (and
>> usually preferable) to use the CHARACTER SET clause to specify the
>> character set of the file."
>>
>> So your MySQL is too old for that feature.
>
> "Too old?" 5.0.38 isn't even released yet. 5.0.37 is the latest download.

Hehe. You're experiencing the time lag between updating the
documentation repository and releasing a new version.
To get this feature, you have to checkout MySQL source code
from the public bk repository and compile yourself:

http://dev.mysql.com/doc/refman/5.0/...urce-tree.html

Sorry for the inconvenience but apparently this is a "bleeding
edge" feature...


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

[Database Administrator]

Axel Schwenke wrote:
> John Nagle wrote:
>
>>Axel Schwenke wrote:

> Hehe. You're experiencing the time lag between updating the
> documentation repository and releasing a new version.
> To get this feature, you have to checkout MySQL source code
> from the public bk repository and compile yourself:
>
> http://dev.mysql.com/doc/refman/5.0/...urce-tree.html
>
> Sorry for the inconvenience but apparently this is a "bleeding
> edge" feature...

I know. I spent most of Sunday trying to figure out why
my LOAD DATA command wouldn't work. Actually, I didn't
need a CHARACTER SET clause; everything (server, database, tables,
connection, client) is configured for utf8, and the default
for LOAD FILE should then be utf8 too.

John Nagle