CODEWITHSUNDEEP
javamysqltomcatjdbcpacket
DeathGun
DeathGun

Reputation: 79

com.mysql.jdbc.exception.jdbc4.CommunicationsException: The last packet successfully received from the server was

I have a method that executes in the early morning but jdbc connection tomcat closes by itself and I don't know why and how.

The last packet successfully received from the server was 86.397.130 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3567)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3456)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3997)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318)
    at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)

In my server.xml I have the context with the resource "autoReconnect=true"

url="jdbc:mysql://localhost:3306/database?autoReconnect=true"

But still does not work. Any help or suggestions?

Thanks.

Upvotes: 2

Views: 8654

Answers (2)

yaswanth
yaswanth

Reputation: 2477

This occurs because you are using a mysql connection from tomcat which was expired by the mysql server. This happens if you are creating a single connection and re-using it. You'll have to validate the connection first before re-using it. I would suggest use a connection pool DBCP, C3P0 etc

Even using standard connection pool with default params doesn't fix the issue generally. Taking DBCP as the example,

you need to specify this parameter validationQuery. Before the pool returns a connection for use by the client, this validationQuery is run against the server (mysql) and if it fails the connection is discarded and a new connection is established and returned.

You can use SELECT 1 as a validationQuery

If you don't want to use a connection pool and feel that performance is not an issue and can live with a single connection, the easiest way would be to create a new connection every time and close it.

Edit 1:

Yes you can have a quartz task that can do the work. That means you'll have to implement a wrapper class which has a connection instance variable which gets refreshed every 4 hours by the background Quartz task. Note that the time period is configurable on the mysql server. So your refresh time should typically be lesser than that. Note that your wrapper class which contains the connection object might be accessed by two different threads (your main thread where you need the connection and the refreshing thread) at some point of time, which you'll have to synchronize.

Upvotes: 2

Arvind singh
Arvind singh

Reputation: 7

MySQL disconnects the connection after 8 hours , please implement connection pooling(dbcp) in this case. hope it will resolve your problem.

MYSQL 8 Hours Time out Problem

Upvotes: -2

Related Questions