Cannot get Amplify to monitor MariaDB/mysql. amplify.agent.common.errors.AmplifySubprocessError: (message=ps xao pid,ppid,command | grep -E 'mysqld( |$)', payload={'returncode': 1, 'error': ''})

[Danrancan]

I am trying to get amplify to monitor mysql database on ubuntu server 20.04 running on a raspberry pi 4. I am sure I have set everything up correctly and have crossreferenced my work with many internet guides. However, mysql does not appear in the nginx amplify web interface after setting things up. My logs show the following errors:

2022-08-08 10:32:41,134 [1824839] supervisor ps nginx output: ['   2074       1 nginx: master process /usr/sbin/nginx -c /etc/nginx/nginx.conf', '1740162    2074 nginx: worker process', '1740163    2074 nginx: worker process', '1740165    2074 nginx: worker process', '1740166    2074 nginx: worker process', '1740168    2074 nginx: cache manager process', '']
2022-08-08 10:32:41,161 [1824839] supervisor nginx objects: ['5057a79cf47c63d99f326ae55fea2c99bc0e2dcfd6bc5cdc5492d770f0a55284']
2022-08-08 10:32:41,162 [1824839] supervisor status objects: []
2022-08-08 10:32:41,162 [1824839] supervisor api objects: []
2022-08-08 10:32:41,228 [1824839] supervisor failed to find running mysqld via "ps xao pid,ppid,command | grep -E 'mysqld( |$)'" due to AmplifySubprocessError
2022-08-08 10:32:41,228 [1824839] supervisor additional info:
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/amplify/ext/mysql/managers.py", line 154, in _find_local
    ps, _ = (ps, None) if ps is not None else subp.call(PS_CMD)
  File "/usr/lib/python3/dist-packages/amplify/agent/common/util/subp.py", line 34, in call
    raise AmplifySubprocessError(message=command, payload=dict(returncode=process.returncode, error=raw_err))
amplify.agent.common.errors.AmplifySubprocessError: (message=ps xao pid,ppid,command | grep -E 'mysqld( |$)', payload={'returncode': 1, 'error': ''})
2022-08-08 10:32:41,229 [1824839] supervisor mysql objects: []

Could someone help me debug and fix this? Thank you.

[alpiua]

I got the same on Ubuntu 20.04, MariaDB 10.6.9.

[nomadas-experience]

Same here

[triplesixman]

Same here, Debian 11/mariadb

[suryawomshi]

Same Issue, Not detecting the MariaDB 10.6.11 on Ubuntu 22 LTS, Configuration is correct, able login to mariadb unix socket using amplify-agent username and password and query the global status metrics.

workaround

Change

From ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

To ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

restart daemon, maridb and amplify-agent to start getting data

[artuze]

For what it's worth, I believe MariaDB changed this on their end in 10.5:

When starting the MariaDB server via the systemd service it will be started using the mariadbd binary name, so this will now show up in the system process list instead of mysqld — Changes and Improvements in MariaDB 10.5

Solutions online suggest rewriting /lib/systemd/system/mariadb.service file to revert it, including in @suryawomshi's response above. Because this hasn't changed back in 6+ releases (as far as I know), it seems like it'd be more appropriate to adjust the mysql/util.py file to either to look for both mysqld and mariadb (e.g. grep -E '(mysql|mariadb)d( |$)') or write in the MariaDB check as an alternate if MySQL fails to return (my Python's not so great, otherwise I'd try to provide some code there).

Alternatively, does it make sense to break MariaDB out as a separate extension entirely? I'd almost argue that it doesn't, but I'll leave that to the folks here to decide.

[Danrancan]

I believe I have found the PROPER solution to this problem. This solution is a small modification from the answer I received on my Stack Overflow post.

I have written a short tutorial on how to do this here: https://danran.rocks/2023/06/the-trick-to-monitoring-maria-databse-mariadb-mysql-in-nginx-amplify-on-ubuntu-server-20-04-for-raspberry-pi/

In short (on Ubuntu 20.04 for Raspberry Pi), you need to create the filenginx-amplify.confin /etc/systemd/system/mariadb.service.d/:

sudo nano /etc/systemd/system/mariadb.service.d/nginx-amplify.conf

and add the following content to it...

# THIS FILE IS MEANT TO CHANGE THE SYMLINK PATH OF
# EXECSTART SO NGINX AMPLIFY PICKS UP MYSQL/MARIADB:
#
# It's not recommended to modify the
# file /lib/systemd/system/mariadb.service in-place,
# because it will be overwritten during package upgrades.
# If you want to customize that file, the best way is to create a
# file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will
# be parsed after the file mariadb.service itself is parsed.
#
# For more info about custom unit files, see systemd.unit(5) or
# https://mariadb.com/kb/en/mariadb/systemd/

[Service]

# Clear the original "ExecStart=" settings:
# To clear the original ExecStart in
# the /lib/systemd/system/mariadb.service file, you must first
# enter an "ExecStart=" here with an empty value before adding
# your newly created ExecStart for Amplify.
# https://stackoverflow.com/questions/68817332/why-use-execstart-with-no-value-before-another-execstart-new-value-in-a-syst
ExecStart=

# Now, Set Nginx Amplify "ExecStart=" settings:
# For Nginx Amplify to pick up the mysql/mariadb settings, you
# must change the empty "ExecStart=" value to the mysql symlink path so
# that Amplify Agent picks it up.
ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION

Then run:

sudo systemctl daemon-reload
sudo systemctl restart mariadb
sudo systemctl restart amplify-agent