Duplicate requests in a session
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 _________________________________________________________________ Grandes éxitos, superhéroes, imitaciones, cine y TV... http://es.msn.kiwee.com/ Lo mejor para tu móvil.
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: james.wakefield@deakin.edu.au Website: http://www.deakin.edu.au
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 <jamesw@deakin.edu.au> 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: james.wakefield@deakin.edu.au 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=1697&DI=1055&HL=Footer_mailsenviados_proteccioninf...
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 <jamesw@deakin.edu.au> 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: james.wakefield@deakin.edu.au 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=1697&DI=1055&HL=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
Peter Nixon <listuser@peternixon.net> 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
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.
santiagoawa@hotmail.com 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
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 <jamesw@deakin.edu.au> 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: james.wakefield@deakin.edu.au 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=1697&DI=1055&HL=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
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=1697&DI=1055&HL=Footer_mailsenviados_proteccioninf...
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 <listuser@peternixon.net> 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 <jamesw@deakin.edu.au> 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: james.wakefield@deakin.edu.au 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=1697&DI=1055&HL=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
participants (6)
-
Alan DeKok -
Alex French -
Guy Fraser -
James Wakefield -
Peter Nixon -
Santiago Balaguer García