MySQL 5.7 Upgrade to MySQL 8 Errors - Warning: The following objects use the utf8mb3 character set

MySQL & MariaDB
1

From our upgrade logs:

  1. Usage of utf8mb3 charset
    Warning: The following objects use the utf8mb3 character set. It is
    recommended to convert them to use utf8mb4 instead, for improved Unicode
    support.
    More information:
    https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html

SCHEMA1 - schema’s default character set: utf8
SCHEMA2 - schema’s default character set: utf8
SCHEMA3 - schema’s default character set: utf8
…
SCHEMA1.table1.column1 - column’s default character set:utf8
SCHEMA1.table1.column2 - column’s default character set:utf8
SCHEMA1.table1.column3 - column’s default character set: utf8

What is the most efficient way to resolve this? Should we update at the schema level or the table level (Please provide example SQL statements)?

When we do update the charsets of schemas/tables does this mean the charsets are now a mix of utf8mb3 and utf8mb4 (the newer records being mb4)? Or, on updating a table or schema does all data get updated to mb4? When we convert a table what impact does this have on the DB, is this something we need downtime for? Also, what impact might this have on the applications reading/writing might they be incompatible with the newer character set?

What about other character sets such as latin1?

Any help is appreciated.
Thanks.

2

Hello @ChrisDK,

Alter each table and convert the columns and tables to use utf8mb4

All data will be converted to utf8mb4. You cannot have a mix within a column definition.

You can use pt-online-schema-change to make these ALTERs less impactful/online.

Doubtful. Especially if you are not actually using any of the extended characters (ie: emojis)

3

Hi Mathew,

Thanks so much for your response. Another question I had in the original post is regarding other character sets. Can these be left as-is? Latin1 for example.

4

You can leave things as latin1 but you run the risk of character set mismatch. Your app might connect to MySQL via utf8 (new default) and send utf8 encoded characters to MySQL. But your indexes will have been created in latin1. Somewhere an implicit conversion will have to take place. It’s better just to have everything utf8; no need to worry about conversions.