sqlalchemy.orm.exc.MultipleResultsFound while POST /posts

[gaudat]

[2023-01-05 13:39:43] waitress Exception while serving /posts
Traceback (most recent call last):                                                                                        File "/usr/lib/python3.8/site-packages/waitress/channel.py", line 350, in service
    task.service()                                                                                                        File "/usr/lib/python3.8/site-packages/waitress/task.py", line 171, in service
    self.execute()                                                                                                        File "/usr/lib/python3.8/site-packages/waitress/task.py", line 441, in execute
    app_iter = self.channel.server.application(environ, start_response)
  File "/opt/app/szurubooru/rest/app.py", line 104, in application
    response = handler(ctx, match.groupdict())
  File "/opt/app/szurubooru/api/post_api.py", line 79, in create_post
    post, new_tags = posts.create_post(
  File "/opt/app/szurubooru/func/posts.py", line 421, in create_post
    update_post_content(post, content)
  File "/opt/app/szurubooru/func/posts.py", line 632, in update_post_content
    db.session.query(model.Post)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3467, in one_or_none
    raise orm_exc.MultipleResultsFound(
sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one_or_none()

I was hammering my szuru instance by uploading from multiple clients. This somehow made the database inconsistent.

[gaudat]

szuru=# select count(col) from (select distinct checksum from post) col;
 count
--------
 288087
(1 row)

szuru=# select count(col) from (select checksum from post) col;
 count
--------
 288226
(1 row)

szuru=#

Looks like multiple posts with identical checksums get uploaded.

[gaudat]

Suggested solution: Add unique checking of checksum to sql side. Changing from normal index to unique index on the column should work. https://docs.sqlalchemy.org/en/13/core/metadata.html#sqlalchemy.schema.Column.params.index

[LibertX]

Hi

I got the same issue with reverse-search:

[2023-05-09 09:58:53] waitress Exception while serving /posts/reverse-search
Traceback (most recent call last):
  File "/usr/lib/python3.8/site-packages/waitress/channel.py", line 350, in service
    task.service()
  File "/usr/lib/python3.8/site-packages/waitress/task.py", line 171, in service
    self.execute()
  File "/usr/lib/python3.8/site-packages/waitress/task.py", line 441, in execute
    app_iter = self.channel.server.application(environ, start_response)
  File "/opt/app/szurubooru/rest/app.py", line 104, in application
    response = handler(ctx, match.groupdict())
  File "/opt/app/szurubooru/api/post_api.py", line 301, in get_posts_by_image
    ctx, posts.search_by_image_exact(content)
  File "/opt/app/szurubooru/func/posts.py", line 924, in search_by_image_exact
    db.session.query(model.Post)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3467, in one_or_none
    raise orm_exc.MultipleResultsFound(
sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one_or_none()