Can no longer update library database with sqlite3

Plex Media Server Metadata & Adding Files
,
49

So, what I learned is that there are two ways to go about this.

  1. Use Plex’s internal sqlite commands, which you can find examples of above. (Note: I did not do this because I wasn’t sure what commands to do for my Synology NAS in particular.)
  2. Use manual commands to find and remove the triggers on the table that are causing the problem, then reinstate those triggers afterward. This is what solved it for me.

So, how did I go about method #2? Here’s how: (my example is for updating the added_at date of media, which modifies the ‘metadata_items’ table, so adjust as necessary from the commands I’m listing here) This command would be run from the shell session at the same folder as the database file. Also, I’m going to assume you already stopped Plex and made any necessary backup.

  • Run a command to list the offending triggers…
$> sqlite3 com.plexapp.plugins.library.db "SELECT name, sql FROM sqlite_master WHERE type='trigger' AND tbl_name='metadata_items' AND name LIKE '%update%';"
  • Paying note to the triggers it identifies, remove those triggers…
$> sqlite3 com.plexapp.plugins.library.db "DROP TRIGGER fts4_metadata_titles_before_update_icu;"
$> sqlite3 com.plexapp.plugins.library.db "DROP TRIGGER fts4_metadata_titles_after_update_icu;"
  • Run the update command I originally wanted to run…
$> sqlite3 com.plexapp.plugins.library.db "UPDATE metadata_items SET added_at = originally_available_at WHERE originally_available_at IS NOT NULL;"
  • Reinstate the triggers, by copying over the exact text that the first command gave me for the trigger language…
$> sqlite3 com.plexapp.plugins.library.db "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;"
$> sqlite3 com.plexapp.plugins.library.db "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;"
10 Likes