You are viewing a plain text version of this content. The canonical link for it is here.
Posted to log4net-user@logging.apache.org by Simon Wallis <ma...@wallis.ca> on 2006/02/02 16:31:29 UTC
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:Greg.Ismay@comalco.riotinto.com.au]
> Sent: 10 January 2005 04:15
> To: log4net-user@logging.apache.org
> 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
> >
>
RE: Database Appender
Posted by Ron Grabowski <ro...@yahoo.com>.
I think a more generalized approach would be to mimic NLog's
RetryTargetWrapper wrapper to allow any Appender to be retried:
<appender name="RetryAdoNetAppender" type="RetryAppender">
<retryCount value="3" />
<retryMillisecondDelay value="100" />
<appender-ref ref="AdoNetAppender" />
</appender>
--- Georg Jansen <Ge...@FaktNet.com> wrote:
>
>
>
>
> 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
>
> {
>
=== message truncated ===
RE: Database Appender
Posted by Georg Jansen <Ge...@FaktNet.com>.
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:Georg.Jansen@FaktNet.com]
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:mailing@wallis.ca]
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:Greg.Ismay@comalco.riotinto.com.au]
> Sent: 10 January 2005 04:15
> To: log4net-user@logging.apache.org
> 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
> >
>
RE: Database Appender
Posted by Georg Jansen <Ge...@FaktNet.com>.
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:mailing@wallis.ca]
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:Greg.Ismay@comalco.riotinto.com.au]
> Sent: 10 January 2005 04:15
> To: log4net-user@logging.apache.org
> 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
> >
>