Issues with Maria DB after crash and future recordings

Hi all,

I experience issues with my Database, which most likely occurred after a HA crash. I use Maria DB.

Issues:

  1. My Energy statistics show wrong values at a specific times. I tried restoring an older backup and there actually was an entry with something 0,x kWh (which is future from the backup on 2024-07-20).

  2. Energy statistics do not update after that time (2024-07-20 21-22h).

  3. Preview statistics of certain entities either show wrong values, which go into the future or do not show any preview at all. From the older Backup there were values, which went from 2024-07-20 until 2024-07-27 (future from backup). No issues though, if I use show more function. It seems to affect entities that update quite frequently like solar or heat pump.
    3 quick view statistics
    4 quick view statistics

Analyze and measures:
recorder_runs table:
First Entry of recorder_runs table shows wrong start date. It starts 1 Month after it ends, which most likely caused all the issues.
I tried both changing the value of the start date to 2024-06-30 and also tried to delete the entry, which didn’t work out.

statistics_runs table:
Table has entries that go until future date 2024-07-27 18:55:00 and stop there.
I didn’t experiment with that table.

statistics table:
Crash occured on 2024-06-30 at 16:00:10 (last entry at 1719756010.393281)
I restarted the system at 19:18:42 (first entry at 1719767922.2774086)

statistics, states, statistics_short_term and events tables:
Concluding from the recorder_runs table I tried to look for and delete future data from 2024-07-30 19:20:00 and onwards. By doing so, the data in the statistics tab looks normal, but updating statistics stops after that. Waiting doesn’t help. Also preview statistics of entities in lovelace simply shows “No statistics found.”.
I also tried deleting all entries on 2024-06-30 16:00 - 23:59:59 Uhr (before and after the crash occured). Issues are similar as the measures above.
Deleting Data from 2024-07-20 (when the faulty entry in the energy tab occurs) and onwards didn’t help and resulted in same issues as already stated.
8. delete future entries

optimizing, reparing, checking tables with myphpadmin:
I tried optimizing, repairing and checking the tables with myphpadmin, which unfortunately didn’t work. Before and after deleting data, that didn’t help.

hopes:
Since preview statistics show wrong values until future date 2024-07-27 and wrong future statistics were recorded on future date 2024-07-30, I hope that after that date my statistics and preview statistics will work “normally” again.
Maybe there is a way to correct the wrong future data, because deleting it seems to be more disruptive.
I have no Idea how to fix the preview statistics. I already tried things like going to developer tab and fix some statistics, but it seems not to help. I’m also not sure if readding integrations will help, but that is no real solution for me, since I have > 1000 entities and checking them manually is no solution for me.
Either it’s possible to fix this issue some how or I need to start a new Database. Maybe switching back to the standard HA database, but it’s a pity that my energy data will be gone from beginning of the year.

BR
mau

I just found out, that I updated my NUC BIOS and either I somehow managed to change the date or it was done somehow by the update.

Hi Igor,

I hope that means you found your problem.

I was one of the advocates of using maria a few years ago. I have long ago switched back to the default sqlite because the code it now highly optimized to use the default DB It is faster and more efficient that other DB’s based on the HA optimizations.

What you have is still viable, don’t get me wrong, but I still recommend switching. If you want to switch and keep your data, there is a topic here in the forums that can help with the conversion.

Hi @Sir_Goodenough,
Before I switch, I would like to migrate my (hopefully fixable) data.
Which unfortunately means, I haven’t found a fix yet.
I tried to manually clean the data via Dev Tools → Statistics, but still neither Energy Data statistics update nor statistics preview is shown for certain entities.

BR
mau