[Xorm-users] Re: UPDATE: RE: ADDENDUM: RE: Xorm, MySQL/J and autoReconnect

[Wes B. <we...@tr...>]

Chuck,

I'm copying your thread to the mailing list because I think this issue 
has been dealt with before (though it's possible some recent change has 
broken things).

Does this sound familiar, anyone?

Wes

Chuck Williams wrote:

> Wes,
>
> My saga continues. Unfortunately rollback at the level of the 
> InterfaceManager operations did not work because 
> TranscationImpl.begin() resets its state to no-transaction, which 
> generates an exception in TransactionImpl.rollback(). This seems 
> inconsistent with the semantics of BaseSQLDriver.begin(), which does 
> not reset its state if the driver operations fail (e.g., due to a 
> stale collection). So, this made me think the real fix should be in 
> BaseSQLDriver after all. However, there are subtle cases here 
> depending on where the error occurs (mine is occurring in the call to 
> setAutoCommit() in BaseSQLDriver.begin(), but errors could occur many 
> other places). The problem is clear – Xorm is leaving itself in an 
> inconsistent non-recoverable state if various errors occur in the 
> underlying driver, such as connections that have gone bad and probably 
> many others. However, I’m not sure of the right fix.
>
> I’m now trying to workaround my current problem by setting 
> org.xorm.datastore.sql.IdleCheckSQL to the same SQL that MySQL/J uses 
> for a ping (“SELECT 1”). This should stop bad connections from ever 
> being issued from the pool and hopefully will address the current 
> issue – however, I’m still concerned that Xorm is non-recoverably dead 
> after certain errors. And this is not a very efficient solution as it 
> adds an extra SQL operation to every transaction. The complete fix 
> would be for Xorm to capture errors in the driver, ensure its state is 
> consistent for future operations, and retry the operation for errors 
> that are retryable (communications errors, sql exception sate 08S01, 
> and deadlocks, sql exception state 41000). At least this is what 
> MySQL/J recommends. This would also be more efficient, as the pings 
> would not be necessary.
>
> Do you have any suggestions?
>
>
> Thanks,
>
>
> Chuck
>
> Chuck Williams
>
> V: (415)464-1889
>
> C: (415)846-9018
>
> /ch...@ma... <mailto:ch...@ma...>/
>
> AIM: hawimanawiz
>
> -----Original Message-----
> *From:* Chuck Williams
> *Sent:* Saturday, November 27, 2004 9:07 PM
> *To:* Chuck Williams; 'wb...@us...'
> *Subject:* ADDENDUM: RE: Xorm, MySQL/J and autoReconnect
>
> Wes,
>
> I’ve been looking at this further. If I’m understanding Xorm properly, 
> then I think there is a bug in the treatment of NontransactionalRead, 
> which would be easy for me to fix. There are three places where Xorm 
> creates a transaction automatically, all in InterfaceManager: 
> refreshColumns, selectRows and executeSizeQuery. Each of these creates 
> a transaction if NontransactionalRead’s are being used (which my 
> project uses). Each of these cases has a finally clause that commits 
> the transaction. However, if the commit fails due to an earlier error 
> in the processing, no rollback is done. If the error occurs in 
> BaseSQLDriver.begin (and probably in many other places as well), e.g. 
> due to a dead socket (my problem), the transaction is left in a bad 
> state. I don’t think it is right in general for BaseSQLDriver.begin to 
> clean up its state, as it may be important to exterior error handling 
> (is that right?) – also this would likely not be a complete fix if the 
> error occurred later in the transaciton. However, I think that the 
> users of BaseSQLDriver.begin should issue a rollback if an error 
> occurs, and it appears the rollback would restore the state properly.
>
> Does this seem right?
>
> Thanks again for any help,
>
> Chuck
>
> -----Original Message-----
> *From:* Chuck Williams
> *Sent:* Saturday, November 27, 2004 3:31 PM
> *To:* 'wb...@us...'
> *Subject:* Xorm, MySQL/J and autoReconnect
>
> Wes,
>
> I don't know if you are still involved with Xorm as your sourceforge 
> email address bounces now, but you helped me out with a question a few 
> months ago and I was wondering if you might have a suggestion for a 
> problem I just hit. If there is a better place to ask a question like 
> this, please let me know.
>
> I'm using Xorm beta 6 with MySQL/J jdbc driver to MySQL 4.1.7. After 8 
> hours of inactivity, MySQL/J closes the socket connection to the MySQL 
> server. We have autoReconnect=true, so the next attempted database 
> operation should automatically reconnect, but apparently this is not 
> the case for some calls. Xorm is calling Connection.setAutoCommit(), 
> which appears to require the caller to catch the generated 
> SQLException and then retry. Connection.setAutoCommit() sets a flag on 
> the first error, and then if this flag is set reopens the connection 
> on the next attempt. However, Xorm is not making a next attempt.
>
> To handle this error at the application level, we would seem to need 
> to wrap every accessor call in our app, which is quite painful and 
> would defeat the whole purpose of the Xorm/JDO abstraction.
>
>
> Do you have any suggestions?
>
> Thanks for any help,
>
> Chuck
>
> Chuck Williams
>
> V: (415)464-1889
>
> C: (415)846-9018
>
> /ch...@ma... <mailto:ch...@ma...>/
>
> AIM: hawimanawiz
>