Lost Historian Data Visualization (possibly after system name change)

Hello,

I submitted a ticket for this but thought I would try the forum to get the answer quicker.

Not sure if this has anything to do with me trying to change the ‘System Name’ in Gateway -> Config -> Gateway Settings but an easy chart is only displaying a small window of past historian data for a tag that increments each time a sequence is complete (current value is 289402). I changed the system name back to what it was but new data is still not showing.

If I look at the sqlth_te table, there are 3 rows for the tag with the tagpath in question (configured with Sample Mode = On Change), each with a different (tag)id (1400, 3325 & 3448) and scid’s (4, 11, 15 respectively).

If I look at each of the 3 sqlt.data tables for this month:

  1. _1_2020_03
    • tagid 1400 has a bunch of rows but the latest row was 2 days ago with a value of 286425
    • tagid 3325 has no rows
    • tagid 3448 has no rows
  2. _4_2020_03
    • tagid 1400 has 3 rows, latest row was 12 days ago with a value of 279364
    • tagid 3325 has 3 rows, latest row was 12 days ago with a value of 279361
    • tagid 3448 has no rows
  3. _7_2020_03
    • tagid 1400 has a bunch of rows and the latest row is today with a value of 289402 (current tag value)
    • tagid 3325 has no rows
    • tagid 3448 has 14 rows, latest row was 12 days ago with a value of 279375 (this is the data the easy chart is displaying)

What can I do to get the easy chart displaying the past and new data again?

I would really like to change the system name to something more meaningful so our platform isn’t ‘janky’ to new employees, what considerations do I need to make before doing so?

1, 4, and 7 tie to entries in sqlth_drv. Changing the gateway name should automatically update the entry for your gateway in that table, and history should continue seamlessly. If it hasn’t, then you’re at least somewhat hosed. Support’s got access to a tool for internal use that can help with data migration and cleanup; that’s probably the easiest option to fix things for you.

In general, if what you have is a single gateway logging into a single schema in the DB, then you should have ~2 rows in sqlth_drv; one with null for the provider and one with default. If you’ve made any other realtime tag providers, they’ll also be there. There should not be extra rows with different gateway names, unless you have multiple gateways storing into the same schema in a DB.

this happened to me the other day when I migrated to a new server. I wanted to update that setting to match the new server name. I was in a rush so I just restored a previous DB and GWBK and started the process over, but it definitely added a entry in the sqlth_drv from what I remember and hosed historical data. I am sure it can be fixed with a little sql magic.

Had the same thing happen to us on a server migration.
Wanted to rename the gateway to a better name and lost all trending.
Is there a record in the internal DB that also points to the provider name?
This is something that needs to be able to be done without losing the trend data.

I did the same as diat and just redid the migration while keeping the unwanted gateway name.
:expressionless:

Did you do the rename after the restore, or as part of the restore? The latter I wouldn’t expect to work, but the former should - the tag historian module has to be up and running to ‘react’ to the gateway name change and update the record in sqlth_drv.

After the restore.
No matter what I did in the database it would NOT link back to the old data.
New data started recording with the new name and same tag paths but the old data was inaccessible.
I had to rename it back, shut down the gateway, do the database restore again and then start the gateway to get the old data back.

Kevin Allen from IA support had me change the system name to what I wanted then had me send them screenshots of what was in the sqlth_drv, sqlth_partitions and sqlth_scinfo tables, from there they provided me with 9 queries to run which got everything back to normal!

1 Like

Were any of those internal DB queries or were they all against the SQL Server?

I ran them all directly through SQL Server Management Studio as Kevin was concerned that executing certain queries though the designer’s ‘Database Query Browser’ would time out. Rightly so, a query I ran that copied all the data from ‘sqlt_data_1_2020_03’ to ‘sqlt_data_7_2020_03’ took nearly 10 minutes!

Definitely the latter. The historian doesn't track any state within the internal DB, beyond the base configuration settings (whether to partition, how often to prune, etc). That's by design, so that any Ignition system can point to a DB containing historical data and query from it successfully.

Yeah I figured that when he replied that he had him copy data from one partition to another.
That would take all day for some of ours as we have a TON of data.
Wouldn’t just renaming the table work just as well?

We are facing the similar issue with tag history right now as we migrated the server and renamed the gateway at the time of restore. Now there are two IDs for each provider with same gateway name in sqlth_drv table. Please see the screenshot. id=4 and id=12 are pointing to same gateway and provider.

We found duplicate records in sqlth_te table with latest scid. Therefore we updated the earlier records in sqlth_te table with latest scid. It worked fine (we could retrieve the tag history) until the gateway was restarted after few days. This time new records were added in sqlth_te with the old scid.

@nminchin / @cmallonee /@PGriffith
Please suggest if updating any records in drv or scinfo tables will fix the issue. If yes, which ones?

sqlth_scinfo

You should really contact support, so they can look at your system live and figure out the best path forward.

The gist is that you need to only have one set of records in sqlth_drv for each gateway name. As long as you have multiple, issues will keep cropping up. However, migrating/merging the history is non-trivial.