Read more

MySQL client: Double check the used character set with utf8mb4

Avatar
Andreas Vöst
December 13, 2022Software engineer at makandra GmbH

The Oracle mysql client has an odd behavior if your server uses latin1 as default character-set-server.

Command

mysql --version
mysql  Ver 8.0.31-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

mysql --default-character-set utf8mb4 -e "SHOW VARIABLES LIKE '%char%';"

Expectation

Illustration online protection

Rails professionals since 2007

Our laser focus on a single technology has made us a leader in this space. Need help?

  • We build a solid first version of your product
  • We train your development team
  • We rescue your project in trouble
Read more Show archive.org snapshot

utf8mb4 will be used as character set in this session.

Reality

The mysql client falls back to latin1:

+--------------------------+----------------------------------------------+
| Variable_name            | Value                                        |
+--------------------------+----------------------------------------------+
| character_set_client     | latin1                                       |
| character_set_connection | latin1                                       |
| character_set_database   | utf8mb4                                      |
| character_set_filesystem | binary                                       |
| character_set_results    | latin1                                       |
| character_set_server     | latin1                                       |
| character_set_system     | utf8                                         |
+--------------------------+----------------------------------------------+

Explanation

The client can't handle the --default-character-set utf8mb4 command line option and falls back to the servers default character set. You could use --default-character-set utf8 and set the character set Show archive.org snapshot inside the session to circumvent this behavior.

Better solutions

Use MariaDB mysql client

The MariaDB mysql client handles the --default-character-set option correct:

mysql --version
mysql  Ver 15.1 Distrib 10.3.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

mysql --default-character-set utf8mb4 -e "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     | latin1                                       |
| character_set_system     | utf8                                         |
+--------------------------+----------------------------------------------+

Switch the servers character set

Both mysql clients will use the servers default character set. If you configure your server to use utf8mb4 even Oracles mysql can handle utf8mb4.

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Posted by Andreas Vöst to makandra Operations (2022-12-13 11:54)