• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8448
  • Last Modified:

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

I'm getting the following error message when I run the query below.

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

First, I'm wondering if there's a better way to write my query.  Look at the Where clause. Notice I'm trying to combine wildcards with a field name as the search criteria. I was able to get this to work in Access, but I'm unsure of the syntax in MySQL.  

If there's no better syntax, then how do I fix the error message?  In one post on EE someone suggested using _latin1, but I don't know how to apply that to this query.  Alternatively, I could potentially change my data to latin1... but I don't even know where I'm using UTF8 b/c my tables are latin1 (as far as I can tell).

SELECT shipping_details.orders_id, shipping_criteria.Hold_Till_Next_Week, shipping_criteria.Box_Type
FROM shipping_criteria,
      (
            SELECT Orders.orders_id,
            CASE
                  WHEN Orders.shipping_method like '%ground%' THEN 'GND'
                  WHEN Orders.shipping_method like '%2nd day%' THEN '2DA'
                  WHEN Orders.shipping_method like '%next day%' THEN '1DA'
                  WHEN Orders.shipping_method like '%canada%' THEN 'STD'
                  WHEN Orders.shipping_method like '%expedited%' THEN 'XPD'
                  WHEN Orders.shipping_method like '%Free Shipping%' THEN 'GND'
              ELSE Orders.shipping_method
            END as shipping_type,
            DATE_FORMAT(now(),'%a') as Process_Day,
            CASE
                  WHEN Orders.shipping_allowed Like '%GND%' Or Orders.shipping_allowed Like '%XPD%' Or Orders.shipping_allowed Like '%STD%' THEN 'Cold'
              Else 'Warm'
            END as Destination
            FROM Orders
      ) AS shipping_details
WHERE shipping_criteria.Active=1 AND shipping_criteria.Destination Like CONCAT('%', shipping_details.destination, '%') AND shipping_criteria.Method Like CONCAT('%', shipping_details.shipping_type, '%') AND shipping_criteria.Day_Sent_To_CCG Like CONCAT('%', shipping_details.Process_Day, '%');
0
JoeFletcher
Asked:
JoeFletcher
  • 6
  • 6
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'

to solve that, use the COLLATE syntax:
http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html


0
 
Bradley HaynesCommented:
This is a common error when multiple character sets are in affect in a database. Queries run on tables that don't have the same Collation produce this. Change the character set on all your tables to match.

0
 
JoeFletcherAuthor Commented:
Thanks!  I still don't have it though.  Can you hold my hand and take a look and see what I'm doing wrong here?  I get an error message "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT('%', shipping_details.destination, '%') AND shipping_criteria.Method Like' at line 21" so I don't know if it's the collation stuff or my wildcard syntax.  Thanks again!

WHERE shipping_criteria.Active=1 AND shipping_criteria.Destination Like _latin1 CONCAT('%', shipping_details.destination, '%') AND shipping_criteria.Method Like _latin1 CONCAT('%', shipping_details.shipping_type, '%') AND shipping_criteria.Day_Sent_To_CCG Like _latin1 CONCAT('%', shipping_details.Process_Day, '%') COLLATE latin1_swedish_ci;
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to put the COLLATE <collation> statement for each column / expression where you want / need to apply it.

like this:

WHERE shipping_criteria.Active=1 AND shipping_criteria.Destination collate _latin1  Like CONCAT('%', shipping_details.destination, '%') COLLATE _latin1
AND shipping_criteria.Method COLLATE _latin1 Like CONCAT('%', shipping_details.shipping_type, '%') COLLATE _latin1
AND shipping_criteria.Day_Sent_To_CCG COLLATE _latin1 Like CONCAT('%', shipping_details.Process_Day, '%') COLLATE latin1_swedish_ci;

0
 
JoeFletcherAuthor Commented:
b_haynes: I looked at all my tables, and they're all the same latin1_swedish_ci.  (unless I'm missing something). I'm using subqueries. Is it possible that the subquery is being created in utf8 or something like that?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I looked at all my tables, and they're all the same latin1_swedish_ci.  
then, your database (or server) has a default collation of utf8_general_ci.

the expressions CONCAT('%', shipping_details.destination, '%')  will then meet the '%' in the server's / database's default collation, and try to concat it to the field, which is in the latin1_swedish_ci.

you might consider changing simply the database/server default collation...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for the doc:
http://dev.mysql.com/doc/refman/5.0/en/charset-server.html
http://dev.mysql.com/doc/refman/5.0/en/charset-database.html

so, you might consider issuing this command:

ALTER DATABASE db_name DEFAULT COLLATE latin1_swedish_ci;

and see what happens to your original query.
0
 
JoeFletcherAuthor Commented:
Ok, I tried your suggested statement and got this error message:  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_latin1  Like CONCAT('%', shipping_details.destination, '%') COLLATE _latin1
A' at line 22

I'm going to look into what you're saying about the default database collation, But I thought that it was created with latin1_swedish_ci (but maybe not). Is there a command or other way to see what the default db/server collation is set at? B/c I'll need to do this again on another server.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is there a command or other way to see what the default db/server collation is set at
see the comments on the second link I gave... there it is shown...

correcting my query syntax:
WHERE shipping_criteria.Active=1
AND shipping_criteria.Destination Like CONCAT('%', shipping_details.destination, '%') COLLATE latin1_swedish_ci
AND shipping_criteria.Method Like CONCAT('%', shipping_details.shipping_type, '%') COLLATE latin1_swedish_ci
AND shipping_criteria.Day_Sent_To_CCG Like CONCAT('%', shipping_details.Process_Day, '%') COLLATE latin1_swedish_ci;
0
 
JoeFletcherAuthor Commented:
Ok, so I ran ALTER DATABASE db_name DEFAULT COLLATE latin1_swedish_ci;

Re-running my query, I got:  COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'

I then ran ALTER DATABASE db_name  DEFAULT CHARACTER SET latin1;  , but I got the same above error after re-querying.

Is it possible that utf8 is coming from me copying and pasting my sql from NotePad+ into MySQL Query Browser?  My format is set at ANSI.
0
 
JoeFletcherAuthor Commented:
Any other thoughts on how I can fix this?  The mysql error # on this is 1253 (COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'utf8'), and there's a reference here: http://www.mysql.org/doc/refman/4.1/en/charset-collation-charset.html but for mysql 4 and I'm using mysql 5.  

I have no idea where the UTF8 is coming from... is it in my data? It doesn't make sense.  Again, the complete code I'm using is:

SELECT shipping_details.orders_id, shipping_criteria.Hold_Till_Next_Week, shipping_criteria.Box_Type
FROM shipping_criteria,
      (
            SELECT Orders.orders_id,
            CASE
                  WHEN Orders.shipping_method like '%ground%' THEN 'GND'
                  WHEN Orders.shipping_method like '%2nd day%' THEN '2DA'
                  WHEN Orders.shipping_method like '%next day%' THEN '1DA'
                  WHEN Orders.shipping_method like '%canada%' THEN 'STD'
                  WHEN Orders.shipping_method like '%expedited%' THEN 'XPD'
                  WHEN Orders.shipping_method like '%Free Shipping%' THEN 'GND'
              ELSE Orders.shipping_method
            END as shipping_type,
            DATE_FORMAT(now(),'%a') as Process_Day,
            CASE
                  WHEN Orders.shipping_allowed Like '%GND%' Or Orders.shipping_allowed Like '%XPD%' Or Orders.shipping_allowed Like '%STD%' THEN 'Cold'
              Else 'Warm'
            END as Destination
            FROM Orders
      ) AS shipping_details
WHERE shipping_criteria.Active=1
AND shipping_criteria.Destination Like CONCAT('%', shipping_details.destination, '%') COLLATE latin1_swedish_ci
AND shipping_criteria.Method Like CONCAT('%', shipping_details.shipping_type, '%') COLLATE latin1_swedish_ci
AND shipping_criteria.Day_Sent_To_CCG Like CONCAT('%', shipping_details.Process_Day, '%') COLLATE latin1_swedish_ci;
0
 
JoeFletcherAuthor Commented:
I stumbled upon a reference about casting and converting: http://www.cs.duke.edu/csl/docs/mysql-refman/charset.html

I weaved this in to my code below and it seems to bring the query up totally fine, although I still need to inspect it.  Does this seem the right thing to do?

SELECT shipping_details.orders_id, shipping_criteria.Hold_Till_Next_Week, shipping_criteria.Box_Type
FROM shipping_criteria,
      (
            SELECT Orders.orders_id,
            CASE
                  WHEN Orders.shipping_method like '%ground%' THEN 'GND'
                  WHEN Orders.shipping_method like '%2nd day%' THEN '2DA'
                  WHEN Orders.shipping_method like '%next day%' THEN '1DA'
                  WHEN Orders.shipping_method like '%canada%' THEN 'STD'
                  WHEN Orders.shipping_method like '%expedited%' THEN 'XPD'
                  WHEN Orders.shipping_method like '%Free Shipping%' THEN 'GND'
              ELSE Orders.shipping_method
            END as shipping_type,
            DATE_FORMAT(now(),'%a') as Process_Day,
            CASE
                  WHEN Orders.shipping_allowed Like '%GND%' Or Orders.shipping_allowed Like '%XPD%' Or Orders.shipping_allowed Like '%STD%' THEN 'Cold'
              Else 'Warm'
            END as Destination
            FROM Orders
      ) AS shipping_details
WHERE shipping_criteria.Active=1
AND shipping_criteria.Destination Like CAST(CONCAT('%', shipping_details.destination, '%') AS CHAR CHARACTER SET latin1) COLLATE latin1_swedish_ci
AND shipping_criteria.Method Like CAST(CONCAT('%', shipping_details.shipping_type, '%') AS CHAR CHARACTER SET latin1) COLLATE latin1_swedish_ci
AND shipping_criteria.Day_Sent_To_CCG Like CAST(CONCAT('%', shipping_details.Process_Day, '%') AS CHAR CHARACTER SET latin1) COLLATE latin1_swedish_ci;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I have no idea where the UTF8 is coming from... is it in my data?
I neither...

anyhow, looks like you found the correct syntax for your environment...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now