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

Do I have to stop mariadb addon when i use this

Home Assistant Supervisor: Create a partial backup.



@tom_l I just came across, 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!



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?


@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

OK, just checked last nights backup and can see the following on the mariadb logs:

[00:30:03] INFO: Lock tables using mariadb client…
[00:30:03] INFO: MariaDB tables locked.
[00:30:13] INFO: MariaDB tables unlocked.

So at this stage have no reason to think that this was not happening previously!

Yes, I’ve been “sitting pretty” for ages now too (or perhaps not). Could be worth posting in the thread I linked, or some other Mariadb area.

Answer to topic question: “No, it’s likely not normal (anymore)”. Because of a restore experience:

Seems this is working meanwhile. Had to restore a HA backup and database looks fine so far. Details @ Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #23 by e-raser.

Please see Fix corrupted / malformed SQLite database (home-assistant_v2.db) - #27 by e-raser containing great news :partying_face: