Core: transmission/recorder issue, lots of errors

[jurgenweber]

The problem

Lots of errors on startup from transmission/recorder. This started after 108 --> 109 upgrade. Not sure if there are any outward signs, the sensors on my transmission card still seem to be functioning. It does stop after a while. Do note I have over 500 torrents.

Environment

• Home Assistant Core release with the issue:
  109.3
• Last working Home Assistant Core release (if known):
  108.8
• Operating environment (Home Assistant/Supervised/Docker/venv):
  docker
• Integration causing this issue:
  not sure if it is a transmission or recorder platform issue
• Link to integration documentation on our website:

Problem-relevant configuration.yaml

#https://home-assistant.io/components/recorder/
recorder:
  db_url: !secret recorder_db_url
  purge_keep_days: 15

transmission:
  host: !secret server_ip
  port: 9091
  username: !secret transmission_rpc_user
  password: !secret transmission_rpc_pass
  scan_interval: 300

Traceback/Error logs

2020-05-05 10:22:53 ERROR (Recorder) [homeassistant.components.recorder] Error saving events: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (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) VALUES (%s, %s, %s, %s, %s, %s, %s)]
[parameters: ('state_changed', '{"entity_id": "sensor.transmission_active_torrents", "old_state": null, "new_state": {"entity_id": "sensor.transmission_active_torrents", "state": "5 ... (72781 characters truncated) ... :00", "last_updated": "2020-05-05T00:21:21.305836+00:00", "context": {"id": "1f6017b567284ecfafef33548d651925", "parent_id": null, "user_id": null}}}', 'LOCAL', datetime.datetime(2020, 5, 5, 0, 21, 21, 305920, tzinfo=<UTC>), datetime.datetime(2020, 5, 5, 0, 22, 23, 542347), '1f6017b567284ecfafef33548d651925', None)]
(Background on this error at: http://sqlalche.me/e/9h9h) (Background on this error at: http://sqlalche.me/e/7s2a)
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 419, in _commit_event_session_or_retry
    self._commit_event_session()
  File "/usr/src/homeassistant/homeassistant/components/recorder/__init__.py", line 470, in _commit_event_session
    self.event_session.commit()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1036, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 501, in commit
    self._assert_active(prepared_ok=True)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 295, in _assert_active
    code="7s2a",
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (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) VALUES (%s, %s, %s, %s, %s, %s, %s)]
[parameters: ('state_changed', '{"entity_id": "sensor.transmission_active_torrents", "old_state": null, "new_state": {"entity_id": "sensor.transmission_active_torrents", "state": "5 ... (72781 characters truncated) ... :00", "last_updated": "2020-05-05T00:21:21.305836+00:00", "context": {"id": "1f6017b567284ecfafef33548d651925", "parent_id": null, "user_id": null}}}', 'LOCAL', datetime.datetime(2020, 5, 5, 0, 21, 21, 305920, tzinfo=<UTC>), datetime.datetime(2020, 5, 5, 0, 22, 23, 542347), '1f6017b567284ecfafef33548d651925', None)]
(Background on this error at: http://sqlalche.me/e/9h9h) (Background on this error at: http://sqlalche.me/e/7s2a)

Additional information

[bdraco]

72k+ in the state state data. Thats a new one

[probot-home-assistant[bot]]

Hey there @engrbm87, @JPHutchins, mind taking a look at this issue as its been labeled with a integration (transmission) you are listed as a codeowner for? Thanks!
_{^{(message by CodeOwnersMention)}}

[JPHutchins]

This would be related to #34223 and #27111. Good news: Home Assistant now knows all of your active torrents! Bad news: we did not account for massive lists going into the database!

@zhulik In my initial PR I had thought about using a queue of length 10 that shows only the recently active torrents. Since we were looking at downloads only we decided to give it a shot without a limit because we felt it was unlikely to be a problem. Now we see that with seeding perhaps we should implement a queue - perhaps the "current most active 10" (open to definition) for seeded.

My preference is still to maintain this functionality as I would ultimately like HA to manage the entire torrent process (at least for casual users!)

P.S.: @engrbm87 you predicted this problem, good call!

[zhulik]

I can dive deeper into into this today afternoon. I only need to clarify a couple of things:

• we're going with limiting the queue side, right? Then we need at least 2 configuration options.
• The queue limit config option e.g. torrents_limit, with default value of 10, 0 means - no limit
• The other one e.g. sorting_mode will have values like age, age_desc, id, id_desc, ratio, ratio_desc

@JPHutchins @engrbm87 WDYT? Any ideas on what else sorting options I should add?

[JPHutchins]

@zhulik Yes, default to 10 - we already know that allowing unlimited will result in problems with the SQL - consider disallowing "unlimited" - I'd just let someone type in "500" and let them decide whether they really need 500 jinja templated torrent entries in their HA.
I love the ideas for sorting mode but I think you should decide for the user! My best guess is by age so that recent entries are reflected. For seeding it might make sense to display by activity.

[jurgenweber]

yeah, I feel 10 is plenty, hey.

[zhulik]

@JPHutchins sounds reasonable, I'll disable the "unlimited" mode. But I won't agree with recent first sorting, because for my personal case I need oldest first, that's why I'd go with a config option

[jurgenweber]

@zhulik following this conversation (and I realise a little bit off topic), what are you doing? Do you have a blog or post on what you are doing with this? That goes for anyone on this thread. Just interested to see what automations you are coming up with.

[JPHutchins]

@zhulik Will have better ideas than me. Right now you can just get a templated markdown card: https://www.home-assistant.io/integrations/transmission/#templating

tldr maybe I'll make torrent frontend within the next five years
I have been working more in JS lately and want to make a frontend component that simply manages torrents (for normal users): add magnet link, see status, start/stop - very basic. Technically much of this is already working via automations and templating but it is a sea of jinja - a sea of jinja one can drown in. The templating documentation for HA was written by a true optimist!

[zhulik]

@zhulik following this conversation (and I realise a little bit off topic), what are you doing? Do you have a blog or post on what you are doing with this? That goes for anyone on this thread. Just interested to see what automations you are coming up with.

I simply delete torrents older than n days and depending on other conditions

[bagobones]

While I don't generally care about the names of the torrents seeding in HA I do like to know what is incomplete / downloading and use it to watch for STUCK torrents.

This should be configurable and probably have a hard limit preset to avoid the database errors.

Going to say that 72781 characters truncated is probably on the LOW side for anyone that is using transmission for long term seeding to host files. Torrent names in general can also be VERY VERY long so how this is stored by the integration should probably be reconsidered in general.