HowTo: Centralize Your XBMC Library with MySQL

[skank]

I tried not using the name tag and it still wasn't working for me.  If someone can get it working I would really like to set this up.

 

Did you create the databases on your own?

If yes, dont do that! Let xbmc do it.

Then export db in xbmc and import

[thedude459]

I just put the Frodo RC1 on the media PC.  I am going to try this again.

[momoz]

oh my.. I am experiencing the same issues.

 

Eden works great on my mysql database on unraid.

Frodo on the other hand (RC2) isn't working.  I have done detail debug logging and it is something with our install of mysql.. just haven't figured it out yet.

 

I have followed every guide out there.. and even dumped all databases and let it rebuild a new DB which I think it ends up erring out adding all the tables.

 

One suggestion I haven't tried is some max_connections statement.

 

 

[snag4]

I did a fresh install with 2 XBMC and initially work worked great.  I had to kill power on the xmc boxes and when I went to power them back up, I could no longer see any of my shows.  I looked at mySQL and the tables are gone.  I installed MySQL via unmenu so I wanted to just to a fresh install again so I uncheckd the install at boot, stopped the array, rebooted and now my unraid will not come up.  I had this before and something gets corrupt and I have to rebuild the flash, a issue for another time!  I have mySQL installed on my cache drive with the swapfile feature enabled.  Would the mover script cause issues?  I dont have it installed iwth a "."  at the beginning of the folder??? maybe??

 

I see in the XBMC logs that it cannot find the tables but the DB is created by XBMC.  I have a few questions I hope someone can answer.

 

 

1:  In what directories for you put the advancedsettings.xml?  userdata AND thumbnails?  I just put it in the userdata and it seems to not create the DB so I put it in the thumbnails and bingo but after a reboot the XBMC doesnt show any files available for viewing!  I remove it from userdata, reboot and now I can watch my shows

 

2: After setting up the first XBMC and importing the database from a previous export, I setup the second one without any database or NFS mappings but with the advancedsettings.xml and it magically started working until I rebooted.  What is the procedure to add the additional xbmc into the mix?

 

3: Anyone experiencing why the tables disappear? 

 

TIA, now to get my unraid back up!

[skank]

Mine is working great!

 

[ePro.Dark.Angel]

You got a step by step guide on how you achieved this, i just installed the mySQL plugin from the OP of this thread and I'm at a loss at what to do next. Thanks in advance. ePro.Dark.Angel.

[batfink]

I did a fresh install with 2 XBMC and initially work worked great.  I had to kill power on the xmc boxes and when I went to power them back up, I could no longer see any of my shows.  I looked at mySQL and the tables are gone.

 

Same thing happened to me when I tried this, so keen on knowing what the answer is too !

 

Cheers

[DoeBoye]

Any update on this folks? I had this running perfectly on Eden, but Frodo doesn't seem to want to play nice with our mySql install. I get stalled at the same spot as everyone else. Error in logs shows "Can't connect to MySQL server on..."

 

Grrr.... Was stoked to try the new version out, but may have to revert to Eden...

[BLKMGK]

Have you guys tried connecting to the mySQL server with a regular client to see what things look like and verify userid\password? I have mySQL running in a small VM vs on my unRAID install but I have found that having a client to connect to it with is very handy! I've not got the name of the software I use handy but I'll find and post it when I get a chance...

[DoeBoye]

I've connected via phpMyAdmin to verify and all is good. User xbmc:xbmc logs in without issue. I even granted all privileges for xbmc to the ip address, server name, as well as to 'all'. I've also logged in via command line with xbmc:xbmc, and created and deleted dbs without issue....

 

Is anyone using an older version of mySQL and still having the same problem? I updated the mySQL plugin to the 5.5 version while still on Eden, and I feel there was already some issues with the sharing of watched status etc between xbmc boxes (sometimes a show I watched upstairs wouldn't show as watched in my basement setup...). Could the problem be the new mySQL plugin?

 

DB

 

[EDIT] To clarify, I don't necessarily mean that plugin is broken (Especially since it seems to work perfectly with everything else). I mean Frodo and our 5.5 install may have some incompatibilities?

[DoeBoye]

SUCCESS!!!

 

Turns out the problem was the permissions on the xbmc user. I needed to delete all xbmc users from mySQL, and then re-add the user  'xbmc' with all privileges, and use the wildcard (%) for host.

 

Tip: If you are using phpMyAdmin, just leave the host field blank and it will automatically add the wildcard.

 

Hope this helps!

 

[EDIT] For those importing libraries from a single file: Just click on the saved folder and say ok. The xml file does not show up in the list, so you can't select it. Once you hit ok, it make take awhile for anything to happen. On my system, it was a good 30-40 seconds before it started importing...

 

[EDIT 2] I'm aware this is the recommended process when setting up mySQL. The wiki states:

    Type in: CREATE USER 'xbmc' IDENTIFIED BY 'xbmc'; and press return
    Type in: GRANT ALL ON *.* TO 'xbmc'; and press return 

 

Which I'm sure I did.... But for whatever reason, it didn't work and I started adding the extra users.... Perhaps it just needed to be repeated... Anyhow... If you can't connect to mySQL, delete all your XBMC users from the DB and re-create user xbmc

[DoeBoye]

So just an update. looks like recreating the user was not a fix.  . Just had the problem again. Some more research took me to this post.

 

It seems like it is related to XBMC being up before the connection to mySQL is established. Adding the pause in the boot script slows things down a bit...

 

As per thread, I did the following:

 

In /etc/init/xbmcbuntu-wait.conf, I changed the code from:

 

script
status xbmcbuntu | grep -q "start/running" && exit 0
start xbmcbuntu || true

 

to

script
status xbmcbuntu | grep -q "start/running" && exit 0
sleep 10
start xbmcbuntu || true

 

Both my XBMC players are running on SSDs, and others have hypothesized that it is only occurring on them, but not on regular platter drives... Out of curiosity, those that are experiencing this issue, are you also using an SSD boot drive for XBMC?

 

For the record, I have rebooted each machine several times, and connection always works!....

 

 

[skank]

O issues and yes ssd here

[DoeBoye]

O issues and yes ssd here

Doh! Was sort of hoping all ssd users were having same problem! I hate not knowing for sure if a fix is truly a fix!  . Anyone else with ssd and issues/no issues with mysql connection?

[skank]

Changed smth and now getting this

 

130106 19:25:53 [ERROR] Cannot find or open table MyVideos76/path from

the internal data dictionary of InnoDB though the .frm file for the

table exists. Maybe you have deleted and recreated InnoDB data

files but have forgotten to delete the corresponding .frm files

of InnoDB tables, or you have moved .frm files to another database?

or, the table contains indexes that this version of the engine

doesn't support.

See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

how you can resolve the problem.

 

 

Any help pls

[jbartlett]

I'm using XMBC Eden using MySQL running on my Mac Mini server (since it's always on) with the shares remapped (via advancedsettings.xml) to an XBMC share on UNRAID. Working great with machine security set to "%"

 

Installed Frodo over Eden and no movie information was displayed. I didn't proceed to investigate having heard a rumor that Frodo was removing MySQL support. After catching up on this thread, I may try again.

[JimmyJoe]

I got MySQL all setup on my unRAID and working great syncing my library for 2 windows xbmc clients and 1 openelec running on an Intel NUC.  Life was good.  I bought a second NUC and I have been pulling my hair out all day long.  I am using the same advancedsettings.xml and sources.xml files across all 4 clients. On one of the NUCs I have never seen an issue connecting to MySQL. On the one I just got today, it has connected to MySQL maybe 2-3 times out of 50 reboots and 10 reinstalls. I am using version OpenELEC-Intel.x86_64-2.99.2.

 

This is what I see in the log:

<SNIP>

01:01:44 T:139762704336704 NOTICE: Running database version Addons15

01:01:44 T:139762704336704 DEBUG: Initialize, updating databases...

01:01:44 T:139762704336704 NOTICE: Running database version ViewModes4

01:01:44 T:139762704336704 NOTICE: Running database version Textures13

01:01:44 T:139762704336704 ERROR: Unable to open database: MyMusic32 [2003](Can't connect to MySQL server on '192.168.1.99' (101))

<SNIP>

01:01:44 T:139762704336704 ERROR: Unable to open database: MyVideos75 [2003](Can't connect to MySQL server on '192.168.1.99' (101))

<SNIP>

 

I am using DHCP on both. When I get the above error, I don't see the Movies or TV options on the menu. I can go to files, select a source, browse and playback content just fine.  The only difference between the two NUCs is one has a 32GB SSD and the other is 64GB. I would have ordered exactly the same, but that's what was in stock. The one that is having a problem has the 32GB card installed. Both are Crucial M4.

 

Any ideas?

 

[tw332]

By the way, I created a couple of screencasts demonstrating how fast/slow (you be the judge) the thumbnails appear.  For those who are undecided about undertaking this intimidating project.

 

One when wired:

 

The other when wireless:

 

Please please please don't judge my whacked taste in Movies/TV/Music.  I'm ashamed enough as it is.

 

I realise this post is quite old but what skin are you using in the video? I feel like a change...

[PCRx]

From the top of the comments section:

 

"wtfunbubba 5 months ago

Woops I thought I mentioned that in the description. The skin is Cirrus. I'm rockin' Metropolis now but I? sure miss Cirrus' TV view."

 

[CHBMB]

As information becomes outdated so quickly I thought I'd post how I managed to get everything working for OpenElec 2.99.3 but I don't see any reason why it wouldn't work for any other Frodo release.  I've also attached a few files that I've been using to try and help those that like myself are inexperienced with a lot of the stuff that many users on here are very familiar with.

 

My setup is a UnRaid Server and a Openelec HTPC, to configure everything I used my Windows 7 Netbook. MySql database is located on my cache drive.

 

Step 1. 

Used the MySql 5.5 plugin from here.

http://lime-technology.com/forum/index.php?topic=20603.msg229248#msg229248

and installed it to /mnt/cache/.mysql

 

Step 2.

Then as I've never used MySql before I used a program called HeidiSQL for Windows

http://www.heidisql.com/

Started it up, Selected MySQL (TCP/IP) put my Unraid IP address into Hostname/IP and used root as both user and password, clicked open.

 

Step 3.

Go to Tools select user manager and click to add a new user, username and password both xbmc and from host selected access from everywhere.  Then save changes and exit HeidiSQL.

 

Step 4.

Created, with the help of several other people copies, an advancedsettings.xml and copied to the /userdata folder using Notepad++ from here (Make sure encoding is set to UTF8 without BOM): http://notepad-plus-plus.org/

 

Here's a template of mine.

<advancedsettings>
    <videodatabase>
        <type>mysql</type>
        <host>xxx.xxx.xxx.xxx</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
    </videodatabase>
   
   <musicdatabase>
        <type>mysql</type>
        <host>xxx.xxx.xxx.xxx</host>
        <port>3306</port>
        <user>xbmc</user>
        <pass>xbmc</pass>
    </musicdatabase>

     <videolibrary>
          <importwatchedstate>true</importwatchedstate>
     </videolibrary>  
</advancedsettings>

 

Now I did run into a problem once I restarted my Openelec machine.  As it's on a SSD it boots so quickly the network isn't up and running to access the mysql database.

Found the solution on the Openelec forum and it is to create an autostart.sh file and copy it to the config folder.  It contained:

 

#!/bin/sh
ifconfig eth0 up
ifconfig eth0 PUT XBMC MACHINE IP ADDRESS HERE
route add default gw PUT ROUTER IP ADDRESS HERE

 

Rebooted my openelec machine, added my sources and scanned them to the database and hey presto.

 

Thanks to p1lot and SeeDrs on the Unraid Forum and scsa20 over at Openelec, as well as the XBMC and Openelec Wikis.

 

Like i said this was intended to be a very basic guide for those like myself that have no experience with MySql and it's configuration.

[JimmyJoe]

Now I did run into a problem once I restarted my Openelec machine.  As it's on a SSD it boots so quickly the network isn't up and running to access the mysql database.

Found the solution on the Openelec forum and it is to create an autostart.sh file and copy it to the config folder.  It contained:

 

#!/bin/sh
ifconfig eth0 up
ifconfig eth0 PUT XBMC MACHINE IP ADDRESS HERE
route add default gw PUT ROUTER IP ADDRESS HERE

 

Rebooted my openelec machine, added my sources and scanned them to the database and hey presto.

 

Thanks to p1lot and SeeDrs on the Unraid Forum and scsa20 over at Openelec, as well as the XBMC and Openelec Wikis.

 

Like i said this was intended to be a very basic guide for those like myself that have no experience with MySql and it's configuration.

 

That's the same problem I had and that fixed it for me too.

[chrishorton7]

I wonder if anyone can help - I'm struggling to get this plugin working with XBMC.

 

I have installed the plugin and got MYSQL setup ok, and XBMC user created.

 

The advancedsetting.xml file has been created - using these instructions on both my XBMC machines -

 

http://wiki.xbmc.org/index.php?title=HOW-TO:Sync_multiple_libraries/Setting_up_XBMC

 

I have then tried exporting and importing my library. When I look at MYSQL the video and music databases aren't being created.

 

I've had a look on the plugin log file (attached) and I am getting the following error message:

 

130408 21:47:37 [Warning] IP address '192.168.0.6' could not be resolved: Name or service not known

 

I'm not sure where the IP 192.168.0.6 is coming from. The unraid server is on 192.168.0.2 and this is what in the advancedsettings.xml.

 

Any help would be much appreciated.

Tower.txt

[exist2resist]

I wonder if anyone can help - I'm struggling to get this plugin working with XBMC.

 

I have installed the plugin and got MYSQL setup ok, and XBMC user created.

 

The advancedsetting.xml file has been created - using these instructions on both my XBMC machines -

 

http://wiki.xbmc.org/index.php?title=HOW-TO:Sync_multiple_libraries/Setting_up_XBMC

 

I have then tried exporting and importing my library. When I look at MYSQL the video and music databases aren't being created.

 

I've had a look on the plugin log file (attached) and I am getting the following error message:

 

130408 21:47:37 [Warning] IP address '192.168.0.6' could not be resolved: Name or service not known

 

I'm not sure where the IP 192.168.0.6 is coming from. The unraid server is on 192.168.0.2 and this is what in the advancedsettings.xml.

 

Any help would be much appreciated.

 

Looks like your unRaid server can not hit your XBMC machine, if you're using windows disable the firewall or create an exception to allow the SQL ports in.

Check what the IP of your XBMC machine is, try and ping the x.x.x.2 IP if it corresponds to your XBMC from unRaid.

Also let xbmc build the db for you, do not create it on the server. Also omit the <name></name> tag in the advancedsettings.xml file.

 

[johnodon]

CHBMB,

 

Thank you for the PLG and the tutorila.  Quick question...

 

From this thread:  http://lime-technology.com/forum/index.php?topic=11473.0

 

Do you have this issue?  Are you able to stop your array and shutdown/reboot while SQL is running?

 

Step 1 - Swap File and unRAID Shutdown

 

In order to cleanly shutdown unRAID after installing MySQL, the normal Stop and Reboot on unRAID will not work as it will not stop the MySQL running proceeses. Go to unMenu's Package Manager and activate the Clean Powerdown script. Enable on reboot if available. This way when you need to shutdown, you go into unMenu's User Script and you click on the "Power Down" button.

 

This process requires you to install MySQl server on your unRAID server (described later on). Typically unRAID is stored on a USB thumb drive of limited size. Also unRAID servers do not have large amounts of RAM. The problem is that MySQL can potentially use a lot of memory. To compensate, we can create a swap file for the system to use as additional memory in case you exceed your RAM capacity. BUT creating a swap file on the thumb drive can shorten the lifespan of the thumb drive. Thumb drives have a limited amount of read/writes, that the swap file would use up.

 

So the best method to do this is to create the swap file in your cache drive. To create a swap file go into unMenu and click on Pkg Manager. Scroll down the list and click on the button for "unRAID Swapfile Configuration. Install the package and click on "Configuration variables".

 

Swap File Size (Megabytes): I entered 4096 for mine

 

[harry3001]

I've just finished setting up my library in mysql, but I'm having the problem of it not shutting down unless i manually stop the mysql plugin - has anyone got a more automated way of dealing with it (eg something like how sab, sickbeard etc get stopped by the powerdown script, and autostart again when the server comes back up)?

I need to get a monitor connected to the server so i can see what's happening during shutdown, but it looks like it might be the same as this: http://lime-technology.com/forum/index.php?topic=25015.msg217272#msg217272

 

 

CHBMB,

 

Thank you for the PLG and the tutorila.  Quick question...

 

From this thread:  http://lime-technology.com/forum/index.php?topic=11473.0

 

Do you have this issue?  Are you able to stop your array and shutdown/reboot while SQL is running?

 

Step 1 - Swap File and unRAID Shutdown

 

In order to cleanly shutdown unRAID after installing MySQL, the normal Stop and Reboot on unRAID will not work as it will not stop the MySQL running proceeses. Go to unMenu's Package Manager and activate the Clean Powerdown script. Enable on reboot if available. This way when you need to shutdown, you go into unMenu's User Script and you click on the "Power Down" button.

 

This process requires you to install MySQl server on your unRAID server (described later on). Typically unRAID is stored on a USB thumb drive of limited size. Also unRAID servers do not have large amounts of RAM. The problem is that MySQL can potentially use a lot of memory. To compensate, we can create a swap file for the system to use as additional memory in case you exceed your RAM capacity. BUT creating a swap file on the thumb drive can shorten the lifespan of the thumb drive. Thumb drives have a limited amount of read/writes, that the swap file would use up.

 

So the best method to do this is to create the swap file in your cache drive. To create a swap file go into unMenu and click on Pkg Manager. Scroll down the list and click on the button for "unRAID Swapfile Configuration. Install the package and click on "Configuration variables".

 

Swap File Size (Megabytes): I entered 4096 for mine