List:General Discussion« Previous MessageNext Message »
From:mysql Date:August 31 2010 1:01pm
Subject:Re: collation problems
View as plain text  
Hi Ananda

table structure is:

mysql> show full columns from suomi_contacts2;
+------------------+------------------+-------------------+------+-----+-------------------+-----------------------------+---------------------------------+---------+
| Field            | Type             | Collation         | Null | Key |
Default           | Extra                       | Privileges
          | Comment |
+------------------+------------------+-------------------+------+-----+-------------------+-----------------------------+---------------------------------+---------+
| name             | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| firm             | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| title            | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| phone            | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| phone_std        | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| fax              | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| mail             | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| standard_mail    | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| comment          | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| status           | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| url              | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| businesscategory | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| address          | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| addon            | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| givenname        | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| history          | longtext         | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| favorit          | text             | latin1_swedish_ci | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| last_update      | timestamp        | NULL              | NO   |     |
CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
select,insert,update,references |         |
| task_link        | int(11)          | NULL              | YES  |     |
NULL              |                             |
select,insert,update,references |         |
| counter          | int(10) unsigned | NULL              | NO   | PRI |
NULL              | auto_increment              |
select,insert,update,references |         |
+------------------+------------------+-------------------+------+-----+-------------------+-----------------------------+---------------------------------+---------+
20 rows in set (0.00 sec)

mysql>

suomi

On 2010-08-31 14:52, Ananda Kumar wrote:
> can u please list out the table structure...as collation can also be set 
> at column level
> regards
> anandkl
> 
> On Tue, Aug 31, 2010 at 6:00 PM, mysql <mysql@stripped 
> <mailto:mysql@stripped>> wrote:
> 
>     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
> 
> 
> 
> 
> 
>     -- 
>     MySQL General Mailing List
>     For list archives: http://lists.mysql.com/mysql
>     To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> 
> 

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