# Error pruning alarm journal data - read timed out

**URL:** <https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532>\
**Category:** Ignition\
**Tags:** ignition80\
**Created:** [June 9, 2022, 2:31am UTC](https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532 "2022-06-09T02:31:43Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [June 9, 2022, 2:31am UTC](https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532/1 "2022-06-09T02:31:43Z")

</div>

Been getting these errors in the gateway logs recently. Not sure what would have changed to cause it.  
My Alarm Journal is pretty large at the moment, with inserts taking up to 2min.

AlarmJournal is configured to use the S&F system, and enable pruning to keep the last 6 months.  
The tables are created in my “Process” database connection, which has a Max Wait time set to -1 (to disable timing out).  
I also have the following under “Extra connection properties” for the Process connection:  
`zeroDateTimeBehavior=convertToNull;connectTimeout=120000;socketTimeout=120000;rewriteBatchedStatements=true;`

The database connection is pointing to a MaxScale read-write-service that is load balancing 3 SQL DB servers.  
I don’t believe there is a read timeout on the MaxScale config.

Where else can i look to prevent a read time out?

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [June 9, 2022, 3:21am UTC](https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532/2 "2022-06-09T03:21:10Z")

</div>

Full error message below. Could it be related to any of the following MySQL variables?  
net\_read\_timeout = 30  
net\_write\_timeout = 60  
slave\_net\_timeout = 60

```auto
java.sql.SQLNonTransientConnectionException: (conn=267231) Connection timed out

at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73)

at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)

at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:273)

at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229)

at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149)

at org.mariadb.jdbc.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:181)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:981)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:181)

at com.inductiveautomation.ignition.gateway.alarming.journal.DatabaseAlarmJournal$JournalMaintenanceTask.run(DatabaseAlarmJournal.java:508)

at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:582)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Connection timed out

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:192)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321)

at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220)

... 14 common frames omitted

Caused by: java.sql.SQLNonTransientConnectionException: Read timed out

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.handleIoException(AbstractQueryProtocol.java:2035)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1520)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1499)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318)

... 15 common frames omitted

Caused by: java.net.SocketTimeoutException: Read timed out

at java.base/java.net.SocketInputStream.socketRead0(Native Method)

at java.base/java.net.SocketInputStream.socketRead(Unknown Source)

at java.base/java.net.SocketInputStream.read(Unknown Source)

at java.base/java.net.SocketInputStream.read(Unknown Source)

at java.base/java.io.FilterInputStream.read(Unknown Source)

at org.mariadb.jdbc.internal.io.input.ReadAheadBufferedStream.fillBuffer(ReadAheadBufferedStream.java:131)

at org.mariadb.jdbc.internal.io.input.ReadAheadBufferedStream.read(ReadAheadBufferedStream.java:104)

at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:247)

at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:218)

at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1518)

```

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [June 12, 2022, 12:47am UTC](https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532/3 "2022-06-12T00:47:33Z")

</div>

I have tried the following which didn’t help:

- Disable use of S&F system, still get same errors

- Updated “connect\_timeout” variable in SQL database servers themselves from 5-100s. No change.

---

<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:** [June 12, 2022, 12:45pm UTC](https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532/4 "2022-06-12T12:45:26Z")

</div>

Consider turning on statement logging on the DB side to obtain the actual statements that are causing the problem. Then run them with “Explain” in your DB’s front end to figure out why they are choking. I suspect there’s some time-consuming action that isn’t obvious from the Ignition side.

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [June 13, 2022, 1:13am UTC](https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532/5 "2022-06-13T01:13:51Z")

</div>

I suspect it is caused by this query:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/7/972f2d4fc4a815b60b42bd29123164b4ada9f7a7.png)

I’ve run the EXPLAIN SELECT statement on the Subquery that is doing the SELECT of ‘ids’, and it is definitely using my clustered index CLIX\_AlarmEvents\_EEP to do the query.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/2/2208714c36b6873600c61cb2f8c679979796d838.png)

Running the query itself is very fast:

```auto
SELECT id FROM AlarmEvents WHERE AlarmEvents.eventtime < @pruneDate;
/* Affected rows: 0 Found rows: 16,054 Warnings: 0 Duration for 3 queries: 0.126 sec. (+ 0.218 sec. network) */

```

Which means the DELETE is the query taking ages, which isn’t surprising.  
I have got an Index on the AlarmEventsData table for the ‘id’ column. So i would have expected deletes purely based on ‘id’ to be fine.

Table CREATE code below:

```auto
CREATE TABLE `AlarmEventData` (
	`id` INT(11) NULL DEFAULT NULL,
	`propname` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`dtype` INT(11) NULL DEFAULT NULL,
	`intvalue` BIGINT(20) NULL DEFAULT NULL,
	`floatvalue` DOUBLE NULL DEFAULT NULL,
	`strvalue` VARCHAR(5000) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	INDEX `AlarmEventDataidndx` (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=TokuDB
;

```

It is curious that the EXPLAIN for the DELETE statement shows no use of a key for the AlarmEventData table, unless i’m interpreting the output wrong… Seems like it has to loop through all 26007244 rows to do the deletion…

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/8/487d610547be72d72096ace3ff07f58b92387e76.png)

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [June 13, 2022, 1:15am UTC](https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532/6 "2022-06-13T01:15:18Z")

</div>

Would it be better to create a PRIMARY KEY for the ‘id’ column on AlarmEventsData table?  
That’s the only way i can think of getting the query manager to use an index. I presume there would be no duplicate values for ‘id’, but not sure if Ignition would complain about the auto incrementing nature of the Primary Key.

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [June 13, 2022, 1:21am UTC](https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532/7 "2022-06-13T01:21:04Z")

</div>

Could be related to [this](https://dba.stackexchange.com/questions/254026/mysql-delete-statement-doesnt-use-index-although-the-same-select-query-does). May need to split DELETE into smaller chunks…  
Seems like something IA should do if possible.  
But i don’t really know if its possible to know what the SQL Optimizer is going to do in advance.

---

<div class="post-metadata">

**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\
**Post date:** [June 13, 2022, 2:34am UTC](https://forum.inductiveautomation.com/t/error-pruning-alarm-journal-data-read-timed-out/60532/8 "2022-06-13T02:34:37Z")

</div>

```auto
DELETE FROM AlarmEventData WHERE id IN (SELECT id FROM AlarmEvents WHERE AlarmEvents.eventtime <= @pruneDate);
/* Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 3 queries: 00:02:10.8 */

```

Execution worked when running directly on the master SQL Server (HeidiSQL GUI)  
But now i’m a little concerned because the replication Slave servers don’t seem to have performed the deletion.

[This thread](https://stackoverflow.com/questions/68905310/mysql-replication-lag-in-slave-due-to-delete-query-row-based-replication) seems to imply that it would be better to split the delete into smaller chunks for two reasons:

1. Ensure the index is used by the query optimizer
2. Give time for the replication slaves to deal with the smaller delete chunk, and any other replication tasks.

Unfortunately it seems that my current version of MariaDB doesn’t allow use of LIMIT in a subquery.  
So it seems we’d have to almost do the Alarm Journal pruning ourselves, instead of getting IA to do it in the background. If we wanted to be able to have full control over it.