[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-184) Connection Timeout Exception using MySQL and DBCP

[<leg...@at...>]

The following comment has been added to this issue:

     Author: Maarten Coene
    Created: Wed, 27 Aug 2003 2:00 AM
       Body:
Please reopen this issue ...

>(a) you can work around this using DBCP connection validation 

No, this won't work because DBCP tries to activate the Connection before it's being validated. But activating the Connection when autoCommit is false throws an Exception (only with MySQL and after 8 hours of inactivity) in the borrowObject() method of the pool which is propagated as an SQLException in the getConnection() method of the PoolingDataSource. 

>(b) calling setAutocommit() is expensive for some (most?) DBs 

I didn't notice any performance degradation of my application. Also, hibernate sets the autoCommit option in the C3P0ConnectionProvider almost the same way as I proposed in the patch. Why not doing it the same way for both the C3P0ConnectionProvider an the DBCPConnectionProvider?

>(c) this should really be addressed by patching the MySQL driver, NOT Hibernate! 

I agree and these patches are already submitted to MySQL (not by me, but by others). But in the meantime I cannot use the "official" hibernate builds because of this issue which can be solved easily. 

Maarten Coene
---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-184


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-184
    Summary: Connection Timeout Exception using MySQL and DBCP
       Type: Patch

     Status: Closed
   Priority: Major
 Resolution: REJECTED

    Project: Hibernate2

   Assignee: 
   Reporter: Maarten Coene

    Created: Fri, 11 Jul 2003 9:21 AM
    Updated: Tue, 26 Aug 2003 11:51 PM
Environment: Linux

Description:
Hi,

The MySQL database closes by default it connections after 8 hours. If these connections are pooled, a "Connetion Timeout" exception will be thrown if you try to access the connection. The best way to solve this is to add ?autoReconnect=true to the jdbc url.

But this doesn't work with Hibernate and DBCP.

The reason is that this option only works if the autoCommit attribute is set to true and the DBCPConnectionProvider of Hibernate sets this to false by default. I've created a patch to fix this problem.

thx
Maarten Coene



Index: DBCPConnectionProvider.java
===================================================================
RCS file: /cvsroot/hibernate/Hibernate2/src/net/sf/hibernate/connection/DBCPConnectionProvider.java,v
retrieving revision 1.8
diff -u -r1.8 DBCPConnectionProvider.java
--- DBCPConnectionProvider.java	25 Apr 2003 03:40:31 -0000	1.8
+++ DBCPConnectionProvider.java	11 Jul 2003 14:12:09 -0000
@@ -42,6 +42,7 @@
 		try {
 			final Connection c = ds.getConnection();
 			if (isolation!=null) c.setTransactionIsolation( isolation.intValue() );
+                        c.setAutoCommit(false);
 			return c;
 		}
 		catch (SQLException sqle) {
@@ -56,6 +57,7 @@
 	
 	public void closeConnection(Connection conn) throws SQLException {
 		try {
+                        conn.setAutoCommit(true);
 			conn.close();
 		}
 		catch (SQLException sqle) {
@@ -119,7 +121,7 @@
 			// the "real" Connections created by the ConnectionFactory with
 			// the classes that implement the pooling functionality.
 			String validationQuery = props.getProperty(Environment.DBCP_VALIDATION_QUERY);
-			new PoolableConnectionFactory(connectionFactory, connectionPool, statementPool, validationQuery, false, false);
+			new PoolableConnectionFactory(connectionFactory, connectionPool, statementPool, validationQuery, false, true);
 			
 			// Finally, we create the PoolingDriver itself,
 			// passing in the object pool we created.



---------------------------------------------------------------------
JIRA INFORMATION:
This message is automatically generated by JIRA.

If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira