Illegal mix of collations for operation 'UNION'

I don't know for how many reason this error occurred but I found it when going to create a view using two tables using UNION. 

There are different ways to solve this problem. You can solve it by compress the value and then decompress as UNCOMPRESS(COMPRESS(x.name)) AS name.

You have another way to fix this problem. You can use first hex the value and then unhex as UNHEX(HEX(x.name)) AS name.

And finally, to fix this, you 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.

Some more convert functions are listed below:
CONVERT('2014-02-28', DATE)
CONVERT('2014-02-28 08:14:57', DATETIME)
CONVERT('08:14:57', TIME)
CONVERT(125, CHAR)
CONVERT(4-6, SIGNED)
CONVERT(4-6, UNSIGNED)
CONVERT('4', BINARY)
CONVERT('Some String' USING UTF8)
CONVERT('Some String' USING ASCII)
CONVERT('Some String' USING LATIN1)
CONVERT(x.price, DECIMAL)
CONVERT(x.price, DECIMAL(10,2))

MySQL: Create view from two tables with different column names

Its very important to create MySQL view from two or more tables with different column names. It is not mandatory but we sometime need it hardly. This can be done using UNION each table. Union's must have same columns in the same order across all sections. You should explicitly select/declare the null columns in each part of the union. You can use normal JOIN on each part.

Views in MySQL are two types: MERGE or TEMPTABLE. MERGE is simply a query expansion with appropriate aliases and behave as well as the base table. TEMPTABLE is just what it sounds like, the view puts the results into a temporary table before running the WHERE clause, and there are no indexes on it. It cause some performance impact. 

The default option is UNDEFINED, which determined by MySQL to select the appropriate algorithm. MySQL first try to use MERGE because it is more efficient than TEMPTABLE. 

If the MERGE algorithm cannot be used (determined by MySQL itself), a temporary table must be used instead (This algorithm is TEMPTABLE). MERGE cannot be used if the view contains any of the following constructs (Aggregate function or UNION): 

SUM
AVG
DISTINCT
GROUP BY
HAVING
LIMIT
UNION or UNION ALL

And the final example is as follows:

CREATE OR REPLACE ALGORITHM=MERGE VIEW MY_VIEW AS
SELECT test1.id AS _id, test1.name AS _name, test1.roll AS _roll from test1
UNION
SELECT test2.id AS _id, test2.type AS _name, NULL AS _roll from test2