Setting UTF-8 in MariaDB server ignores character_set_system

character_set_system specifies the character set which will be used to store identifiers and other internal information.

It is 3byte utf8, and you cannot change it unless you change sources and recompile MariaDB.

Beginning with 10.6, utf8 was mapped to utf8mb3 (and will be mapped to utf8mb4 in later versions). See MDEV-8334

I am having a similar problem in MariaDB 10.6.5, I was trying to load a dump from AWS and had this error:

ERROR 1253 (42000) at line 26: COLLATION 'utf8mb3_general_ci' is not valid for CHARACTER SET 'utf8mb4'

My config looks like this:

MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

and according to this page https://mariadb.com/kb/en/old-mode/ I had to set old-mode to empty in /etc/mysql/mariadb.conf.d/50-server.cnf

old-mode=

Therefore it changed from

MariaDB [(none)]> SHOW VARIABLES LIKE '%old%';
+------------------------------------------+-----------------+
| Variable_name                            | Value           |
+------------------------------------------+-----------------+
| old_mode                                 | UTF8_IS_UTF8MB3 |
+--------------------------+---------------------------------+

to

MariaDB [(none)]> SHOW VARIABLES LIKE '%old%';
+------------------------------------------+-----------------+
| Variable_name                            | Value           |
+------------------------------------------+-----------------+
| old_mode                                 |                 |
+--------------------------+---------------------------------+

So I've managed to load the SQL dump even if character_set_system was still showing utf8mb3.

HTH somebody.