Re: LOAD DATA INTO doesn't work correctly with utf8
Before you import at the mysql prompt set below variables and then try again to load set session max_error_count=50; set session collation_database=latin1_swedish_ci; set session character_set_database=latin1; regards anandkl On 8/30/07, Harald Vajkonny [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Ananda Kumar schrieb: Before you import at the mysql prompt set below variables and then try again to load set session max_error_count=50; 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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INTO doesn't work correctly with utf8
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/en/charset-connection.html Edward -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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/en/charset-connection.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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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/en/charset-connection.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/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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 [EMAIL PROTECTED] 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/en/charset-connection.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/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
strange. did u exit and reconnect and did the select? On 8/30/07, Harald Vajkonny [EMAIL PROTECTED] 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?
Re: LOAD DATA INTO doesn't work correctly with utf8
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
