search

home-assistant / operating-system

:beginner: Home Assistant Operating System
Apache License 2.0
5.1k stars 994 forks source link

SQL Error Migration Data Base #3696

Closed melmager closed 1 week ago

melmager commented 1 week ago

Describe the issue you are experiencing

update to

Core 2024.11.2 Supervisor 2024.11.4 Operating System 10.5 Frontend 20241106.2

on Raspberry Pi 4

SQL error during DB Migration.

running MariaDB Version: 2.7.1

configuration.yaml recorder: purge_keep_days: 460 db_url: mysql://homeassistant:mydata@core-mariadb/home?charset=utf8mb4

What operating system image do you use?

rpi4-64 (Raspberry Pi 4/400 64-bit OS)

What version of Home Assistant Operating System is installed?

11.2

Did the problem occur after upgrading the Operating System?

Yes

Hardware details

Rasberry Pi 4 USB Storage

Steps to reproduce the issue

just an Update Logs are second attempt from the System to change the SQL Migration

Anything in the Supervisor logs that might be useful for us?

2024-11-24 13:54:04.458 INFO (MainThread) [supervisor.backups.manager] Found 15 backup files
2024-11-24 13:55:25.191 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 13:55:27.995 INFO (MainThread) [supervisor.homeassistant.api] Updated Home Assistant API token
2024-11-24 14:00:27.188 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:05:29.241 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:10:31.195 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:15:33.192 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:19:55.262 INFO (MainThread) [supervisor.host.info] Updating local host information
2024-11-24 14:19:56.819 INFO (MainThread) [supervisor.host.services] Updating service information
2024-11-24 14:19:56.833 INFO (MainThread) [supervisor.host.network] Updating local network information
2024-11-24 14:19:57.375 INFO (MainThread) [supervisor.host.sound] Updating PulseAudio information
2024-11-24 14:19:57.417 INFO (MainThread) [supervisor.host.manager] Host information reload completed
2024-11-24 14:20:35.465 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:25:29.443 INFO (MainThread) [supervisor.homeassistant.api] Updated Home Assistant API token
2024-11-24 14:25:37.220 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:30:39.190 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:35:41.198 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:40:43.189 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:45:45.185 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:50:47.183 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:53:03.818 INFO (MainThread) [supervisor.resolution.check] Starting system checks with state running
2024-11-24 14:53:03.819 INFO (MainThread) [supervisor.resolution.checks.base] Run check for trust/supervisor
2024-11-24 14:53:03.836 INFO (MainThread) [supervisor.resolution.checks.base] Run check for docker_config/system
2024-11-24 14:53:03.837 INFO (MainThread) [supervisor.resolution.checks.base] Run check for security/core
2024-11-24 14:53:03.837 INFO (MainThread) [supervisor.resolution.checks.base] Run check for disabled_data_disk/system
2024-11-24 14:53:03.838 INFO (MainThread) [supervisor.resolution.checks.base] Run check for pwned/addon
2024-11-24 14:53:03.916 INFO (MainThread) [supervisor.resolution.checks.base] Run check for free_space/system
2024-11-24 14:53:03.916 INFO (MainThread) [supervisor.resolution.checks.base] Run check for multiple_data_disks/system
2024-11-24 14:53:03.917 INFO (MainThread) [supervisor.resolution.checks.base] Run check for dns_server_ipv6_error/dns_server
2024-11-24 14:53:03.917 INFO (MainThread) [supervisor.resolution.checks.base] Run check for dns_server_failed/dns_server
2024-11-24 14:53:03.918 INFO (MainThread) [supervisor.resolution.checks.base] Run check for ipv4_connection_problem/system
2024-11-24 14:53:03.918 INFO (MainThread) [supervisor.resolution.check] System checks complete
2024-11-24 14:53:03.919 INFO (MainThread) [supervisor.resolution.evaluate] Starting system evaluation with state running
2024-11-24 14:53:04.404 INFO (MainThread) [supervisor.resolution.evaluate] System evaluation complete
2024-11-24 14:53:04.405 INFO (MainThread) [supervisor.resolution.fixup] Starting system autofix at state running
2024-11-24 14:53:04.405 INFO (MainThread) [supervisor.resolution.fixup] System autofix complete
2024-11-24 14:53:45.210 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/dmslabsbr/hoymiles repository
2024-11-24 14:53:45.235 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/GollumDom/addon-repository repository
2024-11-24 14:53:45.242 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/music-assistant/home-assistant-addon repository
2024-11-24 14:53:45.256 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/esphome/home-assistant-addon repository
2024-11-24 14:53:45.263 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/home-assistant/addons repository
2024-11-24 14:53:45.279 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/hassio-addons/repository repository
2024-11-24 14:53:48.822 INFO (MainThread) [supervisor.store] Loading add-ons from store: 85 all - 0 new - 0 remove
2024-11-24 14:53:48.822 INFO (MainThread) [supervisor.store] Loading add-ons from store: 85 all - 0 new - 0 remove
2024-11-24 14:55:29.727 INFO (MainThread) [supervisor.homeassistant.api] Updated Home Assistant API token
2024-11-24 14:55:49.179 WARNING (MainThread) [supervisor.addons.options] Option 'map' does not exist in the schema for Samba share (core_samba)
2024-11-24 14:59:22.424 INFO (MainThread) [supervisor.api.middleware.security] /network/info access from a0d7b954_ssh
2024-11-24 14:59:22.429 INFO (MainThread) [supervisor.api.middleware.security] /network/info access from a0d7b954_ssh
2024-11-24 14:59:22.433 INFO (MainThread) [supervisor.api.middleware.security] /host/info access from a0d7b954_ssh
2024-11-24 14:59:22.439 INFO (MainThread) [supervisor.api.middleware.security] /core/info access from a0d7b954_ssh
2024-11-24 14:59:52.119 INFO (MainThread) [supervisor.api.middleware.security] /supervisor/logs access from a0d7b954_ssh

Anything in the Host logs that might be useful for us?

Logger: homeassistant.helpers.recorder
Quelle: helpers/recorder.py:101
Erstmals aufgetreten: 22. November 2024 um 14:46:33 (7 Vorkommnisse)
Zuletzt protokolliert: 07:34:26

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.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/helpers/recorder.py", line 101, in session_scope
    yield session
  File "/usr/src/homeassistant/homeassistant/components/recorder/migration.py", line 631, 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

and

Logger: homeassistant.components.recorder.migration
Quelle: components/recorder/migration.py:631
Integration: Recorder (Dokumentation, Probleme)
Erstmals aufgetreten: 22. November 2024 um 14:46:33 (7 Vorkommnisse)
Zuletzt protokolliert: 07:34:26

Could not modify column MODIFY state_id BIGINT NOT NULL AUTO_INCREMENT 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: (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 631, 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) (1206, 'The total number of locks exceeds the lock table size')
[SQL: ALTER TABLE states MODIFY state_id BIGINT NOT NULL AUTO_INCREMENT]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

System information

Version core-2024.11.2 Installationstyp Home Assistant OS Entwicklung false Supervisor true Docker true Benutzer root Virtuelle Umgebung false Python-Version 3.12.4 Betriebssystemfamilie Linux Betriebssystem-Version 6.1.21-v8 CPU-Architektur aarch64 Zeitzone Europe/Berlin Konfigurationsverzeichnis /config

Additional information

No response

github-actions[bot] commented 1 week ago

:wave: @melmager, thanks for reporting an issue!

It looks like this issue is related to Home Assistant Core. Please check the Home Assistant Core repository, the issue might have been reported already. Open a new issue in that repository if you can't find a matching issue.