#1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'UNION'

Solution 1:

As you are restoring the database from a MySQL dump, you can solve your problem while also future-proofing your database by changing the character set and collations for your tables, functions, and procedures. There are a number of changes between MySQL 5.7 and 8.x, so this is really the best time to do so.

Here is what you can do with each CREATE statement:

1. Replace the DEFAULT CHARSET to utf8mb4

2. Replace the COLLATE to utf8mb4_unicode_ci

3. (For tables) Ensure the ENGINE is set to InnoDB

   ^{Note: You do not want to be using MyISAM anymore, nor do you want to mix ENGINE types with queries, as that's a pretty significant performance hit.}

Do all of this with your preferred text editor, then run the import process into a fresh MySQL database. Be sure to set the database DEFAULT CHARSET and COLLATE values to the same as you have for the tables, functions, and procedures.

Reasoning for Suggestions:

MySQL 8.0 is a significant departure from the 5.x line with a great deal of items that were deprecated prior to 5.2 being completely removed. This includes certain column types, as well as collations. The import process will try to adapt the deprecated elements to their modern equivalents automatically, but often makes a mess of character sets and collations.

The utf8mb4_unicode_ci has proven to be the most reliable collation when working with multi-byte characters, such as emoji and those used in non-English languages. While it will use a little more disk space, this will ensure your application(s) can handle any character thrown at it. The _ci bit at the end ensures the values are treated as case insensitive when joining and doing lookups.

Replacing all of the CHARSET and COLLATE values will ensure you do not receive the Illegal mix of collations error again ... unless ...

Things to Consider with Stored Procedures and Triggers

MySQL 8.0 seems to expect a little more specificity when creating temporary tables in stored procedures. If you use temporary tables, be sure to predefine them in the code just as you would with a normal table. The syntax is pretty much the same, except you're adding an additional word:

DROP TEMPORARY TABLE IF EXISTS `YearlySums`;
CREATE TEMPORARY TABLE IF NOT EXISTS `YearlySums ` (
    ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This will ensure you do not run into the Illegal mix of collations error when working with stored procedures.

If your tables have BEFORE INSERT or BEFORE UPDATE triggers, and those tables are populated via stored procedures, you will want to do a boatload of testing prior to putting the database into a production setting. Oracle introduced a pretty serious bug in 8.0.25 that can result in the MySQL Server engine crashing in certain instances when a BEFORE trigger is processing rows as part of data validation, but only when that data is provided by a stored procedure. The issue has existed for well over a year, and Oracle doesn't seem to care.

Do not let this bug ruin your New Year holiday like it did mine last year 😑