home-assistant
Database migration errors in 2024.8.2
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
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 closeCloses the issue. -
@home-assistant rename Awesome new titleRenames the issue. -
@home-assistant reopenReopen the issue. -
@home-assistant unassign recorderRemoves the current integration label and assignees on the issue, add the integration domain after the command. -
@home-assistant add-label needs-more-informationAdd a label (needs-more-information, problem in dependency, problem in custom component) to the issue. -
@home-assistant remove-label needs-more-informationRemove 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)
![home-assistant[bot] avatar](https://avatars.githubusercontent.com/in/97978?v=4 "Published by a pub.dev verified publisher"){kind=small}
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.
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)
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)
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
What is the size of your database?? I think the problem is with Big databases, but in not a expert
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 :)
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
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)
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.
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?
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`
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.
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
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.
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.
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
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.
- 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.
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
Can someone who completed the upgrade normally run the SQL command describe states So I can compared the structure of the states table?
- 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?
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.
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:
My table structure matches your.
How about the indexes?
I have the above indexes.
My table structure matches your. How about the indexes?
I have the above indexes.
