buffered_sql problem

Ricardo Larrañaga ricardo.larranaga at gmail.com
Thu Aug 13 20:02:33 CEST 2015 

Hey guys:
Does someone have any ideas on how I should deal with this? Should I open a
bug report on github?
Thanks.
Regards
On Aug 12, 2015 3:37 PM, "Ricardo Larrañaga" <ricardo.larranaga at gmail.com>
wrote:

> mmmm...i am not sure that is the same problem.
> In my case, it is clear that the session was recorded to the sql database
> before the acct start gets there. and since the detail file is sequential,
> i am not sure how the delay could generate the issue.
>
> I guess the main question fro my would be:
>
> Should the server be able to recover from a duplicated session packet on a
> buffered_sql server, or shouldn't?
>
> Right now, it looks like when it happens, the server is not able to
> process more packets from that detail file, until reset.
>
> Does anyone has any ideas?
> Thanks.
> Regards
>
> On Wed, Aug 12, 2015 at 2:41 PM, Jorge Pereira <jpereiran at gmail.com>
> wrote:
>
>> Hi,
>>
>>   I have a similar problem in my current setup. My approach was to
>> replicate the 'detail' file to another directory
>> without as below example.
>>
>> -----
>> root at lab:/etc/freeradius# grep -v -E "^(.*#|$)"
>> mods-enabled/detail-singlebucket
>> detail *detail-singlebucket* {
>>         *filename = ${radacctdir}/bucket/detail*
>>         permissions = 0600
>>         header = "%t"
>>         locking = yes
>> }
>> root at lab:/etc/freeradius#
>> -----
>>
>> That is called in the section 'accounting' from my
>> $raddb/sites-enabled/accounting and I have another server
>> as described below, That is used to listen and process the 'detail'.
>>
>> -----
>> root at lab:/etc/freeradius# grep -v -E "^(.*#|$)"
>> sites-available/buffered-sql-accounting
>> server *buffered-sql-accouting* {
>>         listen {
>>                 type = detail
>>                 *filename = "${radacctdir}/bucket/detail"*
>>                 load_factor = 10
>>                 poll_interval = 5
>>                 retry_interval = 30
>>         }
>>         preacct {
>>
>>         }
>>         accounting {
>>                 # this is a copy of sql/accounting but inserting in the
>> table 'radiusaccounting_buffered' (same scheme of radiusaccounting)
>>                 -sql_accounting-tb_buffered
>>         }
>> }
>> root at lab:/etc/freeradius#
>> -----
>>
>> The problem is because the accountings from the 'buffered' has a big delay
>> as compared between the queries below.
>>
>> ------
>> mysql> select acctsessionid,acctstarttime from *radiusaccounting*  order
>> by
>> radacctid DESC LIMIT 10;
>> +------------------------+---------------------+
>> | acctsessionid          | acctstarttime       |
>> +------------------------+---------------------+
>> | 166B5A00045CE955CB50BD | 2015-08-12 16:57:19 |
>> | 166B5A00045CDB55CB50AE | 2015-08-12 16:57:03 |
>> | 166B5A00045BAC55CB4F82 | 2015-08-12 16:52:02 |
>> | 166B5A000459C855CB4DEA | 2015-08-12 16:45:14 |
>> | 166B5A0004556755CB49B9 | 2015-08-12 16:27:21 |
>> | 166B5A000453FE55CB4859 | 2015-08-12 16:21:33 |
>> | 166B5A000453E655CB4843 | 2015-08-12 16:21:07 |
>> | 166B5A0004537F55CB47C5 | 2015-08-12 16:19:01 |
>> | 166B5A000452D555CB472D | 2015-08-12 16:16:30 |
>> | 166B5A0004515855CB45A1 | 2015-08-12 16:09:53 |
>> +------------------------+---------------------+
>> 10 rows in set (0.08 sec)
>>
>> mysql> select acctsessionid,acctstarttime from *radiusaccounting_buffered*
>> order by radacctid DESC LIMIT 10;
>> +------------------------+---------------------+
>> | acctsessionid          | acctstarttime       |
>> +------------------------+---------------------+
>> | 166B5A0004481455CB3D4C | 2015-08-12 15:34:21 |
>> | 166B5A0004462155CB3BAB | 2015-08-12 15:27:23 |
>> | 166B5A0004420355CB37FD | 2015-08-12 15:11:43 |
>> | 166B5A000440F755CB3705 | 2015-08-12 15:07:33 |
>> | 166B5A00043F4C55CB3571 | 2015-08-12 15:00:50 |
>> | 166B5A00043E8555CB34D5 | 2015-08-12 14:58:13 |
>> | 166B5A00043D9155CB33E6 | 2015-08-12 14:54:15 |
>> | 166B5A00043B6955CB31C3 | 2015-08-12 14:45:07 |
>> | 166B5A0004389F55CB2F46 | 2015-08-12 14:34:30 |
>> | 166B5A0004385D55CB2EFE | 2015-08-12 14:33:18 |
>> +------------------------+---------------------+
>> 10 rows in set (0.01 sec)
>>
>> mysql>
>> ------
>>
>> I believe that the 'buffered' scheme is too slow that the default
>> approach.
>> someone have suggestions?
>>
>>
>> --
>> Jorge Pereira
>>
>> On Wed, Aug 12, 2015 at 10:02 AM, Ricardo Larrañaga <
>> ricardo.larranaga at gmail.com> wrote:
>>
>> > Hey Alan:
>> > I was wondering if you could help me out a little bit more with this
>> issue.
>> > The problem repeated itself last week.
>> >
>> > I tracked it to a session that only lasted 1 second. For this session i
>> had
>> > a complete accounting record in my database when a start packet came in.
>> > This is the first packet in my detail file, and after it , the detail
>> file
>> > started increasing in size, and my database did not receive any more
>> > accounting stops.
>> >
>> > I remember you mentioning that when there is any "nasty" packets in the
>> > detail file, the server gets stuck and you need to edit the file
>> manually
>> > to remove the packets.
>> > Last time i thought that wast the case, because when i increased the
>> load
>> > factor in my buffered_sql server and restarted the server, the detail
>> file
>> > got processed completely.
>> >
>> > But since this happened again, i guess the server does get stuck.
>> > What i do not get is how come just restarting the server solves the
>> issue
>> > (that also solved the issue this time). Well, i also increased the load
>> > size, but i would not expect that to have any impact on the server
>> logic.
>> >
>> > Is this an expected behavior? Shouldn't the server be able to process
>> the
>> > packet without restarting the server, since it clearly can process it
>> when
>> > you restart it.
>> >
>> > I am just looking for a way to deal with this issue without manual
>> > intervention.
>> >
>> > Thanks a lot!
>> > Regards
>> >
>> >
>> > On Mon, Aug 3, 2015 at 12:40 PM, Ricardo Larrañaga <
>> > ricardo.larranaga at gmail.com> wrote:
>> >
>> > > Hi Alan:
>> > > Actually, i was about to answer my own quesiton.
>> > > So, it looks like the server keeps processing the file, no matter if a
>> > > packet had a failed query.
>> > > Somehow, my server started getting delayed on processing packets from
>> the
>> > > detail file (need to investigate why).
>> > > So what i did just know is increasing the load parameter on buffered
>> sql
>> > > and after a highly loaded cpu period, the server caught up, deleted
>> the
>> > > file and showed me sessions from today.
>> > > What version are you using? I dont really have anything configured to
>> > > nulligy the duplicated records, so i am wondering know if keep
>> processing
>> > > the file even if the insert fails is a new feature?
>> > > Thanks.
>> > > Regards
>> > >
>> > >
>> > >
>> > > On Mon, Aug 3, 2015 at 12:29 PM, <A.L.M.Buxey at lboro.ac.uk> wrote:
>> > >
>> > >> Hi,
>> > >>
>> > >> > that is already in the db, so those fail, but my question is, will
>> the
>> > >> > server keep trying to insert that record and not try any others in
>> the
>> > >> > detail.work file?
>> > >>
>> > >> yep. any nastiness and it gets stuck....and wont proceed. you need
>> > >> to nullify it...either stop server, edit the .work file to remove
>> > >> the dodgy record or configure server to walk over it... probably
>> > >> something hacky like this
>> > >>
>> > >>
>> > >>  sql {
>> > >>          invalid = 2
>> > >>          fail = 2
>> > >>  }
>> > >>  if (fail || noop || invalid) {
>> > >>          ok
>> > >>  }
>> > >>
>> > >> alan
>> > >> -
>> > >> List info/subscribe/unsubscribe? See
>> > >> http://www.freeradius.org/list/users.html
>> > >
>> > >
>> > >
>> > -
>> > List info/subscribe/unsubscribe? See
>> > http://www.freeradius.org/list/users.html
>> >
>> -
>> List info/subscribe/unsubscribe? See
>> http://www.freeradius.org/list/users.html
>>
>
>

More information about the Freeradius-Users mailing list