Hi! I have an issue regarding the Default historian tag. I configured the History tag to save the data to a MySQL database. I have configured the data partionining so that the tag would create a new table every day and store data in it every day. It seemed to work fine for some time until yesterday (9th of October) when the data was still written to the previous day table (8th of October). I checked that the system and gatewaye date / time are the same so I don't think that is the issue. I could solve it eventually by changing the partition units to 1 min and then back to 1 day. At this point it created the corresponding 1 min table as well as the 1 day table for 9th of October.
However my question is what happened there and how can one avoid this in the future?
Ignition's history tables are aligned to whatever the partition unit is from when the system starts up/first stores history. They don't go backwards or forwards to align to arbitrary "wall clock" times, which would invite problems with timezones and the like.
In other words,
If you start a system with daily partitions on 2024-10-9 at 13:28 local time,
I would not expect your next "daily" partition until 2024-10-10 at ~13:28 local time.
You can look in the sqlth_drv table to see the unix timestamps assigned to each table partition - those are what actually matters, not the name assigned to the table.
While there are ways to force the system to align with your expectations, I would recommend you don't, for the simple reason that you shouldn't care. The only supported way to query data out of the historian is via Ignition, and we will automatically bridge across as many tables as needed. Partitioning by arbitrary 24 hour slices will still segment the data and lead to approximately the same table sizes.
Thanks for the reply!
I will then try to check again in 24 hours after having started my system and check if the new table has been created. Will come back with details.
I'm experiencing a similar issue.
My database is partitioned weekly, but I've noticed that Ignition SQLTag History is trying to reference a very old table from 2012, which results in the following error:
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: sqlt_data_1_20121013)
I have no idea why the system is attempting to access data from that far back — currently, there are no tags or queries that should be reaching that far into the past.
I'm wondering:
- How can I prevent this from happening again in the future?
- Is there a way to “clean up” or reset the tag history metadata so that Ignition doesn’t try to access non-existent partitions?
Thanks in advance for any suggestions!
Like the previous poster, I changed the database partitioning period and then reverted it back. The problem disappeared for a while but has now returned.
Well, something is. We're not inventing that date out of thin air.
You can manually clean up the sqlth_partitions table, which is the 'index' of history tables the rest of the system relies on. At that point, whatever is issuing this query for 2012 data may error out more eagerly.
Could it also be an OPC Timestamp from 2012 that is trying to be written to the DB?
My partition table looks as follows:
| pname | drvid | start_time | end_time | blocksize | flags |
|---|---|---|---|---|---|
| sqlt_data_1_20250423 | 1 | 1745419500654 | 1746024300654 | 0 | null |
| sqlt_data_1_20250430 | 1 | 1746024301597 | 1746629101597 | 0 | null |
| sqlt_data_1_20250507 | 1 | 1746629103420 | 1747233903420 | 0 | null |
| sqlt_data_1_20250514 | 1 | 1747233903628 | 1747838703628 | 0 | null |
| sqlt_data_1_20250521 | 1 | 1747838708870 | 1746050399999 | 0 | null |
| sqlt_data_1_2025_05 | 1 | 1746050400000 | 1748009300593 | 0 | null |
| sqlt_data_1_20250523 | 1 | 1748009301077 | 1748614101077 | 0 | null |
| sqlt_data_1_20250523 | 1 | 1748009300594 | 1748009301077 | 0 | null |
After converting the timestamp:
| pname | drvid | start_time | end_time | blocksize | flags |
|---|---|---|---|---|---|
| sqlt_data_1_20250423 | 1 | 2025-04-23 16:45 | 2025-04-30 16:45 | 0 | null |
| sqlt_data_1_20250430 | 1 | 2025-04-30 16:45 | 2025-05-07 16:45 | 0 | null |
| sqlt_data_1_20250507 | 1 | 2025-05-07 16:45 | 2025-05-14 16:45 | 0 | null |
| sqlt_data_1_20250514 | 1 | 2025-05-14 16:45 | 2025-05-21 16:45 | 0 | null |
| sqlt_data_1_20250521 | 1 | 2025-05-21 16:45 | 2025-05-01 00:00 | 0 | null |
| sqlt_data_1_2025_05 | 1 | 2025-05-01 00:00 | 2025-05-23 16:08 | 0 | null |
| sqlt_data_1_20250523 | 1 | 2025-05-23 16:08 | 2025-05-30 16:08 | 0 | null |
| sqlt_data_1_20250523 | 1 | 2025-05-23 16:08 | 2025-05-23 16:08 | 0 | null |
I would like to fix the table in the following way (delete the last row, correct the end_time of the 5th row according to the last entry in the sqlt_data_1_20250521 table):
| pname | drvid | start_time | end_time | blocksize | flags |
|---|---|---|---|---|---|
| sqlt_data_1_20250423 | 1 | 1745419500654 | 1746024300654 | 0 | null |
| sqlt_data_1_20250430 | 1 | 1746024301597 | 1746629101597 | 0 | null |
| sqlt_data_1_20250507 | 1 | 1746629103420 | 1747233903420 | 0 | null |
| sqlt_data_1_20250514 | 1 | 1747233903628 | 1747838703628 | 0 | null |
| sqlt_data_1_20250521 | 1 | 1747838708870 | 1748006912068 | 0 | null |
| sqlt_data_1_2025_05 | 1 | 1746050400000 | 1748009300593 | 0 | null |
| sqlt_data_1_20250523 | 1 | 1748009301077 | 1748614101077 | 0 | null |
| sqlt_data_1_20250523 | 1 | 1748009300594 | 1748009301077 | 0 | null |
- After making these changes, do I need to restart the Gateway?
- Is there any way to prevent incorrect dates from being inserted into this database? I haven’t found a solution.
I made the changes without restarting. Everything is working correctly. Only question number 2 remains.