Server Version#: 4.136.1
Player Version#:
<If providing server logs please do NOT turn on verbose logging, only debug logging should be enabled>
Hello All,
Is there a way to merge the metadata_items_views table from the backup database to current database ? My server library got corrupted and i have no choice but restore 2 months old backup. How can I recover view history from database to restored db ?
My library got corrupted on 1st Oct, I restored the database of 1st Aug. I have backups of 1st oct and 1st Aug DB. 1st Oct db has problem with library so I can not use it. 1st Aug db has the library in right order but missing the view history for Aug & Sep. Is is possible to merge the view history of Aug and Sep to 1st Aug DB which is restored and working fine ?
There are a couple of ways to do this but I think this is probably the most straight forward.
Use the following command to dump the metadata_item_views from both your db:
"/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db ".output export-metadata_item_views-data.sql" ".dump metadata_item_views"
You will need to do this twice from both your dbs. This will give you two sql files, one from each db (make sure to change the output filename). These sql files can then be viewed/edited with a text editor.
Now the semi hard part. You will need to figure out which records you want. Assuming 1st Aug with an Epoch timestamp : 1722470400 find around that time from the viewed_at column in the exported dump. You’ll want to copy everything you want from there into your other sql file, being careful not to have duplicate ids.
Once your happy with your sql file then with the db you want to use going forward you can import this into it using the following to delete the current metadata_item_views table in the db first…
"/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db "delete from metadata_item_views"
and then the following to import the one you manually edited:
"/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db ".read export-metadata_item_views-data.sql"
I’d also run a integrity_check after the import …
"/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db "PRAGMA integrity_check"
I done stuff like this in the past and its worked well but as always KEEP backups just in case and ensure plex is not running when coping around the dbs or when editing the db directly.
What about just directly copying? I wrote this off the top of my head. I have not tested this at all.
"/usr/lib/plexmediaserver/Plex Media Server" --sqlite com.plexapp.plugins.library.db
ATTACH 'com.plexapp.plugins.library.db-2024-10-01.db' AS backup;
INSERT OR IGNORE INTO metadata_item_views SELECT * FROM backup.metadata_item_views;
DETACH backup;
.exit
Oh, well… this should go into some kind of FAQ (with Dkouro’s addition and opening remarks about working with SQL databases.
And expand explanations to become usable for non-db specialist users (like with links on how to find the dbs on different OS, explain if certain software is needed on OS X/Y/Z, etc).
Thanks @SwiftPanda16 , @dokuro there will some records which will have the same id. E.g. for the db of 1st Oct has metadata_item_views id range from 1 to 5000 & db of 1st Aug has metadata_item_views id range from 1 to 4500. I restored the db of 1st Aug on 1st Oct and started watching the shows, so as of Oct 15, the plex db has metadata_item_views id range from 1 to 4600. The db assign the id from 4501 to the shows/movie I watched between 1st oct and 15th Oct. Will it handle the duplication of id ?
Is there a way to insert them with new id or update of the id of existing records ?
Also, the table metadata_items_settings table has all the watch history available of the period between Aug 1 to Oct 1. Is there a way to generate metadata_items_views from metadata_items_settings table.
My plex shows the shows/movies which i watched between 1st Aug to 1st Oct as watched but it does not reflect in watch history.
Duplicate unique ids are not allowed and sql will report an error if you try and insert into a table where an unique id record is already found.
Unless SwiftPanda16 has some other wizardry to handle this which I have not considered then I’d suggest manually editing the export dump sql file and adjusting the ids to unique ones and then import that into your db. Hopefully your not dealing with a lot of records …
Nice. Ran a quick test and it worked. However, be careful with the column named index as it’ll report syntax errors due to index being a reserved word in sql.
To get around that place index within quotes, like I done below:
INSERT INTO metadata_item_views
(account_id,
guid,
metadata_type,
library_section_id,
grandparent_title,
parent_index,
parent_title,
`index`,
title,
thumb_url,
viewed_at,
grandparent_guid,
originally_available_at,
device_id)
SELECT account_id,
guid,
metadata_type,
library_section_id,
grandparent_title,
parent_index,
parent_title,
`index`,
title,
thumb_url,
viewed_at,
grandparent_guid,
originally_available_at,
device_id
FROM backup.metadata_item_views
WHERE id >= 4501;
Thanks dokuro. I tried that and got the same syntax error, i am not a dba expert so followed the process of dumping data in sql file and modified the duplicate record number and import back. It worked. Thanks a lot.
I still failed to understand if my metadata_view_settings table has all the data, why plex can not build the metadata_item_views table from it.
Anyways, my problem is fixed and I am able to see the play history in player.
Thanks a lot for all the support.
