[JAG-Fumbler]

I have version 5.0.19 standard of MySQL in production. 😮

And you are right, its not fixed in in this version. I have to patch to version 5.0.38. This is the part i have to say: Thank you very much! 🙄

MySQL BUG#15126 fixed

I have to ask my provider why he installed this old version…

[JAG-Fumbler]

peterlaursen wrote on Aug 13 2007, 10:37 AM:

The reason you get an error is that you are reversing the order or CHARACTER SET and INTO!

Table specification should come before character set.

We will support character set with csv import in the 5.1x code tree.

<_< Ok, just for the archive. The following was tried on a MySQL 5.0.x stable version with SQLyog 6.04 Enterprise query:

Code:

LOAD DATA LOCAL INFILE 'D:\Profil\Jens\Desktop\TB_GroupText.csv'
INTO TABLE TB_GroupText
CHARACTER SET utf8
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn';

This results in:

Code:

Error Code : 1064
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 'CHARACTER SET utf8
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn'' at line 3
(0 ms taken)

For the LOAD DATA INFILE the MySQL manual allows the use of CHARACTER SET utf8 but the current version of SQLyog do not. In my point of view this is a bug of SQLyog. I am sorry, but i can not install a BETA version of MySQL because of this gap. It would be extremly helful to fix that as soon as possible.

[JAG-Fumbler]

peterlaursen wrote on Aug 7 2007, 10:05 AM:

OK .. server version explains. MySQL 4.0x does not support unicode data with LOAD DATA INFILE. You cannot use utf8 encoded files with that server version.

With MySQL 4.1x and higher there is an option to specify the charset with LOAD data. We will support that from version 5.1 (surrently we asume that the file is utf8 encoded for those server version that support it, and when SQLyog generates a .csv from such MySQL version it is utf8 encoded)

Now i migrate the databese to the stable version 5.0. but still can not use SQLyog to import data correctly.

just try the following query:

Code:

LOAD DATA LOCAL INFILE 'C:\data.csv'
CHARACTER SET utf8
INTO TABLE TB_AodText
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn';

results in:

Code:

Error Code : 1064
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 'CHARACTER SET utf8
INTO TABLE TB_AodText
FIELDS TERMINATED BY 't'
LIN' at line 2
(0 ms taken)

if i delete the line

Code:

CHARACTER SET utf8

i get a import but with the wrong character set

I also tried to use the mysql tool 'mysqlimport.exe'

Code:

mysqlimport.exe –local –default-character-set=utf8 –lines-terminated-by=”rn” –fields-terminated-by=”t” [DATABASENAME] -u [USERNAME] C:[TABLENAME].csv

and this works fine for me and i got the result i was expecting

a complete desciption for MySQL version 5.0 you can find here 12.2.5. LOAD DATA INFILE Syntax

is there a chance to fix that in SQLyog?

[JAG-Fumbler]

Here you are.

You should know that i am connected to a MySQL database version 4.0.24-standard with a character set latin1 (i got this with show—>variables…) – may that helps.

Sorry if i had written in a unpolite form, may you can excuse me because of my bad english knowledge. I will try to find a more convenient way of communication.

Thank you very much!

[Author]

Posts