[Bug Report] thumbnail generation FOREIGN KEY sqlite exception

[Nerothos]

Describe the bug

Starting thumbnail generation
Unhandled exception: FOREIGN KEY constraint failed (SQLite3::Exception)
  from Mango/lib/sqlite3/src/sqlite3/connection.cr:89:11 in 'close'
  from Mango/lib/db/src/db/pool.cr:52:21 in 'close'
  from Mango/lib/db/src/db.cr:126:7 in '->'
  from usr/share/crystal/src/primitives.cr:255:3 in 'start_and_block'
  from Mango/src/mango.cr:47:1 in '->'
  from usr/share/crystal/src/primitives.cr:255:3 in 'start_parse'
  from Mango/src/mango.cr:137:1 in '__crystal_main'
  from usr/share/crystal/src/crystal/main.cr:110:5 in 'main'
  from src/env/__libc_start_main.c:94:2 in 'libc_start_main_stage2'

To Reproduce
Steps to reproduce the behavior:

1. Thumbnail generation, either auto or via admin

Expected behavior
No crash when mango generate thumbnail

Environment :

• OS: Debian via WSL
• Mango Version : 0.24.0

Additional context
The funny thing is that I have three mango running and it seems only this one got that bug on thumbnail generation. Except the manga themselves, the three have pretty much the same setup (just on different port and folders).

Recreating the container didn't change a thing, I'm not sure if the problem is with the database being corrupted (I tried deleting it and recreating the containers and having a new db but the bug still persist) or some images having a problem.

[hkalexling]

This is weird. Did you somehow modified your DB file directly? We have ON UPDATE CASCADE and ON DELETE CASCADE on every foreign constraint in the DB, which should prevent something like this.

[Nerothos]

Not that I know of. I'm not sure when that appeared exactly but probably when I upgraded to last version. Like I said, that's the only instance that do that, even when deleting the database and recreating the container (so the new db is created and not touched).

[hkalexling]

I see. Is it possible for you to share your DB file so I can inspect and try to pinpoint the issue? You can do

1. Backup your existing DB file
2. Let Mango create a new DB file
3. Without logging in or creating new users, let Mango do a library scan to update the file
4. Send me the newly created DB file

The highlighted part in step 3 is important so I won't have access to the users table.

Feel free to DM me on gitter/discord if you prefer to share it privately.

[hkalexling]

@Nerothos I accidentally reproduced the issue when testing an unrelated PR #259. It can be reproduced as follows:

1. Run Mango as normal, and have it generate the library cache (library.yml.gz).
2. Delete the DB file (mango.db).
3. Rerun Mango. It creates a new DB file, but as soon as thumbnail generation starts it would crash with the foreign key error.

One quick workaround I found is to simply delete the library cache and have Mango do a full scan from scratch. Of course this is not ideal, but I will look into this.

---

@Leeingnyo I think this is caused by the inconsistency between the DB and the library cache. When a new DB file is created it doesn't contain the titles and entries in the cache file, and so it fails when generating the thumbnails. I think an easy fix is to compare the entries in DB with the entries in the cache file when we first load it, and if they are different, we delete the cache file and do a full scan. Do you think this would work? Or do you have a better solution?

[Nerothos]

Ah yeah sorry I forgot to send you my files and to report.

Yeah I fixed this by creating an entire new mango folder but keeping only my library. I thought about the library.yml.gz but that didn't do it.

Glad you found a way to reproduce it.

[mango-assistant]

Hi there! The issue has been fixed in v0.25.0. Thanks for the bug report!