App Engine - Cloud Sql - java.sql.SQLException: This Connection is closed

Question

After a while I got this error, so how do I make sure that the connection is not dead before giving my singleton?

Here is my code:

public static Connection getInstance() throws Exception {

    if(connection != null) {
        return connection;
    }

        DriverManager.registerDriver(new AppEngineDriver());
        connection = DriverManager.getConnection("jdbc:google:rdbms://"+instanceName+"/vidaao");

    return connection;

}

Stack trace:

java.sql.SQLException: This Connection is closed. at com.google.cloud.sql.jdbc.internal.Exceptions.newConnectionClosedException(Exceptions.java:52) at com.google.cloud.sql.jdbc.Connection.throwIfNotOpen(Connection.java:633) at com.google.cloud.sql.jdbc.Connection.getHoldability(Connection.java:194) at com.google.cloud.sql.jdbc.Connection.createStatement(Connection.java:123) at com.google.cloud.sql.jdbc.Connection.createStatement(Connection.java:54) at db.DbUser.exist(DbUser.java:209) at controller.CtrlUser.login(CtrlUser.java:343) at vidaao.ConnectServlet.doPost(ConnectServlet.java:36) at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166) at com.google.appengine.api.socket.dev.DevSocketFilter.doFilter(DevSocketFilter.java:74) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.tools.development.ResponseRewriterFilter.doFilter(ResponseRewriterFilter.java:110) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.tools.development.HeaderVerificationFilter.doFilter(HeaderVerificationFilter.java:34) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.api.blobstore.dev.ServeBlobFilter.doFilter(ServeBlobFilter.java:61) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:43) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.tools.development.StaticFileFilter.doFilter(StaticFileFilter.java:125) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.tools.development.BackendServersFilter.doFilter(BackendServersFilter.java:97) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182) at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765) at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418) at com.google.appengine.tools.development.DevAppEngineWebAppContext.handle(DevAppEngineWebAppContext.java:94) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at com.google.appengine.tools.development.JettyContainerService$ApiProxyHandler.handle(JettyContainerService.java:380) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at org.mortbay.jetty.Server.handle(Server.java:326) at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542) at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755) at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218) at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404) at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409) at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)

I tried to use isClosed() but doesn't work and isValid() looks like it is working but it's slowing down the connection to the database each time

Rob Rob · Accepted Answer · 2013-12-12T16:20:24

Calling Connection#isValid would be the best approach, but yes, it pings the database to ensure the connection is valid.

I noticed you are not using the standard mysql connector/j driver inside of AppEngine, I'd recommend giving that a try. It will not time out the connections on the server the same way that the older driver does.

See https://developers.google.com/appengine/docs/java/cloud-sql/#Java_Connect_to_your_database for the classname of the JDBC driver class to use within AppEngine as well as the URL to use.

Hope this helps, Rob

App Engine - Cloud Sql - java.sql.SQLException: This Connection is closed

1 Answers