So, once again I lost my db-file. Probably third or fourth time in as many years.
Luckily, I rely on InfluxDB on an external server but it am mourning the loss of energy data for comparision directly in Home Assistant GUI.
I run Home Assistant on a Proxmox VM so the question is if that makes it easier to fix in the console?
Also, how do I stop it from happening again?
Is your system shutting down only via controlled shutdown or is it getting abruptly shut off? I run on a VM (VMWare) and have no issues, but I always do a controlled shutdown when I need to. I also use MariaDB because I had enough DB failures with the built-in one that I moved away from it when my DB got large.
Always controlled shutdowns from Home Assistant itself, not even using the host shutdown option even though it is safe.
Plenty of space on a 512GB WD Red NMVe on my USFF host.
My latest db was just below 2GB so it’s 1.5 years of data down the drain (although I have it in InfluxDB for proper analyze with Grafana etc).
I do run a MariaDB on my server for a bunch of containers. Please tell me more how I can switch home-assistantv2.db for that. Perhaps it’s even possible to migrate the correuoted one?
Sounds exactly what I need and should have done since the launch of longterm data db.
Thanks, I opened up the latest local backup (tar.gz) and even though it was only 900 mb in size, my few days old db-file of 1.9 GB were there and could replace the fresh empty db-file. Only lost a few days of data.
I’ll convert and migrate my data to MariaDB, then I might move it to my NAS for proper snapshotting and easy manipulation in my already running database.
I will use your link for converting and this for migrating:
I’ve been doing SQL since it was invented and so I’m much happier with my “mySQL” database than I was the SQLIte. I know you can do similar things but Maria, which is a fork of mySQL, has always been easier to work with for me.
I’ve never worked with SQLite but with MariaDB running 7 databases already on my NAS, the choice is easy.
Also, segragtiong the installation a little bit for backups and snapshots make more sense.
I uploaded everything to MariaDB using docker on another machine.
docker run -it \
--workdir $(pwd) \
--volume $(pwd):$(pwd) \
--rm ghcr.io/techouse/sqlite3-to-mysql:latest \
--sqlite-file home-assistant_v2.db \
--mysql-user MYUSER \
--mysql-password MYPASSWORD \
--mysql-database DATABASENAME \
--mysql-host IPTOMARIADBRUNNINGONPORT3306
Worked as a charm.
When done uploading the 1.9 GB it finished as 1.6 GB in MariaDB.
When restarting Home Asssistant some adjustments where made according to the notification, and when I looked the DB had bloated into 3.2 GB.
I suspect Home Assistant made readable copies but without deleting the originals.
Is there any way to purge the doublettes only?
That should clear up on its own. Give it some time.
No indications of that, still at 3.2 GB.
How long time should I give it?
I think my Home Assistant has become somwhat sluggish since the migration. Any idea how to handle that?
See if you can allocate more memory to it and then cofigure MariaDB to use it. I give mine a couple of GBs.
My VM is using 6.5 GB of its allocated 8 GB RAM and does never want any more. I have plenty of memory to allocate so that’s no problem. Also the MariaDB-docker (coreMariaDB or what’s it’s called) should be able to take whatever it wants.
So question is: How do I allocate more memory?
I recently expanded my nVME from initial 32 GB to 64 GB. May be the cause for DB to become corrupt a week later but Home Assistant have plenty of room to play around with.
The amount of memory MariaDB uses is controlled via its my.cnf file. My database in 22gb with 3+ years of data and works fine.
[mysqld]
innodb_buffer_pool_size = 2073741824
I run it in docker so it’s easy to control. I don’t know how you do it when using the add on.
That’s an odd number, 1024^3 + 1G?
I think you can just write 2G?
Addons are also docker-containers so it should be easy although I might need to readd it if the addon is updated.
Though I think it’s easiest to just change (or add) the record to the database using phpmyadmin.
For information it is possible to set the variable from within phpMyAdmin.
This is the default value of 128MiB.
Edit: Crap, it’s not persistent. Gotta figure out how to get to the HA addon’s shell…
Hi,
i am also repeatingly loosing my database every now and then. This time it was already twise this month. I figured that it was during nightly purge and then always follow the steps in this article but I use not the corrupted but the last working state for the repair process.
Still, I cannot find a way to sabilize. What should I so? Is migrating the db to maria the only thing or am I doing anythig wrong?
Since I migrated to MariaDB I haven’t had any issues. Highly recommended.
