Re: delayed update on sql logging
>> At the first try of sql i see the error
>> rlm_sql (sql_lojnet): Couldn't insert SQL accounting START record -
>> Column
>> 'AcctStopTime' cannot be null
>>
>> Maybe that might be causing this...
>> So how can i fix that???
>
> Something is wrong with your schema. Default is:
>
> acctstoptime datetime NULL default NULL
>
> Are you using schema that doesn't belong to this server version?
>
> Ivan Kalik
> Kalik Informatika ISP
>
Schema might belong to a previous version of freeradius.
But i solved this issue with changing dialup.conf as follows.Now it works...
accounting_start_query = " \
INSERT INTO ${acct_table1} \
(acctsessionid,acctuniqueid, username, \
realm,nasipaddress, nasportid, \
nasporttype, acctstarttime,acctstoptime, \
acctsessiontime, acctauthentic,connectinfo_start, \
connectinfo_stop, acctinputoctets, acctoutputoctets, \
calledstationid, callingstationid, acctterminatecause, \
servicetype, framedprotocol, framedipaddress, \
acctstartdelay, acctstopdelay,xascendsessionsvrkey) \
VALUES \
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
'%{SQL-User-Name}', \
'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
'%{NAS-Port-Type}', '%S', '-00-00 00:00:00', \
'0', '%{Acct-Authentic}', '%{Connect-Info}', \
'', '0', '0', \
'%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
'%{Service-Type}', '%{Framed-Protocol}',
'%{Framed-IP-Address}', \
'%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
two options 1. allow null for AcctStopTime field (Alter table radacct ) 2. change queries in dialup.conf file for accounting (start query) and replace NULL with '0' for AcctStopTime Keep in mind that second option is much harder then the first one because you need to change all accounting queries (where ever you see) WHERE AcctStopTime = NULL in WHERE AcctStopTime = 0 Oguzhan Kayhan wrote: At the first try of sql i see the error rlm_sql (sql_lojnet): Couldn't insert SQL accounting START record - Column 'AcctStopTime' cannot be null Maybe that might be causing this... So how can i fix that??? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
> At the first try of sql i see the error > rlm_sql (sql_lojnet): Couldn't insert SQL accounting START record - Column > 'AcctStopTime' cannot be null > > Maybe that might be causing this... > So how can i fix that??? Something is wrong with your schema. Default is: acctstoptime datetime NULL default NULL Are you using schema that doesn't belong to this server version? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
I am checking simultaneous-use information, logged users etc on sql
server.
The problem is, if a user logges in, nearly after a minute later i can
see
the logged on user on mysql tables. But i can see the same user with
radlast command in the same second the user logs in.
So it seems like there is a delay between updating the data on sql.
And with this, user can log in mutliple times with same username even
i
set sim-use to 1.
Is there any parameter for that???
Or what else can cause that problem.
>>>
>>> Are you using buffered-sql accounting?
>>>
>> How can i check if i use buffered or not?? I didnt change much in
>> default
>> settings..
>
> That would mean you don't.
>
> Do a debug radiusd -Xx of login and see how much time passes between login
> and accounting Start packet and is there a delay in inserting data into
> sql on processing Start packet.
>
> Ivan Kalik
> Kalik Informatika ISP
>
Here is the debug results..
This is the begining.. starting at 17:34:03
rad_recv: Access-Request packet from host 192.168.16.145 port 2078, id=14,
length=288
Vendor-14559-Attr-8 = 0x312e302e3131
Wed Sep 30 17:34:03 2009 : Debug: server lojnet {
Wed Sep 30 17:34:03 2009 : Debug: +- entering group authorize
...
Wed Sep 30 17:34:03 2009 : Debug: } # server lojnet
Sending Access-Accept of id 14 to 192.168.16.145 port 2078
Acct-Interim-Interval = 60
WISPr-Bandwidth-Max-Up = 2560
WISPr-Bandwidth-Max-Down = 1
Wed Sep 30 17:34:03 2009 : Debug: Finished request 0.
Wed Sep 30 17:34:03 2009 : Debug: Going to the next request
Wed Sep 30 17:34:03 2009 : Debug: Waking up in 4.9 seconds.
...Wed Sep 30 17:34:03 2009 : Debug: rlm_sql (sql_lojnet): sql_set_user
escaped user --> 'a'
Wed Sep 30 17:34:03 2009 : Debug: expand: %{Acct-Delay-Time} ->
Wed Sep 30 17:34:03 2009 : Debug: expand:INSERT INTO
radacct (acctsessionid,acctuniqueid, username, $
Wed Sep 30 17:34:03 2009 : Debug: rlm_sql (sql_lojnet): Reserving sql
socket id: 8
Wed Sep 30 17:34:03 2009 : Debug: rlm_sql_mysql: MYSQL check_error: 1048
received
Wed Sep 30 17:34:03 2009 : Error: rlm_sql (sql_lojnet): Couldn't insert
SQL accounting START record - Column 'AcctStopTime' cannot be null
Wed Sep 30 17:34:03 2009 : Debug: expand: %{Acct-Delay-Time} ->
Wed Sep 30 17:34:03 2009 : Debug: expand:UPDATE radacct
SET acctstarttime = '%S', acctstartdel$
Wed Sep 30 17:34:03 2009 : Debug: rlm_sql (sql_lojnet): Released sql
socket id: 8
Wed Sep 30 17:34:03 2009 : Debug: modsingle[accounting]: returned from
sql_lojnet (rlm_sql) for request 1
Wed Sep 30 17:34:03 2009 : Debug: ++[sql_lojnet] returns ok
Then at 17:35:03 i can see the user on sql
Wed Sep 30 17:35:03 2009 : Debug: +- entering group accounting
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: calling detail
(rlm_detail) for request 2
Wed Sep 30 17:35:03 2009 : Debug: expand:
/var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d ->
/var/log/freeradius/radacct/$
Wed Sep 30 17:35:03 2009 : Debug: rlm_detail:
/var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to
/var/log/freeradius/ra$
Wed Sep 30 17:35:03 2009 : Debug: expand: %t -> Wed Sep 30 17:35:03
2009
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: returned from
detail (rlm_detail) for request 2
Wed Sep 30 17:35:03 2009 : Debug: ++[detail] returns ok
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: calling unix
(rlm_unix) for request 2
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: returned from
unix (rlm_unix) for request 2
Wed Sep 30 17:35:03 2009 : Debug: ++[unix] returns noop
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: calling
sql_lojnet (rlm_sql) for request 2
Wed Sep 30 17:35:03 2009 : Debug: expand: %{User-Name} -> a
Wed Sep 30 17:35:03 2009 : Debug: rlm_sql (sql_lojnet): sql_set_user
escaped user --> 'a'
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Input-Gigawords} -> 0
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Input-Octets} ->
671161
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Output-Gigawords} -> 0
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Output-Octets} ->
40281
Wed Sep 30 17:35:03 2009 : Debug: expand:UPDATE radacct
SET framedipaddress = '%{Framed-IP-Address}',$
Wed Sep 30 17:35:03 2009 : Debug: rlm_sql (sql_lojnet): Reserving sql
socket id: 7
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Session-Time} -> 61
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Delay-Time} ->
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Input-Gigawords} -> 0
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Input-Octets} ->
671161
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Output-Gigawords} -> 0
Wed Sep 30 17:35:03 2
Re: delayed update on sql logging
>>> I am checking simultaneous-use information, logged users etc on sql >>> server. >>> The problem is, if a user logges in, nearly after a minute later i can >>> see >>> the logged on user on mysql tables. But i can see the same user with >>> radlast command in the same second the user logs in. >>> So it seems like there is a delay between updating the data on sql. >>> And with this, user can log in mutliple times with same username even i >>> set sim-use to 1. >>> >>> Is there any parameter for that??? >>> Or what else can cause that problem. >> >> Are you using buffered-sql accounting? >> > How can i check if i use buffered or not?? I didnt change much in default > settings.. That would mean you don't. Do a debug radiusd -Xx of login and see how much time passes between login and accounting Start packet and is there a delay in inserting data into sql on processing Start packet. Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: delayed update on sql logging
-Original Message- From: freeradius-users-bounces+kamil=extendbroadband@lists.freeradius.org [mailto:freeradius-users-bounces+kamil=extendbroadband@lists.freeradius. org] On Behalf Of Ivan Kalik Sent: Wednesday, September 30, 2009 1:07 PM To: FreeRadius users mailing list Subject: Re: delayed update on sql logging > I am checking simultaneous-use information, logged users etc on sql > server. > The problem is, if a user logges in, nearly after a minute later i can see > the logged on user on mysql tables. But i can see the same user with > radlast command in the same second the user logs in. > So it seems like there is a delay between updating the data on sql. > And with this, user can log in mutliple times with same username even i > set sim-use to 1. > > Is there any parameter for that??? > Or what else can cause that problem. Are you using buffered-sql accounting? How can we use buffered-sql accounting with 1.1.7 ? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.115/2403 - Release Date: 09/29/09 17:56:00 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
>> I am checking simultaneous-use information, logged users etc on sql
>> server.
>> The problem is, if a user logges in, nearly after a minute later i can
>> see
>> the logged on user on mysql tables. But i can see the same user with
>> radlast command in the same second the user logs in.
>> So it seems like there is a delay between updating the data on sql.
>> And with this, user can log in mutliple times with same username even i
>> set sim-use to 1.
>>
>> Is there any parameter for that???
>> Or what else can cause that problem.
>
> Are you using buffered-sql accounting?
>
> Ivan Kalik
> Kalik Informatika ISP
>
How can i check if i use buffered or not?? I didnt change much in default
settings..
Here is my virtual server configuration on sites-enabled
server wireless {
authorize {
preprocess
files
update control {
Auth-Type := perl
}
perl
}
authenticate {
Auth-Type Perl {
perl
}
}
preacct {
acct_unique
files
}
accounting {
sql_wireless
}
session {
sql_wireless
} ..
And here is the sql.conf file for this sql sttement
sql sql_wireless {
database = "mysql"
driver = "rlm_sql_${database}"
# Connection info:
server = "1.1.1.1"
login = ""
password = ""
# Database table configuration for everything except Oracle
radius_db = "wireless"
acct_table1 = "radacct"
acct_table2 = "radacct"
# Allow for storing data after authentication
postauth_table = "radpostauth"
authcheck_table = "radcheck"
authreply_table = "radreply"
groupcheck_table = "radgroupcheck"
groupreply_table = "radgroupreply"
# Table to keep group info
usergroup_table = "usergroup"
# If set to 'yes' (default) we read the group tables
# If set to 'no' the user MUST have Fall-Through = Yes in the
radreply table
# read_groups = yes
# Remove stale session if checkrad does not see a double login
deletestalesessions = yes
# number of sql connections to make to server
num_sql_socks = 10
# number of seconds to dely retrying on a failed database
# connection (per_socket)
connect_failure_retry_delay = 60
#readclients = yes
# Table to keep radius client info
nas_table = "nas"
# Read driver-specific configuration
$INCLUDE sql/${database}/dialup.conf
}
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
> I am checking simultaneous-use information, logged users etc on sql > server. > The problem is, if a user logges in, nearly after a minute later i can see > the logged on user on mysql tables. But i can see the same user with > radlast command in the same second the user logs in. > So it seems like there is a delay between updating the data on sql. > And with this, user can log in mutliple times with same username even i > set sim-use to 1. > > Is there any parameter for that??? > Or what else can cause that problem. Are you using buffered-sql accounting? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
delayed update on sql logging
Hi, I am checking simultaneous-use information, logged users etc on sql server. The problem is, if a user logges in, nearly after a minute later i can see the logged on user on mysql tables. But i can see the same user with radlast command in the same second the user logs in. So it seems like there is a delay between updating the data on sql. And with this, user can log in mutliple times with same username even i set sim-use to 1. Is there any parameter for that??? Or what else can cause that problem. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
