In one of our Ignition projects, the Gateway keeps logging the following error:
[i]
06:35:13 MemoryForwardTransaction Error forwarding data: Duplicate entry ‘24062-1416977712044’ for key ‘PRIMARY’
java.sql.BatchUpdateException: Duplicate entry ‘24062-1416977712044’ for key ‘PRIMARY’
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeBatch(DelegatingStatement.java:59)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeBatch(SRConnectionWrapper.java:682)
at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.insertTagValues(TagHistoryDatasourceSink.java:920)
at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.storeScanClassSet(TagHistoryDatasourceSink.java:343)
at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.storeDataToDatasource(TagHistoryDatasourceSink.java:327)
at com.inductiveautomation.ignition.gateway.history.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:123)
at com.inductiveautomation.ignition.gateway.history.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:99)
at com.inductiveautomation.ignition.gateway.history.sinks.AggregateSink.storeData(AggregateSink.java:147)
at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:138)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘24062-1416977712044’ for key ‘PRIMARY’
at sun.reflect.GeneratedConstructorAccessor47.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1040)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2794)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)
… 11 more
[/i]
Looks like the system is working fine but it would be good to know the root cause of this error and to stop it if possible.
The Gateway is version 7.7.1 and runs on a Win2012 R2 Server Standard. The data base is a MySQL.
Does anyone have the same issue? Have you found the solution?.
Looks like you’re trying to write a value to the column ‘PRIMARY’ that is not unique. That column is most likely your primary key column and you can’t have values in a primary key column that are not unique.
Check how you are generating whatever this value is: ‘24062-1416977712044’
If it’s something that won’t be unique for every record then either it can’t be your primary key or you need to find a different way to generate it so it will be (timestamps or something of the like).
I am having a similar issue but this error also appears with some Quarantined data. I believe this error is being created by Ignition’s Tag History feature which I don’t really know how to troubleshoot. Nothing I’ve created is adding an entry such as ‘2661-1437419362783’ to the ‘PRIMARY’ key of any table.
We have the same issue at each failover on a redundancy platform.
On failover, the master and the backup Gateway tried to write same Tag history values with same timestamp in the sqlt_data_XXX table (the PrimaryKey of this table is tagid and timestamp).
For me , it’s unfortunate that Ignition doesn’t skip this error because it’s not a failure or doesn’t check before insert in sql.
Historian's data tables are keyed off the tagid (11464) and the timestamp (154177etc). Something in your setup is causing duplicate records for the same tag to be driven at the same timestamp. This wouldn't/couldn't/shouldn't/ ever happen with a standard historian setup - are you using any more complex features (system.tag.storeTagHistory) or do you have multiple providers, splitters, gateway network, etc? You may just want to get in contact with support so they can take a look.
I have had a ticket in on this and we have been working on a solution. But…
Any idea why this happens? I have tags from an Edge gateway synchronizing to a tag splitter (in 8.07.) Why it tries to duplicate the entries has us baffled. Just want to see if there was any solution through this thread.
We’ve had this happen with internal databases when we restore from a Gateway backup which came from a Gateway which is still running. In our case, Alarm History was the primary culprit, but in theory it could be any table.
We have the same problem with datas witch came from a Ignition Edge Gateway. This data is being quarantined and we can not see the data updated…
There is something we can do to fix it?
Is this going to be fixed in future updates?
SQLTag History Data Duplicate entry ‘90-1583308997743’ for key ‘PRIMARY’
In your case you will need to either stop one of the Gateways so that you don’t have the duplication, or you could potentially change the storage structure of your Tag and Alarm History so that different keys are used.
Essentially, as long as you have the same tags or same alarms attempting to store their history or state from two Gateways you’re going to continue to experience this.
Thanks for the reply. The odd thing is that I stopped and uninstalled Ignition on the old server, and yet I am getting the same error. We do have an Edge gateway that is being used as a fallback in the event that the master gateway drops connection, however the tag historian module is not installed on that gateway.
All of the entries that were rejected while both servers were running are stuck in quarantine, and Ignition keeps trying to send them. Discard these from quarantine and you shouldn’t see any more.
Yes, as @pturmel said, the existing records which encountered the issue originally will still periodically have a process run which attempts to clean them up. Removing the duplicate Gateway will only prevent new duplicates.
Hi guys, thanks for the follow-up again. I removed all the quarantined entries, however I am still getting the error when I enable the history option for both new and existing tags.
Update: I created a new scheme and configured a new database connection on the gateway, and selected it as the storage provider. I am still getting the error in the logs, and they are now referencing the new schema. It seems to me that this should have corrected the issue if there are in fact two gateways running the same project. Please correct me if I am wrong.
I checked all the development servers to make sure that there is not another gateway running, and I can verify that there is not (aside from the Edge gateway mentioned earlier). I am thinking about disabling the fallback app to test, as I am running out of ideas. This is on an active production machine so I have to be careful with my approach.
I should mention that the main gateway is running in a virtual environment. Host is VMware ESXI 7.0 and guest is running Windows Server 2019.
Any other ideas would be greatly appreciated.
Edit: I don’t have the alarm notification module installed on the main gateway
I have a similar issue with Edge and Central Server (both 8.0.15). After I restart the central gateway, the tag historian stops moving data to the database.
