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 <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
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
On Thu 31 Aug 2006 18:19, Guy Fraser wrote:
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 );
Yes. Infact the pgsql-voip.conf setup does almost exactly that. The problem with this is that because the sql module detects it as a sql failure it disconnects and reconnects the socket which is very wastefull on resources. This should really be solved in the postgres driver (When I wrote the reconnect code for rlm_sql_postgresql there were not actually sufficient return codes from the progresql library so it was either that, on not reconnect on some other (bad) error conditions). In the end I moved the whole query inside a stored procedure and hid the " ERROR: duplicate key violates unique constraint" errors from radiusd however I didnt commit this to cvs as stored procedures are a bit complex for the default FR config. (Just understanding how sql works seems to stump a significant number of our users.) -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
On Thu 31 Aug 2006 18:19, Guy Fraser wrote:
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 );
After looking at this again, a unique constraint (or primary key) on "acctuniqueid" will give exactly the same behaviour as you have here with much lower load on the database. The default acct_unique looks like this: acct_unique { key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address, NAS-Port" } Which has Client-IP-Address as well, but that is trivial to change if you want the same behaviour. It appears that the best way to enforce unique data in the database is to use the "acctuniqueid" however, the next question is: Should we be trying to ensure unique records at runtime or should that be left to post processing? I know than most people "expect" the records to be unique, but is that a realistic expectation? I have a pair of NAS in failover config (Rather large Ciscos running the absolute latest IOS technology release) which send the exact same values in every request: NAS-Port-Type = Virtual NAS-Port = 60000 NAS-Port-Id = "GGSN" Now, while this is not ideal, given that the NAS-Port-Type is Virtual it seems to be legal. Given that I have several thousand users all with identical usernames the default "acctuniqueid" is going to have exactly the same amount of uniqueness as the Acct-Session-Id as all other values are fixed (With the exception of when a NAS failover occurs). Now in my case, I do have other things that I can add to make things more unique (Calling-Station-Id, 3GPP-Charging-ID, 3GPP-IMSI etc) but the point is that I think we are going to trade one type of problem for another. (Now we have people asking why their records aren't unique, later we will have certain sets of people asking why half their records don't appear in the database) Is that a good or a bad tradeoff? I am leaning towards good, but its pretty close to even money.. Cheers -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Peter Nixon <listuser@peternixon.net> wrote:
Should we be trying to ensure unique records at runtime or should that be left to post processing?
If the records are looked up by the server at runtime, they should be made unique at runtime.
I know than most people "expect" the records to be unique, but is that a realistic expectation? I have a pair of NAS in failover config (Rather large Ciscos running the absolute latest IOS technology release) which send the exact same values in every request:
I know... it's a bit of a problem.
Is that a good or a bad tradeoff? I am leaning towards good, but its pretty close to even money..
I'm not sure. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
participants (3)
-
Alan DeKok -
Guy Fraser -
Peter Nixon