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
73.4k stars 30.65k forks source link

Recorder DB Error Message: Data too long for column 'context_id' at row 1 #72862

Closed Crayjin closed 2 years ago

Crayjin commented 2 years ago

The problem

Hi there, since I've updated my Home Assistant Docker container the logs contain repeating entries like the one below. Seems like these entries are connected with multiple entities (e.g. could spot sensors and scenes).

2022-06-01 20:49:51 ERROR (Recorder) [homeassistant.components.recorder.core] Unhandled database error while processing task CommitTask(): (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'context_id' at row 1")

See full traceback under "Anything in the logs that might be useful for us?"

I'm using MySQL 10.6.5 as Docker container.

What version of Home Assistant Core has the issue?

core-2022.6.0

What was the last working version of Home Assistant Core?

core-2022.5.5

What type of installation are you running?

Home Assistant Container

Integration causing the issue

Recorder (I guess)

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?

Logger: homeassistant.components.recorder.core
Source: components/recorder/core.py:864
Integration: Recorder (documentation, issues)
First occurred: 18:49:25 (451 occurrences)
Last logged: 22:32:55

Unhandled database error while processing task CommitTask(): (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'context_id' at row 1") [SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_updated, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor.elv_weather_station_wind_dir', '353', None, None, None, datetime.datetime(2022, 6, 1, 20, 30, 45, 473359, tzinfo=datetime.timezone.utc), None, 56, '01G4GFDAQ1EASGY1YDKAJZ6RR9', None, None, 0)] (Background on this error at: https://sqlalche.me/e/14/9h9h)
Unhandled database error while processing task CommitTask(): (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'context_id' at row 1") [SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_updated, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor.elv_weather_station_in_temp', '21.5', None, None, None, datetime.datetime(2022, 6, 1, 20, 31, 15, 469209, tzinfo=datetime.timezone.utc), None, 6, '01G4GFE80D9RFKCKM9BSKDXXTN', None, None, 0)] (Background on this error at: https://sqlalche.me/e/14/9h9h)
Unhandled database error while processing task CommitTask(): (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'context_id' at row 1") [SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_updated, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sun.sun', 'below_horizon', None, None, datetime.datetime(2022, 6, 1, 19, 47, 39, 594577, tzinfo=datetime.timezone.utc), datetime.datetime(2022, 6, 1, 20, 31, 39, 607854, tzinfo=datetime.timezone.utc), None, 3, '01G4GFEZJQ8TKR4F3ZAYXZMJT9', None, None, 0)] (Background on this error at: https://sqlalche.me/e/14/9h9h)
Unhandled database error while processing task CommitTask(): (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'context_id' at row 1") [SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_updated, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor.elv_weather_station_in_temp', '21.6', None, None, None, datetime.datetime(2022, 6, 1, 20, 32, 15, 473287, tzinfo=datetime.timezone.utc), None, 6, '01G4GFG2KHVMZ7SRN0F1ZQ8G0R', None, None, 0)] (Background on this error at: https://sqlalche.me/e/14/9h9h)
Unhandled database error while processing task CommitTask(): (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'context_id' at row 1") [SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_updated, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)] [parameters: ('sensor.elv_weather_station_wind_dir', '0', None, None, None, datetime.datetime(2022, 6, 1, 20, 32, 45, 478954, tzinfo=datetime.timezone.utc), None, 56, '01G4GFGZX60ZVE3MKB5HKZ7Z83', None, None, 0)] (Background on this error at: https://sqlalche.me/e/14/9h9h)
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.DataError: (1406, "Data too long for column 'context_id' at row 1")

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 599, in _process_one_task_or_recover
    return task.run(self)
  File "/usr/src/homeassistant/homeassistant/components/recorder/tasks.py", line 235, in run
    instance._commit_event_session_or_retry()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 845, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/core.py", line 864, in _commit_event_session
    self.event_session.commit()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1451, in commit
    self._transaction.commit(_to_root=self.future)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 829, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3383, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3523, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 3483, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 453, in execute
    n.execute_aggregate(self, set_)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/unitofwork.py", line 747, in execute_aggregate
    persistence.save_obj(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/persistence.py", line 1238, in _emit_insert_statements
    result = connection._execute_20(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.DataError: (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'context_id' at row 1")
[SQL: INSERT INTO states (entity_id, state, attributes, event_id, last_changed, last_updated, old_state_id, attributes_id, context_id, context_user_id, context_parent_id, origin_idx) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: ('scene.weihnachtsbeleuchtung', 'unknown', None, None, None, datetime.datetime(2022, 6, 1, 16, 47, 10, 503811, tzinfo=datetime.timezone.utc), None, 2, '01G4G2KY57SVXCYBM5V1TH3DTT', None, None, 0)]
(Background on this error at: https://sqlalche.me/e/14/9h9h)

Additional information

No response

probot-home-assistant[bot] commented 2 years 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! (message by CodeOwnersMention)

recorder documentation recorder source (message by IssueLinks)

bdraco commented 2 years ago

Looks like something is wrong with your schema.

Can you provide a dump of the schema for the states table?

Crayjin commented 2 years ago

Hi @bdraco, I hope I got the data you requested. I'm not very experienced with DBs.

states.zip

bdraco commented 2 years ago 
  `context_id` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `context_user_id` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

That's odd that its set to 1. Any idea how that happened?

Make a backup first, then you can fix it with:

alter table states change context_id context_id varchar(36); alter table states change context_user_id context_user_id varchar(36);

Crayjin commented 2 years ago

I have no clue how this could happen, but changing it from 1 to 36 worked and the log entries have disappeared.

Thank you very much!

sashgithub commented 2 years ago

Hi guys,

i had the same issue:

I changed by states the following in the database: alter table states change context_id context_id varchar(36); alter table states change context_user_id context_user_id varchar(36);

Now, everything is fine.