[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7843
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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