starred/audiobookshelf
1
0
Fork 0
mirror of https://github.com/advplyr/audiobookshelf.git synced 2026-05-30 23:40:40 +02:00
Code Issues 1.0k Packages Projects Releases 10 Wiki Activity

[Bug]: [Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: playbackSessions.id] #1733

New Issue
Closed
opened 2026-04-24 23:56:14 +02:00 by adam · 3 comments
adam commented 2026-04-24 23:56:14 +02:00
Owner
Copy Link
Copy Source

Originally created by @jrglasgow on GitHub (Feb 14, 2024).

Describe the issue

Constantly cannot insert into playbackSessions as there is a duplicate ID

 parent: [Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: playbackSessions.id] {
    errno: 19,
    code: 'SQLITE_CONSTRAINT',
    sql: 'INSERT INTO `playbackSessions` (`id`,`mediaItemId`,`mediaItemType`,`displayTitle`,`displayAuthor`,`duration`,`playMethod`,`mediaPlayer`,`startTime`,`currentTime`,`serverVersion`,`coverPath`,`timeListening`,`mediaMetadata`,`date`,`dayOfWeek`,`extraData`,`createdAt`,`updatedAt`,`userId`,`deviceId`,`libraryId`) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22);'
  },

I found this other issues which also have this error listed in the logs.
#1971 & #2294

Why are we using an Insert when there are obviously duplicates in the database... In my experience if there is the possibility of a duplicate key you should use a REPLACE command.

Steps to reproduce the issue

  1. see steps for [Bug]: Inconsistency between numbers of titles displayed in Series view verses Collection view (#2294)

Audiobookshelf version

v2.7.2

How are you running audiobookshelf?

Docker

Originally created by @jrglasgow on GitHub (Feb 14, 2024). ### Describe the issue Constantly cannot insert into playbackSessions as there is a duplicate ID ``` parent: [Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: playbackSessions.id] { errno: 19, code: 'SQLITE_CONSTRAINT', sql: 'INSERT INTO `playbackSessions` (`id`,`mediaItemId`,`mediaItemType`,`displayTitle`,`displayAuthor`,`duration`,`playMethod`,`mediaPlayer`,`startTime`,`currentTime`,`serverVersion`,`coverPath`,`timeListening`,`mediaMetadata`,`date`,`dayOfWeek`,`extraData`,`createdAt`,`updatedAt`,`userId`,`deviceId`,`libraryId`) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22);' }, ``` I found this other issues which also have this error listed in the logs. #1971 & #2294 Why are we using an Insert when there are obviously duplicates in the database... In my experience if there is the possibility of a duplicate key you should use a [REPLACE command](https://www.sqlite.org/lang_replace.html). ### Steps to reproduce the issue 1. see steps for #2294 ### Audiobookshelf version v2.7.2 ### How are you running audiobookshelf? Docker
adam added the bug label 2026-04-24 23:56:14 +02:00
adam closed this issue 2026-04-24 23:56:15 +02:00
adam commented 2026-04-24 23:56:16 +02:00
Author
Owner
Copy Link
Copy Source

@jrglasgow commented on GitHub (Feb 14, 2024):

I suppose I could say that I am experiencing the issue in the Android App (0.9.72-beta)

@jrglasgow commented on GitHub (Feb 14, 2024): I suppose I could say that I am experiencing the issue in the Android App (0.9.72-beta)
adam commented 2026-04-24 23:56:17 +02:00
Author
Owner
Copy Link
Copy Source

@advplyr commented on GitHub (Feb 18, 2024):

The server is checking if there is a duplicate before inserting so I believe this is a race condition like the other reported issues were. A better system of locking can be built-in to handle this but we should probably find out why you're still hitting this race condition on the latest versions.
Is this issue reproducible for you consistently?
Are you storing the sqlite db on a network file system?

If you enable debug logs on the server and share those logs we should be able to confirm that this is the race condition. There are logs for "Inserting new session..." and "Updated session..."

@advplyr commented on GitHub (Feb 18, 2024): The server is checking if there is a duplicate before inserting so I believe this is a race condition like the other reported issues were. A better system of locking can be built-in to handle this but we should probably find out why you're still hitting this race condition on the latest versions. Is this issue reproducible for you consistently? Are you storing the sqlite db on a network file system? If you enable debug logs on the server and share those logs we should be able to confirm that this is the race condition. There are logs for "Inserting new session..." and "Updated session..."
adam commented 2026-04-24 23:56:18 +02:00
Author
Owner
Copy Link
Copy Source

@jrglasgow commented on GitHub (Feb 19, 2024):

Instead of checking for a duplicate as a different task, or an additional step it would be better to use a command that would check for the duplicate and update is a duplicate exists... In my years of developing with MySQL I constantly use the REPLACE command and it will check for a duplicate and update the row if the key is duplicated. The SQLite documentation for REPLACE says:

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. If a NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with the default value for that column, or if the column has no default value, then the ABORT algorithm is used. If a CHECK constraint or foreign key constraint violation occurs, the REPLACE conflict resolution algorithm works like ABORT.

When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, delete triggers fire if and only if recursive triggers are enabled.

The update hook is not invoked for rows that are deleted by the REPLACE conflict resolution strategy. Nor does REPLACE increment the change counter. The exceptional behaviors defined in this paragraph might change in a future release.
Alternatively you could use an UPSERT to update on a conflict.

And yes this is consistently reproducible when I have the database on a network file system and with the current state of my system I can barely log into Audiobookshelf at all, so turning on debug logs is impractical.

I moved the database file to run local to the machine (instead of on the NAS) and it is working now.

@jrglasgow commented on GitHub (Feb 19, 2024): Instead of checking for a duplicate as a different task, or an additional step it would be better to use a command that would check for the duplicate and update is a duplicate exists... In my years of developing with MySQL I constantly use the REPLACE command and it will check for a duplicate and update the row if the key is duplicated. The [SQLite documentation for REPLACE](https://www.sqlite.org/lang_conflict.html#:~:text=key%20constraint%20errors.-,replace,-When%20a%20UNIQUE) says: > When a [UNIQUE](https://www.sqlite.org/lang_createtable.html#uniqueconst) or [PRIMARY KEY](https://www.sqlite.org/lang_createtable.html#primkeyconst) constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. If a [NOT NULL](https://www.sqlite.org/lang_createtable.html#notnullconst) constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with the default value for that column, or if the column has no default value, then the ABORT algorithm is used. If a [CHECK constraint](https://www.sqlite.org/lang_createtable.html#ckconst) or [foreign key constraint](https://www.sqlite.org/foreignkeys.html) violation occurs, the REPLACE conflict resolution algorithm works like ABORT. > > When the REPLACE conflict resolution strategy deletes rows in order to satisfy a constraint, [delete triggers](https://www.sqlite.org/lang_createtrigger.html) fire if and only if [recursive triggers](https://www.sqlite.org/pragma.html#pragma_recursive_triggers) are enabled. > > The [update hook](https://www.sqlite.org/c3ref/update_hook.html) is not invoked for rows that are deleted by the REPLACE conflict resolution strategy. Nor does REPLACE increment the [change counter](https://www.sqlite.org/c3ref/changes.html). The exceptional behaviors defined in this paragraph might change in a future release. Alternatively you could use an [UPSERT](https://www.sqlite.org/lang_upsert.html) to update on a conflict. And yes this is consistently reproducible when I have the database on a network file system and with the current state of my system I can barely log into Audiobookshelf at all, so turning on debug logs is impractical. I moved the database file to run local to the machine (instead of on the NAS) and it is working now.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
authentication
backlog
bug
chapter editor
config-issue
ebooks
encoding/embedding
enhancement
help wanted
listening sessions & progress
planned
possible plugin
progress sync
pull-request
Mirrored from GitHub Pull Request
 sorting/filtering/searching
unable to reproduce
upload
users & permissions
waiting
No Label bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam (Adam Melkus)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/audiobookshelf#1733
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?