sqlalchemy error: A string literal cannot contain NUL (0x00) characters #117303
Comments
|
Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration ( Code owner commandsCode owners of
(message by CodeOwnersMention) recorder documentation |
bdraco
changed the title
|
The recorder cannot record null bytes. You'll need to locate the integration that is providing the entity or event |
|
Hi, Logger: homeassistant.components.recorder.core Error while processing event CommitTask() |
|
Same problem here. It is a new and not specific to a particular integration I think. I have a lot of those logs (80000 lines in one hour), for a large variety of entity. Here is one example: I have the same one for remote.shield (harmony integration), mqtt entities etc I think this error does not come from a particular integration. Seems very weird that several entities coming from several integration starts sending "NUL" characters all at once... @ldiegos Have you figured what is/was the problem for you ? Oh and I am using 2024.9.3 version of HA, running HAOS. |
|
Same here, started a few days ago. All my entities are recording one point here and there, most of the data is being lost and that error keeps popping up all the time |
|
Happy to hear that I'm not alone.
|
Noooope, I still having issues, but, the last one was on the 25ft(today is 29th), I treat it like an endemic issue and live with it. It seems that nobody wants to solve it or at least show us how to identify the origin. All my daily used sensors are working fine, so actually i forgot this bug lol :P Maybe in a future i will try to check other forgotten sensor and it smack me in the face again. My PostGreSQL is outside the HA and only stores 15 days, the influx database is also outside home assistant in other server and stores forever the data, and everything works fine. And as a said, the hundred sensors that i check everyday, are fine. |
|
Might be slightly different then, because this affects all my entities across every integration (mqtt, dyson, myenergi ..), I can't find a single entity recording properly in the past few days. I also wonder if the recording somehow comes into automation triggering ? I'm not sure but I have a feeling automations that depend on states being held for some time aren't firing |
sorry to hear that... Hope you find the cause... |
|
Thank you for the fix ! |
|
Thanks for the fix, any chance this could be included in a minor patch ? Presumably 2024.10 is still a few days away and this issue is affecting all of my entities, it's recording so rarely the energy dashboard doesn't even load any data anymore for example. Thanks |
There is no new minor patch coming before 2024.10 |
I was more asking could there be one, given how broken it is right now ? |
|
For anyone else running into this, I got around to testing it and downgrading to |
|
Nevermind, downgrading doesn't actually fix the issue even all the way to 2024.9.0. |
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
The text was updated successfully, but these errors were encountered: