“character encoding hell”- Configuring mariadb for utf8 end to end

Question

Mariadb: "mysql Ver 15.1 Distrib 10.3.8-MariaDB"
OS: Windows 10 64 bit

I've been wrestling with getting a csv data file that contains "high 8 bit" characters such as the micron character and em dash read into a table in mariadb using "load data infile".

The database, tables and columns are all set to utf8.

After many attempts I got the file loaded in a clunky way by using an external program (Notepad++) to convert the csv file from the original ANSI Codepage 1252 (cp1252) encoding to utf8, and manually using "set names utf8" in the mysql client.

However i'm having trouble setting this up as the default config for the program.

From reading the Mariadb (Mdb) docs I set up my.cnf as-

# Set everything to utf8 (utfmb3 I believe?)
[server]
character_set_server        = utf8
character_set_connection    = utf8
default-collation           = utf8_general_ci
init-connect                = 'SET NAMES utf8'

[client]
character_set_client        = utf8
character_set_connection    = utf8

but the mysql client chokes on the two client options with the errors-

mysql: unknown variable 'character_set_client=utf8'
mysql: unknown variable 'character_set_connection=utf8'

Answer 1

I encountered similar problems with mariadb in this regard too.

First the character-set-server and collation-server from the default my.cnf should not be used, it has to be character_set_server and collation_server (just underlines).

My config looks as follows:

[client]
default-character-set = utf8mb4
[mysqld]
character_set_server   = utf8mb4
collation_server       = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'

mysql reports the following now:

mysql -e "show variables where variable_name like '%char%' or variable_name like '%coll%';"

+--------------------------+----------------------------+
| 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                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8mb4_general_ci         |
| collation_database       | utf8mb4_unicode_ci         |
| collation_server         | utf8mb4_unicode_ci         |
+--------------------------+----------------------------+

Tested on 10.4.7-MariaDB

Answer 2

Note: Original edit moved to answer

I found the correct incantation-

[client]
default_character_set=utf8mb4

[mysql]
default_character_set=utf8mb4

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

This seems to do the job. I still don't understand why what look like client or connection-orientated config variables aren't accepted by the mysql client-

mysql: unknown variable 'character_set_client=utf8'
mysql: unknown variable 'character_set_connection=utf8'

--UPDATE-- Since updating Mariadb to version 10.3.12 this seems to have partially broken again.

With the same config file setup, as above, the mysql client is now reporting-

mysql -e "show variables where variable_name like '%char%' or variable_name like '%coll%';"
+--------------------------+-----------------------------------------------+
| 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                                          |
| character_sets_dir       | C:\Program Files\MariaDB 10.3\share\charsets\ |
| collation_connection     | utf8mb4_general_ci                            |
| collation_database       | utf8mb4_general_ci                            |
| collation_server         | latin1_swedish_ci                             |
+--------------------------+-----------------------------------------------+

The settings for character_set_server and collation_server have gone back to defaults. I can change these settings individually via the mysql client on a per-session basis, but the system defaults are ignoring the settings in the config file.

Unless v10.3.12 changed some rules about the config variables, this seems to be a new issue with this version.