Home assistant database keeps getting corrupted


My database gets corrupted every 5+ days and I lose all stats and sensor data. Would anyone have any solution to this problem I can’t find anything on Google. I was thinking of moving to MariaDB and trying it out but for some reason, MariaDB addon is not available to me in the addon store.

How big is your DB? do you purge after x days?

  purge_keep_days: 7

Also are you running it on an SD card? I’ve had this before when I was running on an rpi4, it’s the reason I switched to running HA on a nuc.

I only found out later you can boot from usb stick on rpi4, which is way more robust then SD card.

I didn’t have a purge set. I’ll set it up then as the database keeps corrupting at random. The HA is setup on RPI3 (since rpi4 are hard to get atm) on a 128gb SD card.

Purge should help. But keep in mind sd card will fail eventually.

You can expect stocks of rpi4 to get back to almost normal 2023q3. But I’ll never run HA on rpi4 again… I’ve kept my nuc running now for 3+ years, and the fan is becoming audible, so I’m on the lookout for a replacement, and I’ve found a refurb Lenovo ThinkCentre M710q i3 for 144€ that I might grab.

It is set to 10 days by default.

I’m quite happy with it running on RPI3 but on the other hand I don’t know any different :joy: The only problem that I have ATM is this database corruption that annoys me.

I had an issue like this recently, the first time I ever had a DB problem with HA. The database would corrupt itself once a day, when HA applied the daily purge. The purge would fail because the database was damaged. I didn’t really have the time to dive into what exactly happened. I just loaded up the damaged DB into sqlitebrowser, exported it as an SQL file, fixed the damaged parts manually and reimported the SQL as a DB using sqlite3. That fixed it. I still don’t know why it happened in the first place.

Running on a Pi3, the DB is stored on a spinning disk NAS.

Now it might not be this scenario for you, as it seems that your corrupt files appear at random times. Could your SD card simply be end of life ? It’s not a good idea to store the DB on an SD card anyway.

It’s a brand new SD card - about 1 month old bought especially for the purpose of running home assistant on it. Unless it had some manufacturing defect the SD card should be all good.

Where did you buy it ? It’s worth noting that pretty much all SD cards sold on Amazon or Ebay or the likes are counterfeit. It’s almost impossible to find a genuine one on these sites. Always buy your SD card from a real brick and mortar store and buy high endurance ones. A low quality one can show damaged sectors within days of use.

Turn on debug logging for the recorder component and see what it complains about when it happens. This happening is not normal and a sign of something deeper. Changing to MariaDB will not solve your problem. I would bet on filesystem corruption due to a bad quality SD card.

I’ve bought it on Amazon - it’s Amazon Basics - MicroSDXC card from: https://amzn.eu/d/8J8uw8l

I had some problems with fake cards before so I thought that the Amazon branded card at least shouldn’t be fake :wink:

I’ll try to set the recorder tomorrow for the debug log and I’ll update when I get the error again. I will move the database then to an external USB drive and see if that resolves the problem.

There you go. There’s your problem. These Amazon branded things are very low quality, bottom of the quality scrap barrel stuff to make them as cheap as possible.

Do yourself a favor and get an actual good SD card. SanDisk High Endurance, SanDisk Extreme Plus/Pro or Samsung PRO Endurance series come to mind. DO NOT buy them on Amazon. Go to your nearest Best Buy / Media Markt / local equivalent and buy it there.

Edit: just to make this clear, as a lot of people don’t realize this. There is a huge technical difference between cheap ‘storage’ type SD cards and branded high endurance SD cards. They use completely different memory cell technologies. El-cheapo cards, like your Amazon basics, use QLC flash without wear leveling. That’s the cheapest flash memory available and also the least reliable one. Often they use chips that didn’t make the QA testing requirements for (cheap) SSDs and are recycled as SD cards. The high endurance series mentioned above use the same memory cell technology as good quality SSDs do (MLC and VNAND, often branded as ‘3d-nand’) and use wear leveling. They’re on a totally different level. Aside from the form factor, they’re two completely different memory devices.

BTW, you need to hit the reply under a post if you want to reply to someone. If you just reply to your own thread noone is going to be notified. I found your reply by accident.