Tomcat + Jersey + MySQL No operations allowed after connection closed

Question

I am implementing an aplication deployed on a Tomcat 8.0.28 which connects to a MySQL Database. This application acts a REST API and will be called every time that new data has to be stored or retrieved in/from the database. I use jersey for the database handling.

The problem is, after a while without calling this app (let's say a day), I cannot call my endpoints anymore. A message indicating that the connections have been expired appear.

These are my server.xml and my DB Connections (I have two endpoints with connection to DB: one for retrieving data, another for inserting):

DatabaseConnection.java

@Singleton
public class DatabaseConnection {
private DataSource dataSource;
private Connection conn;
private static final Logger LOGGER = Logger.getLogger(DatabaseConnection.class);

private static final DatabaseConnection INSTANCE = new DatabaseConnection();

private DatabaseConnection() {
    if (INSTANCE != null) {
        throw new IllegalStateException("Already instantiated");
    }
}

public static DatabaseConnection getInstance() {
    return INSTANCE;
}

private void prepareConnection() {
    try {
        Context initialContext = new InitialContext();
        Context environmentContext = (Context) initialContext
                .lookup("java:comp/env");
        String dataResourceName = "jdbc/DBNAME";
        dataSource = (DataSource) environmentContext
                .lookup(dataResourceName);
    } catch (NamingException e) {
        LOGGER.error("Error in DatabaseConnection ", e);
        e.printStackTrace();
    }
}

public Object[][] executeQuery(String query) {
    prepareConnection();
    try (Connection connection = dataSource.getConnection();
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(query)) {
            return getResult(rs);               
    } catch (SQLException e) {
        LOGGER.error("Error in DatabaseConnection", e);
        e.printStackTrace();
    }
    return null;
}

public Object[][] getResult(ResultSet rs) throws SQLException{
      //Transforms the resultset into a bidimiensional array
}

public Integer executeUpdate(String query) {
    return executeUpdate(query, false);
}

public Integer executeUpdate(String query, boolean returnKeys) {
    Integer result = null;
    prepareConnection();
    try (Connection connection = dataSource.getConnection();
            Statement stmt = connection.createStatement()) {
        if (returnKeys) {
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            try (ResultSet rs = stmt.getGeneratedKeys()) {
                rs.next();
                result = rs.getInt(1);
            }
        } else {
            stmt.executeUpdate(query);
        }
    } catch (SQLException e) {
        LOGGER.error("Error in DatabaseConnection", e);
        e.printStackTrace();
    }
    return result;
}

}

server.xml

There is no difference commenting out the last two lines in server.xml (I already tried to delete all abandoned connections). The exception I am getting is the following one.

2015-11-21 18:10:00,362 [http-nio-3303-exec-38] ERROR com.myapp.server.persistence.DatabaseConnection - Error in DatabaseConnection
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:862)
at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1237)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1232)
at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2437)
at com.mysql.jdbc.ConnectionImpl.createStatement(ConnectionImpl.java:2421)
at sun.reflect.GeneratedMethodAccessor56.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
at com.sun.proxy.$Proxy58.createStatement(Unknown Source)
at com.myapp.server.persistence.DatabaseConnection.executeQuery(DatabaseConnection.java:56)

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 90,165,721 milliseconds ago.  The last packet sent successfully to the server was 90,165,721 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:983)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3644)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2452)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2504)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1370)
at com.myapp.server.persistence.DatabaseConnection.executeQuery(DatabaseConnection.java:57)
... 39 more

Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
at java.net.SocketOutputStream.write(SocketOutputStream.java:159)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3626)
... 50 more

I searched on the internet and the connetions I create are properly closed after the DB is called by using the try-with-paramters construction. Why are I not able then to call the connections again after some time? How could I skip this issue?

St&#233;phane · Accepted Answer

Your connections are idle in the pool for too long and are getting dropped by MySQL causing an error when you use them

You could tell the pool to test the connections before giving them to the application

Tomcat + Jersey + MySQL No operations allowed after connection closed

Answers (1)

Related Questions