8000 Recorder error - Data too long for column 'event_data' at row 1 · Issue #46970 · home-assistant/core · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

Recorder error - Data too long for column 'event_data' at row 1 #46970

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
SnowViet opened this issue Feb 23, 2021 · 4 comments · Fixed by #47026
Closed

Recorder error - Data too long for column 'event_data' at row 1 #46970

SnowViet opened this issue Feb 23, 2021 · 4 comments · Fixed by #47026
Labels
integration: recorder

Comments

@SnowViet
Copy link

The problem

The recorder seems to work, but I have few of these errors at startup.
The server is a MariaDB server running under another docker container.

What is version of Home Assistant Core has the issue?

2021.2.3

What was the last working version of Home Assistant Core?

2021.1

What type of installation are you running?

Home Assistant OS

Integration causing the issue

recorder

Link to integration documentation on our website

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

Example YAML snippet

# Put your YAML below this line
purge_keep_days: 45
db_url: !secret sql_server
include:
  entities:
    - media_player.my_media_player
exclude:
  domains:
    - calendar
    - media_player
    - group
    - script
    - timer
    - automation
    - zone
    - switch
  entities:
    - weather.dark_sky
    [ Long list ]

Anything in the logs that might be useful for us?

# Put your logs below this line
2021-02-23 22:10:28 ERROR (Recorder) [homeassistant.components.recorder] Error executing query: (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'event_data' 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: ('platform_discovered', '{"service": "load_platform.binary_sensor", "platform": "proxmoxve", "discovered": {"config": {"homeassistant": {"name": "Home", "latitude": 00.000000 ... (119685 characters truncated) ... equence": [{"data": {"payload": "{\\"selftest\\": \\"\\"}", "topic": "zigbee2mqtt/smoke_detector/set"}, "service": "mqtt.publish"}]}}, "scene": {}}}}', 'LOCAL', datetime.datetime(2021, 2, 23, 21, 10, 1, 715703, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 2, 23, 21, 10, 1, 715703, tzinfo=datetime.timezone.utc), 'bf2c4586db7300bde4a9ab67efee636d', None, None)]
(Background on this error at: http://sqlalche.me/e/13/9h9h)
2021-02-23 22:10:28 ERROR (Recorder) [homeassistant.components.recorder] Error saving events: (MySQLdb._exceptions.DataError) (1406, "Data too long for column 'event_data' 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: ('platform_discovered', '{"service": "load_platform.binary_sensor", "platform": "proxmoxve", "discovered": {"config": {"homeassistant": {"name": "Home", "latitude": 00.000000 ... (119685 characters truncated) ... equence": [{"data": {"payload": "{\\"selftest\\": \\"\\"}", "topic": "zigbee2mqtt/smoke_detector/set"}, "service": "mqtt.publish"}]}}, "scene": {}}}}', 'LOCAL', datetime.datetime(2021, 2, 23, 21, 10, 1, 715703, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 2, 23, 21, 10, 1, 715703, tzinfo=datetime.timezone.utc), 'bf2c4586db7300bde4a9ab67efee636d', 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 609, 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_data' 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 454, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 509, in _commit_event_session
    self.event_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 419, in execute
    n.execute_aggregate(self, set_)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 521, in execute_aggregate
    self.execute(uow)
  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 609, 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_data' 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: ('platform_discovered', '{"service": "load_platform.binary_sensor", "platform": "proxmoxve", "discovered": {"config": {"homeassistant": {"name": "Home", "latitude": 00.000000 ... (119685 characters truncated) ... equence": [{"data": {"payload": "{\\"selftest\\": \\"\\"}", "topic": "zigbee2mqtt/smoke_detector/set"}, "service": "mqtt.publish"}]}}, "scene": {}}}}', 'LOCAL', datetime.datetime(2021, 2, 23, 21, 10, 1, 715703, tzinfo=datetime.timezone.utc), datetime.datetime(2021, 2, 23, 21, 10, 1, 715703, tzinfo=datetime.timezone.utc), 'bf2c4586db7300bde4a9ab67efee636d', None, None)]
(Background on this error at: http://sqlalche.me/e/13/9h9h)
@probot-home-assistant
Copy link

recorder documentation
recorder source
(message by IssueLinks)

@solidminds
Copy link

Same problem here. Looks like Hass is trying to put a string inside the database which is too large for the width of the cell.
"platform_discovered" also here. Checked the table setup and the field is TEXT with maxlength 65535. Thats a load of data for one cell.
Cant find anything on excluding this type of domain te exclude in the recorder.

@solidminds
Copy link

Check this post for temp solution.

@SnowViet
Copy link
Author

Thanks for the info, it worked !
I hope it will be "correctly" fixed soon.

@ghost ghost mentioned this issue Feb 24, 2021
Merged
21 tasks
@github-actions github-actions bot locked and limited conversation to collaborators Apr 9, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
integration: recorder
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Use LONGTEXT column instead of TEXT for MySQL/MariaDB and migrate existing databases
2 participants
@SnowViet @solidminds
0