Jump to content


Photo

"library.db/userdata/LastPlayedDate" field type changed

backup. restore library

  • Please log in to reply
4 replies to this topic

#1 kolonuk OFFLINE  

kolonuk

    Newbie

  • Members
  • 3 posts
  • Local time: 04:48 PM

Posted 28 February 2019 - 07:31 AM

Hi folks,

 

Short question:

How do I convert the old "LastPlayedDate" datetime field in the "userdata" table in library.db into the newer "LastPlayedDateint" integer field?

 

Long question:

I had emby installed on my FreeNAS for a while, and in December I wanted to update the FreeNAS version and at the same time consolidate my jails into my docker VM on ESX on another server.  Unfortunately, emby in docker for me seemed to cause a lot of DB corruption in library.db about 24 hours after the library had finished being scanned and I started to watch stuff.  Looking in the logs didn't yield anything spectacularly wrong or obvious.  So I deleted it and started again, with no luck - the same corruption.  In the end, I'd recreated/re-downloaded the container about 5 times, set local and remote storage for config (NFS, samba, etc), use another docker vm on a faster/different disk - seems no matter what I did, it still had issues.  I even ran a memtest on the ESX server, which as you may know takes aaaaaages! (during which I had to rely on Netflix and ::shudder:: DVDs!)
 
Long story short, I ended up creating a dedicated emby VM and copied across the config.  At that time, v4 was released, so it did an upgrade on the databases, then I ran a library scan.  After a few days, I decided it was stable enough that I removed the emby jail in FreeNAS and cleaned away all the config files (after making a final backup!).
 
As per the instructions on how to manually backup and restore, I'm now at the point where I'd like to re-import my watched data.  But...  It looks like the upgrade or something else along the way changed the field type on the lastplayeddate from a datetime to an integer in the userdata table.
 
 
So, what is the process of converting the datetime fields into an int?  Is this SQLite or emby specific?  I've exported the old table to an SQL file, so I can apply any formulas or do other processing easily.  In case it matters, I'm a programmer by day, IT enthusiast by night, so I'm OK if the answer is not sugar coated!
 
Actually, I could just set that field to null in my exported file...  What does emby use that field for anyway?  I haven't ever seen a "last played" tag anywhere, and to be honest might be quite nice to actually have it displayed!
 
Thanks in advance!

Edited by kolonuk, 28 February 2019 - 07:35 AM.


#2 PenkethBoy OFFLINE  

PenkethBoy

    Advanced Member

  • Members
  • 3280 posts
  • Local time: 05:48 PM
  • LocationWarrington,UK

Posted 28 February 2019 - 08:55 AM

IIRC the new date is a UNIX date - time from some epoc - 01/01/1970(?) in seconds/ms - cant remember the exact format details

 

But if you have premiere you could use the Backup plugin which makes this easy to do - but maybe your old setup is gone now so my not apply?

 

Or use the trakt plugin?



#3 kolonuk OFFLINE  

kolonuk

    Newbie

  • Members
  • 3 posts
  • Local time: 04:48 PM

Posted 28 February 2019 - 09:43 AM

I have trakt already set up for me, but not my wife.  And yes, I am toying with the idea of a Premier subscription to be able to use the backup plugin and support the devs, but if I do I'll need to move my support away from some other smaller projects, or from my upgrade budget, or my mortgage...

 

Anyway, I experimented and just "Null"ed the last played date in library.db, copied it across and it loaded OK.  I updated the videos I know I'm caught up on and it seems to be fine.  I'll leave it till after a couple of days viewing before declaring victory - my wife's gonna be happy!

 

Still unsure why the date is stored is it's not shown anywhere...


Edited by kolonuk, 28 February 2019 - 09:45 AM.


#4 PenkethBoy OFFLINE  

PenkethBoy

    Advanced Member

  • Members
  • 3280 posts
  • Local time: 05:48 PM
  • LocationWarrington,UK

Posted 28 February 2019 - 10:02 AM

because its used for sorting by "Date Played"



#5 kolonuk OFFLINE  

kolonuk

    Newbie

  • Members
  • 3 posts
  • Local time: 04:48 PM

Posted 28 February 2019 - 10:27 AM

OK, well, I don't use that, so no sweat.  Thanks for your help!







Also tagged with one or more of these keywords: backup., restore, library

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users