Re: Duplicate requests in a session
Hi guys, As far as I can see from 1.1.7, this was never rolled into the code. Can I suggest simply adding an index like this by default: ALTER TABLE radacct ADD constraint radacct_unique_session UNIQUE ( acctuniqueid); Then the composition of acctuniqueid can still be set in the unique id module as appropriate for the site in question? Alex On 31/08/06, Peter Nixon [EMAIL PROTECTED] wrote: Good question. Does anyone have anything against changing this? -Peter On Thu 31 Aug 2006 10:11, Santiago Balaguer García wrote: Thanks James, I don't figure out to use primary key solves the problem of duplicate keys. I had in radacct as primary key radacctid but now I am going to have acctuniqueid. This proble cause a new thread: why radacctid is the primary key of radacct table instead od acctuniqueid? From: James Wakefield [EMAIL PROTECTED] Reply-To: FreeRadius users mailing list freeradius-users@lists.freeradius.org To: FreeRadius users mailing list freeradius-users@lists.freeradius.org Subject: Re: Duplicate requests in a session Date: Wed, 30 Aug 2006 22:07:09 +1000 Santiago Balaguer García wrote: Hi people, 1) In my activity I realize that when the conexion to Internet of a NAS is NOT good (there are some reday in the DSL), the NAS send several Start requests. My problen is my RADIUS server ask for all these requests and they are inserted in my DB. So, when the user or the NAS finalize the session and NAS sends Stop Request, the credit associates to the user account is decremented several times. It happens so because I put a trgger in my DB to decrement the user credit atomatically. Can I avoid the problem of inserting several times the start request? If it is so, how?? 2) Is it supposed that the value of acctsessionid and acctuniqueid in radacct table are UNIQUE and they can not be duplicated ? Thanks, Santiago Hi Santiago, Does your DBMS enforce primary key constraints? Do you have a primary key defined for your radacct table? If I recall correctly, MySQL by default doesn't, are you using MySQL? Cheers, -- James Wakefield, Unix Administrator, Information Technology Services Division Deakin University, Geelong, Victoria 3217 Australia. Phone: 03 5227 8690 International: +61 3 5227 8690 Fax: 03 5227 8866 International: +61 3 5227 8866 E-mail: [EMAIL PROTECTED] Website: http://www.deakin.edu.au - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html _ Acepta el reto MSN Premium: Protección para tus hijos en internet. Descárgalo y pruébalo 2 meses gratis. http://join.msn.com?XAPID=1697DI=1055HL=Footer_mailsenviados_proteccionin fantil - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc - 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: Duplicate requests in a session
If you aplly this change and add this rule, you do the same that freeradius
does to build acctuniqueid attribute and put this attribute as primery key.
Good question. Does anyone have anything against changing this?
-Peter
On Thu 31 Aug 2006 10:11, Santiago Balaguer GarcÃa wrote:
Thanks James, I don't figure out to use primary key solves the problem
of
duplicate keys.
I had in radacct as primary key radacctid but now I am going to
have
acctuniqueid.
This proble cause a new thread: why radacctid is the primary key of
radacct
table instead od acctuniqueid?
I used a slightly different solution in my PostgreSQL implementation :
ALTER TABLE ONLY radacct
ADD CONSTRAINT radacct_unique_session UNIQUE (
username, nasipaddress, nasportid, acctsessionid
);
NOTE: When duplicate records come in you will see errors in the
log file like these :
Fri Jul 7 13:06:47 2006 : Error: rlm_sql (sql): failed after re-connect
Fri Jul 7 13:06:47 2006 : Error: rlm_sql (sql): Couldn't insert SQL
accounting START record - ERROR: duplicate key violates unique
constraint radacct_unique_session
These errors are mostly informational, because when the insert
fails, rlm_sql will use the alternate update method and will
succeed.
This is the same method I used on a customized Cistron
server I used for over 5 years and had no problems.
For some reason acctuniqueid was not unique in the duplicate
packets, so my initial attempts at using it were unsuccessful.
PostgreSQL can have a primary key that spans multiple
columns, and would look like this {IIRC} :
ALTER TABLE ONLY radacct
ADD CONSTRAINT radacct_pkey_session PRIMARY KEY (
username, nasipaddress, nasportid, acctsessionid
);
I did not use this, because I did not want to significantly change
the default configuration of most of the tables. Once I get a chance
to clean up the admin interface I have been developing I will
likely want to add some changes to the PostgreSQL default schema
that will allow better management without affecting the default
configuration, but since I am not finished I don't want to add
the changes to CVS quite yet.
_
Acepta el reto MSN Premium: Protección para tus hijos en internet.
Descárgalo y pruébalo 2 meses gratis.
http://join.msn.com?XAPID=1697DI=1055HL=Footer_mailsenviados_proteccioninfantil
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Duplicate requests in a session
Thanks James, I don't figure out to use primary key solves the problem of duplicate keys. I had in radacct as primary key radacctid but now I am going to have acctuniqueid. This proble cause a new thread: why radacctid is the primary key of radacct table instead od acctuniqueid? From: James Wakefield [EMAIL PROTECTED] Reply-To: FreeRadius users mailing list freeradius-users@lists.freeradius.org To: FreeRadius users mailing list freeradius-users@lists.freeradius.org Subject: Re: Duplicate requests in a session Date: Wed, 30 Aug 2006 22:07:09 +1000 Santiago Balaguer García wrote: Hi people, 1) In my activity I realize that when the conexion to Internet of a NAS is NOT good (there are some reday in the DSL), the NAS send several Start requests. My problen is my RADIUS server ask for all these requests and they are inserted in my DB. So, when the user or the NAS finalize the session and NAS sends Stop Request, the credit associates to the user account is decremented several times. It happens so because I put a trgger in my DB to decrement the user credit atomatically. Can I avoid the problem of inserting several times the start request? If it is so, how?? 2) Is it supposed that the value of acctsessionid and acctuniqueid in radacct table are UNIQUE and they can not be duplicated ? Thanks, Santiago Hi Santiago, Does your DBMS enforce primary key constraints? Do you have a primary key defined for your radacct table? If I recall correctly, MySQL by default doesn't, are you using MySQL? Cheers, -- James Wakefield, Unix Administrator, Information Technology Services Division Deakin University, Geelong, Victoria 3217 Australia. Phone: 03 5227 8690 International: +61 3 5227 8690 Fax: 03 5227 8866 International: +61 3 5227 8866 E-mail: [EMAIL PROTECTED] Website: http://www.deakin.edu.au - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html _ Acepta el reto MSN Premium: Protección para tus hijos en internet. Descárgalo y pruébalo 2 meses gratis. http://join.msn.com?XAPID=1697DI=1055HL=Footer_mailsenviados_proteccioninfantil - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Duplicate requests in a session
Good question. Does anyone have anything against changing this? -Peter On Thu 31 Aug 2006 10:11, Santiago Balaguer García wrote: Thanks James, I don't figure out to use primary key solves the problem of duplicate keys. I had in radacct as primary key radacctid but now I am going to have acctuniqueid. This proble cause a new thread: why radacctid is the primary key of radacct table instead od acctuniqueid? From: James Wakefield [EMAIL PROTECTED] Reply-To: FreeRadius users mailing list freeradius-users@lists.freeradius.org To: FreeRadius users mailing list freeradius-users@lists.freeradius.org Subject: Re: Duplicate requests in a session Date: Wed, 30 Aug 2006 22:07:09 +1000 Santiago Balaguer García wrote: Hi people, 1) In my activity I realize that when the conexion to Internet of a NAS is NOT good (there are some reday in the DSL), the NAS send several Start requests. My problen is my RADIUS server ask for all these requests and they are inserted in my DB. So, when the user or the NAS finalize the session and NAS sends Stop Request, the credit associates to the user account is decremented several times. It happens so because I put a trgger in my DB to decrement the user credit atomatically. Can I avoid the problem of inserting several times the start request? If it is so, how?? 2) Is it supposed that the value of acctsessionid and acctuniqueid in radacct table are UNIQUE and they can not be duplicated ? Thanks, Santiago Hi Santiago, Does your DBMS enforce primary key constraints? Do you have a primary key defined for your radacct table? If I recall correctly, MySQL by default doesn't, are you using MySQL? Cheers, -- James Wakefield, Unix Administrator, Information Technology Services Division Deakin University, Geelong, Victoria 3217 Australia. Phone: 03 5227 8690 International: +61 3 5227 8690 Fax: 03 5227 8866 International: +61 3 5227 8866 E-mail: [EMAIL PROTECTED] Website: http://www.deakin.edu.au - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html _ Acepta el reto MSN Premium: Protección para tus hijos en internet. Descárgalo y pruébalo 2 meses gratis. http://join.msn.com?XAPID=1697DI=1055HL=Footer_mailsenviados_proteccionin fantil - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc pgpyzIw2sQxcd.pgp Description: PGP signature - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Duplicate requests in a session
Peter Nixon [EMAIL PROTECTED] wrote:
Good question. Does anyone have anything against changing this?
They primary key should be a synthetic field, and not something
derived directly from the packet. Calling it 'acctuniqueid' is
awkward, maybe renaming it to 'radiuskey'?
It can then be used in the SQL queries as
%{Acct-Unique-ID:-%{Acct-Session-id}}, which should be safe for all
configuration.
Alan DeKok.
--
http://deployingradius.com - The web site of the book
http://deployingradius.com/blog/ - The blog
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Duplicate requests in a session
They primary key should be a synthetic field, and not something
derived directly from the packet. Calling it 'acctuniqueid' is
awkward, maybe renaming it to 'radiuskey'?
It can then be used in the SQL queries as
%{Acct-Unique-ID:-%{Acct-Session-id}}, which should be safe for all
configuration.
NOP, the default definition of the acctuniqueid attribute is correct.
acct_unique {
key = User-Name, Acct-Session-Id, NAS-IP-Address,
Client-IP-Address, NAS-Port
}
It is more possible that a user connect and disconnect several times in a
same session. I think it is task of the NAS to assign a unique session id to
a user. With acct_unique specification freeradius builds acctuniqueid
attribute.
This is my reason for the change. Do you agree???
_
Grandes éxitos, superhéroes, imitaciones, cine y TV...
http://es.msn.kiwee.com/ Lo mejor para tu móvil.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Duplicate requests in a session
[EMAIL PROTECTED] wrote: NOP, the default definition of the acctuniqueid attribute is correct. I don't think you're clear on what is being discussed. It is more possible that a user connect and disconnect several times in a same session. No, it is not possible. I think it is task of the NAS to assign a unique session id to a user. Yes, and many NASes don't do that. With acct_unique specification freeradius builds acctuniqueid attribute. Yes, which is why I wrote the acct_unique module. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Duplicate requests in a session
On Thu, 2006-08-31 at 12:31 +0300, Peter Nixon wrote:
Good question. Does anyone have anything against changing this?
-Peter
On Thu 31 Aug 2006 10:11, Santiago Balaguer García wrote:
Thanks James, I don't figure out to use primary key solves the problem of
duplicate keys.
I had in radacct as primary key radacctid but now I am going to have
acctuniqueid.
This proble cause a new thread: why radacctid is the primary key of radacct
table instead od acctuniqueid?
I used a slightly different solution in my PostgreSQL implementation :
ALTER TABLE ONLY radacct
ADD CONSTRAINT radacct_unique_session UNIQUE (
username, nasipaddress, nasportid, acctsessionid
);
NOTE: When duplicate records come in you will see errors in the
log file like these :
Fri Jul 7 13:06:47 2006 : Error: rlm_sql (sql): failed after re-connect
Fri Jul 7 13:06:47 2006 : Error: rlm_sql (sql): Couldn't insert SQL
accounting START record - ERROR: duplicate key violates unique
constraint radacct_unique_session
These errors are mostly informational, because when the insert
fails, rlm_sql will use the alternate update method and will
succeed.
This is the same method I used on a customized Cistron
server I used for over 5 years and had no problems.
For some reason acctuniqueid was not unique in the duplicate
packets, so my initial attempts at using it were unsuccessful.
PostgreSQL can have a primary key that spans multiple
columns, and would look like this {IIRC} :
ALTER TABLE ONLY radacct
ADD CONSTRAINT radacct_pkey_session PRIMARY KEY (
username, nasipaddress, nasportid, acctsessionid
);
I did not use this, because I did not want to significantly change
the default configuration of most of the tables. Once I get a chance
to clean up the admin interface I have been developing I will
likely want to add some changes to the PostgreSQL default schema
that will allow better management without affecting the default
configuration, but since I am not finished I don't want to add
the changes to CVS quite yet.
From: James Wakefield [EMAIL PROTECTED]
Reply-To: FreeRadius users mailing list
freeradius-users@lists.freeradius.org
To: FreeRadius users mailing list freeradius-users@lists.freeradius.org
Subject: Re: Duplicate requests in a session
Date: Wed, 30 Aug 2006 22:07:09 +1000
Santiago Balaguer García wrote:
Hi people,
1)
In my activity I realize that when the conexion to Internet of a NAS is
NOT good (there are some reday in the DSL), the NAS send several Start
requests. My problen is my RADIUS server ask for all these requests and
they are inserted in my DB. So, when the user or the NAS finalize the
session and NAS sends Stop Request, the credit associates to the user
account is decremented several times. It happens so because I put a
trgger in my DB to decrement the user credit atomatically.
Can I avoid the problem of inserting several times the start request?
If it is so, how??
2) Is it supposed that the value of acctsessionid and acctuniqueid in
radacct table are UNIQUE and they can not be duplicated ?
Thanks,
Santiago
Hi Santiago,
Does your DBMS enforce primary key constraints? Do you have a primary key
defined for your radacct table? If I recall correctly, MySQL by default
doesn't, are you using MySQL?
Cheers,
--
James Wakefield,
Unix Administrator, Information Technology Services Division
Deakin University, Geelong, Victoria 3217 Australia.
Phone: 03 5227 8690 International: +61 3 5227 8690
Fax: 03 5227 8866 International: +61 3 5227 8866
E-mail: [EMAIL PROTECTED]
Website: http://www.deakin.edu.au
- List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
_
Acepta el reto MSN Premium: Protección para tus hijos en internet.
Descárgalo y pruébalo 2 meses gratis.
http://join.msn.com?XAPID=1697DI=1055HL=Footer_mailsenviados_proteccionin
fantil
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
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
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Duplicate requests in a session
Santiago Balaguer García wrote: Hi people, 1) In my activity I realize that when the conexion to Internet of a NAS is NOT good (there are some reday in the DSL), the NAS send several Start requests. My problen is my RADIUS server ask for all these requests and they are inserted in my DB. So, when the user or the NAS finalize the session and NAS sends Stop Request, the credit associates to the user account is decremented several times. It happens so because I put a trgger in my DB to decrement the user credit atomatically. Can I avoid the problem of inserting several times the start request? If it is so, how?? 2) Is it supposed that the value of acctsessionid and acctuniqueid in radacct table are UNIQUE and they can not be duplicated ? Thanks, Santiago Hi Santiago, Does your DBMS enforce primary key constraints? Do you have a primary key defined for your radacct table? If I recall correctly, MySQL by default doesn't, are you using MySQL? Cheers, -- James Wakefield, Unix Administrator, Information Technology Services Division Deakin University, Geelong, Victoria 3217 Australia. Phone: 03 5227 8690 International: +61 3 5227 8690 Fax: 03 5227 8866 International: +61 3 5227 8866 E-mail: [EMAIL PROTECTED] Website: http://www.deakin.edu.au - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
