Alarm journal profile creation failure

philippe.tremblay · February 17, 2021, 9:07pm

Hello,
I’m trying to create an alarm journal profile. I left pretty much all the parameter to the default value. My datasource is a PostgreSQL database where I’m already writing to and reading from properly with my gateway scripts and my Vision client scripts. Once I’m done with the journal creation, I get the “successfully created” message.
But the link with the DB for the table creation fails. In my gateway/Status/Log, I get the following error

java.lang.Exception: Error running query: CREATE TABLE alarm_events (“id” SERIAL NOT NULL,“eventid” unspecified,“source” unspecified,“displaypath” unspecified,“priority” int,“eventtype” int,“eventflags” int,“eventtime” timestamp,PRIMARY KEY (“id”))

at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:140)

at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.createTable(DBTableSchema.java:109)

at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.verifyAndUpdate(DBTableSchema.java:91)

at com.inductiveautomation.ignition.gateway.alarming.journal.JournalTables$EventTable.verifyTable(JournalTables.java:66)

at com.inductiveautomation.ignition.gateway.alarming.journal.AlarmJournal.verifyTables(AlarmJournal.java:161)

at com.inductiveautomation.ignition.gateway.alarming.journal.AlarmJournal.tryInit(AlarmJournal.java:141)

at com.inductiveautomation.ignition.gateway.alarming.journal.AlarmJournal.startup(AlarmJournal.java:121)

at com.inductiveautomation.ignition.gateway.alarming.journal.AlarmJournalManager.createJournal(AlarmJournalManager.java:129)

at com.inductiveautomation.ignition.gateway.alarming.journal.AlarmJournalManager$2.recordUpdated(AlarmJournalManager.java:77)

at com.inductiveautomation.ignition.gateway.alarming.journal.AlarmJournalManager$2.recordUpdated(AlarmJournalManager.java:63)

at com.inductiveautomation.ignition.gateway.localdb.PersistenceInterfaceImpl.notifyRecordUpdated(PersistenceInterfaceImpl.java:178)

at com.inductiveautomation.ignition.gateway.redundancy.RedundantPersistenceInterfaceImpl.doNotifyRecordUpdated(RedundantPersistenceInterfaceImpl.java:88)

at com.inductiveautomation.ignition.gateway.redundancy.RedundantPersistenceInterfaceImpl$RecordUpdateListener.recordUpdated(RedundantPersistenceInterfaceImpl.java:295)

at com.inductiveautomation.ignition.gateway.redundancy.RedundantPersistenceInterfaceImpl$RecordUpdateMessage.notify(RedundantPersistenceInterfaceImpl.java:346)

at com.inductiveautomation.ignition.gateway.redundancy.RedundantPersistenceInterfaceImpl$RecordUpdateListener.receiveCall(RedundantPersistenceInterfaceImpl.java:278)

at com.inductiveautomation.ignition.gateway.redundancy.QueueableMessageReceiver.receiveCall(QueueableMessageReceiver.java:47)

at com.inductiveautomation.ignition.gateway.redundancy.RedundancyManagerImpl.dispatchMessage(RedundancyManagerImpl.java:852)

at com.inductiveautomation.ignition.gateway.redundancy.RedundancyManagerImpl$ExecuteTask.run(RedundancyManagerImpl.java:924)

at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$ThrowableCatchingRunnable.run(BasicExecutionEngine.java:518)

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.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.postgresql.util.PSQLException: ERROR: type “unspecified” does not exist Position: 59

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)

at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)

at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)

at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:244)

at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:322)

at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:857)

at com.inductiveautomation.ignition.gateway.db.schema.DBTableSchema.executeCommandSet(DBTableSchema.java:138)

… 24 common frames omitted

Should there be a different datatype instead of “unspecified” in the table creation query?

philippe.tremblay · February 18, 2021, 6:03pm

I found that the “Unspecified” type shown in the error message was for “String” data type. I created the two tables manually in my PostgreSQL database, along with the columns related to “string” datatype. Once this step completed, the journal was able to automatically create the remaining column and initialize it properly.

Now, I’m still having error message. When I try to log an alarm, I get alarm message in my logfile about a “bad value for type INT”. I did a test with an INT tag, even if I assume this should work with any datatype alarm, and I can’t get why this alarm journal autopopluated query is having trouble writing to the DB…

PGriffith · February 18, 2021, 7:38pm

It looks like it’s trying to store the display path to an integer typed column - maybe check your query definitions?

philippe.tremblay · February 22, 2021, 3:35pm

@PGriffith the query definition is managed by Ignition directly, once the alarm is triggered. This is why I don’t understand how something built-in gives an error… Is this a mismatch between PostgreSQL and Ignition for the alarm journal management?