How to log SQL syntax errors in MySQL with tcpdump

Today I needed to capture a list of erroring MySQL queries on a server without introducing client-side code changes or a man in the middle proxy.

The slow_query_log will only log successful queries. The general_log will log everything if raw_log is set to ON, but will not differentiate between successful and erroneous queries so I would have had to replay everything to find the erroring queries.

This post by Baron Schwartz in 2009 explains how to do use tcpdump to do this with mk-query-digest: https://www.xaprb.com/blog/2009/11/01/catching-erroneous-queries-without-mysql-proxy/ however mk-query-digest is no longer available and the arguments do not map perfectly to pt-query-digest.

This solution is 90% based on the one above but I've used a filter.txt to print the output inside the filter itself and then return false. There might be a better way to print the output.

• Install percona-toolkit or wget https://www.percona.com/get/pt-query-digest and make it executable somewhere in your $PATH
• Create filter.txt (needed due to an IF statement, otherwise it would give a syntax error):

my $event_ok=0; if ($event->{Error_msg}) { print Dumper $event; } $event_ok

• Run the following tcpdump

tcpdump -i eth0 port 3306 -s 65535 -xnq -tttt  | pt-query-digest --type tcpdump --filter filter.txt > mysql_errors

If you connect to MySQL and run an erroneous query:

MySQL> SELECT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

You should see the corresponding entry in the mysql_errors file:

$VAR1 = {
  Error_msg => 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'\' at line 1',
  Error_no => 1064,
  No_good_index_used => 'No',
  No_index_used => 'No',
  Query_time => '0.000345',
  Rows_affected => 0,
  Thread_id => 4294967298,
  Warning_count => 0,
  arg => 'SELECT',
  bytes => 6,
  cmd => 'Query',
  db => 'prtg',
  fingerprint => 'select',
  host => '<redacted>',
  ip => '<redacted>',
  port => '55924',
  pos_in_log => 349603848,
  ts => '200812 12:37:38.248034',
  user => undef
};

Note that the line db => 'prtg' is wrong and seems to be set by the first query to hit the log.