MariaDB Corruption

Installation Home Assistant OS
1

I am getting corruption on the states table it seems
2023-11-02 11:29:19 21 [ERROR] InnoDB: File './homeassistant/states.ibd' is corrupted

When I do a check table manually however, everything seems to be okay:

mysqlcheck -c --all-databases
homeassistant.event_data                           OK
homeassistant.event_types                          OK
homeassistant.events                               OK
homeassistant.recorder_runs                        OK
homeassistant.schema_changes                       OK
homeassistant.state_attributes                     OK
homeassistant.states                               OK
homeassistant.states_meta                          OK
homeassistant.statistics                           OK
homeassistant.statistics_meta                      OK
homeassistant.statistics_runs                      OK
homeassistant.statistics_short_term                OK

There is always a ib_logfile0 in the folder
-rw-rw---- 1 root root 48.0M Nov 2 11:59 ib_logfile0
I delete it and restart it is okay for a while before the error pops up again the next day.

Also tried repair

bash-5.1# mysqlcheck --auto-repair homeassistant
homeassistant.event_data                           OK
homeassistant.event_types                          OK
homeassistant.events                               OK
homeassistant.recorder_runs                        OK
homeassistant.schema_changes                       OK
homeassistant.state_attributes                     OK
homeassistant.states                               OK
homeassistant.states_meta                          OK
homeassistant.statistics                           OK
homeassistant.statistics_meta                      OK
homeassistant.statistics_runs                      OK
homeassistant.statistics_short_term                OK

I did try to optimise the table but doesnt really help. I am able to fully dump the database, but I am not sure how the procedure will be to restore it since this is running as a container on HASSOS

Pointers would be welcomed

2
  1. Disabled recorder
  2. Took DB dump
mariadb-dump homeassistant > /data/backup/dbs_02112023.sql

File generated OK
-rw-r--r-- 1 root root 1.9G Nov 2 12:38 dbs_02112023.sql
3. Restore from Dump

mariadb homeassistant < /data/backup/dbs_02112023.sql

Restore OK

Yet I still see ib_logfile0 getting generated :frowning: no idea what is wrong here, it seems to be linked to the states table. Every time I do a check on this table, it returns ok but the file gets generated

3

OK I think I might have fixed it. No corruption log in 5 days now. My steps in case anyone else has the same issues. Now this will only work if you are able to dump the db

  1. Disable Recorder
  2. Stop HA Core
  3. Do backup of Database
mariadb-dump homeassistant > /data/backup/dbs_backup.sql
  1. Login to mysql & Drop Database
mysql --user=XXX --password=pass --database=XXX
DROP DATABASE_NAME;
  1. Recreate Database
CREATE DATABASE_NAME;
  1. Restore from backup
mariadb homeassistant < /data/backup/dbs_backup.sql
  1. Reboot Home Assistant.
reboot
4

No dice, still the same thing, restart mariaDB and everything is okay for a few days before the error returns. Doesnt make any sense, everything seems to be working