Urgent: Critical Issue - Unable to Insert Data into MSSQL Database During Script Execution

[Ramya_R]

Hello Everyone ,

I hope this email finds you well.

I am writing to report an urgent issue that we have encountered in our Ignition application(8.1.35), which is severely impacting our production applications.

Problem Description: While running a script in our Ignition application, we are encountering difficulties in inserting values into our MSSQL database. The following error message is observed:

([default]Tags/OEE/Enterprise/BG4/Plant1/Line1/Machine1/OEE/OEE_Trigger, valueChanged) Error executing tag event script:
Traceback (most recent call last):
File "tagevent:valueChanged", line 58, in valueChanged
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)
at jdk.internal.reflect.GeneratedMethodAccessor79.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO OEE_Data (En, Bg, Plant, Line, Machine, Sam_Start_Time, Sam_end_Time, StopTime, run_time, Off_time, Handling_time, Maching_Time, Partid, total_Count, Rejection, Good_Count, Operating_Time, Operator, idle_Cycle_time, CTE, CTH, CTL, Shift, Hour, downTime, Recess, Datetime_Hour, Shifttime, Shift_Hour) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?), [1, 4, 1, 1, 1, 2024-06-07 15:02:46.873, 2024-06-07 16:03:41.51, 0, 3334, 0, 150, 1186, 2, 4, 0, 4, 5379, 2, 1681, 0, 0, 4, Shift2, 16-17, 2045, 0, 2024-06-07 16:00:00, 510, 2024-06-07 15:00:00.000], DBA1, , false, false)

This error occurs frequently and affects the functionality of our application. It appears to be related to a database update/insert operation in our OEE_Data table.

Steps Taken: We have attempted to troubleshoot the issue internally but have been unable to identify a root cause or implement a resolution. We have verified that the database connection is active and that there are no apparent network or configuration issues. Additionally, we have observed that both CPU and database (DB) usage on our system are consistently at 100%, indicating that our resources are heavily overloaded. As a result, we are encountering difficulties in inserting queries into our MSSQL database.

Request for Urgent Assistance: Given the critical nature of this issue and its impact on our operations, we urgently require your assistance in investigating and resolving this matter. We kindly request your immediate attention to this issue and any guidance or support you can provide to help us resolve it as quickly as possible.

Additional Information:

Ignition Version: 8.1.35
MSSQL Database Version: Microsoft SQL Server 2019
Database Connection Details: Database Connection name is DBA1

I have attached Gateway logs for your reference. Please let us know if you require any further information or assistance from our end. We are available to provide additional details or collaborate with your team to address this issue urgently.

PFA mail id : ar.ramya@ranegroup.com

Thank you for your prompt attention to this matter.

[JordanCClark]

Be warned that the forums are not support. If it is truly urgent you can contact them here:
https://support.inductiveautomation.com/

@Ramya_R

Additionally, we have observed that both CPU and database (DB) usage on our system are consistently at 100%

That said, you have not given any information on your Ignition / DB setup, how often you're trying to write to the DB, etc.
If it's related to this post:

Ignition - High CPU Usage in MS SQL DB

We are using Ignition 8.1.23 version. I have a serious problem with my ignition instance and that their SQL server was hanging on 100%. The Ignition gateway and the database server are in different AWS instances. CPU Suddenly, the CPU usage increments and everything in my server falls down, Data logging stopped in DB like that. Then I have restarted the Gateway/ AWS instances and the CPU usage decrements again and I still cannot find why. This has happened multiple times in the last 2 weeks. …

You didn't give any information there, either.

If you expand the error in the log, there should be a 'Caused by' section. Post the entire error here

My gut tells me your AWS instances are underprovisioned for what you are trying to do.

[Ramya_R]

Hi Jordan,
our infrastructure setup for the Ignition system.

1. Application Server Instance: We have dedicated one AWS instance specifically for hosting the Ignition application. This instance is where the Ignition software is installed and runs.
2. Database Server Instance: Installed separately on another AWS instance.

By maintaining separate servers for the application and the database, we aim to enhance performance, scalability, and maintainability.

In addition to segregating our resources, we've implemented various configurations to ensure secure and efficient communication between the application and database servers. This includes setting up tunnel connections and firewall configurations.

If you have any questions or require further details about our infrastructure setup, please feel free to reach out.

[JordanCClark]

Becaue I sometomes say valuable things.

@JordanCClark

If you expand the error in the log, there should be a 'Caused by' section. Post the entire error here

My gut tells me your AWS instances are underprovisioned for what you are trying to do.

[pturmel]

@Ramya_R

If you have any questions or require further details about our infrastructure setup, please feel free to reach out.

That's not really how forums with volunteers work. Provide as much detail as practical up front, preferably in text form (not only screenshots). If you need someone to work with you "live", contact IA support. They can do that.

[Ramya_R]

Hi pturmel, I have raised a ticket to inductive automation.

[Steve_Laubach]

@Ramya_R

Additionally, we have observed that both CPU and database (DB) usage on our system are consistently at 100%, indicating that our resources are heavily overloaded

It looks like you have a number of tag event scripts triggering scripts with database queries and you're running out of system resources. I agree with @JordanCClark. You probably need more resources on your server. It's possible you could be triggering expensive events to happen more often than you intend.

If you're running your Ignition gateway with 100% in any critical resource it can cause unexpected results in Ignition. I've seen this happen on servers that have insufficient resources to do what they are trying to do.