Problem with MySQL connection through load balancer

[Phil Mayers]

On 23/07/14 17:10, Dean Goldhill wrote:

> I have tried to use the ‘lifetime = 120’ option in the sql.conf
>
> Under normal operation, this will cause radius to close and re-establish
> the SQL connection every 120 seconds, so I hoped that when in this stuck
> state, it would do the same and re-establish the connection. But does not.

The problem is, FreeRADIUS will be blocked inside the mysql libraries. 
So "lifetime" probably won't help.

>
> Is there anything someone can suggest?
>
> I don’t have the ability to sync the session table between the 2 load
> balancers.

The problem isn't FreeRADIUS - it's the mysql libraries. They're not 
detecting the dead connections.

Does the load balancer have the ability to return a TCP RST or ICMP 
error when it receives a packet for an unknown session? If so, this will 
probably trigger the mysql libraries to drop the connection, and 
FreeRADIUS to reconnect.

Otherwise, you'll need to investigate if MySQL has any form of dead 
connection detection, and how you can persuade your mysql libraries to 
do it.

TCP keepalives probably won't help - they're usually too slow for this 
kind of thing.

Alternatively, something like:

http://dev.mysql.com/downloads/mysql-proxy/

...could be run locally. We use pgbouncer on postgres for this - it can 
check the connection is alive before sending a query and reconnect as 
needed.