3

I have upgraded MySQL from version 5.7 to 8. I have exported the database from old MySQL 5.7 & Now I am trying to import the database into MySQL 8. But I am getting an error for one of the database view having union clause..

the error is -

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

the view is -

CREATE VIEW VIEW_ALL_ORDER_ITEMS_REF AS
  select * from VIEW_ORDER_ITEM_EQ_REF
  union 
  select * from VIEW_ORDER_ITEM_SO_REF
  union 
  select * from VIEW_ORDER_ITEM_OA_REF
  union 
  select * from VIEW_ORDER_ITEM_WO_REF
  union 
  select * from VIEW_ORDER_ITEM_DO_REF

I faced this error when i am trying to apply union between more than two views.

this view works fine.

drop view if exists VIEW_ALL_ORDER_ITEMS_REF;
CREATE VIEW VIEW_ALL_ORDER_ITEMS_REF AS
  select * from VIEW_ORDER_ITEM_EQ_REF
  union 
  select * from VIEW_ORDER_ITEM_SO_REF

can Anyone help ?

2
  • How are you doing the data import? Restoring from a SQL dump?
    – matigo
    Dec 21, 2021 at 10:43
  • yes............ Dec 21, 2021 at 12:48

1 Answer 1

2

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 😑

5
  • it worked .. thank you so much @matigo .. is there any option to set the charset and collation globally ? Dec 22, 2021 at 12:11
  • You can set a default collation in my.cnf, but this can result in problems if you’re replicating across several servers and one doesn’t have the collation value in the configuration file. It’s an edge case, but a painful one if it hits. Being specific with declarations will save lots of pain in the future 🤐
    – matigo
    Dec 22, 2021 at 12:22
  • ok thanks bro... Dec 22, 2021 at 13:22
  • I dont have stored procedures and triggers in my database.. it contains only the tables.. instead of restoring the database from SQL dump, can i just alter all my tables on existing database to change the charset and collation ? Dec 22, 2021 at 13:37
  • You can alter, yes. Note that there may be garbled text afterwards as the ALTER TABLE and ALTER COLUMN statements are not flawless when moving data between collations. If all your text is standard A-Z and 0-9, then you'll be fine. If you have any non-Latin characters, you will want to test, test, and test some more. I work with a lot of Asian, Hebrew, and Arabic data and have run into numerous problems over the years when trying to "save time". Re-importing after modifying the .sql file has always been fastest for databases larger than 1GB 🤐
    – matigo
    Dec 22, 2021 at 13:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .