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 and MySQL j connector 5.1 and Tomcat 7. After few hours of inactivity I get the following error when I first try to access the application.
The class that takes care of establishing the connection pool is:

public class DbConnection {
private static DbConnection dbConnection;
private SimpleJDBCConnectionPool connectionPool;

private DbConnection(){
	String url          = "jdbc:mysql://localhost:3306/";
            String db           = "paf?autoReconnectForPools=true";
            String driver       = "com.mysql.jdbc.Driver";
            String user         = "paf";
            String pass         = "paf";
            int initialConn		= 2;
            int finalConn		= 20; 
    
	try {
		connectionPool = new SimpleJDBCConnectionPool(
			driver, url+db, user, pass, initialConn, finalConn
		);
	} catch (SQLException e) {
		e.printStackTrace();
	}
}

public static DbConnection getDbConnection(){
	if(dbConnection == null){
		dbConnection = new DbConnection();
	}
	return dbConnection;
}

public SimpleJDBCConnectionPool getConnectionPool() {
	return connectionPool;
}

}

The connection is then created in this way:

Connection conn1 = DbConnection.getDbConnection().getConnectionPool().reserveConnection();

and is released in this way:

DbConnection.getDbConnection().getConnectionPool().releaseConnection(conn1);

Here below is the exception:

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

The last packet successfully received from the server was 92.419.387 milliseconds ago. The last packet sent successfully to the server was 26 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:407)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3092)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3526)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2281)
at com.example.webapppaf.StartLoginForm.searchOnDB(StartLoginForm.java:126)
at com.example.webapppaf.StartLoginForm.access$5(StartLoginForm.java:91)
at com.example.webapppaf.StartLoginForm$1.onLogin(StartLoginForm.java:86)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:490)
at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:164)
at com.vaadin.ui.AbstractComponent.fireEvent(AbstractComponent.java:1193)
at com.vaadin.ui.LoginForm$2.handleParameters(LoginForm.java:104)
at com.vaadin.ui.Window.handleParameters(Window.java:502)
at com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:528)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:563)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:403)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:301)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:162)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketException: Software caused connection abort: recv failed
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(Unknown Source)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2536)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2989)
… 37 more

How can I fix this?
Please, help me.

I’ve been having similar issues, were you able to solve it?

Having a similiar issue and do not see that any answers have been posted. Has anyone been able to figure this issue out

The original post in this thread - while mentioning SQLContainer - crashes in a direct JDBC call that seems to have nothing to do with SQLContainer or Vaadin in general apart from perhaps managing transactions in a Vaadin specific way (but even that is not mentioned in the post IIRC).

The later posts provide no details about the setups and versions used, how transactions are managed, code exhibiting the problem, configuration including various timeouts (HTTP sessions, database, …), how connections are reserved and whether the code is 100% certain to release reserved connections etc. Thus, it is not really possible to answer this in any meaningful way.

This happens when the back end closes connections on time out.

You need to validate the connection with something like this:

public final boolean isValid(final Connection con) throws SQLException {
        final String bogusQuery = "SELECT 1";

        try (Statement st = con.createStatement(); ResultSet res = st.executeQuery(bogusQuery)) {
            return true;
        } catch (final SQLException sqlx) {
            return false;
        }
    }

in Connection#reserveConnection()

        Connection c;
        do {
            if (this.availableConnections.isEmpty()) {
                if (this.reservedConnections.size() < this.maxConnections) {
                    this.availableConnections.add(this.createConnection());
                } else {
                    throw new SQLException("Connection limit has been reached.");
                }
            }

            c = this.availableConnections.iterator().next();
            if (c == null) {
                throw new SQLException("Bad connection pool.");
            }
            if (!this.isValid(c)) {
                try {
                    c.close();
                    this.availableConnections.remove(c);
                    c = null;
                } catch (final SQLException e1) {
                    /* Nothing needs to be done */
                }
            } else {
                break;
            }
        } while (c == null);

Cheers.

Seems like more of an MySql issue. plenty of others have seen this without vaadin

http://stackoverflow.com/questions/6865538/solving-a-communications-link-failure-with-jdbc-and-mysql

Sorry to bother 4 years later but I’m experiencing the “Connection limit has been reached” error too, and it cannot be related to MySQL in any way because I’m using PostgreSQL.

The problem arised after I made some changes in my code after getting tired of explicitly having to pass my database (involving connection pool and other utilities) object to each class that was making use of it. Now I have class with just a static block which initializes the connecion pool, a bogus constructor which does “nothing” and it’s called prior to everything in my code, and I retrieve a connection everywhere I need it via a static getConnectionFromPool() method which simply does a .reserveConnection() and returns it, and I explicitly return it to the pool after having using it much likely Daniela Maratta stated on her first post:

With this method I expected to gain some resource usage improvement as I previously forgot to explicitly “close” (or “release”) the connection in a couple of places. Instead, I get this “Connect limit has been reached” error ¿randomly?.

I tried to follow Carlo Ottolina’s advice but I’m a bit confused, as usual. After inspecting SimpleJDBCConnectionPool source code, I determined that I either need to extend or to overwrite it to include his suggestions.

Carlo didn’t post the fill reserveConnection() code, so this is my interpretation of his suggestion inside v7.7 code:

@Override
public synchronized Connection reserveConnection() throws SQLException {
    if (!initialized) {
        initializeConnections();
    }

    Connection c;
    do {
        if (availableConnections.isEmpty()) {
            if (reservedConnections.size() < maxConnections) {
                availableConnections.add(createConnection());
            }
            else {
                throw new SQLException("Connection limit has been reached.");
            }
        }
        c = availableConnections.iterator().next();
        if(!this.isValid(c)){
            try {
                c.close();
                availableConnections.remove(c);
                c = null;
            }
            catch(SQLException e){
                // nothing needs to be done 
            }
        }
        else {
            break;
        }
    }
    while(c == null);

    reservedConnections.add(c);
    return c;
}

I have yet to test this, but, as I said, I’m not quite sure what’s going on here not about the correctness of this “solutions”.

Any suggestions welcome…

I just want to confirm that the solution above works perfectly for me.

It would be cool, though, if someone could confirm the correctness of it. Tomcat jdbcpool, for instance, has properties in order to set up these behaviours when using it:


https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Common_Attributes

Suggestions welcome…

I think you also need:

availableConnections.remove(c); before:

reservedConnections.add(c);

Hey! I missed this one!

Thanks for your correction, Carlo!!

I’ve been having the same issue with SQLContainer (MySQL DB with Server System Variables connection_timeout=10 minutes) and SimpleJDBCConnectionPool after 10 minutes of inactivity.

So is this the recommended practice to implement a custom connection pooling (as shown above by Carlo and Pere)?

Any help is highly appreciated,
Thanks

Carlo Ottolina:
You need to validate the connection with something like this:

public final boolean isValid(final Connection con) throws SQLException {
        final String bogusQuery = "SELECT 1";

Beware that some RDBMS don’t support SELECT queries without a FROM (for instance, Oracle). So you must change your “bogus” query according to this or write some detection code if your are using multiple RDBMS connections. For instance, with something like this:

final String bogusQuery;
        
        String bdaName = con.getMetaData().getDatabaseProductName();
        if(bdaName.equals("Oracle")||bdaName.equals("Microsoft SQL Server")){
            bogusQuery = "SELECT 1 FROM dual"; // Oracle no admite SELECT sin FROM; esto de "dual" parece valido para todas
        }
        else{
            bogusQuery = "SELECT 1";
        }

Also, it could be useful to let isValid() throw some exception in case it fails due to this reason. For instance:

try (Statement st = con.createStatement(); ResultSet res = st.executeQuery(bogusQuery)) {
            return true;
        } 
        catch(final SQLSyntaxErrorException e){
            throw new SQLException("Database engine doesn't admit this syntax. " + e.getMessage());
        }
        catch (final SQLException sqlx) {
            return false;
        }

Otherwise you may run into unexpected connection “silent” problems, as in my case: reserveConnection() will run forever because .isValid() will always return false, without any other possible result.

Ok people, I came here just to say this:

The above try..catch with resources sequence will, at least when using PostgreSQL, leave an idle in transaction that will seemengly last until last server (Tomcat it my case) reboot, which will probably cause all sort of critical and undesired side effects. Discovered this while trying to find the source of lots of autovacuum operations in our Amazon RDS server which every day, after several hours of server up & running, would finally start to deplete resources and delay operations, causing everything to fail in the end. While googling for hints I found some evidence that having idle in transaction would probably prevent autovacuum from running, and looking for these kind of transactions led me to discover that the one I actually was suffering from was the created by the isValid() code.

In my case, I recommend to completely avoid modifying SimpleJDBCConnectionPool at all, since, after finding out other more “traditional”, usual bugs probably causing the “Connection limit has been reached” error (like not returning the connection back to the pool, etc.), I don’t need these modifications at all and, furthermore, I suspect that they are not very performance-friendly.

If you need the modifications, then probably something like the following would work:

try (Statement st = con.createStatement(); ResultSet res = st.executeQuery(bogusQuery)) {
    conn.commit();
    return true;
} 
catch(final SQLSyntaxErrorException e){
    conn.rollback();
    throw new SQLException("Database engine doesn't admit this syntax. " + e.getMessage());
}
catch (final SQLException sqlx) {
    conn.rollback();
    return false;
}