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

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

function read_query_result (inj)
  local res = assert(inj.resultset)	
  if res.query_status == proxy.MYSQLD_PACKET_ERR then
    local out_string = 	
      "Time Stamp = " ..'%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    = " .. .. "n" ..
      "Thread ID  = " .. proxy.connection.server.thread_id .. "n"
    print(out_string .. "n")


  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.

Setting up MySQL Proxy as a Service on Windows

In the newer versions of MySQL Proxy (0.7.2 and newer), it is now possible to create a Windows service for MySQL Proxy due to the inclusion of “mysql-proxy-svc.exe”.

Basically, you could use “mysql-proxy-svc.exe” in lieu of “mysql-proxy.exe” from the command line and you would not notice a difference. However, just invoking this will not create a service for MySQL Proxy. Furthermore, you cannot invoke it with the “–install” option as you can with mysqld.

But, you can create a service for MySQL Proxy using the “sc” command with “mysql-proxy-svc.exe”.

Say I want to create a service that starts automatically when my system is started/rebooted. I also want it to load a certain lua script, say capture-errors.lua. Given this, I would use the following command:

sc create "proxy" DisplayName= "MySQL Proxy" start= "auto" 
binPath= "C:Program FilesMySQLmysql-proxy-0.7.2binmysql-proxy-svc.exe 

Here is the session from my console:

C:Program FilesMySQLmysql-proxy-0.7.2bin>sc create "proxy" 
DisplayName= "MySQL Proxy" start= "auto" 
binPath= "C:Program FilesMySQLmysql-proxy-0.7.2binmysql-proxy-svc.exe 
[SC] CreateService SUCCESS

Once you have this set-up, you can easily start-up or shutdown the proxy. For instance, the following two commands will start and stop the proxy, respectively:

net start proxy
net stop proxy

Now, a couple of notes about the “sc create” statement:

  1. This will create the service with the name of “proxy”.
  2. I used a “start” type of “auto”. This will mean it will start up automatically when you reboot your system. If you remove that option, it will default to “manual”.
  3. You need a space after each equal sign (=).
  4. I had to use double-quotes (“) around each parameter value, as well as the service name, otherwise it wouldn’t create the service properly on my machine.
  5. The lua script, capture-errors.lua, is located in the proxy basedir (C:Program FilesMySQLmysql-proxy-0.7.2) and so it can be read from that location without a full path. If you have this in a different location, you would need to specify the full path.

If for some reason you want to delete this service, you can issue the following command (where “proxy” is the name of the service that you gave it using the above command):

sc delete proxy

And for more details on “sc”, please refer to the following Microsoft KB article: