#1271 - Illegal mix of collations for operation 'UNION' in like query

Question

I have checked the collation type of each table, and that they have the same collation.

Error: #1271 - Illegal mix of collations for operation 'UNION'

Please see the below query:

$query = "(SELECT id, product_name, description, 'tbl_landt' as type FROM                                      tbl_landt WHERE product_name LIKE '%" . 
          $keyword . "%' OR description LIKE '%" . $keyword ."%') 
           UNION
           (SELECT id, name, null, 'tbl_land_cat' as type FROM tbl_land_cat WHERE name LIKE '%" . $keyword . "%') 
           UNION
           (SELECT tbl_sub_cat.id, tbl_sub_cat.name AS cat_name, null, 'tbl_sub_cat' as type FROM tbl_sub_cat WHERE name LIKE '%" . $keyword . "%')
           UNION
           (SELECT id, name, null, 'tbl_exl_cat' as type FROM tbl_exl_cat WHERE name LIKE '%" . $keyword . "%')";
           $squery = mysql_query($query);

Solution 1

To fix this, you'll likely need to replace some column references in the SELECT list (in one or more of the queries) with an expression, something like CONVERT(name USING utf8) AS name.

The most likely problem is that the queries are returning columns with different charactersets. (The error message identifies the UNION operation having the problem, not the other comparison predicates (character comparison in the WHERE clause, where this type of error can also occur.)

To debug this, you'd need to check the characterset of the expressions being returned in each query, and comparing. To narrow it down, you could try testing subsets of the query to ...

(query1) UNION (query2)
(query1) UNION (query3) 
(query1) UNION (query4)

That should narrow down the combination that is causing the issue.

And then check the charactersets of the character-type columns being returned, included as part of the table/column definition.

---

References:

MySQL CONVERT function reference:

http://dev.mysql.com/doc/refman/5.6/en/charset-convert.html

To show charactersets (and collations) available in MySQL:

SHOW COLLATION

To check the charactersets of the columns referenced in the query...

SHOW CREATE TABLE tbl_landt
SHOW CREATE TABLE tbl_land_cat
etc.

Also, see this (old) question in StackOverflow for a more detailed discussion about coercibility of charactersets in MySQL...

Troubleshooting “Illegal mix of collations” error in mysql https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql

Solution 2

I was able to fix this error on a CREATE VIEW query that was failing at

COALESCE(<TIMESTAMP>,<DATETIME>)

as suggested here by casting the result to DATETIME, such as:

CAST( COALESCE( <TIMESTAMP> , <DATETIME> ) AS DATETIME

Comments

[Shadow]

Neither the names, nor the data types need to match. Names are completely irrelevant, data types should match, but the query will pass, even if they are not a complete match. Just you have to be aware of the consequences.

[salusa]

But this query is working fine on my local environment but, on server it occurred this error.

[salusa]

Thanks for you help spencer, it's working now using CONVERT command.

[Rick James]

It would have been better to be consistent across the tables!

[spencer7593]

Sometimes, we get this same error even when all of the referenced character columns have the same characterset and collation. I most often encounter this error when there are string literals in the query. String literals have their own characterset/collation. Reference: http://dev.mysql.com/doc/refman/5.5/en/charset-literal.html