#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);

Answer 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