2

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.

Thomas Fritsch
  • 9,639
  • 33
  • 37
  • 49
DeathGun
  • 79
  • 2
  • 10

2 Answers2

2

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.

yaswanth
  • 2,349
  • 1
  • 23
  • 33
  • It would be useful to create another Quartz task every for example 4 hours to check the connection only and avoid the disconnection? – DeathGun Nov 06 '17 at 08:21
  • Yep, I added another quartz task every 4 hours, just to connect the database and return simple string to keep alive the jdbc and test it, if it works, really appreciate your explanation. – DeathGun Nov 06 '17 at 08:58
-2

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

Arvind singh
  • 7
  • 2