Same here. Recorder tried to rebuild the Database and stopped with the same error message.
i won’t start with a new database and lose all the old records.
hopefully there is another way…
No fix here either. The upgrade to 2024.7.2 initiated a massive disk and system load for a few hours, but it got through. But now again every night it locks.
Influx Db recorder keeps working.
changed now to mariadb an plan to migrate old sql database to mariadb the next few days.
after the change to mariadb it got working. Of course, all old data will be lost
You bring up a good point. I suspect the problem could be resolved if you shut down HA, delete the database, and let HA build a new one on restart. I’m not sure if this has been suggested, but for many people that would be an acceptable work-around.
Of course, as you say, you’d lose some historical data. To avoid that, it might be possible to shut down HA and open the database in something like SQLite DB Browser. From there you could a compact or repair. It won’t solve the problem of obsolete database structures, but it would fix any DB damage which was caused by HA crashing.
Has anyone tried either of these?
If your system is resource constrained, you can do an offline table rebuild by copying the database and running the changes in Purge causes recorder to stop writing to the DB until HA is restarted (Auto purge happens at 4:12am) · Issue #117263 · home-assistant/core · GitHub and than copy it back.
1 Like
Today at 4:10 am HA made a new DB, and the old one was renamed to corrupt.
I’ve fixed the old one with this quide - Help with corrupt databases
(it helped me a few times earlier, when power outage caused corrupted DB. But now it became corrupt while HA was running 
)
Now lets see what will happen tonight.
1 Like
Ok, today it just stopped as usual at 4:10, at least the DB was not discarded.
At least for my part, I came to a working solution by just waiting longer than expected…
I was gone for the weekend and didn’t have time to try anything else. Today I noticed, that the rebuilding process of the SQLite table actually finished last night…
I just rebooted HA and now everything is back to normal… There are also no further log entries about the recorder…
may I ask how big is your db and what kind of hardware do you use?
I would like to know if I can expect this to take over a day in my case
I performed a test with a copy of my current db on a fresh docker instance of HA core 2024.7.2. The copy of the states table finished with a success message in the log. So far so good.
After comparing a fresh database that was created by a new HA install with my current live db, I noticed some small discrepancies though.
-
The states table seems to contain some old rows from January that were not purged yet. The rows have the
last_updated DATETIMErow populated, while more recent rows only populatelast_updated_ts FLOAT. Shouldn’t the old entries from January have already been purged? -
My cleanly closed db has the
PRAGMA journal_mode=deletesetting, while newer ones usePRAGMA journal_mode=walby default. Not sure if that’s an issue though.
I already had serious problems with my database when upgrading to the February release. Therefore, I want to be extra cautious this time. I was also wondering if there is some kind of high level documentation of the structure of the sqlite3 database. Would be nice to have a reference to compare the existing database with.
Call me crazy, but it’s ~70GB, because I’m tracking history data for 3 years… 
1 Like
That raises the question, why? 
You can’t be telling me, you’d need such a long period of data in HA. If you want to keep the data, use InfluxDB. It is exactly made for that use case.
HA should be the managing part in your home, saving long term data is not part of that job description. InfluxDB and Grafana to visualize do a far better job in handling these data points, and it doesn’t get laggy after some time.
Not that I’d require you to use it, but it really does not make sense to handle this in HA.
Just saying!
2 Likes
Meaning 2.4 GB/h
So in my case it would be done in one hour.
I decided to skip the last update so I can wait for .7.3. Of .8.0
I know I can revert the skip. But at the moment I will wait a bit.
1 Like
You are absolutely correct…
It’s already on the agenda, just not a high priority right now…
1 Like
Tell me about that…
Same for me. Updated to 2024.7.2 last night, removed the auto_purge: false entry in configuration.yaml but unfortunately the issue still shows up 
I’ve restored 2024.6.4 and moved the new DB to that restored HA.
Now it also stops recording at 4:12.
Any idea how to repair the DB?
Looking at the issue tracker, devs are still working on the migration even after the 7.2 release. Maybe they are still trying to work around edge cases some users are experiencing.
One user reported having an additional foreign key constraint that should not be there. I can’t find the relevant issue though anymore. Sorry.
I’m waiting for the last .x release before I will attempt the migration this time. Loosing history is not a catastrophe, but I would prefer to avoid it, obviously.
Look at some explanations: PSA: 2024.7 recorder problems - #57 by zigomatic
Try to check content of your DB with the extra module sqlite web and see where is the issue.
Also for the update to 2024.7.2 , you need to have db size×2.5 as free disk space.
Is there a place where one could learn more details about the structure of the HA SQLite database? I’m aware it’s kind of documented in the source code. But I’m not the best programmer and the codebase is quite sophisticated.
I’m asking, because I seem to have a lot of outdated rows in the states and events tables. Possibly leftovers from previous migrations? I’m now worried those might cause problems in the future.
1 Like