I recently enabled data pruning in Ignition to manage database size. I've set a pruning age, expecting data older than this age to be deleted automatically. However, I've noticed that the older data is still present in the database.
Here are some details about my setup:
Version Ignition: 7.9
Database type: MySQL
Specific tables or data types I expected to be pruned: tag history
How long did you wait? I think the pruning schedule starts with the time of the change and for a 1 day pruning schedule you might have to wait 24 hours before it deletes anything.
Recommend checking logs, as in the following related thread:
If it's been longer than 10 minutes, the logs might let you know what's wrong. In the linked thread the gateway name had changed but the pruning routine only checks historian tables under the current name's drvid record (presumably to prevent pruning other gateways' data on the same DB).
Thanks for the tip.
I've reinitiated the Pruning settings and like you said, after 10 minutes, it worked.
Since the servers' hard drive was full, I deleted the logs manually as a quick fix. The gateway seems to still want to delete these non existing logs and gives me a bunch of errors.
How can erase these?
You need to delete the corresponding entries in the sqlth_drv table; that's what the historian uses as the 'source of truth' for what data tables exist.
Deleting entries from this table does not appear to drop the corresponding sqlt_data_* partitions, so I think I missed something else. Do we need to manually remove all the tables or is there a way to get the partitioning mechanism to clean it up?
I'm not going to give you a step by step guide on how to maintain your historian database, because my advice to you that you might understand fully might end up consumed by someone a year from now with no idea what they're doing and lead to a fully broken historical database.
Contact support if you want direct advice/step by step.
EDIT: Let me amend a little bit, now that I've had some coffee and re-read your post.
sqlth_drv and sqlth_partitions are how the system knows what historical tables exist, at all. Once you start doing any manipulation by hand you're putting the system into an uncertain state. If you've delete from drv but not partitions, I have no idea what pruning will do, because you're in unsupported territory, but it would not surprise me at all that nothing will clean up "orphaned" tables, even if they are identified in the partitions table. It's a clear design goal of the historian that automatic maintenance only applies when the historian is absolutely certain it's touching its own tables. Multiple gateways could be talking to the same SQL database and be interacting with any number of tables, so if our default behavior in any kind of uncertainty was to start dropping tables people would lose data.
Rephrased: If you choose to take on the process of manually bookkeeping your tables, it is up to you to keep any removed rows from partitions or drv in sync with reality in your actual data tables.
