MySQL / MariaDB not logging errors (log_error file empty) on Ubuntu

Written by Claudio Kuenzler - 0 comments

Published on October 20th 2021 - Listed in MySQL MariaDB Database Linux

---

While analyzing a connection problem between a client and a MariaDB 10.2 server, the so-called "error log" needed to be checked for warnings and errors. However the file (/var/log/mysql/error.log) did not contain any logged events.

Checking configuration and permissions

First thing to verify is the MySQL/MariaDB config whether or not the error_log was actually enabled. On this machine, running MariaDB 10.2 on Ubuntu 18.04 (Bionic), the config files seemed in order:

ck@mariadb:~$ cat /etc/mysql/conf.d/mariadb.cnf | grep error
# log errors
log_error=/var/log/mysql/error.log

But the running config needs to be verified as well. This can be done by checking the global variables:

root@mariadb:~# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 308342315
Server version: 10.2.28-MariaDB-1:10.2.28+maria~bionic-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show global variables where variable_name like '%error%';
+--------------------+--------------------------+
| Variable_name      | Value                    |
+--------------------+--------------------------+
| log_error          | /var/log/mysql/error.log |
| max_connect_errors | 100                      |
| max_error_count    | 64                       |
| slave_skip_errors  | OFF                      |
+--------------------+--------------------------+
4 rows in set (0.00 sec)

Same here: The log_error variable is set to a path - which means error logging is enabled.

Another potential reason of failure could be that the file does not exist, or that the permissions are wrong. The MySQL/MariaDB process, running usually under the user "mysql", needs to be able to write into this file:

root@mariadb:/var/log/mysql# ls -la
total 6717652
-rw-r----- 1 mysql adm         0 Oct 20 00:08 error.log
-rw-r----- 1 mysql adm         0 Oct 19 00:07 error.log.1
-rw-r----- 1 mysql adm   5037450 Apr 10  2020 error.log.8.gz
-rw-rw---- 1 mysql adm 105161366 Oct 18 11:58 mariadb-bin.007219
-rw-rw---- 1 mysql adm 105400817 Oct 18 13:26 mariadb-bin.007220
[...]

Inside the directory /var/log/mysql/ the files are listed. The error.log file clearly exists, and with the correct permissions; the mysql user is owner and can therefore read and write this file. However the size is 0 - meaning the file is empty and nothing was logged.

The fact that the MariaDB process is able to successfully write the binary logs (which are enabled on this machine, too) in the same path, shows that there is definitely no permission problem.

Syslog redirection in place!

It turns out that with the MariaDB package, an additional config file was installed, redirecting the error log to syslog under the [mysqld_safe] config section:

admck@mariadb:~$ cat /etc/mysql/conf.d/mysqld_safe_syslog.cnf
[mysqld_safe]
skip_log_error
syslog

This config file was installed by the mariadb-server package:

admck@mariadb:~$ dpkg -S /etc/mysql/conf.d/mysqld_safe_syslog.cnf
mariadb-server-10.2: /etc/mysql/conf.d/mysqld_safe_syslog.cnf

As Ubuntu starts MySQL/MariaDB with mysqld_safe (which can be seen in the process list), these config options are read and applied during the start of MariaDB. They cause the following behaviour:

• skip_log_error: If log_error was set in a config file, this parameter causes MySQL/MariaDB to ignore the log_error variable - therefore switching off the error logging
• syslog: This parameter tells MySQL/MariaDB to log to syslog instead of a dedicated file

It's important to note that log_error and syslog cannot co-exist! Otherwise a warning will be issued and log_error takes precedence.

Once these two config options were commented/disabled and MariaDB restarted, the /var/log/mysql/error.log now started to log warnings and errors:

admck@mariadb:~$ ls -la /var/log/mysql/error.log
-rw-r----- 1 mysql adm 953061 Oct 20 17:14 /var/log/mysql/error.log

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.