(RADIATOR) SQL Timeout?

[Hugh Irvine]

Hello Griff -

The first thing to check is the MySQL logs to see what is happening  
with the database.

Then you should probably turn on LogMicroseconds (requires Time-HiRes  
from CPAN) to see how long the queries are taking and what is happening  
when the problem occurs. It sounds like there is some large processing  
job that is tying up the database server every 3 hours.

And as usual, a complete copy of the configuration file (no secrets)  
and a trace 4 debug are needed to say any more.

regards

Hugh


On Wednesday, October 23, 2002, at 02:08 AM, Griff Hamlin wrote:

> Hello,
>
> I've noticed that about every 3 hours, I get an error message in my
> logfile:
>
> Tue Oct 22 02:03:03 2002: ERR: do failed for 'insert into dialupusage
>       (username, session_id, router_ip, date, session_time, ip_address,
> phone)
>       values
>       ('level3', '0000123410-22', '209.244.125.132', '2002-10-22
> 2:2:53', '0', '65.56.213.14', '9876543210')': SQL Timeout
>
> and whenever I get this message, my server stops authenticating fast
> enough. What's odd, is that if I run radpwtst and try to do a test
> authentication, I'll get no reply after seeing this message until I
> restart the radius server. But if I 'tail -f' my logfile, I see people
> being authenticated, it's just getting to them maybe a minute or more
> after the request comes in. Basically, the server gets behind in the
> event it gets one of the SQL Timeout errors. My first question is, what
> would cause that error, and secondly, why does it hang up Radiator?
>
> The part of my radius.cfg file that handles accounting is as follows:
> <Handler Request-Type=Accounting-Request>
>   RewriteUsername s/^([^@]+).*/$1/
>
>   # Hook for using correct termination field and some logging
>   PreAuthHook file:"/etc/raddb/accounting.hook"
>
>   <AuthBy GROUP>
>     AuthByPolicy ContinueUntilIgnore
>     <AuthBy SQL>
>
>       DBSource    dbi:mysql:localdialup
>       DBUsername  %{GlobalVar:DbUser}
>       DBAuth      %{GlobalVar:DbPass}
>
>       AccountingTable dialupusage
>       AccountingStopsOnly
>       Timeout 5
>       FailureBackoffTime 20
>       AcctColumnDef username, %U, formatted
>       AcctColumnDef session_id, %{Acct-Session-Id}%m-%d, formatted
>       AcctColumnDef router_ip, %c, formatted
>       AcctColumnDef date, %f-%g-%i %j:%k:%p, formatted
>       AcctColumnDef session_time, %{Acct-Session-Time}, formatted
>       AcctColumnDef ip_address, %{Framed-IP-Address}, formatted
>       AcctColumnDef phone, %{Calling-Station-Id}, formatted
>       AcctColumnDef terminate_cause, %{Term-Cause}, formatted
>     </AuthBy>
>     <AuthBy SQL>
>       DBSource    %{GlobalVar:DbServer}
>       DBUsername  %{GlobalVar:DbUser}
>       DBAuth      %{GlobalVar:DbPass}
>       AccountingStopsOnly
>       Timeout 5
>       FailureBackoffTime 20
>
>       AcctSQLStatement update users set
> prepaid_timeleft=prepaid_timeleft-0%{Acct-Session-Time} where
> (prepay='true')&&(username='%U')
>
>       AcctSQLStatement delete from online where
> (((nasidentifier='%c')&&(nasport='%{NAS- 
> Port}'))||((username='%n')&&(callingid='%{Calling-Station-Id}')))
>
>     </AuthBy> # SQL
>   </AuthBy> # Group
>   AccountingHandled
> </Handler>
>
>
>
> Griff Hamlin, III
> Quik Internet
>
> ===
> Archive at http://www.open.com.au/archives/radiator/
> Announcements on radiator-announce at open.com.au
> To unsubscribe, email 'majordomo at open.com.au' with
> 'unsubscribe radiator' in the body of the message.
>
>

NB: I am travelling this week, so there may be delays in our  
correspondence.

-- 
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.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.