4

It is a purely MySQL tweaking question on logging the failed SQL attempts. I need a solution to log the failed SQLs on the MySQL database.

The MySQL general query log does not work here, because it will log only the successful queries only. I need to know the failed ones in a different log file.

My scenario is: I need to cleanup a big messy SQLs and PHP Scripts (literally, around 7,000 SQLs being executed per page). Since the errors are suppressed on the server, even the SQLs failed queries will continue and show the good contents on the website. But I am sure, there are a lot of SQL errors. I found 2 links but cannot retry. This one and writing server side audit plugin in MySQL.

Is using Kontrolbase an answer as mentioned here.

Community
  • 1
  • 1
Bimal Poudel
  • 1,214
  • 2
  • 18
  • 41
  • You could consider logging the errors on the PHP side into the Apache error log. Or is there a reason that this is not a viable alternative ? – Lorenz Meyer May 25 '14 at 17:48
  • No Lorenz, it is nothing about PHP, but you point to check for log is good. I want to log invalid query being executed on the db server. – Bimal Poudel May 25 '14 at 22:01

1 Answers1

0

Percona Server 5.5.37 and 5.6.17 include an implementation of an audit plugin which is free and open-source. Read about it here: http://www.percona.com/doc/percona-server/5.6/management/audit_log_plugin.html

The audit plugin logs both successful queries and erroneous queries. Here's an example where I queried my table foo and then a non-existent table foobar:

<AUDIT_RECORD
  "NAME"="Query"
  "RECORD"="489_2014-05-23T14:06:09"
  "TIMESTAMP"="2014-05-23T14:08:13 UTC"
  "COMMAND_CLASS"="select"
  "CONNECTION_ID"="32709"
  "STATUS"="0"
  "SQLTEXT"="select * from foo"
  "USER"="root[root] @ localhost []"
  "HOST"="localhost"
  "OS_USER"=""
  "IP"=""
/>
<AUDIT_RECORD
  "NAME"="Query"
  "RECORD"="490_2014-05-23T14:06:09"
  "TIMESTAMP"="2014-05-23T14:08:19 UTC"
  "COMMAND_CLASS"="select"
  "CONNECTION_ID"="32709"
  "STATUS"="1146"
  "SQLTEXT"="select * from foobar"
  "USER"="root[root] @ localhost []"
  "HOST"="localhost"
  "OS_USER"=""
  "IP"=""
/>

STATUS=1146 is indicated for the error query above. See http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html to reference server errors. For example, 1146 is ER_NO_SUCH_TABLE.

If you can't use this solution, then you'll have to change your PHP code to remove the error suppression, and log the errors.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • All right, and thanks. It seems it is good for successful queries. I need to know the queries that were failed to execute. The error list is also important. Can we tweak the server config and dump the failed queries to a log file? – Bimal Poudel Jan 21 '15 at 09:52
  • @BimalPoudel, in my example above, it does log queries that result in an error, and the error value is entered into the audit log in the STATUS field. What am I missing? – Bill Karwin Jan 22 '15 at 06:45