Lots of problems after restoring snapshot. What to do?

Yes. Just delete the existing and restart Home Assistant.

1 Like

I can confirm. it doesn’t survive restore I executed too.
Just for a record: Don’t you know this issue filed in github and being worked on?

BTW all graphs works. only history page can’t load. Will it fix on its own after purge_keep_days at the latest?

I am strongly wondering how everyone can accept the “just delete that damn sqlite db and your good to go!” workaround so all history data is gone.

I restored a snapshot and ended up in the same situation, things didn´t work. I tested it 3 times, in every test case the SQLite database got corrupted. Either during backup (creating the snapshot) or restore process.

Just not acceptable to me to loose all my history data every now and then. That´s not a reliable backup and restore progress at all!

Would be really nice to see some core devs working on that issue because it´s a MASSIVE one and widely spread, see all the topics. For me personal I think I´m gonna switch to MySQL, i don´t trust that SQLite stuff anymore.

1 Like

No need to cross post exactly the same thing.

You´re right. There also should be no need to have hundreds of topics which have all the same root cause: a malformed SQLite database.

I´ll have a look at it and if I find indications valid enough to bring them on the table I´ll open a GitHub issue for this. I mean it´s just basic stuff, we should be able to trust the snapshots.

Just FYI: Fix corrupted / malformed SQLite database (home-assistant_v2.db)

As I said in the other post, you will get no traction here. Post an issue on github. I had a look, surprisingly no one feels as strongly as you. There is no existing issue. So it’s up to you to make your voice herd.

you could at least give the link to original post xposted here. I didn’t see it
TBH using arguments about xposting or other formal things instead of supporting the issue reporter doesn’t help HA development.

Also there are mods who can merge doubled posts if they decide so. I Don’t understand why you are adding unnecessary side-topic to discussion

You also have to consider that history while not important to you, might be crucial for others. At the end this is what the history is designed for.

But the fact alone that history doesn’t survive snapshot/restore is indication that something is wrong. I suppose it might be consequence if sqllite being no transactional rdbms. but if there is no way to solve that then it should be stressed enough in docs

1 Like

I gave help towards getting a solution in my post. Read it again. Maybe get someone to help you if you have trouble with the English language.

Why would it be crucial? It is only storing the states…

Cannot be those states important?
For me those are important when tracing back some issues or building automations. If I lost history I lost not only data but the exact sample which might be hard to reproduce.
I assume there are persons who might have different needs.

Explain please because none of that makes much sense

I could but I have a feeling it’s futile. Those are MY data and it’s pretty normal that I might want to not lost them. Doen’t matter what do they contain.

Just inagine you are losing files from your pc. Are you expecting someone will argue an importance of lost content or you would rather get your computer fixed?

I store the data I want to keep long time in influxdb. So it does not matter if sometime I have to delete my HA database.

it’s for you.
Keep in mind that some people are looking for reliable tool, not something which “must be turned off and on to work”. (regardless it’s free, open source, community driven or other dev model - in case you would like to mention it).

If default database is considered unstable amd unreliable, it should be stated tin documentation at least.
From sw development pov it’s wrong if something fails. Even if some users are ok with that.

Merry Christmas :slight_smile:

1 Like

If it is so important to you, have you done anything about it?

You could have done this six times over by now instead of moaning here (which does nothing):

1 Like

If you set up a Maria db in a separate container or on a separate system, data corruption is less likely. I’ve been running with an external Mariadb on my symbology for ~4 years and I’ve never lost my database when loading snapshots.

It is as you said because such external db is not part of backup created by by suprvisor, isn’t it?

By rule of thumb no database should be backed up by copying files between filesystems when db is alive. It ussually leads to data inconsistency, therefore damaged database.

HA devs ignored this fact adding db files to the backup which is being created when system is up and running.
It should be resolved somehow (don’t know sqllite, but other rdbms ussually provides consistent backup mechanisms) or communicated clearly that the sqllite is not reliable for this usecase. (or maybe sql lite should not be backed up at all).

Anyway sqllite has been chosen by devs as out-of-the-box solution. Therefore it gives impression to the general users that this part of the system and related features is tested and reliable as expected.

BTW another rootcause might lay in using ORM which maps data to objects. In case of inconsistent data it likely ends with inability to create complete object resulting general failures. I’m not even touching performance issues when using ORMs

1 Like

Even this is old, I would second this. Esp. with the new long term statistics, energy, etc. If I always loose the whole histroy, esp. in this areas, we should directly remove month or year in the selection.

image

:wink:

Referring to this, is there a FR or github entry or whatever, for which to vote or keep track of improvements (I would call it bugfixing) of this db-backup/restore-process?

As stated previously in this thread, move your database outside of the configuration folder or exclude it from your backups. If your database integrity is important to you, move to the mariadb addon and you’ll avoid this issue all together.