reconnect to mysql on DB write errors
Is there a way / config option for freeRadius to reconnect to sql server on DB operation errors (eg. db write errors). background: the sql cluster we use is behind a DNS based Load balancer. Every 2 weeks, half of the cluster are taken out of DNS for service by automated tasks. The DNS update happens a few seconds after the servers are taken out (and all DBs marked read-only) and during these few seconds, radius can still connect to the down'd sql servers but all writes will fail. Eventually, the read-only mysql will stop and radius will reconnect to the valid sql servers. The one option I see "connect_failure_retry_delay" seems to only matter for establishing sql connection. In this case, sql connection itself is fine, just to a read-only DB. Any suggestions are welcome. tia.
On Mon, 11 Apr 2022, 07:38 Cypher A, <cyphera@gmail.com> wrote:
Is there a way / config option for freeRadius to reconnect to sql server on DB operation errors (eg. db write errors).
Yes: https://github.com/FreeRADIUS/freeradius-server/blob/v3.0.x/raddb/sites-avai...
thanks, that seems to be exactly what I need. though it's unclear how to trigger current sql connections to drop and reconnect? is it just by setting db_online to fail?? On Mon, Apr 11, 2022 at 5:47 PM Terry Burton <tez@terryburton.co.uk> wrote:
On Mon, 11 Apr 2022, 07:38 Cypher A, <cyphera@gmail.com> wrote:
Is there a way / config option for freeRadius to reconnect to sql server on DB operation errors (eg. db write errors).
Yes: https://github.com/FreeRADIUS/freeradius-server/blob/v3.0.x/raddb/sites-avai...
On Mon, 11 Apr 2022, 11:17 Cypher A, <cyphera@gmail.com> wrote:
thanks, that seems to be exactly what I need. though it's unclear how to trigger current sql connections to drop and reconnect? is it just by setting db_online to fail??
No. That's just a "variable". You can probably use an rlm_sql xlat in the resource_check virtual server to run a command to quit or kick the correct connect. On Mon, Apr 11, 2022 at 5:47 PM Terry Burton <tez@terryburton.co.uk> wrote:
On Mon, 11 Apr 2022, 07:38 Cypher A, <cyphera@gmail.com> wrote:
Is there a way / config option for freeRadius to reconnect to sql server on DB operation errors (eg. db write errors).
Yes: https://github.com/FreeRADIUS/freeradius-server/blob/v3.0.x/raddb/sites-avai...
On Mon, 11 Apr 2022, 11:32 Terry Burton, <tez@terryburton.co.uk> wrote:
On Mon, 11 Apr 2022, 11:17 Cypher A, <cyphera@gmail.com> wrote:
thanks, that seems to be exactly what I need. though it's unclear how to trigger current sql connections to drop and reconnect? is it just by setting db_online to fail??
No. That's just a "variable".
You can probably use an rlm_sql xlat in the resource_check virtual server to run a command to quit or kick the correct connect.
* kick the current session. On Mon, Apr 11, 2022 at 5:47 PM Terry Burton <tez@terryburton.co.uk> wrote:
On Mon, 11 Apr 2022, 07:38 Cypher A, <cyphera@gmail.com> wrote:
Is there a way / config option for freeRadius to reconnect to sql server on DB operation errors (eg. db write errors).
Yes: https://github.com/FreeRADIUS/freeradius-server/blob/v3.0.x/raddb/sites-avai...
I can’t find anything in rlm_sql that could kill the current connection. I also tested to use an exec xlat to run a script to kill and restart the current radiusd process. Really interesting that this works when radiusd is running in debug mode. But not when it’s running under systemd .. What’s the proper way to do this??
On Apr 11, 2022, at 8:35 PM, Terry Burton <tez@terryburton.co.uk> wrote:
On Mon, 11 Apr 2022, 11:32 Terry Burton, <tez@terryburton.co.uk> wrote:
On Mon, 11 Apr 2022, 11:17 Cypher A, <cyphera@gmail.com> wrote: thanks, that seems to be exactly what I need. though it's unclear how to trigger current sql connections to drop and reconnect? is it just by setting db_online to fail??
No. That's just a "variable".
You can probably use an rlm_sql xlat in the resource_check virtual server to run a command to quit or kick the correct connect.
* kick the current session.
On Mon, Apr 11, 2022 at 5:47 PM Terry Burton <tez@terryburton.co.uk> wrote:
On Mon, 11 Apr 2022, 07:38 Cypher A, <cyphera@gmail.com> wrote: Is there a way / config option for freeRadius to reconnect to sql server on DB operation errors (eg. db write errors).
Yes: https://github.com/FreeRADIUS/freeradius-server/blob/v3.0.x/raddb/sites-avai...
On Apr 25, 2022, at 8:33 AM, Cypher A <cyphera@gmail.com> wrote:
I can’t find anything in rlm_sql that could kill the current connection.
Because the connection handling is done by the connection pool code, not by rlm_sql. See src/main/connection.c
I also tested to use an exec xlat to run a script to kill and restart the current radiusd process. Really interesting that this works when radiusd is running in debug mode. But not when it’s running under systemd ..
What’s the proper way to do this??
The proper way is to not make your database read-only while FreeRADIUS is trying to write to it. What's happening here is fairly simple. When the DB goes read-only there is pretty much no way for FreeRADIUS (or the SQL driver) to know that. An error of "cannot insert duplicate row" is similar to "cannot write to the DB". So the database is up, it's just not doing it's job. Since the DB is up, the SQL connections don't fail over. The solution here is really "don't take the DB down while it' being used in production". There's no simple way to tell FreeRADIUS "close this SQL connection", because no one needs that functionality. For most people, if the DB is up, then it's writable. And when the DB is down, the normal SQL failover mechanism works fine. This is very much an unusual situation which is caused by the decision to randomly make the DB read-only, but still have it respond to SQL queries. Another solution which should work is to simply use FW rules to reset all of the old connections to the DB. Ensure that the firewall returns RST, then FreeRADIUS will see that the connections are down, and will fail over. Alan DeKok.
I completely agree with you though the control of the DB is outside my realm. I somehow needs to find a way to detect this and do something so radiusd can recover as quick as possible. I thought resource-check was the solution, maybe still is, just unsure of the last piece.
On Apr 26, 2022, at 2:51 AM, Alan DeKok <aland@deployingradius.com> wrote:
On Apr 25, 2022, at 8:33 AM, Cypher A <cyphera@gmail.com> wrote:
I can’t find anything in rlm_sql that could kill the current connection.
Because the connection handling is done by the connection pool code, not by rlm_sql. See src/main/connection.c
I also tested to use an exec xlat to run a script to kill and restart the current radiusd process. Really interesting that this works when radiusd is running in debug mode. But not when it’s running under systemd ..
What’s the proper way to do this??
The proper way is to not make your database read-only while FreeRADIUS is trying to write to it.
What's happening here is fairly simple. When the DB goes read-only there is pretty much no way for FreeRADIUS (or the SQL driver) to know that. An error of "cannot insert duplicate row" is similar to "cannot write to the DB".
So the database is up, it's just not doing it's job. Since the DB is up, the SQL connections don't fail over.
The solution here is really "don't take the DB down while it' being used in production". There's no simple way to tell FreeRADIUS "close this SQL connection", because no one needs that functionality. For most people, if the DB is up, then it's writable. And when the DB is down, the normal SQL failover mechanism works fine.
This is very much an unusual situation which is caused by the decision to randomly make the DB read-only, but still have it respond to SQL queries.
Another solution which should work is to simply use FW rules to reset all of the old connections to the DB. Ensure that the firewall returns RST, then FreeRADIUS will see that the connections are down, and will fail over.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Apr 25, 2022, at 8:55 PM, Cypher A <cyphera@gmail.com> wrote:
I completely agree with you though the control of the DB is outside my realm. I somehow needs to find a way to detect this
To detect *what*? That's the question. Yes, it's "detect when stuff goes wrong". But WHAT is going wrong? What error message is being sent to FreeRADIUS? i.e. using the information available to FreeRADIUS, how can it tell (a) what's wrong, and (b) do something about it? Until you get this information, it will be difficult to automatically solve the problem. And maybe tell the DB people to stop doing stupid things, because it's breaking the RADIUS server. I've never heard of DBs being made randomly read-only while they're being used in productions. It's a sign of incompetence, TBH.
and do something so radiusd can recover as quick as possible. I thought resource-check was the solution, maybe still is, just unsure of the last piece.
You're trying to solve the problem by looking at what *you* know about it. That isn't enough. You have to look at the network, and see what information is available there. What bit goes by in the network which lets FreeRADIUS see that something is wrong? Alan DeKok.
participants (3)
-
Alan DeKok -
Cypher A -
Terry Burton