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? (Mailing list users, please Cc: responses for those of us who may not be subscribed. TIA.) -- 2. That which causes joy or happiness.
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
On Mon, Dec 07, 2009 at 01:16:02PM -0700, Guy Fraser wrote:
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 have been using that query for years and have never had a problem.
The exact failing query is right there pasted from the log - you can reproduce it yourself if you have PostgreSQL 8.1, either by supplying it fake data or by trivially modifying the query.
"select CASE WHEN '' = '' THEN" does not care what is in the empty strings because they are not designated as integers.
Your problem is elsewhere.
Yes, it's not there, it's in the else case, like I said.
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.
It's fine, that subexpression returns a valid number. The problem is that the PostgreSQL server rejects the else case. Please read the rest of my previous message again :)
Why is your stop record broken? Stop records are supposed to have a valid Acct-Session-Time, your would appear to be missing.
I don't know why that particular record didn't have Acct-Session-Time, but the default query format already compensates for that, that's why the whole CASE is there. If the stop record doesn't carry Acct-Session-Time, it calculates it from the current timestamp (that's what %S expands into), minus the start time, minus the delay time. And that code is correct, because the session time attribute is not mandatory in accounting packets, per RFC 2866 - section 5.13 lists it as "0-1", meaning "Zero or one instance of this attribute MAY be present." -- 2. That which causes joy or happiness.
participants (2)
-
Guy Fraser -
Josip Rodin