Illegal mix of collations mysql error?

Question

I am using geoloaction to find the current location of user which returns the xml data

<geonames>
    <geoname>
        <toponymName>Gulbahar</toponymName>
        <name>Golīmar</name>
        <lat>24.8922</lat>
        <lng>67.0287</lng>
        <geonameId>1346867</geonameId>
        <countryCode>PK</countryCode>
        <countryName>Pakistan</countryName>
        <fcl>P</fcl>
        <fcode>PPLX</fcode>
        <distance>0.14608</distance>
    </geoname>
</geonames>

now i want <name>Golīmar</name> which has a special character and on the basis of that name i have to run the query to fetch more results except this one but it generates some sort of collation errors

SELECT DISTINCT country
FROM   propertydetails
WHERE  country NOT IN ( "Golīmar" )

Error:Illegal mix of collations (latin1_swedish_ci,IMPLICIT) AND (utf8_general_ci,COERCIBLE) FOR operation

i have seen many stack pos but couldnt find a way to resolve

Check the collation of your table: SHOW CREATE TABLE propertydetails; Then you will probably have to switch the collation from latin1 to utf-8, or thos of the database connection itself. Let me know what is your table encoding. — Parallelis, Aug 30 '12 at 05:19
SHOW CREATE TABLE propertydetails; this command shows the table name no other details — M Khalid Junaid, Aug 30 '12 at 05:25

score 0 · Answer 1 · answered Aug 30 '12 at 05:34

0

Your table collation and your connection collation don't match closely enough to make the query work. Make sure that they are both the same, preferably utf8_general_ci.

answered Aug 30 '12 at 05:34

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

your connection collation?? i cant get this can you explain more about this – M Khalid Junaid Aug 30 '12 at 05:47
[MySQL documentation, §10.1.4, "Connection Character Sets and Collations"](http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html) – Ignacio Vazquez-Abrams Aug 30 '12 at 05:49
my charset is utf8 and collation is set to utf8_general_ci – M Khalid Junaid Aug 30 '12 at 05:55

score 0 · Accepted Answer · answered Aug 30 '12 at 08:30

0

I have solved my above problem by

SELECT DISTINCT country
FROM   propertydetails
WHERE  country NOT IN ( _latin1 "Golīmar" )

and the charset and collations of both connection and table to latin1 , latin1_swedish_ci

answered Aug 30 '12 at 08:30

M Khalid Junaid

63,861
10
90
118

Illegal mix of collations mysql error?

2 Answers2

Linked