I am trying to set up a redundant system for data storage on two gateways. The idea is to write to a local database, and a remote database (both MYSQL) at the same time. I have it scripted to execute identical named queries, just different connections. Everything works good until one of the databases is in a faulted state (I disabled to test Store and Forward) and the logs show a warning and the script fails.
Is this the expected behavior? I have Store and Forward enabled, and I do believe configured correctly according to manual. Is this setup even feasible for a failsafe setup?
Show your code and error handling. (Post formatted code - not pictures of code.)
Error from the Gateway:
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 18, in runAction at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407) at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173) at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92) at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78) at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:418) at jdk.internal.reflect.GeneratedMethodAccessor64.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: com.inductiveautomation.ignition.gateway.datasource.FaultedDatabaseConnectionException: The database connection 'InventoryManagment2' is FAULTED. See Gateway Status for details.
at org.python.core.Py.JavaError(Py.java:547)
at org.python.core.Py.JavaError(Py.java:538)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:192)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:552)
at org.python.core.PyObject.__call__(PyObject.java:477)
at org.python.core.PyObject.__call__(PyObject.java:481)
at org.python.pycode._pyx123.runAction$1(:20)
at org.python.pycode._pyx123.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:173)
at org.python.core.PyBaseCode.call(PyBaseCode.java:306)
at org.python.core.PyFunction.function___call__(PyFunction.java:474)
at org.python.core.PyFunction.__call__(PyFunction.java:469)
at org.python.core.PyFunction.__call__(PyFunction.java:464)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:846)
at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:828)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:832)
at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:1009)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:897)
at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:158)
at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:97)
at com.inductiveautomation.perspective.gateway.action.ScriptAction.runAction(ScriptAction.java:74)
at com.inductiveautomation.perspective.gateway.action.ActionDecorator.runAction(ActionDecorator.java:18)
at com.inductiveautomation.perspective.gateway.action.SecuredAction.runAction(SecuredAction.java:44)
at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.lambda$call$0(ActionCollection.java:263)
at com.inductiveautomation.perspective.gateway.api.LoggingContext.mdc(LoggingContext.java:54)
at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:252)
at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:221)
at com.inductiveautomation.perspective.gateway.threading.BlockingTaskQueue$TaskWrapper.run(BlockingTaskQueue.java:154)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.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 com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: org.python.core.PyException: java.lang.Exception: java.lang.Exception: com.inductiveautomation.ignition.gateway.datasource.FaultedDatabaseConnectionException: The database connection 'InventoryManagment2' is FAULTED. See Gateway Status for details.
... 34 common frames omitted
Caused by: java.lang.Exception: com.inductiveautomation.ignition.gateway.datasource.FaultedDatabaseConnectionException: The database connection 'InventoryManagment2' is FAULTED. See Gateway Status for details.
at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:407)
at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173)
at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92)
at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:418)
at jdk.internal.reflect.GeneratedMethodAccessor64.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)
... 31 common frames omitted
Caused by: com.inductiveautomation.ignition.gateway.datasource.FaultedDatabaseConnectionException: The database connection 'InventoryManagment2' is FAULTED. See Gateway Status for details.
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnectionImpl(DatasourceManagerImpl.java:211)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:293)
at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.runPrepQuery(NamedQueryExecutor.java:481)
at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:399)
Script that calls the Named Queries:
def runAction(self, event):
partNum = self.getSibling("airgasPart").props.text
partCRM = self.getSibling("CRMPart").props.text
partDesc = self.getSibling("decription").props.text
partUOM = self.getSibling("Dropdown").props.value
partMIN = self.getSibling("partMIN").props.value
partMAX = self.getSibling("partMAX").props.value
# partVendor = self.getSibling("vendorName").props.text
#get selected row from table
selRow = self.getSibling("Table").props.selection.selectedRow
id = self.getSibling("Table").props.selection.data[0].PartNumber
system.db.runNamedQuery('AdjustInventory',{'partNum':partNum,'partCRM':partCRM,'partDesc':partDesc,'partUOM':partUOM,'partMIN':partMIN,'partMAX':partMAX})
system.db.runNamedQuery('AdjustInventory2',{'partNum':partNum,'partCRM':partCRM,'partDesc':partDesc,'partUOM':partUOM,'partMIN':partMIN,'partMAX':partMAX})
self.getSibling('Table').refreshBinding('props.data')
Counting through your code, it appears that line 18 is calling 'AdjustInventory2'. I would expect the first runNamedQuery to work but you indicate that neither work.
I'm not a Python expert but I would have thought you should use
try:
system.db.runNamedQuery('AdjustInventory', ...
except:
pass # or error handling here
try:
system.db.runNamedQuery('AdjustInventory2', ...
except:
pass # or error handling here
If both databases are active and not faulted, the code works as expected and the script finishes. If one of the databases is faulted, the script puts that Warning in the logs and execution stops; neither query is executed and the app stops.
My understanding was that the Store and Forward would handle this and write the data to memory until the connection was reestablished? Even if I comment out one of the named queries and just execute a single one on the faulted database, the script fails and the app does not execute.
If I follow your advice (thank you by the way) and add the error handling code that gave an example of, will the data be placed in the cache? Or will the named query call just be skipped over as it appears in the code you provided?
Thanks!
The script can't know in advance that the latter query is going to fail so I don't understand why the first isn't executed.
On the other hand, I didn't realise that store and forward can apply to regular database queries (other than, say, Ignition's historian inserts, etc.). It appears that each connection has its own store and forward.
https://docs.inductiveautomation.com/pages/viewpage.action?pageId=58599521
I don't think I can help further.
Thank you for your time.
I hope someone from Inductive can shed some light on this also.