From: Ananda Kumar Date: August 31 2010 1:17pm Subject: Re: collation problems List-Archive: http://lists.mysql.com/mysql/222750 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=00163645901ef8eedb048f1e65b1 --00163645901ef8eedb048f1e65b1 Content-Type: text/plain; charset=Big5 Content-Transfer-Encoding: quoted-printable can u please give the output of desc suomi_contacts2; 2010/8/31 mysql > 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 se= t > > 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 i= n > > 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 =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 wil= l > > ever happen to you.\r\n> =B8=AF=B4=B5=A7J =B7R=BCw=B5=D8 / =A5x=A5_= =A5=AB=A4K=BCw=B8=F4=A5|=ACq\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 > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Danandkl@stripped > > --00163645901ef8eedb048f1e65b1--