Database corrupt after HomeAssistant update

Installation Home Assistant OS
1

Dear community,
it´s now the second time, that I have problems with corrupt database after Home Assistant update.
My configuration is as follows:
recorder is configured to use a MariaDB which is hosted on a Synology diskstation:

ecorder:
  db_url: !secret recorder_sql_db_diskstation
  commit_interval: 10
  purge_keep_days: 365
  auto_purge: true
  exclude:
    entity_globs:
      -sensor.synology*...

After updating Home Assistant core and OS (unfortunately, I made both update right after each other, so don´t know which one is the cause), I get the following error in the log, all data history is gone and the recorder is not working:

Logger: homeassistant.components.recorder.util
Source: components/recorder/util.py:96
Integration: Recorder (documentation, issues)
First occurred: 21:38:30 (1 occurrences)
Last logged: 21:38:30

Error executing query: (MySQLdb._exceptions.OperationalError) (1034, "Index for table 'events' is corrupt; try to repair it") [SQL: CREATE INDEX ix_events_data_id ON events (data_id)] (Background on this error at: https://sqlalche.me/e/14/e3q8)

As said, this is now the second time that this happend after updating and the only thing that helps, is to delete the complete database table (using phpMyAdmin). This is really annoying, as I lose all my data history.
I appreciate any hint to solve this problem and to find the root cause for this behaviour.
Many thanks in advance for your suggestions and support1

1 Like
2

Yea, I got the same error from 5 and 6 updates and have delete the db and start fresh. I want to know how to fix as well.

3

try to run the following sql command on your database:

REPAIR TABLE events
4

I tried running that on my mariadb docker container and was greeted with “The storage engine for the table doesn’t support repair” It seems to be a limitation of innodb itself.

5

Since its just the indices that appear to be broken, this should do the trick.

optimize table events

See output

+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| hass.events | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| hass.events | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.074 sec)

As it said, optimise is not supported on innodb, instead the table is recreated and analysed, effectively rebuilding the index along the way.