Recorder exceptions with 0.116.2 and EC 9.0.1

[mikeage]

Description

Recorder reports errors in 0.116.2 (in 0.116.0, it actually broke the recorder for the rest of the session; 0.116.2 introduces a recovery). It looks like the limit is 36 characters (https://www.home-assistant.io/docs/backend/database/) and mine was 37

Configuration

entity_controller:
  downstairs_bathroom:
    sensor: binary_sensor.0x00158d0003ccfd12_occupancy
    entity: light.yeelight_ct2_04cf8c887220
    sensor_type: duration
    delay: 90
    block_timeout: 900
    overrides:
      - binary_sensor.shabbat_mode
    state_attributes_ignore:
      - brightness
      - color_temp

Steps to reproduce

Run

Expected behavior

No errors

Actual Behaviour

2020-10-10 22:19:36 ERROR (Recorder) [homeassistant.components.recorder] Error saving events: (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'context_id' at row 1")                           [SQL: INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id, context_parent_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)]
[parameters: ('state_changed', '{}', 'LOCAL', datetime.datetime(2020, 10, 10, 19, 17, 14, 924586, tzinfo=<UTC>), datetime.datetime(2020, 10, 10, 19, 19, 36, 165175, tzinfo=<UTC>), 'entity_controller.downstairs_bathroom', None, 'entity_controller')]
(Background on this error at: http://sqlalche.me/e/13/9h9h)
Traceback (most recent call last):
  File "/Users/mikeage/.homeassistant/deps/lib/python/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/Users/mikeage/.homeassistant/deps/lib/python/site-packages/sqlalchemy/engine/default.py", line 593, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.DataError: (1406, "Data too long for column 'context_id' at row 1")

Logs

Above

Version

9.0.1

[danobot]

What's the best way to shorten the context id? Remove domain name and trim off excess chars?

[mikeage]

probably. Maybe use something like ec.XXXXX and just print a warning if it's over 33 characters? I haven't really looked into it enough to see what's common

[danobot]

Check #203 .
Is the column length limit of 33 characters universal to all databases? If this depends the DB backend how will Home Assistant itself handle this?

[mikeage]

Oh, I meant 33 characters for the part after the ec., so it's 36 in total.

I don't know about the DB; the linked page doesn't say anything about the schema being different for different backends. Mine (MariaDB) is 36; I confirmed that with a describe.