I have an MSSQL database connection that is working fine for the most part but it will occasionally fault out for a very short amount of time (less than 1 second) and then reconnect. This doesn’t really cause issues unless there’s a query in the GUI that is pulling from the database when it does this. Then, I get pesky error messages.
Are there any settings in the database connection that might help mitigate this issue?
Maybe. There are a number of possible causes, some of which can be helped with settings.
Network flakiness. If a network link or firewall is breaking your connections, there's nothing that can be done within Ignition. Get your IT to diagnose this (wireshark) and address it.
Slow query stalls. If you fire several slow queries at the same time, you will exhaust the connection pool. Everything else will have to wait for one of the pooled connections to free up, and there's a timeout for that. (Might be the pesky errors. You should show an example.) This is common when your historian tables do not have proper indices, or you are simply allowing larger history queries than your DB can really handle. This can sometimes be mitigated by increasing the connection pool size.
DB throughput stalls. If you have a wimpy database for your application, you can get stalls on inserts and updates, too, not just large queries. If this is the case, increasing the connection pool size will usually make it worse, so don't blindly do that.
I just looked at the log after the latest fault.
java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:734)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:300)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnectionImpl(DatasourceManagerImpl.java:169)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:293)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:152)
at com.inductiveautomation.gateway.tags.history.processing.GatewayContextConnectionProvider.getConnection(GatewayContextConnectionProvider.java:31)
at com.inductiveautomation.gateway.tags.history.processing.GatewayContextConnectionProvider.getConnection(GatewayContextConnectionProvider.java:10)
at com.inductiveautomation.gateway.tags.history.query.DatasourceHistoryInterface.getConnection(DatasourceHistoryInterface.java:77)
at com.inductiveautomation.gateway.tags.history.query.DatasourceHistoryInterface.maybeLoadIds(DatasourceHistoryInterface.java:96)
at com.inductiveautomation.gateway.tags.history.query.DatasourceHistoryInterface.getSystemId(DatasourceHistoryInterface.java:159)
at com.inductiveautomation.gateway.tags.history.query.DatasourceQueryExecutor.initialize(DatasourceQueryExecutor.java:544)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.AggregateHistoryQueryExecutor.initialize(AggregateHistoryQueryExecutor.java:78)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.HistoryWriter.initLoader(HistoryWriter.java:141)
at com.inductiveautomation.ignition.gateway.sqltags.history.query.HistoryWriter.execute(HistoryWriter.java:211)
at com.inductiveautomation.ignition.gateway.sqltags.history.TagHistoryManagerImpl.queryHistory(TagHistoryManagerImpl.java:572)
at com.inductiveautomation.ignition.gateway.tags.model.ProjectDefaultTagManagerFacade.queryHistory(ProjectDefaultTagManagerFacade.java:440)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.QueryTagHistory.invoke(QueryTagHistory.java:102)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:435)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)
at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:578)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1570)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1543)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.Server.handle(Server.java:563)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:505)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:762)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:497)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:416)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:385)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:272)
at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.lambda$new$0(AdaptiveExecutionStrategy.java:140)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:321)
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:428)
at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:232)
at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
... 62 common frames omitted
OK. You now have to study your database performance to see why you are exhausting the pool. Look at the diagnostics for slow queries first.
I am dynamically building 5 tag history bindings (queries) when a window opens via indirect bindings on some internal props. I think the issue is that the tag history binding tries to run before those indirect properties are “ready” so it’s grabbing way too much data.
For instance:
trendStart (result = 12/30/2025 08:42:30 -0600):
try(sortDataset({Starter Diagnostics.startTimes},0,0)[0,0],0)
trendEnd (result = 12/30/2025 08:45:30 -0600):
try(dateArithmetic({Starter Diagnostics.trendStart1},{Starter Diagnostics.trendWindow},'sec'),0)
(trendWindow = 165)
startTimes:
(the 0 is used to index the “start” of the values so the script that updates these tags knows where it’s at)
Maybe the failover for the try statements shouldn’t be 0? Is that causing it to try and grab all rows?
When all of the values are “ready” and the tag history binding executes it ends up being ~60 rows each (x5) which shouldn’t take long to query.
Any ideas for delaying that tag history binding until the others are ready? Thinking I’ll have to change it to a property change script to run the queries and populate the datasets manually once it’s all ready.
Arrange for the default start and end times to be null (non-persistent), then use coalesce() to substitute a time far in the future. That ensures the startup historian query returns nothing, and quickly.
