4

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);
salusa
  • 61
  • 1
  • 2
  • 7
  • How do you expect us to answer this question based on the query alone? Copy in the create table commands for each affected table. – Shadow Nov 01 '15 at 00:29
  • So, you mean provide you the create command of each table? – salusa Nov 01 '15 at 00:58
  • That is exactly what I meant. – Shadow Nov 01 '15 at 01:20
  • The most likely problem is a *difference* between the characterset of expressions returned in the queries. My guess is the second column in the result set of each query. But that's just a guess. On an unrelated note: the PHP mysql_ extension is deprecated. New development should use either PDO or mysqli. – spencer7593 Nov 01 '15 at 01:24
  • I had the same error message, but behold: doing an union with multiple queries on the same table! In my case the problem was the PDO connection that was not set to UTF-8. by using `charset=utf8` on `new PDO('mysql:host=foo.net;dbname=mydb;charset=utf8, $user, $pass);` the problem was resolved. – Fernando Basso Apr 26 '17 at 20:13

3 Answers3

11

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

Community
  • 1
  • 1
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thanks for you help spencer, it's working now using CONVERT command. – salusa Nov 01 '15 at 15:51
  • It would have been better to be consistent across the tables! – Rick James Dec 08 '15 at 02:41
  • 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](http://dev.mysql.com/doc/refman/5.5/en/charset-literal.html) – spencer7593 Dec 08 '15 at 03:29
1

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
Community
  • 1
  • 1
IsraelWebDev
  • 11
  • 1
-4

Make sure the columns have the same data types, encoding, and names. For example, one of the unions is named name and the next union statment is named cat_name. Make sure the aliases in select are the same throughout all the unions.

the happy mamba
  • 468
  • 2
  • 6
  • 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. – Shadow Nov 01 '15 at 00:49
  • But this query is working fine on my local environment but, on server it occurred this error. – salusa Nov 01 '15 at 01:03