Oops! Database Error: UNIQUE constraint failed. Library is not corrupt

[kanjieater]

Describe the bug/problem

Books uploaded to Calibre-web fails. Book added to classic calibre succeeds. Ran Library Maintenance, fix and deleted, found no issues, issue persists. Oops! Database Error: UNIQUE constraint failed: publishers.name.

Publisher in question: 株式会社 東京創元社

To Reproduce

Steps to reproduce the behavior:

1. Drag and drop [庵野 ゆき] 竜の医師団１ (創元推理文庫).epub into library
2. Error generated and fails to add book Not that other books add fine. Logfile

--- Logging error ---

Traceback (most recent call last):
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: publishers.name

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/app/calibre-web/cps/editbooks.py", line 257, in upload
    db_book, input_authors, title_dir, renamed_authors = create_book_on_upload(modify_date, meta)
  File "/app/calibre-web/cps/editbooks.py", line 712, in create_book_on_upload
    calibre_db.session.flush()
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3449, in flush
    self._flush(objects)
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3588, in _flush
    with util.safe_reraise():
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3549, in _flush
    flush_context.execute()
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
    rec.execute(self)
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 237, in save_obj
    _emit_update_statements(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 1001, in _emit_update_statements
    c = connection._execute_20(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
    ret = self._execute_context(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
    self._handle_dbapi_exception(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
    util.raise_(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "/lsiopy/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: publishers.name
[SQL: UPDATE publishers SET name=? WHERE publishers.id = ?]
[parameters: ('株式会社 東京創元社', 235)]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

Expected behavior

Books that can be added to Calibre should be able to be added to Calibre-Web without having to open Calibre

Environment (please complete the following information):

• OS: Windows
• Python version: 3.10.10
• Calibre-Web version: [e.g. 0.6.8 or 087c4c59 (git rev-parse --short HEAD)]:
• Docker container: [None/LinuxServer]: LinuxServer
• Special Hardware: Windows PC
• Browser: Chrome

Additional context Add any other context about the problem here. [e.g. access via reverse proxy, database background sync, special database location]

[OzzieIsaacs]

There is a publish with the same name, but different in the database. I don't know how to give an example form JKC letters but for normal ascii characters it would be something like "Unknown" and "unknown" (Capital letter or not)

[kanjieater]

There is a publish with the same name, but different in the database. I don't know how to give an example form JKC letters but for normal ascii characters it would be something like "Unknown" and "unknown" (Capital letter or not)

How do you avoid this? Only add books via calibre-web? Reset my library and let calibre-web handle all imports?

[xmgz]

@kanjieater in my particular case, with you same error (publishers.name key value) I had to manually edit the books in my library with that particular publisher name that caused the error.

go to "book list" (last item in the left dashboar menu); order by "editor" and look for the publisher name that brings your error; manually edit (click on its name) deleting its value in every book that has this publisher. In my case there were only three.

then try to add a new book and it worked for me

then manually edit again those previous books adding back its publisher name.

I know, this is not how it should work, but at least I could add new books to library

[aserrallerios]

I'm having the same issue with certain book:

Oops! Database Error: UNIQUE constraint failed: tags.name.

I only add books from the calibre-web UI.

I run the calibre-web in docker in TrueNAS, and somehow the logs of the application are not shown, so the only alternative I got was to copy the metadata.db to a local calibre and test again.

The database loaded successfully and I ran a validation of the database that showed no errors. Then I tried to add the book and it was added with no problems. Now I can see the tags of the book:

Historia, Religion

So basically the suggestion is to update the DB locally and upload it to calibre-web every time I get this type of error?

Edit:

I found the logs in the container and I think there's a bug somewhere in the code while trying to reuse an existing tag, probably related to char encoding.

[2024-06-14 15:50:51,430] ERROR {cps.editbooks:305} Database error: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(sqlite3.IntegrityError) UNIQUE constraint failed: tags.name
[SQL: UPDATE tags SET name=? WHERE tags.id = ?]
[parameters: ('Religion', 839)]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

[OzzieIsaacs]

I‘ll have a look at it

[kaibagley]

I am also having this issue, but for authors.name. I had a book called The Cyberiad by Stanislaw Lem, and was trying to upload Solaris from the same author.

I saw that The Cyberiad was under the author Stanisław Lem (with a ł), and the book I was uploading mustn't have had the ł character, because when I renamed the author in The Cyberiad to be Stanislaw, I was then able to upload Solaris.

Hope this helps!

[OzzieIsaacs]

@kaibagley: I can reproduce it

[OzzieIsaacs]

It's a bit tricky, I need some tests, the solution will show up within next week

[NicholasYZ]

For some additional information, the error message I encountered was UNIQUE constraint failed: authors.name.

The uploaded epub files' author name 理不盡な孫の手 was conflicted with the author name 理不尽な孙の手 of books already inside the database.The difference inbetween is Japanese kanji/traditional Chinese and simplified Chinese characters (its actually a Japanese name, but different translators chose different approaches when dealing with the author name).

I suspect that some kind of pluggin identify these two as the same key but with different characters, causing the unique contraint in database to fail. Therefore I changed the author name into 理不尽な孙の手 using calibre ebook editior, specifically speaking, the <dc:creator opf:role="aut"> </dc:creator> line in content.opf page, and it worked just fine. The book after editing could be uploaded and identified just like any other books.

Hope this would help, and thanks again for all your efforts put in this project.