(RADIATOR) Radiator Dropping DB Connections
Andrew Stevenson
andrews at ntt.net.au
Mon Jul 14 10:40:30 CDT 2003
Hi,
I am running Radiator 3.6 under FreeBSD with DBI 1.37, DBD::Sybase 1.00,
perl 5.8 talking to MS SQL 2000. The problem I am having is connections
to the DB are dropped regularly (although restablished quickly).
This happens whenever radiator needs to speak to the database but as an
example when an interim update packet arrives radiator updates the
session database (with a delete and insert) and then insert a record in
the accounting database.
According to the Radiator log file the first delete sometimes succeeds but
usually fails and the first insert always fails. Looking in the DB and at
the DB logs, I can see that both SQL statements actually succeed.
Radiator then reports the last insert, the insert into the accounting
table, fails also. Again it actually succeeds. Similar things happen with
SELECTs.
During this process radiator connects to the DB 5 times in rapid
sucession, seemingly not even trying to use some connections after login.
After running these statements one connection is still active but the
others are in TIME_WAIT (the FIN comes from the box running radiator).
DBI also prints error messages to STDERR ("DBI handle cleared whilst still
active at /usr/local/lib/perl5/site_perl/5.005/Radius/Util.pm line 531").
I am guessing that the DBI error messages are caused by the dropping of
the DB connections. I am also guessing that the dropping of connections is
caused by Radiator thinking its SQL statements failed. I have no idea why
Radiator thinks the SQL statements fail.
Mind you, when I go through Radiator with the debugger it seems that the
errors are being coming when DBD::Sybase::db::do returns...perhaps
something is going out of scope, perl is GCing it and killing off the DB
connections?
No reason is logged for the "failure" as $reason eq ''. e.g.
Mon Jul 14 23:55:31 2003: ERR: do failed for 'DELETE FROM Radius..acc
WHERE nas_ip = 'x.x.x.x' AND port = 'x'':
Note that there is nothing after the :
There are no other errors apart from the "do failed for" in the logfile,
even at trace level 4. I have included my config file below.
Does anyone have any ideas? Things I can try? I've tried radiator 2.17.1
and it has the same problems. I can speak to the DB fine from the box
using sqsh or even other perl scripts.
Any help would be greatfully received.
Thanks,
Andrew
# Radiator config file for XXXX
# set the logging level (2 is NOTICE, 3 is INFO, 4 is DEBUG)
Trace 4
# run radiator in the foreground (so the wrapper script can detect an exit)
Foreground
# set the value of %L
LogDir /var/log/radius/XXXXX
# where to put the log file
LogFile %L/logfile-%Y%m%d
# port to listen for authentication requests on
AuthPort XXXX
# port to listen for accounting records on
AcctPort XXXX
# set the value of %D
DbDir /usr/local/etc/radiator/raddb
# where to find the radius dictionary
DictionaryFile %D/dictionary
# path to pid file
PidFile /var/run/radiator-XXXX.pid
# user to setuid to so we don't have to run as root
User radius
Group radius
# path to snmpget
SnmpgetProg /usr/local/bin/snmpget
# perl code to run on startup/restart (HUP) - don't include comments
StartupHook sub { \
$ENV{SYBASE} = '/usr/local/etc'; \
umask(022); \
}
# strip realms from usernames before consulting XXXX
RewriteUsername s/XXXXX/XXXXX/
RewriteUsername s/XXXXX/XXXXX/
# Client entries for each NAS
<Client X.X.X.X>
# shared secret
Secret XXXXXX
# type of NAS (Cisco using L2TP tunnels)
NasType CiscoVPDN
# SNMP community string
SNMPCommunity XXXXXX
</Client>
# handler for authentication requests
<Handler Request-Type=Access-Request>
# how to determine if we should let this user in
<AuthBy SQL>
# where to find the DB (tried in order)
DBSource dbi:Sybase:server=XXXXX;database=Radius
DBUsername XXXXXX
DBAuth XXXXXX
DBSource dbi:Sybase:server=XXXXXX;database=Radius
DBUsername XXXXXX
DBAuth XXXXXX
# don't look for a deafult user entry (there isn't one to find)
NoDefault
# the SQL query to find the user's details
AuthSelect \
SELECT \
password, \
max_sessions, \
attributes, \
attributes2 \
FROM Radius..users WHERE \
username = '%n'
# what to do with the details we have found
AuthColumnDef 0, User-Password, check
AuthColumnDef 1, Simultaneous-Use, check
AuthColumnDef 2, GENERIC, reply
AuthColumnDef 3, GENERIC, reply
</AuthBy>
</Handler>
# handler for start records
<Handler Acct-Status-Type=Start>
# we don't care about start accounting records but we need to
# acknowledge them or the NAS will keep sending them
# there are many other ways to get this effect but I think this is the
# most obvious
</Handler>
# handler for keep-alive records
<Handler Acct-Status-Type=Alive>
# add in a start time (as we don't record start records)
PreAuthHook file:"%D/addstarttime.pl"
# record accounting info in file in case IS loose it
AcctLogFileName %L/details-%Y%m%d
# how to record this record in the DB (NB this is a badly named
# directive)
<AuthBy SQL>
# where to find the DB (tried in order)
DBSource dbi:Sybase:server=XXXXXX;database=Radius
DBUsername XXXXXX
DBAuth XXXXXX
DBSource dbi:Sybase:server=XXXXXXX;database=Radius
DBUsername XXXXXX
DBAuth XXXXXX
# file to write accounting requests that couldn't be inserted
# in the DB (perhaps due to DB failure)
AcctFailedLogFileName %L/missed-%Y%m%d
# put the user in XXXXXX
AcctSQLStatement \
INSERT INTO Radius..acc_history ( \
username, \
session_id, \
nas_ip, \
port, \
start, \
stop, \
session_time, \
in_octets, \
out_octets, \
client_ip, \
station_id, \
status, \
record_type, \
stop_epoch_seconds \
) VALUES ( \
'%n', \
'%{Acct-Session-Id}', \
'%N', \
'%{NAS-Port}', \
'%{Pseudo-Start-Time}', \
'%g/%i/%f %j:%k', \
'%{Acct-Session-Time}', \
'%{Acct-Input-Octets}', \
'%{Acct-Output-Octets}', \
'%{Framed-IP-Address}', \
'%{Calling-Station-Id}', \
'ACT', \
'1', \
'%{Timestamp}' \
)
</AuthBy>
</Handler>
# handler for stop records
<Handler Acct-Status-Type=Stop>
# add in a start time (as we don't record start records)
PreAuthHook file:"%D/addstarttime.pl"
# record accounting info in file in case XXXXX loose it
AcctLogFileName %L/details-%Y%m%d
# how to record this record in the DB (NB this is a badly named
# directive)
<AuthBy SQL>
# where to find the DB (tried in order)
DBSource dbi:Sybase:server=XXXXXX;database=Radius
DBUsername XXXXXX
DBAuth XXXXXX
DBSource dbi:Sybase:server=XXXXXX;database=Radius
DBUsername XXXXXX
DBAuth XXXXXX
# file to write accounting requests that couldn't be inserted
# in the DB (perhaps due to DB failure)
AcctFailedLogFileName %L/missed-%Y%m%d
# put the user in acc_history so they can be billed
AcctSQLStatement \
INSERT INTO Radius..acc_history ( \
username, \
session_id, \
nas_ip, \
port, \
start, \
stop, \
session_time, \
in_octets, \
out_octets, \
client_ip, \
station_id, \
status, \
record_type, \
stop_epoch_seconds \
) VALUES ( \
'%n', \
'%{Acct-Session-Id}', \
'%N', \
'%{NAS-Port}', \
'%{Pseudo-Start-Time}', \
'%g/%i/%f %j:%k', \
'%{Acct-Session-Time}', \
'%{Acct-Input-Octets}', \
'%{Acct-Output-Octets}', \
'%{Framed-IP-Address}', \
'%{Calling-Station-Id}', \
'COM', \
'0', \
'%{Timestamp}' \
)
</AuthBy>
</Handler>
# where to keep the session database (used to track who is logged in so
# simultaneous usage limits can be enforced). For some reason the AddQuery
# statement gets executed for each keep-alive packet
<SessionDatabase SQL>
# where to find the DB (tried in order)
DBSource dbi:Sybase:server=XXXXXX;database=Radius
DBUsername XXXXXX
DBAuth XXXXXX
DBSource dbi:Sybase:server=XXXXXX;database=Radius
DBUsername XXXXXX
DBAuth XXXXXX
AddQuery \
INSERT INTO Radius..acc ( \
username, \
session_id, \
nas_ip, \
port, \
start, \
stop, \
session_time, \
in_octets, \
out_octets, \
client_ip, \
station_id, \
status \
) VALUES ( \
'%n', \
'%{Acct-Session-Id}', \
'%N', \
'%{NAS-Port}', \
'%g/%i/%f %j:%k', \
'%g/%i/%f %j:%k', \
0, \
0, \
0, \
'%{Framed-IP-Address}', \
'00000000', \
'ACT' \
)
DeleteQuery \
DELETE FROM Radius..acc WHERE \
nas_ip = '%N' AND \
port = '%{NAS-Port}'
ClearNasQuery \
DELETE FROM Radius..acc WHERE \
nas_ip = '%N'
CountQuery \
SELECT \
nas_ip, \
port, \
session_id \
FROM Radius..acc WHERE \
username = '%n'
</SessionDatabase>
===
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.
More information about the radiator
mailing list