# 2 sessions, but 4 queries

**URL:** <https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530>\
**Category:** Ignition\
**Tags:** ignition78, database, scripting, vision\
**Created:** [November 12, 2024, 6:01pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530 "2024-11-12T18:01:36Z")\
**Posts on this page:** 15\
**Page:** 2

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 21, 2024, 5:33am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/22 "2024-11-21T05:33:07Z")

</div>

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

```python
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>

```

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 21, 2024, 5:52am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/23 "2024-11-21T05:52:56Z")

</div>

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

```python
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

```

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 21, 2024, 6:14am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/24 "2024-11-21T06:14:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 21, 2024, 9:59am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/25 "2024-11-21T09:59:04Z")

</div>

> [@andrews](#):
>
> ... but it isn't much prettier.

Well fix it then! Remove the line breaks.

```python
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>

```

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 21, 2024, 10:18am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/26 "2024-11-21T10:18:48Z")

</div>

@Transistor any ideas?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 21, 2024, 10:39am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/27 "2024-11-21T10:39:55Z")

</div>

The error seems to be,

```python
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](https://forum.inductiveautomation.com/t/error-database-connection/82085/5):
>
> 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 …

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 21, 2024, 10:49am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/28 "2024-11-21T10:49:56Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 21, 2024, 11:05am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/29 "2024-11-21T11:05:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 21, 2024, 11:12am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/30 "2024-11-21T11:12:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 21, 2024, 12:14pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/31 "2024-11-21T12:14:28Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 21, 2024, 12:14pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/32 "2024-11-21T12:14:36Z")

</div>

> [@andrews](#):
>
> MySQL 5.1

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

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 21, 2024, 12:22pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/33 "2024-11-21T12:22:40Z")

</div>

@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. 😭

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 21, 2024, 5:01pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/34 "2024-11-21T17:01:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 21, 2024, 8:23pm UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/35 "2024-11-21T20:23:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 28, 2024, 9:26am UTC](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530/36 "2024-11-28T09:26:09Z")

</div>

Long story short, I didn't wanna spend too much time on this. So I dropped the last col which is what has been slowing it down. And I wrote a Macro in Excel to finish it off.

[Previous page](https://forum.inductiveautomation.com/t/2-sessions-but-4-queries/98530.md?page=1)