Database migration errors in 2024.8.2 #124186
Comments
|
Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration ( Code owner commandsCode owners of
(message by CodeOwnersMention) recorder documentation |
|
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:
|
|
Also errors here, I've tried 2 times and after 13 hours I got this: Database error during schema migration The above exception was the direct cause of the following exception: Traceback (most recent call last): During handling of the above exception, another exception occurred: Traceback (most recent call last): The above exception was the direct cause of the following exception: Traceback (most recent call last): |
|
I am also having similar problems Logger: homeassistant.components.recorder.core Traceback (most recent call last): The above exception was the direct cause of the following exception: Traceback (most recent call last): During handling of the above exception, another exception occurred: Traceback (most recent call last): The above exception was the direct cause of the following exception: Traceback (most recent call last): |
|
What is the size of your database?? |
|
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. Creating a temp table:CREATE TABLE temp_ids (event_id INT PRIMARY KEY); Adding event_idINSERT INTO temp_ids (event_id) Removing this id'sDELETE e This took 1 minute.... |
|
Interesting idea. 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) |
|
my database is about 100gb |
|
my database is about 5gb |
|
same here |
|
Getting the same. My database is 90GB. Updated from 2024.6 to 2024.8.2. |
|
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!!! I do nothing diferent that the 2 previous attempts. this is my history: no error, and aparently ok Upgrade to version 45 done` |
|
I got similar issue here but migration from schema 43 to 45 with mariaDb. |
|
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. |
|
Same error here now, after 26 hours trying the migration for the third time... 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 |
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. |
|
For me this was the solution. But i see more People with problems. |
|
Can someone who completed the upgrade normally run the SQL command describe states |
But, does your dump not contain a 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: 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. |
states table: |
|
My table structure matches your. I have the above indexes. |
|
|
... and the foreign keys? |
Here you have the description and the CREATE TABLE. Hope it helps you. Field Type Null Key Default Extra -- homeassistant.states definition CREATE TABLE |
This is why I moved my database to its own virtual machine so I could control my.cnf at will. |
2 tries = 2x FK contraint? I have 6 of those from 6 tries ... :-( |
|
Hola, English, Saludos. |
Looks like I have found aworkaround.
Of course this is a temporary change and it will be overwritten with the next Addon update!
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. |
|
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. |
|
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-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 |
|
I don't know where it is, but here's how to find it 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! |
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 ... |
|
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
|
|
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?
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. Another issue/question when I do a mariab-dump I get a 52gb, yet the states table is over a 100gb on disk. |
|
@cybernad , thank you very much for the guide! I finally was able to get through this update. IDK if ha devs are reading this, probably not, but altering prod tables is a huge no-no in any production environment. Yes, schema changes happen, but a good practice is to create a new table, copy data, and drop the old table. |
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:
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_1FOREIGN KEY (metadata_id) REFERENCESstatistics_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_1FOREIGN KEY (metadata_id) REFERENCESstatistics_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_1FOREIGN KEY (metadata_id) REFERENCESstatistics_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_1FOREIGN KEY (metadata_id) REFERENCESstatistics_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
The text was updated successfully, but these errors were encountered: