I am facing the well known issue of connection that timeout on mysql throwing a broken pipe exception or similar.

There are many threads on the subject, I know and I researched and followed many.
I have applied configurations suggested and currently the connection pool config is:
<prop key="hibernate.dbcp.maxIdle">5</prop>
<prop key="hibernate.dbcp.maxActive">50</prop>
<prop key="hibernate.dbcp.whenExhaustedAction">1</prop>
<prop key="hibernate.dbcp.maxWait">90000</prop>
<prop key="hibernate.dbcp.initialSize">1</prop>
<prop key="hibernate.dbcp.testOnBorrow">true</prop>
<prop key="hibernate.dbcp.testOnReturn">false</prop>
<prop key="hibernate.dbcp.testWhileIdle">true</prop>
<prop key="hibernate.dbcp.timeBetweenEvictionRunsMillis">10000</prop>
<prop key="hibernate.dbcp.minEvictableIdleTimeMillis">60000</prop>
<prop key="hibernate.dbcp.validationQuery">select 1</prop>
<prop key="hibernate.dbcp.ps.maxActive">10</prop>
<prop key="hibernate.dbcp.ps.maxIdle">10</prop>
<prop key="hibernate.dbcp.ps.maxWait">1000*60*30</prop>
<prop key="hibernate.dbcp.ps.whenExhaustedAction">1</prop>

STILL...I can say that the problem is almost solved but not completely. What happens now is that after the timeout priod on mysql has expired...on the first request I send to the server I get the exception on the broken pipe. On the following request the connector is probably refreshed and all works fine.

I still cannot figure out why is the FIRST request not checking for the validity of the connection.

Thanks,
Arie

how do you know the first one fails? Are you sure you are not looking into the connection pool's log?



Farzad-

I set the mysql timeout in the my.ini to be about 2 minutes.

I make an HTTP request to my application and get the page. I wait more than 2 minutes and submit another request...this is the one I call "first". First after the mysql is supposed to timeout the connections that were all idle.

At this point I get an exception that is typical to this problem on threads all over the web. for example java.net.SocketException: Software caused connection abort: recv failed.

I refresh the page and then I get it right. Before placing the above configuration....a refresh did not help and I got "stuck" with the bad connection. Now I at least get a new fresh connection on the request after the timeout...but naturally I want to avoid the initial exception as well.

Arie

What I can see in the log is the following sequence. I am actually not sure regarding what I need to see in order to assure the connection is actually checked with the validation statement "select 1". It is not really there in any way.

2009-01-17 18:05:45,656 [http-80-2] DEBUG org.hibernate.transaction.JDBCTransaction - begin
2009-01-17 18:05:45,656 [http-80-2] DEBUG org.hibernate.transaction.JDBCTransaction - begin
2009-01-17 18:05:45,656 [http-80-2] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection
2009-01-17 18:05:45,656 [http-80-2] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection
2009-01-17 18:05:45,656 [http-80-2] DEBUG org.hibernate.transaction.JDBCTransaction - current autocommit status: true
2009-01-17 18:05:45,656 [http-80-2] DEBUG org.hibernate.transaction.JDBCTransaction - current autocommit status: true
2009-01-17 18:05:45,656 [http-80-2] DEBUG org.hibernate.transaction.JDBCTransaction - disabling autocommit
2009-01-17 18:05:45,656 [http-80-2] DEBUG org.hibernate.transaction.JDBCTransaction - disabling autocommit
2009-01-17 18:05:45,656 [http-80-2] ERROR org.hibernate.transaction.JDBCTransaction - JDBC begin failed
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

and the exception

2009-01-17 18:05:45,656 [http-80-2] ERROR org.hibernate.transaction.JDBCTransaction - JDBC begin failed
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

afishler, did you ever figure out how to fix the problem?

As I mentioned this is a common problem but somehow something slipped in the configuration process.

First I suggest to set a testing environment. A local db running with a very short timeout to facilitate testing (in the mysql fonfig file wait_timeout=120).

Then enable debug logging for the specific connector pool you are using and check if the pool actually looks like it handles the exceptions from the db when the connection times out.

What I did is switch my pool to c3p0 and carefully follow configuration instructions. This was immediately successful and I saw that the pool catches exception from the connections and opens new ones.

Here is my configuration. Note that some of the properties are configured at the data source level other at the hibernate properties level.

<bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
<property name="driverClass" value="${db.driver}"/>
<property name="jdbcUrl" value="${db.url}"/>
<property name="user" value="${db.user}"/>
<property name="password" value="${db.pw}"/>
<property name="testConnectionOnCheckin" value="false"/>
<property name="testConnectionOnCheckout" value="true"/>
<property name="preferredTestQuery" value="select 1;"/>
</bean>

<!-- C3P0 Connector pool attributes -->
<prop key="hibernate.c3p0.acquire_increment">3</prop>
<prop key="hibernate.c3p0.idle_test_period">9000</prop>
<prop key="hibernate.c3p0.timeout">18000</prop>
<prop key="hibernate.c3p0.max_size">15</prop>
<prop key="hibernate.c3p0.min_size">2</prop>
<prop key="hibernate.c3p0.max_statements">5</prop>

Thats it. Hope it helps.