Database Repair Error: no such collation sequence: icu_root

Server Version#: 1.22.3.4523 (Unraid Docker, PMS official)
Player Version#: 1.31.1.2262-74fdc6b7

So I’ll just get this out of the way first: unclean shutdown due to power failure, db correuption, no backups (only the blobs were backing, since apparently the main db has had corruption for a while I was unaware of, and seems the db won’t backup if there’s any corruption), and I’ve made sure my array is properly setup now going forward with my Plex config folder (appdata) is being backed up.

Spent the past few days reading every post on the Plex forums and Reddit I could Google, but still can’t repair my db.

I’ve tried the db repair procedure now on both the native terminal (Unraid/Linux), and copied over to my Windows desktop, and keep getting the same errors:

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'"
Error: no such table: schema_migrations

sqlite3 com.plexapp.plugins.library.db "PRAGMA integrity_check"
Error: no such collation sequence: icu_root

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

sqlite3 com.plexapp.plugins.library.db < dump.sql
Error: near line 1073618: no such collation sequence: icu_root

I’ve even tried opening dump.sql in SQL management studio, and deleting schema_migrations version 20180501000000 manually, and also found references to index_title_sort_naturalsort and index_title_sort_icu , but don’t really know what I’m looking at.

What’s weird is that Plex still runs seemingly fine, with the only symptom of weirdness being that TV Shows don’t show up on the home screen at all.

Anyone have any further tips or tricks to hopefully salvage my db? The main reason I don’t want to start over is mainly because I don’t want to re-invite my family member’s and friends and set up their permissions again. Outside of that I would have just cut my losses and started fresh.

TIA!

You can not use bare sqlite3.

You need to use /usr/lib/plexmediaserver/Plex\ Media\ Server --sqlite on Linux.
This will invoke the sqlite shell which is built into PMS and give you the additional plug-ins for sqlite.

Thank you for the reply. I’m trying to figure out how I can stop my PMS service on the Docker. It keeps auto-restarting when I kill the proc. Could I just spin up a server on my Windows desktop, then use sqlite?
Btw, I’m on my Windows desktop when running sqlite3

Tell docker to stop the container . You don’t “KIll” it. You command it to stop.

You cannot use SQLITE3 standalone.

Again, you must use Plex Media Server --sqlite to open the SQLite shell which is embedded to have support for the extra plug-ins

Finally think I’m doing it correctly now that I figured out how to stop Plex within the container. However, still not working out. Importing dump.sql results in a 0kb file, and no error on the console:

# ./plex_service.sh -d
# cd "/config/Library/Application Support/Plex Media Server/Plug-in Support/Databases/"
# cp com.plexapp.plugins.library.db com.plexapp.plugins.library.db.original
# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db "DROP index 'index_title_sort_naturalsort'"
Error: no such index: index_title_sort_naturalsort
# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db "DELETE from schema_migrations where version='20180501000000'"
# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db "PRAGMA integrity_check"
*** in database main ***
On tree page 270300 cell 11: 2nd reference to page 321059
Error: database disk image is malformed
# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db "DROP index 'index_title_sort_naturalsort'"
Error: no such index: index_title_sort_naturalsort
# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db "DELETE from schema_migrations where version='20180501000000'"
# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db .dump > dump.sql
# rm -rf com.plexapp.plugins.library.db                                               
# "/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db < dump.sql
# ls -l
total 2056568
drwxr-xr-x 1 root root        352 May 17 13:33 backups
-rw-r--r-- 1 root root  305267712 May 16 22:13 com.plexapp.plugins.library.blobs.db
-rw-r--r-- 1 plex users 283509760 May  6 02:03 com.plexapp.plugins.library.blobs.db-2021-05-06
-rw-r--r-- 1 plex users 283016192 May  9 02:04 com.plexapp.plugins.library.blobs.db-2021-05-09
-rw-r--r-- 1 plex users 285820928 May 12 02:04 com.plexapp.plugins.library.blobs.db-2021-05-12
-rw-r--r-- 1 plex users 304721920 May 15 02:03 com.plexapp.plugins.library.blobs.db-2021-05-15
-rw-r--r-- 1 root root          0 May 17 13:30 com.plexapp.plugins.library.db
-rw-r--r-- 1 root root  346404864 May 17 13:22 com.plexapp.plugins.library.db.original
-rw-r--r-- 1 root root  297172087 May 17 13:29 dump.sql

If the image is malformed (corrupt), you’ll need to use one of the backup databases

  1. Move this ‘current’ one off to the side.
  2. Copy the most recent backup (look at the dates)
  3. Name it com.plexapp.plugins.library.db
  4. Be certain to make sure UID/GID is correct

Future reference, You can type:

"/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db

and you’ll stay on the sqlite command line.

To note, this isn’t documented here or anywhere else that I can find. I’d assume this should / would need to be mentioned or added to this article: Repair a Corrupt Database | Plex Support

I’ve been having the same issues running Plex in a Docker container so can’t access the sqllite shell with the right plugins without using a separate machine / non docker Plex install.

1 Like

There are some other changes pending for that page :smiling_imp:

I want to say outloud but can’t. When you see it, you’ll like it – guaranteed.

1 Like

It’s been 18 days since this was opened, and even longer since the commands in those instructions stopped working.

Why is the official documentation still not corrected? It’s insane that searching for instructions to repair a corrupt database yields a real doc page with instructions that do not work, and you have to then google some random arcane error message to find a random forum thread to figure out how to fix it.

2 Likes

can you get into the command line and RENAME the backup copy to be com.plexapp.plugins.library.db ?

That works without anything other than mv and bash.

There are no backups for the main database, only the blobs.db. I was able to repair it after finding this post with the specific magical incantation to open a sqlite CLI with Plex’s extensions loaded, but FFS, fix the documentation. It’s been weeks. How is having broken documentation considered acceptable?

Or, hey, give us an option to use a real database instead of forcing sqlite…

1 Like

for anyone else using docker -

To not have to enter INTO the container to do the SQL work,
you can instead copy PMS out of the container the /usr/lib/plexmediaserver/

docker stop plex
docker cp plex:/usr/lib/plexmediaserver/ /opt/plexsql
cd /opt/plexsql

then you can simply execute your SQL

"Plex Media Server" --sqlite /path/to/your/DB/com.plexapp.plugins.library.db "SELECT * FROM sometable"

Hopefully at some point, months from now, Plex will update their documentation on how to Fix a corrupted DB

It’s not like they didn’t know, when they made this change, that the process would change

2 Likes

How does this work if the PMS install is only in the container, not the Docker/Unraid host?

In any docker container for Plex I’ve ever used (linux based) there are folders, for plex, stored outside of the container - the DB files are part of what’s stored outside of the container

If that is the case as clovisd5 said Plex REALLY needs to update their documentation on this page.

https://support.plex.tv/articles/201100678-repair-a-corrupt-database/

Blockquote

Linux

Linux installs don’t come with SQLite3 tools installed by default. However, you can download and install them yourself from the SQLite website.

Related Page : SQLite.org download page

I’ll find out why the docs don’t reflect it but you have the tool you need (Plex’s version of SQLite3 included with PMS

Please observe my shell session – errors included

-rw-r--r--.   1 chuck chuck  2147483648 Apr 19 03:02 ztest.bin
[chuck@lizum ~.507]$ cd /var/lib/plexmediaserver/Plex\ Media\ Server/Plug-in\ Support/Databases/
[chuck@lizum Databases.508]$ ls -la
total 14656
drwxr-xr-x. 2 chuck chuck      268 May 13 12:04 ./
drwxr-xr-x. 7 chuck chuck       96 May 13 02:56 ../
-rw-r--r--. 1 chuck chuck   358400 May 13 11:27 com.plexapp.plugins.library.blobs.db
-rw-r--r--. 1 chuck chuck    32768 May 13 12:04 com.plexapp.plugins.library.blobs.db-shm
-rw-r--r--. 1 chuck chuck        0 May 13 12:04 com.plexapp.plugins.library.blobs.db-wal
-rw-r--r--. 1 chuck chuck 14544896 May 13 11:27 com.plexapp.plugins.library.db
-rw-r--r--. 1 chuck chuck    32768 May 13 12:05 com.plexapp.plugins.library.db-shm
-rw-r--r--. 1 chuck chuck    31472 May 13 12:05 com.plexapp.plugins.library.db-wal
[chuck@lizum Databases.509]$ /usr/lib/plexmediaserver/Plex
Plex Commercial Skipper*  Plex Media Scanner*       Plex Script Host*         Plex Tuner Service*
Plex DLNA Server*         Plex Media Server*        Plex SQLite*              
Plex Media Fingerprinter* Plex Relay*               Plex Transcoder*          
[chuck@lizum Databases.509]$ /usr/lib/plexmediaserver/Plex\ S
Plex Script Host* Plex SQLite*      
[chuck@lizum Databases.509]$ /usr/lib/plexmediaserver/Plex\ SQLite com.plex
com.plexapp.plugins.library.blobs.db      com.plexapp.plugins.library.blobs.db-wal  com.plexapp.plugins.library.db-shm 
com.plexapp.plugins.library.blobs.db-shm  com.plexapp.plugins.library.db            com.plexapp.plugins.library.db-wal 
[chuck@lizum Databases.509]$ /usr/lib/plexmediaserver/Plex\ SQLite com.plexapp.plugins.library.db
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> .tables
accounts                             media_parts                        
blobs                                media_provider_resources           
cloudsync_files                      media_stream_settings              
devices                              media_streams                      
directories                          media_subscriptions                

/usr/lib/plexmediaserver/Plex\ SQLite com.plexapp.plugins.library.db

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