2 sessions, but 4 queries

[andrews]

@Transistor This is directly copied from the terminal, but it isn't much prettier.

mysql> SHOW INDEXES FROM wastewaterlog;
+---------------+------------+-------------------------+--------------+---------
----------+-----------+-------------+----------+--------+------+------------+---
------+
| Table         | Non_unique | Key_name                | Seq_in_index | Column_n
ame       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Co
mment |
+---------------+------------+-------------------------+--------------+---------
----------+-----------+-------------+----------+--------+------+------------+---
------+
| wastewaterlog |          0 | PRIMARY                 |            1 | wastewat
erlog_ndx | A         |       64371 |     NULL | NULL   |      | BTREE      |
      |
| wastewaterlog |          1 | WasteWaterLogt_stampndx |            1 | t_stamp
          | A         |       64371 |     NULL | NULL   | YES  | BTREE      |
      |
+---------------+------------+-------------------------+--------------+---------
----------+-----------+-------------+----------+--------+------+------------+---
------+
2 rows in set (0.00 sec)

mysql>

[andrews]

I did create indexes via the terminal. I can query a days worth of data but more than that and I get this error:

java.lang.Exception: Error running query:
SQLQuery(query=SELECT 
    main.t_stamp AS Date_And_Time,
    main.PHlevel AS PH_Level,  
    main.GrandTotal AS Totalizer_Read_Gallons,  
    main.FlowRate / 10 AS Flow_Rate_GPM,
    main.GrandTotal - IFNULL((SELECT sub.GrandTotal 
                             FROM wastewaterlog sub 
                             WHERE sub.t_stamp < main.t_stamp 
                             ORDER BY sub.t_stamp DESC 
                             LIMIT 1), main.GrandTotal) AS Gallons_Change
FROM wastewaterlog main
WHERE main.t_stamp BETWEEN "2023-06-01 00:00:00" AND "2023-06-15 23:59:59"
ORDER BY main.t_stamp;, database=Wellington)@5000ms
On: Reporting.Root Container.Table.data
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:317)
	at java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Connection Error: Cannot get a connection, pool error Timeout waiting for idle object
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:327)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:301)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:258)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:803)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:772)
	at com.inductiveautomation.factorypmi.application.gateway.SQLQuery.execute(SQLQuery.java:48)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:690)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.access$500(QueryManager.java:663)
	at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:314)
	... 1 more
Caused by: 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.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:199)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnectionImpl(DatasourceManagerImpl.java:131)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:194)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:116)
	at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:66)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:60)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:345)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
	at org.eclipse.jetty.server.Server.handle(Server.java:518)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
	at java.lang.Thread.run(null)

Ignition v7.8.5 (b2016120813)
Java: Oracle Corporation 1.8.0_341

[andrews]

it is interesting, because i can query everything from the query browser and command terminal fine. But it just doesn't wanna populate the table past a day.

[Transistor]

@andrews

... but it isn't much prettier.

Well fix it then! Remove the line breaks.

mysql> SHOW INDEXES FROM wastewaterlog;
+---------------+------------+-------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name                | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+-------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| wastewaterlog |          0 | PRIMARY                 |            1 | wastewaterlog_ndx | A         |       64371 |     NULL | NULL   |      | BTREE      |         |
| wastewaterlog |          1 | WasteWaterLogt_stampndx |            1 | t_stamp           | A         |       64371 |     NULL | NULL   | YES  | BTREE      |         |
+---------------+------------+-------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql>

[andrews]

@Transistor any ideas?

[Transistor]

The error seems to be,

SQLNestedException: Cannot get a connection, 
pool error Timeout waiting for idle object

A web search for that error gives many results including some on this site. See here, for example:

Error database connection

Right at the top: Cannot get a connection, pool error Timeout waiting for idle object This isn't an error on the database side, strictly speaking. Ignition makes multiple internal connections to your DB and recycles them as each query completes. The number and behavior of this "connection pool" is part of the advance settings on a database connection. Not getting a connection for this timeout means you are running more simultaneous long queries than your DB can handle. Either your queries …

[andrews]

@Transistor yes, i have searched that but it is somewhat vague.

it is a fairly simple query, so all I can think of is the DB not powerful enough.

[Transistor]

I'd say your IFNULL subquery is overloading something somewhere. Depending on your DB flavour you may be able to use the SQL LEAD() and LAG() functions to do the query more efficiently.

[andrews]

I think i mentioned but I am using MySQL 5.1 which does not support those.

Which is why my query is using the IFNULL

[andrews]

i noticed i have about 4 connections with nothing happening. I think this is because i have the query in the data for the table, so it constantly runs. I'm going to see if changing it a button action helps alleviate some aches.

[pturmel]

@andrews

MySQL 5.1

Fix this. Really. There's an upgrade path from MySQL 5.x to MariaDB. You should do that.

[andrews]

@pturmel I want to but I have to justify it to the customer. Which means I have to exhaust my resourced on MySQL 5.1 first.

Unfortunately I've been pushing to upgrade this for 2-3 years now, and they still don't wanna budge. I'm hoping this will be the straw the breaks the camels back. But I wouldn't be surprised if they would rather do 1 day exports vs upgrade.

[pturmel]

I hope you are billing T&M for this research project.....

[andrews]

I am, but also didn't think it was gonna be that big of a deal. Seemed pretty straightforward, transaction manager puts them in and query pulls them out.