I am having a problem with my Database Connection Faulting after a few days of running Ignition. The system is barely even being used as it is still in the experimental stages so I don’t believe it’s due to heavy traffic or queries.
What I usually have to do is simply restart the Gateway service but I would like to know if this can be done automatically once a fault is detected or any guesses as to what might be causing it?
Let me know if any scripts/logs I can upload will help resolve this.
Thanks.
A few questions:
-
What version of Ignition is it?
-
What is the fault message that is provided on the Database Status page of the gateway?
Regards,
Version 7.2.8 (b178)
Short Error:
Faulted
Error:
Cannot get a connection, pool error Timeout waiting for idle object
Full Error:
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:104)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:179)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:128)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:174)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:117)
com.inductiveautomation.ignition.gateway.sqltags.history.query.loaders.DatasourceHistoryLoader.loadDriverId(DatasourceHistoryLoader.java:154)
com.inductiveautomation.ignition.gateway.sqltags.history.query.loaders.DatasourceHistoryLoader.initialize(DatasourceHistoryLoader.java:135)
com.inductiveautomation.ignition.gateway.sqltags.history.query.loaders.AggregateHistoryLoader.initialize(AggregateHistoryLoader.java:50)
com.inductiveautomation.ignition.gateway.sqltags.history.query.HistoryWriter.execute(HistoryWriter.java:158)
com.inductiveautomation.ignition.gateway.sqltags.history.query.BasicTagHistoryQueryProvider.query(BasicTagHistoryQueryProvider.java:137)
com.inductiveautomation.ignition.gateway.servlets.gateway.functions.QueryTagHistory.invoke(QueryTagHistory.java:99)
com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:389)
javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
java.lang.Thread.run(Unknown Source)
java.util.NoSuchElementException: Timeout waiting for idle object
org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:958)
org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:179)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:128)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:174)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:117)
com.inductiveautomation.ignition.gateway.sqltags.history.query.loaders.DatasourceHistoryLoader.loadDriverId(DatasourceHistoryLoader.java:154)
com.inductiveautomation.ignition.gateway.sqltags.history.query.loaders.DatasourceHistoryLoader.initialize(DatasourceHistoryLoader.java:135)
com.inductiveautomation.ignition.gateway.sqltags.history.query.loaders.AggregateHistoryLoader.initialize(AggregateHistoryLoader.java:50)
com.inductiveautomation.ignition.gateway.sqltags.history.query.HistoryWriter.execute(HistoryWriter.java:158)
com.inductiveautomation.ignition.gateway.sqltags.history.query.BasicTagHistoryQueryProvider.query(BasicTagHistoryQueryProvider.java:137)
com.inductiveautomation.ignition.gateway.servlets.gateway.functions.QueryTagHistory.invoke(QueryTagHistory.java:99)
com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:389)
javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
java.lang.Thread.run(Unknown Source)
That message means you don’t have any connections in the pool left for your database connection. When you connect to the database we create 8 connections in the pool by default that Ignition can use. Sometimes the connections get stuck in which case you cannot use it. Take a look at the Ignition Gateway Status page and select the Database connections section. There you can see how many of those connections are used up right now.
If you log in to the gateway config section you can see the exact queries running in each connection. By enabling live values you can see really what is going on, ie if the connections are stuck or if you are simply using them all.
If you want to increase the number of connections in the pool you can edit your database connection in the Gateway config section and increase the:
Max Active
Max Idle
settings (in the Advanced properties section) to a higher number like 20. Hope this helps.
I cannot see how many active connections there are at the moment because it has faulted and will not display that. Once I restart the gateway service I can see how many connections are active. Generally only 1 or 2 are active at once.
OK, I’ve increased those parameters. Won’t that just lead to the same result just take longer for it to happen? I want to know if there is a way to cycle the database connection or restart the Gateway Service when this happens.
I want to add that, in my newbness to the Ignition system I made a database called “___Test” and then later made one called MySQL. I made the new MySQL database the default database and changed all my tags/history to query from the MySQL database, however it seems that the original ‘Test’ database is still being used for some queries and sometimes they are both displaying the same query:
SELECT id FROM sqlth_scinfo WHERE scname=? AND drvid=?
Could this be the cause of the fault, two queries to the same database from different connections?
Is there an absolute way to replace the old database name/access with my new one? I’ve reconfigured in every way I could find as stated above(tags, history, default database).
Or will I have to make a new project or something of that sort?
Thanks Again.
In my last post I had said that I set the default database to my new ‘Mysql’ connection, however I revisited the setting and it was not the default anymore… This may have been the culprit. Sorry for the incorrect info and I’ll re-post if the problem persists.
Hi, I have a similar problem
Faulted
Error:
Cannot get a connection, pool error Timeout waiting for idle object
I could see that i have 125 Total querys in average
what is the max value to increase the number of connections
Max Active
Max Idle
It helped me putting 20 but as i saw there are more querys and i have the problem again.
Carito 
I would suggest looking at the status page and selecting database. You can enable the live values and see what is taking a long time to run. Additionally, you can go to configure and store and forward and look at the quarantine control tab to see what is being quarantined. This will also give you some additional information that can give you an idea as to why they queries are being quarantined.
If you have already increased to 20 then you should be looking into my suggestions above to get to the root cause to the issue.
As a side note, its usually best to start a new thread with your question.