can u please give the output of desc suomi_contacts2; 2010/8/31 mysql <my...@ayni.com>
> 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 <my...@ayni.com > > <mailto:my...@ayni.com>> 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=anan...@gmail.com > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com > >