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

Written by - 0 comments

Published on - 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.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder