DBRepair development

Dev Community Apps & Creations
931

@Tony_T

My DB was pristine.

Today , running 1.42.2, it shows corrupted.

This is a PMS issue. Hacking the DB won’t resolve the problem.

I’ve chatted with FordGuy who will take to Plex.

Notice the specific indexes which now show failed on 1.42.2

Checking the PMS databases
Check complete.  PMS main database is OK.
Check complete.  PMS blobs database is OK.

Checking FTS (Full-Text Search) indexes
DEBUG:  Status = Error: stepping, database disk image is malformed (11)
  FTS index 'fts4_metadata_titles' - DAMAGED
    Error: Error: stepping, database disk image is malformed (11)
DEBUG:  Status = Error: stepping, database disk image is malformed (11)
  FTS index 'fts4_metadata_titles_icu' - DAMAGED
    Error: Error: stepping, database disk image is malformed (11)
DEBUG:  Status = Error: stepping, database disk image is malformed (11)
  FTS index 'fts4_tag_titles' - DAMAGED
    Error: Error: stepping, database disk image is malformed (11)
DEBUG:  Status = Error: stepping, database disk image is malformed (11)
  FTS index 'fts4_tag_titles_icu' - DAMAGED
    Error: Error: stepping, database disk image is malformed (11)
  FTS index 'fts4_metadata_titles' (blobs) - OK
  FTS index 'fts4_metadata_titles_icu' (blobs) - OK
  FTS index 'fts4_tag_titles' (blobs) - OK
  FTS index 'fts4_tag_titles_icu' (blobs) - OK
FTS integrity check complete. One or more FTS indexes are DAMAGED.
Use 'reindex' command (option 6) or 'automatic' (option 2) to rebuild.

NOTE: FTS indexes are damaged but main database structure is OK.
      Use 'reindex' (option 6) or 'automatic' (option 2) to rebuild.
1 Like
932

Not sure that I’d call it hacking the Database by having the Plex Sqlite remove the “bad” FTS Tables and have Plex reload them.
DBRepair reports no errors now.

Seriously, what in what I posted do you see as hacking the db?

933

“hacking” in this context (to me) means:

  • Delete the SHM / WAL. — which is anything not committed to the DB file
    – you don’t know which records are in and which aren’t. Metadata can easy get ouit of sync

Please understand, my opinion doesn’t matter anymore.

If DBRepair is doing the wrong thing, I’ll fix what I can or just remove the FTS code entirely. It was added recently to address problems with Collections and Playlists. If it’s doing more harm then good — It’s outta there.

934

Your opinion DOES matter.
I knew when I first posted this issue that it was a Plex Issue (not DBRepair) and IIRC, said so.
The FTL code is useful as it identified a PLEX problem in my database that I didn’t know existed, that you made the Plex Team aware of.

Deleting the SHM / WAL was not necessary for me as it’s “gone” with sudo systemctl stop plexmediaserver (I debated leaving that out of my write-up)

935

Having had a look at the sqlite3 docs a bit regarding how the integrity check of the FTS4 tables is being done in DBrepair.sh, I found this:

(See section 7.3)

That section outlines the means by which an integrity check is performed on a virtual table (e.g. the ‘fts4’ tables in the PMS DBs). The failure modes are described. The second one is:

  • Using the FTS4 content option and failing to manually keep the content in sync with the FTS4 inverted indices.

I’m guessing that’s the failure mode being triggered. I assume that the database is reindexed occasionally as part of normal PMS background maintenance activities, but there are times when the metadata_items (from which fts4_metadata_titles is created) and fts4_metadata_titles become out of sync.

Any failure of the table integrity check returns a SQLITE_CORRUPT error, regardless of the failure mode (even if the database isn’t really corrupt).

This is mostly supposition, based on the docs (and how the fts4 virtual tables are created).

It’s interesting reading. I’d recommend reading the intro to section 7, plus the sections for the ‘integrity-check’ and ‘rebuild’ commands, for anyone interested.

For what it’s worth, here’s how the fts4_metadata_titles table is created in the PMS DB schema:

CREATE VIRTUAL TABLE fts4_metadata_titles USING fts4(content=‘metadata_items’, title, title_sort, original_title)

So, it’s populated from the title, title_sort, and original_title columns of the metadata_items table.

936

While no errors yesterday (tested more than a few times), this morning the errors returned.

As @ChuckPa has said “This is a PMS issue. I’ve chatted with FordGuy who will take to Plex.” I’ll just have to wait and see if Plex fixes this issue.

Checking FTS (Full-Text Search) indexes

FTS index ‘fts4_metadata_titles_icu’ - DAMAGED

Error: Error: stepping, database disk image is malformed (11)

FTS index ‘fts4_tag_titles_icu’ - DAMAGED

Error: Error: stepping, database disk image is malformed (11)

FTS index ‘fts4_metadata_titles_icu’ (blobs) - OK

FTS index ‘fts4_tag_titles_icu’ (blobs) - OK

FTS integrity check complete. One or more FTS indexes are DAMAGED.

Use ‘reindex’ command (option 6) or ‘automatic’ (option 2) to rebuild.
937

I messaged one of the developers, referencing this thread and some tests I ran on my own server.

They filed an issue to investigate what is happening with the db, what DBRepair is seeing, etc.

That’s all the info I have right now. I’ll update if I hear anything, and Plex devs can obviously post updates themselves as needed.

4 Likes