SQL database stops updating

[RedRain77]

What happened
Spent ~5 days making ~50k metadata edits with ComicRackCE open. App stalled; after closing and reopening, all edits from the last few days were gone in the UI. Older edits are still there. No new books were added.

What I expected
Edits to persist (committed to DB) so they survive a restart.

How I verified

• DB: doujin.comics row count unchanged.
• Searched for a unique tag I added during the session ("In DB") inside the JSON data field → 0 matches.
• So the recent edits never reached the DB.

Environment
ComicRackCE (Most recent version), Windows 10, MariaDB 10.4.27 (binlogs off).

Ask
Is CE buffering edits client-side / deferring commits? What triggers a guaranteed flush/commit, and is there a setting to force immediate persistence so this can’t happen again?

[maforget]

is there a setting to force immediate persistence so this can’t happen again?

Yes by not using SQL.

Not trying to be an ass, but SQL support was something that was added late in the life of ComicRack so is very basic. There is some serious work required. It's basically just an XML being dumped in a database. There are no individual columns for each data type or an object-relational mapper. All work is basically done by a 3rd party library. There are maybe 4-5 SQL queries in total.

I had a lengthy conversation with someone on Reddit in the past about a similar situation. So instead of trying to remember details I do not fully remember I would suggest you check the post and maybe something will help you find at least a way to reproduce.

From what I can tell you the database should be saving every second pretty much constantly. Whenever some value is changed or the library is modified in any way. But it also all depends on an update_counter. A counter is saved in the changes table and that tells the db what to update. It may be possible that both table values become desynchronized and not think an update is needed. Again see the above post for details.

From what I can tell briefly all work is done in a update database background thread. Maybe it's possible that there was an error with the connection that made the thread die and not update anymore. Maybe aborting user tasks might have stopped the update db thread? Just an idea to investigate.

With databases and the multitude of way things can go wrong, and the basic contact ComicRack has with the database itself, a lot can go wrong between the 2. So it's difficult to fix with having a way to reproduce the problem.

It's why I've added a way to export your SQL database to XML as a backup and a way to switch to XML if required. Normally the ComicDb.xml file you have when you are using SQL doesn't contains the books themselves, only the lists. If you backup the database in preferences, the books are now included. So that file can be used to restore the database by using it normally with a connection string active, or using it independently by not having one. The ComicDb.xml is normally saved on exit, and every 10 minutes.

[RedRain77]

Hey, thanks for the reply, and for keeping ComicRack alive. I really appreciate the time and care you’ve put into it over the years (including the plugins before this undertaking).

The main reason I’ve stuck with the SQL backend is performance. When my library crossed around 100,000 entries, the XML route got pretty sluggish on my end. Switching to the database made things feel noticeably smoother. I’m well past 200,000 books now, so I’ve kept using SQL for that headroom.

Thanks again for the behind the scenes and for continuing to support the project.

[maforget]

I would say it's the other way around. The database is all loaded into memory. Doesn't matter if it's SQL or XML. The SQL doesn't add faster access. Just adds overhead. Why would the location of the db is saved change the speed when it's all in memory anyway.

Maybe the size of the file and it's a matter of disk speed. But even then it's saved every 10 minutes.

Sluggishness may be helped by upping memory and cache for images. Pre-generating thumbnails can help.