MySQL Bugs: #90647: mysql-community-srv is unable to follow configurations as defined in /etc/my.cnf

Bug #90647	mysql-community-srv is unable to follow configurations as defined in /etc/my.cnf
Submitted:	26 Apr 2018 13:07	Modified:	26 Jun 2018 15:15
Reporter:	Jean-Claude DOLE	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.6.39	OS:	SUSE (leap 42.3)
Assigned to:		CPU Architecture:	x86 (x86_64)

Description:
mysql-server is unable to follow configurations as defined in /etc/my.cnf
Extract of the file my.cnf :
# --- begin
.....

.....

# If you want to use mysqld_multi uncomment 1 or more mysqld sections

# below or add your own ones.

 

# WARNING

# --------

# If you uncomment mysqld1 than make absolutely sure, that database mysql,

# configured above, is not started.  This may result in corrupted data!

#

# [mysqld1]

# port       = 3306

# datadir    = /var/lib/mysql

# pid-file   = /var/lib/mysql/mysqld.pid

# socket     = /var/lib/mysql/mysql.sock

# user       = mysql

 

# [mysqld2]

# port       = 3307

# datadir    = /var/lib/mysql-databases/mysqld2

# pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid

# socket     = /var/lib/mysql-databases/mysqld2/mysql.sock

# user       = mysql

.....

.....
# --- end

extract from mysql@.service
# --- begin
.....

.....

[Service]

Restart=on-abort

Type=simple

ExecStartPre=/usr/lib/mysql/mysql-systemd-helper  install %i

ExecStartPre=/usr/lib/mysql/mysql-systemd-helper  upgrade %i

ExecStart=/usr/lib/mysql/mysql-systemd-helper     start   %i

ExecStartPost=/usr/lib/mysql/mysql-systemd-helper wait    %i

.....

.....
# --- end

From mysql-systemd-helper which is called with the instance number

Multiple instances problem 1 : datadir path does not follow what could be in /etc/my.cnf

# --- begin
.....

    if [[ -z "$INSTANCE" ]]; then

        datadir=/var/lib/mysql

        socket="/var/run/mysql/mysql.sock"

    else

        datadir="/var/lib/mysql-$INSTANCE"

        socket="/var/run/mysql/mysql.${INSTANCE}.sock"

  fi

.....
.....

# --- end

Multiple instances problem 2 : /etc/my.cnf is not read

# --- begin
....

    if [[ -n "$INSTANCE" ]]; then

        opts="$(/usr/bin/my_print_defaults mysqld mysqld_multi "$INSTANCE")"

        tmp_opts="$opts"

        config="/etc/my${INSTANCE}.cnf"

    else

        opts="$(/usr/bin/my_print_defaults mysqld)"

        tmp_opts="$opts"

        config="/etc/my.cnf"

    fi

....

# --- end

Multiple instances problem 3 : /etc/my.cnf is not read again
If one try to configure an instance following mysql-systemd-helper ( say instance 2 ) in /etc/my2.cnf, a new database is not installed in the right datadir (set in /etc/my2.cnf ) because it is hard-coded in mysql-systemd-helper (/var/lib/mysql-2).
Of course the folder '/var/lib/mysql' does not exists because it is not in use.
See journald.txt

How to repeat:
1°) install mysql
2°) Configure [mysqld2] in /etc/my.cnf
3°) Start the instance "sudo systemctl start mysql@2
==> fail to start

or

1°) install mysql
2°) Configure /etc/my2.cnf 
3°) Start the instance "sudo systemctl start mysql@2
==> fail to start

Suggested fix:
Rewrite /usr/lib/mysql/mysql-systemd-helper

or give a template to create a mysql@.service file to start database directly without mysql-systemd-helper.
In particular give the options to pass to the program mysqld_multi.
ExecStart=/usr/bin/mysqld_multi --option1 --option2 ....... start %i
The current documentation does not help me in this case.

When using multiple instance, in the documentations, it is not clear what stay in /etc/my.cnf when using only this configuration file.
And what stay in /etc/my.cnf and what stay in /etc/my%.cnf when you use the two configuration file.

Config file : my.cnf

Attachment: my.cnf.-test1 (application/octet-stream, text), 3.97 KiB.

Part of journald

Attachment: journald.txt (text/plain), 2.58 KiB.

Config file with a separate config for instance

Attachment: my2.cnf.-test2 (application/octet-stream, text), 661 bytes.

HI,

Thank you for your bug report.

First of all, you do not seem to be using mysqld_multi at all. Also, all lines that start with `#' sign are comments and can not be used.

Regardless of whether you use mysqld_multi or systemd, you have to specify a separate configuration file for each instance.