The MySQL error log is an essential part of database server performance monitoring. Whenever something goes wrong or performance degrades, the Error Logs are usually the first place we look to start troubleshooting.

The MySQL Error Log is one of three related log types:

• The Error Log: It contains information about errors that occur while the server is running (as well as server start and stop events).
• The General Query Log: This is a general record of what mysqld is doing (connect, disconnect, queries).
• The Slow Query Log: It consists of “slow” SQL statements as defined in the long_query_time global variable.

You can enable error log monitoring to allow Monyog to keep an eye on your MySQL Error Log, and notify you when something goes awry. Moreover, Monyog combines the General Query, Slow Query and Error logs in a single view for both network and cloud servers. For example, in the …

Although only available as a Release Candidate, MySQL 8 is already proving itself to be a huge leap forward in many regards. Error logging is no exception. The MySQL development team just announced that they have redesigned the error logging subsystem to use a new component architecture.

The redesign will allow the filtering of log events, as well as the routing of error log output to multiple destinations, via the enabling of multiple sink components.  This will make it possible to send error log events to third-party systems for additional formatting and analysis.

In today’s blog, we’ll explore how to employ MySQL 8’s component-based error logging to achieve a variety of logging configurations. Note that all this is specific for MySQL 8 and is not available in earlier versions and also not in MariaDB. However, MariaDB has an option to write the error log to the system “syslog” on systemd-based Linux variants – …

When evaluating a MySQL system one of the first things to look at is the MySQL error log. This is defined by the log[_-]error variable in the MySQL Configuration file. Generally found like:

grep log.error /etc/my.cnf
log_error=/var/lib/mysql/logs/mysql_error_log
log-error=/var/lib/mysql/logs/mysql_error_log

It is possible to find multiple rows because this could be defined in the [mysqld] and [mysqld_safe] sections. It is also possible it is incorrectly defined twice in any given section.

Immediately I see a problem here, and the following describes why. If you look at this file name, in this case it’s actually found, but the file is empty.

$ ls -l /var/lib/mysql/logs/mysql_error_log
-rw-r----- 1 mysql mysql 0 Feb 19 20:35 /var/lib/mysql/logs/mysql_error_log

An error log should never exist and be empty, because starting the instance producing messages. An error log could be empty because the system does …

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE …

I say correct because there are several incorrect approaches to managing MySQL logs. In MySQL you have two important log files, the MySQL error log (configured with –log-error) and the MySQL slow query log (configured with –log-slow-queries or –slow-query-log and –slow-query-log-file which is available …