can't stop mysql/mariadb with `systemctl stop`

Question

EDIT it started working by itself. I can't see how to delete or flag this question.

Mariadb is up and running on CentOS 7

$ ps aux | grep mysqld
mysql     4249  0.0  9.0 841448 89576 pts/0    Sl   12:25   0:00 /usr/libexec/mysqld ...snip...
$ mysqladmin ping && echo ok
mysqld is alive
ok

But when I try to stop it.

$ sudo systemctl stop mariadb.service && echo ok
ok

Running systemctl status mariadb before and after shows that this line has been logged:

Nov 17 13:02:31 sk-test1 systemd[1]: Stopped MariaDB database server.

Nevertheless, the above ps/ping command shows mysqld is still running with the same PID.

Why won't the service stop?

Solution 1

To check the status of MySQL or MariaDB

systemctl status mariadb

To Stop MySQL/MariaDB

systemctl stop mariadb

Solution 2

When there is a condition when it is not safe for MySQL or MariaDB to shutdown in a safe mode, it will just freeze. This hapens to me each time I run out of disk space.

The best way to solve this is to add more disk space, but if this is not possibe, I am stopping all applications, then kill the service. You will lose some pending transactions, but the database integrity should be ok, if you use XtraDB or InnoDB engine.

XtraDB is default for MariaDB. InnoDB is default for recent version of Oracle MySQL and Percona server.

Edit 1: To shutdown MariaDB or Mysql service you can use mysqladmin shutdown. Or just run: kill -SIGTERM pid-of-mysqld-process. See also: https://mariadb.com/kb/en/mariadb/shutdown/

Edit 2:

If you restart MySQL durring a write operation (INSERT, UPDATE, DELETE, ALTER), the transaction will be rolled back by InnoDB engine. You can see the progress of the rollback with show engine innodb status. See bellow.

Check which queries are slow. The following command returns all running queries that are were started 10 seconds or more ago.

mysql -e "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND<>'Sleep' and TIME > 10"

If you have a slow query that is using InnoDB engine, you can see what its progress with:

mysql -e "show engine innodb status \G"|less

Comments

[spraff]

systemctl status mariadb wongly indicates the service was stopped. I updated the question.

[spraff]

There's plenty of disk space. What else would be an "unsafe" condition?

[Mircea Vutcovici]

Check the MariaDB logs.

[spraff]

It's mysteriously started working again. Thanks anyway.