SQL error on latest PMS

[rg9400]

I am not sure when this error started occurring, but UpdateTool was definitely working after the changes for the new TV agent were added. However, I noticed the container constantly restarting with the below error recently. PMS Version Version 1.22.1.4200

[INFO ] - 2021-03-17 14:12:15 @ ImdbDatabaseSupport.requestBatchUpdateOf: Running batch update for 6 items with new plex agent.

[INFO ] - 2021-03-17 14:12:15 @ ImdbDockerImplementation$ImdbBatchJob.run: Job returned ERROR : [SQLITE_ERROR] SQL error or missing database (unknown tokenizer: collating)

[ERROR] - 2021-03-17 14:12:15 @ ImdbDockerImplementation$ImdbBatchJob.run: SQLiteException exception encountered...

[ERROR] - 2021-03-17 14:12:15 @ ImdbDockerImplementation$ImdbBatchJob.run: Please contact the maintainer of the application with the stacktrace below if you think this is unwanted behavior.

[ERROR] - 2021-03-17 14:12:15 @ ImdbDockerImplementation$ImdbBatchJob.run: ========================================

[ERROR] - 2021-03-17 14:12:15 @ ImdbDockerImplementation$ImdbBatchJob.run: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (unknown tokenizer: collating)

at org.sqlite.core.DB.newSQLException(DB.java:1012)

at org.sqlite.core.DB.newSQLException(DB.java:1024)

at org.sqlite.core.DB.throwex(DB.java:989)

at org.sqlite.core.NativeDB.prepare_utf8(Native Method)

at org.sqlite.core.NativeDB.prepare(NativeDB.java:134)

at org.sqlite.core.DB.prepare(DB.java:257)

at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:45)

at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)

at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:25)

at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:35)

at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:241)

at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:205)

at updatetool.imdb.ImdbDatabaseSupport.internalBatchUpdate(ImdbDatabaseSupport.java:165)

at updatetool.imdb.ImdbDatabaseSupport.requestBatchUpdateOf(ImdbDatabaseSupport.java:154)

at updatetool.imdb.ImdbPipeline.updateDatabase(ImdbPipeline.java:195)

at updatetool.imdb.ImdbPipeline.updateDatabase(ImdbPipeline.java:48)

at updatetool.api.Pipeline.invoke(Pipeline.java:26)

at updatetool.imdb.ImdbJobRunner.run(ImdbJobRunner.java:19)

at updatetool.imdb.ImdbDockerImplementation$ImdbBatchJob.run(ImdbDockerImplementation.java:254)

at updatetool.TaskWrapper.run(Main.java:252)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

[ERROR] - 2021-03-17 14:12:15 @ ImdbDockerImplementation$ImdbBatchJob.run: ========================================

[ERROR] - 2021-03-17 14:12:15 @ ImdbDockerImplementation$ImdbBatchJob.run: The application will terminate now.

[dnmaia]

The exact same is happening with me.

[mynttt]

Could you guys run this command on the database after the Plex Update and post the output here? My guess is they changed something with the DB.

.schema metadata_items

[rg9400]

CREATE TABLE IF NOT EXISTS "metadata_items" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "library_section_id" integer, "parent_id" integer, "metadata_type" integer, "guid" varchar(255), "media_item_count" integer, "title" varchar(255), "title_sort" varchar(255) COLLATE NOCASE, "original_title" varchar(255), "studio" varchar(255), "rating" float, "rating_count" integer, "tagline" varchar(255), "summary" text, "trivia" text, "quotes" text, "content_rating" varchar(255), "content_rating_age" integer, "index" integer, "absolute_index" integer, "duration" integer, "user_thumb_url" varchar(255), "user_art_url" varchar(255), "user_banner_url" varchar(255), "user_music_url" varchar(255), "user_fields" varchar(255), "tags_genre" varchar(255), "tags_collection" varchar(255), "tags_director" varchar(255), "tags_writer" varchar(255), "tags_star" varchar(255), "originally_available_at" datetime, "available_at" datetime, "expires_at" datetime, "refreshed_at" datetime, "year" integer, "added_at" datetime, "created_at" datetime, "updated_at" datetime, "deleted_at" datetime, "tags_country" varchar(255), "extra_data" varchar(255), "hash" varchar(255), "audience_rating" float, "changed_at" integer(8) DEFAULT 0, "resources_changed_at" integer(8) DEFAULT 0, 'remote' integer);
CREATE INDEX "index_metadata_items_on_library_section_id" ON "metadata_items" ("library_section_id" );
CREATE INDEX "index_metadata_items_on_parent_id" ON "metadata_items" ("parent_id" );
CREATE INDEX "index_metadata_items_on_created_at" ON "metadata_items" ("created_at" );
CREATE INDEX "index_metadata_items_on_index" ON "metadata_items" ("index" );
CREATE INDEX "index_metadata_items_on_title" ON "metadata_items" ("title" );
CREATE INDEX "index_metadata_items_on_title_sort" ON "metadata_items" ("title_sort" );
CREATE INDEX "index_metadata_items_on_guid" ON "metadata_items" ("guid" );
CREATE INDEX "index_metadata_items_on_metadata_type" ON "metadata_items" ("metadata_type" );
CREATE INDEX "index_metadata_items_on_deleted_at" ON "metadata_items" ("deleted_at" );
CREATE INDEX "index_metadata_items_on_library_section_id_and_metadata_type_and_added_at" ON "metadata_items" ("library_section_id", "metadata_type", "added_at" );
CREATE INDEX "index_metadata_items_on_hash" ON "metadata_items" ("hash" );
CREATE INDEX "index_metadata_items_on_added_at" ON "metadata_items" ("added_at" );
CREATE INDEX "index_metadata_items_on_originally_available_at" ON "metadata_items" ("originally_available_at" );
CREATE INDEX "index_metadata_items_on_changed_at" ON "metadata_items" ("changed_at" );
CREATE INDEX "index_metadata_items_on_resources_changed_at" ON "metadata_items" ("resources_changed_at" );
CREATE INDEX 'index_metadata_items_on_original_title' ON 'metadata_items' ('original_title');
CREATE INDEX 'index_metadata_items_on_absolute_index' ON 'metadata_items' ('absolute_index');
CREATE INDEX 'index_metadata_items_on_remote' ON 'metadata_items' ('remote');
CREATE INDEX 'index_title_sort_icu' ON 'metadata_items' ('title_sort' COLLATE icu_root);
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;
CREATE TRIGGER fts4_metadata_titles_before_delete_icu BEFORE DELETE ON metadata_items BEGIN DELETE FROM fts4_metadata_titles_icu WHERE docid=old.rowid; END;
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;
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;

[mynttt]

I don't know if this is the public version from the 9th March but it seems that they added a custom collater into the database now via the COLLATE NOCASE behind the title column.

This is really bad because collaters in sqlite are compiled into the shared library. That means that only the customized plex sqlite library is able to use this table.

Edit: I suspect that maybe because a prepared statement is used this error could appear. Maybe by changing that it could be removed but that has to be tested first.

I wonder why reading from the table works but writing crashes it. Might need to investigate if there's a chance on getting it to work again with a different way of running the query. As a last step it might work by replacing the sqlite libraries that updatetool uses with the ones of Plex, but that might cause problems with the Java library interfacing with the native libraries.

This really has the potential of rendering this tool useless. But as of right now it needs to be investigated first. You should send your database to my mail for that.

[mrjohnpoz]

Yeah just noticed this myself, and yeah just updated pms the other day.. currently running 1.22.1.4200

Be happy to send you copy of current db.. Is that something that will help. Take it same db sent you before.

[mynttt]

Yeah it would make sense to send me that copy. I've updated my docker and it's only sitting at 1.22.0.4163 right now and works with no issues.

[mrjohnpoz]

Ok sent.. Let me know if any issues getting it..

[mynttt]

I've retrieved it!

[mynttt]

I can also reproduce this on my system.

[mrjohnpoz]

Fingers crossed something you can easy fix.. I woke this morning and noticed some stuff was using wrong ratings.. And was like what the hell.. Checked my docker and it was off.. tried to restart it, but then it stopped - looking at the log saw same errors posted above..

[mynttt]

Okay the issue is that they changed their schema and now use the ICU library which is indicated by this change:

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

This causes the same issue in every SQLite3 version that is not compiled with the ICU addon. I tried copying the Plex SQLite3 version but the Java SQLite3 library is unable to work with this version. I suspect because Plex did compile it without JNI support which is needed by the Java JDBC driver.

This has been discussed once at the SQLite3 driver's repository.

So there's only one solution to prevent UpdateTool from being incompatible: That is to compile the same sqlite library that the JDBC version uses but with ICU support added. I suspect that this will kill some more exotic platforms but I guess that is fine as long as Linux x64, Windows x64 and maybe MacOS x64 work. I might look into this next week.

[mrjohnpoz]

Sweet.. Looking forward to it.. Thanks!

[rh535]

Thank you for taking a look into this. I love this for my Plex library on unRAID.

[mynttt]

Found a solution that works without recompiling sqlite3 (which is a mess and I would rather let UpdateTool die then diving in static linking that with ICU support on multiple architectures)!

Instead of adding ICU support we just disable what actually causes the issue here. I just looked at the new schema again and saw these triggers that I've overseen so far. Disabling them allows me to run UPDATE operations without that error and without ICU support enabled. After doing my update I'll just re-enable them, because the timeframe of updating is so small it should not cause any issues when editing something manually at the same time (and even if, it would not be that dramatic for the item to not end up in the index).

So I'm just gonna do this:

DROP TRIGGER fts4_metadata_titles_before_update_icu;
DROP TRIGGER fts4_metadata_titles_after_update_icu;
... EXECUTE UPDATE ... 
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;
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;

Messy solution but at least it will cause this tool to not die. Probably needs some additional checks to only execute this when the trigger is actually present and some securities to always restore the triggers no matter what happens. And it should also detect when the trigger changes to not mess up things after a future update. If you guys are lucky I might get it done this week but I'll have to see how that goes at the moment.

[dnmaia]

Thanks man. You are awesome. Very good tool. Keep it alive.