Hi Georg (and others),
Thanks a lot for that code snippet. I used it as a starting point and made a
few modifications. Instead of constantly trying to reconnect on each logging
call (which would block the calling application), I added a new parameter
"retryDelaySeconds". This will wait X number of seconds before trying the next
reconnect. Any logging attempts within this window will be ignored. I've pasted
my SendBuffer() function and a DatabaseReconnect() helper. I'd appreciate
anyone taking a look and letting me know what they think.
Thanks,
Simon.
PS. Sorry for the multiple return statements :0
/// <summary>
/// Inserts the events into the database.
/// </summary>
/// <param name="events">The events to insert into the
database.</param>
override protected void SendBuffer(LoggingEvent[] events)
{
//if the delay to retry reconnect has not passed, then
quit function. All events will be lost
if (m_blnReconnectOnError &&
(m_dtLastConnectFailure.AddSeconds(m_intRetryDelaySeconds) > DateTime.Now))
{
ErrorHandler.Error("AdoNetRetryAppender: Not
sending buffer. Delay of " + m_intRetryDelaySeconds + " secs has not elapsed
since last error at " + m_dtLastConnectFailure);
return;
}
//first see if we need to open database connection
(note: this one check alone is not good enough, since the connection state is
not always updated upon failure)
if (m_blnReconnectOnError && (m_dbConnection == null ||
m_dbConnection.State != ConnectionState.Open) && !DatabaseReconnect())
{
return; //if reconnect fails then quit
}
bool blnSuccess = false;
int intSendAttempts = (m_blnReconnectOnError) ? 2 : 1 ;
//if we should reconnect on error (and haven't already tried), then make two
attempts. Otherwise only try once.
for (int intAttemptNum=1;
intAttemptNum<=intSendAttempts && !blnSuccess; intAttemptNum++)
{
//the first attempt to send buffer failed, so
force reconnect
if (intAttemptNum>1 && !DatabaseReconnect())
{
return; //if reconnect fails then quit
}
// Check that the connection exists and is open
if (m_dbConnection != null &&
m_dbConnection.State == ConnectionState.Open)
{
if (m_useTransactions)
{
// Create transaction
// NJC - Do this on 2 lines
because it can confuse the debugger
IDbTransaction dbTran = null;
try
{
dbTran =
m_dbConnection.BeginTransaction();
SendBuffer(dbTran,
events);
// commit transaction
dbTran.Commit();
blnSuccess = true;
}
catch(Exception ex)
{
// rollback the
transaction
if (dbTran!=null)
{
try
{
dbTran.Rollback();
}
catch(Exception)
{
//Ignore exception
}
}
// Can't insert into
the database. That's a bad thing
ErrorHandler.Error("Exception while writing to database", ex);
}
}
else
{
// Send without transaction
SendBuffer(null, events);
blnSuccess = true;
}
}//if connection open
}//for
}//SendBuffer()
/// <summary>
/// Attempts to reconnect to the logging database
/// </summary>
/// <returns>true if reconnect worked, false otherwise</returns>
private bool DatabaseReconnect()
{
bool blnReconnected = true;
ErrorHandler.Error("AdoNetRetryAppender: Attempting to
reconnect to database");
InitializeDatabaseConnection();
InitializeDatabaseCommand();
//check if reconnect worked
if (m_dbConnection == null || m_dbConnection.State !=
ConnectionState.Open)
{
ErrorHandler.Error("AdoNetRetryAppender:
Reconnect to database failed.");
m_dtLastConnectFailure = DateTime.Now;
//record failure
blnReconnected = false;
}
return blnReconnected;
}//DatabaseReconnect()
-----Original message-----
From: "Georg Jansen" [EMAIL PROTECTED]
Date: Thu, 02 Feb 2006 18:17:43 -0800
To: "'Log4NET User'" [email protected]
Subject: RE: Database Appender
>
>
>
>
> I did some modifications to the SendBuffer method of AdoNetAppender, and it
> seems to work. I tested it -- starting and stopping SQLServer between log
> calls.
>
> The modified source is based on the 1.2.9 source where the config parameter
> reconnectonerror is available.
>
>
>
> I have kept the original "retry code" but it doesn't seems like it is
> working, the m_dbConnection.State is not (at least during my test) updated
> if a connection is dropped from the server.
>
>
>
>
>
> Regards
>
> Georg
>
> http://www.l4ndash.com <http://www.l4ndash.com/> Log4Net Dashboard /
> Log4Net Viewer
>
>
>
>
>
>
>
> override protected void SendBuffer(LoggingEvent[] events)
>
> {
>
> if (m_reconnectOnError && (m_dbConnection == null || m_dbConnection.State
> != ConnectionState.Open))
>
> {
>
> LogLog.Debug("AdoNetAppender: Attempting to reconnect to database.
> Current Connection State: " +
> ((m_dbConnection==null)?"<null>":m_dbConnection.State.ToString()) );
>
>
>
> InitializeDatabaseConnection();
>
> InitializeDatabaseCommand();
>
> }
>
>
>
> int NoOfReTry;
>
> bool Sucess = false;
>
>
>
> if (m_reconnectOnError)
>
> {
>
> NoOfReTry = 2;
>
> }
>
> else
>
> {
>
> NoOfReTry = 1;
>
>
>
> }
>
>
>
> for (int iTryNo = 1; iTryNo <= NoOfReTry && Sucess == false; iTryNo++)
>
> {
>
> try
>
> {
>
> if (iTryNo > 1) // second time? --> try to reconnect
>
> {
>
> while (iTryNo <= NoOfReTry)
>
> {
>
> LogLog.Debug("AdoNetAppender: Attempting to reconnect to
> database");
>
>
>
> InitializeDatabaseConnection();
>
> if (m_dbConnection != null && m_dbConnection.State ==
> ConnectionState.Open)
>
> break;
>
>
>
> iTryNo++;
>
> }
>
>
>
> if (m_dbConnection == null || m_dbConnection.State !=
> ConnectionState.Open)
>
> {
>
> LogLog.Error("Giving up database reconect after trying:
> " + NoOfReTry.ToString() + " times");
>
> return;
>
> }
>
>
>
> InitializeDatabaseCommand();
>
> }
>
>
>
> // Check that the connection exists and is open
>
> if (m_dbConnection != null && m_dbConnection.State ==
> ConnectionState.Open)
>
> {
>
> if (m_useTransactions)
>
> {
>
> // Create transaction
>
> // NJC - Do this on 2 lines because it can confuse the
> debugger
>
> IDbTransaction dbTran = null;
>
> try
>
> {
>
> dbTran = m_dbConnection.BeginTransaction();
>
>
>
> SendBuffer(dbTran, events);
>
>
>
> // commit transaction
>
> dbTran.Commit();
>
> Sucess = true;
>
> }
>
> catch (Exception ex)
>
> {
>
> // rollback the transaction
>
> if (dbTran != null)
>
> {
>
> try
>
> {
>
> dbTran.Rollback();
>
> }
>
> catch (Exception)
>
> {
>
> // Ignore exception
>
> }
>
> }
>
>
>
> // Can't insert into the database. That's a bad
> thing
>
> ErrorHandler.Error("Exception while writing to
> database", ex);
>
> }
>
> }
>
> else
>
> {
>
> // Send without transaction
>
> SendBuffer(null, events);
>
> Sucess = true;
>
> }
>
> }
>
> }
>
>
>
> catch (Exception ex)
>
> {
>
> Sucess = false;
>
> }
>
> }
>
> }
>
>
>
>
>
>
>
> _____
>
> From: Georg Jansen [mailto:[EMAIL PROTECTED]
> Sent: 2. februar 2006 17:22
> To: 'Log4NET User'
> Subject: RE: Database Appender
>
>
>
>
>
> Simon,
>
>
>
> I believe it was done some work on that in 1.2.9 (you are using an earlier
> version, right?)
>
>
>
> I checked the source code for the AdoNetAppender, and in the SendBuffer it
> tries to do a reconnect (if the parameter reconnectonerror is stated in the
> config).
>
>
>
> override protected void SendBuffer(LoggingEvent[] events)
>
> {
>
> if (m_reconnectOnError && (m_dbConnection == null ||
>
> m_dbConnection.State != ConnectionState.Open))
>
> {
>
> LogLog.Debug("AdoNetAppender: Attempting to reconnect to database.
>
> Current Connection State: " +
>
> ((m_dbConnection==null)?"<null>":m_dbConnection.State.ToString()) );
>
> InitializeDatabaseConnection();
>
> InitializeDatabaseCommand();
>
> }
>
>
>
>
>
> Problem is that when I tested this (I restarted SQL server between two log
> statements), I still gets an exception stating a "transport layer error".
>
>
>
> I agree with you, Simon this is a problem with applications running for a
> long time for example web applications. A simple solution would be to modify
> the adonetappender and try to do a reconnect whenever the sql statement
> fails (I will give it a try).
>
>
>
>
>
> Regards
>
> Georg
>
> http://www.l4ndash.com <http://www.l4ndash.com/> Log4Net Dashboard /
> Log4Net Viewer
>
>
>
>
>
> -----Original Message-----
> From: Simon Wallis [mailto:[EMAIL PROTECTED]
> Sent: 2. februar 2006 16:31
> To: Log4NET User
> Subject: RE: Database Appender
>
>
>
> Hi,
>
>
>
> Has anyone come up with any creative ways to solve this problem of the
> database connection hanging? The only thing I can think of is to have a
> process that "touches" the config file on an hourly basis. Otherwise we have
> to remember to go around doing this manually on all the servers whenever the
> database is rebooted or there's a temporary network failure.
>
>
>
> I realize it would be a lot of work to add retry or failure semantics to the
> appender, but whenever this is implemented, likely the most flexible
> approach would be to provide a number of options via the config file.
>
>
>
> Simon.
>
>
>
>
>
> -----Original Message-----
>
> Subject: RE: Database Appender
>
> From: "Nicko Cadell" <nicko () neoworks ! com>
>
> Date: 2005-01-10 13:59:27
>
> Message-ID: DDEB64C8619AC64DBC074208B046611C59C838 () kronos ! neoworks ! co
> ! uk
>
> [Download message RAW]
>
>
>
> Greg,
>
>
>
> The AdoNetAppender does not reopen the connection if there is a failure.
>
> The database connection is only opened during appender initialisation.
>
> Error handling is a general issue for appenders but for the
>
> AdoNetAppender there are a number of options.
>
>
>
> The current behaviour is for the appender to stop trying to write to the
>
> database if the connection is closed. An alternative is for the appender
>
> to try to reconnect to the database. The issues with reconnecting is
>
> when to try and how many times. The appender could try to reconnect when
>
> the buffered events are sent. If it fails to contact the server then the
>
> connection will time out after some time. This is a synchronous call and
>
> therefore any logging call in your application could randomly incur this
>
> connection timeout penalty at any time. Is that acceptable behaviour for
>
> the appender? If the appender cannot reconnect should it discard the
>
> events that it cannot deliver? or should it store the events and
>
> redeliver them when/if it does reconnect, if so how many event should it
>
> store?
>
>
>
> There are no easy solutions to these issues, and different behaviours
>
> will be appropriate for different situations. The current AdoNetAppender
>
> implementation does not attempt to support any retry or failure
>
> semantics. This is one area where we will need to do some work in
>
> future. If you have any suggestions we would be happy to discuss them.
>
>
>
> Nicko
>
>
>
> > -----Original Message-----
>
> > From: Ismay, Greg (CALBRIS)
>
> > [mailto:[EMAIL PROTECTED]
>
> > Sent: 10 January 2005 04:15
>
> > To: [email protected]
>
> > Subject: Database Appender
>
> >
>
> >
>
> > > howdy everyone...
>
> > >
>
> > > im using the database ADONetAppender to log to a sql server
>
> > database and its all going peachy keen... for a while...
>
> > >
>
> > > ive got the same app installed at 4 of our sites, with the
>
> > appender pointing back to a central database accessible over
>
> > the wan (i will probably end up using a local db at each
>
> > site, but a central db was my going in position, with a "lets
>
> > see how it goes before we decentralise" mentality). the wans
>
> > pretty fast and im only doing production logging (ie only
>
> > errors and fatals) this way.
>
> > >
>
> > > basically, it works for a few days and sometimes up to a
>
> > week or so, before the logging just stops.
>
> > >
>
> > > forcing the loggers to reinitialised (ie by "touch"ing the
>
> > log4net.config file) makes it all good again...
>
> > >
>
> > > my question is this... are there any known problems with the db
>
> > > appender (or any appender) whereby a loss of connection (which can
>
> > > happen over the wan) over time could result in the above
>
> > state (eg...
>
> > > maybe running out of connections in the pool due to them gradually
>
> > > getting "stuck")
>
> > >
>
> > > ill troll through the code in a few minutes, but just
>
> > thought id ask first.
>
> > >
>
> > > thanks,
>
> > >
>
> > > Greg Ismay
>
> > >
>
> > > Specialist - Database Support (Automation Improvement) Comalco
>
> > > Aluminium Limited
>
> > >
>
> > > Phone : +61 7 3867 1853
>
> > > Fax : +61 7 3867 1855
>
> > > Mobile : +61 4 1760 6429
>
> > >
>
> >
>
>
