Setting up XBMC and Mysql for painless upgrades
#1
When i upgraded to the latest nightly build I noticed that XBMC was trying to upgrade my database and appending a version number to the end of the database.

When I originally set up my permissions i restricted the xbmc user to the database xbmc.*

So when it tried to do the upgrades it didn't have the required permissions to create the new database XBMC57.

I decided to write this guide to make sure everyone can have painless upgraade without doing GRANT ALL PRIVILEGES ON *.*

The way I did this was to prefix all of my xbmc databases with "xbmc_"

So I now have:
Code:
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| xbmc_music         |
| xbmc_video         |
+--------------------+
4 rows in set (0.00 sec)

I then changed my advancedsettings.xml to have the following as the xbmc database:

Code:
<videodatabase>
<type>mysql</type>
<host>IPADDRESS</host>
<port>3306</port>
<user>USERNAME</user>
<pass>PASSWORD</pass>
<name>xbmc_video</name>
</videodatabase>

see the database name xbmc_video

Now all you have to do is run the following in mysql to setup permissions on your database:

Code:
GRANT USAGE ON *.* TO 'xbmc'@'192.168.1.%' IDENTIFIED BY '<INSERT PASSWORD>';
GRANT ALL PRIVILEGES ON `xbmc_%`.* TO 'xbmc'@'192.168.1.%';

My XBMC devices are all on my dhcp range so i have used the host '192.168.1.%' so that they can connect from anywhere in my LAN

Also you will see that the database is xbmc_%

This allows xbmc to do anything with it's databases and can't touch anything else that you may have running on your mysql server.

Next time you boot up your xbmc device and it needs to do a database upgrade different revisions will start appearing for example:

Code:
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| xbmc_music         |
| xbmc_video         |
| xbmc_video57       |
+--------------------+
5 rows in set (0.00 sec)


The same goes with the music database if you use it.

If you have any questions or suggestions to make this post better you are more than welcome to comment.
Reply
#2
where is this in Apple Tv 2 ?
Reply
#3
phileas50 Wrote:where is this in Apple Tv 2 ?

There is options to run mysql instead of the internal databases it is helpful if you have multiple AppleTV around the house

I think I may have put this in the wrong section anyway.
Reply
#4
Thanks for the information. You should post it in the general forum so more people get to see it. There have been problems with permissions lately with the database since the upgrade method was changed.

If you are feeling brave the wiki needs a section of setting up external databases. At present people are using external sources such as lifehacker.
Reply
#5
Addz, is this with XBMC running on a server plus on ATV2 client(s) or just XBMC running on several ATV2s using MySQL (only) on a server?

In any case, a guide or Wiki update would be MOST welcome!! Wink Wink
Reply
#6
I have to agree. Please add your info to the wiki.

I have red quite a few post regarding this topic and your is the simplest explanation.

I still haven't been able to synchronize multiple XBMCs to work. I am hoping that final will revert back to old database name setup. Right now I am not having any luck. I have been going at it for over a week.

Issues
1) permission issues - even with root
2) too many connections
3) creating database xbmc_video1, xbmc_video*
Reply
#7
http://wiki.xbmc.org/index.php?title=HOW..._libraries

Just make an account to edit.

On the XBMC Wiki we suggest that people just use "GRANT ALL ON *.* TO 'xbmc';" since most people reading the wiki will probably be using their mysql server only for XBMC. However, I was thinking of making a general MySQL page for full/advanced suggestions and explanations.
Reply
#8
So once you get a db all setup and shiny, when this upgrade occurs do you end up with a forked database?? so the atv2's will not be in sync with the pc/macs?? or will all the nightlies automagically hop to the new '57' db??

I'm not understanding why the schema change needs a new complete db... anyone got a link that explains this??
Reply
#9
mgithens Wrote:So once you get a db all setup and shiny, when this upgrade occurs do you end up with a forked database?? so the atv2's will not be in sync with the pc/macs?? or will all the nightlies automagically hop to the new '57' db??

I'm not understanding why the schema change needs a new complete db... anyone got a link that explains this??

All the code is in sync with each other. PC, Mac, iOS nightlies are all the same. When Eden (XBMC v11) comes out, they will all be the same and use the same db version (mysql or local).
Reply
#10
mgithens Wrote:So once you get a db all setup and shiny, when this upgrade occurs do you end up with a forked database?? so the atv2's will not be in sync with the pc/macs?? or will all the nightlies automagically hop to the new '57' db??

I'm not understanding why the schema change needs a new complete db... anyone got a link that explains this??

467 (PR)
AppleTV4/iPhone/iPod/iPad: HowTo find debug logs and everything else which the devs like so much: click here
HowTo setup NFS for Kodi: NFS (wiki)
HowTo configure avahi (zeroconf): Avahi_Zeroconf (wiki)
READ THE IOS FAQ!: iOS FAQ (wiki)
Reply
#11
so as long as you keep the same dates of nightlies, the database will be kept common (and usable) to all of your installs...?

I got a copy of all last night builds, gonna redo the database tonight... finger's crossed.
Reply
#12
Can anyone help me with getting my ATV2 working. I have MySQL on my Windows Home Server (which has all my media on). I have two Windows 7 PCs successfully connected to the MySQL server on the WHS and sharing the databases, but when I copy over the advancedsettings.xml and sources.xml files to the ATV it doesn't want to display them (I did initially try with just the advancedsettings.xml, btw!)

My xml files are advanced settings;
PHP Code:
<advancedsettings>
    <
videodatabase>
        <
type>mysql</type>
        <
host>smb://HOMESERVER/</host>
        
<port>3306</port>
        <
user>xbmc</user>
        <
pass>pass</pass>
        <
name>xbmc_video</name>
    </
videodatabase

    <
musicdatabase>
        <
type>mysql</type>
        <
host>smb://HOMESERVER/</host>
        
<port>3306</port>
        <
user>xbmc</user>
        <
pass>pass</pass>
        <
name>xbmc_music</name>
    </
musicdatabase>
</
advancedsettings
Sources;
PHP Code:
<sources>
    <
programs>
        <default 
pathversion="1"></default>
    </
programs>
    <
video>
        <default 
pathversion="1"></default>
        <
source>
            <
name>Latest movies to watch</name>
            <
path pathversion="1">smb://HOMESERVER/New Movies &amp; TV Shows/New Movies To Watch/Latest movies to watch/</path>
        
</source>
        <
source>
            <
name>My Favourite Movies</name>
            <
path pathversion="1">smb://HOMESERVER/Movie Archive/My Favourite Movies/</path>
        
</source>
        <
source>
            <
name>My Movie Archive</name>
            <
path pathversion="1">smb://HOMESERVER/Movie Archive/50&apos;s &amp; earlier Movies/</path>
            
<path pathversion="1">smb://HOMESERVER/Movie Archive/60&apos;s Movies/</path>
            
<path pathversion="1">smb://HOMESERVER/Movie Archive/70&apos;s Movies/</path>
            
<path pathversion="1">smb://HOMESERVER/Movie Archive/80&apos;s Movies/</path>
            
<path pathversion="1">smb://HOMESERVER/Movie Archive/90&apos;s Movies/</path>
            
<path pathversion="1">smb://HOMESERVER/Movie Archive/Modern Movies/</path>
        
</source>
        <
source>
            <
name>Bond Movies</name>
            <
path pathversion="1">smb://HOMESERVER/Movie Archive/Bond Movies/</path>
        
</source>
        <
source>
            <
name>New Movies to Watch Older</name>
            <
path pathversion="1">smb://HOMESERVER/New Movies &amp; TV Shows/New Movies To Watch/New Movies to Watch - Older/</path>
            
<path pathversion="1">smb://HOMESERVER/New Movies &amp; TV Shows/New Movies To Watch/New Movies to Watch - Foreign/</path>
        
</source>
        <
source>
            <
name>My Current TV Shows</name>
            <
path pathversion="1">smb://HOMESERVER/New Movies &amp; TV Shows/My Current TV Shows/</path>
        
</source>
    </
video>
    <
music>
        <default 
pathversion="1"></default>
    </
music>
    <
pictures>
        <default 
pathversion="1"></default>
    </
pictures>
    <
files>
        <default 
pathversion="1"></default>
    </
files>
</
sources

Do I need to change the network label format for ATV?

Also, I'm using WinSCP to SSH into the ATV2...I presume that is fine?

Thanks! :confused2:
Reply
#13
have you done the mysql INI tweak about name resolve? Its gonna be MEGA MEGA slow without - took my ATV2s about 45 seconds to boot without that.

Plus make sure all your references are to an IP address? And let your ATV2s create the database?
Reply
#14
No I haven't, I'll have a look about the ini tweak.

So drop the HOMESERVER for the IP. And let ATV2 create the database? Do you mean ditch them all and re-create it in the ATV2 and copy those back to the Win7 PCs? Wouldn't be too difficult.
Reply
#15
remove the smb:// in the database configuration and only put the ip of your mysql there in between the host tags...
AppleTV4/iPhone/iPod/iPad: HowTo find debug logs and everything else which the devs like so much: click here
HowTo setup NFS for Kodi: NFS (wiki)
HowTo configure avahi (zeroconf): Avahi_Zeroconf (wiki)
READ THE IOS FAQ!: iOS FAQ (wiki)
Reply
 
Thread Rating:
  • 0 Vote(s) - 0 Average



Logout Mark Read Team Forum Stats Members Help
Setting up XBMC and Mysql for painless upgrades00