Talk:MySQL

From Official Kodi Wiki
Revision as of 10:16, 5 January 2014 by Scsa20 (talk | contribs) (→‎Windows Firewall Rules: new section)
Jump to navigation Jump to search

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. -- Ned Scott 04:37, 15 November 2011 (EST)

Other useful links

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

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

--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. "CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';" 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! -- 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! 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. -- 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)

       <!-- profiles for iPad: everything except guisettings  -->
       <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>    

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. -- 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 ) 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 --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. -- 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. 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. 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

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? -- 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. 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

<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>

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

passwords.xml

<passwords>
    <path>
        <from pathversion="1">smb://192.168.0.1/</from>
        <to pathversion="1">smb://username:[email protected]/</to>
    </path>
</passwords>

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://[email protected]".

All three files go in the same location in the userdata folder. Restart XBMC and you're done! 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? Scsa20 (talk) 05:16, 5 January 2014 (EST)