Hi all!
I’m sorry i’m not fully sure if it’s a good category, but let’s try, maybe someone will give me some hints…
I’m facing weird corruptions on DB in my system. They happen mostly on states and state_attributes tables, and corruption gives me exact these two errors:
Error 194 "Tablespace is missing for a table" from storage engine InnoDB
or
Error 1034 - "Index for table 'db' is corrupt; try to repair it".
Warning : InnoDB: The B-tree of index PRIMARY is corrupted.
Warning : InnoDB: Index ix_states_event_id is marked as corrupted
Warning : InnoDB: Index ix_states_old_state_id is marked as corrupted
Warning : InnoDB: Index ix_states_last_updated_ts is marked as corrupted
Warning : InnoDB: Index ix_states_attributes_id is marked as corrupted
Warning : InnoDB: Index ix_states_entity_id_last_updated_ts is marked as corrupted
Warning : InnoDB: The B-tree of index ix_states_context_id is corrupted.
It happens quite often, once a week, but generally randomly, there is no higher system load when it corrupts.
Some info on my setup:
a) HomeAssistant and MariaDB (standalone) on same Raspberry Pi 4. Raspbian as an Host OS.
b) Power supply for Raspberry Pi 4 original, anyway i tried to change it two times - no luck
c) System and storage drive is NVME connected via USB3.0 adapter, not an SD Card
d) Absolutely no weird entries in system logs, no FS faults, USB, undervoltage etc.
c) MariaDB is at version 10.6.13, however i’m trying to keep it up to date with 10.6.x branch - i’m not sure what’s the maximum version supported by HomeAssistant.
d) It’s not possible to recover table by optimize table sql command:
| homeassistant.states | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| homeassistant.states | optimize | error | Got error 106 "Transport endpoint is already connected" from storage engine InnoDB |
| homeassistant.states | optimize | status | Operation failed |
BTW im not really getting this Transport endpoint already connected. When i run optimize i had home assistant turned off 
So i tried to dump problematic table using inno_db_recovery set to 3:
sudo mysqldump -B homeassistant -t states > dump_states.sql
mysqldump: Error 1034: Index for table 'states' is corrupt; try to repair it when dumping table `states` at row: 11417188
e) NVME is fresh, i also tought it could be a problem, so i changed to a new one - no luck.
Do you have any hints what could be the issue?