List:General Discussion« Previous MessageNext Message »
From:mysql Date:August 31 2010 12:30pm
Subject:collation problems
View as plain text  
Hi listers
mysql server here is

mysql-server-5.1.48-2.fc13.x86_64

this morning i created a message with a literal string in chinese in it. 
the messages in the application i used are stored in a mysql database, 
when you submit them, like in a "sent" folder.
With this chinese literal in it, i, however, got
ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'

when i sent the message.

without the chinese literal, the message was stored in the mysql db 
correctly.

i, then, changed the connection names to utf8 and collation to utf8_bin 
and then the session parameters looked like:

mysql> set names "utf8";
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_connection = "utf8_bin";
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like "character_set%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show session variables like "collation%";
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_bin          |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql>

i thought, if you set the collation_connection to utf8_bin, you may send 
fairly everything to mysql.


but still, when i tried to concat the new string (including the chinese 
characers) i got:

mysql> update suomi_contacts2 set history = concat(now(), ' ', '' 
,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi 
Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will 
ever happen to you.\r\n> 葛斯克 愛德華 / 
台北市八德路四段\r\n>\r\n\r\n 
\r\nsuomi\r\n', ' ', '----------------------- ', history) where counter 
= 1127;
ERROR 1271 (HY000): Illegal mix of collations for operation 'concat'



Question: What did i miss? what do i have to change?

Thanks in advance

suomi




Thread
collation problemsmysql31 Aug
  • Re: collation problemsAnanda Kumar31 Aug
    • Re: collation problemsmysql31 Aug
      • Re: collation problemsAnanda Kumar31 Aug
        • Re: collation problemsmysql31 Aug
          • Re: collation problemsAnanda Kumar31 Aug
            • Re: collation problemsmysql31 Aug
            • [SOLVED] Re: collation problemsmysql31 Aug