Error When Polling Data from SQL Queries

Ignition
1

I am encountering an error when trying to poll data from SQL queries in Ignition. This issue has arisen recently, and the queries were previously working fine without any problems.
java.lang.Exception: Error running query:
SQLQuery(query=SELECT top(1) equipmentphase FROM Batch_view_table
order by starttime desc, database=Chrom_DB)@250ms
On: SQL-OPC.Root Container.Label 2.text
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:311)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Read timed out
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:556)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:292)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:893)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:864)
at com.inductiveautomation.factorypmi.application.gateway.SQLQuery.execute(SQLQuery.java:50)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:668)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:308)
... 1 more
Caused by: java.net.SocketTimeoutException: Read timed out
at java.base/sun.nio.ch.NioSocketImpl.timedRead(Unknown Source)
at java.base/sun.nio.ch.NioSocketImpl.implRead(Unknown Source)
at java.base/sun.nio.ch.NioSocketImpl.read(Unknown Source)
at java.base/sun.nio.ch.NioSocketImpl$1.read(Unknown Source)
at java.base/java.net.Socket$SocketInputStream.read(Unknown Source)
at java.base/sun.security.ssl.SSLSocketInputRecord.read(Unknown Source)
at java.base/sun.security.ssl.SSLSocketInputRecord.readHeader(Unknown Source)
at java.base/sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(Unknown Source)
at java.base/sun.security.ssl.SSLSocketImpl.readApplicationRecord(Unknown Source)
at java.base/sun.security.ssl.SSLSocketImpl$AppInputStream.read(Unknown Source)
at java.base/java.io.BufferedInputStream.fill(Unknown Source)
at java.base/java.io.BufferedInputStream.read1(Unknown Source)
at java.base/java.io.BufferedInputStream.read(Unknown Source)
at java.base/sun.net.www.http.HttpClient.parseHTTPHeader(Unknown Source)
at java.base/sun.net.www.http.HttpClient.parseHTTP(Unknown Source)
at java.base/sun.net.www.protocol.http.HttpURLConnection.getInputStream0(Unknown Source)
at java.base/sun.net.www.protocol.http.HttpURLConnection.getInputStream(Unknown Source)
at java.base/java.net.HttpURLConnection.getResponseCode(Unknown Source)
at java.base/sun.net.www.protocol.https.HttpsURLConnectionImpl.getResponseCode(Unknown Source)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:450)
... 8 more

Ignition v8.1.36 (b2024010211)
Java: Azul Systems, Inc. 17.0.8

2

Hi,

You might want to check the quality of the connection to your DB.

As the error message states, the query seems to time out.

Is the status of the DB Connection in the Gateway Valid ? Are there any other logs related to your SQL DB in the Gateway logs ?

3

Vision has a hard 60-second limit on responses from the gateway to the Vision client. Which breaks for queries that take more than 60 seconds. Something about your environment is making these queries take longer than they used to.

If these are history queries, and the tables involved are missing the correct indices, then queries will get slower as data accumulates.

4

I want to poll the SQL data as soon as the data is updated in the SQL rows. My query is
SELECT TOP(1) BatchID, uniqueID, equipmentphase, starttime, endtime, completionstate, RecordID
FROM Batch_view_table
ORDER BY starttime DESC
but it gives an error when I try to fetch data.

mceclip0
mceclip01249×843 73.7 KB

5

Seems pretty clear to me. You don't have legacy database permissions enabled in your project. (And you shouldn't turn that on--use Named Queries instead.)