MagicPinball 3 Posted February 17, 2020 Posted February 17, 2020 I recently had a system crash and reloading everything. Loaded emby and followed the restore instructions. I get a Foreign Key error and cannot update the library.db. 1. How can I fix this and do a restore? (My last backup was about 3 months ago. I guess the db structure changed.) 2. How can I get a listing of Watched Movies? I can use SQL with SQLite, but I am not familiar with the data structure. Plus, I am not a SQL expert. Can I get a sql script?
MagicPinball 3 Posted February 17, 2020 Author Posted February 17, 2020 Foreign key constraint failed
Happy2Play 9782 Posted February 17, 2020 Posted February 17, 2020 There is no way to take a older database and import it into a newer database do to the column changes. There are dozens of topics with this exact issue. But from a same database to same database there is no issue.
MagicPinball 3 Posted February 17, 2020 Author Posted February 17, 2020 That's what I guessed. That's why I asked in part 2 of my post: How can I get a listing of Watched Movies from library.db? I can use SQL with SQLite, but I am not familiar with the data structure. Plus, I am not a SQL expert. Can I get a sql script?
Happy2Play 9782 Posted February 17, 2020 Posted February 17, 2020 Not user if there is even a compatible way to do this but I never really tested this. https://emby.media/community/index.php?/topic/77238-unable-to-migrate-to-new-server/?p=784231
MagicPinball 3 Posted February 17, 2020 Author Posted February 17, 2020 Not sure if I understand. If I cannot update my library.db file to the latest database, can I get a listing of watched/unwatched movies from the old library.db file using a sql script? Can you supply a sql script?
Luke 42080 Posted February 17, 2020 Posted February 17, 2020 Someone will have to design one. That's why i pulled in chef in case he might have done it before.
chef 3810 Posted February 18, 2020 Posted February 18, 2020 (edited) So we're going to read the old library.db to get watched and unwatched statuses and apply them to items in the new library.db file. Yes, I believe I can do this. @@MagicPinball can this be a windows thing, or are you on another platform? Edited February 18, 2020 by chef
MagicPinball 3 Posted February 18, 2020 Author Posted February 18, 2020 Windows 10 and I have SQlite or I can download the SQL client that you need. My last library.db file is about 3 months ago. I realize that any movie after that, I will have to set manually. I have about 500 movies in my library. If I can update my current library.db from my backup, that would be great. If I can get a listing, that would be good also. Thank you for your time and effort.
chef 3810 Posted February 18, 2020 Posted February 18, 2020 (edited) I'm just looking into the table data now. I have a basic console app outlined that should do the trick. I just need to figure out some proper queries and I think it should be able to read through the old library db, then use the API client to apply the changes. I'll let you know how it goes. Edit: just one user's watched status? This might get complicated... Edit: ..and it's complicated. I'm not sure where 'isPlayed' markers are stored in the library.db I've checked 'UserDatas' table and the 'MediaItems' table. Most likely there has to be a comparison between the user data and the Media item data, just like in the API. But by the time my brain wraps around what to do, you could probably use the multi-select option in the web app and change your watched status. Which is faster then going through movies one at a time to click the checkmark. There might be a better sqlite guy out there. Edited February 18, 2020 by chef
vaise 340 Posted April 23, 2020 Posted April 23, 2020 (edited) I have a questions that may (or may not be) loosely connected to this. For backups, I currently use the emby backup plugin and have 31 daily backups I can go back too. I use a robycopy script to copy all my media from the Unraid server NAS to a desktop PC running stablebit drivepool for onsite backups. Should I have a failure meaning lost media on my server, I would copy the files media files back from the PC to the NAS. I suspect Emby will see them all as new and all watchlists gone ? If this is the case, what is the correct way of restoring the data, and keeping media and watchlists as it is ? By watchlists - I mean played states...... Edited April 23, 2020 by vaise
crusher11 1101 Posted April 23, 2020 Posted April 23, 2020 Played states are stores by movie ID, not by file name.
vaise 340 Posted April 23, 2020 Posted April 23, 2020 Played states are stores by movie ID, not by file name. Howdy - Can you elaborate on this. I believe in the past if I have added a later better quality media file of the same name, then emby reports a new file added. It then appears in the 'Latest Media' sections as a new file Ideally, I dont want that to happen either, but I could live with that if the played states are still correct.
ebr 16185 Posted April 23, 2020 Posted April 23, 2020 Howdy - Can you elaborate on this. I believe in the past if I have added a later better quality media file of the same name, then emby reports a new file added. It then appears in the 'Latest Media' sections as a new file Ideally, I dont want that to happen either, but I could live with that if the played states are still correct. If you store metadata with your media in nfo files, this will not happen either because the original added date will be in the nfo file. Whenever possible we store play state and favorite status, etc. by a global ID of some sort (tmdb, tvdb+season+episode, etc.). We do this for this very reason - so the data will survive technical changes to or relocation of the media.
vaise 340 Posted April 23, 2020 Posted April 23, 2020 hhhmmm - ok - I have never used nfo files to date. so - If I was to want to convert to nfo for this backup/restore option, I assume it is much more than just clicking the nfo option in the library config and then doing a metadata refresh ? If it is just that, do they all become 'new' at that time again ?
Happy2Play 9782 Posted April 23, 2020 Posted April 23, 2020 Howdy - Can you elaborate on this. I believe in the past if I have added a later better quality media file of the same name, then emby reports a new file added. It then appears in the 'Latest Media' sections as a new file Ideally, I dont want that to happen either, but I could live with that if the played states are still correct. This should only happen on items that do not have a externalid (TMDB, IMDB, TVDB). As for date added and updating a file. If you do not save metadata with media, the old file gets removed from the database and all images related to that databaseid are deleted. Then the new file gets added to the database with a new id. So items with metadata with media, Emby can reuse the recorded metadata instead of starting over. hhhmmm - ok - I have never used nfo files to date. so - If I was to want to convert to nfo for this backup/restore option, I assume it is much more than just clicking the nfo option in the library config and then doing a metadata refresh ? If it is just that, do they all become 'new' at that time again ? You can test on one item, but after changing this library option and doing a Library Refresh (not a scan) you should see the nfo file with media. If not I would look in the log for that item to see if there were errors.
vaise 340 Posted April 23, 2020 Posted April 23, 2020 Cool. I have done all my smaller libraries (4k media, 3d media etc etc) and that has all worked fine. excuse my ignorence, but where was this stuff before ? The database ?
Happy2Play 9782 Posted April 23, 2020 Posted April 23, 2020 (edited) Cool. I have done all my smaller libraries (4k media, 3d media etc etc) and that has all worked fine. excuse my ignorence, but where was this stuff before ? The database ? Yes metadata was only in the database and image are saved by some database id in the \metadata\library folder. That is why so many users have issues when there are slight database changes. Items get purged and re-added with new database ids. Edited April 23, 2020 by Happy2Play
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now