Capturing Erroneous Queries with MySQL Proxy

The MySQL General Query Log can be very beneficial as it records all queries sent to the server. However, there are times you want more specific information, which can be hard, if not impossible, to filter from this log.

For instance, say you want to capture only queries that cause an error. The errors are not included in the general query log, so filtering based on that is not possible. However, we can easily capture this information using MySQL Proxy with a simple Lua script.

To implement this, the key is to examine the query status in the returned packet from the server to see if there is an error or not. Should the query status be that of an error, then we have what we want, and all that remains is to output whatever relevant information we’d like to see.

Code-wise, we want to examine the returned packet in function read_query_result(), and then check the query status to see if it returns an error (i.e., proxy.MYSQLD_PACKET_ERR). So, we have the following:

function read_query_result (inj)
  local res = assert(inj.resultset)
  if res.query_status == proxy.MYSQLD_PACKET_ERR then
    -- here we have query that returns an error
    print(...)
  end
end

And that is basically all there is to it.

Here is the full script, so you can see it in its entirety:

function read_query( packet )
  if string.byte(packet) == proxy.COM_QUERY then
    proxy.queries:append(1, packet, {resultset_is_needed = true} )
    return proxy.PROXY_SEND_QUERY
  end
end

function read_query_result (inj)
  local res = assert(inj.resultset)
  if res.query_status == proxy.MYSQLD_PACKET_ERR then
    local out_string =
      "Time Stamp = " .. os.date('%Y-%m-%d %H:%M:%S') .. "n" ..
      "Query      = " .. inj.query .. "n" ..
      "Error Code = " .. res.raw:byte(2)+(res.raw:byte(3)*256) .. "n" ..
      "SQL State  = " .. string.format("%q", res.raw:sub(5, 9)) .. "n" ..
      "Err Msg    = " .. string.format("%q", res.raw:sub(10)) .. "n" ..
      "Default DB = " .. proxy.connection.client.default_db .. "n" ..
      "Username   = " .. proxy.connection.client.username .. "n" ..
      "Address    = " .. proxy.connection.client.src.name .. "n" ..
      "Thread ID  = " .. proxy.connection.server.thread_id .. "n"
    print(out_string .. "n")
  end
end

Notes:

  1. In function read_query(), when I append the query, I also append the “{resultset_is_needed = true}” flag, as you need to set this to “true” in order to get the resultset returned to the proxy.
  2. In function read_query_result(), I assign all information to a variable, and then simply “print()” the variable. This does add a line of code, but in the end, it makes it easier to write this to an output file instead of to the terminal (or to both), if you desire.

Tags: , , , , , , ,

4 Responses to “Capturing Erroneous Queries with MySQL Proxy”

  1. [...] is now, but the concept remains the same: direct your database traffic through a man-in-the-middle. Chris Calender’s post on capturing erroneous queries with MySQL Proxy shows one use for Proxy. But wait. MySQL Proxy is just inspecting the MySQL protocol. And unless [...]

  2. [...] Mark Leith has been catching erroneous queries, without MySQL proxy, having been inspired by Chris Calender’s post, Capturing Erroneous Queries with MySQL Proxy. [...]

  3. [...] Chris on MySQL Tips and Solutions for MySQL, MySQL Proxy, and other MySQL-related Topics « Capturing Erroneous Queries with MySQL Proxy [...]

  4. [...] You may want to log a sample of queries on your older setup and replay them on your new setup to catch the errors. Peter suggested using mk-upgrade in the maatkit tool which is a very sensible choice, also on my end I decided to catch live errors using mysql-proxy and the capture filter function. [...]


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty