Bulk modifying chapter read status caused all progress for user to be unread

[scare376]

What happened?

I had a series that I marked as unread and it caused all of the read progress of my user to be unread. I was able to narrow down the issue to when i used the bulk select and mark read/unread on a specific chapter in that series.

I dug into the database and found that the chapter in question had 2 read entries for my user.

I deleted these rows from the DB and the series is now working as intended.

What did you expect?

Changing the read progress of one series shouldn't affect the state of unrelated series.

Kavita Version Number - If you don not see your version number listed, please update Kavita and see if your issue still persists.

0.8.4.2 - Stable

What operating system is Kavita being hosted from?

Docker (Dockerhub Container)

If the issue is being seen on Desktop, what OS are you running where you see the issue?

Windows

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

Microsoft Edge

If the issue is being seen on Mobile, what OS are you running where you see the issue?

None

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

No response

Relevant log output

No response

Additional Notes

I have backups that can be provided as needed

[scare376]

screenshot of our convo on discord

[Fesaa]

Have had the same thing happen, while on the nightly branch.

Rough timeline

• Added a few new chapters to unrelated series
• Nightly scans happens
• Random series has chapters half-read (while being fully read)

If I would then mark the entire series as read (SeriesId = 627), I would lose all progress. Restoring from backup, would always yield the same results. Nor would scanning the series make a difference. Marking the chapter that was half-read as read would do nothing.

Duplicated entries in database;

I had around 16 in total, across 4/5 series.

I then deleted all the entries with duplicates. (Don't use this, if you don't understand what it does)

DELETE
FROM
    AppUserProgresses
WHERE
    (AppUserId, ChapterId) IN(
    SELECT
        AppUserId,
        ChapterId
    FROM
        AppUserProgresses
    GROUP BY
        AppUserId,
        ChapterId
    HAVING
        COUNT(*) > 1
);

This was rather aggressive as it would delete both all entries for each with duplicates. But it was late, and I couldn't be bothered.

After starting Kavita back up after this, everything was resolved (with some unread chapters, but that's expected)

I was unable to get the (/any) series back into this broken state afterwards.

If needed, Horo has my database before, after, and logs on Debug during the transition.

[merlinmarijn]

I encountered the same issue and created some SQL queries to address it.

To check for duplicate entries, you can use the following query:

SELECT *
FROM AppUserProgresses
WHERE (AppUserId, ChapterId) IN (
    SELECT
        AppUserId,
        ChapterId
    FROM
        AppUserProgresses
    GROUP BY
        AppUserId,
        ChapterId
    HAVING
        COUNT(*) > 1
);

If duplicates are found, you can remove them using this query:

WITH DuplicateRows AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY AppUserId, ChapterId ORDER BY PagesRead DESC, Id) AS RowNum
    FROM
        AppUserProgresses
)
DELETE FROM AppUserProgresses
WHERE Id IN (
    SELECT Id
    FROM DuplicateRows
    WHERE RowNum > 1
);

This query retains the entry with the highest PagesRead value. For instance, if there are two entries with PagesRead values of 15 and 3, it will keep the 15 and delete the 3.

Important: As @Fesaa mentioned, please proceed with caution. Ensure you fully understand the queries before executing them. It is strongly recommended to back up your data beforehand to avoid any unintended data loss.