-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: MySql connections timeout
PostPosted: Fri Jan 16, 2009 6:09 pm 
Newbie

Joined: Wed Jul 30, 2008 5:50 am
Posts: 15
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


Top
 Profile  
 
 Post subject: Re: MySql connections timeout
PostPosted: Fri Jan 16, 2009 6:13 pm 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
how do you know the first one fails? Are you sure you are not looking into the connection pool's log?



Farzad-


Top
 Profile  
 
 Post subject: Here is what I do....
PostPosted: Sat Jan 17, 2009 11:51 am 
Newbie

Joined: Wed Jul 30, 2008 5:50 am
Posts: 15
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


Top
 Profile  
 
 Post subject: The log
PostPosted: Sat Jan 17, 2009 12:10 pm 
Newbie

Joined: Wed Jul 30, 2008 5:50 am
Posts: 15
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


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 02, 2009 3:30 pm 
Newbie

Joined: Mon Feb 02, 2009 3:28 pm
Posts: 1
afishler, did you ever figure out how to fix the problem?


Top
 Profile  
 
 Post subject: Yep...
PostPosted: Mon Feb 02, 2009 3:44 pm 
Newbie

Joined: Wed Jul 30, 2008 5:50 am
Posts: 15
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.