ERROR(304): Connection Error: Cannot get a connection, pool

Robert · November 25, 2009, 4:40pm

We used to get this error a fair bit. Then we reworked the app and tweaked the number of connections and things seemed to be working.

Today the customer called with the error on his screen:
Gateway Error 304:
Connection Error: Datasource “dcsFep_p1v” is FAULTED.

The gateway was restarted to clear the error. There were no errors in the datasource logs (postgres)

The following was in the Gateway log:

Aug 10, 2009 09:36:26 AM ERROR ERROR(304): Connection Error: Cannot get a connection, pool exhausted org.apache.catalina.core.ContainerBase.[mainEngine].[localhost].[/gateway] http-9090-Processor2 org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
     at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)
     at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
     at com.inductiveautomation.factorypmi.gateway.db.DatasourceManager$DSInfo.createConnection(DatasourceManager.java:575)
     at com.inductiveautomation.factorypmi.gateway.db.DatasourceManager.getConnection(DatasourceManager.java:178)
     at com.inductiveautomation.factorypmi.gateway.db.DatasourceManager.getConnection(DatasourceManager.java:170)
     at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doDBAction(Gateway.java:2277)
     at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.runQuery(Gateway.java:1852)
     at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doPost(Gateway.java:332)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
     at com.inductiveautomation.factorypmi.gateway.ErrorReportValve.invoke(ErrorReportValve.java:95)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
     at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
     at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
     at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
     at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
     at java.lang.Thread.run(Unknown Source)
    Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
     at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:958)
     at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
     ... 23 more

What is cause of the error?

Robert · November 25, 2009, 4:47pm

[quote=“Robert”]We used to get this error a fair bit. Then we reworked the app and tweaked the number of connections and things seemed to be working.

Today the customer called with the error on his screen:
Gateway Error 304:
Connection Error: Datasource “dcsFep_p1v” is FAULTED.

The gateway was restarted to clear the error. There were no errors in the datasource logs (postgres)

The following was in the Gateway log:

Nov 25, 2009 08:39:30 AM ERROR ERROR(304): Connection Error: Cannot get a connection, pool exhausted org.apache.catalina.core.ContainerBase.[mainEngine].[localhost].[/gateway] http-9090-Processor13 org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
     at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)
     at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
     at com.inductiveautomation.factorypmi.gateway.db.DatasourceManager$DSInfo.createConnection(DatasourceManager.java:575)
     at com.inductiveautomation.factorypmi.gateway.db.DatasourceManager.getConnection(DatasourceManager.java:178)
     at com.inductiveautomation.factorypmi.gateway.db.DatasourceManager.getConnection(DatasourceManager.java:170)
     at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doDBAction(Gateway.java:2277)
     at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.runQuery(Gateway.java:1852)
     at com.inductiveautomation.factorypmi.gateway.servlets.Gateway.doPost(Gateway.java:332)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
     at com.inductiveautomation.factorypmi.gateway.ErrorReportValve.invoke(ErrorReportValve.java:95)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
     at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
     at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
     at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
     at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
     at java.lang.Thread.run(Unknown Source)
    Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
     at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:958)
     at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
     ... 23 more

What is cause of the error?[/quote]

Robert · November 25, 2009, 4:49pm

Sorry for the double post. I thought I was editing the first post.
Reason for edit: pasted the correct error message. (expect for the date it does look the same)

Carl.Gould · November 27, 2009, 5:38pm

This error simply means that the connection pool is full (all connections in use), and no connection became available within the timeout. (the size of the pool and the time to wait are parameters of the connection.

By default the pool has a max of 8 connections. This is sufficient for most applications. The presence of this error typically means one of two things:

You’re running a bunch of queries that are taking a long time to complete, thus they’re filling up the pool. This is usually caused by poorly optimized table layout (lack of indexes)
You’re just running too many queries in general. This is often caused by mistakenly not using the “swap” navigation technique, resulting in many windows being open in the background simultaneously on the clients.

To troubleshoot this, you can turn on the verbose output of the datasource status by appending “&verbose=1” to the end of the url, so the url would be something like this:
myhost:8080/gateway/Gateway?tab= … &verbose=1

That will tell you how many connections are currently in use. Because this is a fairly common problem, the next major version has much better troubleshooting tools for this, allowing you to see more detail about the current state of the connection pool.

Robert · November 27, 2009, 10:37pm

Thanks, I’ll give verbose a try and see what it tells me.
I sure we have already bumped the number of connections.