Hi Dan,
OK,
here is a new version of SqlDb.pm that implements a new DisconnectAfterQuery
flag. This will cause AuthBy SQL and other SQL users to disconnect after
every SQL 'do' and after every 'getOneRow'.
Let me know how you go.
Cheers.
On Fri, 30 Aug 2002 05:11, Dan Melomedman wrote:
> Mike McCauley wrote:
> > On Wed, 28 Aug 2002 08:32, Hugh Irvine wrote:
> > > Hello Dan -
> > >
> > > I would have to suggest that you use a more sensible database.
> >
> > Of course that there might be other reasons that prevent you from doing
> > that.
> >
> > I am a bit puzzled though: I would normally expect Radiator to attempt to
> > reconnect and have another go after failing to execute that query the
> > first time?
> >
> > Perhaps if you send more the the trace file we might see if that is
> > happening?
> >
> > Cheers.
>
> Here's what happens:
>
> 1) If the server has been writing to MSSQL server frequently, no problems.
> 2) If the server has not written anything over TCP connection to MSSQL
> in quite a long while, the server is blocked. Any subsequent
> requests to the server fail. This is the last thing in the log before a
> block:
>
> Wed Aug 28 18:58:52 2002 707093: DEBUG: do query is: insert into
> failedattempts
> (LoggedAt,User_Name,NAS_IP_Address,Caller_ID,NAS_Port,Failure_Message,
> Active_Handler) values ('2002-08-28
> 18:58:52.000','dan','203.63.154.1','987654321','1234','''Bad
> Password''', 'prodnetilla') .
>
> I suspect a problem may be with FreeTDS libraries, DBD::Sybase, or MSSQL
> server itself. Unfortunately I can't use a different database for
> logging for beauraucratical reasons.
>
> A connect-log-disconnect feature would be a quick fix for this. It would
> also allow some people simple load balancing with round-robin DNS to
> boot.
--
Mike McCauley [EMAIL PROTECTED]
Open System Consultants Pty. Ltd Unix, Perl, Motif, C++, WWW
24 Bateman St Hampton, VIC 3188 Australia http://www.open.com.au
Phone +61 3 9598-0985 Fax +61 3 9598-0955
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, Active Directory etc etc
on Unix, Win95/8, 2000, NT, MacOS 9, MacOS X etc etc
# SqlDb.pm
#
# Object for handling an SQL database.
# Routines are provided to connect to a server (and fall back
# to alternates if not available.
# Also routines to do generic prepare/execute
#
# This module also implements database handle sharing: all instances
# that connect to the same database with the same username and password
# will share the same database connection
#
# Author: Mike McCauley ([EMAIL PROTECTED])
# Copyright (C) 1997 Open System Consultants
# $Id: SqlDb.pm,v 1.19 2002/05/23 02:02:44 mikem Exp mikem $
package Radius::SqlDb;
@ISA = qw(Radius::Configurable);
use Radius::Configurable;
use DBI;
use strict;
%Radius::SqlDb::ConfigKeywords =
('DBSource' => 'stringarray',
'DBUsername' => 'stringarray',
'DBAuth' => 'stringarray',
'Timeout' => 'integer',
'FailureBackoffTime' => 'integer',
'DateFormat' => 'string',
'DisconnectAfterQuery' => 'flag',
);
# This is a has of "$dbsource;$dbusername;$dbauth" to database handle
# that allows multuple instances to share handles
%Radius::SqlDb::handles;
#####################################################################
# Constructs a new SQL database
sub new
{
my ($class, @args) = @_;
my $self = $class->SUPER::new(@args);
$self->log($main::LOG_WARNING,
"No DBSource defined for $class at '$main::config_file' line $.")
if @{$self->{DBSource}} == 0;
return $self;
}
#####################################################################
# Do per-instance default initialization
# This is called by Configurable during Configurable::new before
# the config file is parsed. Its a good place initalze
# instance variables
# that might get overridden when the config file is parsed.
sub initialize
{
my ($self) = @_;
$self->SUPER::initialize;
# Empty arrays for database details
$self->{DBSource} = [];
$self->{DBUsername} = [];
$self->{DBAuth} = [];
$self->{Timeout} = 60; # Seconds
$self->{FailureBackoffTime} = 600; # Seconds
$self->{DateFormat} = '%b %e, %Y %H:%M'; # eg 'Sep 3, 1995 13:37'
}
#####################################################################
# reconnect
# Connect or reconnect to a database
# Returns true if there is a viable database connection available
sub reconnect
{
my ($self) = @_;
# Implement backoff strategy in case of database failure
return 0 if time < $self->{backoff_until};
if (!$Radius::SqlDb::handles{$self->{dbname}})
{
print "Reconnecting to $self->{dbname}\n";
# A new connection is required, try all the
# ones in the $self->{DBSource} in order til we
# find either an existing shared one, or a can create
# a new connection
my $i;
for ($i = 0; $i < @{$self->{DBSource}}; $i++)
{
my ($dbsource, $dbusername, $dbauth)
= ($self->{DBSource}[$i],
$self->{DBUsername}[$i],
$self->{DBAuth}[$i]);
$dbsource = &Radius::Util::format_special($dbsource);
$dbusername = &Radius::Util::format_special($dbusername);
$dbauth = &Radius::Util::format_special($dbauth);
$self->{dbname} = "$dbsource;$dbusername;$dbauth";
return 1
if $Radius::SqlDb::handles{$self->{dbname}};
# We evaluate the connection
# with an alarm for the timeout period
# pending. If the alarm goes off, the eval will die
&Radius::Util::exec_timeout($self->{Timeout},
sub {
$Radius::SqlDb::handles{$self->{dbname}}
= DBI->connect($dbsource,
$dbusername,
$dbauth);
});
if ($Radius::SqlDb::handles{$self->{dbname}})
{
$Radius::SqlDb::handles{$self->{dbname}}->{AutoCommit} = 1;
# This one stops DBD-Sybase finish causing hangs
# in MS-SQL, see Sybase.pm in DBD-Sybase.
$Radius::SqlDb::handles{$self->{dbname}}->{syb_flush_finish} = 1;
return 1; # Database is available
}
# If the connect failed, we get an exception, with
# the message in $@
$self->log($main::LOG_ERR, "Could not connect to SQL database with DBI->connect $dbsource, $dbusername, $dbauth: $@ $DBI::errstr");
}
$self->log($main::LOG_ERR, "Could not connect to any SQL database. Request is ignored. Backing off for $self->{FailureBackoffTime} seconds");
$self->{backoff_until} = time + $self->{FailureBackoffTime};
# Some SQL client libs (notably Oracle) break the sigchld
# handler, so we reestablish it here
$SIG{CHLD} = \&main::handle_sigchld if $^O ne 'MSWin32';
return 0; # Database is not available
}
return 1; # Database is still up
}
#####################################################################
# Turn a Unix epoch seconds count into an SQL format date
# of the format Sep 3, 1995 13:37
# Uses local time
# This is deprecated and will be removed one day soon
# use $self->formatDate() or
# Radius::Util::strftime($self->{DateFormat}) instead
sub formatSQLDate
{
return Radius::Util::strftime('%b %e, %Y %H:%M', $_[0]);
}
# Format a date according to DateFormat
sub formatDate
{
my ($self, $time) = @_;
return Radius::Util::strftime($self->{DateFormat}, $time);
}
#####################################################################
# Convenience function to prepare and execute a query.
# If it fails to execute, complain, and try to reconnect and reexecute.
# If it still fails to execute, return undef, else a statement handle
sub prepareAndExecute
{
my ($self, $q, @bind_values) = @_;
my ($attempts, $sth, $rc);
$self->log($main::LOG_DEBUG, "Query is: $q\n");
# Try to execute the query. If we fail due to database failure
# try to reconnect and try again. If that also fails, give up
while (!$sth && $attempts++ < 2)
{
if ($self->reconnect())
{
# We evaluate the execution
# with an alarm for the timeout period
# pending. If the alarm goes off, the eval will die
&Radius::Util::exec_timeout($self->{Timeout},
sub {
$sth = $Radius::SqlDb::handles{$self->{dbname}}->prepare($q);
$rc = $sth->execute(@bind_values) if $sth;
});
# Some DBD drivers dont undef rc on DB failure
return $sth if $sth && $rc && !$DBI::err;
# If we got here, something went wrong
my $reason = $DBI::errstr;
$reason = "SQL Timeout" if $@ && $@ =~ /timeout/;
$self->log($main::LOG_ERR, "Execute failed for '$q': $reason");
}
# Hmm, failed prob due to database failure, try to reconnect
# to an alternate
$self->disconnect();
$sth = undef;
}
return;
}
#####################################################################
# Convenience function to do a query.
# If it fails to execute, complain, and try to reconnect and reexecute.
sub do
{
my ($self, $q, @bind_values) = @_;
my ($attempts, $rc, $reason);
$self->log($main::LOG_DEBUG, "do query is: $q\n");
while (!defined($rc) && $attempts++ < 2)
{
if ($self->reconnect())
{
# We evaluate the execution
# with an alarm for the timeout period
# pending. If the alarm goes off, the eval will die
&Radius::Util::exec_timeout($self->{Timeout},
sub {
$rc = $Radius::SqlDb::handles{$self->{dbname}}->do
($q, @bind_values);
});
$self->disconnect() if $self->{DisconnectAfterQuery};
if (!$rc)
{
$reason = $DBI::errstr;
$reason = "SQL Timeout" if $@ && $@ =~ /timeout/;
$self->log($main::LOG_ERR,
"do failed for '$q': $reason");
}
# Primary key violation is not a cause for disconnection
return $rc if defined $rc
|| $reason =~ /violation/i
|| $reason =~ /Duplicate entry/im
|| $reason =~ /^ORA-00001/;
}
# there was an error (as opposed to no rows affected)
$self->disconnect();
}
return;
}
#####################################################################
# Get a single row from a previously prepared handle,
# with appropriate timeouts
sub getOneRow
{
my ($self, $sth) = @_;
my @row;
&Radius::Util::exec_timeout($self->{Timeout},
sub {
@row = $sth->fetchrow();
$sth->finish;
});
$self->disconnect() if $self->{DisconnectAfterQuery};
if ($@ && $@ =~ /timeout/)
{
# there was an error (as opposed to no rows affected)
$self->disconnect();
$self->log($main::LOG_ERR, "getOneRow timed out");
}
return @row;
}
#####################################################################
# Force disconnection from the current database
sub disconnect
{
my ($self) = @_;
exists $Radius::SqlDb::handles{$self->{dbname}}
&& defined $Radius::SqlDb::handles{$self->{dbname}}
&& $Radius::SqlDb::handles{$self->{dbname}}->disconnect;
delete $Radius::SqlDb::handles{$self->{dbname}};
}
#####################################################################
# Quote a string in a DB dependent way, but hide the dbh
# Ensures the database is connected first
sub quote
{
my ($self, $s) = @_;
return unless $self->reconnect();
return $Radius::SqlDb::handles{$self->{dbname}}->quote($s);
}
1;
