Are my mariadb character set and collation set wrong?

richpav

Here's what I see:

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb3                    |
| character_set_connection | utf8mb3                    |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb3                    |
| character_set_server     | latin1                     |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb3_general_ci         |
| collation_database       | latin1_swedish_ci          |
| collation_server         | latin1_swedish_ci          |
+--------------------------+----------------------------+

Here's what I think I'm supposed to see:

+--------------------------+--------------------+
| 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     | utf8                 |
| collation_connection     | utf8mb4_general_ci  |
| collation_database       | utf8mb4_general_ci  |
| collation_server         | utf8mb4_general_ci  |
+--------------------------+--------------------+

Am I right? Our Wordpress sites use English & Japanese. If I need to fix this, how do I do it?

FWIW, I'm installing on cloud server on hetzner.com. I'm transferring all our sites currently on Digital Ocean.

perroud

utf8mb4_unicode_ci would be nice, try on!

richpav

perroud

How? Where?

Should I add the below to /etc/mysql/conf.d/my.cnf ? Is this the right file to alter or some other? I don't know if this will fix it or blow it all up.

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4
character_set_client: utf8mb4
character_set_database: utf8mb4
character_set_results: utf8mb4
character_set_connection: utf8mb4
collation_connection: utf8mb4_unicode_ci
collation_server: utf8mb4_unicode_ci
character_set_server: utf8mb4

perroud

richpav

Yes!

[client]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
character-set-server = utf8mb4
skip-character-set-client-handshake

For exist database, you can use the SRDB. An excellent PHP tool to convert and manipulate database such as Adminer, but it faster.

richpav

perroud Thank you! I never would have figured that out by myself.

FYI, -z and --dry-run don't work in the SRDB script. It says the conversions haven't been made, but they actually have, so plan ahead accordingly.

perroud

richpav

The -dry-run mode into GUI works properly here. Thanks for your info about CLI won’t work (I guess so). After that, on Adminer you’ll change the database charset, not only the tables and data.