HowTo: Centralize Your XBMC Library with MySQL

[dheg]

I don't know what I'm doing wrong here...

 

My set up is as follows:

• XBMC center running on the entertainment room on a dedicated Win7 machine
  
• Fresh installation, also running on Win7, on my desktop
  

 

Before starting I ran a backup from the dedicated xbmc box. I backed up twice, first into separated files (jpg, tbn and nfo files) and another one into a single file, just in case.

 

I then installed mySQL as per post 1 following the instructions up to step 2. I had no issues here. the only major difference is that instead of installing into the cache drive (or any other disk), I installed it in an SSD drive mounted through SNAP. The path is /mnt/disk/apps. I have this set up to work with crashplan without issues.

 

So then I installed XBMC at my desktop and added the sources, and this is where I can't get it to work. The steps I have followed are:

• select the source
  
• set the contents type (movies, IMDB scrapper)
  
• press ok
  

 

I then get a message whether I want to update the library, press ok... and nothing happens, no more messages, libray is not updated and the sql library is not written to.

 

I also tried to import the library, and everything looks fine, the progress bar runs up to 100% without errors, but the library isn't updated. I don't know anything (almost) about mySQL, but I'm suspecting is a write-access issue.

 

What am I doing wrong? Any help would be appreciated

 

[Tight_wad]

Last time I try to do this, I had some issues and decide to hold off for a while.  But since Eden will be out soon, and the nightlies for it are using the  <pathsubstitution> in the advancedsettings I thought I might give MySQL a new try.

 

I have an extra 30 gig SSD that I am doing nothing with.  I was thinking that I might add it to my unRAID, and use it for a Centralized database and thumb art for XBMC.  I don't have any extra room inside my case for a regular drive, but could easily fit a little 2.5 SSD. 

 

To do this, which way would be the best to add the SSD to my unRAID,  as cache drive (without using the mover script), as just another disk in the array, or using the S.N.A.P. application to make it an accessible drive that is outside of my array?

[Swixxy]

Last time I try to do this, I had some issues and decide to hold off for a while.  But since Eden will be out soon, and the nightlies for it are using the  <pathsubstitution> in the advancedsettings I thought I might give MySQL a new try.

 

I have an extra 30 gig SSD that I am doing nothing with.  I was thinking that I might add it to my unRAID, and use it for a Centralized database and thumb art for XBMC.  I don't have any extra room inside my case for a regular drive, but could easily fit a little 2.5 SSD. 

 

To do this, which way would be the best to add the SSD to my unRAID,  as cache drive (without using the mover script), as just another disk in the array, or using the S.N.A.P. application to make it an accessible drive that is outside of my array?

 

using it as a drive outside of the array seems like the best choice, if it was inside the array the parity drive writes would slow it down, making the fact its an SSD fairly pointless.

 

Your best bet is to have it as a drive outside the array, and create a cron job to backup the database to your array, backing up thumbnails seems like a waste of space as they are fairly easy to recover, but obviously which shows you've watched/haven't watched etc isn't.

[Tight_wad]

Thanks for the reply Swixxy!

 

I was think, which is usually a bad thing, but here goes.  Could I set up my SSD with 2 partitions and use one for the MySQL database and thumbs folder, and then use the other partition for swapfile?  Seems like it would work if you can have a swapfile on the usb flash drive (not that that is the best place for thou).

 

Another question that I have is what format should I do the SSD in?  NTFS, Fat32, or rieserfs?

 

I appreciate any help.

[inh]

Great guide, thank you!

[Swixxy]

Thanks for the reply Swixxy!

 

I was think, which is usually a bad thing, but here goes.  Could I set up my SSD with 2 partitions and use one for the MySQL database and thumbs folder, and then use the other partition for swapfile?  Seems like it would work if you can have a swapfile on the usb flash drive (not that that is the best place for thou).

 

Another question that I have is what format should I do the SSD in?  NTFS, Fat32, or rieserfs?

 

I appreciate any help.

 

That wouldn't be a problem to do, however note that unless you have very memory intensive addons running, or a very low RAM set then a swap file will hardly ever be used and as such isn't really worth it all that much.

 

I'm also pretty certain it should be ReiserFS however i'm not 100%, best to make a thread about it before proceeding (as most people will only check this thread for xbmc related things)

[WeeboTech]

I would refrain from putting swap on your usb flash.

 

1. it will hardly be used, however if it does, it will diminish the life of the flash key.

2. Speed is max 30mb/s but probably more like 12mb/s.

 

You can make a swap partition on your SSD or a swap file and mount it via loopback. (Pretty easy to do).

 

years ago with a Roku we used to put a swap file on a compact flash.

Around the 2 year mark they would usually fail.

You don't want to be writing to the USB key more then needed.

Everytime you start or stop the array the superblock.dat file is updated.

While they don't fail that often, there are numerous reports of failing USB keys.

 

Speed on an SSD will be significantly faster then a magnetic media drive or a usb key.

 

keep in mind, if you use the SSD as a cache drive, then make a swap file on it, you have to do a swapoff -a first before unmounting the ssd cache drive. I.E. if there are processes busy on the cache drive, it could prevent the array from stopping.

 

In this particular case, I might just create a 4GB swap partition and be done with it so that it does not hold the array from stopping.

 

If you mount the SSD as a apps or local drive, then it probably will not prevent the array from stopping and you can do swapfile via loopback.

 

[dannycorker]

I seem to be getting stuck at the first hurdle  I use Putty to telnet into the machine, after installing mysql and the swapfile, but after running the first command (mysql -u root -p) all I get is an error: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:ES). I get the same error with whatever word I try instead of root as well

 

EDIT: Nevermind, a 3rd reinstall seemed to fix it...

[ThEgOg]

so from reading the thread i take it if you don't use a cache drive it isn't advisable to put the swapfile anywhere else, disk 1 or 2 etc??

 

if not and as i don't have a cache, is there any other way round it?i was hoping to set up mysql to use with my 3 xbmc boxes,

[naxiand]

i follwer the quide and seems everything is working but it is slooooow(it can take 2 min for every move i make)....mysql is installed to cahe drive 7200rpm on a HP microserver

 

I have installed mysql on a windows machine abd it works fast....so its something wrong with mysql unraid installation....any help?

[naxiand]

Roll back to 10.1 from beta 3 xbmc, install again over beta 3 and worked:-)

 

Sent from my Optimus 2X using Tapatalk

[aptalca]

Did you have the <name> field in your advancedsettings.xml file?

 

10.1 (Dharma) needed that, but Eden (11) no longer needs it, and in fact it doesn't play well when it is specified.

 

Eden is able to create the databases itself. So you don't have to create the video and music databases either.

 

Most of the guides out there are written for Dharma and when I did the same thing, xbmc would not even start up. (I didn't wait too long before troubleshooting) I think the only up to date source is xbmc's wiki page.

 

So try removing the <name> fields from both the video and music sections of the advancedsettings.xml file, it might fix it.

[naxiand]

this is my settings works now...is it correct?

 

<advancedsettings>
    <videodatabase>
        <type>mysql</type>
        <host>192.168.2.9</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>xbmc_video</name>
    </videodatabase> 

    <musicdatabase>
        <type>mysql</type>
        <host>192.168.2.9</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
        <name>xbmc_music</name>
    </musicdatabase>
</advancedsettings>

[aptalca]

It is correct for xbmc 10.1 (Dharma)

 

But if you want to use the latest xbmc (Eden or 11), remove the following two lines:

 

<name>xbmc_video</name>
<name>xbmc_music</name>

 

 

[naxiand]

does this work for you also with rc2 or rc1? mine is broken...had to roll back to eden beta2 to work again

[aptalca]

I haven't tried rc1 or 2 yet, I am still on beta 2 so far. (Haven't had any problems with beta 2, so I am waiting for final stable)

[naxiand]

Dont try it... I found out a lot of users at xbmc forum have the same problem with broken sql database

 

Sent from my Optimus 2X using Tapatalk

[aptalca]

Ah, thanks for the heads up. I actually almost installed rc2 last night, but didn't have the time.

[EdgarWallace]

I have RC2 on all Macs and PCs (Win7 and Linux) and I can recommend it. MySQL is also running extremely well. You just need to make sure that you remove the <name> entry in the advancedsettings.

[naxiand]

No it doesnt :-P look at your database and your ll see 2 new databases... Myvideo60 and one more... (i am not in front on my pc right now)

 

Sent from my Optimus 2X using Tapatalk

 

[aptalca]

Just to clarify, Eden puts the version number in the database name. For instance in beta 2, the database was named MyVideos58. When I upgraded to RC2, it created a new database named MyVideos60 and moved the old content over.

 

I didn't notice any changes from the user's perspective (until I checked the mysql database records)

 

and I can confirm RC2 works with mysql with no problems

[naxiand]

exactly...and if you dont upgrade all machines to the new version ,then its problem...

[eroz]

Thanks finally got it working.  I'm running unRaid4.7 and Eden RC2.  I had to delete the database I had initially created in mySQL and let xmbc create it.  It ended up creating "myVideos60".

 

Now my zotac in the bedroom and my desktop are in sync.

[Tight_wad]

I finally did mine over the weekend.

 

  Originally I was going to install a 30gig ssd as cache last week.  That ended up being a total nightmare!  UnRAID was recognizing it as a 32gig instead of 30gig that it is.  I didn't realize it until the next day when I was going to do the mysql.  I was having drive errors in my syslog every 3 seconds and it also showed HPA.  Since the forum was down at that time, I had no clue what to do about it.  So, I decided to scrape the ssd cache idea, and just use one of my regular drives and not cache.  Oh well enough whining.

 

Everything went fine over the weekend.  Using unRAID 4.7, Openelec ion 64 build r9963 on 2 of my zboxes, and windows eden rc 2. 

I didn't follow all the directions at the beginning of this thread completely, from what I have read, they are a little dated.  Fine for Dharma, but not for the Eden builds.  I totally skip for now the swap file.  Not sure if I will need it or not.  In all the messing around that I have done so far, I would say that I won't.

 

I did this

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

 

 

Didn't do this

CREATE database xbmc_video;
CREATE database xbmc_music;

 

Used this for my advancedsettings.xml, first with Windows computer.

 

<videodatabase>
        <type>mysql</type>
        <host>***.***.***.***</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
    </videodatabase>
    <pathsubstitution>
         <substitute>
	<from>special://masterprofile/sources.xml</from>
	<to>smb://***.***.***.***/xbmc/userdata/sources.xml/</to>
        </substitute>
<substitute>
	<from>special://masterprofile/Thumbnails</from>
	<to>smb://***.***.***.***/xbmc/userdata/thumbnails/</to>
        </substitute>
</pathsubstitution>

 

I then copied my Thumbnails folder and my sources.xml to the unRAID and then did my import on my windows xbmc from the export file that I had created.  Everything good, mysql database populated just fine.

Don't do it this way.  Let the database make them.  It ended up giving me some errors on my Window XBMC and was the cause of it being so much slower than my Openelec.  Did cure all my issues but some.

 

Tested with my Windows computer, XBMC rc 2.

 

Here is were I just about said forget it again.  Slow.  Extremely slow.  About 30 seconds to enter my movies library.  This is about the same as the last time I tried doing this.  The slowness is still the Thumbnails, pathsubstitution didn't seem to be helping me.  I removed the pathsubstitution for the Thumbnails, used local, and no more lag.

 

I did try added the index's, but it had no effect that I could notice.  I have since read that it is also outdated for Eden builds.

 

use MyVideos60; 
ALTER TABLE movie ADD INDEX ix_idFile(idFile); 
ALTER TABLE movie ADD INDEX idMovie(idMovie);
ALTER TABLE movie ADD INDEX idFile(idFile); 

 

I decided to proceed with the openelec's and see how the speed was with them.  Using the same pathsubstitution, I found them to be faster for movie library access.  Only about 18 seconds.  That was a big improvement, but still too slow.

 

Then, I got to thinking.  Why am I trying to share everything that is in the Thumbnails folder when the only thing I want to share is actually in the Thumbnails/video folder.  I decided to try the pathsubstitution directly on the actual video folder itself.  I changed the pathsubstitution to this

 

<pathsubstitution>
         <substitute>
	<from>special://masterprofile/sources.xml/</from>
	<to>smb://***.***.***.***/xbmc/userdata/sources.xml/</to>
        </substitute>
<substitute>
	<from>special://masterprofile/Thumbnails/Video/</from>
	<to>smb://***.***.***.***/xbmc/userdata/thumbnails/video/</to>
        </substitute>
</pathsubstitution>

 

Now this really improved the access speed.  Now it only takes 7 seconds to enter the movie library with my openelec's.  It still not as fast as having the thumbnails local, but it is now at a speed I can live with.  I can't say the same for the windows computer, it still takes about 20 seconds to load up.  But since it is only for my backend work, it's not that big a deal.

 

That's it, I'm very happy with the end results.  It's definitely going to lessen my work load being able to have instant updated movie and TVshow libraries.  Another good thing, though all my speed testing was done bringing up the complete movie library, we seldom do this.  Normally we use the genre or the recently added, and those load up almost instantly!

 

Might try this with a cache drive some time again, might help the write speed when stopping a movie.  That is the only thing that is slower than I expected, but not too slow, it might not be worth the extra efforts.

[aptalca]

If you want, you can have the thumbs local on all machines, but still in sync

 

I use dropbox (Granted it's all on windows machines) to sync the thumbs folder. Instead of using pathsubstitution, I used a symlink that points to the dropbox folder on each computer.

 

Not sure if you can install dropbox on openelec though, but it looks like some people have done it: http://openelec.tv/forum/65-storage/23455-solved-autostart-dropbox#23455

 

EDIT: I suggest you also look into adding skip-name-resolve to your mysql my.cnf if you haven't done it already. It didn't make a difference for me (my entry into library is already very quick) but some people claimed it worked for them