Thread: freeradius postgresql sql query glitch
Hi,
I've observed an SQL logging problem with FreeRADIUS (2.x) and PostgreSQL
(8.1), on several different installations I occasionally get these errors:
Mon Dec 7 13:19:01 2009 : Error: [ourlittle_sql] Couldn't update SQL accounting STOP record - ERROR: invalid input
syntaxfor integer: ""
sql trace log indicates that this is the offending query:
UPDATE radacct
SET AcctStopTime = ('2009-12-07 13:19:01'::timestamp - '6'::interval),
AcctSessionTime = CASE WHEN '' = '' THEN
(EXTRACT(EPOCH FROM ('2009-12-07 13:19:01'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE
- '6'::INTERVAL)))::BIGINT ELSE '' END,
AcctInputOctets = (('0'::bigint << 32) + '0'::bigint),
AcctOutputOctets = (('0'::bigint << 32) + '0'::bigint),
AcctTerminateCause = 'User-Request',
AcctStopDelay = 0,
FramedIPAddress = NULLIF('4.3.2.1', '')::inet,
ConnectInfo_stop = ''
WHERE AcctSessionId = '57fc9e4821466d86'
AND UserName = 'our@user.name'
AND NASIPAddress = '1.2.3.4'
AND AcctStopTime IS NULL;
I'm using the default unchanged sql/postgresql/dialup.conf setting:
accounting_stop_query = "UPDATE ${acct_table2} \
SET AcctStopTime = ('%S'::timestamp - '%{%{Acct-Delay-Time}:-0}'::interval), \
AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
(EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE \
- '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE '%{Acct-Session-Time}' END, \
AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Input-Octets}:-0}'::bigint), \
AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + '%{%{Acct-Output-Octets}:-0}'::bigint), \
AcctTerminateCause = '%{Acct-Terminate-Cause}', \
AcctStopDelay = 0, \
FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
ConnectInfo_stop = '%{Connect-Info}' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress = '%{NAS-IP-Address}' \
AND AcctStopTime IS NULL"
Looks like the code wants to use CASE to check whether %{Acct-Session-Time}
exists among the internal FreeRADIUS variables, while the return value of
the whole SQL CASE construct is supposed to be a bigint.
This is a reduced failing case:
radiustmobile=# select CASE WHEN '' = '' THEN (EXTRACT(EPOCH FROM ('2009-12-07 13:19:01'::TIMESTAMP WITH TIME ZONE -
AcctStartTime::TIMESTAMPWITH TIME ZONE - '6'::INTERVAL)))::BIGINT ELSE '' END from radacct where AcctSessionId =
'57fc9e4821466d86';
ERROR: invalid input syntax for integer: ""
In the else case, this fallback return value comes into PostgreSQL as just
an empty string, which causes it to trip over - it sees that there's a
possibility to write an empty string into a bigint field, which provokes
the syntax error, even if the problem won't actually happen with this
particular setup of input data.
I'm not sure what to do... can the query be rewritten in a manner that would
allow for both use cases?
(Mailing list users, please Cc: responses for those of us who may not be
subscribed. TIA.)
--
2. That which causes joy or happiness.
----- "Josip Rodin" <joy@entuzijast.net> wrote:
> Hi,
>
> I've observed an SQL logging problem with FreeRADIUS (2.x) and
> PostgreSQL
> (8.1), on several different installations I occasionally get these
> errors:
>
> Mon Dec 7 13:19:01 2009 : Error: [ourlittle_sql] Couldn't update SQL
> accounting STOP record - ERROR: invalid input syntax for integer: ""
>
>
> sql trace log indicates that this is the offending query:
>
> UPDATE radacct
> SET AcctStopTime = ('2009-12-07 13:19:01'::timestamp -
> '6'::interval),
> AcctSessionTime = CASE WHEN '' = '' THEN
> (EXTRACT(EPOCH FROM ('2009-12-07 13:19:01'::TIMESTAMP WITH TIME
> ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE
> - '6'::INTERVAL)))::BIGINT ELSE '' END,
> AcctInputOctets = (('0'::bigint << 32) + '0'::bigint),
> AcctOutputOctets = (('0'::bigint << 32) + '0'::bigint),
> AcctTerminateCause = 'User-Request',
> AcctStopDelay = 0,
> FramedIPAddress = NULLIF('4.3.2.1', '')::inet,
> ConnectInfo_stop = ''
> WHERE AcctSessionId = '57fc9e4821466d86'
> AND UserName = 'our@user.name'
> AND NASIPAddress = '1.2.3.4'
> AND AcctStopTime IS NULL;
>
> I'm using the default unchanged sql/postgresql/dialup.conf setting:
>
> accounting_stop_query = "UPDATE ${acct_table2} \
> SET AcctStopTime = ('%S'::timestamp -
> '%{%{Acct-Delay-Time}:-0}'::interval), \
> AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
>
> (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE -
> AcctStartTime::TIMESTAMP WITH TIME ZONE \
> - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE
> '%{Acct-Session-Time}' END, \
> AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) +
> '%{%{Acct-Input-Octets}:-0}'::bigint), \
> AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32)
> + '%{%{Acct-Output-Octets}:-0}'::bigint), \
> AcctTerminateCause = '%{Acct-Terminate-Cause}', \
> AcctStopDelay = 0, \
> FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
> ConnectInfo_stop = '%{Connect-Info}' \
> WHERE AcctSessionId = '%{Acct-Session-Id}' \
> AND UserName = '%{SQL-User-Name}' \
> AND NASIPAddress = '%{NAS-IP-Address}' \
> AND AcctStopTime IS NULL"
>
> Looks like the code wants to use CASE to check whether
> %{Acct-Session-Time}
> exists among the internal FreeRADIUS variables, while the return value
> of
> the whole SQL CASE construct is supposed to be a bigint.
>
> This is a reduced failing case:
>
> radiustmobile=# select CASE WHEN '' = '' THEN (EXTRACT(EPOCH FROM
> ('2009-12-07 13:19:01'::TIMESTAMP WITH TIME ZONE -
> AcctStartTime::TIMESTAMP WITH TIME ZONE - '6'::INTERVAL)))::BIGINT
> ELSE '' END from radacct where AcctSessionId = '57fc9e4821466d86';
> ERROR: invalid input syntax for integer: ""
>
> In the else case, this fallback return value comes into PostgreSQL as
> just
> an empty string, which causes it to trip over - it sees that there's
> a
> possibility to write an empty string into a bigint field, which
> provokes
> the syntax error, even if the problem won't actually happen with this
> particular setup of input data.
>
> I'm not sure what to do... can the query be rewritten in a manner that
> would
> allow for both use cases?
If I understand correctly the below may work. If %{Acct-Session-Time} is an empty string it will return NULL otherwise
itwill return %{Acct-Session-Time}.
ELSE
NULLIF('%{Acct-Session-Time}','') END,
See here details:
http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12697
Adrian Klaver
aklaver@comcast.net
On Mon, Dec 07, 2009 at 10:02:39PM +0000, Adrian Klaver wrote:
> > Mon Dec 7 13:19:01 2009 : Error: [ourlittle_sql] Couldn't update SQL
> > accounting STOP record - ERROR: invalid input syntax for integer: ""
> >
> > accounting_stop_query = "UPDATE ${acct_table2} \
> > SET
> > AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
> > (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE -
> > AcctStartTime::TIMESTAMP WITH TIME ZONE \
> > - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE
> > '%{Acct-Session-Time}' END, \
> >
> > I'm not sure what to do... can the query be rewritten in a manner that
> > would
> > allow for both use cases?
>
> If I understand correctly the below may work. If %{Acct-Session-Time} is
> an empty string it will return NULL otherwise it will return
> %{Acct-Session-Time}.
>
> ELSE
> NULLIF('%{Acct-Session-Time}','') END,
Thanks, that should work, with a slight modification - explicit cast to
'bigint', because a nullif()'ed '' is still a 'text' by default.
The two cases then evaluate like this:
pgsql=# select CASE WHEN '' = '' THEN 1234::BIGINT ELSE NULLIF('', '')::BIGINT END AS value;
value
-------
1234
(1 row)
pgsql=# select CASE WHEN '13' = '' THEN 1234::BIGINT ELSE NULLIF('13', '')::BIGINT END AS value;
value
-------
13
(1 row)
--
2. That which causes joy or happiness.