I’ve been spending some time trying to fix this one on my own, but it looks like I will have to give in to the support system again.
In a given window in my application I have a reporting plugin and a date range selector. When the value of the date range selector is changed, I run a query to retrieve statistical calculations from historical data within that date range using MIN() AVG() STD() etc. However, since I started doing this I’ve come across random errors saying my datasource is faulted when trying to evaluate certain tags in SQLTags. Now, I will say that the query is rather massive (although the response time is about 10-50ms in MySQL query browser), and I am thinking that it’s causing some sort of timeout. After closing the 15 or 30 error boxes, the problem subsides and the application works fine.
Unfortunately I cannot predict when the error happens. I searched the forums and didn’t find any topics describing the issue, but I’m hoping it can be easily explained and just hasn’t been brought up yet.
A datasource will go FAULTED when the underlying JDBC driver and/or connection pooling system throws an error when trying to get a new connection. Then, any queries that are pointed at that datasource will fail because the datasource is faulted, but every 10 seconds all faulted datasources are re-tested with their validation query, and if that works, the datasource is marked as good again, and queries will start working.
There are lots of reasons that the system would be unable to get a connection. The first errored query in the stream of 15-30 bad ones will contain the actual error that caused the datasource to go faulted. You need to find this - the Gateway logs may be helpful.
I had a chance to get the output from the Gateway logs for this error. The output reads:
2009-03-03 10:58:29AM ERROR ERROR(304): Connection Error: Cannot get a connection, pool error Timeout waiting for idle object [/gateway]
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:104)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
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:96)
... 23 more
It happened when I opened a window containing a reporting viewer object and some tables with some pretty hefty SQL queries.
That error is a cryptic way of telling you that you tried to get a database connection from the pool, but the pool was full, and it tried to wait for its configured time, but then it timed out.
In other words, it overloaded. Your options are (and these aren’t mutually exclusive)
Increase the pool size and/or the wait timeout. ( I think it defaults to 8 connections with a 5 second timeout)
Do some performance tuning on your queries so that they don’t take so long!