Repairing a corrupt library - receiving error

[Timmehtimtims]

Server Version#: 1.20.2.3402
Player Version#: N/A

I am having issues with some 3rd party programmes accessing my database and believed that it could be due to corruption. It is a new database on a linux dedicated server.

I downloaded the database to my mac, and ran through the repair process (https://support.plex.tv/articles/201100678-repair-a-corrupt-database/) with the following results:

sqlite3 com.plexapp.plugins.library.db "PRAGMA integrity_check"
*** in database main ***
Main freelist: 27 of 8146 pages missing from overflow list starting at 834436
On tree page 66 cell 0: 2nd reference to page 244191
On tree page 244208 cell 21: Rowid 1747087 out of order
On tree page 244190 cell 1: Rowid 45569 out of order
On tree page 66 cell 11: 2nd reference to page 631526
On tree page 66 cell 9: 2nd reference to page 1015788
On tree page 66 cell 8: 2nd reference to page 1016450
On tree page 1015781 cell 0: Rowid 256619 out of order
On tree page 1015722 cell 3: Rowid 45573 out of order
On tree page 1015492 cell 111: 2nd reference to page 244208
On tree page 975351 cell 7: 2nd reference to page 244209
On tree page 1016348 cell 0: 2nd reference to page 1015781
row 24 missing from index sqlite_autoindex_fts4_tag_titles_segdir_1
wrong # of entries in index sqlite_autoindex_fts4_tag_titles_segdir_1
row 12 missing from index sqlite_autoindex_fts4_metadata_titles_segdir_1
row 13 missing from index sqlite_autoindex_fts4_metadata_titles_segdir_1
row 14 missing from index sqlite_autoindex_fts4_metadata_titles_segdir_1
row 15 missing from index sqlite_autoindex_fts4_metadata_titles_segdir_1
row 16 missing from index sqlite_autoindex_fts4_metadata_titles_segdir_1
Error: database disk image is malformed

I then ran the repair with the following result:

sqlite3 com.plexapp.plugins.library.db "DROP index 'index_title_sort_naturalsort'"
Error: no such index: index_title_sort_naturalsort

sqlite3 com.plexapp.plugins.library.db "DELETE from schema_migrations where version='20180501000000'"

sqlite3 com.plexapp.plugins.library.db .dump > dump.sql

rm com.plexapp.plugins.library.db

sqlite3 com.plexapp.plugins.library.db < dump.sql
Error: near line 3925279: UNIQUE constraint failed: fts4_metadata_titles_segdir.level, fts4_metadata_titles_segdir.idx
Error: near line 3925280: UNIQUE constraint failed: fts4_metadata_titles_segdir.level, fts4_metadata_titles_segdir.idx

There are clearly issues with the database however the repair process seems to have failed.
Where do I go from here? Any advice would be appreciated.

[anon18523487]

Oh wow. Your file is more than just damaged. Those are not typical errors.

The import is only complaining about the error with table “fts4_metadata_titles_segdir”. I’d suggest dropping that table as well before you export and see if that helps. You’ll need to recreate that table structure afterwards.

[Timmehtimtims]

I tried repairing the file again and it lost all the data and I ended up with a 0kb empty file.

I then tried again using a backup of the database file. This time it said that there were no errors, but I tried repairing anyway. I then uploaded that file back onto the dedi and the database wouldn’t work.

Plex ‘works’ with the corrupt database but there are significant issues.

1. Random TV shows stick to the beginning of the recently added list
2. Very long delays in some media loading
3. Is this likely to explain why running GitHub - adamgot/python-plexlibrary: Create and maintain dynamic Plex libraries based on recipes. python script can’t read the library? I have been battling with this issue for weeks and it would be nice to know that this corrupt database is the reason…

Any advice on where to go from here would be great. Rescanning the databases from scratch is the absolute last option…

[anon18523487]

@Timmehtimtims

rm com.plexapp.plugins.library.db

That command removed the old database file that’s why the first step in the guide is to make a copy of the database.

For your issues.

1 - It’s likely the added at date for that movie is sometime in the future. Recently added sorts by the newest date so these will always be in the front. Plex Dance the file and this should get fixed.

2 - This might be normal. Without more details it’s hard to say. One thing you can try, if you haven’t already, is to optimize the database.

3 - I have no idea what that is for so I can’t comment. Please note that the Plex database does change from time to time. If that script isn’t maintained, it’s possible it can stop reading the database file even without any data corruption.