The following error is filling up my log. Any idea what I can do to stop the problem?
[quote]INFO | jvm 3 | 2012/08/24 12:57:42 | ERROR [MemoryForwardTransaction ] [12:57:42,230]: Error forwarding data: Violation of PRIMARY KEY constraint ‘PK__sqlt_dat__BE126DD1436BFEE3’. Cannot insert duplicate key in object ‘dbo.sqlt_data_1_20120820’. The duplicate key value is (117, 1345834661167).
INFO | jvm 3 | 2012/08/24 12:57:42 | java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint ‘PK__sqlt_dat__BE126DD1436BFEE3’. Cannot insert duplicate key in object ‘dbo.sqlt_data_1_20120820’. The duplicate key value is (117, 1345834661167).
INFO | jvm 3 | 2012/08/24 12:57:42 | at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1132)
INFO | jvm 3 | 2012/08/24 12:57:42 | at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
INFO | jvm 3 | 2012/08/24 12:57:42 | at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeBatch(DelegatingStatement.java:59)
INFO | jvm 3 | 2012/08/24 12:57:42 | at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeBatch(SRConnectionWrapper.java:628)
INFO | jvm 3 | 2012/08/24 12:57:42 | at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.insertTagValues(TagHistoryDatasourceSink.java:805)
INFO | jvm 3 | 2012/08/24 12:57:42 | at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.storeDataToDatasource(TagHistoryDatasourceSink.java:286)
INFO | jvm 3 | 2012/08/24 12:57:42 | at com.inductiveautomation.ignition.gateway.history.sinks.AbstractDatasourceSink.storeToDatasource(AbstractDatasourceSink.java:120)
INFO | jvm 3 | 2012/08/24 12:57:42 | at com.inductiveautomation.ignition.gateway.history.sinks.AbstractDatasourceSink.storeData(AbstractDatasourceSink.java:97)
INFO | jvm 3 | 2012/08/24 12:57:42 | at com.inductiveautomation.ignition.gateway.history.sinks.AggregateSink.storeData(AggregateSink.java:147)
INFO | jvm 3 | 2012/08/24 12:57:42 | at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:134)[/quote]
I saw a situation similar to this recently. I would start looking for multiple tags. There may likely be two , or more, identical tags with the same paths that are logging the same data to the database.
Hey,
Do this:
- Go to Console>Advanced, click the scary link
- Run:
select path, name from sqltag where sqltag_id=117
This should give you the tag that caused that particular error.
- Go find that tag, and look at its history settings.
In particular, I’m interested in:
- Max age - is it unlimited, or limited?
- Deadband - is it set to 0?
- Timestamp source - is it “Value”?
Basically, the database expects only 1 value definition for a given tag, at a given time. We’ve been trying to track down and eliminate various situations that can cause the system to try to store the same value multiple times, which causes this error. Certain values of the settings mentioned above can lead to this.
Back in the advanced query tab, you an also try running:
select name,path, ownerid, providerid, tagtype from sqltag group by providerid,name,path,ownerid,tagtype having count(*)>1
Do you get any results? If so, these are “duplicates” that may be causing the issue, and I can provide you with a tool to clean them up.
Regards,
The query “select path, name from sqltag where sqltag_id=117” returned “No rows returned” (perhaps due to the fact I had just restarted Ignition. However, by looking at my SQL tables, specifically sqlth.te, I think I have identified the tag and it has the following historical settings.
Max time between records - unlimited
Deadband - 0.01
Timestamp source - System
The second query “select name,path, ownerid, providerid, tagtype from sqltag group by providerid,name,path,ownerid,tagtype having count(*)>1” would not run until I removed the reference to ownerid. After I removed that it returned a large list of tags.
Sorry, it was the end of the day, and I don’t know what I was thinking about- the tag id 117 isn’t the internal id, but the id from the sqltag history tag table. You would need to run (in your external database): “select * from sqlth_te where id=117”.
As for owner id, I didn’t ask what version you were running, but anything previous to 7.4 wouldn’t have that column. On the plus side, I think that in non-udt based systems, we can clean up the internal db a bit more easily, if that’s really the cause of the problem.
So, try looking for the bad tag in the database, and then go back and run the query in the internal database, and see if he shows up in the list. I would probably also add a “count(*)” to that query, so we can see how many copies of each tag there are. Maybe you can post a screen shot of the result of that, or copy and paste the page.
Regards,
Actually, I have been getting this error for other tags in addition to id=117. All of them show up in the query. I didn’t attach the results of the query because it is quite large. There are hundreds of tags with count>1. Most have a count of 2 while some have a count of three and one (117) has a count of 4.
I was going to upgrade to version 7.5 but will hold off until we fix this problem if you feel it will be easier.
I think you can try deleting the duplicates yourself, and see if that helps the situation. If not, the root cause might be something different, and we can try to track that down (in which case, upgrading to 7.5 would be a good first step).
To clean them up:
- Make a gateway backup!
- Look at this thread. There are queries at the end. These queries will remove the oldest duplicate for all tags that have duplicates. There’s a big warning there, because that query wouldn’t be correct for 7.4+ (well, if udts were being used). In your case, it’s ok.
- Run the first query multiple times, to delete all of the properties for duplicate tags. The “having” clause will prevent it from deleting all of the instances.
- Run the second query multiple times to delete the tags.
(Note: the point of #3 & #4 is that properties must be deleted before tag definitions).
Once done, restart your gateway. Let me know if the errors continue after this.
Regards,
The first query ran cleanly with 9602 rows affected! Ran it again and zero rows were affected. I then tried to run the second query but the console is spitting out the following error message:
“SQL: delete from sqltag where sqltag_id in (select min(sqltag_id) from sqltag group by path,name, providerid, tagtype having count(*)>1)
See log for details.”
When I look in the wrapper I find nothing. Any ideas?
Ah, there are two other tables with foreign keys that might be causing a problem. Try these two before running that query:
[code]delete from alertstate where tagid in (select min(sqltag_id) from sqltag group by path,name, providerid, tagtype having count(*)>1)
delete from tagpermissionrecord where tagid in (select min(sqltag_id) from sqltag group by path,name, providerid, tagtype having count(*)>1)[/code]
Regards,
That was a pain. I don’t know if it was because users were using the system, but I had to run all four queries in order multiple times before I got all of the duplicate records.
Next step will be to upgrade to 7.5.2
Thank you.
In theory you should have had to run them 3 times, since one tag have 4 instances. Did that make an impact on the history error?
Regards,
