MySQL Bugs: #108752: installer: Illegal mix of collations

Bug #108752	installer: Illegal mix of collations
Submitted:	12 Oct 2022 11:01	Modified:	14 Oct 2022 9:01
Reporter:	Anthony Marston	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S2 (Serious)
Version:	8.0.31	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Collations, installer

Description:
While attempting to upgrade from version 8.0.30 to 8.0.31 the installer failed with the following error:

Beginning configuration step: Starting the server and upgrading system tables (may take a long time)

Attempting to start service MYSQL80...
E:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.31) starting as process 12112
InnoDB initialization has started..
InnoDB initialization has ended....
Server upgrade from '80030' to '80031' started........
Server upgrade from '80030' to '80031' completed.
mysqld: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb3_general_ci,COERCIBLE) for operation '='
System views initialization failed.
Aborting
E:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.31)  MySQL Community Server - GPL..........................

How to repeat:
upgrade from version 8.0.30 to 8.0.31

Suggested fix:
Find out which column has the wrong collation and fix it.

Hi Mr. Marston,

Thank you for your bug report.

We were unable to repeat the problem that you are reporting.

Hence, we need additional info from you.

We need you to provide us with a fully repeatable test case. That means that you have to take your backup and restore data as it were in the old release. Then, we need you to start older release of MySQL and find out which of the tables has column(s) Swedish collation in the Latin character set.

Without your proper feedback and full test case, we can not proceed with the processing of your report.

Waiting on your feedback.

The error message is useless as it does not identify which columns on which tables caused the error. The error message clearly states that the failure occurred during System views initialization, so it points to an error with the system tables and not any of my application tables.

Hi Mr. Marston,

There could be a problem in system tables, but we still require a fully repeatable test case, which you have not provided.

Also, the errors in OS and in the hardware can cause these errors.

Can't repeat.

I have gone through all my databases and confirmed that none of them are using utf8mb3_general_ci as I changed them all to utf8mb4_0900_ai_ci several releases ago, so reloading them from a backup won't do any good. I cannot reload any system tables as they are built into your product. Am I supposed to de-install 8.0.31, re-install 8.0.30 and try the upgrade again?

Hi Mr. Marston,

We would advise you first to use INFORMATION_SCHEMA to find out whether you have in any of your schemas a table with a CHAR / VARCHAR column in the character set latin1 ........ any latin1.

Please include all schemas, including data dictionary , I_S, P_S and SYS.

Use I_S table COLUMNS>

Shows contents if INFORMATION_SCHEMA.COLUMNS

Attachment: columns.zip (application/x-zip-compressed, text), 213.59 KiB.

I have run SELECT * FROM information_schema.columns to create a file called columns.csv which I have uploaded into a file called columns.zip