[MDEV-19210] use environment file in systemd units for _WSREP_* Created: 2019-04-07  Updated: 2023-12-20

Status: In Testing
Project: MariaDB Server
Component/s: Configuration
Affects Version/s: None
Fix Version/s: 11.4

Type: Bug Priority: Major
Reporter: Anel Husakovic Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: systemd

Issue Links:
Relates
relates to MDEV-14707 systemd: remove PermissionsStartOnly=... Open
relates to MDEV-23110 Support for Galera Multi-Instance in ... Open
relates to MDEV-29884 Ctrl-Cing out of galera_new_cluster d... Open

 Description   

We used to run systemctl set-environment to pass _WSREP_START_POSITION. This is bad because:

  • it clutter systemd's environment (yes, pid 1) - upsets some Debian people dbz 984996
  • it requires root privileges - hence it will fix MDEV-14707, as such it cases all ExecStartPre scripts to be run as root. PermissionsStartOnly was deprecated by systemd in 2018.
  • options (like LimitNOFILE=) are not applied to ExecStartPre scripts.

The solution is:

Let's just create an environment file in ExecStartPre=, that is read before ExecStart= kicks in. We have _WSREP_START_POSITION around for the main process without any downsides.

 Comments   
Comment by Geoff Montee (Inactive) [ 2019-09-24 ]

According to a conversation between marko and danblack on Zulip, we may need to merge this pull request to improve our Galera testing with systemd.

Comment by Michael Widenius [ 2022-02-15 ]

Please add a description of:

  • What this task is supposed to solve/do
  • What is the problem
  • What is the solution(s)
Comment by Andrew Hutchings [ 2023-08-08 ]

Reverted this as it broke something in old buildbot.

Comment by Daniel Black [ 2023-12-20 ]

Testing:

From docker compose written to help this:

Two test environments: - top level Almalinux. , deb directory, there's small bit of debian based tested.

build

 
~/repos/mariadb.org-tools/daniel/MDEV-19210 
$ docker-compose build
 
Building node1
 
STEP 1/4: FROM quay.io/almalinuxorg/8-init
 
Trying to pull quay.io/almalinuxorg/8-init:latest...
 
....

General principle is that a shutdown of any galera node will be able to recover an IST, provided the data cache of the donor node contains all the changes, without a full SST.

start a nodes - just systemd in a container at this point

 
$ docker-compose up
 
Creating network "mdev-19210_default" with the default driver
 
Creating volume "mdev-19210_v1" with default driver
 
Creating volume "mdev-19210_v2" with default driver
 
Creating volume "mdev-19210_v3" with default driver
 
Creating mdev-19210_node1_1 ... done
 
Attaching to mdev-19210_node1_1

Leave this as a running terminal

Per docs this is first step in a cluster:

start new cluster - galera_new_cluster

 
$ docker-compose exec node1 galera_new_cluster
 
$ docker-compose exec node1 journalctl -u mariadb.service

This shows it up as a primary.

$ docker-compose exec node2 systemctl start mariadb.service
 
$ docker-compose exec node2 journalctl -u mariadb.service -f
 
-- Logs begin at Wed 2023-12-20 06:32:51 UTC. --
 
Dec 20 06:35:01 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:35:01 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 2)
 
Dec 20 06:35:01 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:35:01 0 [Note] WSREP: Processing event queue:... -nan% (0/0 events) complete.
 
Dec 20 06:35:01 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:35:01 0 [Note] WSREP: Member 1.0 (f1bb63bfc6aa) synced with group.
 
Dec 20 06:35:01 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:35:01 0 [Note] WSREP: Processing event queue:...100.0% (1/1 events) complete.
 
Dec 20 06:35:01 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:35:01 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 2)
 
Dec 20 06:35:01 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:35:01 2 [Note] WSREP: Server f1bb63bfc6aa synced with group
 
Dec 20 06:35:01 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:35:01 2 [Note] WSREP: Server status change joined -> synced
 
Dec 20 06:35:01 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:35:01 2 [Note] WSREP: Synchronized with group, ready for connections
 
Dec 20 06:35:01 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:35:01 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 
Dec 20 06:35:01 f1bb63bfc6aa systemd[1]: Started MariaDB 11.4.0 database server.
 
^C

Same with node3.

start node3

 
$ docker-compose exec node3 systemctl start mariadb.service
 
 
 
$ docker-compose exec node3 mariadb -e 'show global status like "wsrep%"'

Test 1: shutdown a node. Add data to cluster, see if, how it recovers

$ docker-compose exec node2 systemctl stop mariadb.service
 
$ docker-compose exec node3 mariadb test -e 'create table t (i int not null primary key); insert into t select seq from seq_1_to_10000'
 
$ docker-compose exec node2 journalctl -f -u mariadb.service
 
-- Logs begin at Wed 2023-12-20 06:32:51 UTC. --
 
Dec 20 06:38:33 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:38:33 0 [Note] WSREP: Deinitializing allowlist service v1
 
Dec 20 06:38:33 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:38:33 0 [Note] InnoDB: FTS optimize thread exiting.
 
Dec 20 06:38:33 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:38:33 0 [Note] InnoDB: Starting shutdown...
 
Dec 20 06:38:33 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:38:33 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
 
Dec 20 06:38:33 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:38:33 0 [Note] InnoDB: Buffer pool(s) dump completed at 231220  6:38:33
 
Dec 20 06:38:33 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:38:33 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
 
Dec 20 06:38:33 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:38:33 0 [Note] InnoDB: Shutdown completed; log sequence number 59218; transaction id 39
 
Dec 20 06:38:33 f1bb63bfc6aa mariadbd[61]: 2023-12-20  6:38:33 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
Dec 20 06:38:33 f1bb63bfc6aa systemd[1]: mariadb.service: Succeeded.
 
Dec 20 06:38:33 f1bb63bfc6aa systemd[1]: Stopped MariaDB 11.4.0 database server.

Trigger start:

$ docker-compose exec node2 systemctl start mariadb.service

Observer form logs:
1. a quick start was made to find the position
2. the second start resumed from the log position
3. I saw a full SST here, thought it would be cached.

$ docker-compose exec node2 mariadb test -e 'select count(*) from t'
 
+----------+
 
| count(*) |
 
+----------+
 
|    10000 |
 
+----------+


$ docker-compose exec node3 systemctl stop mariadb.service
 
$ docker-compose exec node3 bash -c 'rm -rf /var/lib/mysql/*'
 
$ docker-compose exec node3 systemctl start mariadb.service

Observer the full rejoin of node3.

Try same on node1 to ensure it wasn't special because it was the bootstrap node.

reset evironment

 
docker-compose down -v

Comment by Daniel Black [ 2023-12-20 ]

Debian. Because Debian configures mariadb to autostart on install is a little different. I also found I couldn't repeat it with the same reliability. Relying on packages may be required -

Change to ./deb directory:

build

 
docker-compose build

Start node1 only

docker-compose start node1

Because of unknown reasons, I needed to revert back to container manager rather than podman to continue the following.

undo debian start and bootstrap node1

 
$ podman exec deb_node1_1 systemctl stop mariadb.service
 
$ podman exec deb_node1_1 galera_new_cluster
 
$ podman exec deb_node1_1 systemctl status mariadb.service
 
* mariadb.service - MariaDB 11.4.0 database server
 
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; preset: enabled)
 
    Drop-In: /etc/systemd/system/mariadb.service.d
 
             `-migrated-from-my.cnf-settings.conf
 
     Active: active (running) since Wed 2023-12-20 07:08:48 UTC; 6s ago
 
       Docs: man:mariadbd(8)
 
             https://mariadb.com/kb/en/library/systemd/
 
    Process: 88 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
 
    Process: 89 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && echo _WSREP_START_POSITION=$VAR > /run/mysqld/wsrep-start-position || exit 1 (code=exited, status=0/SUCCESS)
 
    Process: 165 ExecStartPost=/bin/rm -f /run/mysqld/wsrep-start-position (code=exited, status=0/SUCCESS)
 
    Process: 166 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
 
   Main PID: 148 (mariadbd)
 
     Status: "Taking your SQL requests now..."
 
      Tasks: 18 (limit: 307)
 
     Memory: 92.6M
 
        CPU: 286ms
 
     CGroup: /system.slice/mariadb.service
 
             `-148 /usr/sbin/mariadbd --wsrep-new-cluster --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
 
 
 
Dec 20 07:08:48 3f6a5b840603 mariadbd[148]: Version: '11.4.0-MariaDB-1:11.4.0+maria~deb12'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
 
Dec 20 07:08:48 3f6a5b840603 mariadbd[148]: 2023-12-20  7:08:48 1 [Note] WSREP: Lowest cert index boundary for CC from group: 1
 
Dec 20 07:08:48 3f6a5b840603 mariadbd[148]: 2023-12-20  7:08:48 1 [Note] WSREP: Min available from gcache for CC from group: 1
 
Dec 20 07:08:48 3f6a5b840603 mariadbd[148]: 2023-12-20  7:08:48 1 [Note] WSREP: Server 3f6a5b840603 synced with group
 
Dec 20 07:08:48 3f6a5b840603 mariadbd[148]: 2023-12-20  7:08:48 1 [Note] WSREP: Server status change joined -> synced
 
Dec 20 07:08:48 3f6a5b840603 mariadbd[148]: 2023-12-20  7:08:48 1 [Note] WSREP: Synchronized with group, ready for connections
 
Dec 20 07:08:48 3f6a5b840603 mariadbd[148]: 2023-12-20  7:08:48 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 
Dec 20 07:08:48 3f6a5b840603 systemd[1]: Started mariadb.service - MariaDB 11.4.0 database server.
 
Dec 20 07:08:48 3f6a5b840603 /etc/mysql/debian-start[168]: Upgrading MySQL tables if necessary.
 
Dec 20 07:08:48 3f6a5b840603 /etc/mysql/debian-start[180]: Checking for insecure root accounts.

Important bit is at the bottom, the debian-start executes ok. This was the main difference in the PR from the Almalinux testing above.

start node2

 
$ docker-compose up node2
 
Creating deb_node2_1 ... done
 
Attaching to deb_node2_1


$ podman exec deb_node2_1 systemctl stop mariadb.service
 
$ podman exec deb_node2_1 bash -c 'rm -rf /var/lib/mysql/*'
 
 
 
$ podman exec deb_node2_1 systemctl status mariadb.service
 
 
 
$ podman exec deb_node2_1 systemctl start mariadb.service
 
 
 
$ podman exec deb_node2_1 systemctl status mariadb.service
 
 
 
ABRTed
 
 
 
manually start:
 
$ podman exec --user mysql  deb_node2_1  /usr/sbin/mariadbd
 
 
 
...
 
2023-12-20  7:25:20 0 [Warning] WSREP: 0.0 (3f6a5b840603): State transfer to 1.0 (a74a18339706) failed: -111 (Connection refused)
 
2023-12-20  7:25:20 0 [ERROR] WSREP: ./gcs/src/gcs_group.cpp:gcs_group_handle_join_msg():1207: Will never receive state. Need to abort.
 
2023-12-20  7:25:20 0 [Note] WSREP: gcomm: terminating thread
 
2023-12-20  7:25:20 0 [Note] WSREP: gcomm: joining thread
 
2023-12-20  7:25:20 0 [Note] WSREP: gcomm: closing backend
 
2023-12-20  7:25:21 0 [Note] WSREP: view(view_id(NON_PRIM,9ee8e0e6-8f0b,154) memb {
 
	edc9ced6-9730,0
 
} joined {
 
} left {
 
} partitioned {
 
	9ee8e0e6-8f0b,0
 
})
 
2023-12-20  7:25:21 0 [Note] WSREP: PC protocol downgrade 1 -> 0
 
2023-12-20  7:25:21 0 [Note] WSREP: view((empty))
 
2023-12-20  7:25:21 0 [Note] WSREP: gcomm: closed
 
2023-12-20  7:25:21 0 [Note] WSREP: /usr/sbin/mariadbd: Terminated.

Comment by Daniel Black [ 2023-12-20 ]

for testing - bb-11.4-pr2726-MDEV-19210-environment-file-pkgtest, same as pull request 2726.

packages - https://ci.mariadb.org/41762/

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