search

home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.
https://www.home-assistant.io
Apache License 2.0
69.62k stars 28.8k forks source link

sqlalchemy error #117303

Open thomasdelaet opened 1 month ago

thomasdelaet commented 1 month ago

The problem

Hey,

I've been experiencing an error with the recorder lately, linked to some issue in the database. Would be great to understand if this is a bug or if I did something stupid ;-)

What version of Home Assistant Core has the issue?

2024.5.2

What was the last working version of Home Assistant Core?

2024.5.1

What type of installation are you running?

Home Assistant Core

Integration causing the issue

recorder

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder/

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

2024-05-12 13:26:41.650 ERROR (Recorder) [homeassistant.components.recorder.core] Error while processing event CompileMissingStatisticsTask()
Traceback (most recent call last):
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 902, in _guarded_process_one_task_or_event_or_recover
    self._process_one_task_or_event_or_recover(task)
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 921, in _process_one_task_or_event_or_recover
    self._commit_event_session_or_retry()
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 1185, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 1221, in _commit_event_session
    session.commit()
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1972, in commit
    trans.commit(_to_root=True)
  File "<string>", line 2, in commit
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1257, in commit
    self._prepare_impl()
  File "<string>", line 2, in _prepare_impl
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
    ret_value = fn(self, *arg, **kw)
                ^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1232, in _prepare_impl
    self.session.flush()
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4296, in flush
    self._flush(objects)
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4431, in _flush
    with util.safe_reraise():
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4392, in _flush
    flush_context.execute()
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 463, in execute
    n.execute_aggregate(self, set_)
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 759, in execute_aggregate
    persistence.save_obj(
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1143, in _emit_insert_statements
    result = connection.execute(
             ^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
           ^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_insertmany_context(dialect, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2128, in _exec_insertmany_context
    self._handle_dbapi_exception(
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2360, in _handle_dbapi_exception
    raise exc_info[1].with_traceback(exc_info[2])
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2120, in _exec_insertmany_context
    dialect.do_execute(
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
ValueError: A string literal cannot contain NUL (0x00) characters.
2024-05-12 13:26:41.670 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: A string literal cannot contain NUL (0x00) characters. (Background on this error at: https://sqlalche.me/e/20/7s2a)
2024-05-12 13:26:41.772 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: A string literal cannot contain NUL (0x00) characters. (Background on this error at: https://sqlalche.me/e/20/7s2a)
2024-05-12 13:26:41.872 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: A string literal cannot contain NUL (0x00) characters. (Background on this error at: https://sqlalche.me/e/20/7s2a)
2024-05-12 13:26:41.873 ERROR (Recorder) [homeassistant.components.recorder.core] SQLAlchemyError error processing task <Event state_changed[L]: entity_id=light.front_yard_light_5, old_state=<state light.front_yard_light_5=unavailable; restored=True, min_color_temp_kelvin=3000, max_color_temp_kelvin=6500, min_mireds=153, max_mireds=333, effect_list=['Off', 'Meteor Shower', 'Gradual Change', 'Flash'], supported_color_modes=['color_temp', 'rgb'], friendly_name=Front Yard Light 5, supported_features=36 @ 2024-05-12T13:26:32.042103+02:00>, new_state=<state light.front_yard_light_5=off; min_color_temp_kelvin=3000, max_color_temp_kelvin=6500, min_mireds=153, max_mireds=333, effect_list=['Off', 'Meteor Shower', 'Gradual Change', 'Flash'], supported_color_modes=[<ColorMode.COLOR_TEMP: 'color_temp'>, <ColorMode.RGB: 'rgb'>], effect=None, color_mode=None, brightness=None, color_temp_kelvin=None, color_temp=None, hs_color=None, rgb_color=None, xy_color=None, friendly_name=Front Yard Light 5, supported_features=36 @ 2024-05-12T13:26:39.320088+02:00>>
Traceback (most recent call last):
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 914, in _process_one_task_or_event_or_recover
    self._process_one_event(task)
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 1032, in _process_one_event
    self._process_state_changed_event_into_session(event)
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/core.py", line 1153, in _process_state_changed_event_into_session
    attributes_id := state_attributes_manager.get(
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/table_managers/state_attributes.py", line 76, in get
    return self.get_many(((shared_attr, data_hash),), session)[shared_attr]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/table_managers/state_attributes.py", line 97, in get_many
    return results | self._load_from_hashes(missing_hashes, session)
                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/table_managers/state_attributes.py", line 110, in _load_from_hashes
    for attributes_id, shared_attrs in execute_stmt_lambda_element(
                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/homeassistant/components/recorder/util.py", line 233, in execute_stmt_lambda_element
    executed = session.connection().execute(stmt)
               ^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2034, in connection
    return self._connection_for_bind(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2050, in _connection_for_bind
    return trans._connection_for_bind(engine, execution_options)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in _connection_for_bind
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 103, in _go
    self._raise_for_prerequisite_state(fn.__name__, current_state)
  File "/srv/homeassistant/2024.5.2/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 928, in _raise_for_prerequisite_state
    raise sa_exc.PendingRollbackError(
sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: A string literal cannot contain NUL (0x00) characters. (Background on this error at: https://sqlalche.me/e/20/7s2a)

Additional information

No response

home-assistant[bot] commented 1 month 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)