Are HA Backups useless? 🚮 (sqlite db always corrupted)

[bschatzow (Bill Schatzow)]

I use the mariadb add on and the the Google drive back up add on. I stop the DB prior to back up and this let’s you get a backup that is not corrupt.

[flyize]

Do not store anything you care about in SQLite. Ever.

[seanomat]

This is mostly due to an open transaction while the backup was taken.
In most cases this can be repaired, by dumping and reimporting the database. Plenty of posts here how to do this. I never experienced any problems with this but there might be some inconsistencies.
Apart from that I remember reading here that the team is working on making the database backup more reliable.

[mattdm (Matthew Miller)]

@flyize

Do not store anything you care about in SQLite. Ever.

That’s not fair. When used properly, Sqlite is perfectly robust and can be used for mission-critical data. Sure, other options can be more powerful — if you want to be DBA. Otherwise, you’re adding complexity and probably not actually getting much real benefit (especially if you’re running it on the same system). At best, you’re moving the backup problem to a new server.

The best way to safely back up sqlite databases I’m aware of — but let me state right away that I’m definitely not a DBA! — is to run VACUUM INTO before you do backups, and back up that file instead of the main one. (Or, disk space being cheap… in addition to it.)

Hmm; in fact, this has me considering moving my home-assistant_v2.db to an in-memory database, and using vacuum-into to dump that periodically.

[orange-assistant (the real one)]

@seanomat

In most cases this can be repaired, by dumping and reimporting the database. Plenty of posts here how to do this.

You mimd sharing a link? Linke described above I was already runing sqlite3 .recover many times and at the very moment pragma checks are running already for hours

@mattdm

At best, you’re moving the backup problem to a new server.

I tried to restore all the backups on two different machines and 4 different disks in total. Result is always the same and resulting in a “discard” db once restore is finish despite showing proper history data while the restore takes place.

[seanomat]

DB image malformed. How to fix it?

For all ya googlers (or ducckduckgoers). I fixed mine! shut down HA via shell cd into config folder and execute: sqlite3 ./home-assistant_v2.db “.dump” | sqlite3 ./home-assistant_v2.db.fix mv ./home-assistant_v2.db ./home-assistant_v2.db.broken mv ./home-assistant_v2.db.fix ./home-assistant_v2.db Start ha and check logs and history via ui if fixed Delete home-assistant_v2.db.broken

[flyize]

No one uses SQLite for mission critical data. That would be insane.

Either way, I would assume that most people who have dealt with HA backups, or HA in general, have seen DB corruption. While it might be able to be fixed, for the most folks the data just doesn’t really matter that much. I assume that’s why the devs haven’t gone to something more robust.

Personally, I’ve moved it all to MariaDB.

[mattdm (Matthew Miller)]

@flyize

No one uses SQLite for mission critical data. That would be insane.

That’s quite the confident assertion, but you are wrong, both in whether it happens and whether it is “sane”. I’m not going to escalate on the name calling — I think it’s inappropriate.

But I will say: for every case where I’ve seen someone using Sqlite where a different database server would be more appropriate, I’ve seen a hundred cases where someone has installed mysql, mariadb, or postgresql with minimal configuration and zero administrative attention or care, and those are at least as troubling. (Especially but not only on the topic at hand — reliable backups.)

[flyize]

Apologies if you took that as a personal attack. It wasn’t.

No normal IT person is going to run mission critical data on SQLite. If they do, they won’t be in IT for long. If you disagree, then we will just have to leave it there. I’ve no need to convince you and it’s probably not that constructive for this thread anyway.

My suggestion for OP is still to install MariaDB.

[mattdm (Matthew Miller)]

I didn’t take it as a personal attack, but I don’t think it’s constructive to call people “insane”. And your other assertions continue to just be factually wrong.

To stay to the topic, if you install MariaDB, you will still need to do something about backups other than just back up or snapshot the filesystem where the on-disk database files live, or you will have similar problems.

[flyize]

At no point did I call anyone insane.

[orange-assistant (the real one)]

Thank’s but the dump didn’t help. It results in a .db file with 0 bytes

[mattdm (Matthew Miller)]

If you run sqlite3 ./home-assistant_v2.db and then type .dump at the interactive prompt, what do you get?

What about .recover instead?

[AaronCake]

SQLite is the most widely used database engine around, and it is often and correctly used for mission critical data. I think you’d be amazed how many products and devices are running a SQLite backend. For example, SQLite is built into the Android SDK so almost all data storage in Android is in SQLite DBs.

The real problem is that HA absolutely abuses SQLite and every other database engine by storing every state change as VARCHAR(255) with an attributes field as TEXT (seriously?!) containing a JSON string of all state attributes. This is why HA databases bloat to multiple gigabytes when storing 25MB worth of data. Until this is fixed, the database will always be a ticking time bomb that the user must actively manage. Combine this with SD cards and the problem is amplified 100x.

The HA backup (I am assuming here, as I don’t use Supervised/HASIO/HAS OS, etc.) makes useless backups of the SQLite DB because it doesn’t take a snapshot. It just copies the file. Leaving transactions open (does HA even use transactions? I don’t think so), commits open, and half written data.

I fully agree with your statement though with a minor edit: No one should use the Home Assistant database to store data you actually want. That would be insane.

[orange-assistant (the real one)]

@mattdm

If you run sqlite3 ./home-assistant_v2.db and then type .dump at the interactive prompt, what do you get?

A console full of

INSERT INTO events VALUES(...

and the last 5 lines:

CREATE INDEX ix_statistics_short_term_metadata_id ON statistics_short_term (metadata_id);
CREATE INDEX ix_statistics_short_term_start ON statistics_short_term (start);
CREATE UNIQUE INDEX ix_statistics_statistic_id_start ON statistics (metadata_id, start);
CREATE UNIQUE INDEX ix_statistics_short_term_statistic_id_start ON statistics_short_term (metadata_id, start);
ROLLBACK; -- due to errors

@mattdm

What about .recover instead?

I was running the .recover before (non interactive/verbose) couple of times already and it actually spits out a new db file which is smaller than the original but still discard by ha once restore is complete.

I even did try to recover a recovered db (and did one more recover on that), the db file get’s smaller and smaller but ha still doesn’t want that…

When running .recover in the interactive prompt the last 5 lines are:

CREATE INDEX ix_statistics_short_term_start ON statistics_short_term (start);
CREATE UNIQUE INDEX ix_statistics_statistic_id_start ON statistics (metadata_id, start);
CREATE UNIQUE INDEX ix_statistics_short_term_statistic_id_start ON statistics_short_term (metadata_id, start);
PRAGMA writable_schema = off;
COMMIT;

I also did some PRAGMA checks but don’t have an idea what the output means:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> PRAGMA quick_check;
*** in database main ***
Page 729453: free space corruption
sqlite> PRAGMA integrity_check;
*** in database main ***
Page 729453: free space corruption
row 733 missing from index ix_events_event_type_time_fired
row 733 missing from index ix_events_context_id
row 733 missing from index ix_events_time_fired
database disk image is malformed
sqlite> PRAGMA foreign_key_check;
states|152065729|events|1

[HeyImAlex (Alex)]

@AaronCake

The real problem is that HA absolutely abuses SQLite and every other database engine by storing every state change as VARCHAR(255) with an attributes field as TEXT (seriously?!) containing a JSON string of all state attributes. This is why HA databases bloat to multiple gigabytes when storing 25MB worth of data.

It’s also the reason why history accesses are so unbelievably slow. The entire recorder component will have to be rewritten from scratch at some point.

[sparkydave (🇦🇺 Dave)]

+1 for swapping to the Maria DB. I also don’t include the DB in my backups, since it’s around 5.1GB. Since swapping to the Maria DB I haven’t had any issues at all.

[AaronCake]

Except development is too busy creating a media player for some reason.

(I hope anyone doesn’t take that as more than it was meant to be…slight ribbing. I appreciate everything the HA developers do with the exception of the database/recorder component).

[mattdm (Matthew Miller)]

@AaronCake

Except development is too busy creating a media player for some reason.

Open source development isn’t interchangeable — people work on what they’re interested in. I get that you’re joking, but… if people want this better, the best way to do it is to start actually making it better.

[HeyImAlex (Alex)]

@mattdm

if people want this better, the best way to do it is to start actually making it better.

That’s easier said than done. The recorder is a very central part of HA and has inter-dependencies into several other parts. Changing it would be a very significant amount of work and requires indepth knowledge of the HA internals. Add to this a very ‘closed-inner-circle’ BDFL approach of the core developers, where it is extremely difficult to get anything approved into core that would change a fundamental part of HA as an outsider, regardless of how good it is technically. There are so many hoops they make you jump through, and there’s no guarantee they won’t just throw it out at the end and you wasted all your time.

Realistically, something like a completely new recorder component would be impossible to get approved into core unless one of the main devs does it.