Error starting PMS on QNAP after upgrading QPKG

[ChuckPa]

@madhitz

May I please get a ZIP of your databases?

1. com.plexapp.plugins.library.db
2. com.plexapp.plugins.library.blobs.db

I’ll open a PM for you to attach the link to where I can download them
(dropbox or similar is probably

[madhitz]

PM sent.

[ChuckPa]

Got some info for you.

We don’t know why/how it happened.

We see duplicated rows in the DB where no duplicates are allowed / can exist.

Engineering has given me how to fix your DB ONLY

1. Stop Plex
2. SSH into the QNAP
3. sudo bash
4. whoami (confirm you are admin)

# Get into the Databases directory
cd /share/*/.qpkg/Ple*/Li*/Ple*/Plu*/Datab*
ls -la         # Confirms you are in Databases

# Start Plex SQLite
/share/*/.qpkg/Ple*/*SQLite* com.plexapp.plugins.library.db

Now we copy/paste these two commands (VERY LONG) into the sqlite> prompt

DELETE FROM schema_migrations WHERE version IN ( select version FROM( SELECT version, ROW_NUMBER() OVER(PARTITION BY version) AS rownum FROM schema_migrations) AS sub WHERE rownum > 1);
CREATE UNIQUE INDEX "unique_schema_migrations" ON "schema_migrations" ("version" );

When done, Control+D (eof) to exit gracefully from SQLite

Start PMS,
Confirm normal operation,

Now upgrade PMS.

---

I have a question for you.

Have you, at any time, used DBRepair and told it to IGNORE ?

[madhitz]

Hi Chuck,

All steps performed as requested and PMS now starting and running latest production software correctly.

Thank you for all your help.

To answer the final question, I do not believe we’ve ever used DBRepair in the past with IGNORE option enabled. IF we did, it would have been a long time ago, as gladly the PMS software is generally very robust and only requires simple upgrades/updates.

Derek

[ChuckPa]

Derek,

Thank you for confirming.

This confirms for us that it’s something PMS did. Now to find out why or how to remedy.

(Discussion is that DBRepair is about to become a WHOLE lot smarter about what should be INSIDE the DB tables)

[madhitz]

That’s great. Always nice to know the little loopholes and bugs that exist – as you said, the challenges now are to figure them out. If I can help with anything else, including beta testing, access to library, or other samples of database states, let me know.

The best insight I can provide now is that possibly it was a hard shutdown of the NAS at some point maybe kicked this off? I know we did have a UPS fail which caused a hard down situation. I also know that one time the upgrade via the QNAP GUI barfed and that’s when I started manually doing it via a CLI. These are just speculative of course.

As mentioned previously, I have 2 almost identical setups (same QNAP model, same memory and drive configuration, same version of PMS, and basically the same library contents, only difference being audience/users) and neither one of them had this issue. I wish I had the logs to provide to you, as that may be of some insight, but I don’t believe they exist.