DB Error: no such collation sequence: naturalsort

[zbourdeau]

On the latest version of plex import seems to fail. This is the error I am encountering:

PlexPlaylistImporter.exe PlaylistFile.m3u PlayListFile
Plex playlist is not already created. Would you like to create it now (y/n)? y
DB Error: no such collation sequence: naturalsort

Note: The script is not detecting that the playlist already is created or is creating the playlist.

[dakusan]

Can you check issue #17 ? (I think this comment I just made should have a link in it)

[zbourdeau]

I was able to fix the original issue by manually updating the sql lite dll and placing it in the programs folder. Now I am having an issue creating a playlist:

DB Error: no such collation sequence: icu_root

Are there any workarounds? I created an additional issue in #19

[dakusan]

The 2 google searches that pulled up anything about icu_root are both from within the last 1 month. Both seemed to agree that you need to step back to an older version of sqlite3.

https://forums.plex.tv/t/plex-not-importing-new-media/706729

[zbourdeau]

Thank you for investigating this. It appears to be some sort of structural issue. Unfortunately, I tried with an older Sql lite version that I was able to find using the way back site here : https://www.sqlite.org/2019/sqlite-dll-win32-x86-3290000.zip with no luck. As a next step I posted a issue on the plex forum: https://forums.plex.tv/t/error-checking-db-integrity-error-no-such-collation-sequence-icu-root/709470 . There was a related issue and fix posted here: https://forums.plex.tv/t/error-deleting-collections/701586 but that fix doesn't seem to correct the specific issue i am seeing with the latest server version.

[zbourdeau]

Ok I was able to get the import working. Overall the primary blocker was schema related and not a sqlite3 version issue.

I took these steps to get the import working:

1. Manually created the playlists through the Plex UI. This allowed me to bypass the persistent issues I am having when creating new playlists DB Error: no such collation sequence: icu_root error .
2. I stopped the plex service.
3. I deleted the update triggers referenced in the https://forums.plex.tv/t/can-no-longer-update-library-database-with-sqlite3/701405/40 thread with DB Browser. These triggers are part of a new feature set on Plex and are blocking regular db inserts. Without taking this step upon insert I was seeing the this error DB Error: unknown tokenizer: collating error .
4. I imported all playlists.
5. I opened the DB again with DB Browser and recreated the two triggers I removed.
6. Restarted the plex service.

Overall it seems that raw db inserts and updates are pretty broken. Not sure what can be done through the python libraries given the recent schema changes.

[berz]

zbourdeau's workaround allowed me to import a playlist into an existing playlist:

• create new playlist named testplaylist in plex web interface
• shutdown plexmediaserver
• remove the fts4_metadata_titles_before_update_icu and fts4_metadata_titles_after_update_icu triggers in the plex sqlite database
• import playlist using PlexPlaylistImporter.py
• recreate the database triggers
• restart plexmediaserver

Could the abcence of these triggers during importing lead to database corruption?

I will look into the icu_root error problem since creating the playlists manually is not an option for me.

[dakusan]

Thanks for the update. If this continues to be a problem for people I'll see about adding it into the codebase.

[berz]

I went further down into this rabbit hole and managed to find a partial workaround for creating playlists.
The icu_root error is caused by this index on the metadata_items table:

CREATE INDEX 'index_title_sort_icu' ON 'metadata_items' ('title_sort' COLLATE icu_root);

Changing this index to use the BINARY collation seems to resolve the problem. But we still run into the unknown tokenizer error while creating a playlist. We can avoid this error by disabling the fts4_metadata_titles_after_insert_icu trigger. After these two changes the script is able to create a playlist. Disabling the two triggers mentioned in the comment above allows us to add tracks to this new playlist.

One problem remains: while the new playlist is added to the database it does not show up in the plex interface. This might be caused by the disabled trigger that would normally add an entry to fts4_metadata_titles_icu:

CREATE TRIGGER fts4_metadata_titles_after_insert_icu AFTER INSERT ON metadata_items BEGIN INSERT INTO fts4_metadata_titles_icu(docid, title, title_sort, original_title) VALUES(new.rowid, new.title, new.title_sort, new.original_title); END;

So, to recap, this workaround lets the script create a playlist and add tracks to it, but the playlist does not show up in plex:

- shutdown plex
- open the plex database (com.plexapp.plugins.library.db) using SQLite
    sqlite> DROP INDEX index_title_sort_icu;        
    sqlite> CREATE INDEX 'index_title_sort_icu' ON 'metadata_items' ('title_sort' COLLATE BINARY);
    sqlite> REINDEX index_title_sort_icu;
    sqlite> DROP TRIGGER fts4_metadata_titles_after_insert_icu;
    sqlite> DROP TRIGGER fts4_metadata_titles_before_update_icu;
    sqlite> DROP TRIGGER fts4_metadata_titles_after_update_icu;
- import playlist using PlexPlaylistImporter
- recreate the triggers:
    sqlite> CREATE TRIGGER fts4_metadata_titles_after_insert_icu AFTER INSERT ON metadata_items BEGIN INSERT INTO fts4_metadata_titles_icu(docid, title, title_sort, original_title) VALUES(new.rowid, new.title, new.title_sort, new.original_title); END;
    sqlite> CREATE TRIGGER fts4_metadata_titles_before_update_icu BEFORE UPDATE ON metadata_items BEGIN DELETE FROM fts4_metadata_titles_icu WHERE docid=old.rowid; END;
    sqlite> CREATE TRIGGER fts4_metadata_titles_after_update_icu AFTER UPDATE ON metadata_items BEGIN INSERT INTO fts4_metadata_titles_icu(docid, title, title_sort, original_title) VALUES(new.rowid, new.title, new.title_sort, new.original_title); END;
- restart plex

[berz]

I found some info about the underlying problem causing these issues in this thread on the Plex forum. It seems Plex recently started using collation extensions to SQLite to improve search. This means the Plex database can only be edited using the SQLite version built into Plex (/usr/lib/plexmediaserver/Plex Media Server --sqlite on my ubuntu system).

We are running into these issues because PlexPlaylistImporter uses the SQLite version bundled with python, which does not contain the extensions Plex uses now. Can PlexPlaylistImporter be changed to use the Plex version of SQLite?

[dakusan]

On windows this software does actually use the sqlite3.dll. So if you put a sqlite3.dll in its working directory, it will use that.

For Linux, you can probably use LD_LIBRARY_PATH to accomplish what you want. Just set the directory of where the sqlite3.so file is (I'm assuming that is the file name, I do not have plex installed on linux ATM). Your best bet is to probably copy the sqlite3.so file that you want to use into the PlexPlaylistImporter working directory and use "./" as your LD_LIBRARY_PATH (it might need an absolute path though, unsure).

[lpemcee21]

Ok I was able to get the import working. Overall the primary blocker was schema related and not a sqlite3 version issue.

I took these steps to get the import working:

1. Manually created the playlists through the Plex UI. This allowed me to bypass the persistent issues I am having when creating new playlists DB Error: no such collation sequence: icu_root error .
2. I stopped the plex service.
3. I deleted the update triggers referenced in the https://forums.plex.tv/t/can-no-longer-update-library-database-with-sqlite3/701405/40 thread with DB Browser. These triggers are part of a new feature set on Plex and are blocking regular db inserts. Without taking this step upon insert I was seeing the this error DB Error: unknown tokenizer: collating error .
4. I imported all playlists.
5. I opened the DB again with DB Browser and recreated the two triggers I removed.
6. Restarted the plex service.

Overall it seems that raw db inserts and updates are pretty broken. Not sure what can be done through the python libraries given the recent schema changes.

Creating the playlist in Plex before importing without removing the triggers did not work and resulted in a new error:
DB Error: no such module: fts4
Following @zbourdeau's steps worked for me.

Thanks!

[zbourdeau]

Unfortunately, My playlists continue to get wiped out by plex server updates. It looks like I am going to have to do this again. It would be ideal if Plex had a built in import API where we can provide a file name or file path and a playlist name. Anyone know who to reach out to at Plex to ask for better functionality in this regard?

[dakusan]

It's been over 5 years since I've touched this project and things may have changed in their sqlite user databases. As far as I know, there is no official Plex API. The only official "API" I could find of theirs involved querying data via the web client. It looks like there has been some serious work in the last few years on hooking into the undocumented Plex API in Python and some other languages.

I can almost guarantee you that reaching out to the creators of Plex will get you nowhere. The most you may get is to add a +1 to a request for an API, which is unlikely to ever be responded to. This kind of thing just isn't on their radar of interest.

Did you create your playlist originally in Plex and just update the contents through this project?