Komga: Switch from H2 to SQLite

[gotson]

The H2 file database used in Komga tends to get corrupt without clear root cause. Many users experienced it, but there's no clear solution to address this.

Switching to another database system could help mitigate that, but testing would be required to assess conformance and performance.

---

Embedded database candidates:

• Apache Derby
• HSQLDB
• SQLite

---

Given the discussions below and the fact that SQLite is almost indestructible, SQLite will be the target.

[whalehub]

I'm in favor of SQLite for two reasons. The first reason is that the tooling and documentation for it is ubiquitous. The second reason is that it has proven itself to be a robust and reliable embedded database for applications with significantly larger amounts of metadata than Komga (e.g. Plex, Emby and Jellyfin).

[gotson]

I am also interested in looking first at SQLite, mostly for it's high reliability. It seems the issue with H2 is the hard reboots and unexpected shutdowns, which are something we cannot control for self-hosted user installed environments.

[grim7reaper]

It seems the issue with H2 is the hard reboots and unexpected shutdowns

Yeah, SQLite handles those pretty well (it's part of its humongous test suite).

[gotson]

It seems the issue with H2 is the hard reboots and unexpected shutdowns

Yeah, SQLite handles those pretty well (it's part of its humongous test suite).

That's... Impressive!

[yurigiu]

Today it happened again to me, and the database backup still comes up as corrupted.

I'd like to suggest to keep server configuration on one database, and the comics collection data on a different database. In this way, in case of corruption, the only thing needed is to rescan the comics collection, without the need to re-configure the whole thing.

[gotson]

Today it happened again to me, and the database backup still comes up as corrupted.

I'd like to suggest to keep server configuration on one database, and the comics collection data on a different database. In this way, in case of corruption, the only thing needed is to rescan the comics collection, without the need to re-configure the whole thing.

What would server configuration contain?

[yurigiu]

What would server configuration contain?

• User accounts (email, password)
• Permissions
• Libraries settings (locations, scan options)
• other?

[gotson]

That's not possible, having separate database would prevent making joins in SQL. User is required to get series with read progress for instance. Libraries are required when refreshing a book's metadata to decide whether it should be imported.

Plus there would be no guarantee that the other database wouldn't get corrupt too.

[yurigiu]

Fair enough. What about backing up those tables in a separate db backup? A backup of the whole corrupted db is useless.

Of course a better solution would be the issue title, having a reliable db engine. But until we get there...

[gotson]

I'll work on it starting tomorrow, target is to use sqlite.

On Sun, Jul 5, 2020, 17:26 yurigiu [e-mail] wrote:

Fair enough. What about backing up those tables in a separate db backup? A
backup of the whole corrupted db is useless.

Of course a better solution would be the issue title, having a reliable db
engine. But until we get there...

—
You are receiving this because you modified the open/close state.
Reply to this email directly, view it on GitHub
https://github.com/gotson/komga/issues/218#issuecomment-653863840, or
unsubscribe
https://github.com/notifications/unsubscribe-auth/AAQKH7PKVMF6P44XDKKCRCDR2BBNFANCNFSM4OIJTNOA
.

[gotson]

:tada: This issue has been resolved in version 0.46.0-beta.1 :tada:

The release is available on:

• v0.46.0-beta.1
• GitHub release

Your semantic-release bot :package::rocket:

[gotson]

:tada: This issue has been resolved in version 0.48.0 :tada:

The release is available on:

• v0.48.0
• GitHub release

Your semantic-release bot :package::rocket: