Database migration issue after 2024.8 upgrade

tokenize47 commented 3 months ago

The problem

I got the following error while DB upgrade was processing after 2024.8 update. I'm running MariaDB. How should I proceed?

2024-08-08 11:29:43.674 ERROR (Recorder) [homeassistant.components.recorder.migration] Could not update foreign options in states table ... sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1206, 'The total number of locks exceeds the lock table size') [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)

EDIT - Meanwhile I got the message in the log that the upgrade to schema version 44 has ended. Can I just ignore the error?

What version of Home Assistant Core has the issue?

core-2024.8.0

What was the last working version of Home Assistant Core?

core-2024.7

What type of installation are you running?

Home Assistant Container

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?

2024-08-08 11:29:43.674 ERROR (Recorder) [homeassistant.components.recorder.migration] 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: (1206, 'The total number of locks exceeds the lock table size')
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 694, in _restore_foreign_key_constraints
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) (1206, 'The total number of locks exceeds the lock table size')
[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)

Additional information

No response

tokenize47 commented 3 months ago

I should add that meanwhile I got the message:

2024-08-08 11:45:15.372 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 44 done

ecylcje commented 3 months ago

I have a similar issue in the data migration to 2024.08. Not sure if it is related, as I didn't get the final 'upgrade to version 44 done'.

Logger: homeassistant.components.recorder.migration Source: components/recorder/migration.py:578 integration: Recorder (documentation, issues) First occurred: 15:33:47 (2 occurrences) Last logged: 15:39:32

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

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

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

tokenize47 commented 3 months ago

Your situation seems to be different.

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1034, "Index for table 'states' is corrupt; try to repair it")

You seem to have a corrupted index. Try to connect directly to MariaDB and repair it.

home-assistant[bot] commented 3 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)}}

mib1185 commented 3 months ago

duplicated in #123451

ljmerza commented 2 months ago

This is not a dupe. The error relates to max locks reached.

ljmerza commented 2 months ago

please reopen this it is not a dup and the fix in the linked issue doesnt not fix it. you will keep getting the same error here no matter how many times to trying to migrate

mib1185 commented 2 months ago

The initial reported error here is The total number of locks exceeds the lock table size and that is the same, as mentioned in https://github.com/home-assistant/core/issues/123358#issuecomment-2278744512

FloatOverflow commented 2 months ago

I had the same problem because I had created in database (postgresql) a some views based on table "statistics" and table "states". When I manually tried to alter tables I saw an error with altering tables. So I deleted the views and restarted Home Assistant - the problem was solved. Then I recreated the views.

YewTreeWeb commented 2 months ago

I'm having the same issue with Home Assistant 2024.9.0. The error I'm getting is MySQLdb.OperationalError: (1206, 'The total number of locks exceeds the lock table size'). Please can this ticket be reopened

hitesh-singh commented 2 months ago

Same here

Core 2024.9.0 Supervisor 2024.08.0 Operating System 13.1 Frontend 20240904.0

Getting the following error

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1114, "The table 'states' is full") [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)

ljmerza commented 2 months ago

you can increase the number of locks available. This config for mysql allowed me to run the migration again and finish it without issues. you can probably have a smaller buffer pool

[mysqld]
# increase table lock size
innodb_buffer_pool_size=2G
innodb_lock_wait_timeout=50

kuttikutt commented 2 months ago

[mysqld]
# increase table lock size
innodb_buffer_pool_size=2G
innodb_lock_wait_timeout=50

Hello, I have the same issue, but my Home Assistant is running on a RPI5 with a DB using the MariaDB addon.

Can you give me a hint where I can increase the lock size? On a normal setup I just would go to /etc/mysql, but I don't have this path on my system. I guess, I have to add it somewhere else, but where?

DAVIZINH0 commented 2 months ago

https://mariadb.com/docs/server/storage-engines/innodb/operations/configure-buffer-pool/

SET GLOBAL innodb_buffer_pool_size=(2 1024 1024 * 1024);

this set the innodb_buffer_pool_size to 2GB but return to default after a reboot of addon

kuttikutt commented 2 months ago

Ahh there! Thanks a lot!

Let's see if Home Assistant manages to alter the table now ;)

I actually need to change this just to get ALTER TABLE states MODIFY state_id BIGINT NOT NULL AUTO_INCREMENT finished ...

By the way - I guess it's a bug... If I select "show only active" the percentage is multiplied by 2 grafik (sorry for german layout) 102,289% :D

hitesh-singh commented 2 months ago

Same here

Core 2024.9.0 Supervisor 2024.08.0 Operating System 13.1 Frontend 20240904.0

Getting the following error

sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1114, "The table 'states' is full") [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)

Okay, so for my case the issue with my VM's storage. I Incresed the Storage side and restarted HA and the migrations was completed successfully.

andbad commented 2 months ago

I've similar error when upgrade from 2024.8.3 to 2024.9.1. (MariaDB in container, HA in venv, Ubuntu 22.04.4)

2024-09-07 00:34:59.152 ERROR (Recorder) [homeassistant.components.recorder.core] Database error during schema migration
Traceback (most recent call last):
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/andbad/.homeassistant/homeassistant/lib/python3.12/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1034, "Index for table 'homeassistant/#sql-alter-1-12' is corrupt; try to repair it")

I can't fine table sql-alter-1-12 or similar in homeassistant db, so I don't know how to repair it.

If I restart HA, the number after "sql-alter-1-" changes.

By(t)e

SeLLeRoNe commented 2 months ago

Is this temp increase of locks something that's going to be introduced by HA during migration?

I am using the MariaDB Add-on and getting the same error.

While I do understand the problem and the solution, there is no "easy way" to fix for someone that's not "technical" and knows (nor potentially cares) on how to connect to a MySQL database and change its settings (add-ons configuration doesn't seem to allow those either).

In my specific case, being on holiday makes me so I cannot connect to the MySQL server remotely to apply that change, no big deal, I can fix it once back, but other users might never be able to have it fixed without someone helping them on each step required.

I would recommend having the migration script to apply the change before the update query and revert back to the original value once done (or in case of failure).

There obviously might be "complications" due to access level required for such a change, the "ha" MySQL user might not be able to apply those changes "on the fly"

Just a suggestion/feedback ;)

Thanks for taking the time on reading

A

Ralphy140 commented 2 months ago

Ahh there! Thanks a lot!

Let's see if Home Assistant manages to alter the table now ;)

I actually need to change this just to get ALTER TABLE states MODIFY state_id BIGINT NOT NULL AUTO_INCREMENT finished ...

By the way - I guess it's a bug... If I select "show only active" the percentage is multiplied by 2 (sorry for german layout) 102,289% :D

Would you be able to explain how you got access to the MariaDB to perform the command? I am not entirely sure what system I am supposed to use to run the commands, I have installed the MariaDB client on a linux system but that doesn't seem to offer a persistent connection to execute the commands on

kuttikutt commented 2 months ago

Sure ;) I use phpmyadmin. https://community.home-assistant.io/t/home-assistant-community-add-on-phpmyadmin/171729

Actually, I am using MySQL DB's since around 20 years, but never changed the config of the server within the mysql shell nor phpmyadmin. I always tweaked the settings, using the config files.

After the increase of the innodb_buffer_pool_size the update went through without any trouble, It took some time, but finished successfully

One sidenote: If the update fails, you should not ignore it. On my old system (RPI4 + 400GB SSD) it failed over and over again, then I switched to a RPI5 with a 1TB NVME. On checking the old SSD I was a bit shocked to see, that it had already over 600000 GB (yes, that's 60 TB) of data written in just around 1,5 years ...

This can't be normal usage, I guess it did copy my 60 GB database over and over again, while failing to complete the altering of the tables...

Just to compare: I run a dedicated server, with multiple blogs, hundreds of mail accounts and a forum with around 25000 users. This Server manages around 15 TB of writing to it's NVME's per annum. On the other hand would it be amazing if the little home assistant "outwrites" a real internet server :D

andbad commented 2 months ago

One question. I should increase buffer pool and then update home assistant or update HA and then update buffer pool?

By(t)e

SeLLeRoNe commented 2 months ago

Buffer first

tokenize47 commented 2 months ago

Meanwhile HA was able to complete the migration successfully for me. The locks were probably due to an increased activity in the DB at the time.

Nevertheless, the suggestion to temporarily increase the number of locks before migration seems like a good one.

bgou commented 2 months ago

Ahh there! Thanks a lot! Let's see if Home Assistant manages to alter the table now ;) I actually need to change this just to get ALTER TABLE states MODIFY state_id BIGINT NOT NULL AUTO_INCREMENT finished ... By the way - I guess it's a bug... If I select "show only active" the percentage is multiplied by 2 (sorry for german layout) 102,289% :D

Would you be able to explain how you got access to the MariaDB to perform the command? I am not entirely sure what system I am supposed to use to run the commands, I have installed the MariaDB client on a linux system but that doesn't seem to offer a persistent connection to execute the commands on

I faced similar challenges. I used the terminal addon with Protection Mode turned off, and ran

docker exec -it addon_core_mariadb bash

to gain access to the MariaDB container. From there, ran

mysql

to get in the mysql interface. Then

SET GLOBAL innodb_buffer_pool_size=(2 * 1024 * 1024 * 1024);

to set the buffer pool size to 2GB.

You can verify it by looking at the log tab in the MariaDB add on page.

Hope this helps!

home-assistant / core