collation problems

am 31.08.2010 14:30:19 von mysql

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=gcdmg-mysql-2@m.gmane.org

Re: collation problems

am 31.08.2010 14:52:46 von Ananda Kumar

--0016364ef610cd7eaa048f1e0e42
Content-Type: text/plain; charset=Big5
Content-Transfer-Encoding: quoted-printable

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 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, wh=
en
> 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 a=
nd
> then the session parameters looked like:
>
> mysql> set names "utf8";
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> set collation_connection =3D "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 =3D 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> ¸¯´µ=A7J =B7R=BCwµØ / =A5x=A5_¥«=A4K=
=BCw¸ô=A5|q\r\n>\r\n\r\n \r\nsuomi\r\n', ' ',
> '----------------------- ', history) where counter =3D 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=3Danandkl@gmail.com
>
>

--0016364ef610cd7eaa048f1e0e42--

Re: collation problems

am 31.08.2010 15:01:26 von mysql

Re: collation problems

am 31.08.2010 15:17:09 von Ananda Kumar

Re: collation problems

am 31.08.2010 15:24:20 von mysql

Re: collation problems

am 31.08.2010 15:41:05 von Ananda Kumar

--0016e645499e963065048f1ebb06
Content-Type: text/plain;
did u try changing and try the update.

2010/8/31 mysql

> On 2010-08-31 15:17, > > desc suomi_contacts2;
>
> mysql> desc suomi_contacts2;
>
> +------------------+---- > | Field | Type > Extra |
>
> +------------------+---- > | name | text > |
> | firm | text > |
> | title | text > |
> | phone | text > |
> | phone_std | text > |
> | fax | text > |
> | mail | text > |
> | standard_mail | text > |
> | comment | text > |
> | status | text > |
> | url | text > |
> | businesscategory | text > |
> | address | text > |
> | addon | text > |
> | givenname | text > |
> | history | longtext > |
> | favorit | text > |
> | last_update | timestamp > on update CURRENT_TIMESTAMP |
> | task_link | int(11) > |
> | counter > auto_increment |
>
> +------------------+---- > 20 rows in set (0.00 sec)
>
> mysql>
>
> --
> MySQL General Mailing List
> For list archives: > To unsubscribe: >
>

--0016e645499e963065048f1ebb06-- charset=ISO-8859-1
the collation for history column to UTF8
Ananda Kumar wrote:
--------------+------+-----+-------- -----------+-----------------------------+
| Null | Key | Default |
--------------+------+-----+-------- -----------+-----------------------------+
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| YES | | NULL |
| NO | | CURRENT_TIMESTAMP |
| YES | | NULL |
| int(10) unsigned | NO | PRI | NULL |
--------------+------+-----+-------- -----------+-----------------------------+
http://lists.mysql.com/mysql
http://lists.mysql.com/mysql?unsub=anandkl@gmail.com

Re: collation problems

am 31.08.2010 16:51:22 von mysql

Hi Ananda
not sofar. But if you recommend it, i will give it a try.

thanks so much.

suomi

On 2010-08-31 15:41, Ananda Kumar wrote:
> did u try changing the collation for history column to UTF8
> and try the update.
>
> 2010/8/31 mysql >
>
> On 2010-08-31 15:17, Ananda Kumar wrote:
> > desc suomi_contacts2;
>
> mysql> desc suomi_contacts2;
> +------------------+------------------+------+-----+-------- -----------+-----------------------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +------------------+------------------+------+-----+-------- -----------+-----------------------------+
> | name | text | YES | | NULL |
> |
> | firm | text | YES | | NULL |
> |
> | title | text | YES | | NULL |
> |
> | phone | text | YES | | NULL |
> |
> | phone_std | text | YES | | NULL |
> |
> | fax | text | YES | | NULL |
> |
> | mail | text | YES | | NULL |
> |
> | standard_mail | text | YES | | NULL |
> |
> | comment | text | YES | | NULL |
> |
> | status | text | YES | | NULL |
> |
> | url | text | YES | | NULL |
> |
> | businesscategory | text | YES | | NULL |
> |
> | address | text | YES | | NULL |
> |
> | addon | text | YES | | NULL |
> |
> | givenname | text | YES | | NULL |
> |
> | history | longtext | YES | | NULL |
> |
> | favorit | text | YES | | NULL |
> |
> | last_update | timestamp | NO | | CURRENT_TIMESTAMP |
> on update CURRENT_TIMESTAMP |
> | task_link | int(11) | YES | | NULL |
> |
> | counter | int(10) unsigned | NO | PRI | NULL |
> auto_increment |
> +------------------+------------------+------+-----+-------- -----------+-----------------------------+
> 20 rows in set (0.00 sec)
>
> mysql>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

[SOLVED] Re: collation problems

am 31.08.2010 17:12:17 von mysql

Hi Ananda
that worked fine:

mysql> alter table suomi_contacts2 modify history longtext character set
utf8 collate utf8_bin;
Query OK, 6327 rows affected (0.34 sec)
Records: 6327 Duplicates: 0 Warnings: 0

mysql>

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\n044 280 22 44\r\n079 239 29 01\r\n', ' ',
'----------------------- ', history) where counter = 1127;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>

Thank you very much

suomi

On 2010-08-31 15:41, Ananda Kumar wrote:
> did u try changing the collation for history column to UTF8
> and try the update.
>
> 2010/8/31 mysql >
>
> On 2010-08-31 15:17, Ananda Kumar wrote:
> > desc suomi_contacts2;
>
> mysql> desc suomi_contacts2;
> +------------------+------------------+------+-----+-------- -----------+-----------------------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +------------------+------------------+------+-----+-------- -----------+-----------------------------+
> | name | text | YES | | NULL |
> |
> | firm | text | YES | | NULL |
> |
> | title | text | YES | | NULL |
> |
> | phone | text | YES | | NULL |
> |
> | phone_std | text | YES | | NULL |
> |
> | fax | text | YES | | NULL |
> |
> | mail | text | YES | | NULL |
> |
> | standard_mail | text | YES | | NULL |
> |
> | comment | text | YES | | NULL |
> |
> | status | text | YES | | NULL |
> |
> | url | text | YES | | NULL |
> |
> | businesscategory | text | YES | | NULL |
> |
> | address | text | YES | | NULL |
> |
> | addon | text | YES | | NULL |
> |
> | givenname | text | YES | | NULL |
> |
> | history | longtext | YES | | NULL |
> |
> | favorit | text | YES | | NULL |
> |
> | last_update | timestamp | NO | | CURRENT_TIMESTAMP |
> on update CURRENT_TIMESTAMP |
> | task_link | int(11) | YES | | NULL |
> |
> | counter | int(10) unsigned | NO | PRI | NULL |
> auto_increment |
> +------------------+------------------+------+-----+-------- -----------+-----------------------------+
> 20 rows in set (0.00 sec)
>
> mysql>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org