Getting Started with MySQL Proxy

[sorry4uboth]

More Customized Logging

The simple and effective logging script from the previous section is tempting, but it's really basic. We have had a glimpse of the Proxy internals, and we have seen that we can get better information, and these logs can be much more interesting than a bare list of queries. For example, we would like to report if a query was successful or rejected as a syntax error, how many rows were retrieved, how many rows were affected.
We know all the elements to reach this goal. The script will be a bit longer, but not much.

 -- logs.lua
 assert(proxy.PROXY_VERSION >= 0x00600,
 "you need at least mysql-proxy 0.6.0 to run this module")

 local log_file = os.getenv("PROXY_LOG_FILE")
 if (log_file == nil) then
  log_file = "mysql.log"

 end

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

In the global part of the script, we check that we're using an appropriate version of the Proxy, since we are using features that are not available in version 0.5.0. Then we set the filename, taking it from a environment variable, or assigning the default value.

 function read_query( packet )
  if string.byte(packet) == proxy.COM_QUERY then
   query = string.sub(packet, 2)
   proxy.queries:append(1, packet )
   return proxy.PROXY_SEND_QUERY
  else
    query = ""

  end
 end

The first function does little work. It appends the query to the proxy queue, so that the next function will be triggered when the result is ready.

 function read_query_result (inj)
  local row_count = 0
  local res = assert(inj.resultset)
  local num_cols = string.byte(res.raw, 1)
  if num_cols > 0 and num_cols < 255 then
   for row in inj.resultset.rows do
    row_count = row_count + 1
   end
  end
  local error_status =""
  if res.query_status and (res.query_status < 0 ) then
    error_status = "[ERR]"

  end
  if (res.affected_rows) then
    row_count = res.affected_rows
  end
  --
  -- write the query, adding the number of retrieved rows
  --
  fh:write( string.format("%s %6d -- %s {%d} %s\n", 
   os.date('%Y-%m-%d %H:%M:%S'), 
   proxy.connection["thread_id"], 
   query, 
   row_count,
   error_status))
  fh:flush()
 end

In this function we can check if we are dealing with a data manipulation query or a select query. If there are rows, the function counts them, and the result is printed in braces to the logfile. If there are affected rows, then this is the number that is reported. We also check if there was an error, in which case the information is returned in brackets, and, finally, everything gets written to the logfile. Here is an example:

 2007-06-29 16:41:10   33 -- show databases {5} 
 2007-06-29 16:41:10   33 -- show tables {2} 
 2007-06-29 16:41:12   33 -- Xhow tables {0} [ERR]
 2007-06-29 16:44:27   34 -- select * from t1 {6} 
 2007-06-29 16:44:50   34 -- update t1 set id = id * 100 where c = 'a' {2} 
 2007-06-29 16:45:53   34 -- insert into t1 values (10,'aa') {1} 
 2007-06-29 16:46:07   34 -- insert into t1 values (20,'aa'),(30,'bb') {2} 
 2007-06-29 16:46:22   34 -- delete from t1 {9}

The first, second, and fourth lines say that the queries returned respectively five, two, and six rows. The third one says that the query returned an error. The fifth row reports that two rows were affected by the UPDATE command. The following lines all report the number of affected rows for INSERT and DELETE statements.
Note on the Examples

The examples provided with this article have been tested with a few different operating systems. The code is still in alpha stage, though, so it may happen that data structures, options, and interfaces change, until the feature set is stabilized.
What's Next

At the end of this long excursus, I feel that I have barely scratched the surface. MySQL Proxy is this, and much more. There are features that I have not touched and that should require appropriate coverage, with some benchmarking. Also, I did not get into much detail with the architecture. Somebody will cover that as well.

Expect more articles about MySQL Proxy, covering load balancing, replication specific features, benchmarks, and especially a MySQL Proxy cookbook, as soon as the community gathers enough recipes to justify the title.

contributed by : Mohammad Shadmani