MySQL Bugs: #30718: Connections cannot be recycled when a client dies / query or lua fails

Bug #30718	Connections cannot be recycled when a client dies / query or lua fails
Submitted:	30 Aug 2007 10:28	Modified:	13 Jun 2008 8:18
Reporter:	simon elliston ball	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Proxy	Severity:	S3 (Non-critical)
Version:	0.6.0 r197	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When something bad happens, like a client disappears without sending a disconnect, or when a lua script fails, or when a query fails to find a backend (eg. #30668), the connected_clients field of the backend is not reduced, meaning the pool cannot reuse that connection.

How to repeat:
cause:
(read_query) /usr/local/share/mysql-proxy/rw-splitting.lua:289: attempt to index local 's' (a nil value)
network-mysqld-proxy.c.2815: I have no server backend, closing connection
network-mysqld.c.1161: plugin_call(CON_STATE_READ_QUERY) failed 

kill a client network connection suddenly. 

write dodgy lua in read_query on top of either rw-splitting or tutorial-keepalive. 

Suggested fix:
Some sort of configurable timeout on connections in the pool would help, but be difficult to implement given the nature of the pool reuse in for eg. rw-splitting.lua. Essentially something similar to mysqld's wait_timeout might work for the dead clients problem. However, it would be much preferable to handle decrementing connected_clients directly on error conditions to allow faster recycling of pooled connections.

Thank you for the report.

Seems I can't clearly understand what you tell about: I tried to kill mysql client connected via mysql-proxy and examine SHOW PROCESSLIST output. Server noticed clients disconnected.

Please provide step-by-step instruction how to repeat the problem and what to examine.

In the case of a suddenly killed client, what I'm seeing here is that the disconnect_client hook gets called when killing a client. This however does not decrement connected_clients on the backend in the proxy. This would seem to be fine, but for the lack of a --. 

However, the repeatable case from other bugs where the proxy fails to get a backend and reaches: network-conn-pool.c:75       g_message("%s.%d: no pool-entry for %s found", __FILE__, __LINE__, username->str);
does not allow connections to get recycled. The error results from one backend making a connection, and the read query trying to go to a different backend, which obviously does not have the connection. The connection on the original backend will never be recycled, or reused and is left on the db server in sleep, and as part of the backends connected_clients in the proxy. It will never become part of idle_connections on the proxy, so cannot be reused.

Hope that's a little clearer. I can send vaste amounts of log if it would help. I suspect this may be connected to other bugs which are leaving connected_clients active when they should be returned to the pool.

Thank you for a problem report. Please, try to repeat with a newer version, 0.6.1, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I could not repeat issue with additional details either: number of connected_clients is always correct. So we really need results from testing of last version and repatable test case.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".