[debian-mysql] Bug#1025138: mariadb-server-10.5: editing config files has no apparent effect on bind-address

Wed Nov 30 07:34:25 GMT 2022

Package: mariadb-server-10.5
Version: 1:10.5.15-0+deb11u1
Severity: normal
X-Debbugs-Cc: RossBoylan at stanfordalumni.org

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA512

This is actually a pretty severe impact on usability for me, since I can't
contact the server from other machines.  Since I think I followed the
instructions in README.Debian,
this is probably a documentation issue  at a minimum.

** BACKGROUND
mariadb was working fine on a single machine, mostly working for mythtv.
I wanted to access the database from other machines on my local network.

Both the upstream documents and the NETWORKING section of the server
README.Debian indicate bind-address is the key directive I want to use,
replacing the default 127.0.0.1 to listen on.  I have 2 NICs and only want it
to listen on 1, and so do not want to use 0.0.0.0.

** STEPS TO REPRODUCE
1. Shut down clients, i.e., mythtv-backend, accessing the database.
2. Shut down database.
3. Edit /etc/mysql/mariadb.cnf (the README.Debian says mysql.cnf, but that's a
symlink to mariadb.cnf) by uncommenting `port = 3306` and inserting
`bind_address = 192.168.1.10` in the `[client-server]` section.
4. Restart the mariadb server.

** OBSERVED RESULT
netstat and log messages all confirm that maria continues to listen on
127.0.0.1 and no other IP addresses.

** EXPECTED RESULT
maria listening on 192.168.10.1.
I'm a little fuzzy on whether it will also listen on 127.0.0.1 in this case; I
think the documents indicate it won't.  I could live with either, but would
prefer if it continued to listen on the loopback.

** ATTEMPTED SOLUTIONS
I tried variation in the exact syntax of the bind_address specification, with a
restart each time.

Consulted documentation and other internet resources, some of which I reference
in the next section.

I noticed the bind_address of 127.0.0.1 currently seems to be set in
/etc/mysql/mariadb.conf.d/50-server.cnf, and so I tried changing it there and
commenting out my other changes.  Since the whole thing was in a [mysqld]
section it was unclear to me if any of it applies to my installation.

None of the changes made a difference, except for the error message for some
syntaxes.

** POSSIBLE EXPLANATIONS

1. The bind_address must be specified on the command line when the program is
invoked.  This wouldn't be too surprising, but I don't think the documentation
says this (https://mariadb.com/kb/en/server-system-variables/ indicates it can
be provided on the command line and is not dynamic).

2. The bind_address is being specified on the command line when the program is
invoked, and this overrides settings in the configuration files.

3. The "command line" is actually systemd for me.  Its invocations include
$MYSQLD_OPTS.  I can't tell if anything is setting that, or where it would be
set.  /etc/default/ has neither a maria nor a mysql file; some of the startup
scripts attempt to include them if they are present.

4. Some other systemd limitations are in play.  For example, perhaps I need to
explicitly declare a dependence on the necessary IP address or device.
https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/ does
not mention this as a necessary step.  The discussion in
https://mariadb.com/kb/en/systemd/#systemd-socket-activation also makes it
sound as if this is optional.

5. debconf options are controlling the binding in some way not visible to me
inspecting the startup code.

6. systemd restart, or systemd stop and start in a short time, both of which I
think I tried, is not sufficient to do a real reset and leaves the old
configuration in play, regardless of what I changed on the disk.  But if that
were true I would have had no error variation when I varied the syntax, and I
did have such variation.  I'm pretty sure I have observed this with some other
services, maybe isc-dhcp-server.

The mythtv database user does have permission to access the database from
outside the local machine, though my problems seem to be occurring at an
earlier step.

- -- System Information:
Debian Release: 11.5
  APT prefers stable-security
  APT policy: (500, 'stable-security'), (500, 'stable-debug'), (500, 'proposed-
updates-debug'), (500, 'proposed-updates'), (500, 'stable')
Architecture: amd64 (x86_64)
Foreign Architectures: i386

Kernel: Linux 5.10.0-19-amd64 (SMP w/8 CPU threads)
Kernel taint flags: TAINT_PROPRIETARY_MODULE, TAINT_OOT_MODULE,
TAINT_UNSIGNED_MODULE
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8), LANGUAGE not
set
Shell: /bin/sh linked to /usr/bin/dash
Init: systemd (via /run/systemd/system)
LSM: AppArmor: enabled

Versions of packages mariadb-server-10.5 depends on:
ii  adduser                   3.118
ii  debconf [debconf-2.0]     1.5.77
ii  galera-4                  26.4.11-0+deb11u1
ii  gawk                      1:5.1.0-1
ii  iproute2                  5.10.0-4
ii  libc6                     2.31-13+deb11u5
ii  libdbi-perl               1.643-3+b1
ii  libpam0g                  1.4.0-9+deb11u1
ii  libssl1.1                 1.1.1n-0+deb11u3
ii  libstdc++6                10.2.1-6
ii  lsb-base                  11.1.0
ii  lsof                      4.93.2+dfsg-1.1
ii  mariadb-client-10.5       1:10.5.15-0+deb11u1
ii  mariadb-common            1:10.5.15-0+deb11u1
ii  mariadb-server-core-10.5  1:10.5.15-0+deb11u1
ii  passwd                    1:4.8.1-1
ii  perl                      5.32.1-4+deb11u2
ii  procps                    2:3.3.17-5
ii  psmisc                    23.4-2
ii  rsync                     3.2.3-4+deb11u1
ii  socat                     1.7.4.1-3
ii  zlib1g                    1:1.2.11.dfsg-2+deb11u2

Versions of packages mariadb-server-10.5 recommends:
ii  libhtml-template-perl  2.97-1.1

Versions of packages mariadb-server-10.5 suggests:
ii  mailutils [mailx]  1:3.10-3+b1
pn  mariadb-test       <none>
ii  netcat-openbsd     1.217-3

- -- Configuration Files:
/etc/logcheck/ignore.d.paranoid/mariadb-server-10_5 [Errno 13] Permission
denied: '/etc/logcheck/ignore.d.paranoid/mariadb-server-10_5'
/etc/logcheck/ignore.d.server/mariadb-server-10_5 [Errno 13] Permission denied:
'/etc/logcheck/ignore.d.server/mariadb-server-10_5'
/etc/logcheck/ignore.d.workstation/mariadb-server-10_5 [Errno 13] Permission
denied: '/etc/logcheck/ignore.d.workstation/mariadb-server-10_5'
/etc/mysql/mariadb.conf.d/50-server.cnf changed:
[server]
[mysqld]
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking
bind-address = 192.168.1.10
expire_logs_days        = 10
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci
[embedded]
[mariadb]
[mariadb-10.5]

- -- debconf information:
  mariadb-server-10.5/postrm_remove_databases: false
  mariadb-server-10.5/old_data_directory_saved:

-----BEGIN PGP SIGNATURE-----

iQFSBAEBCgA8FiEEreS674/HIyV9gBfdnAYPmOsbK2AFAmOHB04eHHJvc3Nib3ls
YW5Ac3RhbmZvcmRhbHVtbmkub3JnAAoJEJwGD5jrGytgF3QIAJ4VkPSvDTseUn8X
6Y0lwh9SoBpBPn3cGAZTt6tmGBZiYxW2pPTcv4M+WgtrTQ0CyEosoulDXMDEzYBy
FhmQSm3fFxia2UyAIYwH8pkpFtnttPJv5s8uj8mjq5n1h1GgqBzaYs7/B2x13vdg
z7iQw3k+hMkKCXpszxMmuWZav5bGPGYJLu2AYcruO7+LaPGhOntu05osu3207ld0
I8ks2tQFJTnK7/IHQBY1R/pDmjTOoQM8VTUFqZQAoNkuIIh4rkD93kG78mT9B0fn
M5i7Glcix9DdUOVcJV7R3IEQLRxWRMklqNDe2Fn/3eAZhO4nNmWBfy9reqHu2YC9
Nb96SOs=
=oEpE
-----END PGP SIGNATURE-----