Return-Path: Delivered-To: apmail-logging-log4net-user-archive@www.apache.org Received: (qmail 44122 invoked from network); 8 Feb 2006 18:44:36 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 8 Feb 2006 18:44:36 -0000 Received: (qmail 33230 invoked by uid 500); 8 Feb 2006 18:44:30 -0000 Delivered-To: apmail-logging-log4net-user-archive@logging.apache.org Received: (qmail 33100 invoked by uid 500); 8 Feb 2006 18:44:29 -0000 Mailing-List: contact log4net-user-help@logging.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Log4NET User" List-Id: Delivered-To: mailing list log4net-user@logging.apache.org Received: (qmail 33048 invoked by uid 99); 8 Feb 2006 18:44:29 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 08 Feb 2006 10:44:29 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: neutral (asf.osuosl.org: local policy) Received: from [213.188.134.18] (HELO mailengine4.web2000.activeisp.com) (213.188.134.18) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 08 Feb 2006 10:44:27 -0800 Received: from XPGeorg (unverified [80.203.106.155]) by webmail.activeisp.com (Rockliffe SMTPRA 6.1.20) with ESMTP id for ; Wed, 8 Feb 2006 19:44:13 +0100 Message-ID: From: "Georg Jansen" To: "'Log4NET User'" Subject: RE: Database Appender Date: Wed, 8 Feb 2006 19:43:56 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 In-Reply-To: <1998318c08c1dbd9d3bbeb6d705b3a90@wallis.ca> thread-index: AcYs2V+Y8qV0d6uBSPaTipktrN00zAAA01xw X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Hi Simon, Great work! A useful extension (while you are on to it....:)) would be to add a failover connection string and try to connect using that connection string if the original connection strings fails. This would probably need a second configuration parameter as well, telling the appender how long time to wait before it switched to the failover connection string. Best regards Georg www.l4ndash.com - Log4Net Dashboard / Log4Net Viewer -----Original Message----- From: Simon Wallis [mailto:mailing@wallis.ca] Sent: 8. februar 2006 18:50 To: Log4NET User Subject: RE: Database Appender 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 /// /// Inserts the events into the database. /// /// The events to insert into the database. 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() /// /// Attempts to reconnect to the logging database /// /// true if reconnect worked, false otherwise 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" Georg.Jansen@FaktNet.com Date: Thu, 02 Feb 2006 18:17:43 -0800 To: "'Log4NET User'" log4net-user@logging.apache.org 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 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)?"":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)?"":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 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" > > 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 > > > > > > > > >