MySQL Proxy

Robust application frameworks will include the ability to log all database activity. There are cases where you may encounter a situation where access to this functionality is limited, obscure or completely absent. This is especially the case with Pentaho where reports may often fail with no explanation and nothing but a long stack trace. In these cases, it is helpful to implement a way to implement logging on the database side.

MySQL General Log

There is of course the ability to turn on general logging in mysql through the --general-log and --general-log-file options. There are cases where this isn't very helpful. Especially if you are working with a development environment that has multiple developers and applications where the volume of queries from applications other than the one you are working with causes this method to be cumbersome.

mysql-proxy

mysql-proxy is a lua-based framework for intercepting and manipulating communication between a mysql client and server. It is capable of rewriting queries on-the-fly as well as rewriting result sets on the fly. In this case we can use it for auditing the queries from pentaho and its mysql-connection.

Mysql-proxy scripts are written in lua and are passed using --proxy-lua-script=. The server you are proxying to is specified by --proxy-backend-addresses=. The implementation of the log itself is rather simple. This implementation prints to STDOUT.

	-- mysql_proxy.log
	-- mysql-proxy --proxy-lua-script=/home/tdevol/tmp/mysql_proxy_log.lua --proxy-backend-addresses=192.168.1.236:3306
	--
	function read_query(packet)
	if string.byte(packet) == proxy.COM_QUERY then
	print("Intercepted Query : " .. string.sub(packet,2))
	end
	end

view raw mysql_proxy.lua hosted with ❤ by GitHub

The implementation simply involves creating a new JNDI through the Pentaho administration console and setting the host to server running the proxy. (The port for mysql-proxy defaults to 4040). The credentials passed to mysql-proxy are passed on to the backend server. Once the JNDI is setup, implementing the proxy is only a matter of changing the data source for the report being debugged to the new JNDI.