Home / server / Questions / 529140
Accepted
eComEvo
Asked: 2013-08-07 12:03:04 +0800 CST

Identifying queries that crash MySQL service

  • 772

There is some query on a server that is regularly crashing the MySQL service.

I've tried sorting through the slow query log but 100's of queries pile up behind whatever is crashing the service so trying to figure out what was the progenitor of the whole mess has proven too difficult.

Is there a log that specifically shows the last query executed before the mysql service fails?

mysql

2 Answers

  1. Best Answer

    I have a general log file logging every query at /var/run/mysqld/mysqld.log, If you enable that level of logging, you could look at the timestamp for when the crash is logged and then find the associated query.

    how to enable general logging: mysql> SET GLOBAL general_log_file='/var/log/mysql.log'; Query OK, 0 rows affected (0.00 sec)

    mysql> SET GLOBAL general_log = 1; Query OK, 0 rows affected (0.00 sec)

    Then the file /var/log/mysql.log should start filling up with statements. Once your done disable the general log:

    mysql> SET GLOBAL general_log = 0; Query OK, 0 rows affected (0.01 sec)

    • 1

  2. Besides the logs, you can use the query SHOW FULL PROCESSLIST to see what MySQL is currently running to figure out what's holding it up. Use the Time column to see for how many seconds the connection has been open (which usually corresponds to how long the query has been running).

    • 0