Hi, I made a java program where much database queries are processed in MySQL. After some time running, I got an error message:
|
1 2 |
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:
|
1 2 3 4 5 |
//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:
|
1 2 3 4 5 6 7 8 9 10 |
<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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<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!