Duplicate sqlth_te entries?

I was investigating some weird history query results today and stumbled on this fun tag:

MariaDB [ignition]> SELECT * FROM sqlth_te WHERE tagpath = 'edge nodes/demo/352656102543801/dev1/report count';
+-------+---------------------------------------------------+------+----------+-----------+---------------+---------------+
| id    | tagpath                                           | scid | datatype | querymode | created       | retired       |
+-------+---------------------------------------------------+------+----------+-----------+---------------+---------------+
|  5301 | edge nodes/demo/352656102543801/dev1/report count |    2 |        0 |         0 | 1587835131426 |          NULL |
| 29434 | edge nodes/demo/352656102543801/dev1/report count |    2 |        0 |         0 |        487656 | 1616766361122 |
| 29439 | edge nodes/demo/352656102543801/dev1/report count |    2 |        0 |         0 |          2481 | 1616770886795 |
| 30028 | edge nodes/demo/352656102543801/dev1/report count |    2 |        0 |         0 |          2415 | 1617970448112 |
| 30037 | edge nodes/demo/352656102543801/dev1/report count |    2 |        0 |         0 |          2402 | 1617970988858 |
| 36046 | edge nodes/demo/352656102543801/dev1/report count |    2 |        0 |         0 |           446 | 1624476523658 |
| 36047 | edge nodes/demo/352656102543801/dev1/report count |    2 |        0 |         0 |        107316 | 1624476729211 |
| 38245 | edge nodes/demo/352656102543801/dev1/report count |    2 |        0 |         0 |          2429 | 1626193664074 |
+-------+---------------------------------------------------+------+----------+-----------+---------------+---------------+
8 rows in set (0.122 sec)

I can explain the really old timestamps. That was our fault, generating malformed SparkplugB payloads.

I can’t explain the multiple entries.

Is this normal? How might this have happened? Does the historian query all of them when I ask for the history of that tag path?

Are you using the storeTagHistory function? When a value is stored though this function, it expects the value’s quality timestamp to be after the “created” timestamp in the database. One trick is to set all created timestamps to zero after the initial creation of a historical tag in the designer and then restart the tag historian in the gateway. This allows someone to use the storeTagHistory function to backdate any value and not to create a new id with the same tagpath. If not using the designer and just writing the tag to history (virtually), I’m not sure how it works, but might want to have the timestamps from oldest to newest.

Yes, as long as the other column’s values are the same (querymode, datatype), it should put them altogether in one query.

One other important thing to look for, but hopefully would be rare. We had a corrupt tag provider a year ago or so (v8.0.XX), and we deleted the old one and restored from backup and renamed it as the old. It didn’t like that and duplicated all tags in the historian so we had duplicate non-retired historical paths. This caused a significant slow performance of our historian. I created a script to clean this up, but took a while. Sometimes not best to mess with things too much, but we learned a lot.

We aren’t using the storeTagHistory function directly, but I suspect the MQTT Engine module does effectively the same thing when it gets a SparkplugB tag with is_historical=True and is setup with “Store Historical Events” enabled.

Now that we’ve fixed bugs in the source of our Sparkplug messages, I expect it to be very unlikely that we will be receiving retroactive timestamps any more.

Your comment about the duplicate non-retired paths worries me a bit though. I suspect we may have some of those since we have been beating the snot out of MQTT Engine with unusual Sparkplug traffic in the past. I may go looking to see if we have any of those. Clean up is just forcefully retiring one of the paths? Probably should be done while the historian module is disabled…