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
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).
Hope this helps someone else as I’ve been trying to fix it for a few days now! Give me a like if so! 
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 
(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)
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.