Catching errors in SQL xlat queries

[Pshem Kowalczyk]

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 at deployingradius.com> wrote:

> On May 26, 2016, at 5:59 PM, Pshem Kowalczyk <pshem.k at 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