Server Version#: Current Plex Server for Windows Version as of 2025-01-13 22:27:00
I hope this guide helps someone. Took me a bit to figure out the query for fixing tv shows mess. To be clear, I used ChatGPT to help with writing this quide, but the way I approached fixing stuck tv shows was my idea. I just used ChatGPT as a sounding board while troubleshooting. Also, between movies and TV shows, you need to keep track of the metadata_type and library_section_id values. They may be different from the values I have in this guide. If you want to use the same values you would have had to have created at least two other plex libraries before adding your tv-show library I first created a movies (ultra HD) library, followed by a movies (HD) library, followed by my TV show library
Guide: Fixing Stuck Movies and TV Shows in Plex’s Recently Added
If you’re dealing with movies or TV show episodes that are perpetually stuck at the front of the “Recently Added” section in Plex, this guide is for you. By directly addressing the underlying database timestamps, we can correct this issue once and for all.
Important Notes
-
Back Up Your Plex Database:
Before proceeding, make a backup of your Plex database file (com.plexapp.plugins.library.db). This ensures you can restore it in case anything goes wrong. -
Prerequisite Knowledge:
This guide assumes you’re comfortable working with SQLite and understand how to manipulate the Plex database. If you’re new to this, take some time to familiarize yourself with SQLite tools like DB Browser for SQLite. -
Choose Your SQLite Client:
For this guide, we use DB Browser for SQLite, but any SQLite client should work.
Step 1: Disable Plex Database Triggers
To safely update the database, you need to temporarily disable two triggers that affect the metadata_items table:
fts4_metadata_titles_before_update_icufts4_metadata_titles_after_update_icu
How to Disable the Triggers:
- Locate the triggers under the “Triggers” node in your SQLite client.
- Right-click each trigger and delete them (don’t worry, we’ll recreate them later).
- Optionally, note down the SQL statements for the triggers before deleting them:
-- Trigger: fts4_metadata_titles_before_update_icu 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; -- Trigger: fts4_metadata_titles_after_update_icu 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;
- Optionally, note down the SQL statements for the triggers before deleting them:
Step 2: Fix Movies
Bulk Update All Movies:
If you want all movies to have their added_at timestamp match their original release date (originally_available_at), use this query:
UPDATE metadata_items
SET added_at = originally_available_at
WHERE library_section_id = 1 AND metadata_type = 1;
Update Specific Stuck Movies:
If only a few movies are stuck at the front, identify the last correct added_at timestamp and update the problematic entries:
UPDATE metadata_items
SET added_at = originally_available_at
WHERE library_section_id = 1 AND metadata_type = 1 AND added_at > LAST_VALID_TIMESTAMP;
Step 3: Fix TV Shows
Create a Sorted Temporary Table:
Run this query to sort TV show episodes by their air date (originally_available_at) and store the results in a temporary table:
CREATE TEMP TABLE sorted_items AS
SELECT
M.id,
M.library_section_id,
M.metadata_type,
M.parent_id,
M.title,
-- TV Show Title
CASE
WHEN M.metadata_type IN (3, 4) THEN
(SELECT M1.title FROM metadata_items AS M1 WHERE M1.id =
(SELECT M2.parent_id FROM metadata_items AS M2 WHERE M2.id = M.parent_id LIMIT 1))
ELSE NULL
END AS tv_show_title,
-- Season Title
CASE
WHEN M.metadata_type = 4 THEN
(SELECT M1.title FROM metadata_items AS M1 WHERE M1.id = M.parent_id)
ELSE NULL
END AS episode_season,
-- Episode Number
CASE
WHEN M.metadata_type = 4 THEN M."index"
ELSE NULL
END AS episode_number,
M.year,
DATETIME(M.originally_available_at, 'unixepoch') AS originally_available_at_dt,
M.originally_available_at
FROM metadata_items AS M
WHERE library_section_id = 3 AND metadata_type IN (2, 3, 4)
ORDER BY originally_available_at_dt DESC;
Update added_at for TV Show Episodes:
After creating the temporary table, update the added_at column in the main table:
UPDATE metadata_items
SET added_at = (
SELECT originally_available_at
FROM sorted_items
WHERE sorted_items.id = metadata_items.id
)
WHERE id IN (SELECT id FROM sorted_items);
Step 4: Verify the Changes
Run the following query to ensure that added_at matches originally_available_at:
SELECT id, title, DATETIME(added_at, 'unixepoch') AS added_at_dt, DATETIME(originally_available_at, 'unixepoch') AS originally_available_at_dt
FROM metadata_items
WHERE id IN (SELECT id FROM sorted_items)
ORDER BY originally_available_at_dt ASC;
Step 5: Recreate the Triggers
Finally, recreate the deleted triggers:
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;
Step 6: Cleanup
- The temporary table (
sorted_items) will automatically be dropped when you close the SQLite session. - Write your changes to the database file.
Conclusion
This method ensures your Plex library’s “Recently Added” section reflects accurate dates, both for movies and TV shows. By leveraging the originally_available_at field, you create a consistent and logical order that aligns with media release dates.
