Unable to delete library or repair database on mac

Plex Media Server Desktops & Laptops
1

Server Version#: 1.13.8.5395
Player Version#: web client 3.67.1

Hi,

I’m having difficulty repairing my database. I am unable to delete a library from my database and until i rolled back my database using Time Machine (osx) i was unable to mark a show as watched, but since rolling it back that part is now fixed.

when i run the command to delete the library i get the following response in my logs:

Oct 05, 2018 19:52:19.833 [0x70000bf47000] DEBUG - Activity: registered new activity 49f42cf8-76bf-4a5e-bf0e-3723161867fa - Deleting Library

Oct 05, 2018 19:52:19.834 [0x70000c2dc000] DEBUG - Completed: [127.0.0.1:52897] 200 DELETE /library/sections/1?async=1 (9 live) GZIP 14ms 371 bytes (pipelined: 6)

Oct 05, 2018 19:52:19.835 [0x70000c465000] DEBUG - Activity: updated activity 49f42cf8-76bf-4a5e-bf0e-3723161867fa - completed 0% - Deleting Library

Oct 05, 2018 19:52:20.824 [0x70000c465000] ERROR - SQLITE3:0x70000c0cf590, 11, database corruption at line 59437 of [fc49f556e4]

Oct 05, 2018 19:52:20.824 [0x70000c465000] ERROR - SQLITE3:0x70000c0cf590, 11, database corruption at line 59479 of [fc49f556e4]

Oct 05, 2018 19:52:20.824 [0x70000c465000] ERROR - SQLITE3:0x70000c0cf590, 11, statement aborts at 35: [delete from taggings where id in (select taggings.id from taggings join metadata_items on metadata_items.id=taggings.metadata_item_id where metadata_items.library_section_id=?)] databa

Oct 05, 2018 19:52:20.824 [0x70000c465000] ERROR - Exception inside transaction (inside=1) (…/Library/Scanner/LibrarySection.cpp:314): sqlite3_statement_backend::loadOne: database disk image is malformed

Oct 05, 2018 19:52:20.939 [0x70000c465000] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x70000bf47000: sqlite3_statement_backend::loadOne: database disk image is malformed

I have tried to run the script to repair the database from here

I’ve made a number attempts at repairing it, and often errors. Whilst preparing this post i closed my plex server down and ran the script again and this time no errors were observed: (response below from Terminal)

wearefamily:~ davidlaker$ cd ~/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases/

wearefamily:Databases davidlaker$ cp com.plexapp.plugins.library.db com.plexapp.plugins.library.db.original

wearefamily:Databases davidlaker$ sqlite3 com.plexapp.plugins.library.db “DROP index ‘index_title_sort_naturalsort’”

wearefamily:Databases davidlaker$ sqlite3 com.plexapp.plugins.library.db “DELETE from schema_migrations where version=‘20180501000000’”
wearefamily:Databases davidlaker$ sqlite3 com.plexapp.plugins.library.db .dump > dump.sql
wearefamily:Databases davidlaker$ rm com.plexapp.plugins.library.db
wearefamily:Databases davidlaker$ sqlite3 com.plexapp.plugins.library.db < dump.sql

But on loading plex it crashes:

Oct 05, 2018 20:10:09.013 [0x700006f69000] INFO - Plex Media Server v1.13.8.5395-10d48da0d - Apple Mac - build: darwin-x86_64 macosx - GMT 01:00
Oct 05, 2018 20:10:09.014 [0x700006f69000] INFO - MacOSX version: 10.13.6, language: en-GB
Oct 05, 2018 20:10:09.014 [0x700006f69000] INFO - Processor Intel® Core™ i5-2415M CPU @ 2.30GHz
Oct 05, 2018 20:10:09.014 [0x700006f69000] INFO - /Applications/Plex Media Server.app/Contents/MacOS/Plex Media Server
Oct 05, 2018 20:10:09.260 [0x70000727b000] DEBUG - BPQ: [Idle] -> [Starting]
Oct 05, 2018 20:10:09.268 [0x70000727b000] DEBUG - FeatureManager: Using cached data for features list
Oct 05, 2018 20:10:09.269 [0x70000727b000] DEBUG - Opening 20 database sessions to library (com.plexapp.plugins.library), SQLite 3.13.0, threadsafe=1
Oct 05, 2018 20:10:09.283 [0x70000727b000] DEBUG - Running migrations. (EPG 0)
Oct 05, 2018 20:10:09.284 [0x70000727b000] ERROR - SQLITE3:0x70000727a590, 1, no such table: schema_migrations
Oct 05, 2018 20:10:09.284 [0x70000727b000] ERROR - Database corruption: sqlite3_statement_backend::prepare: no such table: schema_migrations for SQL: select version from schema_migrations order by version
Oct 05, 2018 20:10:09.284 [0x70000727b000] ERROR - Error: Unable to set up server: sqlite3_statement_backend::prepare: no such table: schema_migrations for SQL: select version from schema_migrations order by version (N4soci10soci_errorE)

Can anyone assist please, much appreciated.

2

Does this mean that you were previously trying to repair the database while Plex server was still running?
That may have been the crucial mistake.
Shut down Plex Server.

Then delete your current version of the main database file com.plexapp.plugins.library.db
If there are still files named

com.plexapp.plugins.library.db-shm
com.plexapp.plugins.library.db-wal

in the folder, delete them as well.

Rename the newest of the backup copies, which are named like
com.plexapp.plugins.library.db-2018-09-25
to com.plexapp.plugins.library.db

Perform the database repair procedure again, because even this backup copy might already be damaged.

3

That’s not good. That means you removed a table that PMS needs. The an older backup like @OttoKerner said. Otherwise you’ll need to do some advanced SQL to get it working again.

4

i’ll keep playing about with it. I don’t think I’ve ever tried the repair whilst the server was running but if it was then I’ll still be able to restore to a point well before i noticed i couldn’t delete a certain library. I only noticed that problem after buying a new external drive and then trying to edit the location of the files within the plex page. To get around that i ended up renaming the library and creating a new one.

some time after that it stopped marking shows as watched.

Like i said i have restored the db to a point that i can again mark shows as watched, but still cannot delete a library.

i only get the error “no such table: schema_migrations” following running the repair commands.

I have restored to a point previous to this to get plex back up and the error i get trying to delete this particular library is:

Oct 05, 2018 20:53:22.559 [0x70000c739000] ERROR - SQLITE3:0x70000c426590, 11, database corruption at line 59437 of [fc49f556e4]
Oct 05, 2018 20:53:22.559 [0x70000c739000] ERROR - SQLITE3:0x70000c426590, 11, database corruption at line 59479 of [fc49f556e4]
Oct 05, 2018 20:53:22.560 [0x70000c739000] ERROR - SQLITE3:0x70000c426590, 11, statement aborts at 35: [delete from taggings where id in (select taggings.id from taggings join metadata_items on metadata_items.id=taggings.metadata_item_id where metadata_items.library_section_id=?)] databa
Oct 05, 2018 20:53:22.560 [0x70000c739000] ERROR - Exception inside transaction (inside=1) (…/Library/Scanner/LibrarySection.cpp:314): sqlite3_statement_backend::loadOne: database disk image is malformed
Oct 05, 2018 20:53:22.561 [0x70000c739000] ERROR - Thread: Uncaught exception running async task which was spawned by thread 0x70000cbd4000: sqlite3_statement_backend::loadOne: database disk image is malformed

I can delete other library though, just not this one.

5

I deleted the library and renamed the backup, that solved all problems but it was very old Jan-2018.

i guess its a case of restoring my db using time machine to a point everything worked but if that file is anything to go by that could be the only fully working db prior to my problems starting.

6

So Ive been through every month and all the backups of the database folder are of a corrupt database with the only fully functional database being prior to 19.jan.2018.

Not sure what happened that day lol, I’ve restored to that point and amended the library to current folder structure. I’ll run with that for now, marking stuff i know I’ve watched, in the mean time ill probably keep trying to repair the most recent database that allowed me to mark shows as watched but not delete a library.

Thanks for your help so far.

7

I cannot recommend you to do that. This database is already damaged and it will break totally at some point again.

8

Ok.

I was going to try repairing the dB on a separate machine whilst the main pc runs on the January db that works.

Tbh not much point anyway as it won’t take long to mark my watches stuff.

Cheers

9

If you run out of idea, you can PM me your file and I can try to fix the corruption, the actual corruption and/or the schema one.

10

Thanks for the offer :slightly_smiling_face:. Over the weekend I’ll try and look at the sql dump with my limited experience of fixing my websites database in phpmyadmin.

I can get away with what I have but I’m also curious as to what needs fixing with it.

11

A database corruption is often caused by bad data. The most common I’ve seen is gibberish inserted into a date field. If you have something that can visually view the database, sort through the date fields in the tables that start with “media” or “metadata”. If you see a funky date, delete it.

For the schema error you are seeing, check if that table exists in your database. Sounds like somehow it got removed or it is possibly corrupted. If you rename/move your database away from it’s location and restart PMS, it will create a new database for you. Find the schema migration table in this database and import it into the one you are using. Remove this new one and put the one you just modified back.

Edit - Ignore that part. I’m getting your issue mixed up with someone else.

Edit 2 - Ignore my edit. Ugh. You do have a schema table issue.

12

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.