Catching errors in SQL xlat queries
Hi, We have a setup where a main frontend cluster (3.0.11) proxies requests to various remote radius servers. We validate and adjust responses from those serves to match the requirements of a particular BNGs the session is on (this is for a DSL/fibre setup). For example if the remote radius system wants the speed of the connection limited to a given number they reply with our own 'SV-Speed-Down' attribute and a value. We do some SQL lookups: update { &control:Tmp-String-0 := "%{frontend_sql:SELECT prefix || '#' || suffix FROM attrib_map, nas_type \ WHERE nas_type.nasid='%{request:NAS-Identifier}' AND \ attrib_map.nas_type = nas_type.type AND \ attrib_map.attribute = 'SV-Speed-Down'}" } in order to figure out the actual syntax required for the particular BNG. Then we use a regex to parse out the details: if (&control:Tmp-String-0 =~ /([a-z-]+)#[a-0-9]+)/) { Recently we had some issues with the DB running out of steam (and handles). We've noticed that this didn't stop the queries from running and returning empty results (and not matching the regex). With the regular rlm_sql queries the module returns 'fail' if there was a problem with the database or 'notfound' if there was nothing to return. Is there a way to detect the 'fail' state with the xlat queries? kind regards Pshem
Use a redundant sql config with fail detect condition to fail through to another server alan
On May 26, 2016, at 5:59 PM, Pshem Kowalczyk <pshem.k@gmail.com> wrote:
We have a setup where a main frontend cluster (3.0.11) proxies requests to various remote radius servers. We validate and adjust responses from those serves to match the requirements of a particular BNGs the session is on (this is for a DSL/fibre setup). For example if the remote radius system wants the speed of the connection limited to a given number they reply with our own 'SV-Speed-Down' attribute and a value. We do some SQL lookups:
OK...
update { &control:Tmp-String-0 := "%{frontend_sql:SELECT prefix || '#' || suffix FROM attrib_map, nas_type \ WHERE nas_type.nasid='%{request:NAS-Identifier}' AND \ attrib_map.nas_type = nas_type.type AND \ attrib_map.attribute = 'SV-Speed-Down'}" }
in order to figure out the actual syntax required for the particular BNG. Then we use a regex to parse out the details:
if (&control:Tmp-String-0 =~ /([a-z-]+)#[a-0-9]+)/) {
Which is fine.
Recently we had some issues with the DB running out of steam (and handles). We've noticed that this didn't stop the queries from running and returning empty results (and not matching the regex).
If the DB fails, the string expansion is empty. You can do fail-over in string expansions. See raddb/radiusd.conf, the "instantiate" section.
With the regular rlm_sql queries the module returns 'fail' if there was a problem with the database or 'notfound' if there was nothing to return. Is there a way to detect the 'fail' state with the xlat queries?
The simplest thing is to check for an empty string: update { &control:Tmp-String-0 := "%{front... } if ((&control:Tmp-String-0 != "") && (&control:Tmp-String-0 =~ /([a-z-]+)#[a-0-9]+)/)) { ... Alan DeKok.
Hi, Ok, this definitely helps, but I'll go back to my original question - is there a way to tell if that empty string is a result of the query returning nothing or a DB failure? We don't run redundant DBs - each instance of the frontend has it's own copy of the DB (so we can stamp out more frontends as we need them in no time), but perhaps we should consider allowing them to query 'other' DBs as well. kind regards Pshem On Fri, 27 May 2016 at 10:55 Alan DeKok <aland@deployingradius.com> wrote:
On May 26, 2016, at 5:59 PM, Pshem Kowalczyk <pshem.k@gmail.com> wrote:
We have a setup where a main frontend cluster (3.0.11) proxies requests to various remote radius servers. We validate and adjust responses from those serves to match the requirements of a particular BNGs the session is on (this is for a DSL/fibre setup). For example if the remote radius system wants the speed of the connection limited to a given number they reply with our own 'SV-Speed-Down' attribute and a value. We do some SQL lookups:
OK...
update { &control:Tmp-String-0 := "%{frontend_sql:SELECT prefix || '#' || suffix FROM attrib_map, nas_type \ WHERE nas_type.nasid='%{request:NAS-Identifier}' AND \ attrib_map.nas_type = nas_type.type AND \ attrib_map.attribute = 'SV-Speed-Down'}" }
in order to figure out the actual syntax required for the particular BNG. Then we use a regex to parse out the details:
if (&control:Tmp-String-0 =~ /([a-z-]+)#[a-0-9]+)/) {
Which is fine.
Recently we had some issues with the DB running out of steam (and handles). We've noticed that this didn't stop the queries from running and returning empty results (and not matching the regex).
If the DB fails, the string expansion is empty. You can do fail-over in string expansions. See raddb/radiusd.conf, the "instantiate" section.
With the regular rlm_sql queries the module returns 'fail' if there was a problem with the database or 'notfound' if there was nothing to return. Is there a way to detect the 'fail' state with the xlat queries?
The simplest thing is to check for an empty string:
update { &control:Tmp-String-0 := "%{front... }
if ((&control:Tmp-String-0 != "") && (&control:Tmp-String-0 =~ /([a-z-]+)#[a-0-9]+)/)) { ...
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 26 May 2016, at 19:28, Pshem Kowalczyk <pshem.k@gmail.com> wrote:
Hi,
Ok, this definitely helps, but I'll go back to my original question - is there a way to tell if that empty string is a result of the query returning nothing or a DB failure?
Not currently, internally a lot of the xlat functions do return < 0 to signify a failure, but with v3.0.x this doesn't get translated into something useful. What could we usefully do with a failing string expansion? I guess if it's in an update block and we make updates atomic, then the whole update block could fail. If it's a module configuration item the module could fail... Conditions are weird though. -Arran
On Thu, May 26, 2016 at 09:13:51PM -0400, Arran Cudbard-Bell wrote:
What could we usefully do with a failing string expansion? I guess if it's in an update block and we make updates atomic, then the whole update block could fail.
If it's a module configuration item the module could fail...
Conditions are weird though.
Seems like a nasty hack, but create "xlat-Failure := <something>"? I don't like it, but it's one way... Matthew -- Matthew Newton, Ph.D. <mcn4@le.ac.uk> Systems Specialist, Infrastructure Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom For IT help contact helpdesk extn. 2253, <ithelp@le.ac.uk>
On 26 May 2016, at 22:14, Matthew Newton <mcn4@leicester.ac.uk> wrote:
On Thu, May 26, 2016 at 09:13:51PM -0400, Arran Cudbard-Bell wrote:
What could we usefully do with a failing string expansion? I guess if it's in an update block and we make updates atomic, then the whole update block could fail.
If it's a module configuration item the module could fail...
Conditions are weird though.
Seems like a nasty hack, but create "xlat-Failure := <something>"?
I don't like it, but it's one way...
Or have conditions return fail. That could be caught and then used to do something else. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
participants (5)
-
Alan Buxey -
Alan DeKok -
Arran Cudbard-Bell -
Matthew Newton -
Pshem Kowalczyk