Problem with library.db migration

[frandan 0]

Hi to everyone.

I moved my media to a new server and i followed the migration guide linked on github.

On my old server was running emby 4.3.1.0, the same that i installed on the new one.

I made a manual backup and it was all ok until i tried to restore the usaerdatas table from the old library db to the new one.

The problem is that the old db has more columns that the new one in the userdatas table (14 vs 12)

There is a way to fix this problem?
Thanks in advance for your answers.

@@Happy2Play

@@arrbee99

@@chef

[Happy2Play 9351]

Ideally you would use the Server Configuration backup plugin to backup users and userdata to be restored into a new database, but requires Premiere.

 

But to the issue, the database table continues to evolve and as you have noticed columns have changed over different versions.  So the only way the database table merge method will work is if you are merging databases of the same version.

 

Since a plugin can do it I would assume there could be a way to manually do it, but I could be wrong.

 

some more discussion here.

https://emby.media/community/index.php?/topic/82674-restore-process-and-get-a-listing-of-watched-movies/

[Luke 39626]

@@frandan what version were you running before?

[frandan 0]

The two dashboards (the old one and the new one) show the same version : 4.3.1.0 but the tables on the two server are different

[Luke 39626]

The new database doesn't use those two columns anymore so you can ignore them if you're writing your own sql code to migrate the data. Does that answer your question?

[frandan 0]

I tried with this query "REPLACE INTO librarynew.userdatas SELECT UserDataKeyId, userId, rating, played, playCount, isFavorite, playbackPositionTicks, LastPlayedDateInt, AudioStreamIndex, SubtitleStreamIndex, RatingLastModified, PlaystateLastModified FROM userdatas" but i obtained an error on column "RatingLastModified" that in the new database must be "NOT NULL" and in the old one not.

 

Upgrade: I filled Ratinglastmodified and PlaystateLastModified column with not null values but when i try the query i obtain a "FOREIGN KEY constraint failed" error

  

Edited February 22, 2020 by frandan

[Luke 39626]

What platform are you running emby server on? I'm guessing you don't have your old version installation anymore, right?

[frandan 0]

Excuse me for the late answer.

My emby server is running on windows 10 pro 64bit.

No i don't have my old installation more: i have only the backup of library db.

[Luke 39626]

Are you still having an issue with the database?

[garrettjones331 1]

Hope this isn't considered a necro, but I'm having the same exact issue as frandan.  I got up to the same point as him and I'm getting the "FOREIGN KEY constraint failed error" as well in DB Browser.  Don't have the old installation available, just the library.db

[Luke 39626]

Ok, I would suggest only migrating configuration and not the entire library database.

[garrettjones331 1]

So no way to recover "previously watched" data?

[Luke 39626]

Well yes but it would have to be done surgically via extraction from the database file. @@cayars

[Carlo 4543]

Can I ask a dumb question?  Sorry gonna ask anyway. LOL

 

Is there a reason you can't just use the older database?  I'm just wondering why you are trying to migrate the data from one Emby database to another Emby database???

 

Help us understand the need to do this so we can better help you.

Carlo

[garrettjones331 1]

I'm not sure. I was just following the "manual backup method" described here, which specifically mentions not copying it over.  https://support.emby.media/support/solutions/articles/44001159936-backup

[Happy2Play 9351]

I'm not sure. I was just following the "manual backup method" described here, which specifically mentions not copying it over.  https://support.emby.media/support/solutions/articles/44001159936-backup

 

Have you tried using the database itself without attempting to migrate from OLD to NEW tables?

 

With all the reported issues either a proper solution needs to added to the wiki or a note about database version updates and this issue.

[Carlo 4543]

I'm not sure. I was just following the "manual backup method" described here, which specifically mentions not copying it over.  https://support.emby.media/support/solutions/articles/44001159936-backup

Are you migrating to a different platform or different machine with different library setups or similar?

If you want a quick hand with this shoot me a PM and we can setup a TeamViewer session and try this the easy way.

 

Carlo

[garrettjones331 1]

Using the old library seems to have brought all the old data over.  My library directories got messed up (in emby's settings, not the directories themselves), but removing and readding them seems to have fixed it.

[Luke 39626]

Thanks for the feedback !

[Carlo 4543]

Using the old library seems to have brought all the old data over.  My library directories got messed up (in emby's settings, not the directories themselves), but removing and readding them seems to have fixed it.

Basically I think that was the problem the old wiki article tried to avoid, but as you see it isn't that hard to fix.  Make sure to go back and check user rights/access to the new libraries as you removed/added them back.  Other than that you should be good to go.

[garrettjones331 1]

Another issue I've run into in the past few days.  Since I moved from a windows to a linux server install, all of my directories changed and none of my "people" or "studio" images were showing up (As my old database was based on the windows installation and thus the windows directories).  I was able to fix the people issue by updating the MediaItems table in the database and replacing all of the "\" occurrences with "/" for all of the people paths.  Now all my people images are showing up fine. 

 

I'm still having an issue with the studio images.  The only reference I can seem to see in the database is in the TypedBaseItems table, but that one has old paths for EVERYTHING (including people and movies).  Any advice?

[Carlo 4543]

Have you thought of just doing a refresh metadata job during scanning and allowing Emby to pull down and fix them on it's own?

If you want to try this, shut down emby and backup/copy the databases in case you want to easily revert back.

[garrettjones331 1]

How would I do that specifically for stuff that’s stored in the metadata folder, like people/studios? I’d prefer to do that if it’s a possibility so I don’t have to worry about incorrect directories in the library.db that was carried over from my windows install

[Carlo 4543]

Just have Emby refresh the data.  It will find the data isn't there and pull it down again.  Which method is better probably has more to do with how much you may have customized the data or how many videos you have that would need to get refreshed.

[Happy2Play 9351]

Another issue I've run into in the past few days.  Since I moved from a windows to a linux server install, all of my directories changed and none of my "people" or "studio" images were showing up (As my old database was based on the windows installation and thus the windows directories).  I was able to fix the people issue by updating the MediaItems table in the database and replacing all of the "\" occurrences with "/" for all of the people paths.  Now all my people images are showing up fine. 

 

I'm still having an issue with the studio images.  The only reference I can seem to see in the database is in the TypedBaseItems table, but that one has old paths for EVERYTHING (including people and movies).  Any advice?

"TypedBaseItems" was used prior to v4, that table was migrated to "MediaItems", but you will have to potentially manual update changed paths for these items in the Images column as they would be pointing to path that no longer exists.

 

You would need to do something similar to this in reference to Images column.  Assuming the items exist in that path.

https://emby.media/community/index.php?/topic/63573-36051-metadata-people-empty/page-2&do=findComment&comment=631133