History data partitioning and MySQL

We have commissioned one Ignition project in Feb 2015 & has Daily & yearly reports.

Recently our customer reported that daily reports are working fine but yearly reports are not working properly. We investigated & found that reports are not working for Year 2016.

Below is the brief summary of how we have configured reports and what problem we are facing:

  1. We have configured the tag history for required tags and data partitioning of history is set for “1 Month”.

  2. We have reviewed the MySQL data tables and we found partitions from Feb 2015 to till date. But we have not found the history table created for June 2016.

  3. When we query for yearly report (period Jan 2016 to Dec 2016) Ignition gives error that “Table test2.sqlt_data_1_2016_06 doesn’t exist” & report does not generate.

  4. Please find attached screenshot of MySQL Tables.

  5. We are using history tag binding to table object (located on Ignition Screen) and the same table data we are exporting to excel to show the report in customised excel template.

  6. Our expectation is that Ignition shall provide the data which is available in the MySQL tables. If Data is not available for June 2016, then it should show N/A or 0. As per attached screenshot, we have data available for Jan 2016 to Nov 2016 except Jun 2016. But Ignition is giving error mentioned in point 3 above and not providing the data to Table object.

  7. This project was commissioned in Feb 2015 & do not have data for Jan 2015. Surprisingly if we select the yearly report for 2015 year (Jan 2015 to Dec 2015) then report gets generated even if there is no data for Jan 2015. It does not give any error and shows 0/NA in Jan 15 data. We get the respective data for Feb 2015 to Dec 2015.

8 ) After further investigation we noticed that Ignition has created a partition for month of Jun 2016 as well. Please refer “test2.sqlth_partitions” table in attached screenshot.

Please let me know the solution for this problem. :prayer: :scratch:

Thank you in advance.

It looks like somehow, the sqlt_data_1_2016_06 table has been dropped. The error is occurring because Ignition expects it to be there based on sqlth_partitions, which has a record for this partition. The solution will be to delete this record from the partitions table using a query such as:

DELETE FROM sqlth_partitions WHERE pname = ‘sqlt_data_1_2016_06’