[Bug]: Server crash with Unhandled rejection: SequelizeDatabaseError: SQLITE_CORRUPT: database disk image is malformed, promise: Promise

[ak-42]

What happened?

Upon submitting metadata changes for 'English Pronunciation Made Easy' (metadata pulled from Audible, ASIN B01CH0U3LG), a full server crash occurs, see attached logs. The book itself plays fine, and this does not occur with any other book.

What did you expect to happen?

I expected metadata to be saved as normal, and the server to not crash.

Steps to reproduce the issue

1. Pull metadata from Audible for the book 'English Pronunciation Made Easy' (ASIN B01CH0U3LG), while making no changes to it.
2. Click submit and observe the error/crash.

Audiobookshelf version

v2.12.3

How are you running audiobookshelf?

Docker

What OS is your Audiobookshelf server hosted from?

Linux

If the issue is being seen in the UI, what browsers are you seeing the problem on?

None

Logs

[2024-08-12 10:57:36.319] FATAL: [Server] Unhandled rejection: SequelizeDatabaseError: SQLITE_CORRUPT: database disk image is malformed, promise: Promise {
   <rejected> Error
       at Database.<anonymous> (/node_modules/sequelize/lib/dialects/sqlite/query.js:185:27)
       at /node_modules/sequelize/lib/dialects/sqlite/query.js:183:50
       at new Promise (<anonymous>)
       at Query.run (/node_modules/sequelize/lib/dialects/sqlite/query.js:183:12)
       at /node_modules/sequelize/lib/sequelize.js:315:28
       at async SQLiteQueryInterface.update (/node_modules/sequelize/lib/dialects/abstract/query-interface.js:355:12)
       at async Book.save (/node_modules/sequelize/lib/model.js:2490:35)
       at async Book.update (/node_modules/sequelize/lib/model.js:2598:12)
       at async libraryItem.fullUpdateFromOld (/server/models/LibraryItem.js:374:9)
       at async Database.updateLibraryItem (/server/Database.js:463:21) {
     name: 'SequelizeDatabaseError',
     parent: [Error: SQLITE_CORRUPT: database disk image is malformed] {
       errno: 11,
       code: 'SQLITE_CORRUPT',
       sql: 'UPDATE `books` SET `title`=$1,`titleIgnorePrefix`=$2,`subtitle`=$3,`publisher`=$4,`description`=$5,`asin`=$6,`language`=$7,`narrators`=$8,`tags`=$9,`genres`=$10,`updatedAt`=$11 WHERE `id` = $12'
     },
     original: [Error: SQLITE_CORRUPT: database disk image is malformed] {
readarr-audiobooks       | [Info] BookInfoProxy: BookInfo returned 429, backing off for 10s 
       errno: 11,
       code: 'SQLITE_CORRUPT',
       sql: 'UPDATE `books` SET `title`=$1,`titleIgnorePrefix`=$2,`subtitle`=$3,`publisher`=$4,`description`=$5,`asin`=$6,`language`=$7,`narrators`=$8,`tags`=$9,`genres`=$10,`updatedAt`=$11 WHERE `id` = $12'
     },
     sql: 'UPDATE `books` SET `title`=$1,`titleIgnorePrefix`=$2,`subtitle`=$3,`publisher`=$4,`description`=$5,`asin`=$6,`language`=$7,`narrators`=$8,`tags`=$9,`genres`=$10,`updatedAt`=$11 WHERE `id` = $12',
     parameters: {}
   }
 } (Server.js:170)
audiobookshelf exited with code 0

Additional Notes

No response

[advplyr]

Can you double check that you are on v2.12.3? This should be fixed on the latest version without needing to restore a backup db.

[ak-42]

The bottom left in the interface says v2.12.3 docker. I am using the latest tag, and I appear to be up-to-date.

[advplyr]

Most likely the database is fixable from what I've seen with the other sqlite corrupt cases but I would need a copy of the db unless you are familiar with sqlite enough to run a query. A few columns need to be re-indexed.

Otherwise you can restore from a backup before v2.12.0

[advplyr]

If you want to fix the sqlite file yourself this is the thread #3199 (comment)

[ak-42]

Are there any downstream effects of not addressing this? It's only one book so far, could there be more in the future, or have the bug fixes addressed it?

I should have a database backup prior to v2.12.0, but I won't be able to try to roll back until the weekend as I don't want to impact the users.

[advplyr]

I think you'll have to address it if it didn't fix itself after the update. You are the first user that reported it didn't resolve itself.

You can try soft deleting the book and running another scan. If that soft delete crashes the server you'll have to use a backup or run a REINDEX query on the db.

[ak-42]

I think I fixed it based on your instructions.

First I deleted the book that was causing the issue from the ABS interface, but that crashed the server, and wouldn't bring it back up, the container just failed to start with database errors very similar to the initial message.

So, I took the absdatabase.sqlite file, opened it with sqlite3 absdatabase.sqlite and executed REINDEX NOCASE; command, followed by .quit. Then placed the reindexed absdatabase.sqlite back into its original location.
Upon starting ABS container, everything seems to work, including pulling data for the offending book.

Thank you for your help, and for your work on ABS.

[hardwareadictos]

This is reproduced on v2.15.0:

AudioBookShelf-PRO  | [2024-10-13 18:10:26.841] ERROR: [MigrationManager] Migration failed: MigrationError: Migration v2.15.0-series-column-unique.js (up) failed: Original error: SQLITE_CORRUPT: database disk image is malformed
AudioBookShelf-PRO  |     at /server/libs/umzug/umzug.js:150:17
AudioBookShelf-PRO  |     at async Umzug.runCommand (/server/libs/umzug/umzug.js:112:12)
AudioBookShelf-PRO  |     at async MigrationManager.runMigrations (/server/managers/MigrationManager.js:102:9)
AudioBookShelf-PRO  |     at async Database.init (/server/Database.js:184:7)
AudioBookShelf-PRO  |     at async Server.init (/server/Server.js:128:5)
AudioBookShelf-PRO  |     at async Server.start (/server/Server.js:191:5) {
AudioBookShelf-PRO  |   migration: {
AudioBookShelf-PRO  |     direction: 'up',
AudioBookShelf-PRO  |     name: 'v2.15.0-series-column-unique.js',
AudioBookShelf-PRO  |     path: '/config/migrations/v2.15.0-series-column-unique.js',
AudioBookShelf-PRO  |     context: { queryInterface: [SQLiteQueryInterface], logger: [Logger] }
AudioBookShelf-PRO  |   },
AudioBookShelf-PRO  |   [cause]: Error
AudioBookShelf-PRO  |       at Database.<anonymous> (/node_modules/sequelize/lib/dialects/sqlite/query.js:185:27)
AudioBookShelf-PRO  |       at /node_modules/sequelize/lib/dialects/sqlite/query.js:183:50
AudioBookShelf-PRO  |       at new Promise (<anonymous>)
AudioBookShelf-PRO  |       at Query.run (/node_modules/sequelize/lib/dialects/sqlite/query.js:183:12)
AudioBookShelf-PRO  |       at /node_modules/sequelize/lib/sequelize.js:315:28
AudioBookShelf-PRO  |       at async Object.up (/server/migrations/v2.15.0-series-column-unique.js:160:29)
AudioBookShelf-PRO  |       at async /server/libs/umzug/umzug.js:148:11
AudioBookShelf-PRO  |       at async Umzug.runCommand (/server/libs/umzug/umzug.js:112:12)
AudioBookShelf-PRO  |       at async MigrationManager.runMigrations (/server/managers/MigrationManager.js:102:9)
AudioBookShelf-PRO  |       at async Database.init (/server/Database.js:184:7) {
AudioBookShelf-PRO  |     name: 'SequelizeDatabaseError',
AudioBookShelf-PRO  |     parent: [Error: SQLITE_CORRUPT: database disk image is malformed] {
AudioBookShelf-PRO  |       errno: 11,
AudioBookShelf-PRO  |       code: 'SQLITE_CORRUPT',
AudioBookShelf-PRO  |       sql: 'DELETE FROM Series\n' +
AudioBookShelf-PRO  |         "          WHERE name = 'Círculo del Crimen' AND libraryId = '69c6c533-51d3-4d30-8b93-926ca636fd63'\n" +
AudioBookShelf-PRO  |         "          AND id != 'dec190a2-9758-4b32-8da0-84a78a53ce84'"
AudioBookShelf-PRO  |     },
AudioBookShelf-PRO  |     original: [Error: SQLITE_CORRUPT: database disk image is malformed] {
AudioBookShelf-PRO  |       errno: 11,
AudioBookShelf-PRO  |       code: 'SQLITE_CORRUPT',
AudioBookShelf-PRO  |       sql: 'DELETE FROM Series\n' +
AudioBookShelf-PRO  |         "          WHERE name = 'Círculo del Crimen' AND libraryId = '69c6c533-51d3-4d30-8b93-926ca636fd63'\n" +
AudioBookShelf-PRO  |         "          AND id != 'dec190a2-9758-4b32-8da0-84a78a53ce84'"
AudioBookShelf-PRO  |     },
AudioBookShelf-PRO  |     sql: 'DELETE FROM Series\n' +
AudioBookShelf-PRO  |       "          WHERE name = 'Círculo del Crimen' AND libraryId = '69c6c533-51d3-4d30-8b93-926ca636fd63'\n" +
AudioBookShelf-PRO  |       "          AND id != 'dec190a2-9758-4b32-8da0-84a78a53ce84'",
AudioBookShelf-PRO  |     parameters: {}
AudioBookShelf-PRO  |   }
AudioBookShelf-PRO  | }
````

[hardwareadictos]

Hey! How can I fix this? V2.15.0 may have a fix to test for the issue mentioned upper ☝️☝️☝️

[advplyr]

I tested with your database @hardwareadictos and was able to repro the issue.

I'm not sure how we will roll out a fix yet for everyone incase anyone else has this issue but the fix is very simple if you can run the following sql command on the db:

REINDEX NOCASE

I just ran this on your test db and the issue is resolved on startup.

[github-actions]

Fixed in v2.15.1.

[hardwareadictos]

I can confirm! No more corruption errors on ABS!