SQLContainer - communication link failure after few hours of inactivity

Hello,
I’m using SQLContainer to connect to MySQL database. I’m using MySQL db 5.5.13 and MySQL j connector 5.1.16 and Tomcat 7. After few hours of inactivity I get the following error when I first try to access the application. I’ve to hit refresh (CTRL+R) few times before the error goes away. I’m thinking this is some kind of stale connection issue. Has anyone else experienced this?

I’ve seen in different forums people having the same issue, and the fixes were mainly for container datasources and didn’t not help much with the issue I’m having using SQLContainer.

Thanks,
Jacob.

Please see my exception below

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 37,305,336 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.handleServiceException(AbstractApplicationServlet.java:999)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:548)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

root cause

java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 37,305,336 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
com.vaadin.addon.sqlcontainer.query.TableQuery.fetchMetaData(TableQuery.java:525)
com.vaadin.addon.sqlcontainer.query.TableQuery.(TableQuery.java:87)
com.vaadin.addon.sqlcontainer.query.TableQuery.(TableQuery.java:101)
com.test.ossmt.util.DatabaseHelper.getSchedulerRunDetails(DatabaseHelper.java:80)
com.test.ossmt.HomeView.addCollectionStatusPanel(HomeView.java:82)
com.test.ossmt.HomeView.refresh(HomeView.java:65)
com.test.ossmt.HomeView.(HomeView.java:41)
com.test.ossmt.MonitoringTool.showHomeView(MonitoringTool.java:49)
com.test.ossmt.MonitoringTool.init(MonitoringTool.java:44)
com.vaadin.Application.start(Application.java:554)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.startApplication(AbstractApplicationServlet.java:1208)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:484)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

root cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 37,305,336 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
java.lang.reflect.Constructor.newInstance(Constructor.java:513)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3090)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3520)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1510)
com.mysql.jdbc.DatabaseMetaData$9.forEach(DatabaseMetaData.java:4983)
com.mysql.jdbc.IterateBlock.doForAll(IterateBlock.java:51)
com.mysql.jdbc.DatabaseMetaData.getTables(DatabaseMetaData.java:4961)
com.vaadin.addon.sqlcontainer.query.TableQuery.fetchMetaData(TableQuery.java:484)
com.vaadin.addon.sqlcontainer.query.TableQuery.(TableQuery.java:87)
com.vaadin.addon.sqlcontainer.query.TableQuery.(TableQuery.java:101)
com.test.ossmt.util.DatabaseHelper.getSchedulerRunDetails(DatabaseHelper.java:80)
com.test.ossmt.HomeView.addCollectionStatusPanel(HomeView.java:82)
com.test.ossmt.HomeView.refresh(HomeView.java:65)
com.test.ossmt.HomeView.(HomeView.java:41)
com.test.ossmt.MonitoringTool.showHomeView(MonitoringTool.java:49)
com.test.ossmt.MonitoringTool.init(MonitoringTool.java:44)
com.vaadin.Application.start(Application.java:554)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.startApplication(AbstractApplicationServlet.java:1208)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:484)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

root cause

java.net.SocketException: Connection reset
java.net.SocketInputStream.read(SocketInputStream.java:113)
com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2537)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2990)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3520)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1510)
com.mysql.jdbc.DatabaseMetaData$9.forEach(DatabaseMetaData.java:4983)
com.mysql.jdbc.IterateBlock.doForAll(IterateBlock.java:51)
com.mysql.jdbc.DatabaseMetaData.getTables(DatabaseMetaData.java:4961)
com.vaadin.addon.sqlcontainer.query.TableQuery.fetchMetaData(TableQuery.java:484)
com.vaadin.addon.sqlcontainer.query.TableQuery.(TableQuery.java:87)
com.vaadin.addon.sqlcontainer.query.TableQuery.(TableQuery.java:101)
com.test.ossmt.util.DatabaseHelper.getSchedulerRunDetails(DatabaseHelper.java:80)
com.test.ossmt.HomeView.addCollectionStatusPanel(HomeView.java:82)
com.test.ossmt.HomeView.refresh(HomeView.java:65)
com.test.ossmt.HomeView.(HomeView.java:41)
com.test.ossmt.MonitoringTool.showHomeView(MonitoringTool.java:49)
com.test.ossmt.MonitoringTool.init(MonitoringTool.java:44)
com.vaadin.Application.start(Application.java:554)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.startApplication(AbstractApplicationServlet.java:1208)
com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:484)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

note The full stack trace of the root cause is available in the Apache Tomcat/7.0.16 logs.
Apache Tomcat/7.0.16

Hi,

this is most likely due to MySQL having a default setting of dropping connections after 8 hours of idle time. I can’t remember where the config for this exactly is, but I’d say you find it with a bit of googling. Basically you have two options:

  1. add ?autoReconnect=true to your database URL. This should cause stale connections to automatically reconnect.
  2. Increase the idle timeout from MySQL.

This could also be seen as a bug in SQLContainer: maybe we could check for connection staleness before handing it out from the reserveConnection() method. If you feel this would be a good feature, please create an enhancement ticket about it to our trac.


Teppo

Thanks Teppo,
I’ll try that and will let you know how it turns out.

Jacob