JustasM
JustasM

Reputation: 1

meaning of 'return proxy.PROXY_IGNORE_RESULT' in connect_server() hook

I am trying to implement connection pooling(many clients are served by few db connections) with mysql-proxy.

I took a look at ro-pooling.lua and it seems that some actions must be done in connect_server() hook.

If I want to create a new connection:

  1. Assign target backend index to proxy.connection.backend_ndx.
  2. Return nothing

If I want to use already existing idle connection:

  1. Assign target backend index to proxy.connection.backend_ndx.
  2. Return proxy.PROXY_IGNORE_RESULT

Now, what bothers me that returning proxy.PROXY_IGNORE_RESULT from connect_server() hook seems to have no impact on connection reuse - every time a client connects, a new connection is created and eventually I run into following error: "User 'username' has exceeded the 'max_user_connections' resource (current value: 4)"

So, the question is: What is the meaning of return proxy.PROXY_IGNORE_RESULT in connect_server() hook?

Also, any reference about how mysql-proxy creates and reuses connections would be very helpful - I did not manage to find any...

Any help would be greatly appreciated :)

EDIT:

this is source of script I'm current using:

local default_backend = 1
local min_idle_connections = 1 
local max_idle_connections = 4
local connections_limit = 1
local user_name = "user"

if not proxy.global.count then -- never mind this, not using it...
    proxy.global.count = 0
end

function connect_server()

    local backend  = proxy.global.backends[1]
    local pool = backend.pool
    local cur_idle = pool.users[""].cur_idle_connections
    print ("CONNECT SERVER:")
    print("current backend:" ..proxy.connection.backend_ndx)
    if cur_idle >= min_idle_connections then
        print("using pooled connection")
        proxy.connection.backend_ndx=0
        print("current backend:" ..proxy.connection.backend_ndx)
        return proxy.PROXY_SEND_RESULT
    end
    proxy.global.count = proxy.global.count + 1
    print("Creating new connection")
    proxy.connection.backend_ndx = default_backend
    print("current backend:" ..proxy.connection.backend_ndx)

end

function read_handshake()
    print("READ_HANDSHAKE")
    print("current backend:" ..proxy.connection.backend_ndx)

end

function read_auth()
    local username = proxy.connection.client.username
    print("READ_AUTH: " ..username)
    print("current backend:" ..proxy.connection.backend_ndx)

end

function disconnect_client()
    print ("DISCONNECT CLIENT. ")
    print("current backend:" ..proxy.connection.backend_ndx)

end

function read_auth_result(auth)
        print("READ_AUTH_RESULT")
        if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
            --auth was fine, disconnect from the server--
            proxy.connection.backend_ndx = 0
            print("disconnected backend after auth")
            print("current backend:" ..proxy.connection.backend_ndx)
        end
end

function read_query(packet)
    print("READ_QUERY:")
    print("current backend:" ..proxy.connection.backend_ndx)

if packet:byte() == proxy.COM_QUIT then
        print("received signal QUIT")
         proxy.response = {
             type = proxy.MYSQLD_PACKET_OK,
         }
        return proxy.PROXY_SEND_RESULT
    end

    if proxy.connection.backend_ndx == 0 then
        print("assigning backend to process query...")
        proxy.connection.backend_ndx = default_backend
        print("current backend:" ..proxy.connection.backend_ndx)

    end

    local username = proxy.connection.client.username
     local cur_idle = proxy.global.backends[default_backend].pool.users[username].cur_idle_connections
     print ("current idle user" ..username.." connections: " ..cur_idle)
    if string.byte(packet) == proxy.COM_QUERY then
         print("Query: " .. string.sub(packet, 2))

    end
    proxy.queries:append(1, packet)
    return proxy.PROXY_SEND_QUERY
end

 function read_query_result( inj )
    print("READ_QUERY_RESULT:")
    print("current backend:" ..proxy.connection.backend_ndx)
     local res = assert(inj.resultset)
        local flags = res.flags

        if inj.id ~= 1 then
             return proxy.PROXY_IGNORE_RESULT
        end
        is_in_transaction = flags.in_trans

        if not is_in_transaction then
            -- release the backend
            print("releasing backend")
            proxy.connection.backend_ndx = 0
        end
    print("current backend:" ..proxy.connection.backend_ndx)
end

Upvotes: 0

Views: 137

Answers (0)

Related Questions