Errors after upgrade from schema version 46 to 47 - The total number of locks exceeds the lock table size

Hi,

After the upgrate to the latest 2024.9.x version the database needed some migration but the ended with an error: The total number of locks exceeds the lock table size

After some goolge search I apparently found some hints to changing the configuration, but I have no idea how to access or even find the configuration in home assistant supervised installation ?

Thanks

4 Likes

How big is your database?

What were the hints? Any url?

If it helps anyone else, I was using mariadb v10 (mariadb:10.11.2-jammy). I upgraded to latest (11.5.2-MariaDB-ubu2404), and I added the following to my custom.cnf file:

[mariadb]
innodb_buffer_pool_size = 512M
innodb_lock_wait_timeout = 300

I’m not sure which change impacted it, but i read the lock issue was more prevalent within v10 installations ( ER_LOCK_TABLE_FULL — MariaDB Documentation)

My database is ~27GB:

and it took about 30-40mins to complete the upgrade from 46->47 (on NVMe).

image

Hope this helps someone else as I’ve been trying to fix it for a few days now! Give me a like if so! :slight_smile:

Hi,
I’m on 2024.7.4 and just tried to update to 2024.10.1.
I got a 17GB database, which also failed to update :frowning: (see below for the logs)
I use the mariaDB as an addons and have no idea how to change the configuration.
My system run on a SSD it run for 3h48 before it crash but strangely I still have a log saying it’s done. fortunately i’m on procmox so I’m gonna restore the backup of the full system, but I wonder How could I make sure the migration actually succeed (or even check it’s actually good or restart it without a full restore each time)

first saying it’s done

Enregistreur: homeassistant.components.recorder.migration
Source: components/recorder/migration.py:308
intégration: Recorder (documentation, problèmes)
S'est produit pour la première fois: 14:54:56 (3 occurrences)
Dernier enregistrement: 16:40:54

Upgrade to version 44 done
Upgrade to version 45 done
Upgrade to version 46 done

then crash (so probably between 46-> 47 ?)

Enregistreur: homeassistant.components.recorder.core
Source: components/recorder/core.py:974
intégration: Recorder (documentation, problèmes)
S'est produit pour la première fois: 19:31:39 (1 occurrences)
Dernier enregistrement: 19:31:39

Database error during schema migration
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/local/lib/python3.12/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (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/core.py", line 974, in _migrate_schema
    new_schema_status = migrator(
                        ^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 336, in migrate_schema_live
    schema_status = _migrate_schema(
                    ^^^^^^^^^^^^^^^^
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 302, in _migrate_schema
    _apply_update(instance, hass, engine, session_maker, new_version, start_version)
  File "/usr/src/homeassistant/homeassistant/components/recorder/util.py", line 726, in wrapper
    job(instance, *args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 860, in _apply_update
    migrator_cls(instance, hass, engine, session_maker, old_version).apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 906, in apply_update
    self._apply_update()
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 1750, in _apply_update
    _restore_foreign_key_constraints(
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 698, in _restore_foreign_key_constraints
    _add_constraint(session_maker, add_constraint, table, column)
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 730, 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) (1206, 'The total number of locks exceeds the lock table size')
[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)
1 Like

Where can I find this custom.cnf file to make these changes? Is it something within Home Assistant or in MariaDB?

Have you found a solution yet? I also run MariaDB as a Home Assistant Addon.

Update: I decided to uninstall the addon and remove all associated data then reinstall and start from scratch with the new schema. It seems to be working now, though I have of course lost all historic data.