[Bug]: FOREIGN KEY constraint failed - Resuming play after having deleted and re-added library

[mikelnorth]

What happened?

I deleted my library and re added it to make it pick up some series that it was not seeing after some directory restructuring.

In doing so, users playback sessions were still kept. Since the library was re-created the ID in the playbackSession no longer exists to match it the correct book causing a foreign key constraint failure that crashes the server

What did you expect to happen?

Acceptable experience would be clearing users playSession to avoid the error.

A nice experience would be attempting to re-map the playSession off of meta data if its a 100% match? Not knowing the system, I don't know if this would work and be 100% reliable. Worse case scenario if someone re-created a library with a different audio file, but the same meta data of name ASIN number, etc... then it would just show that you have a playback session on a book you shouldnt. if it can't find a match, just delete.

Steps to reproduce the issue

1. Start a playback session
2. Delete the library
3. Re-add the library without any changes
4. Go back to the same book, and begin a playback session
5. Get a playback error
6. Server crash

Audiobookshelf version

v2.17.7

How are you running audiobookshelf?

Windows Tray App

What OS is your Audiobookshelf server hosted from?

Windows

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

None

Logs

promise: Promise {
  <rejected> Error
      at Database.<anonymous> (C:\snapshot\audiobookshelf\node_modules\sequelize\lib\dialects\sqlite\query.js:185:27)
      at C:\snapshot\audiobookshelf\node_modules\sequelize\lib\dialects\sqlite\query.js:183:50
      at new Promise (<anonymous>)
      at Query.run (C:\snapshot\audiobookshelf\node_modules\sequelize\lib\dialects\sqlite\query.js:183:12)
      at C:\snapshot\audiobookshelf\node_modules\sequelize\lib\sequelize.js:315:28
      at async SQLiteQueryInterface.bulkUpdate (C:\snapshot\audiobookshelf\node_modules\sequelize\lib\dialects\abstract\query-interface.js:366:12)
      at async playbackSession.update (C:\snapshot\audiobookshelf\node_modules\sequelize\lib\model.js:2010:28)
      at async PlaybackSessionManager.closeSession (C:\snapshot\audiobookshelf\server\managers\PlaybackSessionManager.js)
      at async PlaybackSessionManager.startSession (C:\snapshot\audiobookshelf\server\managers\PlaybackSessionManager.js)
      at async PlaybackSessionManager.startSessionRequest (C:\snapshot\audiobookshelf\server\managers\PlaybackSessionManager.js) {
    name: 'SequelizeForeignKeyConstraintError',
    parent: [Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed] {
      errno: 19,
      code: 'SQLITE_CONSTRAINT',
      sql: 'UPDATE `playbackSessions` SET `id`=$1,`mediaItemId`=$2,`mediaItemType`=$3,`libraryId`=$4,`displayTitle`=$5,`displayAuthor`=$6,`duration`=$7,`playMethod`=$8,`mediaPlayer`=$9,`startTime`=$10,`currentTime`=$11,`serverVersion`=$12,`createdAt`=$13,`updatedAt`=$14,`userId`=$15,`deviceId`=$16,`timeListening`=$17,`coverPath`=$18,`mediaMetadata`=$19,`date`=$20,`dayOfWeek`=$21,`extraData`=$22 WHERE `id` = $23'
    }

Additional Notes

No response

[advplyr]

Are you leaving the audio player open?