[MDEV-18646] mysqld ignoring character_set_server and collation_server directives in config files Created: 2019-02-19  Updated: 2019-03-26  Resolved: 2019-03-26

Status: Closed
Project: MariaDB Server
Component/s: Character Sets, Configuration
Affects Version/s: 10.3.12
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: VL Incans Assignee: Vladislav Vaintroub
Resolution: Not a Bug Votes: 0
Labels: configuration
Environment:

Host: VMWare
OS: Windows 10 Enterprise 64 bit v1809
OS Build: 17763.253

Mariadb: mysql Ver 15.1 Distrib 10.3.12-MariaDB, for Win64 (AMD64), source revision faf206a848684bc0f87c93a2b47a55063bbccc6c



 Description   

I spent several days grappling with "character encoding hell" trying to get my mariadb setup "unicode clean".

Having found the recommended incantation in the Mariadb docs at
https://mariadb.com/kb/en/library/setting-character-sets-and-collations/
to set the whole system to "utf8mb4" top to bottom I _almost _had things looking right under 10.3.8.

I decided to upgrade to the latest stable version (10.3.12) to try to finalise things, but this seems to have caused a regression. In 10.3.12 with an unchanged config the system is ignoring the character_set_server and collation_server directives in the config files, and reporting both these settings as the default (latin1 and latin1_swedish_ci respectively) on startup.

Config files are as below (all except C:\my.cnf that contains a default database, user and password for my dev account)

C:\Program Files\MariaDB 10.3\my.cnf

#
# Set everything to utf8
#
# (per https://mariadb.com/kb/en/library/setting-character-sets-and-collations/)
#
 
[client]
default_character_set=utf8mb4
 
[mysql]
default_character_set=utf8mb4
 
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
 
# Tried but found ineffective...
#
#init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_general_ci'
#character_set_client_handshake = FALSE

C:\Program Files\MariaDB 10.3\data\my.ini

[mysqld]
datadir=C:/Program Files/MariaDB 10.3/data
port=3306
innodb_buffer_pool_size=1023M
[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.3/lib/plugin

Server state after restarting the MySQL service-

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



 Comments   
Comment by Vladislav Vaintroub [ 2019-02-20 ]

Just have all of charset variables in my.ini . mysqld does not read both my.ini and my.cnf. It reads either/or

Comment by VL Incans [ 2019-02-24 ]

I am confused by this comment @Vladislav Vaintroub

  1. The server prints out a long list of config file locations if started with --help --verbose flags, and there is nothing mentioned about the files being in a priority order, with one file "masking" another.
  2. Several documents I have read online refer to a user storing a personal "my.cnf" (admittedly mostly on *nix platforms). There would be little point in that if you had to go find any other my.cnf or my.ini files buried in the installation directories (C:\Program Files\MariaDB 10.3\data\my.ini is shipped with the installation) and remove them after copying the content.
  3. My setup is reading at least SOME information from 3 separate files at the moment, as long as the variables being set are different in each file there shouldn't be an issue.
  4. As noted in the report, the current config was largely working in v10.3.8, so something seems to have changed in v10.3.12

If you can refer me to the section of the Mariadb docs where it describes this behaviour then of course i'll have to accept it's true, but at this point it is not my understanding of how the system is expected to work.

Comment by Vladislav Vaintroub [ 2019-02-25 ]

I've tested once again, and indeed the comment was misleading and confusing. sorry for that. All

When you run installed, or mysql_install_db.exe, as service, the server is started --defaults-file=C:\path\to\datadir\my.ini . And only this file is read, rather than everything in the default search. "MariaDB x.y (x64) command line" , the shortcut to the "root" command line, uses --defaults-file as well, thus no search path is used. If you just start mysql.exe from the command line, it uses the default search path.

In any case, if you install as service, server parameters need to be in the above --defaults-file.
What has changed relatively recently, was the clients default charset https://github.com/mariadb/mariadb-connector-c/commit/8455b6e1c9d318e5785110648d133d9939ed36da , from default utf8 to default latin1.
Maybe this is the problem you struggle with

The default path search (if used) has not changed for a long time. The mysql_install_db.exe, the tool that is used when MSI installs a db, used --defaults-file, this did not use default search path, from the time it was created.
Also MariaDB Command Line Client , the root command line shortcut, used --defaults-file pointing to the same my.ini , located in the data directory.

The idea behind --defaults-file use is that you can use multiple versions of MariaDB, and they do not conflict with one another.

Hope this helps.

Comment by VL Incans [ 2019-02-28 ]

Thank you @Vladislav Vaintroub, this is beginning to make some sense now.

  1. I had not appreciated that the installer set up a "forced" config file location, and thus the search path reported by mysqld --help --verbose doesn't mean anything.
  2. I was indeed caught out by the change in default server character set between 10.3.8 and 10.3.12.

A couple of notes-

  1. In my personal opinion, changing the default from utf8 to latin1 in the system is a step backwards. Modern software systems need to support users from all countries. The old way of designing and testing a solution in ASCII, and then having to "Internationalise" the application is just way too painful and error prone.
  2. The search path isn't entirely irrelevant, because my "mysql.exe" client is quite happily picking up a default database name, user name and user password (for my dev acount) from C:/my.cnf. I want to keep this separate from the main system config so this info doesn't accidentally get included in backups of the data dir.

However the key thing is my installation is now working as expected. Thank you very much for your help.

Comment by Elena Stepanova [ 2019-03-11 ]

Assigning to wlad in case he wants to address any of the issues above. Otherwise please close.

Generated at Thu Feb 08 08:45:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.