search

B5r1oJ0A9G / teufel_raumfeld

Integration for Teufel smart speaker (aka Raumfeld Multiroom) into https://www.home-assistant.io/.
GNU General Public License v3.0
30 stars 6 forks source link

Data too long for column 'event_type' #5

Closed mplogas closed 3 years ago

mplogas commented 3 years ago

Hi, thanks for adding this integration for Teufel. I installed the integration yesterday and was greeted by a bunch of errors in the log today

System: Hass.io on a NUC

Recorder: MariaDb AddOn 2.2.2 HACS 1.11.3 teufel_raumfeld component version: 696ac9b Teufel Systems:

Error:

sqlalchemy.exc.DataError: (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'event_type' 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: ('teufel_raumfeld.webservice_update', '{"type": "system_state"}', 'LOCAL', datetime.datetime(2021, 3, 12, 9, 50, 31, 439025, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 3, 12, 9, 50, 31, 439025, tzinfo=datetime.timezone.utc), '5e12278b6b5af7c2acd3b8d8d83187e4', None, None)]
(Background on this error at: http://sqlalche.me/e/13/9h9h)
2021-03-12 10:55:47 ERROR (Recorder) [homeassistant.components.recorder] Unexpected error saving events: (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'event_type' 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: ('teufel_raumfeld.webservice_update', '{"type": "host_info"}', 'LOCAL', datetime.datetime(2021, 3, 12, 9, 55, 47, 383320, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 3, 12, 9, 55, 47, 383320, tzinfo=datetime.timezone.utc), '4cad8da787364a91960d48e2b60a21b2', None, None)]
(Background on this error at: http://sqlalche.me/e/13/9h9h)
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, 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 'event_type' 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/__init__.py", line 496, in _commit_event_session_or_recover
    self._commit_event_session_or_retry()
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 517, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 547, in _commit_event_session
    self.event_session.commit()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1046, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 504, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 483, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2540, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2682, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2642, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1135, in _emit_insert_statements
    result = cached_connections[connection].execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, 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)
sqlalchemy.exc.DataError: (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'event_type' 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: ('teufel_raumfeld.webservice_update', '{"type": "host_info"}', 'LOCAL', datetime.datetime(2021, 3, 12, 9, 55, 47, 383320, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 3, 12, 9, 55, 47, 383320, tzinfo=datetime.timezone.utc), '4cad8da787364a91960d48e2b60a21b2', None, None)]
(Background on this error at: http://sqlalche.me/e/13/9h9h)
B5r1oJ0A9G commented 3 years ago

Thanks for sharing this issue!

As the error message already indicates, the value teufel_raumfeld.webservice_update with 33 characters exceeds the current limit of 32 characters of the recorder component: https://github.com/home-assistant/core/blob/597bf67f5a8483f1e032e43678103cb33bec561f/homeassistant/components/recorder/models.py#L52

There is no issue when using SQLite as it does not impose any length restrictions. See: Affinity Name Examples, Datatypes In SQLite Version 3.

Now I'm wondering if there is a specification that mandates the 32 character limit for a reason, or if it's just a value that was considered sufficient at a certain point in time.

Edit: There is an open pull request to set the limit to 64 characters: https://github.com/home-assistant/core/pull/47748

Instead of making changes to teufel_raumfeld I'd suggest to wait for the implementation of this PR. One could also consider to alter the table manually. But I cannot tell whether this will finally fix the issue or could even break something. I neither have any experience with the recorder component nor do I have with the MariaDB Add-on. It would therefore be at your own risk.