Hiya
have been getting a few odd errors since we started directing Cisco
accounting traffic to freeradius and using postgres.
I am seeing error message:
Tue Jun 8 16:51:46 2004 : Error: rlm_sql (sql): failed after re-connect
Tue Jun 8 16:51:46 2004 : Error: rlm_sql (sql): Couldn't insert SQL
accounting ALIVE record - ERROR: Bad int8 external representation ""
In the radius log.
Some digging through -X output and I have a copy of the sql causing
this: (Customer sensitive data 'x'ed out)
INSERT into radacct (AcctSessionId, AcctUniqueId,UserName, Realm,
NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctSessionTime,
AcctAuthentic, AcctInputOctets, AcctOutputOctets, CalledStationId,
CallingStationId, ServiceType, FramedProtocol, FramedIPAddress)
values('0008310F', '0d37c9e2496fd02d','usernamehere',
'','xxx.xxx.xxx.xxx','20016', 'ISDN',(now() - '4'::interval-
'0'::interval),'0', 'RADIUS',(('0'::bigint << 32) +'0'::bigint),
(('0'::bigint << 32)+ '0'::bigint),'xxxxxx',
'xxxxxxxxxxx','Framed-User','PPP',
NULLIF('xxx.xxx.xxx.xxx','')::inet);
This produces the error. Now from what I can tell from the postgres
config in freeradius, this is the 'accounting_update_query_alt' query.
And I think that the problem lies with trying to insert a null into
AcctSessionTime of the format ''.
The actual string from the config is:
"INSERT into ${acct_table1} \
(AcctSessionId, AcctUniqueId, UserName, Realm,
NASIPAddress, NASPortId, NASPortType, AcctStartTime, \
AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets,
CalledStationId, CallingStationId, \ ServiceType,
FramedProtocol, FramedIPAddress) \
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
'%{NAS-Port}', '%{NAS-Port-Type}', (now() -
'%{Acct-Delay-Time:-0}'::interval -
'%{Acct-Session-Time:-0}'::interval), \
'%{Acct-Session-Time}', '%{Acct-Authentic}', \
(('%{Acct-Input-Gigawords:-0}'::bigint << 32) +
'%{Acct-Input-Octets:-0}'::bigint), \
(('%{Acct-Output-Gigawords:-0}'::bigint << 32) +
'%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
'%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
NULLIF('%{Framed-IP-Address}', '')::inet)"
I replaced the '%{Acct-Session-Time}' variable with
'%{Acct-Session-Time:-0}'
which (fingers crossed) should have fixed this issue, it certainly has
stopped the errors. Perhaps worth including in the distribution?
--
-----
Graeme Hinchliffe (BSc)
Core Internet Systems Designer
Zen Internet (http://www.zen.co.uk/)
ICQ 3842605 (link)
Direct: 0845 058 9074
Main : 0845 058 9000
Fax : 0845 058 9005
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
