Hello everyone, I’m facing a recurring issue with my MariaDB setup in HomeAssistant. The states and state_attributes tables are frequently becoming corrupted, which is quite disruptive. The errors I encounter are:
Error 194: “Tablespace is missing for a table” from storage engine InnoDB
Error 1034: “Index for table ‘db’ is corrupt; try to repair it”
This happens about once a week, and there’s no discernible pattern in terms of system load. My setup includes HomeAssistant and MariaDB running on the same Raspberry Pi 4, using a NVME drive via USB3.0. I’ve tried optimizing the tables, but it doesn’t work and sometimes results in an error about the transport endpoint being already connected, even when HomeAssistant is turned off.
I’ve also attempted to dump the problematic tables using inno_db_recovery, but that didn’t help either. The NVME drive is new, so I don’t think that’s the issue. I’m wondering if there’s something specific about the Raspberry Pi 4’s hardware or MariaDB configuration that might be causing this. Any suggestions or insights would be greatly appreciated!
This kind of recurring corruption usually points to an underlying I/O or stability issue rather than Home Assistant itself. On a Raspberry Pi 4, MariaDB with InnoDB can be quite sensitive to power fluctuations, USB–NVMe bridge quirks, or filesystem issues, especially when there are lots of frequent writes like the states tables generate. I’d first check power supply quality, USB cable/adapter, and run filesystem checks on the NVMe. From the MariaDB side, reducing write pressure helps a lot: limit recorder history, enable purge_keep_days, consider excluding attributes you don’t need, and make sure innodb_flush_log_at_trx_commit and sync_binlog aren’t set too aggressively. Also verify you’re using a stable filesystem like ext4 and not hitting memory pressure or OOM kills. If corruption has already occurred and backups aren’t clean, native InnoDB recovery options are limited; in those cases, third-party tools like Stellar Repair for MySQL can sometimes extract data from damaged tables when standard dumps or innodb_force_recovery fail. Long term, though, many HA users find moving the database to a separate machine or switching to a lighter setup (or even the default SQLite with pruning) greatly improves reliability on Pi hardware.