MariaDB does not recover from HASS.io backup #1353
Comments
|
Anything in the logs? |
Different cases on log messages are inside #667 . I had similar one: . |
|
A lot has changed in the meantime, I would really need recent add-on logs in order to get clue on where to start. |
|
I mentioned it in the other thread, taking a snapshot from a database while the service is running just like that is not a proper backup operation. Either the service must be shut down during snapshoting, or the steps documented in the "Filesystem Snapshots" need to be followed: https://mariadb.com/kb/en/backup-and-restore-overview/#filesystem-snapshots. Everything else is a hackaround. |
|
That is correct, unfortunately, the add-on is not aware of such a thing, and cannot handle it as suggested. |
|
Restore log, if it helps: Can't restore origin data: [('/data/tmp/tmpken481ki/data/databases/mariadb.err', '/data/addons/data/core_mariadb/databases/mariadb.err', ' |
|
That's the error I get when ever attempting a restore |
|
@frenck I guess it needs an additional hook or something from the add on system would be needed. E.g. a command which would be executed in the container context before/(and after) doing the backup. Then the |
|
I have the same problem and the same logs: Is there a way to do the backup of the database only using something like |
|
Same here. I just restored yesterday's snapshot because of issues with deconz, but MySQL is a mess right now. Exactly the same issues as in the log snippet above. |
|
Same issue here. Restored today on new hardware. No luck. |
|
@frenck, would a backup work if one first shuts down the add-on before making a snapshot? |
|
@basnijholt For me it is working if the add-on is stopped before backup and restarted after that. |
Hi Frenck, Hope, it will help to solve the issue. |
|
This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. |
|
Don‘t close as it isn‘t solved. |
|
What I currently use is the table lock approach suggested by the MariaDB documentation. Basically, SSH into the underlaying OS, and do the following: Leave the shell/mysql client open, and do the backup, then, after the backup finishes: It seems that with this method, I did not even lose an event! The database inserts got queued, and executed after I ran This snapshot of the events table is reverse sorted, so the first row is the newer row... One can see that the backup took 27 minutes (actually, it was faster, I just unlocked the table only after 27 minutes). At 2020-07-17 21:38:19 loooots of events got stored at once: I think having the Supervisor executing this commands before/after taking the snapshot of the database would be the ideal solution. |
|
Thanks @agners for this idea. I think this should work fine. Supervisor should care about that. So if a snapshot gets triggered and MaraDB is configured then it should execute this commands before/after snapshot. Any ideas how to achieve this? |
|
You can also use following commands to run it directly:
Take snapshot and wait until finished.
You can also exec any DB command you like with: |
|
@vistalba careful, the documentation states explicitly that the client must remain open: https://mariadb.com/kb/en/backup-and-restore-overview/#filesystem-snapshots. From what I understand, using |
|
You're right :( my bad. |
|
i hope this issue gets a fix ;) |
|
This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. |
|
This is still an issue for me. Would be great if we could get some kind of fix for this... |
|
Side effect the add on bookstack can't be restored as it uses by default the marinade addon |
|
At a high level, a potential fix could look like:
mariabackup --backup \
--target-dir=/var/mariadb/backup/ \
--user=homeassistant --password=mypassword
mariabackup --prepare \
--target-dir=/var/mariadb/backup/
Questions:
Additional consideration would also be needed during the restore process to restore the files to the mariadb data directory. Thoughts on if this implementation is feasible? Alternatives: As mentioned earlier in the thread Overall, option 1 would probably be more robust, but would add complexity since it doesn't fit as neatly into the existing snapshot framework. |
|
Other side effect the addon Nginx Proxy Manager is relying on the mariadb addon. |
|
Yep - I wouldn't say this issue was closed. I've hit this issue yesterday/today - I had to go back to a snapshot from the 20th Dec to get things like bookstack recovered, it almost started to work but mysqld kept core dumping - lots of message Innodb' so I quickly issued a 'drop database homeassistant;' and then restarted homeassistant to let it re-create everything. |
|
It seems the MariaDB addon is running v10.4.13. Current 10.4 stable release is 10.4.17. Per release notes from 10.4.17-10.4.14, the following corruption and crash recovery bugs have been fixed:
I'm not sure of a way to tell that any of these bugs specifically are causing the corruption, but upgrading the add-on the current 10.4 stable release could at least rule them out. Maybe this could be tested locally by upgrading the MariaDB package inside the running add-on container? This also got me thinking, if a new version of the MariaDB add-on is released, is the data stored in the database migrated as well? Or is the old version container destroyed (along with the database) and just replaced with the new instance of the upgraded container? |
|
@jhampson-dbre For the time being I've done an automation to hassio.addon_stop the addon_core_mariadb, partial backup just of the addon core_mariadb , wait 3mins while the backup is happening and finally hassio.addon_start of the hassio.addon_core_mariadb. This I execute 10 mins after my full backup... |
|
@jhampson-dbre Besides those fixes, just copying live data is a recipe for disaster. There area some settings which increase fault tolerance, but that will slow down the database, especially on installations running on usb sticks or sd cards. I guess we'd need some kind of pre and post snapshot and restore hooks, that could be used for triggering a db backup and restore. Also, specifying or multiple backup paths (for backup directories) or excluding certain paths (like live db data) in the snapshot / restore code might be beneficial @0101binary0101 Adding the mariadb-backup package is just a matter of adding it to the Dockerfile of the MariaDB add-on. |
|
@0101binary0101 It looks like the
Update: MariaDB addon does not support sending commands using
Update: MariaDB add-on already uses a persistent data volume by setting the default database directory in the Dockerfile to Some links on this topic: Some advantages of using external volumes:
Disadvantages:
|
|
@jhampson-dbre The addon data directory is by default mapped to /data in the container as a bind mount. So the same goes for the MariaDB container. So there's no need for persistent volumes. Snapshotting just creates tar files from the add-on data directories, collects them in one tar file and places it in the backup location. In the case of MariaDB, a copy is made from the open database files, which introduces corruption. As I can see now, there's no mechanism in the snapshotting code to execute any pre or post actions during the snapshot. I guess that's something that needs to be worked on. In that case, we could perform an online backup of the database to ie When restoring the add-on, there should also be a post action which moves the data from |
|
I think the need for a pre and post hook for backup/restore should be requested in a dedicated issue. Who is volunteer to make it ? |
|
@alexdepalex @oncleben31 - Agreed hooks to do pre-snap and post-snap @jhampson-dbre - yep that's why I said that the mariadb-backup wasn't installed... Sure I could do a mysqldump ..but it's all about the fiddling and faffing around that you'd have to do is the pain during the restore. For now I'm happy with my temporary solution of 10 mins after the full snapshot automation. I do an automation which does the following:
I could paste the full automation, but tbh I think the issue with pre-snap and post-snap should be addressed. This way the snapshot contains the DB that was NOT RUNNING (no open files) at the time. That way I have two snaps.. the full snapshot (with online potentially bad db) and then a correctly saved snapshot of just the DB. For restoring/failovers I would restore the full snapshot and then restore the partial snaptop of the db (which would then have the bookstack and the homeassistant db intact ). |
Add a partial snapshot for only MariaDB with the addon stopped. Linked to home-assistant/addons#1353.
|
I share my implementation of the work around. |
|
This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. |
|
I am still affected by this. |
|
adding a me too |
|
+1 |
* Add service which locks database tables Add an additional service which helds a lock on all database tables. This allows to safely snapshot the database files. * Lock database during snapshot operation Fixes: #1353 * Update config.json * Update CHANGELOG.md Co-authored-by: Pascal Vizeli <pvizeli@syshack.ch>
|
The latest MariaDB Add-on 2.4.0 does essentially what MariaDB suggest when taking a file system level snapshot (and I outlined in #1353 (comment)). So snapshots taken with this version of the add-on should not lead to unrecoverable database snapshots anymore. You should see something like this in the add-on log during snapshot: |
|
Can someone with a test bench confirm that it is working? I mean taking a snapshot and restoring it while mariadb is working? |
|
FWIW, on my test instance a recovery of a snapshot taken while MariaDB running seems to have worked. I did make sure that data got written during snapshot, so it should represent a real world situation. |
Home Assistant release with the issue:
Home Assistant 0.110.2
Last working Home Assistant release (if known):
Unknown (but it definitely worked earlier).
Operating environment (Hass.io/Docker/Windows/etc.):
Hass.io/Docker/Supervised
Component/platform:
MariaDB add-on used as record/history component.
Description of problem:
After recovery from Hass.io snapshot MariaDB doesn't work any more. The only way to force it to work is to uninstall the add-on and install it again with the same settings. Of course, in this case you lose your DB and history.
Problem-relevant configuration.yaml entries and (fill out even if it seems unimportant):
Traceback (if applicable):
Additional information:
It worked fine on one of earlier version of hass.io. There are other issues containing relevant information that were closed without a solution:
home-assistant/core#26152
#667
The text was updated successfully, but these errors were encountered: