DB error that breaks riven

[binGhzal]

Issue Description

i keep getting this error. and everytime i need to reset the db to make riven work again.

Docker Image Type

riven:latest 20.0 with riven-frontend:latest 17.0

OS Version and Last Update

Ubuntu 24.0 LT

Error Logs and Timestamps

| ❌ ERROR     | event_manager._process_future - Traceback (most recent call last):

  File "/riven/src/program/managers/event_manager.py", line 74, in _process_future

    result = future.result()

             ^^^^^^^^^^^^^^^

  File "/usr/local/lib/python3.11/concurrent/futures/_base.py", line 449, in result

    return self.__get_result()

           ^^^^^^^^^^^^^^^^^^^

  File "/usr/local/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result

    raise self._exception

  File "/usr/local/lib/python3.11/concurrent/futures/thread.py", line 58, in run

    result = self.fn(*self.args, **self.kwargs)

             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/riven/src/program/db/db_functions.py", line 349, in run_thread_with_db_item

    program.em.add_item(item, service)

  File "/riven/src/program/managers/event_manager.py", line 312, in add_item

    if not db_functions.get_item_by_external_id(imdb_id=item.imdb_id):

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/riven/src/program/db/db_functions.py", line 72, in get_item_by_external_id

    item = _session.execute(query).unique().scalar_one_or_none()

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/app/.venv/lib/python3.11/site-packages/sqlalchemy/engine/result.py", line 1487, in scalar_one_or_none

    return self._only_one_row(

           ^^^^^^^^^^^^^^^^^^^

  File "/app/.venv/lib/python3.11/site-packages/sqlalchemy/engine/result.py", line 805, in _only_one_row

    raise exc.MultipleResultsFound(

sqlalchemy.exc.MultipleResultsFound: Multiple rows were found when one or none was required

[egreenmachine]

I am seeing this when I add multiple Plex RSS watchlists to riven. If I use only a single Plex watchlist then I am not seeing this happen.

[arao]

temporary solution is to get into psql and clear duplicate episode entry from MediaItem table. In my case, duplicate was on imdb_id .

[skeet70]

Thanks for the tip @arao

riven=# select imdb_id, count(*) from "MediaItem" group by imdb_id HAVING count(*)>1;
  imdb_id  | count
-----------+-------
           |   567
 tt0988824 |     4
(2 rows)
riven=# select id, title from "MediaItem" where imdb_id = 'tt0988824';
       id       |          title
----------------+-------------------------
 show_31770     | Naruto Shippuden
 episode_767858 | The Results of Training
 episode_768117 | War Begins
 episode_768191 | The Fallen Castle
(4 rows)
riven=# delete from "MediaItem" where id IN ('episode_767858', 'episode_768117', 'episode_768191');
ERROR:  update or delete on table "MediaItem" violates foreign key constraint "Episode_id_fkey" on table "Episode"
DETAIL:  Key (id)=(episode_767858) is still referenced from table "Episode".
riven=# delete from "Episode" where id IN ('episode_767858', 'episode_768117', 'episode_768191');
DELETE 3
riven=# delete from "MediaItem" where id IN ('episode_767858', 'episode_768117', 'episode_768191');
DELETE 3

It seems like that (combined with an shm_size change) may have fixed the memory leak issues I was having with riven that were leading it to get killed every day or so. On this restart the memory was creeping back up, but after deleting the duplicates it dropped back down to a normal level.