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.
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
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 = "xxxx" password = "xxxx" # 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 }
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
Due to multiple issues using radutmp, on the advice of this list, I am converting to mysql to track users and enable Simultaneous-Use controls. 1) radutmp (code) assumed only 1 user per port (our device allows several users per NAS port). So, (by default) radutmp only showed the LAST user assigned to a specific NAS port, wich required a bit of a workaround/hack. I would like to 'loose the hack' if possible. Does the sql module also assume only 1 user per NAS port? 2) Is there a command similar to radwho that acts upon the sql database? Thanks, -craig __________ Information from ESET Smart Security, version of virus signature database 4470 (20090930) __________ The message was checked by ESET Smart Security. http://www.eset.com
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 = 25600000 WISPr-Bandwidth-Max-Down = 100000000 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 --> 'aaaaa' 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} -> aaaaa Wed Sep 30 17:35:03 2009 : Debug: rlm_sql (sql_lojnet): sql_set_user escaped user --> 'aaaaa' 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 2009 : Debug: expand: %{Acct-Output-Octets} -> 40281 Wed Sep 30 17:35:03 2009 : Debug: expand: INSERT INTO radacct (acctsessionid, acctuniqueid, username, $ Wed Sep 30 17:35:03 2009 : Debug: rlm_sql (sql_lojnet): Released sql socket id: 7 Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: returned from sql_lojnet (rlm_sql) for request 2 Wed Sep 30 17:35:03 2009 : Debug: ++[sql_lojnet] returns ok Wed Sep 30 17:35:03 2009 : Debug: } # server lojnet 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
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
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', '0000-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
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
-----Original Message----- From: freeradius-users-bounces+kamil=extendbroadband.com@lists.freeradius.org [mailto:freeradius-users-bounces+kamil=extendbroadband.com@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
participants (5)
-
Craig Campbell -
Ivan Kalik -
Kamil Semavi -
Marinko Tarlac -
Oguzhan Kayhan