Recorder limits event type to 32 chars, core does not, causes crash in recorder

[dlashua]

Home Assistant release with the issue:
0.76.0

Last working Home Assistant release (if known):
??

Operating environment (Hass.io/Docker/Windows/etc.):
Ubuntu venv

Component/platform:
recorder / mysql

Description of problem:
Home Assistant allows events to fire with event_type longer than VARCHAR(32). However, the MySQL table structure created by Home Assistant doesn't support it.

Once the error (see below) is thrown, recorder stops updating records entirely until Home Assistant is restarted.

1. Code limits need to be put in place to limit the length of an event_type. Or, MySQL queries should shorten that field before inserting. Additionally, recorder component should catch the error and allow future updates to continue, instead of dying until the next restart.

Problem-relevant configuration.yaml entries and (fill out even if it seems unimportant):

recorder:
  purge_interval: 1
  purge_keep_days: 42
  exclude:
    domains:
      - media_player
      - camera
  db_url: !secret mysql_db_url

Traceback (if applicable):

Aug 19 13:05:11 maverick hass[20390]: 2018-08-19 13:05:11 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (_mysql_exceptions.DataError) (1406, "Data too l
ong for column 'event_type' at row 1") [SQL: 'INSERT INTO events (event_type, event_data, origin, time_fired, created, context_id, context_user_id) VALUES (%s, %s, %s, %s, %s, %s, %s)'] [
parameters: ('device_tracker.life360_update_overdue', '{"entity_id": "device_tracker.celeste_xxxxxx_xxxxxx"}', 'LOCAL', datetime.datetime(2018, 8, 19, 18, 5, 10, 991876, tzinfo=<UTC>), da
tetime.datetime(2018, 8, 19, 18, 5, 11, 1865), '3440540872d84e4a82894fa4b3170e10', None)] (Background on this error at: http://sqlalche.me/e/9h9h)

Additional information:
I've since upgrades to 0.76.1. The problem may be corrected in that version. I'm not sure because I altered the MySQL table to be a VARCHAR(64) in order to stop this error from happening.

[sagilo]

Still happens. version 0.77.3

[balloobbot]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.

Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍

[ziptbm]

I'm seeing this as well in 0.84.6.

Is this the preferred workaround changing the column size? Or will something be changing on the HA side?

[sagilo]

I'm seeing this as well in 0.84.6.

Is this the preferred workaround changing the column size? Or will something be changing on the HA side?
I've created a fix as you can see but it will only fix future configurations.
You'll have to alter the column type manually to fix this on your current configuration.

[ziptbm]

Thank you. Searching for the specifics of how and what to modify. Any quick tips on how I should make this change?

…

-Talon

________________________________ From: sagilo <[email protected]> Sent: Monday, December 31, 2018 9:44:18 AM To: home-assistant/home-assistant Cc: ziptbm; Comment Subject: Re: [home-assistant/home-assistant] Recorder limits event type to 32 chars, core does not, causes crash in recorder (#16074) I'm seeing this as well in 0.84.6. Is this the preferred workaround changing the column size? Or will something be changing on the HA side? I've created a fix as you can see but it will only fix future configurations. You'll have to alter the column type manually to fix this on your current configuration. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fhome-assistant%2Fhome-assistant%2Fissues%2F16074%23issuecomment-450660079&data=02%7C01%7C%7C9372bbfd44aa48cfbf2808d66f36d390%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636818678592559517&sdata=2%2FHIiJabEsC5f%2Fy6q7tcHeK1MbVnNOpWP3qeoKWlpeM%3D&reserved=0>, or mute the thread<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAr_881PN4YbO6EsuDQQSsG9OdB5AZOwDks5u-jDSgaJpZM4WD4uH&data=02%7C01%7C%7C9372bbfd44aa48cfbf2808d66f36d390%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C636818678592715769&sdata=kZXp1YiPghIJjfsOBiUrpG8VIVGwGZ8yYx3C4yr6vlE%3D&reserved=0>.

[sagilo]

@ziptbm, I've updated the PR to have manual fix instructions for existing configurations
#19670

[stale]

There hasn't been any activity on this issue recently. Due to the high number of incoming GitHub notifications, we have to clean some of the old issues, as many of them have already been resolved with the latest updates.
Please make sure to update to the latest Home Assistant version and check if that solves the issue. Let us know if that works for you by adding a comment 👍
This issue now has been marked as stale and will be closed if no further activity occurs. Thank you for your contributions.

[basnijholt]

This still seems like it's an issue, see #15984 (comment).