Database upgrade fails after home assistant core update

DIYMax · September 5, 2024, 7:05am

Hi. just upgraded to 2024.9.0 and first got a message:

“Database upgrade in progress”

Then after a minute or so two other messages came in:

“Database Migration Failed”
“Recorder could not start”

What should a database noob like me do now? Revert to the version before the update to 2024.9.0 by restoring a backup? Anything else?

Is this a known bug already or on my end? Never had this issue before.

I am running MariaDB and InfluxDB. Most automations seem to work, but stuff like logbook, history, and the energy dashboard does not. Grafana seems to receive proper data from sensors through InfluxDB. I assume this is related to MariaDB.

I restarted both MariaDB and InfluxDB integrations.
Restarting Home Assistant triggers the data base migration, which always fails.

Logs and screenshots below.
Thanks for any ideas how to solve this.

Logger: homeassistant.components.recorder.migration
Source: components/recorder/migration.py:577
integration: Recorder (documentation, issues)
First occurred: 08:40:57 (1 occurrences)
Last logged: 08:40:57

Could not modify column MODIFY event_id BIGINT in table states
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1034, "Index for table 'states' is corrupt; try to repair it")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 577, in _modify_columns
    connection.execute(text(f"ALTER TABLE {table_name} {column_def}"))
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1034, "Index for table 'states' is corrupt; try to repair it")
[SQL: ALTER TABLE states MODIFY event_id BIGINT]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

and

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:981
integration: Recorder (documentation, issues)
First occurred: 08:40:57 (1 occurrences)
Last logged: 08:40:57

Database error during schema migration
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1034, "Index for table 'states' is corrupt; try to repair it")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 981, in _migrate_schema
    new_schema_status = migrator(
                        ^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 334, in migrate_schema_live
    schema_status = _migrate_schema(
                    ^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 300, in _migrate_schema
    _apply_update(instance, hass, engine, session_maker, new_version, start_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 703, in wrapper
    job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 883, in _apply_update
    migrator_cls(instance, hass, engine, session_maker, old_version).apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 929, in apply_update
    self._apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1722, in _apply_update
    _modify_columns(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 577, in _modify_columns
    connection.execute(text(f"ALTER TABLE {table_name} {column_def}"))
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1034, "Index for table 'states' is corrupt; try to repair it")
[SQL: ALTER TABLE states MODIFY event_id BIGINT]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

koying · September 5, 2024, 7:12am

Why did you choose to use to use MariaDB in the first place, then?
Bold

This seems to be the root cause.
Have a look on how to repair that error, or restore a DB backup.

DIYMax · September 5, 2024, 8:03am

Thanks, I went down that route now. Here`s what I got from the terminal:

➜  ~ mariadb-check --all-databases
homeassistant.event_data                           OK
homeassistant.event_types                          OK
homeassistant.events                               OK
homeassistant.migration_changes                    OK
homeassistant.recorder_runs                        OK
homeassistant.schema_changes                       OK
homeassistant.state_attributes                     OK
homeassistant.states
Warning  : InnoDB: The B-tree of index PRIMARY is 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_old_state_id is marked as corrupted
Warning  : InnoDB: Index ix_states_metadata_id_last_updated_ts is marked as corrupted
Warning  : InnoDB: Index ix_states_context_id_bin is marked as corrupted
Warning  : InnoDB: Index ix_states_context_id is marked as corrupted
error    : Corrupt
homeassistant.states_meta                          OK
homeassistant.statistics                           OK
homeassistant.statistics_meta                      OK
homeassistant.statistics_runs                      OK
homeassistant.statistics_short_term
Warning  : InnoDB: The B-tree of index PRIMARY is corrupted.
Warning  : InnoDB: Index ix_statistics_short_term_statistic_id_start_ts is marked as corrupted
Warning  : InnoDB: Index ix_statistics_short_term_start_ts is marked as corrupted
Warning  : InnoDB: Index ix_statistics_short_term_start is marked as corrupted
error    : Corrupt
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
sys.sys_config                                     OK

Any idea how to take it from here? There is a suggestion to use https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_force_recovery – but I have no idea how to get it to work in terminal

koying · September 5, 2024, 8:09am

Maybe try How to Re-index MySQL database after restored | by Ashik CK | Medium

YewTreeWeb · September 5, 2024, 12:29pm

I have also got this error and are wondering how to fix it. I used Maria DB after watching EverythingSmartHome on YouTube and he advised to use DB when using SSD storage for performance

WatsonDue2 · September 5, 2024, 4:33pm

The issue in my case was the migration filling up the all the space in the VM with MariaDB. Increasing the size of the VM and restarting HA fixed the issue.

Sir_Goodenough · September 5, 2024, 5:08pm

Hi Max,

Maybe

Is go to the default database instead of mariaDB.

My suggestion is remove the MariaDB statement in your recorder integration and the database will rebuild. History will be at start-over. You could also ‘fix’ the broken tables (but that is something I couldn’t do) and migrate your data over, Migrate back from MariaDB to the default SQLite.

I personally delete my data every year.

DIYMax · September 5, 2024, 5:26pm

Thanks. Will InfluxDB get affected from this? I want to keep all the data that is in there and continue to use it to record sensors and state changes.

Lastly, why would you recommend to return to the default DB? Isn`t MariaDB much better performing? (at least I remember it was like that 3 years ago, or so, when I switched)

Sir_Goodenough · September 5, 2024, 5:29pm

Should not effect influx at all. That has a separate data feed AFAIK.

HA has been tweaked to do especially well with the default Sqllite. It is also the default is is most thoroughly tested and vetted for all functions.

DIYMax · September 5, 2024, 6:23pm

Thanks. Meanwhile I have reverted back to a full backup with core 2024.8.3 and do not get these messages again. Removing MariaDB did not cause any issues and InfluxDB indeed runs in parallel. I wasnt sure anymore if the data first goes into MariaDB and from there into InfluxDB. But InfluxDB gets the data directly with no other DB in between. So no concern for the long-term storage of data. I am updating now again to 2024.9.0. Cross fingers.

btw.

I personally delete my data every year.

does this mean you setup a keep period of a year for the default recorder DB? Isn`t that very long? Or are you referring to InfluxDB?

DIYMax · September 5, 2024, 6:26pm

Upgrade to 2024.9.0 and no error messages. thanks! I didn`t expect that getting rid of MariaDB would be a potential solution at all.

Sir_Goodenough · September 5, 2024, 6:29pm

No. I delete the database…My graphs all pop up as new. 0 history. I never look at it, so I don’t need it.

dani2dani · September 9, 2024, 7:30am

This can solve the issue: https://github.com/home-assistant/core/issues/125339

CDAutomate · September 10, 2024, 3:24am

I also have a similar issue. Will try the fix above and will report back.

CDAutomate · September 10, 2024, 3:06pm

Tried the recommended fix of increasing the innodb_buffer_size. Still failed.

Logger: homeassistant.components.recorder.migration
Source: components/recorder/migration.py:577
integration: Recorder (documentation, issues)
First occurred: 8:11:37 AM (1 occurrences)
Last logged: 8:11:37 AM

Could not modify column MODIFY old_state_id BIGINT in table states
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1114, "The table 'states' is full")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 577, in _modify_columns
    connection.execute(text(f"ALTER TABLE {table_name} {column_def}"))
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1114, "The table 'states' is full")
[SQL: ALTER TABLE states MODIFY old_state_id BIGINT]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:981
integration: Recorder (documentation, issues)
First occurred: 8:11:37 AM (1 occurrences)
Last logged: 8:11:37 AM

Database error during schema migration
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1114, "The table 'states' is full")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 981, in _migrate_schema
    new_schema_status = migrator(
                        ^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 334, in migrate_schema_live
    schema_status = _migrate_schema(
                    ^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 300, in _migrate_schema
    _apply_update(instance, hass, engine, session_maker, new_version, start_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 703, in wrapper
    job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 883, in _apply_update
    migrator_cls(instance, hass, engine, session_maker, old_version).apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 929, in apply_update
    self._apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1722, in _apply_update
    _modify_columns(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 577, in _modify_columns
    connection.execute(text(f"ALTER TABLE {table_name} {column_def}"))
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1114, "The table 'states' is full")
[SQL: ALTER TABLE states MODIFY old_state_id BIGINT]
(Background on this error at: https://sqlalche.me/e/20/e3q8)