Official Kodi Wiki - User contributions [en]

Talk:MySQL

2014-01-05T10:16:08Z

Scsa20: /* Windows Firewall Rules */ new section

== NAS how-to link dump ==

Dumping some urls that might be usable or be a base for how-to's on mysql on NAS's. -- [[User:Ned Scott|Ned Scott]] 04:37, 15 November 2011 (EST)

*http://humphrey.za.net/2010/09/25/xbmc-mysql-multi-room-sync/

== Other useful links ==

Some more links to look into. -- [[User:Ned Scott|Ned Scott]] 20:55, 15 November 2011 (EST)

*http://lifehacker.com/5634515/how-to-synchronize-your-xbmc-media-center-across-every-room-in-the-house
*http://xbmcstuff.bossanova808.net/2011/03/summary-of-xbmc-mysql-database.html

== A couple of comments ==

Ubuntu users can install MySQL from official Ubuntu repos using apt-get: https://help.ubuntu.com/10.04/serverguide/C/mysql.html

Also, the top of the article has a note mentioning that the latest pre-Eden nightly is required. What specific steps in the article does this apply to, and are there alternative steps applicable to the latest stable release (Dharma 10.1 at the time of this writing)?

Lastly, the scottbrant.net/?p=495 link in the article is not working for me.

Update: I also got access denied errors in the MySQL log while trying to import my XBMC database into it. I think there's a missing step in the article for setting the MySQL 'xbmc' user password. The MythTV wiki has some troubleshooting info on setting passwords that helped: http://www.mythtv.org/wiki/Category:MySQL

--[[User:HunterZ|HunterZ]] 14:42, 11 December 2011 (EST)

:Added your links. Eventually we'll have a more general MySQL page for troubleshooting and more advanced setups, but I still put in the mythtv link in the "see also" section for the time being. "<code>CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';</code>" should create the password. The password is what IDENTIFIED BY sets.

:Pathsub for thumbs requires pre-Eden, but most of the MySQL setup will still work for Dharma, but you might need to manually create the "xbmc_video" and "xbmc_music" databases (Eden will create databases if missing).

:The scottbrant.net link has now been replaced with a link to http://xbmcstuff.bossanova808.net/2011/03/summary-of-xbmc-mysql-database.html?m=1 which has more settings and further discussion on speeding up mysql.

:Thanks for the links and let us know if you have any other feedback/ideas/whatever! -- [[User:Ned Scott|Ned Scott]] 22:52, 11 December 2011 (EST)

Someone should make a note that the Pre-Eden builds do NOT like it if you create the xbmc_video and xbmc_music databases in advance.

:Add them to your advancedsettings.xml but DO NOT CREATE them when installing and configuring MySql or your initial set up will fail. If you have already created them, or you are upgrading from an earlier version of XBMC that did not have this problem, just use a DIFFERENT database name in your advancedsettings.xml, or DROP the two fresh databases if you are installing from scratch.

:If upgrading from an earlier version, export your library before upgrading, then import it once everything is set up again on the new version. I just spent about 10 hours tracking down this elusive piece of information, so hopefully this helps someone else. I know for sure that this affects all nightlies from 12/9/2011 and on, could very easily be an issue going back for months as far as I know. This may change, so try the "old" way first and if it fails (check your log) then simply drop those two databases (unless upgrading and you don't have a backup already!)

:As I just registered here for the purpose of sharing this info, I put it here rather than in the actual guide as I am not entirely familiar with the rules yet and don't want to step on any toes right off the bat! [[User:J114|J114]] 02:32, 15 December 2011 (EST)

::will put a warning note in there, but hopefully that's a bug that will be fixed by Eden's release. -- [[User:Ned Scott|Ned Scott]] 02:37, 15 December 2011 (EST)

On my win 7 PC, sharing thumbnails caused hangs/freezes, removing the path substitution from advancedsettings.xml fixed the problem.

I've shared my whole profile directories between my HTPC and my iPad. Of course, I do not want the same guisettings... I tried the following (in advancedsettings.xml on my iPad) and it works to share anything regarding profile (mySQL config, Thumbnails, etc) *except* guisettings.xml (with a dedicated shared guisetting_iPad.xml file instead)
<pre>

<substitute>
<from>special://masterprofile/profiles/Olivier/guisettings.xml</from>
<to>nfs://SHARE/userdata/profiles/Olivier/guisettings_iPad.xml</to>
</substitute>
<substitute>
<from>special://masterprofile/profiles/Olivier</from>
<to>nfs://SHARE/userdata/profiles/Olivier</to>
</substitute>
</pre>
[[User:Frofpa|Frofpa]] 04:08, 9 April 2012 (EDT)

::That is a very bad idea, as it will share things that should not be shared, such as the database files. -- [[User:Ned Scott|Ned Scott]] 04:50, 9 April 2012 (EDT)

== Restricted privileges ==

If you are using your database for something other than XBMC, it may be better to restrict privileges, in case a bad add-on manages to do a DROP with the xbmc user.

Using this instead of the "*.*" line makes XBMC only access/create its own databases:
GRANT ALL PRIVILEGES ON `xbmc_%`.* TO 'xbmc'@'192.168.1.%';

(Source: http://forum.xbmc.org/showthread.php?tid=114017 ) [[User:Michelv|Michelv]] 14:20, 23 June 2012 (EDT) Michelv

== Path substitution causes duplicate entries ==

The path substitution step listed in the article is problematic. I'm trying to use it now to access my video files via an OS mount instead of the smb:// paths that are currently stored in the database, and it's resulting in duplicate entries on the next library update: http://forum.xbmc.org/showthread.php?tid=140536
--[[User:HunterZ|HunterZ]] 21:44, 14 September 2012 (EDT)

:Path subs alone cannot create duplicates, as it only takes a source and points it to a different location. There would still only be one source. You only get duplicates if you have more than one source that is pointing to the same files. -- [[User:Ned Scott|Ned Scott]] 02:58, 15 September 2012 (EDT)

:What may have happened here is that you scanned your sources and the video files were enter into your db through the smb path. Then you added the path substitution and scanned your sources again. The substitution causes the same video files to have different paths and they get enter into your db through this new path as well. And since the old smb path is still valid, the old entries are not removed. I've had this happen when I switched from using NetBIOS name to ip address in "smb://" sources. I found the easiest way to get rid of the old ("smb://netbios-name/...") files was to change the name of the machine to something else, so these paths are no longer valid, clean up the db and then change the name of machine back. [[User:SkyLined|SkyLined]] 08:44, 24 May 2013 (EDT)

== MariaDB ==

MariaDB works as a drop in replacement to MySQL and it's a bit faster. Should it be added to this page or be placed on its own separate page? (The instructions require a PPA be used in Ubuntu so they are a bit more complicated. Also, upgrading from MySQL has to be covered as well. [[User:Ivanmmj|Ivanmmj]] 12:46, 24 January 2013 (EST)

== MySQL connection errors at start-up ==

Note: This issue is related to XBMC running on Raspberry PI using OpenElec

I just spent several hours debugging strange behavior in XBMC after migrating to MySQL. The strange behavior revolved around certain featured disappearing after reboot, such as the Movie tab on the home screen and under Videos. Also if you right clicked a movie you no longer had all the selection like "Movie Information".

So after reviewing the logs under <HOME>/.xbmc/temp I noticed errors connecting to the DB and the art share. It appears there is a problem in the timing between the network interface and when XBMC looks for the shares or tries to connect to the DB.

So I decided to mess around with autostart.sh which executes before XBMC starts and pre-mount the two servers needed for these connections. That too was frustrating. Turns out when the autostart script executes the mount command the network interface has not yet been started. So to solve the problem I had to add a ifconfig up eth0 <ipaddress>. This solved the problem and XBMC now starts without issues.

So after thinking about Ned's point below I decided that the only think I need to do during autostart.sh execution is to start the network interface

: #!/bin/sh
: #set -x
:echo "===== Start $0 ====="
:ifconfig
:ifconfig eth0 up <your IP address>
:ifconfig
:route
:echo "===== End $0 ====="
:sleep 5
:exit

[[User:Jpgeek|Jpgeek]] 13:57, 30 April 2013 (EDT)

:Thanks for the heads up, but I think most users would not be mounting their shares on the OS level, but instead having XBMC take care of connecting to the shares? -- [[User:Ned Scott|Ned Scott]] 14:02, 30 April 2013 (EDT)

:Good point, and I guess I don't need to mount anything just make sure the network interface is up. This is probably not an issue on a standard XBMC installation, it appears to be a startup synchronization problem on OpenElec and an appliance like Raspberry PI. [[User:Jpgeek|Jpgeek]] 16:14, 30 April 2013 (EDT)

== Alternative to export/import ==

I have an alternative to exporting/importing that I find to be easier to use and explain. I developed it after I ran into a few issues with a Windows server hosting my media which I resolved by using its IP address, rather than its NetBIOS name and providing a valid user in "passwords.xml", even though I was sharing my media without username/password. I am not entirely sure what the root cause of my issues was. I was getting errors on my Android and Raspberry PI devices but not on my Windows laptop, so I assumed it had to do with the smb implementation on *nix machines wasn't working as expected. The sync seemed to work fine on all my devices as I could see all the media files in my library, but when I wanted to play something on the *nix based devices, I got an error saying that "the file is no longer available" and would I want to remove it from the library? I never bothered to check my log files, so I can't tell you more about it.

Anyway, rather than export and import the media database in xbmc, you will need to create/copy two additional files (sources.xml and passwords.xml, in addition to advancedsettings.xml) in/to the userdata folder of any device you want to sync. If others agree that this is easier than exporting and importing the database we should update the HOW-TO.

===advancedsettings.xml===
Create advancedsettings.xml as described in the HOW-TO
===sources.xml===
<syntaxhighlight lang="xml"><sources>
<programs>
<default pathversion="1"></default>
</programs>
<video>
<default pathversion="1"></default>
<source>
<name>Movies</name>
<path pathversion="1">smb://192.168.0.1/Shares/Movies</path>
</source>
<source>
<name>TV Shows</name>
<path pathversion="1">smb://192.168.0.1/Shares/TV Shows</path>
</source>
</video>
<music>
<default pathversion="1"></default>
<source>
<name>Music</name>
<path pathversion="1">smb://192.168.0.1/Shares/Music</path>
</source>
</music>
<pictures>
<default pathversion="1"></default>
<source>
<name>Pictures</name>
<path pathversion="1">smb://192.168.0.1/Shares/Pictures</path>
</source>
</pictures>
<files>
<default pathversion="1"></default>
</files>
</sources></syntaxhighlight>
''Obviously, you'll need to replace "smb://192.168.0.1/Shares/..." with the relevant path in your setup.''
{{Note|Use a static IP address for your media server and not "smb://MediaServer/...", as NetBIOS names may give problems on some devices.}}
{{Note|Remote paths should not include a username/password, even if you use one, as these are specified in passwords.xml}}</p>
===passwords.xml===
<syntaxhighlight lang="xml"><passwords>
<path>
<from pathversion="1">smb://192.168.0.1/</from>
<to pathversion="1">smb://username:password@192.168.0.1/</to>
</path>
</passwords></syntaxhighlight>
''Again, you'll need to replace "smb://192.168.0.1/" with the relevant path in your setup.''
{{Note|When using an smb that is not protected with a username and password and you are getting errors when you want to playback files, you will need to supply a username:password that is valid on the server. If your user does not have a password, you can just provide the username as in "smb://username@192.168.0.1".}}

All three files go in the same location in the userdata folder. Restart XBMC and you're done! [[User:SkyLined|SkyLined]] 09:30, 24 May 2013 (EDT)

== Windows Firewall Rules ==

I've updated the Windows section about the Windows Firewall. There is no need to use UDP since MySQL only talks on TCP. Also there's no need to set up an outbound rule since you only need it for incoming traffic to get the data so why bother covering it with rules that's not needed and possibly open yourself to nasty stuff? [[User:Scsa20|Scsa20]] ([[User talk:Scsa20|talk]]) 05:16, 5 January 2014 (EST)

MySQL/Setting up MySQL

2014-01-05T10:10:12Z

Scsa20: /* Windows */ updated firewall rules information

{{Frodo updated}}{{:HOW-TO:Sync multiple libraries/Contents}}
You'll need to choose which of your computers, XBMC devices, or [[NAS]] will be the MySQL server. The server needs to be on 24/7 or have wake-on-lan (preferably the former), and needs to have a local static IP. You will probably want the XBMC device that is hosting most or all of your videos and music to also be the MySQL server, but this is not required.
{{-}}

<big>{{notice|Users are reporting issues with '''MySQL 5.6'''. Use '''MySQL 5.5''' instead.}}</big>
MySQL can be installed on just about every major OS:

=Ubuntu Linux=
'''Ubuntu Linux:'''
#<code>$sudo apt-get install mysql-server</code> ''(see also: https://help.ubuntu.com/10.04/serverguide/C/mysql.html )''
#Create a password when asked
#To configure MySQL to listen for connections from network hosts, edit ''/etc/mysql/my.cnf'' and change the bind-address directive to the server's IP address:
##from: <code>bind-address = 127.0.0.1</code>
##to: <code>bind-address = 192.168.0.5</code>
#*Note: Replace 192.168.0.5 with the appropriate address.
#*Note: To allow local and remote access try comment out bind-address using a hash(#) or use <code>bind-address = 0.0.0.0</code>
#Restart your MySQL server. <code>$sudo restart mysql</code>
#Get into the MySQL command line utility: <code>$mysql -u root -p</code>
#Enter the following commands:
##Type in: <code>CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';</code> and press return
##Type in: <code>GRANT ALL ON *.* TO 'xbmc';</code> and press return
#Close out the command line tool with <code>\q</code>

=Arch Linux=
'''Arch Linux:'''
*Please note: Arch, along with many other distributions, has migrated to the faster and more open MariaDB. It is a drop in replacement to MySQL and, thus, XBMC will require no modifications to work with it.
#Install MariaDB: <code>$ sudo pacman -S mariadb</code>
#Start the service: <code>$ sudo systemctl start mysqld</code>
#To have it automatically launch at startup: <code>$ sudo systemctl enable mysqld</code>
#Create a password: <code>$ /usr/bin/mysql_secure_installation</code> ''This will also clean up the demo database.''
#Get into the MariaDB command line utility: <code>$ mysql -u root -p</code>
#Enter the following commands:
##Type in: <code>CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';</code> and press return
##Type in: <code>GRANT ALL ON *.* TO 'xbmc';</code> and press return
#Close out the command line tool with <code>\q</code>

=RedHat based Linux=
'''RedHat based Linux (RHEL, CentOS, Fedora):'''
#<code>$sudo yum install mysql-server</code>
#Create a password when asked
#To configure MySQL to listen for connections only from network hosts, edit ''/etc/mysql/my.cnf'' and add the bind-address directive to the server's IP address:<br /><code>bind-address = 192.168.0.5</code>
#*Note: Replace 192.168.0.5 with the appropriate address.
#*Note: Alternatively you can leave this file as is to allow local and remote access.
#Restart your MySQL server. <code>$sudo /sbin/service mysqld restart</code>
#*Recommended - Set your MySQL root password with <code>/usr/bin/mysqladmin -u root password 'new-password'</code>
#Get into the MySQL command line utility: <code>$mysql -u root -p</code>
#*Enter your MySQL root password as set above, or just press enter if you didn't set one to log in with a blank password
#Enter the following commands:
##Type in: <code>CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';</code> and press return
##Type in: <code>GRANT ALL ON *.* TO 'xbmc';</code> and press return
#Close out the command line tool with <code>\q</code>

=Mac OS X=
'''Mac OS X:'''
#Download a copy of MySQL server from http://dev.mysql.com/downloads/mysql/
#In Mac OS X, go to System Preferences -> MySQL, and click on “Start MySQL Server”. You’ll also want to tick to enable the “Automatically … on Startup” option.
#Now to set up the "xbmc" user in MySQL and give it access. Launch Terminal.app on your Mac and issue the following commands, one line at a time:
##<code>sudo /usr/local/mysql/bin/mysql</code>
##<code>CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';</code>
##<code>GRANT ALL ON *.* TO 'xbmc';</code>
##<code>exit</code>
#Remain in Terminal.app. Now you'll be giving other computers on your network access to MySQL. Enter the next set of commands, one line at a time:
##<code>cd /usr/local/mysql/support-files/</code>
##<code>sudo cp my-huge.cnf /etc/my.cnf</code>
##:''The command line will ask for your Mac OS X user password. Enter it and press return. No text will show when you type your password in, this is normal.
##<code>cd /etc</code>
##<code>sudo nano my.cnf</code>
##:''This opens a file in a command line text editor''
##Scroll down to the [mysqld] section and add or edit the following line so that it appears as:
##:<code>bind-address = ***.***.***.***</code>
##Save and exit from the command line text editor
#Reboot the Mac, or restart the MySQL service from the System Preferences pane you installed earlier

*Alternative guide for Mac OS X: http://forum.xbmc.org/showthread.php?tid=151631

=Windows=
'''Windows:'''
#Download a copy of MySQL server from http://dev.mysql.com/downloads/mysql/
#Select ''Standard configuration''
#Create a password when asked
#Select that you want the database to have network access when prompted during installation.
#Open the "MySQL Command Line Client" from the MySQL start menu
#Enter the following commands:
##Type in: <code>CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';</code> and press return
##Type in: <code>GRANT ALL ON *.* TO 'xbmc';</code> and press return
#Close out the command line tool
#If you're using a firewall such as the one that's built into Windows, you will need to do this next step.
:*'''Windows 7 Built-in Firewall'''
:#Go to control panel and open Windows Firewall
:#Click on Advanced Settings
:#Click on Inbound Rules, then New Rule
:#For the Rule Type, choose '''''Port''''' and hit Next
:#For Protocol and Ports, select TCP protocol and type in '''''3306''''' for the '''''Specific local ports''''' and hit Next
:#For Action, leave it at default which is '''''Allow the connection''''' and hit Next
:#For Profile, you can leave it as default (everything checked by default) and hit Next
:#For Name, set it to '''''MySQL''''' or any name that you will know what it is and click Finished
:*'''Windows XP Built-in Firewall'''
:#Open Control Panel and Set to 'Classic View'
:#Double Click on Windows Firewall
:#Click on tab that says Exceptions
:#Click on Add Port
:#Name it '''''MySQL''''' or any other name
:#Set the port number to '''3306'''
:#Choose '''''TCP''''' and click OK

{{Note|If XBMC is unable to connect to the MySQL server, returning an error code [1130]:}}<br/>
If this occurs to you, try amending Step 2 above to read <code>GRANT ALL ON *.* TO 'xbmc'@'xxx.xxx.xxx.%';</code> where xxx.xxx.xxx is the first three quadrants of your home network's IP address (192.168.1 for example) and % as the last quadrant to allow addresses on the same subnet to connect.

=Other NAS=
MySQL can also be installed on some [[w:network-attached storage|network-attached storage (NAS)]] device devices. Here are some guides for various NAS devices that can have a MySQL server installed:

*Devices running Arch Linux ARM (such as the PogoPlug, Dockstar, etc) can use the [[HOW-TO:Share libraries using MySQL/Setting up MySQL/ALARM|Arch Linux instructions]] on this page.
*[http://quixventure.com/2011/12/configure-a-synology-nas-as-mysql-server-for-xbmc/ Synology NAS as MySQL server for XBMC]
*http://www.robvanhamersveld.nl/2013/02/05/share-your-xbmc-12-0-library-on-a-synology-ds-or-other-nas/ Synology DS with MySQL guide based on Frodo
*http://forum.xbmc.org/showthread.php?tid=172548 - unRAID

=FreeNAS and freeBSD=

== About FreeNAS ==

http://www.freenas.org/

FreeNAS is a FreeBSD based NAS system that can be run on almost any hardware - it uses ZFS for disk and file management. It is used at home and in enterprise businesses. 99% of tasks can be done via the web interface.

Example below is based on FreeNAS 9.1.1 on 12/12/13, and assumes you have already configured your nas with drives, datasets etc

== How to ==

=== Make a jail ===

create a Jail via the FreeNAS webui - if unsure see here http://doc.freenas.org/index.php/Adding_Jails

make a note of the jail IP address shown - you will need this to put in the advancedsettings.xml file on the XBMC machine later

=== Prepare the jail ===

*ssh into FreeNAS
**If you have a windows PC use putty (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html)
**(easiest) click the "shell" button in the left of the FreeNAS webui

type the following commands

*<code>jls</code>

Take note of the JID of the jail you created, use it in the command below and replace n with the number of the jail you just created

*<code>jexec n csh</code>

=== Install mysql ===
You are now inside your jail

*<code>pkg install mysql55-server</code>

*<code>pkg install nano</code>
**(nano is a text editor you will need shortly)

We now need to enable mysql in rc.conf

*<code>nano /etc/rc.conf</code>

insert the line below into the rc.conf file

*<code>mysql_enable="YES"</code>

save the file and exit (see bottom of nano edit page for keystrokes info)

*<code>service mysql-server start</code>

Optional step below - this is an automated script that will secure you mysql instance - it will prompt you for answers to questions. Its up to you if you do this.

*<code>mysql_secure_installation</code>

=== Configure mysql ready for xbmc to use ===

type in

*<code>mysql -u root -p</code>

You are now in mysql administration

* <code>CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';</code>
* <code>GRANT ALL ON *.* TO 'xbmc';</code>
* <code>quit;</code>

that's it - proceed to the next step below and configure XBMC to use your FreeNAS hosted XBMC MYSQL database - I love mine!

<headertabs />

*If your library loads slowly with MySQL then you may wish to check out '''[[HOW-TO:Sync multiple libraries/Advanced notes#MySQL speed-up tweaks|these MySQL speed-up tweaks]]'''.
{{Divbox|green||'''Optional:''' For a more secure MySQL installation use these 2 commands to grant permission only to databases XBMC uses. This is useful if you plan on using your MySQL server for more than just XBMC, or if you're worried about your internal network being exposed and wanting to lock things down more, etc. If you only use the MySQL server for XBMC and that server has non-critical data on it, then this probably not necessary.
#<code>GRANT ALL ON `MyMusic%`.* TO 'xbmc'@'%' IDENTIFIED BY 'xbmc';</code>
#<code>GRANT ALL ON `MyVideos%`.* TO 'xbmc'@'%' IDENTIFIED BY 'xbmc';</code>
Note: these grants do not appear to give the xbmc user permissions to create required TRIGGERs. So either figure out how to do an additional limited grant to create TRIGGERs or use the broader grant *.* statement above. }}

{{Next|[[HOW-TO:Share libraries using MySQL/Setting up XBMC|Setting up XBMC]]}}

File:Win7firewallname.JPG

2014-01-05T09:43:37Z

Scsa20: Windows 7 Firewall, final step

Windows 7 Firewall, final step

File:Win7firewallapplyto.JPG

2014-01-05T09:42:31Z

Scsa20: Windows 7 Firewall, applying to section

Windows 7 Firewall, applying to section

File:Win7firewallallow.JPG

2014-01-05T09:41:26Z

Scsa20: Windows 7 Firewall choosing the allow action

Windows 7 Firewall choosing the allow action

File:Win7firewallrules port2.JPG

2014-01-05T09:40:03Z

Scsa20: Second part of the ports for Windows 7 Firewall

Second part of the ports for Windows 7 Firewall

File:Win7firewallrules port.JPG

2014-01-05T09:38:57Z

Scsa20: Windows 7 Firewall, selecting Port

Windows 7 Firewall, selecting Port

File:Win7firewallrules.JPG

2014-01-05T09:36:26Z

Scsa20: Windows 7 Firewall Inbound Rules

Windows 7 Firewall Inbound Rules

File:Win7firewall.JPG

2014-01-05T09:35:18Z

Scsa20: Windows Firewall for Windows 7 (same thing for Windows Server 2008)

Windows Firewall for Windows 7 (same thing for Windows Server 2008)

User:Scsa20

2014-01-05T09:07:44Z

Scsa20:

Hello everyone, name's Michael, been using XBMC for a good 2 years or so on my PC and running OpenELEC on 3 different devices. I hope I can help contribute with providing helpful information about this wonderful media center.