How to fix java.io.EOFException: Can not read response from server for MySQL

H

Hi, I made a java program where much database queries are processed in MySQL. After some time running, I got an error message:

java.io.EOFException: Can not read response from server.
The last packet successfully received from the server was X milliseconds ago

After some research, it seems that the MySQL driver is not reconnecting after it lost its connection to the database.

First, append the following line ?autoReconnect=true to your jdbc connection string:

//old jdbc connection String
jdbc:mysql://127.0.0.1:3306/mydatabase

//new jdbc connection String
jdbc:mysql://127.0.0.1:3306/mydatabase?autoReconnect=true

Next, you must find out what the wait timeout is of your MySQL. Run the query ‘show variables’ a search for wait_timeout. For me, it has the value 60.

Now we know the timeout, we can adjust our data source. I use Hibernate and C3P0. First, make sure you have the following dependencies to make hibernate and C3P0 work correctly:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>4.3.6.Final</version>
</dependency>
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.2.1</version>
</dependency>

Now we edit the data source values. Just make sure that the size of ‘idleConnectionTestperiod’ is smaller than your wait_timeout:

<bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="..." />
    <property name="jdbcUrl" value="..." />
    <property name="user" value="..." />
    <property name="password" value="..." />
    <property name="idleConnectionTestPeriod" value="50" /> <!-- is smaller than 60 -->
    <property name="preferredTestQuery" value="select 1" />
    <property name="testConnectionOnCheckout" value="true" />
    <property name="testConnectionOnCheckin" value="true" />
    <property name="maxPoolSize" value="10" />
    <property name="minPoolSize" value="5" />
</bean>

It’s done! Nice work! I hope it did the trick for you too. Enjoy!

Add comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Tag Cloud

Categories