MySQL Proxy: merging resultsets

From time to time we get the question how to split a query into a several smaller queries and unifying the result-set before we send it back to the client.

As the client only expects to get one result-set, we have to merge the result-sets from the server into one, like this:

Merging resultsets

First we need a storage for the result-set we want to build:

res = { }

Each connection gets its own one. We declare it outside of the functions as we want to share it between the result-sets of the same connection.

As an example let me just duplicate a query and send it to the server twice:

function read_query(packet)
    if packet:byte() ~= proxy.COM_QUERY then return end

    local q = packet:sub(2)

    res = { }

    if q:sub(1, 6):upper() == "SELECT" then
            proxy.queries:append(1, packet)
            proxy.queries:append(2, packet)

            return proxy.PROXY_SEND_QUERY
    end
end

If it isn't a SELECT query we just pass it on and don't care about it. We use 2 IDs to figure out of this is the first or the second round.

The interesting part is the result-set handler. It has to copy the resultset into our global storage and build a new result-set from it:

function read_query_result(inj)
    -- append the rows to the result-set storage
    for row in inj.resultset.rows do
            res[#res + 1] = row
    end

    -- if it is not the second query, don't send anything back
    if inj.id ~= 2 then
            return proxy.PROXY_IGNORE_RESULT
    end

    -- build new resultset
    proxy.response = {
            type = proxy.MYSQLD_PACKET_OK,
            resultset = {
                    rows = res
            }
    }

    -- merge the field-definition
    local fields = {}
    for n = 1, #inj.resultset.fields do
            fields[#fields + 1] = {
                    type = inj.resultset.fields[n].type,
                    name = inj.resultset.fields[n].name,
            }
    end

    proxy.response.resultset.fields = fields

    -- send the merged resultset
    return proxy.PROXY_SEND_RESULT
end

It is pretty much straight forward. We may simplify this a bit more the more we get into async queries. But the idea stays the same.

Comments