freeradius postgresql sql query glitch

[Guy Fraser]

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 = 'our at 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: ""

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