On 2009-Dec-07, at 06:00, Josip Rodin 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 = '[email protected]'
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: ""
I have been using that query for years and have never had a problem.
"select CASE WHEN '' = '' THEN" does not care what is in the empty
strings because they are not designated as integers.
Your problem is elsewhere.
Have you checked to see if AcctStartTime has valid data?
Try :
select AcctStartTime from radacct where AcctSessionId =
'57fc9e4821466d86';
If it contains data that looks like an integer then try :
\d raddact
Make sure AcctStartTime is a time stamp.
Why is your stop record broken? Stop records are supposed to have a
valid Acct-Session-Time, your would appear to be missing.
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.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
--
Guy Fraser
Network Administrator
The Internet Centre
1-888-450-6787
(780)450-6787
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
