Hello,

I would like to import data from a utf8-coded comma seperated file. I
created my database with "DEFAULT CHARACTER SET utf8 COLLATE
utf8_general_ci" and I started my mysql-client with the
--default-character-set=utf8 option. Nevertheless, when I input primary
key fields, which differ only in one umlaut character (e.g. "achten" and
"ächten") I get the following error message:

ERROR 1062 (23000): Duplicate entry 'ächten' for key 1

(Same thing happens when I try to manually INSERT the row.)

When I display my variable settings with "SHOW variables LIKE 'c%';" I receive the following result:


+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
+--------------------------+----------------------------+
14 rows in set (0.02 sec)

From this I conclude it is the server setting, which causes the trouble
here. When I manipulate the settings manually from the client (with "SET
character_set_server=utf8; SET collation_server=utf8_general_ci;") the
values do change, but not the behaviour. But this can be expected, since
the server is already up and running with the wrong settings.

Does anybody know how I restart my mysql-server with the correct
character and collation settings, if this is the cause for my problem,
or if there might be any other reason for it. My mysql version is
5.0.26-12, running on a Suse Linux 10.2.

Best regards,
H.

--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

Before you import at the mysql prompt set below variables and then try again
to load

set session max_error_count=500000;
set session collation_database=latin1_swedish_ci;
set session character_set_database=latin1;

regards
anandkl


On 8/30/07, Harald Vajkonny <vajkonny@t-online.de> wrote:
>
> Hello,
>
> I would like to import data from a utf8-coded comma seperated file. I
> created my database with "DEFAULT CHARACTER SET utf8 COLLATE
> utf8_general_ci" and I started my mysql-client with the
> --default-character-set=utf8 option. Nevertheless, when I input primary
> key fields, which differ only in one umlaut character (e.g. "achten" and
> "ächten") I get the following error message:
>
> ERROR 1062 (23000): Duplicate entry 'ächten' for key 1
>
> (Same thing happens when I try to manually INSERT the row.)
>
> When I display my variable settings with "SHOW variables LIKE 'c%';" I
> receive the following result:
>
>
> +--------------------------+----------------------------+
> | Variable_name | Value |
> +--------------------------+----------------------------+
> | character_set_client | utf8 |
> | character_set_connection | utf8 |
> | character_set_database | utf8 |
> | character_set_filesystem | binary |
> | character_set_results | utf8 |
> | character_set_server | latin1 |
> | character_set_system | utf8 |
> | character_sets_dir | /usr/share/mysql/charsets/ |
> | collation_connection | utf8_general_ci |
> | collation_database | utf8_general_ci |
> | collation_server | latin1_swedish_ci |
> | completion_type | 0 |
> | concurrent_insert | 1 |
> | connect_timeout | 5 |
> +--------------------------+----------------------------+
> 14 rows in set (0.02 sec)
>
> From this I conclude it is the server setting, which causes the trouble
> here. When I manipulate the settings manually from the client (with "SET
> character_set_server=utf8; SET collation_server=utf8_general_ci;") the
> values do change, but not the behaviour. But this can be expected, since
> the server is already up and running with the wrong settings.
>
> Does anybody know how I restart my mysql-server with the correct
> character and collation settings, if this is the cause for my problem,
> or if there might be any other reason for it. My mysql version is
> 5.0.26-12, running on a Suse Linux 10.2.
>
> Best regards,
> H.
>
> --
> "Wouldn't the sentence 'I want to put a hyphen between the words Fish
> and And and And and Chips in my Fish-And-Chips sign' have been clearer
> if quotation marks had been placed before Fish, and between Fish and
> and, and and and And, and And and and, and and and And, and And and
> and, and and and Chips, as well as after Chips?"
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

Ananda Kumar schrieb:
> Before you import at the mysql prompt set below variables and then try
> again to load
>
> set session max_error_count=500000;
> set session collation_database=latin1_swedish_ci;
> set session character_set_database=latin1;
This is not what I need, because I use utf8 as well as in the database
as in the input file and I do not have any latin1 at all. When I choose
latin1 here, indeed, I don't receive an error message, but my data
becomes corrupted.

When, however, I choose "utf8" and "utf8_general_ci" as "session
character_set_database" and "session collation_database", I still get
the same error message.

Best regards,
H.


--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

> I would like to import data from a utf8-coded comma seperated file. I
> created my database with "DEFAULT CHARACTER SET utf8 COLLATE
> utf8_general_ci" and I started my mysql-client with the
> --default-character-set=utf8 option. Nevertheless, when I input primary
> key fields, which differ only in one umlaut character (e.g. "achten" and
> "ächten") I get the following error message:
>
> ERROR 1062 (23000): Duplicate entry 'ächten' for key 1
>
> (Same thing happens when I try to manually INSERT the row.)
>
> When I display my variable settings with "SHOW variables LIKE
> 'c%';" I receive the following result:
>
>
> +--------------------------+----------------------------+
> | Variable_name | Value |
> +--------------------------+----------------------------+
> | character_set_client | utf8 |
> | character_set_connection | utf8 |
> | character_set_database | utf8 |
> | character_set_filesystem | binary |
> | character_set_results | utf8 |
> | character_set_server | latin1 |
> | character_set_system | utf8 |
> | character_sets_dir | /usr/share/mysql/charsets/ |
> | collation_connection | utf8_general_ci |
> | collation_database | utf8_general_ci |
> | collation_server | latin1_swedish_ci |
> | completion_type | 0 |
> | concurrent_insert | 1 |
> | connect_timeout | 5 |
> +--------------------------+----------------------------+
> 14 rows in set (0.02 sec)
>
> From this I conclude it is the server setting, which causes the trouble
> here. When I manipulate the settings manually from the client (with "SET
> character_set_server=utf8; SET collation_server=utf8_general_ci;") the
> values do change, but not the behaviour. But this can be expected, since
> the server is already up and running with the wrong settings.
>
> Does anybody know how I restart my mysql-server with the correct
> character and collation settings, if this is the cause for my problem,
> or if there might be any other reason for it. My mysql version is
> 5.0.26-12, running on a Suse Linux 10.2.
>
> Best regards,
> H.

Try using the SET NAMES 'utf8' statement [1] to tell MySQL that your client
is sending data in UTF-8. I believe that as your server is latin1, it will
assume this is the character set used by the command line client.

[1] http://dev.mysql.com/doc/refman/5.0/...onnection.html

Edward

Edward Kay schrieb:
> Try using the SET NAMES 'utf8' statement [1] to tell MySQL that your client
> is sending data in UTF-8. I believe that as your server is latin1, it will
> assume this is the character set used by the command line client.
>
> [1] http://dev.mysql.com/doc/refman/5.0/...onnection.html
>
> Edward
>
>
I tried, but the behaviour remains the same. I guess my first option
should be to force the server to use utf8 as default. I would have no
problems in doing so, because I hardly use any latin1 anymore.

But how would I do so? The mysqld has some options for this, but I
didn't manage yet to simply restart it with new options, because it is
started somewhere in the init.d-procedure of my Linux system and even as
root I cannot simply invoke it from the command line.

Best regards,
Harald


--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

Edward Kay napsal(a):
>> I would like to import data from a utf8-coded comma seperated file. I
>> created my database with "DEFAULT CHARACTER SET utf8 COLLATE
>> utf8_general_ci" and I started my mysql-client with the
>> --default-character-set=utf8 option. Nevertheless, when I input primary
>> key fields, which differ only in one umlaut character (e.g. "achten" and
>> "ächten") I get the following error message:
>>
>> ERROR 1062 (23000): Duplicate entry 'ächten' for key 1
>>
>> (Same thing happens when I try to manually INSERT the row.)
>>
>> When I display my variable settings with "SHOW variables LIKE
>> 'c%';" I receive the following result:
>>
>>
>> +--------------------------+----------------------------+
>> | Variable_name | Value |
>> +--------------------------+----------------------------+
>> | character_set_client | utf8 |
>> | character_set_connection | utf8 |
>> | character_set_database | utf8 |
>> | character_set_filesystem | binary |
>> | character_set_results | utf8 |
>> | character_set_server | latin1 |
>> | character_set_system | utf8 |
>> | character_sets_dir | /usr/share/mysql/charsets/ |
>> | collation_connection | utf8_general_ci |
>> | collation_database | utf8_general_ci |
>> | collation_server | latin1_swedish_ci |
>> | completion_type | 0 |
>> | concurrent_insert | 1 |
>> | connect_timeout | 5 |
>> +--------------------------+----------------------------+
>> 14 rows in set (0.02 sec)
>>
>> From this I conclude it is the server setting, which causes the trouble
>> here. When I manipulate the settings manually from the client (with "SET
>> character_set_server=utf8; SET collation_server=utf8_general_ci;") the
>> values do change, but not the behaviour. But this can be expected, since
>> the server is already up and running with the wrong settings.
>>
>> Does anybody know how I restart my mysql-server with the correct
>> character and collation settings, if this is the cause for my problem,
>> or if there might be any other reason for it. My mysql version is
>> 5.0.26-12, running on a Suse Linux 10.2.
>>
>> Best regards,
>> H.
>>
>
> Try using the SET NAMES 'utf8' statement [1] to tell MySQL that your client
> is sending data in UTF-8. I believe that as your server is latin1, it will
> assume this is the character set used by the command line client.
>
> [1] http://dev.mysql.com/doc/refman/5.0/...onnection.html
>
> Edward
>
>
>
From my experience SET NAMES doesn't work, but character set of the
database must be same as file's character set and this condition is OK.
For sure I used script:

USE database_with_correct_charset;
LOAD DATA ...;

And this worked fine for files with cp1250 and also with keybcs2 (I had
two databases, of course)

HTH,
Dusan

I used the latin collation and latin db character set, to load data similar
to you, and we got this done correctly.

If your inserting multi byte data, then u need to set the above parameters.
This was one of the solutions give by mysql, i am not able to get the url. I
will search my notes and get back to you all.

regards
anandkl


On 8/30/07, Dušan Pavlica <pavlica@unidataz.cz> wrote:
>
> Edward Kay napsal(a):
> >> I would like to import data from a utf8-coded comma seperated file. I
> >> created my database with "DEFAULT CHARACTER SET utf8 COLLATE
> >> utf8_general_ci" and I started my mysql-client with the
> >> --default-character-set=utf8 option. Nevertheless, when I input primary
> >> key fields, which differ only in one umlaut character (e.g. "achten"
> and
> >> "ächten") I get the following error message:
> >>
> >> ERROR 1062 (23000): Duplicate entry 'ächten' for key 1
> >>
> >> (Same thing happens when I try to manually INSERT the row.)
> >>
> >> When I display my variable settings with "SHOW variables LIKE
> >> 'c%';" I receive the following result:
> >>
> >>
> >> +--------------------------+----------------------------+
> >> | Variable_name | Value |
> >> +--------------------------+----------------------------+
> >> | character_set_client | utf8 |
> >> | character_set_connection | utf8 |
> >> | character_set_database | utf8 |
> >> | character_set_filesystem | binary |
> >> | character_set_results | utf8 |
> >> | character_set_server | latin1 |
> >> | character_set_system | utf8 |
> >> | character_sets_dir | /usr/share/mysql/charsets/ |
> >> | collation_connection | utf8_general_ci |
> >> | collation_database | utf8_general_ci |
> >> | collation_server | latin1_swedish_ci |
> >> | completion_type | 0 |
> >> | concurrent_insert | 1 |
> >> | connect_timeout | 5 |
> >> +--------------------------+----------------------------+
> >> 14 rows in set (0.02 sec)
> >>
> >> From this I conclude it is the server setting, which causes the trouble
> >> here. When I manipulate the settings manually from the client (with
> "SET
> >> character_set_server=utf8; SET collation_server=utf8_general_ci;")the
> >> values do change, but not the behaviour. But this can be expected,
> since
> >> the server is already up and running with the wrong settings.
> >>
> >> Does anybody know how I restart my mysql-server with the correct
> >> character and collation settings, if this is the cause for my problem,
> >> or if there might be any other reason for it. My mysql version is
> >> 5.0.26-12, running on a Suse Linux 10.2.
> >>
> >> Best regards,
> >> H.
> >>
> >
> > Try using the SET NAMES 'utf8' statement [1] to tell MySQL that your
> client
> > is sending data in UTF-8. I believe that as your server is latin1, it
> will
> > assume this is the character set used by the command line client.
> >
> > [1] http://dev.mysql.com/doc/refman/5.0/...onnection.html
> >
> > Edward
> >
> >
> >
> From my experience SET NAMES doesn't work, but character set of the
> database must be same as file's character set and this condition is OK.
> For sure I used script:
>
> USE database_with_correct_charset;
> LOAD DATA ...;
>
> And this worked fine for files with cp1250 and also with keybcs2 (I had
> two databases, of course)
>
> HTH,
> Dusan
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

> Does anybody know how I restart my mysql-server with the correct

> character and collation settings, if this is the cause for my problem,
> or if there might be any other reason for it. My mysql version is
> 5.0.26-12, running on a Suse Linux 10.2.
>
Meanwhile I managed to change the server settings by adding the
following lines in the [mysqld] section of my /etc/my.cnf:

collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake

Now the server also runs with utf8, but when loading the file, I still
get the same error message...

Regards,
H.


--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

Ananda Kumar schrieb:
> I used the latin collation and latin db character set, to load data
> similar to you, and we got this done correctly.
>
> If your inserting multi byte data, then u need to set the above
> parameters. This was one of the solutions give by mysql, i am not able
> to get the url. I will search my notes and get back to you all.
When I load my multibyte data using latin character set and collation, I
get the data loaded correctly into the database without error msg, but
when I want to display it with "SELECT * from table" I don't get it
displayed correctly, even after I change the settings back to utf8 after
the import.

Best regards,
H.



--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

strange. did u exit and reconnect and did the select?

On 8/30/07, Harald Vajkonny <vajkonny@t-online.de> wrote:
>
> Ananda Kumar schrieb:
> > I used the latin collation and latin db character set, to load data
> > similar to you, and we got this done correctly.
> >
> > If your inserting multi byte data, then u need to set the above
> > parameters. This was one of the solutions give by mysql, i am not able
> > to get the url. I will search my notes and get back to you all.
> When I load my multibyte data using latin character set and collation, I
> get the data loaded correctly into the database without error msg, but
> when I want to display it with "SELECT * from table" I don't get it
> displayed correctly, even after I change the settings back to utf8 after
> the import.
>
> Best regards,
> H.
>
>
>
> --
> "Wouldn't the sentence 'I want to put a hyphen between the words Fish
> and And and And and Chips in my Fish-And-Chips sign' have been clearer
> if quotation marks had been placed before Fish, and between Fish and
> and, and and and And, and And and and, and and and And, and And and
> and, and and and Chips, as well as after Chips?"
>
>

Ananda Kumar schrieb:
> strange. did u exit and reconnect and did the select?
>
Yes, I tried it once more. I have to put the USE command before I change
session settings to "latin" to make it work without error (otherwise I
still get the duplicate message). But even after exiting I get the
national characters displayed as two (or more) bytes.

Regards,
H.


--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

Ananda Kumar schrieb:
> strange. did u exit and reconnect and did the select?
>
Yes, I tried it once more. I have to put the USE command before I change

session settings to "latin" to make it work without error (otherwise I
still get the duplicate message). But even after exiting I get the
national characters displayed as two (or more) bytes.

Regards,
H.





--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

Harald Vajkonny napsal(a):
> Ananda Kumar schrieb:
>
>> strange. did u exit and reconnect and did the select?
>>
>>
> Yes, I tried it once more. I have to put the USE command before I change
>
> session settings to "latin" to make it work without error (otherwise I
> still get the duplicate message). But even after exiting I get the
> national characters displayed as two (or more) bytes.
>
Try to convert file to latin1, if it's possible, create database with
latin1 charset, create table with required structure (you can set utf8
charset to string fields ) and then load data. What client do you use to
check whether data are OK? I can recommend MySQL Query Browser where I
don't have to care about setting charset and collation parameters.

HTH,
Dusan

Dušan Pavlica schrieb:
> Try to convert file to latin1, if it's possible, create database with
> latin1 charset, create table with required structure (you can set utf8
> charset to string fields ) and then load data.
I can not convert the file into latin1, because it is multilingual (i.e.
European, Japanese, Korean etc.).

> What client do you use to check whether data are OK? I can recommend
> MySQL Query Browser where I don't have to care about setting charset
> and collation parameters.
>
I checked the file in MySQL Query Browser too, but even there I get it
wrong, when I imported it with a latin session. I get it displayed
right, when I import it with utf8, but then I have the message with
duplicate keys and get only half of the data.

Regards,
H.

--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

Harald Vajkonny napsal(a):
> Dušan Pavlica schrieb:
>
>> Try to convert file to latin1, if it's possible, create database with
>> latin1 charset, create table with required structure (you can set utf8
>> charset to string fields ) and then load data.
>>
> I can not convert the file into latin1, because it is multilingual (i.e.
> European, Japanese, Korean etc.).
>
>
>> What client do you use to check whether data are OK? I can recommend
>> MySQL Query Browser where I don't have to care about setting charset
>> and collation parameters.
>>
>>
> I checked the file in MySQL Query Browser too, but even there I get it
> wrong, when I imported it with a latin session. I get it displayed
> right, when I import it with utf8, but then I have the message with
> duplicate keys and get only half of the data.
>
What's the charset and collation of your primary field in the table?

Dusan

Dušan Pavlica schrieb:
> What's the charset and collation of your primary field in the table?
With which command do I get the charset and collation information of a
single field in a table? SHOW CREATE TABLE returns:
....
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

But I believe it is utf8, because when I enter Japanese or Russian text
in this field by INSERT it is displayed right. It just can't distinguish
between "a", "ä" or "á", but treats it as if they were "a".

In doing this I got another idea: Does anybody know the difference
between the collations utf8_general_ci, utf8_unicode_ci and utf8_bin?
I'll try these first and then get back to you about the results.

Best regards,
H.

--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

Harald Vajkonny schrieb:
> In doing this I got another idea: Does anybody know the difference
> between the collations utf8_general_ci, utf8_unicode_ci and utf8_bin?
> I'll try these first and then get back to you about the results.
>
That was it. If I choose utf8_bin as collation everything works fine!

Best regards,
H.

--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

Hi Harald,
So you set the

collation_database=utf8_bin, what was your character_set_database values.

regards
anandkl


On 8/30/07, Harald Vajkonny <vajkonny@t-online.de> wrote:
>
> Harald Vajkonny schrieb:
> > In doing this I got another idea: Does anybody know the difference
> > between the collations utf8_general_ci, utf8_unicode_ci and utf8_bin?
> > I'll try these first and then get back to you about the results.
> >
> That was it. If I choose utf8_bin as collation everything works fine!
>
> Best regards,
> H.
>
> --
> "Wouldn't the sentence 'I want to put a hyphen between the words Fish
> and And and And and Chips in my Fish-And-Chips sign' have been clearer
> if quotation marks had been placed before Fish, and between Fish and
> and, and and and And, and And and and, and and and And, and And and
> and, and and and Chips, as well as after Chips?"
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

Hi Ananda,

Ananda Kumar schrieb:
>
> So you set the collation_database=utf8_bin, what was your
> character_set_database values.
character_set_database is "utf8". The collation utf8_bin slows down
queries, but is necessary in dealing with multilingual information.
utf8_general_ci is faster, but can not distinguish in keys between
symbols which are sorted at the same position in national character
sets, like e.g. German "a" and "ä", or French "e" and "é".

Regards,
Harald

--
"Wouldn't the sentence 'I want to put a hyphen between the words Fish
and And and And and Chips in my Fish-And-Chips sign' have been clearer
if quotation marks had been placed before Fish, and between Fish and
and, and and and And, and And and and, and and and And, and And and
and, and and and Chips, as well as after Chips?"

Okie, i will also try this, as we also load data from a flat file.

regards
anandkl


On 8/31/07, Harald Vajkonny <vajkonny@t-online.de> wrote:
>
> Hi Ananda,
>
> Ananda Kumar schrieb:
> >
> > So you set the collation_database=utf8_bin, what was your
> > character_set_database values.
> character_set_database is "utf8". The collation utf8_bin slows down
> queries, but is necessary in dealing with multilingual information.
> utf8_general_ci is faster, but can not distinguish in keys between
> symbols which are sorted at the same position in national character
> sets, like e.g. German "a" and "ä", or French "e" and "é".
>
> Regards,
> Harald
>
> --
> "Wouldn't the sentence 'I want to put a hyphen between the words Fish
> and And and And and Chips in my Fish-And-Chips sign' have been clearer
> if quotation marks had been placed before Fish, and between Fish and
> and, and and and And, and And and and, and and and And, and And and
> and, and and and Chips, as well as after Chips?"
>
>