Play com.mysql.jdbc.exceptions.jdbc4.CommunicationsException with setting Spring, Hibernate, and C3P0

Question

I got this error from production code:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet was successfully received from the server was36940 seconds ago. The last packet successfully sent to the server was 36940 seconds ago, which is more than the server configured value "wait_timeout". You should consider either expiring and / or checking the validity of the connection before using it in your application, increasing the configured server value for client timeouts, or using the Connector / J connection property 'autoReconnect = true' to avoid this problem.

And now I'm trying to reproduce the problem locally and fix it. I am setting up the spring context as follows:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" p:driverClass="com.mysql.jdbc.Driver" p:jdbcUrl="jdbc:mysql://localhost:3306/test?userUnicode=yes&amp;characterEncoding=UTF-8&amp" p:idleConnectionTestPeriod="120" p:initialPoolSize="1" p:maxIdleTime="1800" p:maxPoolSize="1" p:minPoolSize="1" p:checkoutTimeout="1000" /> <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <property name="hibernateProperties"> <value> hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider hibernate.dialect=org.hibernate.dialect.MySQL5Dialect hibernate.default_schema=platform_server_original hibernate.show_sql=false </value> </property> <property name="mappingResources"> <list> <value>sometables.hbm.xml</value> </list> </property> </bean> 

Then I set mysql wait_timeout for 10 seconds, then run my test, which basically opens the connection, executes the request, closes it, so it goes back to the pool, then smooths the stream for 15 seconds, and then opens the connection again, and retry the request. so that it breaks. However, I received only the same error:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communication link error

The last packet sent to the server was 16 ms ago.

So, I wonder if these two errors are the same or are they different? I did some research, and it seems that both errors came down to the same solution: using the property "testConnectionOnCheckout = true". However, according to c3p0, this is a very expensive test. He advises using "idleConnectionTestPeriod", but I already set it to 120 seconds. What value should I use so that it can correctly check the connection in standby mode?

Therefore, I basically ask two things: 1. How to reproduce the error that I received in the production code? 2. How to fix it?

Thank!

Answer 1

I had similar problems with MySQL and connection pooling. The problem is that you tell the connection pool that the idle timeout is 30 minutes, but the database disconnects the connection after 10 seconds. Since your idle test period is 120 seconds, it leaves a little less than 110 seconds for the pool to use a broken connection!

I would use the following settings for production:

 MySQL: wait_timeout=75 C3P0: maxIdleTime=60 idleConnectionTestPeriod=55 

Answer 2

Fei - can be one of several things, actually can’t speak based on the information posted so far.

Suggest adding MySQL / Spring / Hibernate / C3PO / JDBC numbers to your question if there is a known issue.

A production error message is common, with many possible causes. Some lead to you:

• A production error may indicate that your application is not releasing a connection to the pool when this is done, preventing c3p0 from checking. (C3p0 idle checks can only be applied to uncheck-out.)

• Make sure c3p0 really works (you can use the 'vanilla' connections if not). In your test, if you set (for example,) MySql wait_timeout = 10, application thread sleep = 35 and idleConnectionTestPeriod = 30, if the pool works, the exception should be thrown.

• Due to the downtime of the check: do not use getTables () by default - maybe preferTestQuery is installed for something cheap (-er) 'SELECT 1', maybe for MySQL?

Answer 3

To reproduce your error, set the connection timeout in your MySQL properties to a very low value, i.e. 2 ms, and run the query, which, as you know, has a long processing time. You can set the timeout property either in the MySQL connection string or through the property if you use property files to configure your JDBC connection. You can find Javadocs in your specific jaxax.sql.DataSource connection and in your MySQL docs to determine how to do this.