home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.

https://www.home-assistant.io

Apache License 2.0

73.7k stars 30.82k forks source link

Database migration errors in 2024.8.2 #124186

Open DAVIZINH0 opened 2 months ago

DAVIZINH0 commented 2 months ago

The problem

When the update 2024.8.2 apears. a new migration of the bbdd apears and have some warnings and after some hours, apears errors message.

After the errors, the recorder stops, i reboot the home assistant and start again the migration with the same results

my setup is:

home assistant OS in a minipc
recorder in mariadb addon
the bbdd have more than 45GB
modify the poolsize to 2GB on boot

First: a message that the update is in progress: `Registrador: homeassistant.components.recorder.migration Fuente: components/recorder/migration.py:288 integración: Recorder (documentación, problemas) Ocurrió por primera vez: 21:01:59 (1 ocurrencias) Último inicio de sesión: 21:01:59

The database is about to upgrade from schema version 44 to 45`

After this, other warning message: `Registrador: homeassistant.components.recorder.migration Fuente: components/recorder/migration.py:717 integración: Recorder (documentación, problemas) Ocurrió por primera vez: 21:01:59 (7 ocurrencias) Último inicio de sesión: 22:41:31

Adding foreign key constraint to states.old_state_id. Note: this can take several minutes on large databases and slow machines. Please be patient! Adding foreign key constraint to states.attributes_id. Note: this can take several minutes on large databases and slow machines. Please be patient! Adding foreign key constraint to states.metadata_id. Note: this can take several minutes on large databases and slow machines. Please be patient! Adding foreign key constraint to statistics.metadata_id. Note: this can take several minutes on large databases and slow machines. Please be patient! Adding foreign key constraint to statistics_short_term.metadata_id. Note: this can take several minutes on large databases and slow machines. Please be patient!`

After some hours, the first errors: `Registrador: homeassistant.helpers.recorder Fuente: helpers/recorder.py:101 Ocurrió por primera vez: 22:41:32 (1 ocurrencias) Último inicio de sesión: 22:41:32

Error executing query 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.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (homeassistant.#sql-alter-43-1f, CONSTRAINT #sql-alter-43-1f_ibfk_1 FOREIGN KEY (metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE)')

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

Traceback (most recent call last): File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 101, in session_scope yield session File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 727, in _add_constraint connection.execute(add_constraint) 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/ddl.py", line 180, in _execute_on_connection return connection._execute_ddl( ^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl 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.IntegrityError: (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (homeassistant.#sql-alter-43-1f, CONSTRAINT #sql-alter-43-1f_ibfk_1 FOREIGN KEY (metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE)') [SQL: ALTER TABLE statistics_short_term ADD FOREIGN KEY(metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE] (Background on this error at: https://sqlalche.me/e/20/gkpj)`

and other error more: `Registrador: homeassistant.components.recorder.migration Fuente: components/recorder/migration.py:695 integración: Recorder (documentación, problemas) Ocurrió por primera vez: 22:41:32 (1 ocurrencias) Último inicio de sesión: 22:41:32

Could not update foreign options in statistics_short_term table, will delete violations and try again 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.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (homeassistant.#sql-alter-43-1f, CONSTRAINT #sql-alter-43-1f_ibfk_1 FOREIGN KEY (metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE)')

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 695, in _restore_foreign_key_constraints _add_constraint(session_maker, add_constraint, table, column) File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 727, in _add_constraint connection.execute(add_constraint) 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/ddl.py", line 180, in _execute_on_connection return connection._execute_ddl( ^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl 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.IntegrityError: (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (homeassistant.#sql-alter-43-1f, CONSTRAINT #sql-alter-43-1f_ibfk_1 FOREIGN KEY (metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE)') [SQL: ALTER TABLE statistics_short_term ADD FOREIGN KEY(metadata_id) REFERENCES statistics_meta (id) ON DELETE CASCADE] (Background on this error at: https://sqlalche.me/e/20/gkpj)`

and one more warning: `Registrador: homeassistant.components.recorder.migration Fuente: components/recorder/migration.py:746 integración: Recorder (documentación, problemas) Ocurrió por primera vez: 22:41:32 (1 ocurrencias) Último inicio de sesión: 22:41:32

Rows in table statistics_short_term where metadata_id references non existing statistics_meta.id will be deleted. Note: this can take several minutes on large databases and slow machines. Please be patient!`

And the migration is still in progress (only 2 hours this third attemp. I will update with the final errors.

What version of Home Assistant Core has the issue?

2024.8.2

What was the last working version of Home Assistant Core?

2024.8.1

What type of installation are you running?

Home Assistant OS

Integration causing the issue

recorder

Link to integration documentation on our website

No response

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

home-assistant[bot] commented 2 months ago

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of `recorder` can trigger bot actions by commenting: - `@home-assistant close` Closes the issue. - `@home-assistant rename Awesome new title` Renames the issue. - `@home-assistant reopen` Reopen the issue. - `@home-assistant unassign recorder` Removes the current integration label and assignees on the issue, add the integration domain after the command. - `@home-assistant add-label needs-more-information` Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue. - `@home-assistant remove-label needs-more-information` Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

recorder documentation recorder source _{^{(message by IssueLinks)}}

sp7dpt commented 2 months ago

I also have a problem with "recorder" in version 2024.8.2. In version 2024.8.1 everything works correctly but after updating to 2024.8.2 all stat entities do not work.

DAVIZINH0 commented 2 months ago

in my mariadb i can see this log 7 hours after start the migration again:

2024-08-19 5:02:08 36 [Warning] Aborted connection 36 to db: 'homeassistant' user: 'homeassistant' host: '172.30.33.18' (Got timeout reading communication packets)

oriolplav commented 2 months ago

Also errors here, I've tried 2 times and after 13 hours I got this:

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.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (homeassistant.#sql-alter-1-4, CONSTRAINT #sql-alter-1-4_ibfk_3 FOREIGN KEY (event_type_id) REFERENCES event_types (event_type_id))')

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 695, in _restore_foreign_key_constraints _add_constraint(session_maker, add_constraint, table, column) File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 727, in _add_constraint connection.execute(add_constraint) 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/ddl.py", line 180, in _execute_on_connection return connection._execute_ddl( ^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl 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.IntegrityError: (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (homeassistant.#sql-alter-1-4, CONSTRAINT #sql-alter-1-4_ibfk_3 FOREIGN KEY (event_type_id) REFERENCES event_types (event_type_id))') [SQL: ALTER TABLE events ADD FOREIGN KEY(event_type_id) REFERENCES event_types (event_type_id)] (Background on this error at: https://sqlalche.me/e/20/gkpj)

During handling of the above exception, another exception occurred:

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: (2013, 'Lost connection to server during query')

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 988, in _migrate_schema new_schema_status = migrator( ^^^^^^^^^ File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 335, in migrate_schema_live schema_status = _migrate_schema( ^^^^^^^^^^^^^^^^ File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 301, 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 856, in _apply_update migrator_cls(instance, hass, engine, session_maker, old_version).apply_update() File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 902, in apply_update self._apply_update() File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1697, in _apply_update _restore_foreign_key_constraints( File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 704, in _restore_foreign_key_constraints _delete_foreign_key_violations( File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 807, in _delete_foreign_key_violations result = session.connection().execute( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 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) (2013, 'Lost connection to server during query') [SQL: DELETE FROM events WHERE (events.event_type_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM event_types AS t2 WHERE t2.event_type_id = events.event_type_id)) LIMIT 100000;] (Background on this error at: https://sqlalche.me/e/20/e3q8)

cybernard commented 2 months ago

I am also having similar problems How do I resolve this issue?

Logger: homeassistant.components.recorder.core Source: components/recorder/core.py:988 integration: Recorder (documentation, issues) First occurred: 4:45:18 AM (1 occurrences) Last logged: 4:45:18 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.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (homeassistant.#sql-alter-4a6-d, CONSTRAINT #sql-alter-4a6-d_ibfk_1 FOREIGN KEY (old_state_id) REFERENCES states (state_id))')

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 695, in _restore_foreign_key_constraints _add_constraint(session_maker, add_constraint, table, column) File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 727, in _add_constraint connection.execute(add_constraint) 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/ddl.py", line 180, in _execute_on_connection return connection._execute_ddl( ^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl 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.IntegrityError: (MySQLdb.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (homeassistant.#sql-alter-4a6-d, CONSTRAINT #sql-alter-4a6-d_ibfk_1 FOREIGN KEY (old_state_id) REFERENCES states (state_id))') [SQL: ALTER TABLE states ADD FOREIGN KEY(old_state_id) REFERENCES states (state_id)] (Background on this error at: https://sqlalche.me/e/20/gkpj)

During handling of the above exception, another exception occurred:

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.InternalError: (126, 'Got error \'126 "Index is corrupted"\' for \'/tmp/#sql-temptable-4a6-e-2.MAI\'')

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 988, in _migrate_schema new_schema_status = migrator( ^^^^^^^^^ File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 335, in migrate_schema_live schema_status = _migrate_schema( ^^^^^^^^^^^^^^^^ File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 301, 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 856, in _apply_update migrator_cls(instance, hass, engine, session_maker, old_version).apply_update() File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 902, in apply_update self._apply_update() File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1697, in _apply_update _restore_foreign_key_constraints( File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 704, in _restore_foreign_key_constraints _delete_foreign_key_violations( File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 769, in _delete_foreign_key_violations result = session.connection().execute( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 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.InternalError: (MySQLdb.InternalError) (126, 'Got error \'126 "Index is corrupted"\' for \'/tmp/#sql-temptable-4a6-e-2.MAI\'') [SQL: UPDATE states as t1 SET old_state_id = NULL WHERE (t1.old_state_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM (SELECT state_id from states) AS t2 WHERE t2.state_id = t1.old_state_id)) LIMIT 100000;] (Background on this error at: https://sqlalche.me/e/20/2j85

DAVIZINH0 commented 2 months ago

What is the size of your database?? I think the problem is with Big databases, but in not a expert

oriolplav commented 2 months ago

Yes it seems a problem with big databases, but Home assistant has a long history, there will be more users with big databases, It has to be taken into account. For now I removed the rows manually with more optimized way (I'm sure there are faster ways without creating temporary tables, but for my case it works.):

Creating a temp table:

CREATE TABLE temp_ids (event_id INT PRIMARY KEY);

Adding event_id

INSERT INTO temp_ids (event_id) SELECT event_id FROM homeassistant.events WHERE event_type_id IS NOT NULL AND event_type_id NOT IN (SELECT event_type_id FROM homeassistant.event_types) ORDER BY event_id;

Removing this id's

DELETE e FROM homeassistant.events e INNER JOIN temp_ids ti ON ti.event_id = e.event_id;

This took 1 minute.... We'll see if it works or if I find more errors :)

DAVIZINH0 commented 2 months ago

Interesting idea. Tell us if works and the migration works. After this you will copy again the events to the original table? Is this your plan?

And i agree with you. A lot of People have Big databases! I dont know if the developers test this migration with Big databases

oriolplav commented 2 months ago

My plan is to have the migration work by itself now, Home Assistant was going to delete these rows anyway, but the query took too long and this returned the error that made the process fail. We'll see if now, that I've deleted these rows manually, the process continues, or at least fails at another point... Tomorrow I'll post the results since the process is long (my database has about 60GB, a long time with Home Assistant :D)

cybernard commented 2 months ago

my database is about 100gb

sp7dpt commented 2 months ago

my database is about 5gb

Rothammel commented 2 months ago

same here

mortenmoulder commented 2 months ago

Getting the same. My database is 90GB. Updated from 2024.6 to 2024.8.2. innodb_buffer_pool_size is set to 4GB. CPU is an i7-13700K and the machine has 64GB RAM. Storage is a Samsung 980 Pro NVMe SSD.

bsafh commented 2 months ago

same problem here. MySQL-HA-Addon, innodb_buffer_pool_size temporarly to 4 GB(*), HA supervised, running on a 6-Core-Xeon 54xx with 16 GB RAM.

Database is still on Version 44 after 3 days. "Databse update in progress" displayed for a few hours, then disappears, then reappears the next day without touching HA ...

Database size is 96 GB.

(*)there seems to be absolutely no way to set this and other mysql.cnf parameters permenently with the HA MySQL addon (no, editing the values in .../addon/mysql/mysql.cnf (or whatever the name is) does NOT work

Does anyone have the SQL statement for the database schema update? perhaps it works running it from the docker container console in the mariadb prompt?

DAVIZINH0 commented 2 months ago

Today it finish my third attemp of migration and finis ok WOW!!! NOTHING CHANGE in my bbdd. and finish

I do nothing diferent that the 2 previous attempts. this is my history: 1.- first attempt: Errors en logs an finally error with migration failed and stop recorder 2.- reboot the machine 3.- message in the start with the migration starts 4.- erros in the log during the attempt of migration (about 7 hours after start the migration) 5.- migration failed and stop the recorder 6.- reboot the machine 7.- message in the start with the migration starts 8.- errors in the log during the attempt of migration. The errors are that I put in this issue. 9.- after more than 36 hours message of upgrade done.

i reboot the machine

no error, and aparently ok this is the log of the migration done: `Registrador: homeassistant.components.recorder.migration Fuente: components/recorder/migration.py:307 integración: Recorder (documentación, problemas) Ocurrió por primera vez: 09:25:46 (1 ocurrencias) Último inicio de sesión: 09:25:46

Upgrade to version 45 done`

RedPaladin commented 2 months ago

I got similar issue here but migration from schema 43 to 45 with mariaDb. Database size is only 4.1 GB but it is running on Synology with docker so not very fast machine. It's been 2 hours the migration has started. I can see the schema has been migrated to 44. Now waiting for the migration from 44 to 45. No indication on the log so I don't know if the process is still on going or not. I hope migration progress status will be more improved in future version of home assistant.

Broekman commented 2 months ago

Same for me when updating from 2024.8.1 to 2024.8.2, MariaDB of ~40G; database update starts and fails after around 15 minutes after startup. Rolling back to 2024.8.1 restored it to an operating state. Tried twice. Adding my logs for reference.

innodb_buffer_pool_size set to 2GB.

Logger: homeassistant.helpers.recorder
Source: helpers/recorder.py:104
First occurred: 19:51:49 (1 occurrences)
Last logged: 19:51:49
Error executing query

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 104, in session_scope
    session.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2017, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1309, in commit
    trans.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2629, in commit
    self._do_commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2734, in _do_commit
    self._connection_commit_impl()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2705, in _connection_commit_impl
    self.connection._commit_impl()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1146, in _commit_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2356, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1144, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
                                  ^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 585, in connection
    return self._revalidate_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 677, in _revalidate_connection
    self._invalid_transaction()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 667, in _invalid_transaction
    raise exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back.  Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b)

Could not update foreign options in states table

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: (2013, 'Lost connection to server during query')

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 727, in _add_constraint
    connection.execute(add_constraint)
  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/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    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) (2013, 'Lost connection to server during query')
[SQL: ALTER TABLE states ADD FOREIGN KEY(old_state_id) REFERENCES states (state_id)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Error during schema migration

Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 988, in _migrate_schema
    new_schema_status = migrator(
                        ^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 335, in migrate_schema_live
    schema_status = _migrate_schema(
                    ^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 301, 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 856, in _apply_update
    migrator_cls(instance, hass, engine, session_maker, old_version).apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 902, in apply_update
    self._apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1697, in _apply_update
    _restore_foreign_key_constraints(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 695, in _restore_foreign_key_constraints
    _add_constraint(session_maker, add_constraint, table, column)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 724, in _add_constraint
    with session_scope(session=session_maker()) as session:
  File "/usr/local/lib/python3.12/contextlib.py", line 144, in __exit__
    next(self.gen)
  File "/usr/src/homeassistant/homeassistant/helpers/recorder.py", line 104, in session_scope
    session.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2017, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1309, in commit
    trans.commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2629, in commit
    self._do_commit()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2734, in _do_commit
    self._connection_commit_impl()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2705, in _connection_commit_impl
    self.connection._commit_impl()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1146, in _commit_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2356, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1144, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
                                  ^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 585, in connection
    return self._revalidate_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 677, in _revalidate_connection
    self._invalid_transaction()
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 667, in _invalid_transaction
    raise exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: Can't reconnect until invalid transaction is rolled back.  Please rollback() fully before proceeding (Background on this error at: https://sqlalche.me/e/20/8s2b

oriolplav commented 2 months ago

Same error here now, after 26 hours trying the migration for the third time...

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: (2013, 'Lost connection to server during query')

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 727, in _add_constraint
    connection.execute(add_constraint)
  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/ddl.py", line 180, in _execute_on_connection
    return connection._execute_ddl(
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1529, in _execute_ddl
    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) (2013, 'Lost connection to server during query')
[SQL: ALTER TABLE states ADD FOREIGN KEY(attributes_id) REFERENCES state_attributes (attributes_id)]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

The bad thing is that every time you try the migration it starts from 0 and does everything again, it means that if I try the migration again solving this problem I will have to wait at least 26 hours more... great.

RedPaladin commented 2 months ago

FYI - I've completed the migration to 2024.8.2 with mariaDB (without changing innodb_buffer_pool_size) on my Synology NAS. I took me more or less 2 hours to migrate from 43 to 44 and same from 44 to 45 schema version.

oriolplav commented 2 months ago

Finally I've increased the resources of the Database and it finished in 2 hours. I will decrease the resources again now because I use the server for more services than that

Broekman commented 2 months ago

Same for me when updating from 2024.8.1 to 2024.8.2, MariaDB of ~40G; database update starts and fails after around 15 minutes after startup. Rolling back to 2024.8.1 restored it to an operating state. Tried twice. Adding my logs for reference.

innodb_buffer_pool_size set to 2GB.

To Reply to my own message, I significantly reduced my database size by clearing some noisy sensors (e.g. power sensors) to ~7 days history. Also reduced from 100 days overall to 60 days. Database from 40 --> 14GB. Update now succeeded after ~20 minutes.

cybernard commented 2 months ago

I used mariadb-dump --no-create-info -u homeassistant -p"password" homeassistant states >home.sql to dump the states table.
Then I deleted the contents of the states table, used truncate command, but left the empty table there. I didn't delete the table so the upgrade process could upgrade its structure.
Rebooted home assistant
Allowed the database upgrade to happen, which only took about 20 mins.
Re-imported the contents of the states table from home.sql mariadb -u root -"password" homeassistant <home.sql Needless to say it took about 8 hours to re-import everything.

DAVIZINH0 commented 2 months ago

Today it finish my third attemp of migration and finis ok WOW!!! NOTHING CHANGE in my bbdd. and finish

I do nothing diferent that the 2 previous attempts. this is my history: 1.- first attempt: Errors en logs an finally error with migration failed and stop recorder 2.- reboot the machine 3.- message in the start with the migration starts 4.- erros in the log during the attempt of migration (about 7 hours after start the migration) 5.- migration failed and stop the recorder 6.- reboot the machine 7.- message in the start with the migration starts 8.- errors in the log during the attempt of migration. The errors are that I put in this issue. 9.- after more than 36 hours message of upgrade done. 10. i reboot the machine

no error, and aparently ok this is the log of the migration done: `Registrador: homeassistant.components.recorder.migration Fuente: components/recorder/migration.py:307 integración: Recorder (documentación, problemas) Ocurrió por primera vez: 09:25:46 (1 ocurrencias) Último inicio de sesión: 09:25:46

Upgrade to version 45 done`

For me this was the solution.

But i see more People with problems. I not close this issue but i dont know if is correct to keep open

cybernard commented 2 months ago

Can someone who completed the upgrade normally run the SQL command describe states So I can compared the structure of the states table?

bsafh commented 2 months ago

I used mariadb-dump -c -u homeassistant -p"password" homeassistant states >home.sql to dump the states table. ...

Re-imported the contents of the states table from home.sql mariadb -u root -"password" homeassistant <home.sql Needless to say it took about 8 hours to re-import everything.

But, does your dump not contain a

DROP TABLE IF EXISTS `states`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `states` (

statement at the beginning? A re-import would then re-create the old structure, not the updated one?

Am I missing anything here?

and furthermore, the dump seems to have multiple identical restraints from all the failed attempts in it:

 PRIMARY KEY (`state_id`),
  KEY `ix_states_attributes_id` (`attributes_id`),
  KEY `ix_states_old_state_id` (`old_state_id`),
  KEY `ix_states_last_updated_ts` (`last_updated_ts`),
  KEY `ix_states_context_id_bin` (`context_id_bin`(16)),
  KEY `ix_states_metadata_id_last_updated_ts` (`metadata_id`,`last_updated_ts`),
  CONSTRAINT `states_ibfk_1` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_10` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_11` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_12` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_13` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_14` FOREIGN KEY (`attributes_id`) REFERENCES `state_attributes` (`attributes_id`),
  CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_3` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_4` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_5` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_6` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_7` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_8` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`),
  CONSTRAINT `states_ibfk_9` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`)
) ENGINE=InnoDB AUTO_INCREMENT=355816004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

can those be deleted in the dump before re-import?

cybernard commented 2 months ago

FYI: I only did that to the states table so the other tables should have been upgraded.

This is why I want to see someone describe of the states database after upgrade, to see if I have the correct structure.

DAVIZINH0 commented 2 months ago

FYI: I only did that to the states table so the other tables should have been upgraded.

This is why I want to see someone describe of the states database after upgrade, to see if I have the correct structure.

states table:

cybernard commented 2 months ago

My table structure matches your. How about the indexes?

I have the above indexes.

DAVIZINH0 commented 2 months ago

My table structure matches your. How about the indexes?

I have the above indexes.

bsafh commented 2 months ago

... and the foreign keys?

oriolplav commented 2 months ago

... and the foreign keys?

Here you have the description and the CREATE TABLE. Hope it helps you.

Field Type Null Key Default Extra state_id bigint(20) NO PRI [NULL] auto_increment domain varchar(64) YES [NULL]
entity_id varchar(255) YES MUL [NULL]
state varchar(255) YES [NULL]
attributes longtext YES [NULL]
event_id bigint(20) YES MUL [NULL]
last_changed datetime(6) YES [NULL]
last_updated datetime(6) YES [NULL]
created datetime(6) YES [NULL]
context_id varchar(36) YES [NULL]
context_user_id varchar(36) YES [NULL]
datetime datetime YES [NULL] VIRTUAL GENERATED old_state_id bigint(20) YES MUL [NULL]
attributes_id bigint(20) YES MUL [NULL]
origin_idx int(11) YES [NULL]
context_parent_id varchar(36) YES [NULL]
last_updated_ts double YES MUL [NULL]
last_changed_ts double YES [NULL]
context_id_bin tinyblob YES MUL [NULL]
context_user_id_bin tinyblob YES [NULL]
context_parent_id_bin tinyblob YES [NULL]
metadata_id bigint(20) YES MUL [NULL]
last_reported_ts double YES [NULL]

-- homeassistant.states definition

CREATE TABLE states ( state_id bigint(20) NOT NULL AUTO_INCREMENT, domain varchar(64) DEFAULT NULL, entity_id varchar(255) DEFAULT NULL, state varchar(255) DEFAULT NULL, attributes longtext DEFAULT NULL, event_id bigint(20) DEFAULT NULL, last_changed datetime(6) DEFAULT NULL, last_updated datetime(6) DEFAULT NULL, created datetime(6) DEFAULT NULL, context_id varchar(36) DEFAULT NULL, context_user_id varchar(36) DEFAULT NULL, datetime datetime GENERATED ALWAYS AS (convert_tz(last_updated,'UTC','Europe/Madrid')) VIRTUAL, old_state_id bigint(20) DEFAULT NULL, attributes_id bigint(20) DEFAULT NULL, origin_idx int(11) DEFAULT NULL, context_parent_id varchar(36) DEFAULT NULL, last_updated_ts double DEFAULT NULL, last_changed_ts double DEFAULT NULL, context_id_bin tinyblob DEFAULT NULL, context_user_id_bin tinyblob DEFAULT NULL, context_parent_id_bin tinyblob DEFAULT NULL, metadata_id bigint(20) DEFAULT NULL, last_reported_ts double DEFAULT NULL, PRIMARY KEY (state_id), KEY ix_states_event_id (event_id), KEY idx_states_state_id_entity_id (state_id,entity_id), KEY idx_states_entity_id_state_id (entity_id,state_id), KEY ix_states_old_state_id (old_state_id), KEY idx_states_entity_id (entity_id), KEY ix_states_attributes_id (attributes_id), KEY ix_states_last_updated_ts (last_updated_ts), KEY ix_states_context_id_bin (context_id_bin(16)), KEY ix_states_metadata_id_last_updated_ts (metadata_id,last_updated_ts), KEY idx_states_metadata_id_state_id (metadata_id,state_id), CONSTRAINT states_ibfk_1 FOREIGN KEY (old_state_id) REFERENCES states (state_id), CONSTRAINT states_ibfk_2 FOREIGN KEY (old_state_id) REFERENCES states (state_id), CONSTRAINT states_ibfk_3 FOREIGN KEY (attributes_id) REFERENCES state_attributes (attributes_id), CONSTRAINT states_ibfk_4 FOREIGN KEY (metadata_id) REFERENCES states_meta (metadata_id) ) ENGINE=InnoDB AUTO_INCREMENT=79016718 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

cybernard commented 2 months ago

same problem here. MySQL-HA-Addon, innodb_buffer_pool_size temporarly to 4 GB(*), HA supervised, running on a 6-Core-Xeon 54xx with 16 GB RAM.

Database is still on Version 44 after 3 days. "Databse update in progress" displayed for a few hours, then disappears, then reappears the next day without touching HA ...

Database size is 96 GB.

(*)there seems to be absolutely no way to set this and other mysql.cnf parameters permenently with the HA MySQL addon (no, editing the values in .../addon/mysql/mysql.cnf (or whatever the name is) does NOT work

Does anyone have the SQL statement for the database schema update? perhaps it works running it from the docker container console in the mariadb prompt?

This is why I moved my database to its own virtual machine so I could control my.cnf at will.

bsafh commented 2 months ago

CONSTRAINT states_ibfk_1 FOREIGN KEY (old_state_id) REFERENCES states (state_id), CONSTRAINT states_ibfk_2 FOREIGN KEY (old_state_id) REFERENCES states (state_id),

2 tries = 2x FK contraint?

I have 6 of those from 6 tries ...

:-(

madboy134 commented 2 months ago

Hola, Mismo problema. Lo solucione aumentado la capacidad de la maquina que contenía la base de datos.

English, Same problem. I solved it by increasing the capacity of the machine containing the database.

Saludos.

bsafh commented 2 months ago

This is why I moved my database to its own virtual machine so I could control my.cnf at will.

Looks like I have found aworkaround.

1) I gave a bit more power to the VM running HA + MariaDB Addon: 64 GB RAM + 12 vCPUs (Xeon)

2) my MariaDB is running as Addon. I could have migrated it to a new virtual machine, but, well, I like the automatic update of addons in HA and another VM is another admin task ... But, you can "hack" the docker container setting (at least on a "supervised" HA instance, which is what I am running). The Addon does not allow changing mariadb-server.cnf But you can find the actual mariadb-server.cnf which is used by the docker container / HA Addon by logging into the host OS (Debian) and issue this cli command find /var/lib/docker/overlay2/ -name mariadb-server.cnf so:

PERFORM A BACKUP of your database AND HA !!
stop the Addon

change the config file found

innodb_buffer_pool_size = 56G
innodb_buffer_pool_chunk_size = 32G
max_binlog_size = 32G 
innodb_log_buffer_size = 8G

wait_timeout=1172800
lock_wait_timeout=172800
interactive_timeout=172800

start MariaDB addon
stop HA core on cli ha core stop in order to prevent HA from writing into the database

Of course this is a temporary change and it will be overwritten with the next Addon update!

5) Next I followed "use the force, read the source" and found the bit of source code in the HA core recorder source code where the actual database migration (schema 44 to 45) takes place: migration.py (line 1657, class _SchemaVersion45Migrator(_SchemaVersionMigrator, target_version=45))

6) then I deleted all CONTRAINTS, FOREIGN_KEYS and INDEXes on the table states (there were lots of duplicate ones from the failed migration attempts) (using either phpMyAdmin, the MariaDB CLI or whatever SQL tool you liek, e.g. HeidiSQL on Windows).

7) from that you can derive the actual SQL statements issued against MariaDB to perform the migration, e.g.: alter table states add foreign_key(event_id); ... ... alter table states add foreign key(attributes_id) references state_attributes (attribute_id); each of those comands took between 10 minutes and nearly 2 days ...

8) rename the CONTRAINTS (foreign keys, indexes, constrains) according to the naming schema used by HA (idx_...), as seen in the other tables not affected by the migration (have a look using your favorite SQL tool or phpMyAdmin)

9) insert (using SQL command) a new line into the table schema_changes for the new schama version 45

10) at this point you can restat HA core and check whether everything is running OK. HA should not try to do the migration again.

11) revert all changes in mariadb-server.cnf and the sizing of the VM, reboot VM, enjoy ...

seems to run now...

... at least the following update from HA 2024.8.2 to 2024.8.3 did run without problems, and all history graphs are OK again.

surfudder commented 2 months ago

I figured out why my database migration failed. I did not have enough free disk space. After adding storage it passed fine. According to de documentation it is advised to have free space in the amount of the database size. So advise to check the amount of free space. Worked for me.

cybernard commented 2 months ago

So they upgraded/changed the database again and I had the same problem. It took 8 hours and didn't complete the upgrade.

So I dumped the states DB to a file

mariadb-dump --no-create homeassistant states >states.sql Dumped the structure of the DB to a file
mariadb-dump --no-data homeassistant states >table_states.sql
Drop table states (take a long time even with NVMe) because the database is 1 thread per connection so effectively locked at 1 core no matter how many you have)
re-import the structure mariadb homeassistant <tables_states.sql
Reboot homeassistant Let the upgrade happen, with 102gb removed and only a single 4gb table left it still took 20-40 min
Re-import the data mariadb homeassistant <states.sql

mariadb-dump --no-data homeassistant states >table_states-new.sql

diff tables_states.sql table_states-new.sql

Now you can compare the changes made to the table.

ilttkolosov commented 2 months ago

So they upgraded/changed the database again and I had the same problem. It took 8 hours and didn't complete the upgrade.

So I dumped the states DB to a file

mariadb-dump --no-create homeassistant states >states.sql Dumped the structure of the DB to a file

mariadb-dump --no-data homeassistant states >table_states.sql

Drop table states (take a long time even with NVMe) because the database is 1 thread per connection so effectively locked at 1 core no matter how many you have)

re-import the structure mariadb homeassistant <tables_states.sql

Reboot homeassistant Let the upgrade happen, with 102gb removed and only a single 4gb table left it still took 20-40 min

Re-import the data mariadb homeassistant <states.sql

mariadb-dump --no-data homeassistant states >table_states-new.sql

diff tables_states.sql table_states-new.sql

Now you can compare the changes made to the table.

Thanks for this journey. I've been thinking for weeks about how to upgrade and it finally helped. but I still have one question - how do I delete the states.sql file, which was temporary? It just takes up a lot of space and I don't understand where it lies.

cybernard commented 2 months ago

I don't know where it is, but here's how to find it find / -iname "states.sql"

It should be in the directory you executed the dump command from, but in case you don't know where that is use the find command.

ilttkolosov commented 2 months ago

I don't know where it is, but here's how to find it find / -iname "states.sql"

It should be in the directory you executed the dump command from, but in case you don't know where that is use the find command.

Thank you!

bsafh commented 2 months ago

So they upgraded/changed the database again and I had the same problem. It took 8 hours and didn't complete the upgrade.

So I dumped the states DB to a file

be aware that this method only works as long as the database structure changes are only for INDEX, FOREIGN KEYS/CONSTRAINT - but not when the data fields change (renamed fields/columns, deleted fields, additional fields). In the latter case the data import will fail because there are unmatched fields and the import can't put the data into the columns ...

cybernard commented 2 months ago

It's all we can do until the home assistant team figures out why it fails and fixing the problem. If a field is added, mariadb will just fill it with null.

bsafh commented 2 months ago

It's all we can do until the home assistant team figures out why it fails and fixing the problem. If a field is added, mariadb will just fill it with null.

well, that's pretty obvious, I guiess

there design goal is a small installation on a RaspberryPi or alike with a small (<2 GB) database
the MariaDB Addon is specifically configured to run on an extremly limited machine with very small buffer sizes etc
so I guess there are no tests during development with larger databases (>10 GB)
the developer reversed their earlier recommendation to use MariaDB instead of SQlite, they now state that sqlite (after improvements) is "more stable" than MariaDB (well, yes, with that of a small configuration of the MariaDB addon, that may be, in general, it's not)
so I do not expect this situation being resolved at all
and there is one problem with MariaDB: one transaction = one thread. So any ALTER TABLE on a really large table will run "forever", especiially on underpowered hardware. And as soon as the time it takes to do one of the ALTER TABLE exceeds the configured timeouts - boom, that ALTER TABLE fails. It's not possible to split up one ALTER TABLE to set a single index into several..
with a 96 GB database, I had to crank up the VM MariaDB is running in to 64 GB RAM and allow buffer sizes up to 54 GB and the buffer chunk size to 48 GB (that sit's on 512 kB in the addon config ...) and set the timeout to over 36 hours in order to get the ALTER TABLE for the "states" table to be successful (but on spinning disks, not NVME, that would speed it up).

cybernard commented 2 months ago

How do I increase the timeout? what is the configuration file setting?

The mariadb is designed for enterprise with database in the terabytes, how does the Mysql addon limit this potiental?

and there is one problem with MariaDB: one transaction = one thread.

Yes, but the states table could be divide by sensor into 4 tables and then each table could run at the same time thus using 4 cores at once.

In fact each different add-on could use a different table, as mysql/mariadb support something like 2 billion tables. This would solve to problems, since each on in separate you would have a lot more multi-threading going on and faster multi-core CPU would complete tasks faster. When upgrades happen there would be less risk of timeout or failure because no single table would have the MAJORITY the data. Also having all your data in one basket all provides are greater opportunity for you to lose all your data.

Another issue/question when I do a mariab-dump I get a 52gb, yet the states table is over a 100gb on disk.