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@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