XBMC MySQL Library

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
nanouk76 Offline
Junior Member
Posts: 41
Joined: Aug 2011
Reputation: 1
Post: #16
(2012-05-11 09:46)speedway Wrote:  ... Booting XBMC bring all movies and TV-series back, but no thumbs/Fanart any more.
...
Noticed that too with a nightly from the nathan renniewaldock xbmc nightly PPA. Tried that to try and solve a problem (not being able to play ISO's and VIDEO_TS folders properly). Actually XBMC started downloading new thumbs and fanart. Annoying when you have spent quite some time customizing themSad . I reverted to Eden even though the original problem had been solved. Any thoughts?

ASUS Motherboard M5A78L-M/USB3 // 8 GB RAM
Nvidia GeForce GT 430 graphics card // 2 GB RAM
Ubuntu upgraded to 12.04 LTS
XBMC v.11 Eden
find quote
speedway Offline
Senior Member
Posts: 142
Joined: Feb 2005
Reputation: 0
Location: Belgium
Post: #17
(2012-05-11 10:06)Memphiz Wrote:  Let XBMC update the DB to v63 don't mess around with it by yourself.

This is what I did in the first please, but something went wrong when updating from MyVideos60 to MyVideos63 (cfr.log
http://xbmclogs.com/show.php?id=2362&hash=36497117 (psswd = debug).

Intel NUC BOXD34010WYK2 / Intel mSata 525 30GB / Kingston 8 GB / Intel 7260hmw
find quote
speedway Offline
Senior Member
Posts: 142
Joined: Feb 2005
Reputation: 0
Location: Belgium
Post: #18
(2012-05-11 17:59)speedway Wrote:  
(2012-05-11 10:06)Memphiz Wrote:  Let XBMC update the DB to v63 don't mess around with it by yourself.

This is what I did in the first please, but something went wrong when updating from MyVideos60 to MyVideos63 (cfr.log
http://xbmclogs.com/show.php?id=2362&hash=36497117 (psswd = debug).

Just repeated my actions but with Eden and everything goes well. Afterwards I tried via XBMC nightly build to update MyVideos60 to MyVideo63 and again troubles (no movies/series/fanart/thumbs).

18:53:21 T:6600 ERROR: Unable to open database: MyVideos63 [1049](Unknown database 'MyVideos63')
18:53:21 T:6600 ERROR: Unable to open database: MyVideos62 [1049](Unknown database 'MyVideos62')
18:53:21 T:6600 ERROR: Unable to open database: MyVideos61 [1049](Unknown database 'MyVideos61')
18:53:21 T:6600 NOTICE: Old database found - updating from version 60 to 63
18:53:22 T:692 ERROR: CRemoteControl::Connect - failed to connect
18:53:31 T:6600 NOTICE: Attempting to update the database MyVideos63 from version 60 to 63
18:53:32 T:6600 ERROR: SQL: The table does not exist
Query: INSERT INTO seasons (idShow,season) SELECT DISTINCT idShow,c12 FROM episodeview WHERE idShow=1
18:53:32 T:6600 ERROR: Error attempting to update the database version!
18:53:32 T:6600 ERROR: Can't update the database MyVideos63 from version 60 to 63

I think something is wrong with the update mechanisme ... hopefuly I will be fixed Confused
Only solution is to go back again to Eden, restore, export within xbmc my liberary.
Do a complete new install with nightly build, let it create MyVideos63 and import my liberary via XBMC .... Sad.

Intel NUC BOXD34010WYK2 / Intel mSata 525 30GB / Kingston 8 GB / Intel 7260hmw
(This post was last modified: 2012-05-11 19:04 by speedway.)
find quote
djbarney Offline
Junior Member
Posts: 42
Joined: Apr 2011
Reputation: 0
Post: #19
I'm using the latest Aeon MQ3 skin in conjunction with the may 10th build (didnt have much time to update today) but my fanart, thumbnails etc. are still working as they should on all my connected devices using the pathsubstitution.
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,674
Joined: Oct 2003
Reputation: 169
Post: #20
There were a number of small faults in the nightlies (the one above is one such example). As art is stored in the db now, this is why that will screw up with a db update gone bad.

I believe it should be all good now, but ofcourse if you have a db update go bad, then you may need to get rid of one or two db's in the meantime. There's nothing in v61->v63 that is critical (other than any changes you've made to watched status and the like) so you can drop them one by one starting with 64 going back until you get things going again.

Cheers,
Jonathan

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
speedway Offline
Senior Member
Posts: 142
Joined: Feb 2005
Reputation: 0
Location: Belgium
Post: #21
(2012-05-12 04:38)jmarshall Wrote:  There were a number of small faults in the nightlies (the one above is one such example). As art is stored in the db now, this is why that will screw up with a db update gone bad.

I believe it should be all good now, but ofcourse if you have a db update go bad, then you may need to get rid of one or two db's in the meantime. There's nothing in v61->v63 that is critical (other than any changes you've made to watched status and the like) so you can drop them one by one starting with 64 going back until you get things going again.

Cheers,
Jonathan

By 'art' do you mean thumbs & fanart ? So pathsubstitution won't be necessary anymore?

Intel NUC BOXD34010WYK2 / Intel mSata 525 30GB / Kingston 8 GB / Intel 7260hmw
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,674
Joined: Oct 2003
Reputation: 169
Post: #22
Correct - path substitution is just slowing things down for Videos at this point. You're welcome to keep the Music subfolder shared until they're done as well.

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
(This post was last modified: 2012-05-13 03:06 by jmarshall.)
find quote
zebraitis Offline
Donor
Posts: 358
Joined: Jan 2008
Reputation: 6
Location: NorthAm
Post: #23
(2012-05-12 04:38)jmarshall Wrote:  I believe it should be all good now...

Cheers,
Jonathan


Well, I think this thread may just explain my last three-day learning curve experience. Confused

Thank goodness for an understanding wife.


I tried to make this all happen using the Eden 11 release... apparently, I should start with a more recent nightly.

Maybe someone may wish to state on the wiki that the instructions may not work? Especially since they were made specifically for the Eden release?

Would have saved me a lot of time.



BTW .. .is this where the Monty Pythonesque knight comes out with the rubber chicken, hits me over the head, and suggests " Start Again." ?

And for those unsure of what I mean : http://www.youtube.com/watch?v=MTt-ewgKm5A

v.
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,674
Joined: Oct 2003
Reputation: 169
Post: #24
zebraitis: You'll notice this thread changed from Eden-specific issues to Nightlies around post 6.

Either way, no instructions in this thread or on the wiki are any different than what you'd do in Eden.

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
speedway Offline
Senior Member
Posts: 142
Joined: Feb 2005
Reputation: 0
Location: Belgium
Post: #25
I decided to build my library from scratch (which is faster than troubleshouting in going from Eden to a nightly build) and want to benefit from MySQL to store my art.
I've started to build my new library via advancedsettings.xml WITH path substitution :-(
It seems like none of the art is stored in my database (which is only 8.5 MB large, while my art is approx. 600 MB).
This is confirmed when I delete the <pathsubsistution> and start XBMC again, no art is shown.
Putting the tag back again, gives me back my art (indeed a little bit slow, but acceptable to live with it :-) )
Any idea where I can find steps to point XBMC to store my art in MySQL?

Edit 1: I've found the art table and it contains only hyperlinks to my scraper -> ?

Intel NUC BOXD34010WYK2 / Intel mSata 525 30GB / Kingston 8 GB / Intel 7260hmw
(This post was last modified: 2012-05-14 08:49 by speedway.)
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,674
Joined: Oct 2003
Reputation: 169
Post: #26
The actual images aren't stored in the mysql database, however the links are. (See the art table). Thus, all clients know exactly where to get art, so can store a nice, fast local cache.

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
zebraitis Offline
Donor
Posts: 358
Joined: Jan 2008
Reputation: 6
Location: NorthAm
Post: #27
(2012-05-13 12:02)jmarshall Wrote:  The actual images aren't stored in the mysql database, however the links are. (See the art table). Thus, all clients know exactly where to get art, so can store a nice, fast local cache.

Yessssss... Interesting...

OK Jonathan,

Thanks very much for the reply. And a quick thanks for all the efforts over the years.

For me, this thread is important, as my boggle was thumbnails. And you (XBMC Team) may have changed this to be easier.

I have read the wiki, I scanned the forums, my google-foo is strong. In short, I believe that I have resolved my challanges, but I still have a question:


QUESTION: In the thread above, you say that thumbs are being kept in the database... But then you say that it's not really thumbs, but it's links...

Can you explain what you mean by that?

Does it actually contain a thumbnail... or does it just link to my thumb "somewhere else".



I ask, as I have taken great steps to get this to work, overcoming several "special" win7 related challanges (as listed below) and one self-inflicted one as well.

I want to be sure that the thumbs will be in the "right" place when I have other local XBMC installs point to it. (I have 4 XBMC boxes... so shared database & thumbs is important... to me.)

Bottom line (literally, down at #52 below) I saw that there were no thumbs being stored where I expected them to be.

(...and what about the actor thumbs that still seem to be local?)


=======================================

For those curious of what I saw in Win7 with MySQL, please read on, and apologies in advance for the length of the post.

- - -

1) Build a 40TB server for home storage (win7 Ult 32-bit).

2) load MySQL 5.5 on home server.

(tip: Reinstall fails unless you remove hidden MySQL subdirectories)

(MySQL related Bug: skip-name-resolve tweak for C:\Program Files\MySQL\MySQL Server 5.5\my.ini fails in win7. Causes MySQL to fail - Command client will not open. Some problem with "localhost" being named rather than IP Address in MySQL setup.)

3) Create firewall TCP and UDP inbound and outbound port 3306 on MySQL machine

4) Load 13-May build for Win32

5) Import Partial AdvancedSettings.xml (to address "watched" status on reimport)

-----------------------------------

<advancedsettings>

<!-- This should force smaller picture size -->
<useddsfanart>true</useddsfanart>

<!-- This ensures that a movie is not marked as watched until 90% of film has been played -->
<video>
<playcountminimumpercent>90</playcountminimumpercent>
</video>

<!-- This increases the number of recently added and also imports watched state -->
<videolibrary>
<recentlyaddeditems>100</recentlyaddeditems>
<importwatchedstate>true</importwatchedstate>
</videolibrary>

<!-- This creates an unlimited buffer for internet based streaming -->
<network>
<cachemembuffersize>0</cachemembuffersize>
</network>

</advancedsettings>

--------------------------------------------

6) Scan films and TV

(Added seven SMB shared directories, 2300+ films, 90 tv series. Skipped music import / export as it is not yet done per this thread. Also note: TV Watched Status did not import, and it also did not take preference for local artwork)

7) Export Films and TV to file.

(Note: Films exported, but TV Series did not export... drag. Nor did it capure watched status of the TV Series)

8) Exit XBMC

9) Unplug the ethernet cable from the XBMC Win7 NIC

10) restart XBMC, run a "clean" to get rid of my existing database content.

11) Exit XBMC, plug ethernet cable back in.

12) Apply additional AdvancedSettings.xml section, defining path substitution, creating MySQL DB's, and defining the location of the MySQL location:

--------------------------------------------

<!-- This defines where the shared thumbnails source is located -->
<pathsubstitution>
<substitute>
<from>special://masterprofile/Thumbnails/Video/</from>
<to>smb://ZebraitisServ2/xbmc_thumb_cache/Video/</to>
</substitute>

<substitute>
<from>special://masterprofile/Thumbnails/Music/</from>
<to>smb://ZebraitisServ2/xbmc_thumb_cache/Music/</to>
</substitute>
</pathsubstitution>

<!-- This creates my main databases in MY SQL -->
<videodatabase>
<type>mysql</type>
<host>192.168.015.014</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</videodatabase>

<musicdatabase>
<type>mysql</type>
<host>192.168.015.014</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</musicdatabase>

--------------------------------------------

13) reboot... just to be sure (don't yet start XBMC)

14) Since my substitution only addresses "Video" and "music", leave the thumbnails subdirectory in AppData/roaming/xbmc/userdata , but delete both the Video and Music subdirectory withing the Thumbnails subdirectory.

15) Create both those subdirectories in ZebraitisServ2/xbmc_thumb_cache/

16) verify I have network access to those two new subdirectories by checking from the XBMC machine (via "paste" of address into Int Exp)

!!! SURPRISE !!! I did not !!!

There is a "persistance of credentials feature / bug" in Win7 that makes access to a network drive wonky.

If you are so challanged, you can find the answer at http://www.w7forums.com/windows-7-does-n...621p2.html in a post by Redhotorange.

17) Restart XBMC

18) Watch XBMC just hang at the splash screen...

19) Check to see if DB's are created in MySQL... they are not.

20) Kill the splash screen [ctrl-alt-del]

21) Add username and password to <pathsubstitution> line in advancedsettings.xml

22) again remove videos and music from thumbnails

23) restart xbmc

24) Watch XBMC just hang at the splash screen...

25) Check to see if DB's are created in MySQL... they are not.

26) Kill the splash screen [ctrl-alt-del]

27) However, THIS TIME there is information in the shared drive "videos" and "music" directory. (newly created subdirectories)

28) Let it run AGAIN, this time being VERY patient...

29) got past the splash screen... wallpaper displayed.. finally a menu.

This time the subdirectories were created again, but no databases created in MySQL.

--- NOTE: AT THIS POINT, I HAVE RESOLVED THE THUMBNAILS SUBSTITUTION RELOCATION ISSUE, DUE TO WIN7 PERMISSIONS ---
(oddly, I resolved an issue that may no longer matter in the new DB Schema... but a win is a win)


30) look VERY CAREFULLY at advancedsettings.xml

31) could a "zero" ( "0" ) in the IP Address be a problem? Let's find out...

Change 192.168.015.014 to 192.168.15.14

32) restart XBMC

33) XBMC starts almost immediately. Definate improvement!!!

SO: DON"T PUT "0" in the IP Address as a leading character.

34) However, there is no "video" or "Music" DB in mySQL. Maybe I need to add content?

35) "Import video library" information from saved single file..... there is a long pause...

36) system seems locked up. XBMC not responding pop-up box... (and still no created DB)

37) Wild Guess: Maybe the firewall needs to be inbound only on port 3306... Make that change to remove the outbound port rule on the MySQL server host firewall

38) restart XBMC

39) nope... still no databases created.

40) Start up debugging to create an XBMC.log, try to import video library... review teh log file...

41) saw a lot of "access denied for user" messages.

42) changed the password in advancedsettings.xml to reflect the password that I used when I created the mySQL install.

43) restart XBMC

44) this time, import directly from my shared drives... (stopped after 10 films). this seemed to work for the thumbnails, BUT... (review the debug log) ... when I check the debug log, again, the DB's have "access denied for user" messages and the database has defaulted back to SQLlite.

But the blocking seems to be happening at XBMC_MAIN (my xbmc win7 box)

45) Just for grins, since I was able to resolve my thumbs issue using the server name, I replaced the IP Address with a server name, and changed the password back to XBMC.

(of course, "clean" the videos again, reboot, etc.)

46) run xbmc

47) check the log...

OH SNAP !!!!

It just dawned on me that I forgot to include this in the MySQL setup:

CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
GRANT ALL ON *.* TO 'xbmc';

Add those two commands at the MySQL command line client...

Change the server name back to an IP Address... make sure that it is teh default password (xbmc)


48) Start XBMC... check MySQL... YES !!! the DB's have been created!

49) Import the video library..... and ... nothing. Locked up again.

50) OK... if Import doesn't work, then how about just a scan from the shared drives. Scan in a couple of films... Check the log file...

LOOK LIKE MYSQL COMMANDS ARE HAPP'NIN' !!! WHOO HOO !!!

51) Let's see if the size of teh DB grows as I continue to scan...

SELECT COUNT(*) from myvideos64.movie;

Yes! The database continues to gow.

...and easy-peasy, in just 7 hours! (Not including building the 40TB server)


52) Wait a minute... there are no files in my shared "videos" subdirectory. Nor is there anything in my local "video" subdirectory...

WHAT MAGIC IS THIS? Confused WHERE ARE THE THUMBNAILS? Confused

I see loads of actor thumbs in the local thumbnails subdirectory.

Should I have substituted the entire thumbnails sub?

Or... is it using "something else"?
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,674
Joined: Oct 2003
Reputation: 169
Post: #28
Look in your MyVideos64.db. You'll notice an art table present. In there, you'll notice the URLs to the art.

This allows each client to know where the original image lies. Thus, they can all pull that image down (it's done when you view it, or in the background thumbloader threads) and store it in their local thumb cache. See Textures12.db for the url <-> hash map, and the Thumbnails folder (0-9/a-f folders) for the images. Anything in the Video/ subfolder can be deleted - they're unused.

No more "which image is this mysterious 120c8e7f.tbn?" "what type of image is it? (JPG/PNG?)" "what media item is it for?". We now know all that.

Note that Music art is still not done.

Cheers,
Jonathan

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote
zebraitis Offline
Donor
Posts: 358
Joined: Jan 2008
Reputation: 6
Location: NorthAm
Post: #29
(2012-05-13 22:32)jmarshall Wrote:  Look in your MyVideos64.db. You'll notice an art table present. In there, you'll notice the URLs to the art.

This allows each client to know where the original image lies. Thus, they can all pull that image down (it's done when you view it, or in the background thumbloader threads) and store it in their local thumb cache. See Textures12.db for the url <-> hash map, and the Thumbnails folder (0-9/a-f folders) for the images. Anything in the Video/ subfolder can be deleted - they're unused.

No more "which image is this mysterious 120c8e7f.tbn?" "what type of image is it? (JPG/PNG?)" "what media item is it for?". We now know all that.

Note that Music art is still not done.

Cheers,
Jonathan


Cool... OK, bear with me, as I am not a DB expert.


So... I believe what you are saying is:

Since I have my movies saved on my shared drives, along with artwork (fanart and poster)....

When I scan the shared drive, XBMC then puts that info into the MyVideos64.db

In a field in that database, there is a URL that links back to that artwork on the shared drive... AND (?) it also makes a local copy into the Thumbnails folder (0-9/a-f folders).

Each machine will still have a local copy of Thumbnails... but no longer have the need for a thumbnails/video

After the initial import, XBMC will reference the local thumbnails folder (0-9/a-f)... but that folder will always check against the URL in MyVideos64.db to ensure that it has the correct latest info.


Q#1: Have I got that right?


So.. when I add a new movie, and do a scan, XBMC will bring in the basic info, and copy that basic info to MyVideos64.db

If I change a fanart or poster for that movie, the copy is written to local thumbs AND ALSO (?) to MyVideos64.db ?


Q#2: Have I got that right?


Q#3: Is there still a need for an export of video library... since there is a copy both local and in MyVideos64.db ? Or... will it still export the files back out to the shared drives (where the movie resides)?


Q#4: What is the impact of losing the MySQL database ? What is a recomended back-up process for it?


(As for music, it looks like that is being correctly saved via substitution, so all is good there until the completion of the DB change)


Again, thanks,

Vincent
find quote
jmarshall Offline
Team-XBMC Developer
Posts: 25,674
Joined: Oct 2003
Reputation: 169
Post: #30
Q1: Not completely, no.

A: When a client fetches movies out of the library, it's thumbnail and fanart properties are set to the URLs that are in the art table (i.e. that might be to a folder.jpg on your server, or to a http:// URL from themoviedb.org etc.).

B: When the thumbloaders or texture loading is done, we first check in Textures*.db to see if that image has been cached locally (to the special://xbmc/Thumbnails folders). If so, we use the local cached version. If not, we cache it and use that then. In addition, at this point we check to see whether the original image has changed or not (eg you copied a new folder.jpg over the old one). If it has, we schedule an update of the cached version (NOTE: This is not done for http:// images. For local images it's done on a 24 hour cycle).

Q2. When you update anything to do with a movie or show, the changes only go into the MyVideos64.db. As the URLs will differ (if you've changed the art) they'll automatically be picked up by the cache as per Q1.

Q3. This is completely separate. A backup of data to separate files will copy your locally cached information onto your server, as well as the .nfo file information. A backup of your library to a single file/folder structure now no longer copies the art, as all you need is the URLs to the art because we can always re-download them.

Q4. Same as today. You lose your database, you've lost your metadata. Recommended backup is to backup to a single file/folder XML periodically.

Now, I've really got to do this in the wiki, as otherwise I'll answer the same things over and over again Wink

Cheers,
Jonathan

Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


[Image: badge.gif]
find quote