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

tom_l · October 31, 2021, 12:24am

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.

Sddawson · October 31, 2021, 12:48am

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?

tom_l · October 31, 2021, 1:03am

Yes they can:

ha_user22323 · October 31, 2021, 9:51am

Do I have to stop mariadb addon when i use this

Home Assistant Supervisor: Create a partial backup.

?

tom_l · October 31, 2021, 10:21am

Yes

Sddawson · November 26, 2021, 6:09am

@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!

tom_l · November 26, 2021, 6:31am

Nice.

e-raser · December 20, 2021, 1:01pm

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).

Sddawson · December 20, 2021, 7:46pm

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…

e-raser · December 20, 2021, 8:24pm

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

tom_l · December 20, 2021, 9:29pm

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

github.com/home-assistant/core

Allow to lock SQLite database during backup

home-assistant:dev ← agners:ha-core-backup-notification

opened 09:58PM - 02 Dec 21 UTC

agners

+310 -11

## Breaking change  ## Proposed change  This implements two new Websocket API endpoints `backup/start` and `backup/end`. Those are meant to notify the Core when the Supervisor is creating a backup of the Core. Since backups are done while the Core is running, this allows the Core to make sure all files in the `config` directory are in a safe state to read. Until now, the recorder continued to write to the database file while the backup is being made. This can lead to corrupted database files in the backup. With this implementation, the recorder locks the database using `BEGIN IMMEDIATE`, which essentially prevents writes from any Database connection while keeping the database available for reads. The implementation also halts the recorders queue so that no database inserts are even attempted. This method is described as "historical" method in the SQLite documentation. Developers also confirmed that this method is still save to use and also works with WAL enabled. https://www.sqlite.org/backup.html https://sqlite.org/forum/forumpost/1307e369863b7b24?t=h Note: Currently the `backup/start` and `backup/end` endpoints are implemented in the recorder directly. In case other components need to know when a backup is created, the Websocket API endpoint should be implemented as part of the `hassio` component or similar. ## Type of change  - [ ] Dependency upgrade - [ ] Bugfix (non-breaking change which fixes an issue) - [ ] New integration (thank you!) - [x] New feature (which adds functionality to an existing integration) - [ ] Breaking change (fix/feature causing existing functionality to break) - [ ] Code quality improvements to existing code or addition of tests ## Additional information  - This PR fixes or closes issue: fixes # - This PR is related to issue: - Link to documentation pull request: ## Checklist  - [x] The code change is tested and works locally. - [x] Local tests pass. **Your PR cannot be merged unless tests pass** - [ ] There is no commented out code in this PR. - [ ] I have followed the [development checklist][dev-checklist] - [ ] The code has been formatted using Black (`black --fast homeassistant tests`) - [ ] Tests have been added to verify that the new code works. If user exposed functionality or configuration variables are added/changed: - [ ] Documentation added/updated for [www.home-assistant.io][docs-repository] If the code communicates with devices, web services, or third-party tools: - [ ] The [manifest file][manifest-docs] has all fields filled out correctly. Updated and included derived files by running: `python3 -m script.hassfest`. - [ ] New or updated dependencies have been added to `requirements_all.txt`. Updated by running `python3 -m script.gen_requirements_all`. - [ ] For the updated dependencies - a link to the changelog, or at minimum a diff between library versions is added to the PR description. - [ ] Untested files have been added to `.coveragerc`. The integration reached or maintains the following [Integration Quality Scale][quality-scale]:  - [ ] No score or internal - [ ] 🥈 Silver - [ ] 🥇 Gold - [ ] 🏆 Platinum  To help with the load of incoming pull requests: - [ ] I have reviewed two other [open pull requests][prs] in this repository. [prs]: https://github.com/home-assistant/core/pulls?q=is%3Aopen+is%3Apr+-author%3A%40me+-draft%3Atrue+-label%3Awaiting-for-upstream+sort%3Acreated-desc+review%3Anone  [dev-checklist]: https://developers.home-assistant.io/docs/en/development_checklist.html [manifest-docs]: https://developers.home-assistant.io/docs/en/creating_integration_manifest.html [quality-scale]: https://developers.home-assistant.io/docs/en/next/integration_quality_scale_index.html [docs-repository]: https://github.com/home-assistant/home-assistant.io

e-raser · December 21, 2021, 10:04am

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

e-raser · February 4, 2022, 12:33am

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.

tom_l · February 4, 2022, 12:46am

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

rossk · June 4, 2022, 9:17pm

@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

Sddawson · June 5, 2022, 12:17am

@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.

rossk · June 5, 2022, 6:59am

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.

Sddawson · June 5, 2022, 8:21am

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?

rossk · June 5, 2022, 9:15am

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

rossk · June 5, 2022, 9:35am

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!