Freeradius 3.0.12 problems - redis and mysql pool connections

[Alan DeKok]

On Dec 22, 2016, at 10:30 AM, Michal Tomaszewski <Michal.Tomaszewski at cca.pl> wrote:
> Our several freeradius servers are using MySQL cluster (percona) and redis master/slave mapped to local port using HA Proxy on each freeradius server.
> So in fact each Freeradius sees 1 local port for MySQL and 1 local port for redis.

  That's likely a source of the problem.  If the HA proxy isn't closing bad connections, FreeRADIUS has no idea that the connection is bad,

> When redis becomes unavailable for a while (restart of redis-server or simple restart of HA_Proxy) Freeradius does not check that connection is no longer available and keeps those invalid connections till lifetime time. In fact in such situation old connection should be discarded and new connection shall be opened.

  How does FreeRADIUS know that the connection is no longer available?  It doesn't periodically poll the idle connections.  It just uses the connections when needed.

> Standard lifetime in mods-available/redis is set to 86400 so redis module is not able to read/write anything from redis-server for 24 hours.

  That doesn't make any sense.  If FreeRADIUS tries to use a connection and the connection is bad, it closes the connection, and tries to open a new one.

  In addition, you should check the "idle_timeout" setting.  If a connection has been idle for (say) 10 minutes, it may have timed out.  Setting idle_timeout will ensure that the server doesn't even try old / idle connections, but instead closes them and opens a new one.

> In /var/log/freeradius/radius.log there are only ERRORs when there is redis write command, there is no error information when there is (unsuccessful) read command. To see that debug mode shall be started.
> So in fact redis module is not verifying redis valid/invalid connection state.

  FreeRADIUS just calls the redis library to access redis.  If the library doesn't return errors on read, then there's little that FreeRADIUS can do.

> Changing lifetime to e.g. 60 helps to force redis module start working (in 60 second from redis-server restart) but that is just a workaround not a solution.

  Why not change idle_timeout? 

> #2
> In var/log/freeradius/radius.log we have lots of errors:
> ERROR: SQL query failed: no connection
> 
> It seems that error shows up when connection is closed by e.g. idle-timeout...
...
> Thu Dec 22 15:59:45 2016 : Info: Need 0 more connections to reach min connections (5)
> Thu Dec 22 15:59:47 2016 : Info: Need 0 more connections to reach min connections (5)

  Those messages have been removed in the v3.0.x branch.

> Thu Dec 22 15:59:47 2016 : Info: Need 0 more connections to reach min connections (5)
> Thu Dec 22 15:59:48 2016 : Info: Need 0 more connections to reach min connections (5)
> Thu Dec 22 15:59:48 2016 : ERROR: (164)         ERROR: SQL query failed: no connection
> Thu Dec 22 15:59:48 2016 : Info: Need 0 more connections to reach min connections (5)
> Thu Dec 22 15:59:51 2016 : Info: Need 0 more connections to reach min connections (5)
> Thu Dec 22 15:59:54 2016 : Info: Need 0 more connections to reach min connections (5)
> Thu Dec 22 15:59:54 2016 : Info: Need 0 more connections to reach min connections (5)
> Thu Dec 22 15:59:54 2016 : ERROR: (172)       ERROR: SQL query failed: no connection

  I don't see that ERROR as much of a problem. The server tried to use an old connection, it failed, and when the server tried another connection, that one failed, too.

> We tested to set freeradius use mysql directly, without haproxy and using default values in mods-availables/sql (including pool { start value) but it did not change this strange behavior.
> 
> Any help would be appreciated...

  FreeRADIUS doesn't implement SQL, TCP, or any other networking code.  It's at the mercy of the underlying libraries.  If you take the SQL server down and the TCP connection remains up... blame the OS.  Don't blame FreeRADIUS.  It has no idea what's on the other end of the connection.

  Then set idle_timeout to a smaller value.  That way idle connections will get closed more quickly, even if you have "lifetime" set to one day.

  Perhaps I could turn this problem around.  What do you want FreeRADIUS to *do* when the SQL server goes down?  How does FreeRADIUS know that SQL is down?  What happens when SQL goes down?

  These aren't simple questions to answer.  FreeRADIUS does it's best with the information it has.

  And in the end, if your SQL server goes down... of course FreeRADIUS will produce errors.  There is simple no way for it to work around the fact that the SQL server is down.

  Alan DeKok.