Database corruption after full restore - is this “normal” still?

I recently preformed a full restore from backup, and got database corruption, resulting in the database being deleted by HA. Seems to happen when HA tries to cull the database at 4am, and has happened using two different backups. I have read that this happens because the backup is taken “live”, and the database might be in the process of being updated at the time. Is this still the case? Is there any other recommended way of taking reliable full system backups? I’m using an HA Blue, so not an SD card issue as such.

Thanks for any pointers.

Use the MariaDB addon. Stop the addon before making a backup.

This will cause quite a few errors in Home Assistant as it cant write to the database but the database backup should succeed.

Thank you for that. I’ll look into it. I automate my backups. Can the add-on be stopped and restarted via automation in some way?

Yes they can:

2 Likes

Do I have to stop mariadb addon when i use this

Home Assistant Supervisor: Create a partial backup.

?

Yes

@tom_l I just came across https://github.com/home-assistant/addons/issues/1353#issuecomment-864385570, which seems to indicate you don’t have to stop mariadb anymore when doing a snapshot. Can anyone confirm this is the case, and that a full backup/restore cycle will work properly? If so, this is another great reason to swap to mariadb!

2 Likes

Nice.

How do you run logical automated MySQL backups?

No need to stop the Addon during Snapshot/Backup is nice, but it will create a physical backup I think. Which is always not so handy compared to a simple MySQL dump (logical backup).

I have no idea on that one. In what way would a logical backup be preferable? I’m just interested in getting the whole HA instance back to how it was at a point in time, and if the physical backup accomplishes that I’m happy. But if there’s a better way…

Have a look at minute 05:06 of Home Assistant: MariaDB.. yes I’ll show you where the files are! - YouTube.

The framework for fixing this in the SQLite DB is being worked on,

1 Like

Oh wow. Never imagined this will happen. That’s great, fabulous news! One reason less to migrate to MySQL :slight_smile:

Any estimations when this will make it into a productive release?

Had a little incident (Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #10 by e-raser) and that unreliable database backup thing is still a major issue in my opinion.

No, from what little I understand it is not a trivial fix. It will take some time.

@tom_l Do you know if there is an event I can listen for as a trigger for completion of a backup so as to trigger a restart of the mariadb addon?

TIA

@rossk if you’re worried about corruption, see my post Database corruption after full restore - is this “normal” still? - #7 by Sddawson above. As far as I know, it is all handled by mariadb.

Well I am worried about it as I had to use restore from a backup I took 2 days ago and on restart was littered with mariadb / recorded errors, can’t start etc.

I ended up loosing my database completely.

I have spend the last 18 months gathering data and making daily backups and this is the first time I have ever had to restore from one and to say I am pissed would be a massive understatement.

I also discovered that my backups have grown from an average 350 mb to over 3gb, as for some reason following a recent home assistant update my automated partial backups have decided to select the media folder all by itself (suspect when they changed the media folder in a recent update).

I lost the whole day yesterday messing with home assistant.

So I am now looking for a way to know when a backup has completed but it seems this is not even possible and the whole backup thing is much neglected.

So having read the above, my own personal experience suggests it’s still an issue.

1 Like

That’s not good to hear! When your backups are taken, do you see the Mariadb messages in the log that are mentioned in the post I linked to? Did you try to restore from a previous backup, other than the latest one?

To be honest I never even looked for these as I never knew this was an issue until I was afflicted by it.

Yes tried a couple of partials and one full all with the same results.

It troubles me somewhat that I have been sitting pretty, thinking look at my well organised and managed backups - all the time never giving any thought as to their usefulness.

Having lost my data now, I am going to investigate the best ways of backing up and clearly simply relying on HA backup function is not sufficient at present.

Thanks for the input