Re: mysql pool problem (again)

Dennis Bekkering skrev:
> A validation query doesn't help in the MySQL scenario: The driver will
> automatically reconnect and successfully execute the query, but the
> Connection still has a chance of remaining in an inconsistent state. This
> won't happen on every reconnect, just on some.

You are perfectly right that the MySQL JDBC-driver's auto re-connect
feature conflicts with the Java connection pool settings,
creating this inconsistency. However, I don't agree that a validation
query does not help - you should be able to turn off the MySQL-specific
feature in the JDBC driver and let the OJB or DBPC connection pool
implementations handle re-connect via validation query
(and validation query only).

To turn off the MySQL re-connect feature:
1. make sure the JDBC URL does *not* include ?autoReconnect=true
(the default value is false = off)

2. make sure that the OJB connection pool settings does *not*
include the JDBC connection property:
<attribute attribute-name="jdbc.autoReconnect" attribute-value="true"/>

To make sure that MySQL auto re-connect is off, you can add an
explicit "false" setting in the OJB connection-pool configuration
(but do make sure to clean the JDBC URL according to #1 above):
<connection-pool ... testOnBorrow="true" ... validationQuery="...">
...
  <attribute
     attribute-name="jdbc.autoReconnect" attribute-value="false"/>
...
</connection-pool>
(Although the default setting has been false since MySQL JDBC v1.1)

Wouldn't this help in your scenario?

See also:
*) MySQL documentation:
Driver/Datasource Class Names, URL Syntax and Configuration Properties 
for Connector/J
http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

*) OJB documentation:
Repository File, connection-pool, Custom attributes, jdbc.*
http://db.apache.org/ojb/docu/guides/repository.html#jdbc.*

Regards,
  Martin