Chris on MySQL

In my previous post, Capturing Erroneous Queries with MySQL Proxy, I showed how to capture erroneous queries, along with relevant information, that one could not effectively obtain from the MySQL general query log. However, in that post, I simply output the information to the terminal. Therefore, in this example, I want to show how to write this information to a proxy log file.

To change this to log to a log file does not require too many changes, and so this is a useful example on how to log any proxy-related information to a log file.

1. I perform a check for an existing log file (at least using the variable of name “log_file”), and then assign log_file to the location of where I want the log file to reside (at the beginning of the lua script).

   if (log_file == nil) then
     log_file = "C:/Program Files/MySQL/mysql-proxy-0.7.2/proxy.log"
   end

2. Directly after that, and a key element, is to then declare a file handle for this file and open it for writing purposes (directly after the above statements):

   local fh = io.open(log_file, "a+")

3. I create a local variable named “out_string” and assign all output that I want to write to this variable.
4. Once you have “out_string” fully populated, then just issue the following two commands:

   fh:write(out_string .. "n")
   fh:flush()

Now everything you have stored in the variable “out_string” will be written to the new proxy log file that you have opened.

Here is the full script for reference:

if (log_file == nil) then
  log_file = "C:/Program Files/MySQL/mysql-proxy-0.7.2/proxy.log"
end
local fh = io.open(log_file, "a+")

function read_query( packet )
  if string.byte(packet) == proxy.COM_QUERY then
    proxy.queries:append(1, packet, {resultset_is_needed = true} )
    local out_string = "+ ---------- Incoming query ---------- " .. "n" ..
      "+ Query = " .. string.sub(packet, 2) .. "n" ..
      "+ Time  = " .. os.date('%Y-%m-%d %H:%M:%S') .. "n" ..
      "+ ------------------------------------ "
    fh:write(out_string .. "n")
    fh:flush()
    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 = "---------- Returning results ----------" .. "n" ..
      "- time stamp = "
        .. os.date('%Y-%m-%d %H:%M:%S') .. "n" ..
      "- query = "
        .. inj.query .. "n" ..
      "- result.err.code = "
        .. res.raw:byte(2) + (res.raw:byte(3) * 256) .. "n" ..
      "- result.err.sql_state = "
        .. string.format("%q", res.raw:sub(5, 9)) .. "n" ..
      "- result.err.msg = "
        .. string.format("%q", res.raw:sub(10)) .. "n" ..
      "- proxy.connection.client.default_db = "
        .. proxy.connection.client.default_db .. "n" ..
      "- proxy.connection.client.username = "
        .. proxy.connection.client.username .. "n" ..
      "- proxy.connection.client.address = "
        .. proxy.connection.client.src.name .. "n" ..
      "- proxy.connection.server.thread_id = "
        .. proxy.connection.server.thread_id .. "n"
    fh:write(out_string .. "n")
    fh:flush()
  end
end

Tags: chris calender, log file, logging, MySQL, mysql proxy, proxy log file

This entry was posted on Monday, November 30th, 2009 at 3:57 pm and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.