RE: (RADIATOR) SQL Timeout when purging ACCOUNTING table
My experience with such things is that it's a database contention problem: the process of purging the table can easily (for Sql Server) escalate to a table-lock, blocking any further Insert attempts. I had to write a routine that would drop into a loop and delete one row at a time, referring to that row exclusively by its Primary Key. That way, the DBMS could find the row using the table index, and a table lock was hence not necessary. This worked for me. Another thing to consider is that the purge could simply be pegging the CPU on the DB server (we've seen this, too). This is a lot easier to check: use "top" on Unix or else Task Manager in NT. Dave > -Original Message- > From: Janet N. del Mundo [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, March 13, 2001 5:16 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: (RADIATOR) SQL Timeout when purging ACCOUNTING table > > > Hi Hugh, > > The missedaccounting file contained just the accounting stop records. > Yes, I waited for about 30 mins. and authentication requests > started to > be rejected. That's when I had to restart Radiator. What can I do to > not interrupt the users authentication? Would it make any > difference if > I use just a regular flat file, and not the 'AcctFailedLog...' feature > to capture start and stop records? > > Thanks, > Janet > > Hugh Irvine wrote: > > > > Hello Janet - > > > > What data is contained in the AcctFailedLogFileName file? > Did it actually > > catch the accounting data? Notice that this will only catch > accounting stops > > as sessions end, and as the debug shows, Radiator will not retry the > > connection for 10 minutes (600 seconds) - did you wait that > long to see if it > > would re-establish? > > > > regards > > > > Hugh > > > > On Tuesday 13 March 2001 15:58, Janet N. del Mundo wrote: > > > Hi everyone, > > > > > > Radiator still times out connecting to the SQL server > when we do a month > > > end purge of old accounting data and our users are unable to > > > authenticate. I thought by using the > 'AcctFailedLogFileName' feature, > > > the accounting data would default to this flat file and > not interrupt > > > user authentication. However, we just did a purge of old > data and I had > > > to restart Radiator when the purge process was done. > What can I do to > > > avoid this problem? > > > > > > log file: > > > - > > > Mon Mar 12 07:40:12 2001: ERR: Could not connect to SQL > database with > > > DBI->connect dbi:ODBC:, x, x: > [OpenLink][ODBC]exceeded > > > maximum number of allowed connections (SQL-08004) > > > [OpenLink][ODBC]Connection rejected by data source (SQL-08004) > > > [OpenLink][ODBC]exceeded maximum number of allowed connections > > > (SQL-08004) > > > [OpenLink][ODBC]Connection rejected by data source > (SQL-08004)(DBD: > > > db_login/SQLConnect err=-1) > > > Mon Mar 12 07:40:12 2001: ERR: Could not connect to any > SQL database. > > > Request is ignored. Backing off for 600 sec > > > onds > > > > > > config file: > > > > > > > > > Identifier SQL > > > DBSource dbi:ODBC:x > > > DBUsername x > > > DBAuth x > > > > > > AuthSelect select Password, Expiration, SimUse, \ > > > IdleTime, SessionTime, StaticIP \ > > > from USERS where IDENTIFIER = '%n' AND > STATUS != 'C' > > > > > > AuthColumnDef 1, Expiration, check > > > AuthColumnDef 2, Simultaneous-Use, check > > > AuthColumnDef 3, Idle-Timeout, reply > > > AuthColumnDef 4, Session-Timeout, reply > > > AuthColumnDef 5, Framed-IP-Address, reply > > > > > > AccountingTable ACCOUNTING > > > > > > AcctColumnDef IDENTIFIER,User-Name > > > AcctColumnDef > TIME_STAMP,Timestamp,formatted-date,'%m-%d-%Y > > > %H:%M:%S' > > > AcctColumnDef DURATION,Acct-Session-Time,integer > > > AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type > > > AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer > > > #AcctColumnDef ACCTINPUTOCTETS,Acct-Input-Octets,integer > > > #AcctColumnDef > ACCTOUTPUTOCTETS,Acct-Output-Octets,integer > > > AcctC
Re: (RADIATOR) SQL Timeout when purging ACCOUNTING table
Hello Janet - On Wednesday 14 March 2001 09:15, Janet N. del Mundo wrote: > Hi Hugh, > > The missedaccounting file contained just the accounting stop records. > Yes, I waited for about 30 mins. and authentication requests started to > be rejected. That's when I had to restart Radiator. What can I do to > not interrupt the users authentication? Would it make any difference if > I use just a regular flat file, and not the 'AcctFailedLog...' feature > to capture start and stop records? > It won't make any difference what file you use, as the problem appears to be with the OpenLink ODBC driver, or with the database (although I suspect the former). BTW - if monthly (long) runs are causing a problem, why not do a daily run at the quietest time of your day (probably around 4am)? You really shouldn't be doing any heavy processing on the database during your busy times. I have forwarded your mail to Mike for his comments. regards Hugh -- Radiator: the most portable, flexible and configurable RADIUS server anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X. - Nets: internetwork inventory and management - graphical, extensible, flexible with hardware, software, platform and database independence. === Archive at http://www.starport.net/~radiator/ Announcements on [EMAIL PROTECTED] To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.
Re: (RADIATOR) SQL Timeout when purging ACCOUNTING table
Hi Hugh,
The missedaccounting file contained just the accounting stop records.
Yes, I waited for about 30 mins. and authentication requests started to
be rejected. That's when I had to restart Radiator. What can I do to
not interrupt the users authentication? Would it make any difference if
I use just a regular flat file, and not the 'AcctFailedLog...' feature
to capture start and stop records?
Thanks,
Janet
Hugh Irvine wrote:
>
> Hello Janet -
>
> What data is contained in the AcctFailedLogFileName file? Did it actually
> catch the accounting data? Notice that this will only catch accounting stops
> as sessions end, and as the debug shows, Radiator will not retry the
> connection for 10 minutes (600 seconds) - did you wait that long to see if it
> would re-establish?
>
> regards
>
> Hugh
>
> On Tuesday 13 March 2001 15:58, Janet N. del Mundo wrote:
> > Hi everyone,
> >
> > Radiator still times out connecting to the SQL server when we do a month
> > end purge of old accounting data and our users are unable to
> > authenticate. I thought by using the 'AcctFailedLogFileName' feature,
> > the accounting data would default to this flat file and not interrupt
> > user authentication. However, we just did a purge of old data and I had
> > to restart Radiator when the purge process was done. What can I do to
> > avoid this problem?
> >
> > log file:
> > -
> > Mon Mar 12 07:40:12 2001: ERR: Could not connect to SQL database with
> > DBI->connect dbi:ODBC:, x, x: [OpenLink][ODBC]exceeded
> > maximum number of allowed connections (SQL-08004)
> > [OpenLink][ODBC]Connection rejected by data source (SQL-08004)
> > [OpenLink][ODBC]exceeded maximum number of allowed connections
> > (SQL-08004)
> > [OpenLink][ODBC]Connection rejected by data source (SQL-08004)(DBD:
> > db_login/SQLConnect err=-1)
> > Mon Mar 12 07:40:12 2001: ERR: Could not connect to any SQL database.
> > Request is ignored. Backing off for 600 sec
> > onds
> >
> > config file:
> >
> >
> > Identifier SQL
> > DBSource dbi:ODBC:x
> > DBUsername x
> > DBAuth x
> >
> > AuthSelect select Password, Expiration, SimUse, \
> > IdleTime, SessionTime, StaticIP \
> > from USERS where IDENTIFIER = '%n' AND STATUS != 'C'
> >
> > AuthColumnDef 1, Expiration, check
> > AuthColumnDef 2, Simultaneous-Use, check
> > AuthColumnDef 3, Idle-Timeout, reply
> > AuthColumnDef 4, Session-Timeout, reply
> > AuthColumnDef 5, Framed-IP-Address, reply
> >
> > AccountingTable ACCOUNTING
> >
> > AcctColumnDef IDENTIFIER,User-Name
> > AcctColumnDef TIME_STAMP,Timestamp,formatted-date,'%m-%d-%Y
> > %H:%M:%S'
> > AcctColumnDef DURATION,Acct-Session-Time,integer
> > AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
> > AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
> > #AcctColumnDef ACCTINPUTOCTETS,Acct-Input-Octets,integer
> > #AcctColumnDef ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
> > AcctColumnDef ACCTSESSIONID,Acct-Session-Id
> > AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
> > #AcctColumnDef NASIDENTIFIER,NAS-Identifier
> > AcctColumnDef NASIDENTIFIER,NAS-IP-Address
> > AcctColumnDef NASPORT,NAS-Port,integer
> > AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
> > AcctColumnDef CONNECTSPEED,Connect-Info
> > AcctColumnDef CONNECTSPEED,USR-Connect-Speed
> > AcctColumnDef CALLERID,Calling-Station-Id
> > AcctColumnDef POPID,Called-Station-Id
> >
> > # You can arrange to log accounting to a file if the
> > # SQL insert fails with AcctFailedLogFileName
> > # That way you could recover from a broken SQL
> > # server
> > AcctLogFileFormat %{User-Name};%m-%d-%Y
> > %H:%M:%S;%{Acct-Session-Time};%{Acct-Status-Type};\
> > %{Acct-Delay-Time};%{Acct-Session-Id};%{Acct-Terminate-Cause};\
> > %{NAS-IP-Address};%{NAS-Port};%{Framed-IP-Address};%{Connect-Info};\
> > %{USR-Connect-Speed};%{Calling-Station-Id};%{Called-Station-Id}
> >
> > AcctFailedLogFileName %D/missedaccounting.%y%m%d
> >
> >
> >
> > Thank you,
> > Janet
>
> --
> Radiator: the most portable, flexible and configurable RADIUS server
> anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
> -
> Nets: internetwork inventory and management - graphical, extensible,
> flexible with hardware, software, platform and database independence.
--
_
Janet del Mundo
Internet Administrator, Startec Global Communications
135 Chalan Santo Papa Agana, Guam 96910
Email: [EMAIL PROTECTED]
Phone: (671) 475-6879
Fax : (671) 477-6054
===
Archive at http://www.starport.net/~radiator/
Announcements on [EMAIL PROTECTED]
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe ra
Re: (RADIATOR) SQL Timeout when purging ACCOUNTING table
Hello Janet -
What data is contained in the AcctFailedLogFileName file? Did it actually
catch the accounting data? Notice that this will only catch accounting stops
as sessions end, and as the debug shows, Radiator will not retry the
connection for 10 minutes (600 seconds) - did you wait that long to see if it
would re-establish?
regards
Hugh
On Tuesday 13 March 2001 15:58, Janet N. del Mundo wrote:
> Hi everyone,
>
> Radiator still times out connecting to the SQL server when we do a month
> end purge of old accounting data and our users are unable to
> authenticate. I thought by using the 'AcctFailedLogFileName' feature,
> the accounting data would default to this flat file and not interrupt
> user authentication. However, we just did a purge of old data and I had
> to restart Radiator when the purge process was done. What can I do to
> avoid this problem?
>
> log file:
> -
> Mon Mar 12 07:40:12 2001: ERR: Could not connect to SQL database with
> DBI->connect dbi:ODBC:, x, x: [OpenLink][ODBC]exceeded
> maximum number of allowed connections (SQL-08004)
> [OpenLink][ODBC]Connection rejected by data source (SQL-08004)
> [OpenLink][ODBC]exceeded maximum number of allowed connections
> (SQL-08004)
> [OpenLink][ODBC]Connection rejected by data source (SQL-08004)(DBD:
> db_login/SQLConnect err=-1)
> Mon Mar 12 07:40:12 2001: ERR: Could not connect to any SQL database.
> Request is ignored. Backing off for 600 sec
> onds
>
> config file:
>
>
> Identifier SQL
> DBSource dbi:ODBC:x
> DBUsername x
> DBAuth x
>
> AuthSelect select Password, Expiration, SimUse, \
> IdleTime, SessionTime, StaticIP \
> from USERS where IDENTIFIER = '%n' AND STATUS != 'C'
>
> AuthColumnDef 1, Expiration, check
> AuthColumnDef 2, Simultaneous-Use, check
> AuthColumnDef 3, Idle-Timeout, reply
> AuthColumnDef 4, Session-Timeout, reply
> AuthColumnDef 5, Framed-IP-Address, reply
>
> AccountingTable ACCOUNTING
>
> AcctColumnDef IDENTIFIER,User-Name
> AcctColumnDef TIME_STAMP,Timestamp,formatted-date,'%m-%d-%Y
> %H:%M:%S'
> AcctColumnDef DURATION,Acct-Session-Time,integer
> AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
> AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
> #AcctColumnDef ACCTINPUTOCTETS,Acct-Input-Octets,integer
> #AcctColumnDef ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
> AcctColumnDef ACCTSESSIONID,Acct-Session-Id
> AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
> #AcctColumnDef NASIDENTIFIER,NAS-Identifier
> AcctColumnDef NASIDENTIFIER,NAS-IP-Address
> AcctColumnDef NASPORT,NAS-Port,integer
> AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
> AcctColumnDef CONNECTSPEED,Connect-Info
> AcctColumnDef CONNECTSPEED,USR-Connect-Speed
> AcctColumnDef CALLERID,Calling-Station-Id
> AcctColumnDef POPID,Called-Station-Id
>
> # You can arrange to log accounting to a file if the
> # SQL insert fails with AcctFailedLogFileName
> # That way you could recover from a broken SQL
> # server
> AcctLogFileFormat %{User-Name};%m-%d-%Y
> %H:%M:%S;%{Acct-Session-Time};%{Acct-Status-Type};\
> %{Acct-Delay-Time};%{Acct-Session-Id};%{Acct-Terminate-Cause};\
> %{NAS-IP-Address};%{NAS-Port};%{Framed-IP-Address};%{Connect-Info};\
> %{USR-Connect-Speed};%{Calling-Station-Id};%{Called-Station-Id}
>
> AcctFailedLogFileName %D/missedaccounting.%y%m%d
>
>
>
> Thank you,
> Janet
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
===
Archive at http://www.starport.net/~radiator/
Announcements on [EMAIL PROTECTED]
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
(RADIATOR) SQL Timeout when purging ACCOUNTING table
Hi everyone,
Radiator still times out connecting to the SQL server when we do a month
end purge of old accounting data and our users are unable to
authenticate. I thought by using the 'AcctFailedLogFileName' feature,
the accounting data would default to this flat file and not interrupt
user authentication. However, we just did a purge of old data and I had
to restart Radiator when the purge process was done. What can I do to
avoid this problem?
log file:
-
Mon Mar 12 07:40:12 2001: ERR: Could not connect to SQL database with
DBI->connect dbi:ODBC:, x, x: [OpenLink][ODBC]exceeded
maximum number of allowed connections (SQL-08004)
[OpenLink][ODBC]Connection rejected by data source (SQL-08004)
[OpenLink][ODBC]exceeded maximum number of allowed connections
(SQL-08004)
[OpenLink][ODBC]Connection rejected by data source (SQL-08004)(DBD:
db_login/SQLConnect err=-1)
Mon Mar 12 07:40:12 2001: ERR: Could not connect to any SQL database.
Request is ignored. Backing off for 600 sec
onds
config file:
Identifier SQL
DBSource dbi:ODBC:x
DBUsername x
DBAuth x
AuthSelect select Password, Expiration, SimUse, \
IdleTime, SessionTime, StaticIP \
from USERS where IDENTIFIER = '%n' AND STATUS != 'C'
AuthColumnDef 1, Expiration, check
AuthColumnDef 2, Simultaneous-Use, check
AuthColumnDef 3, Idle-Timeout, reply
AuthColumnDef 4, Session-Timeout, reply
AuthColumnDef 5, Framed-IP-Address, reply
AccountingTable ACCOUNTING
AcctColumnDef IDENTIFIER,User-Name
AcctColumnDef TIME_STAMP,Timestamp,formatted-date,'%m-%d-%Y
%H:%M:%S'
AcctColumnDef DURATION,Acct-Session-Time,integer
AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
#AcctColumnDef ACCTINPUTOCTETS,Acct-Input-Octets,integer
#AcctColumnDef ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
AcctColumnDef ACCTSESSIONID,Acct-Session-Id
AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
#AcctColumnDef NASIDENTIFIER,NAS-Identifier
AcctColumnDef NASIDENTIFIER,NAS-IP-Address
AcctColumnDef NASPORT,NAS-Port,integer
AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
AcctColumnDef CONNECTSPEED,Connect-Info
AcctColumnDef CONNECTSPEED,USR-Connect-Speed
AcctColumnDef CALLERID,Calling-Station-Id
AcctColumnDef POPID,Called-Station-Id
# You can arrange to log accounting to a file if the
# SQL insert fails with AcctFailedLogFileName
# That way you could recover from a broken SQL
# server
AcctLogFileFormat %{User-Name};%m-%d-%Y
%H:%M:%S;%{Acct-Session-Time};%{Acct-Status-Type};\
%{Acct-Delay-Time};%{Acct-Session-Id};%{Acct-Terminate-Cause};\
%{NAS-IP-Address};%{NAS-Port};%{Framed-IP-Address};%{Connect-Info};\
%{USR-Connect-Speed};%{Calling-Station-Id};%{Called-Station-Id}
AcctFailedLogFileName %D/missedaccounting.%y%m%d
Thank you,
Janet
--
_
Janet del Mundo
Internet Administrator, Startec Global Communications
135 Chalan Santo Papa Agana, Guam 96910
Email: [EMAIL PROTECTED]
===
Archive at http://www.starport.net/~radiator/
Announcements on [EMAIL PROTECTED]
To unsubscribe, email '[EMAIL PROTECTED]' with
'unsubscribe radiator' in the body of the message.
