Understanding utf8mb3 and utf8mb4 during upgrade to MariaDB

Question

I have problems to understand the upgrade from MariaDB 10.5 to 10.6 on Ubuntu 20.04 about the characterset or colleration.

After a migration process from MySQL 5.7 to MariaDb 10.11 the database returns this values:

MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb3 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8mb3 |
+--------------------------+---------+
7 rows in set (0.001 sec)

MariaDB [(none)]>  SHOW SESSION VARIABLES LIKE 'collation\_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'old\_mode%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| old_mode      | UTF8_IS_UTF8MB3 |
+---------------+-----------------+
1 row in set (0.001 sec)

From the schema I see comments like this:

/*!40101 SET character_set_client = utf8 */;

or row creation in that way:

 `description` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,

In the server configuration file (/etc/mysql/mariadb.conf.d/50-server.cnf) this entries are activated:

#
# * Character sets
#

# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

Now I'm so confused that I'm not sure which encoding is used and if the tables are in an defined state.

My understanding is that we using with MySQL 5.7 UTF-8. UTF-8 is now utf8mb3. In future we use utf8mb4 because using Debian/Ubunutu?

Must I encode complete database from utf8mb3 to utf8mb4? When yes how?

It can be set to imply utf8mb4 by changing the value of the old_mode system variable.

Must I update only the old_mode to get utf8mb4 as recommended?

Must I update the schemas to new encoding utf8mb4?

`description` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

or

`description` varchar(1024) COLLATE utf8mb4_general_ci DEFAULT NULL,

Thanks, Markus

Answer 1

The 'world' is moving toward utf8mb4, away from utf8mb3. Since you are going through the upgrade pain, you should do as much of it as is practical now.

Let's look at groups of character_set_% settings:

• _client, _connection, _results -- These match what can be set by SET NAMES, and they refer to the charset that the client is using.
• _filesystem, _system -- These are relevant to the OS and MySQL/MariaDB; don't mess with them.
• _database, _server -- mysterious.

Then there are the settings on the tables. The charset and collation of each column is either specified or defaulted to the table's setting, which in turn is defaulted to the database's setting.

You should move to utf8mb4 to get Emoji and all of Chinese.

At some point in the future, "utf8" will no longer be a synonym of "utf8mb3", but change to "utf8mb4". (I dread the day and the chaos it will cause.) (cf old_mode)

Unless (until) the Upgrade automatically ALTERs all tables from utf8mb3 to utf8mb4, it is up to users to hang onto utf8mb3 or do ALTER TABLE .. CONVERT TO utf8mb4. Note: That ALTER will change all string columns, even those that are explicitly some other charset. (I recommend ascii for country_code, postal_code, etc.)

"Now I'm so confused that I'm not sure which encoding is used ..." -- I've been studying the problem for more than a decade; I still get confused, too.

The Unicode standard is moving from 'general' to 'unicode' to 'unicode520' to even newer collations. I would not worry about upgrading to the latest unless your app needs it. More: https://stackoverflow.com/a/38664797/1766831

For performance when JOINing and comparing, be sure to use the same charset and collation for both parts. If, after this upgrade, something slows down or even gets an error message ("...illegal combination of collation...") look at the column definitions and the client connection.

See also https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for common errors.