mysql-proxy-discuss team mailing list archive

[Jan Kneschke <jan@xxxxxxxxxxx>]

Hi,

Gathering all the input around the connection pool I see the huge
limitations with the way we currently expose the internals to the
scripting layer. It is based around assumptions that aren't obvious and
always known and on the long run it is time to clean that up.

Examples:
* read_query() gets a "packet" parameter
  - it is passed in as string even if is is a huge SQL statement (INSERT
    ... max_allowed_packets).
  - doesn't handle LOAD DATA INFILE LOCAL
  - is passed in even if it isn't needed by the script
* read_query_result() doesn't handle multi-resultset
  -> inj.resultset
* proxy.resultset.* is not obvious what it accepts

When it comes to connections pools there is the magic:
* proxy.connection.backend_ndx = 0

The current way we handle packets from the server will make it hard to
handle multiple backends in one client connections at the same time.

Based on all these little things I spent some time to rethink all the
little pieces and put it on a new foundation:

---
-- a object-oriented proxy
--
-- the script is loaded after the client connected to us
--
-- * we see everything from the side of the client

local con = proxy.connection.new()

function con:connect_server()
  ...
end

We work with objects. No more global "proxy.connection.client.*", it is
just self.* now (and similar). Less to write and more readable, as it is
explicit.

A connection as client and server side and we can read() and write()
from them.

-- * each client connection has a local connection pool
-- * all backend-side methods get a 'name' as parameter
--   * the name of the connection in the local pool

This the major driver for the other API changes below. A global
connection pool for the backends that are not related to a connection
(as now) and a local connection pool for connections only this
connection operates with. Backend connections can be moved between the
two pools.

There is no proxy.connection.server.* any more. That was basicly a
minimal connection pool with only one possible "pooled" connection.
Instead it is now:

   local pcon = self.pool:get(name)

And that connection has all the parameters we are used to from
proxy.connection.server.*

-- * read_query() doesn't get a 'packet' parameter anymore
--   * instead self:read() returns a table with all the packets in the
--     read_queue / a iterator over the queue
-- * read_query_result() doesn't get a 'inj' paramter anymore
--   * instead it gets 'name' for the pooled connection
--   * from the pooled connection you get read() the old injection
--     packet
-- * proxy.response.* is gone
--   * instead there is self.write() to write data to the client
--   * it gets a table of packets
--   * the resultset or error-packet can be created with the proto.*
--     functions

The proxy.response.* looked like a nice idea but is actually tricky when
it comes to error-handling. It also limits to what is possible. Instead
we have all the encoding/decoding functions in mysql.proto already.
Let's just use them.

-- * proxy.queries:append() is gone
--   * instead use pcon:write() with the same API
--   * the 2nd param is a table with packets instead of just one packet

In the attached example I try to explore that new API style with a few
basic ideas:

* we have 2 backend connection: one to the "master", one to the "slave"
* we open connections if there isn't one for both of them in the pool
* in read_auth_response() we create auth packet for the "slave"
connection based on a cleartext password from a external storage
* we send the same query to 2 backends at the same time in read_query()

All the code is a complete mock. There is no code written to handle this
yet. It is just a idea. It is solving most of the problems around
connection pooling and how to access it and feels a lot cleaner to me.

* .read() / .write() instead of proxy.response.* and
proxy.queries:append() or packet parameters
* everything is decoded and encoded by the proto functions
* no magic proxy.connection.backend_ndx, just explicit pool access

My idea is to build all this on a fork of the current proxy-plugin and
try it out for a while.

cheers,
  Jan
-- 
 jan: "Gee, Brain^WEric, what'd you wanna do tonight?"
eric: Same thing we do everynight: Take over the HelloWorld!

---
-- a object-oriented proxy
-- 
-- the script is loaded after the client connected to us
-- 
-- * we see everything from the side of the client
-- * each client connection has a local connection pool
-- * all backend-side methods get a 'name' as parameter
--   * the name of the connection in the local pool
-- * read_query() doesn't get a 'packet' parameter anymore
--   * instead self:read() returns a table with all the packets in the read_queue / a iterator over the queue
-- * read_query_result() doesn't get a 'inj' paramter anymore
--   * instead it gets 'name' for the pooled connection
--   * from the pooled connection you get read() the old injection packet
-- * proxy.response.* is gone
--   * instead there is self.write() to write data to the client
--   * it gets a table of packets
--   * the resultset or error-packet can be created with the proto.* functions
-- * proxy.queries:append() is gone
--   * instead use pcon:write()
--   * the 2nd param is a table with packets
--

local con = proxy.connection.new()

function con:connect_server()
	local pcon

	pcon = proxy.global.backends[1].pool:steal(1) -- steal a connection from the global pool, if available
	if not pcon then
		pcon = self.pool:add("master")
		print(("%s.%s"):format(pcon.name, pcon.state))
		pcon:connect_to(proxy.global.backends[1])
	end
	
	pcon = proxy.global.backends[2].pool:steal(1) -- steal a connection from the global pool, if available
	if not pcon then
		pcon = self.pool:add("slave")
		print(("%s.%s"):format(pcon.name, pcon.state))
		pcon:connect_to(proxy.global.backends[2])
	end

	-- after connect_server we try to connect all
	-- the connections added to the local pool
end

---
-- read the handshake from a backend
--
-- by default we return the result to the client
-- as we have more than one backend in the pool we 
-- have to make sure to only send it once
function con:read_auth_challenge(name)
	local pcon = self.pool:get(name)
	print(("%s.%s"):format(pcon.name, pcon.state))

	if name == "slave" then
		return proxy.PROXY_IGNORE_RESULT
	end

	con:write(pcon:read())
end

---
-- read the response from the client (and send it to the backend)
--
function con:read_auth_response()
	local pcon = self.pool:get("master")
	print(("%s.%s"):format(pcon.name, pcon.state))

	pcon:write(1,
		self.read()
	)

	-- setup the slave connection
	local pcon = self.pool:get("slave")
	print(("%s.%s"):format(pcon.name, pcon.state))

	pcon:write(1,
		proto.to_response_packet({
			username = self.client.username,
			database = self.client.default_db,
			response = proto.scramble("theuserspass", pcon.scramble_buffer)
		}),
		{ resultset_is_needed = true }
	)

	-- we send everything to the backend and wait for a response

	return 
end

function con:read_auth_result(name)
	if name == "slave" then
		return proxy.PROXY_IGNORE_RESULT
	end
end

function con:read_query()
	local pcon = self.pool:get("master") 
	print(("%s.%s"):format(pcon.name, pcon.state))
	pcon:write(1, con:read(), { resultset_is_needed = true })
	
	local pcon = self.pool:get("slave") 
	print(("%s.%s"):format(pcon.name, pcon.state))
	pcon:write(1, con:read(), { resultset_is_needed = true })
end

function con:read_query_result(name)
	local pcon = self.pool:get(name) -- get the connection and check what we got back
	print(("%s.%s"):format(pcon.name, pcon.state))
	local resp = pcon:read() -- get the injection packet

	self:write(resp:walk_packets()) -- pass a iterator to the client connection
end

function con:disconnect()
	-- on default the local connection pool is just freed
	-- we can move the connections over to the global pool instead
	--
	local pcon

	pcon = self.pool:steal("master")
	proxy.global.backends[1].pool:donate(pcon)

	pcon = self.pool:steal("slave")
	proxy.global.backends[2].pool:donate(pcon)
end

return con