radsqlrelay: can't it just give up sometimes?
Hello, I keep having issues now and then with radsqlrelay. Sometimes we get some accounting records which miss attributes or come with too unexpected values - the resulting SQL queries then sometimes are bogus. My last instance of this generated in radsqlrelay: Incorrect integer value: '' for column 'AcctSessionTime' at row 1 Incorrect integer value: '' for column 'AcctSessionTime' at row 1 Incorrect integer value: '' for column 'AcctSessionTime' at row 1 Incorrect integer value: '' for column 'AcctSessionTime' at row 1 Incorrect integer value: '' for column 'AcctSessionTime' at row 1 radsqlrelay keeps being excited over the query in question *forever* and retries to eternity. The query doesn't get any better over time though - it just makes the whole relaying process come to a grinding halt. Can't radqlrelay be a bit more clever about this, like try three times, and if the query is consistently rejected, save it to a ".reject" file and move on? I can't be the only one who falls into this, am I? Greetings, Stefan Winter -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473 PGP key updated to 4096 Bit RSA - I will encrypt all mails if the recipient's key is known to me http://pgp.mit.edu:11371/pks/lookup?op=get&search=0xC0DE6A358A39DC66
The joys of broken NAS kit. We've used a bit of unlang in our config for accounting for years to skip illegal packets. I think the default config now has similar? alan -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
Hi,
The joys of broken NAS kit. We've used a bit of unlang in our config for accounting for years to skip illegal packets. I think the default config now has similar?
It still doesn't catch everything, and I keep chasing corner cases every once in a while (after losing stats due to queued SQL in radsqlrelay :-( ). Looking at 3.0 mysql:queries.conf I see a Stop record: UPDATE ${....acct_table2} SET acctstoptime = FROM_UNIXTIME(%{integer:Event-Timestamp}), acctsessiontime = '%{Acct-Session-Time}', ... which is the exact thing that broke: in its wisdom, a new NAS did NOT include Acct-Session-Time in its Stop packets, which turned the statement into acctsessiontime = '', which triggered the integer error in my original posting. I don't see AcctSessionTime mangling anywhere in raddb, so don't think this would be caught by unlang. Of course the proper fix would be to then calculate SessionTime = (StopTime-StartTime), but the far more convenient fix for me was acctsessiontime = '%{%{Acct-Session-Time}:-0}', since it's just a worthless eduroam accounting table which we don't make much use of anyway. I would still much appreciate if such kinds of errors wouldn't clog up the query queue. Greetings, Stefan Winter -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473 PGP key updated to 4096 Bit RSA - I will encrypt all mails if the recipient's key is known to me http://pgp.mit.edu:11371/pks/lookup?op=get&search=0xC0DE6A358A39DC66
On Wed, Nov 6, 2013 at 3:44 PM, Stefan Winter <stefan.winter@restena.lu>wrote:
Hi,
The joys of broken NAS kit. We've used a bit of unlang in our config for accounting for years to skip illegal packets. I think the default config now has similar?
It still doesn't catch everything, and I keep chasing corner cases every once in a while (after losing stats due to queued SQL in radsqlrelay :-( ).
Of course the proper fix would be to then calculate SessionTime = (StopTime-StartTime), but the far more convenient fix for me was
acctsessiontime = '%{%{Acct-Session-Time}:-0}',
since it's just a worthless eduroam accounting table which we don't make much use of anyway.
I would still much appreciate if such kinds of errors wouldn't clog up the query queue.
Does Alan's comment on this thread apply to you? http://freeradius.1045715.n5.nabble.com/decoupled-accounting-cron-check-td34... -- Fajar
Hi,
Does Alan's comment on this thread apply to you? http://freeradius.1045715.n5.nabble.com/decoupled-accounting-cron-check-td34...
Well... apply, yes. But letting it continue to break, and then get alerts is worse than fixing what is causing the breakage IMHO. Stefan -- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473 PGP key updated to 4096 Bit RSA - I will encrypt all mails if the recipient's key is known to me http://pgp.mit.edu:11371/pks/lookup?op=get&search=0xC0DE6A358A39DC66
I already said to chuck the broken kit into the bin....THAT'S the fix ;) alan -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
Hi,
which is the exact thing that broke: in its wisdom, a new NAS did NOT include Acct-Session-Time in its Stop packets, which turned the statement into
bin it ;-) if (Acct-Session-Time != 0) { do_stuff_here } else { ok }
I would still much appreciate if such kinds of errors wouldn't clog up the query queue.
we use buffered_sql which seems more resilient to nasty things. at least we could edit its work file and restart the process if required (havent needed to do that for a few years now!) alan
On 11/06/2013 07:32 AM, Stefan Winter wrote:
Can't radqlrelay be a bit more clever about this, like try three times, and if the query is consistently rejected, save it to a ".reject" file and move on?
I can't be the only one who falls into this, am I?
No, we've had similar problems, but not for a while now - I went through query-by-query, column-by-column and made damn sure all our queries would execute at all times. I think the issue with modifying radsqlrelay is that it's hard to know what a general, robust solution to this problem would look like. I guess the simplest solution would be to try a line for N times/M seconds, then give up and write it to a .failed logfile which the admin can monitor However, if you're using the newer radsqlrelay which sends the logs in batches and uses transactions, you have to: 1. Rollback the transaction 2. Seek backwards in the log to your start point 3. Re-process from there 4. Remember the logs that failed and ignore them ...so it gets more complex quite quickly. In the past, one solution I considered was writing "--" into the file at the start point of the failed query, which has the advantage of putting the "skip" state into the file and simplifying the implementation somewhat, but it has bad code smell ;o) As others have pointed out, decoupled accounting has some other ways to handle this; OTOH it can't handle post_auth and AFAIK doesn't batch insertions into transactions, so has some of the performance issues of the old radsqlrelay. In a way, what we really need is something like an ORM that maps radius accounting & post-auth packets to SQL in a more intelligent way than using string interpolation, and that could realise a mapping was going to fail before it tried the SQL query.
Hi,
I think the issue with modifying radsqlrelay is that it's hard to know what a general, robust solution to this problem would look like. I guess the simplest solution would be to try a line for N times/M seconds, then give up and write it to a .failed logfile which the admin can monitor
That sounds good, ...
However, if you're using the newer radsqlrelay which sends the logs in batches and uses transactions, you have to:
1. Rollback the transaction 2. Seek backwards in the log to your start point 3. Re-process from there 4. Remember the logs that failed and ignore them
...so it gets more complex quite quickly.
... but since I am using this, yes, the code complexity makes this a non-trivial approach in the end :-(
In the past, one solution I considered was writing "--" into the file at the start point of the failed query, which has the advantage of putting the "skip" state into the file and simplifying the implementation somewhat, but it has bad code smell ;o)
As others have pointed out, decoupled accounting has some other ways to handle this; OTOH it can't handle post_auth and AFAIK doesn't batch insertions into transactions, so has some of the performance issues of the old radsqlrelay.
The batching performance hit is not an issue for me, but our queries include post-auth ones. :-( Of course I could limit the radsqlrelay to just those, and do the rest with buffered-sql. I'll see about that. Thanks for the suggestions! Greetings, Stefan
In a way, what we really need is something like an ORM that maps radius accounting & post-auth packets to SQL in a more intelligent way than using string interpolation, and that could realise a mapping was going to fail before it tried the SQL query. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Stefan WINTER Ingenieur de Recherche Fondation RESTENA - Réseau Téléinformatique de l'Education Nationale et de la Recherche 6, rue Richard Coudenhove-Kalergi L-1359 Luxembourg Tel: +352 424409 1 Fax: +352 422473 PGP key updated to 4096 Bit RSA - I will encrypt all mails if the recipient's key is known to me http://pgp.mit.edu:11371/pks/lookup?op=get&search=0xC0DE6A358A39DC66
Stefan Winter wrote:
I keep having issues now and then with radsqlrelay. Sometimes we get some accounting records which miss attributes or come with too unexpected values - the resulting SQL queries then sometimes are bogus. My last instance of this generated in radsqlrelay:
Incorrect integer value: '' for column 'AcctSessionTime' at row 1
radsqlrelay keeps being excited over the query in question *forever* and retries to eternity.
The solution is to fix radsqlrelay. It should treat fail differently from "sql down". The failed queries can get written to a different log file.
Can't radqlrelay be a bit more clever about this, like try three times, and if the query is consistently rejected, save it to a ".reject" file and move on?
<cough> Patch? It shouldn't be hard. The main difficulty is figuring out the return code from $dbinfo->{handle}->do($query). It's possible, I just haven't looked. Alan DeKok.
On 06/11/13 16:57, Alan DeKok wrote:
It shouldn't be hard. The main difficulty is figuring out the return code from $dbinfo->{handle}->do($query). It's possible, I just haven't looked.
I wonder if the Perl DBI/DBD drivers are consistent in this respect; certainly in other languages it can be a real pig to distinguish between server-side "query fail" and "other" (I'm looking at you, earlier versions of libpq!) But yes, this kind of approach is probably best.
participants (6)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan Buxey
-
Alan DeKok -
Fajar A. Nugraha -
Phil Mayers -
Stefan Winter