Duplicate requests in a session

[Guy Fraser]

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 at deakin.edu.au>
> > >Reply-To: FreeRadius users mailing list
> > ><freeradius-users at lists.freeradius.org>
> > >To: FreeRadius users mailing list <freeradius-users at 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 at 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