MySQL: Illegal mix of collations?

KOTT · 1-12-05, 09:21 AM

I have just ran this query on my MySQL database:

Code:

SELECT * FROM `ibf_members` WHERE `email` LIKE '%aquarist%'

And it has given this error:

Code:

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'

Something wrong methinks?

1-13-05, 02:14 AM

I got the same error. when you first login to phpmyadmin, you can select this option:
MySQL connection collation: and there is a drop down. This type has to be set as the same type as your table entries otherwise all your mysql queries get that error. Either you can change the type of each field in the tables to utf blablabla or you can change your MySQL connection collation to be the latin sweidish blablabla.

jerometam · 1-13-05, 03:39 AM

I got the same error, I have a archive database that is originally stored in big5 charset, when I import the database in latin1_sewdish_ci, all my data change to question marks...

If I import the data in big5, I have the same problem with KOTT and heemer:

Illegal mix of collations (big5_chinese_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='

Anybody can help?

KOTT · 1-13-05, 09:56 AM

Question now is, is there a quick way to change all my tables and fields collation? and how come my table's and fields are set to latin1_swedish_ci in the first place?

KOTT · 1-13-05, 11:07 AM

I have changed my MySQL connection collation to latin1_swedish_ci for now so I can run the querys I need to, but what else will this affect? anything? if so then we could do with a way of changing all the table's/fields.

devinemke · 1-13-05, 11:18 AM

mySQL 4.1 has extended support for multiple character sets. there are the character sets themselves and the "collations" which are sets of rules governing how strings are compared. every character set has a subset of collations (one being the default). character sets and collations can set at several levels: server, database, table, column. these heirachical settings govern the default set/collation for each subsetting. in other words if the default server character set is latin5 (ISO 8859-9 Turkish) then all databases/tables/columns will inheirts that set. the server level character set for powweb's mySQL servers is UTF-8 Unicode (utf8) with the default collation of utf8_general_ci.

databases created in pre 4.1 versions of mySQL will likely have the latin1 (ISO 8859-1 West European) character set with it's default collation of latin1_swedish_ci. to read more about all this goto http://dev.mysql.com/doc/mysql/en/Charset.html.

Retsep · 1-17-05, 01:23 AM

I am having the same problem but only when I try to run queries in phpmyadmin. From what I can tell everything is set to latin1_swedish_ci, but when I click on the SQL tab and run a query there it says:

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Is there a switch somewhere that I missed to change the default? I'm 99% sure that everything says latin1_swedish_ci throughout the database... If I run the exact same query from php (not in phpmyadmin) it works fine.

Retsep · 1-17-05, 01:27 AM

Nevermind...it helps to thoroughly read the posts

Thanks heemer

2-4-05, 05:22 AM

i have this problem too, who can help~~

1-13-05, 09:56 AM	#4
KOTT Join Date: Sep 2003 Location: Cleveleys, UK Posts: 73 Reputation: 5	Question now is, is there a quick way to change all my tables and fields collation? and how come my table's and fields are set to latin1_swedish_ci in the first place? __________________ UK Aquarist Forums

1-13-05, 11:07 AM	#5
KOTT Join Date: Sep 2003 Location: Cleveleys, UK Posts: 73 Reputation: 5	I have changed my MySQL connection collation to latin1_swedish_ci for now so I can run the querys I need to, but what else will this affect? anything? if so then we could do with a way of changing all the table's/fields. __________________ UK Aquarist Forums

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

1-12-05, 09:21 AM	#1
KOTT Join Date: Sep 2003 Location: Cleveleys, UK Posts: 73 Reputation: 5	MySQL: Illegal mix of collations? I have just ran this query on my MySQL database: Code: SELECT * FROM `ibf_members` WHERE `email` LIKE '%aquarist%' And it has given this error: Code: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like' Something wrong methinks? __________________ UK Aquarist Forums

1-13-05, 02:14 AM	#2
heemer Posts: n/a	I got the same error. when you first login to phpmyadmin, you can select this option: MySQL connection collation: and there is a drop down. This type has to be set as the same type as your table entries otherwise all your mysql queries get that error. Either you can change the type of each field in the tables to utf blablabla or you can change your MySQL connection collation to be the latin sweidish blablabla.

1-13-05, 03:39 AM	#3
jerometam Registered Join Date: Jan 2005 Location: Hong Kong Posts: 11 Reputation: 0	I got the same error, I have a archive database that is originally stored in big5 charset, when I import the database in latin1_sewdish_ci, all my data change to question marks... If I import the data in big5, I have the same problem with KOTT and heemer: Illegal mix of collations (big5_chinese_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '=' Anybody can help?

1-13-05, 11:18 AM	#6
devinemke broken tv Join Date: Dec 2001 Location: NYC Posts: 268 Reputation: 13	mySQL 4.1 has extended support for multiple character sets. there are the character sets themselves and the "collations" which are sets of rules governing how strings are compared. every character set has a subset of collations (one being the default). character sets and collations can set at several levels: server, database, table, column. these heirachical settings govern the default set/collation for each subsetting. in other words if the default server character set is latin5 (ISO 8859-9 Turkish) then all databases/tables/columns will inheirts that set. the server level character set for powweb's mySQL servers is UTF-8 Unicode (utf8) with the default collation of utf8_general_ci. databases created in pre 4.1 versions of mySQL will likely have the latin1 (ISO 8859-1 West European) character set with it's default collation of latin1_swedish_ci. to read more about all this goto http://dev.mysql.com/doc/mysql/en/Charset.html.

1-17-05, 01:23 AM	#7
Retsep Registered Join Date: Jan 2005 Location: Aurora, OH Posts: 20 Reputation: 0	I am having the same problem but only when I try to run queries in phpmyadmin. From what I can tell everything is set to latin1_swedish_ci, but when I click on the SQL tab and run a query there it says: #1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' Is there a switch somewhere that I missed to change the default? I'm 99% sure that everything says latin1_swedish_ci throughout the database... If I run the exact same query from php (not in phpmyadmin) it works fine.

1-17-05, 01:27 AM	#8
Retsep Registered Join Date: Jan 2005 Location: Aurora, OH Posts: 20 Reputation: 0	Nevermind...it helps to thoroughly read the posts Thanks heemer

2-4-05, 05:22 AM	#9
magicbox Posts: n/a	i have this problem too, who can help~~