Freeradius - MySQL Update problem

[musoket at mtn.co.ug]

Hello Ivan,
Mon, 06 Aug 2007 16:45:09 +0100, you wrote:

>>rlm_acct_unique: WARNING: Attribute NAS-Port was not found in request,
>>unique ID MAY be inconsistent

>This is the source of the errors in log entries.

>These are accounting start (VoIP and telephony) and stop requests. They
>will use accounting_start_query and accounting_stop_query. In your
>previous post you showed what you have done to
>accounting_update_query_alt:
>
>accounting_update_query_alt = "INSERT into ${acct_table1} (AcctSessionId,
>AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,
>AcctSt
>artTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start,
>AcctInputOctets,
>AcctOutputOctets, CalledStationId, CallingStationId,
>ServiceType,FramedProtocol, FramedIPAddress, AcctStartDelay)
>values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
>'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
>'%{Cisco-NAS-Port}',
>'%{NAS-Port-Type}', DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} +
>%{Acct-Delay-Time:-0}) SECOND), '%{Acct-Session-Time}',
>'%{Acct-Authentic}','', '%{Acct-Input-Octets}',
>'%{Acct-Output-Octets}',
>'%{Called-Station-Id}', '%{Calling-Station-Id}',
>'%{h323-remote-address}',
>'%{Framed-Protocol}', '%{h323-remote-address}', '0')"

>That query is not getting executed. You need to alter
>accounting_start_query and expand it with those attributes.

>Ivan Kalik
>Kalik Informatika ISP

Here are my accounting_start and accounting_stop queries. The attributes were
already included:


accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId,
AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPor
tType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,
ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets,
 CalledStationId, CallingStationId, AcctTerminateCause, ServiceType,
FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}',
'%{Realm}', '%{NAS-IP-Address}', '%{Cisco-NAS-Port}', '%{NAS-Port-T
ype}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{h323-r
emote-address}', '%{Framed-Protocol}', '%{h323-remote-address}',
'%{Acct-Delay-Time}', '0')"

        accounting_start_query_alt  = "UPDATE ${acct_table1} SET
AcctStartTime = '%S', AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_s
tart = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND
UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}
'"

        accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime =
'%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets =
 '%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}',
AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acc
t-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId =
'%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIP
Address = '%{NAS-IP-Address}'"

accounting_stop_query_alt = "INSERT into ${acct_table2} (AcctSessionId,
AcctUniqueId, UserName, Realm, NASIPAddress,NASPortId, NASPortType,
 AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,
ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, Calle
dStationId, CallingStationId, AcctTerminateCause, ServiceType,
FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Ac
ct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}',
'%{NAS-IP-Address}', '%{Cisco-NAS-Port}', '%{NAS-Port-Type}',
 DATE_SUB('%S', INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0})
SECOND), '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '',
'%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Caus
e}', '%{h323-remote-address}', '%{Framed-Protocol}',
'%{h323-remote-address}', '0', '%{Acct-Delay-Time}')"