List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:August 31 2010 1:17pm
Subject:Re: collation problems
View as plain text  
can u please give the output of
desc suomi_contacts2;

2010/8/31 mysql <mysql@stripped>

> 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
> >
> >
>
>
> --
> 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