Freeradius 3.0.12 problems - redis and mysql pool connections

[Michal Tomaszewski]

Hi Alan,
Thank you for a quick response.

>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,

Not exactly.
It doesn't happen in case of MySQL.
And please note - the same situation appears when you stop and start haproxy service. Connections are closed for sure as haproxy is stopped, so such connection does not exist any more.
What more - there is no other way than haproxy to point freeradius to master redis-server while redis does not have another mechanism to check which server is a master and redirect requests. The haproxy use is the preferred one. It sends PING/PONG to check if redis server is alive. And in freeradius we have no other tools to choose master redis-server. Writes to slave are not replicated to master and thus are not persistent...
https://discuss.pivotal.io/hc/en-us/articles/205309388-How-to-setup-HAProxy-and-Redis-Sentinel-for-automatic-failover-between-Redis-Master-and-Slave-servers
http://blog.haproxy.com/2014/01/02/haproxy-advanced-redis-health-check/

Freeradius does not check the state if the connection is alive. Maybe redis-tools (or redis library) is broken and does not give such possibility but you always can verify whether connection is alive using PING/PONG mechanism.

>> 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.
>Soi t is aparently not working this way...
>
>  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.
So changing idl_timeout to 60 secs makes it working much better. But in for example MySQL connection state is checked much better.

>> 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.

Maybe periodic PING/PONG on connection?
> 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?
I've changed it and it helps. But there is still 60-second delay to have redis-server responding.

>> #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.

Yes. I saw this. Thank you. Tomorrow we'll compile the newest branch but it still does not resolve Error...

>> 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.
Yes. But connection has expired and probably something in connection's pool management can be improved.

>> 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.

Server is not down. Server is working perfectly all the time. Those messages are not because MySQL server is down. They are appearing in normal operation when server is 100% available. They are probably because idle_timeout closes the connection and freeradius does not record the fact that such connection does not exist.

>  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.
Idle_timeout is in default value of 60 seconds.
If I change it to e.g. 2 secs I have much more ERROR messages.

>  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?

SQL is working all the time without any interruption. It is not this case.

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

In my opinion connection is closed because of idle_timeout but freeradius still tries to use closed connection...

________________________________________ Uwaga: Treść niniejszej wiadomości może być poufna i objęta zakazem jej ujawniania. Jeśli czytelnik tej wiadomości nie jest jej zamierzonym adresatem, pracownikiem lub pośrednikiem upoważnionym do jej przekazania adresatowi, informujemy że wszelkie rozprowadzanie, rozpowszechnianie lub powielanie niniejszej wiadomości jest zabronione. Jeśli otrzymałeś tę wiadomość omyłkowo, proszę bezzwłocznie odesłać ją nadawcy, a samą wiadomość usunąć z komputera. Dziękujemy. ________________________________ Note: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.If you have received this communication in error, please notify the sender immediately by replying to the message and deleting it from your computer. Thank you. ________________________________