The “Ideal” XBMC setup …
I found the ideal XBMC setup to be the one where you have:
- One (or more) network location containing all the media.
For example a NAS box (QNAP for example), a Windows setup with a shared media folder, a Linux box running FreeNAS, etc.
- One or more XBMC PC’s throughout the house.
They should all be running exact the same version of XBMC, but can be running on different operating systems.
Sharing files on the XBMC boxes themselves works as well, but can make things more complicated – specially when you’d like to sync them as described here. In this article, I’m going to assume that you’re working with a setup like this: one central network share, and one or more XBMC HTPC’s.
MySQL is a freely available, yet powerful database engine intended for server use – you wouldn’t know, but the majority of webservers actually utilize MySQL to run their dynamic content. Recent changes in XBMC make it possible to use MySQL for storing information that can be shared amongst several other XBMC setups in your house. Setting up MySQL is straight forward and you might already have it … for example my QNAP NAS already comes with MySQL pre-installed (it just needs enabling).
Here is my article on How to install MySQL, Web-Server, and phpMyAdmin on your QNAP.
MySQL is available other platforms as well: Windows, Linux, MacOS X.
You could of course decide to manually install MySQL – go to this link and select the OS (not needed for a QNAP NAS). I do however strongly recommended (not needed for a QNAP NAS setups) to use one of the many AMP (Apache, MySQL, PHP) packages (most are free);
- WAMP Server for Windows, or
- XAMPP for multiple platforms including Windows, MacOS X and Linux.
- MAMP for MacOS X (OS X Server already comes with MySQL)
The advantage being that it’s easy to install and they all come with phpMyAdmin preinstalled – which makes maintaining MySQL and running queries so much easier straight from any web-browser. The disadvantage (?) might be that Apache (webserver) will be installed as well (disk-space / minimal CPU load).
But … I don’t want to use phpMyAdmin!
If you decide to use a setup without phpMyAdmin then make yourself familiar with how to access MySQL from the command line on your system (or get a program like EMS MySQL Manager or DevArt SQL Studio, or the free HeidiSQL), see the MySQL documentation for details on your system since you will have to manually (in a shell) go to the mysql command line tool for execute a few SQL statements.
The typical command for all systems is: mysql -u username -p
(replace username with your actual username, the -p will make MySQL ask for your password)
On some systems (Windows migth be one of those) you will have to make sure that the port (default: 3306) you’re using for database access is not blocked by your firewall.
Preparing a Database
I’m assuming XBMC Eden (v11) or newer for this article ….
Older XBMC versions will work the same way for SVN version R28117 or above, but might require you to run two additional SQL statements!
Upgrading to a newer version of XBMC or KODI
Note that when you upgrade to a newer version of KODI/XBMC that the database will be re-created for that particular version.
This could mean that your Kodi starts a little slow the first time, just let it be and do it’s thing.
Based on the following instructions you will give XBMC the access to the database it needs to create those databases, the needed tables, and eventually your library data.
The Short Version
Go to phpMyAdmin and click the “SQL” tab. In the upcoming text-box type (or paste) the following SQL statements and click the “Go” button in the lower right corner to execute the SQL statements:
CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
GRANT ALL ON *.* TO 'xbmc';
What does this do?
CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
Translated: Create the user “xbmc” with the password “xbmc”
PHPMyAdmin/MySQL – Create a user for XBMC
After execution, PHPMyAdmin will jump back to the PHPMyAdmin start page – unless you somehow made a typo. You can click the tab “Privileges” and under the “User Overview” you should now see (at the bottom) the user “xbmc“.
Note: I used the password “xbmc” in this example (without the quotes, right after “IDENTIFIED BY”), feel free to use a different password if you’re concerned with security. Make sure to use that new password consistently throughout the next steps.
GRANT ALL ON *.* TO 'xbmc';
Again a loose translation: Grant the user “xbmc” full access
This will give the user “xbmc” sufficient access to create and modify databases.
OK, so from a MySQL perspective we’re set …
These instruction assume you use XBMC Eden (v11)!
Older versions might require you to run two additional SQL statements to create the actual databases (Eden will do this automatically):
CREATE DATABASE xbmc_video;
CREATE DATABASE xbmc_music;
Moving to a newer XBMC version …
When moving to a newer version of XBMC, it can happen that the newer version (Eden and newer) will copy the data of the old database to a new database. The new database, to be used by the newer XBMC version, will have the same name followed by a 2 digit number.
For example: xbmc_music (old – will remain untouched) will be copied into xbmc_music18 (new – will now be used).
Configure your XBMC setup(s)
All XBMC’s – Use the exact same paths to Movies, Music, and TV-Series!
Before we proceed with the MySQL connection for our XBMC boxes we should make sure that the path to our Movies, Music, and TV-Series are consistent for all XBMC’s you’re using. So make sure that all “sources” for all your XBMC HTPC’s are setup equally!
This is a must, otherwise the different paths will be seen as different files!
I recommend starting with 1 single XBMC setup. Make clear notes on what shares (path) have been setup for the different sources and media types – we want to replicate those on the other XBMC’s.
Another reason to start with just 1 XBMC box is that we can use PHPMyAdmin to see what XBMC is really saving in the database, and that in turn will give us a better understanding of what might be a reason why certain things do or do not work.
First we need to make sure that we use a version of XBMC that can actually handle all this fun – current versions support this just fine. So just download a recent version (XBMC Download Page or get OpenElec). It is said that SVN version R28117 and above should be good to go (source).
Step 1: Export your current Library (optional)
This is optional, but it might save a lot of time if we simply re-import the hard work once the MySQL Connection is up and running. This way we don’t have to re-scan your entire library and instead use what we already have.
In XBMC go to the “System” menu, select “Settings” → “Video” where you select “Export Library” option. XBMC will now ask if you want to save this as a single file (yes) and where to save it – pick your destination. After the export you’ll find a folder name (something like) “xbmc_videodb_2012-01-23“.
XBMC (Eden/ v11) – System menu – Select Settings
XBMC (Eden/ v11) – Video menu – Export Video Library
Step 2: Create a file “advancedsettings.xml”
Open a plain text editor ( Windows: Notepad or Notepad++, MacOS X: TextWrangler, Linux: VI or Nano) and paste the following text, after adapting it to your scenario:
- Replace 192.168.1.100 with the IP address of your MySQL Server.
- If you changed the default port number of your MySQL server, then change 3306 to the port number you picked (3306 is the default port, so if you didn’t change it during the MySQL setup, don’t change it here either).
- Enter the proper username between <user> and </user> (if you did not use the suggested “xbmc”)
- Enter the correct password between <pass> and </pass> (if you decided to use something else than “xbmc”)
<name> no longer needed?
The name tag (<name>…</name>) appears no longer needed if you’re using one of the more recent versions of XBMC when using only one shared database.
XBMC will chooses it’s own name and add the version number of the database structure after it for example “xbmc_video60” or “xbmc_video67”. When a new database version is being created the old data will be copied into the new database. This is (probably) a result of an experimental stage in which the database structure changes over time. Unfortunate, specially when you use a few different versions of XBMC as it makes quite a mess in MySQL.
It can however be useful if you want to utilize two different databases, for example one for XBMC stations for Adults and one for the Kids.
The use of multiple databases however have been reported to cause issues.
Now save this file as “advancedsettings.xml“.
- Windows XP
Move this file to “C:\Documents and Settings\<your_user_name>\Application Data\XBMC\userdata\“.
- Windows Vista, 7, 8 and newer
Move this file to “%APPDATA%\XBMC\userdata” (you can paste this in the Windows Explorer).
- MacOS X
In the Finder, click the “Go” menu and select the “Go to Folder …” option. A window opens saying “Go to the folder”, paste “~/Library/Application Support/XBMC/userdata” (without the quotes of course) in the text box and click “Go“.
Now move the advancedsettings.xml file to the folder that just opened.
The file needs to go into this folder: ~/.xbmc/userdata
Accessing this under a live version is a little harder, use either SSH access, or press CTRL+ALT+F2 to go to the shell (use CTRL-ALT-F7 to go back to XBMC) and do this all manually with either VI or nano.
Some great version can be found at XBMCFreak, or you can use the XBMC Live version.
A shared folder called “userdata” can be found in your network for the machine running OpenElec, drop the file in there and reboot the machine.
OpenElec is for example available for Linux, AppleTV (1st generation), and Raspberry Pi – they all use the same method which is awesome!
- Apple iOS device (iPad, AppleTV2, etc)
The instructions claim to save the file here (untested!): “/private/var/mobile/Library/Preferences/XBMC/userdata/“.
- CrystalBuntu / CrystalHD
Use SSH to access the file (ssh username@ip-address). The file goes here: “/root/.xbmc/userdata“.
Step 3: Restart XBMC and optionally import the exported library
Shutdown and restart your XBMC HTPC.
If you find that the startup of your XBMC takes forever then please verify a few things:
- Did you use the right IP address, username, password, and database name in the advancedsettings.xml?
- Does your server or XBMC box have a firewall that blocks port 3306?
- Did you execute the SQL statements properly? (most common issue: lack of access rights)
- Is MySQL actually running?
- Does your XBMC version support MySQL syncing?
- Do you have an older XBMC version (Dharma) and did you execute the 2 additional SQL statements (CREATE DATABASE)?
If you see in phpMyAdmin
that several tables, and maybe even new databases, have been created, then you’re ready to import the previously (optional) export library – the import steps are similar to the previously executed import steps.
phpMyAdmin – XBMC made some tables!
Repeat step 2 and 3 (use the same advancedsettings.xml file) for your other XBMC boxes – even if they run on different operating systems!
The Result …
After doing this for two XBMC setups;
- Running Eden Beta2 on MacOS X Lion
- Running a nightly build (also Eden) of OpenElec (Linux) on a dinky AMD E350 computer (Foxconn nt-a3500 – Dutch review)
- Running MySQL on my QNAP NAS
Now I can see on both machines where I paused, to then resume on the other machine.
XBMC – Machine 1 – MacOS X Eden Beta 2 paused here …
XBMC – Machine 2 – OpenElec (nightly build) Wants to resume here …
Tips to prevent or fix issues and make configuration easier
We now have an XBMC setup that syncs with your MySQL database, but little problems might still sneak in and quite a few of them are easy to prevent.
Use the Debug Log!
XBMC knows several levels of debugging and when running into issues, it will be almost required to use.
More information can be found at the following links: XBMC Debug, XBMC Geavanceerde Debug, and XBMC advancedsettings.xml debug opties.
DEFAULT DEBUG LOG
If you want to do debugging the default setting is always needed:
The default is enabled in the User Interface: In Settings -> System -> Debugging – toggle the Enable debug logging setting.
ADVANCED DEBUG LOG
The advanced debugging takes a little more effort and generates some very good info.
You will need to add the example code below to your advancedsettings.xml file, where the loglevel (1 in the example) can have the following values:
XBMC Debug LogLevels
||What it does …
|| No logging at all.
|| (Default) Only log errors.
|| Debug logging.
|| Debug logging (shows CPU and Memory on screen).
|| Same a level 2, but adds SMB (Windows shares) logging.
A few items I ran into or have seen with other users:
Duplicate entries, not saving where I paused
On one box the server was referred to by it’s name and the other one used an IP address – resulting in two different entries for every file in the database!
This obviously prevented it from marking the proper entry as “wachted”.
For unknown reasons one XBMC box saved a path in the regular format (ie. sbm://<servername>/path), while the other one added username and password (ie. smb://<username>:<password>@<server>/<path>) – obviously again the database stored this as two different files, and again not tracking “watched” properly.
Having to enter sources manually does not only cause these kind of issue, but it is also an cumbersome exercise.
Do it once and copy the proper files saves on work and problems later on due to inconsistency.
A problem I have not found a good solution for:
Probably the biggest issue I’ve been running into is the different database structures for different versions of XBMC – this unfortunately has not been made backwards compatible and creates additional databases on your MySQL server. Not only does it seem unnecessary, but it also comes with problem, since the newer XBMC and the older XBMC versions will NOT keep those databases in sync.
Database not populating – Can’t find MySQL
I actually ran into situations where XBMC started quicker than the network when using dynamic IP addresses (DHCP). The network wasn’t up in time so XBMC did not find the MySQL server so the database remained empty. The solution I found (for OpenElec) was to create a file called “autostart.sh” (see example content below) and copy it into the “Configfiles” of your OpenElec XBMC box.
This will give you XBMC box a fixed IP address!
You will of course need to modify line 3 (the new fixed IP address of your XBMC box) and line 4 (the IP address of your modem or router).
ifconfig eth0 up
ifconfig eth0 192.168.1.97
route add default gw 192.168.1.1
Database not populating – Permission Errors
Another reason why the database might not populate is when there are file permission issues with your music or video files.
Easiest way to discover this is with the advanced debugging. You might see messages like seen below.
What happens is that XBMC can see that there are files and folders, which is enough for most scrapers, but I cannot open the file (to determine resoultion, codec, etc).
File permissions can be a tough topic to deal with. One of the visitors of Tweaking4All resolved it by removing ALL sources, and by manually (not using the XBMC filebrowser!) adding them in the format
Since I didn’t experience this issue, I cannot confirm if this is the only and best method – but you will have to look into how XBMC accesses your files and what the access rights are. (reference: XBMC Forum, and OpenElec Forum)
17:44:11 T:2879218784 INFO: FileSmb->Open: Unable to open file :
unix_err:'d' error : 'Permission denied'
Rules of thumb that I follow
- Use the consistent paths when referring to a server for your sources.
- Refer to a server by it’s IP address for your sources, it appears faster.
- Setup one XBMC box with all the sources and settings you’d like and then copy the configuration files to the other XBMC boxes (reboot after copying).
The following files appear to be crucial in this process:
Database speed improvements
For certain versions of XBMC the databases have not been fully optimized for speed. Adding the following indices will give it a little boost (source: OpenElect configure MySQL) – only do this when you know what you’re doing since database structures will be different with different XBMC versions!
XBMC v12 (Frodo) has these indexes already – so no need to add them.
ALTER TABLE movie ADD INDEX ix_idFile(idFile);
ALTER TABLE movie ADD INDEX idMovie(idMovie);
ALTER TABLE movie ADD INDEX idFile(idFile);
(not applicable for XMBC 12 and newer)
CREATE INDEX idAlbum_idx ON song(idAlbum);
CREATE INDEX idArtist_idx ON song(idArtist);
CREATE INDEX idArtist_idx ON album(idArtist);
CREATE INDEX idArtist_idx ON exartistsong(idArtist);
CREATE INDEX idArtist_idx ON exartistalbum(idArtist);
ALTER TABLE song ADD INDEX idArtist(idArtist);
ALTER TABLE song ADD INDEX idGenre(idGenre);
Speed boost: MyISAM vs InnoDB
I noticed that with XBMC v12 (Frodo) that suddenly database access could be very slow – specially after watching a TV Show Episode and trying to go watch the next episode.
After a lot of tinkering I noticed that the slow down is caused because of two factors:
- The Database engine used on MySQL was MyISAM (slow performance issues).
- The XBMC developers chose to use VIEWs (which is not recommended under MySQL).
Now without changing the core of XBMC, I have no fix for the use of VIEWs.
I do however have control over the database engine – so I switched to InnoDB and guess what … everything works blistering fast again.
For the brave amongst you, the following SQL will convert all tables to InnoDB:
ALTER TABLE writerlinkmovie ENGINE=InnoDB;
ALTER TABLE writerlinkepisode ENGINE=InnoDB;
ALTER TABLE version ENGINE=InnoDB;
ALTER TABLE tvshowlinkpath ENGINE=InnoDB;
ALTER TABLE tvshow ENGINE=InnoDB;
ALTER TABLE taglinks ENGINE=InnoDB;
ALTER TABLE tag ENGINE=InnoDB;
ALTER TABLE studiolinktvshow ENGINE=InnoDB;
ALTER TABLE studiolinkmusicvideo ENGINE=InnoDB;
ALTER TABLE studiolinkmovie ENGINE=InnoDB;
ALTER TABLE studio ENGINE=InnoDB;
ALTER TABLE streamdetails ENGINE=InnoDB;
ALTER TABLE stacktimes ENGINE=InnoDB;
ALTER TABLE settings ENGINE=InnoDB;
ALTER TABLE sets ENGINE=InnoDB;
ALTER TABLE seasons ENGINE=InnoDB;
ALTER TABLE path ENGINE=InnoDB;
ALTER TABLE musicvideo ENGINE=InnoDB;
ALTER TABLE movielinktvshow ENGINE=InnoDB;
ALTER TABLE movie ENGINE=InnoDB;
ALTER TABLE genrelinktvshow ENGINE=InnoDB;
ALTER TABLE genrelinkmusicvideo ENGINE=InnoDB;
ALTER TABLE genrelinkmovie ENGINE=InnoDB;
ALTER TABLE genre ENGINE=InnoDB;
ALTER TABLE files ENGINE=InnoDB;
ALTER TABLE episode ENGINE=InnoDB;
ALTER TABLE directorlinktvshow ENGINE=InnoDB;
ALTER TABLE directorlinkmusicvideo ENGINE=InnoDB;
ALTER TABLE directorlinkmovie ENGINE=InnoDB;
ALTER TABLE directorlinkepisode ENGINE=InnoDB;
ALTER TABLE countrylinkmovie ENGINE=InnoDB;
ALTER TABLE country ENGINE=InnoDB;
ALTER TABLE bookmark ENGINE=InnoDB;
ALTER TABLE artistlinkmusicvideo ENGINE=InnoDB;
ALTER TABLE art ENGINE=InnoDB;
ALTER TABLE actors ENGINE=InnoDB;
ALTER TABLE actorlinktvshow ENGINE=InnoDB;
ALTER TABLE actorlinkmovie ENGINE=InnoDB;
ALTER TABLE actorlinkepisode ENGINE=InnoDB;
It should be noted that I you might want to look in the InnoDB settings in your my.cnf file found in /etc/config.
If you did so, a restart of MySQL is needed for it to take effect:
Even though implementing this is relatively easy; this topic can come with additional questions, find below some links to related webpages:
Enjoy watching TV with your newly added XBMC feature …