Stuck queries / Log file: Error closing connection

I’m seeing an issue where a few queries daily will hang. Depending on how frequently I check the connection pool, they can be anywhere from several minutes to several days old. The queries themselves are random in that they’re not isolated to a specific SQL stmt, and the SQL itself will typically run in a few seconds or less ( in PL/SQL…Oracle DB).

In the log file, I find the following error message:

INFO | jvm 1 | 2018/08/15 05:07:48 | WARN [RunQuery ] [05:07:48,449]: Error closing connection.
INFO | jvm 1 | 2018/08/15 05:07:48 | java.sql.SQLException: Already closed.
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:84)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:181)
INFO | jvm 1 | 2018/08/15 05:07:48 | at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.close(SRConnectionWrapper.java:310)
INFO | jvm 1 | 2018/08/15 05:07:48 | at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:94)
INFO | jvm 1 | 2018/08/15 05:07:48 | at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:345)
INFO | jvm 1 | 2018/08/15 05:07:48 | at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
INFO | jvm 1 | 2018/08/15 05:07:48 | at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
INFO | jvm 1 | 2018/08/15 05:07:48 | at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.Server.handle(Server.java:518)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
INFO | jvm 1 | 2018/08/15 05:07:48 | at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
INFO | jvm 1 | 2018/08/15 05:07:48 | at java.lang.Thread.run(Thread.java:745)

The database connection properties are set to default values, other than the max size of the pool being set to 25.

These “stuck” queries are not running on the Oracle side. While I can easily cancel them on the DB status page, would enabling the evictor thread be an option to get rid of these stuck queries?

We are not sure what is causing the SQL to hang in the first place. Our Oracle DB is on a cloud server, and we’ve been aware of some dropped packets occurring between our Ignition server and Oracle DB, which may be contributing to the issue (not sure at this point).

This is random (1-2 queries per day), and we’re executing tens of thousands of SQL calls daily without issue.

Any suggestions are appreciated. Thank you.