[AppleTV2] MySQL database / library support?

[AppleTV2] MySQL database / library support? - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: iOS & tvOS (https://forum.kodi.tv/forumdisplay.php?fid=137)
+---- Thread: [AppleTV2] MySQL database / library support? (/showthread.php?tid=91485)

Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

- one800higgins - 2011-02-23

BDPNA Wrote:I confirm this works when using official Dharma 10.0 with iOS XBMC builds, but what I am saying is I don't think iOS XBMC works once you start using the nightly post-Dharma builds on your other machines.

That's what I am trying to find out here, if anyone else is using nightly post-Dharma builds successfully with SQL alongside the iOS build.

Ohhhhh, non-iOS builds? Well, wrong forum for that, but I don't see why this functionality would be taken away in newer nightly builds of the main program.

matbor Wrote:That's how I have set mine up.

My windows file server run's MYSQL and XBMC and updates the database and thumbs. Then I have an original ATV1 and a new ATV2 that both connect to it.

Since I'm reworking my whole setup, I may just do that then. Any negatives to having your main XBMC be on a PC that you will never actually use to watch files?

- BDPNA - 2011-02-23

I actually have this set up right now.

1) MySQL database is housed on A Windows 7 machine running nightly build of XBMC.
2) Mac Mini running nightly build of XBMC.
3) iOS latest version running on Apple TV 2.
4) CrystalBuntu on Apple TV 1 running official Dharma.

Machines 1 and 2 see the MySQL DB fine, 3 and 4 do not.

What I believe is that official Dharma and post-Dharma builds do not play well together with MySQL. As soon as I threw any builds that were not Dharma 10.0 official into the mix, the DB would not be seen across all systems.

My theory now is that every machine in the setup needs to be on a post-Dharma build for the database to work on all of them. Right now machines 1 and 2 can use the DB, but 3 and 4 cannot. I assume then that the iOS build is using the MySQL code from Dharma official still, and the nightly XBMC builds (non-iOS) have branched into new MySQL code. Just a guess. Was hoping a dev might be able to confirm that though.

- IAmNotAUser - 2011-02-23

BDPNA Wrote:My theory now is that every machine in the setup needs to be on a post-Dharma build for the database to work on all of them. Right now machines 1 and 2 can use the DB, but 3 and 4 cannot. I assume then that the iOS build is using the MySQL code from Dharma official still, and the nightly XBMC builds (non-iOS) have branched into new MySQL code. Just a guess. Was hoping a dev might be able to confirm that though.

Attempting to run a Dharma 10.0 Live install alongside my nightly Ubuntu install produced errors in the log file saying something along the lines of "This version of XBMC is not compatible with the current database version" so I would think your theory is spot on.

- sandstheman - 2011-02-24

Having some major performance problems with my MySQL setup.

Currently have my main pc (Windows 7) running current stable dharma build and also running the mysql database. Along with this have another PC and laptop both running current stable dharma. These 3 PC's have no problems connecting to the db and there is no lag whatsoever when using xbmc.

Just recently got my apple tv 2, jailbroken and latest nightly of iOS dharma running on it, connected wirelessly on 802.11n. If i leave the setup as default and only add the windows shares for the video files, it runs fine, a little lag when loading up the list of movies or tv shows, i expected this, but for the most part runs fine.

However enabling the mysql settings under advancedsettings.xml and the performance plummits on the apple tv, on first load the interface is unusable for a good minute or 2, and then navigating around and going into sub menus means a delay of 30 seconds or more in some cases before it responds.

Is there something i'm missing in the mysql setup? Already have it set to skip name resolution, firewalls are all setup to allow connections on the right ports, i've disable 'extracting thumbnails' etc even tried different skins but with little success. Can't for the life of me figure out why it runs sooo slow, only have 40 or so movies and 100 or so tv show episodes, as it stands having to consider letting the apple run with it's local db rather than the central mysql db Sad

My advancedsettings.xml looks like this:

Quote:<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.2.4</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>xbmc_video</name>
</videodatabase>

<musicdatabase>
<type>mysql</type>
<host>192.168.2.4</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>xbmc_music</name>
</musicdatabase>
</advancedsettings>

- AScherff - 2011-02-24

i also have a problem to get MySQL running for my ATV2 :

This is a short look to my logfile:

Quote:14:50:42 T:125341696 M:168239104 NOTICE: Contents of special://profile/advancedsettings.xml are...
<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>192.168.123.10</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>xbmc_video</name>
</videodatabase>
</advancedsettings>
14:50:42 T:125341696 M:168230912 WARNING: VIDEO database configuration is experimental.

14:51:08 T:125341696 M:155713536 ERROR: Unable to open database: xbmc_video [1042](Can't get hostname for your address)
14:51:08 T:125341696 M:155709440 ERROR: Unable to open database at host: 192.168.123.10 db: xbmc_video (old version?)
14:51:13 T:125341696 M:155770880 ERROR: Unable to open database: xbmc_video [1042](Can't get hostname for your address)
14:51:13 T:125341696 M:155770880 ERROR: Unable to open database at host: 192.168.123.10 db: xbmc_video (old version?)

MySQL is running. My XBMC on windows has no problem to use it...

Any hint ?

Thanks

- joshua.lyon - 2011-02-24

AScherff Wrote:i also have a problem to get MySQL running for my ATV2 :

This is a short look to my logfile:

Quote:14:51:08 T:125341696 M:155713536 ERROR: Unable to open database: xbmc_video [1042](Can't get hostname for your address)
14:51:08 T:125341696 M:155709440 ERROR: Unable to open database at host: 192.168.123.10 db: xbmc_video (old version?)

MySQL is running. My XBMC on windows has no problem to use it...

Any hint ?

Thanks

You probably need to add "skip-name-resolve" to the [mysqld] section of your my.cnf file on the box that is hosting MySQL. A lot of computers have trouble resolving the Apple TV hostname (like your log says)...

PS. This was discussed earlier in this thread.

- AScherff - 2011-02-24

joshua.lyon Wrote:You probably need to add "skip-name-resolve" to the [mysqld] section of your my.cnf file on the box that is hosting MySQL.

Thanks - that was the problem - my fault Wink

- one800higgins - 2011-02-24

Are there any advantages to running MySQL on a single XBMC setup? I mean, if you don't have multiple setups in various rooms that need to stay in sync, does MySQL provide any sort of advantage over the built in DB?

The main advantage I'm curious about is speed. Would ATV2 running on a MySQL database on a separate computer run a little smoother than ATV2 with the built in DB?

- panaman - 2011-02-24

one800higgins Wrote:Are there any advantages to running MySQL on a single XBMC setup? I mean, if you don't have multiple setups in various rooms that need to stay in sync, does MySQL provide any sort of advantage over the built in DB?

The main advantage I'm curious about is speed. Would ATV2 running on a MySQL database on a separate computer run a little smoother than ATV2 with the built in DB?

there isn't any advantage if you only have one system......
Its really only practical if you have 2 or more xbmc systems

- panaman - 2011-02-24

I am still having an issue, all my thumb nails show up up but all the database info isn't showing up on my appletv
Where is the log file for this at? do I need to turn on debugging?

EDIT: NVM.. i found my problem.. i had a typo in my advancedsettings.xml
everything is working now

- joshua.lyon - 2011-02-24

one800higgins Wrote:Are there any advantages to running MySQL on a single XBMC setup? I mean, if you don't have multiple setups in various rooms that need to stay in sync, does MySQL provide any sort of advantage over the built in DB?

The main advantage I'm curious about is speed. Would ATV2 running on a MySQL database on a separate computer run a little smoother than ATV2 with the built in DB?

I can't think of any performance advantages to running the MySQL database over the built-in SQLite database. SQLite is fast and proven technology -- it works very well for a single XBMC instance. If anything, the mysql daemon would probably take up more resources than SQLite.

- one800higgins - 2011-02-24

joshua.lyon Wrote:I can't think of any performance advantages to running the MySQL database over the built-in SQLite database. SQLite is fast and proven technology -- it works very well for a single XBMC instance. If anything, the mysql daemon would probably take up more resources than SQLite.

Thanks for the info.

- transcender - 2011-02-24

one800higgins Wrote:Are there any advantages to running MySQL on a single XBMC setup? I mean, if you don't have multiple setups in various rooms that need to stay in sync, does MySQL provide any sort of advantage over the built in DB?

The main advantage I'm curious about is speed. Would ATV2 running on a MySQL database on a separate computer run a little smoother than ATV2 with the built in DB?

I'd agree the main premise lies with centrally managing one database across multiple clients. Which is pretty muted if you only have 1 client.
However, having a seperated SQL dbase is nicer if you want to query or otherwise access the dbase information which is way easier than having to SFTP your mdb over and use SQLite to do it and push it back.
I managed to do it but I had to scour google for a driver to access it.

My Working Setup - TheRealDL - 2011-02-28

This is my first post.

I wanted to be able to share my video library across our private network. I purchased two AppleTV2's for just the airplay functionality. I was happy to find a couple of better solutions than Apples iTunes Home Sharing for my 500 Movies and TV Shows. I tried the first one, easy setup, but slow performance. Found out they forked XBMC and bounced away.

Then I bit the bullet and began the journey called XBMC.

I've started a OS X Server with the native MySQL Server running and created the database and advancedsettings.zml outlined in the LifeHacker post. This was pretty good... until I changed the title or artwork of a movie from an AppleTV or MAC OS X Client. POOF. Database gone from view. Run a nightly build? POOF! database gone.

DROP database, CREATE database... I'd say half a dozen times over 2 weeks until I concluded that it was the Apple TV's having WRITE access to xbmc_video!!!!! Oooooo.

Without fanfair, and purely for myself should I ever need it, here is how I'm set up today.

First, The MySQL 'my.cnf' needs a few performance tweeks.

Code:
[client]

socket = /var/mysql/mysql.sock

[mysqld]

skip-sync-frm

skip-thread-priority

skip-name-resolve

Second, Need to create the database users. We need a read/write and read only user.

Code:
CREATE USER 'xbmc' IDENTIFIED BY 'yourpassword';

CREATE USER 'xbmcadmin' IDENTIFIED BY 'adminpassword';

...and the databases.

Code:
CREATE database xbmc_video;

CREATE database xbmc_music;

...and grant the rights allowing the Server to write and the Apple TV's read only.

Code:
GRANT ALL ON *.* TO 'xbmcadmin';

GRANT ALL ON *.* TO 'xbmcadmin'@'localhost';

GRANT SELECT ON *.* TO 'xbmc';

Everything is cool at this point EXCEPT my Wife says that she can't stop watching in one room and resume from where she left off in the other room. In other words, a showstopper Smile

So I investigated this a bit and came up with another GRANT...

Code:
GRANT ALL ON [b]xbmc_video.bookmark[/b] TO 'xbmc';

and another for Watched/Unwatched.

Code:
GRANT ALL ON xbmc_video.files to 'xbmc';

I then setup 2 SAMBA sharepoints with guest access on the OS X Server, 'Movies' and 'TV'.

During earlier setups I ran into mongo issues with thumbnails. After resolving that I wanted them outside the /Library, I put them on another drive and setup a SAMBA share point with guest access.

To get XBMC on the server pointed where I wanted it to go, I created a symbolic link in /XBMC to my chosen thumbnail folder.

Code:
ln -s /YourDesiredLocation/thumbnails/ /Users/USERNAME/Library/Application\ Support/XBMC/Thumbnails

Then I setup the advancedsettings.xml on SERVERNAME

Code:
<advancedsettings>

    <videodatabase>

        <type>mysql</type>

        <host>SERVERNAME</host>

        <port>3306</port>

        <user>xbmcadmin</user>

        <pass>ADMINPASSWORD</pass>

        <name>xbmc_video</name>

    </videodatabase> 

    <musicdatabase>

        <type>mysql</type>

        <host>SERVERNAME</host>

        <port>3306</port>

        <user>xbmcadmin</user>

        <pass>ADMINPASSWORD</pass>

        <name>xbmc_music</name>

    </musicdatabase> 

</advancedsettings>

... And the Apple TV's.

Code:
    <videodatabase>

        <type>mysql</type>

        <host>SERVERNAME</host>

        <port>3306</port>

        <user>xbmc</user>

        <pass>YOURPASSWORD</pass>

        <name>xbmc_video</name>

    </videodatabase> 

    <musicdatabase>

        <type>mysql</type>

        <host>SERVERNAME</host>

        <port>3306</port>

        <user>xbmc</user>

        <pass>YOURPASSWORD</pass>

        <name>xbmc_music</name>

    </musicdatabase> 

    <pathsubstitution>

        <substitute>        

            <from>special://thumbnails</from>

            <to>smb://SERVERNAME/xbmc.thumbnails/</to>

        </substitute>

    </pathsubstitution>

</advancedsettings>

I then used XBMC on the Server with this Sources.xml

Code:
<sources>

    <programs>

        <default pathversion="1"></default>

    </programs>

    <video>

        <default pathversion="1"></default>

        <source>

            <name>Movies</name>

            <path pathversion="1">smb://USERNAME:PASSWORD@SERVERNAME/Movies/</path>

        </source>

        <source>

            <name>TV</name>

            <path pathversion="1">smb://USERNAME:PASSWORD@SERVERNAME/TV/</path>

        </source>

    </video>

    <music>

        <default pathversion="1"></default>

    </music>

    <pictures>

        <default pathversion="1"></default>

    </pictures>

    <files>

        <default pathversion="1"></default>

    </files>

</sources>

Using XBMC, I added the 'Movie' and 'TV" as Sources and scraped the database. Reviewing and correcting the scraping errors over a day cleaned up the database and I exported from XBMC incase it went POOF again.

I'm glad to say it's ROCKING!!! The Apple TV's have "Movies" and "TV" with all artwork and resumability.

I wanted to say THANK YOU to anyone having to do with this project and kindly answering questions to those that ask.

TheRealDL

- tommyhome - 2011-03-01

one800higgins Wrote:Are there any advantages to running MySQL on a single XBMC setup? I mean, if you don't have multiple setups in various rooms that need to stay in sync, does MySQL provide any sort of advantage over the built in DB?

The main advantage I'm curious about is speed. Would ATV2 running on a MySQL database on a separate computer run a little smoother than ATV2 with the built in DB?

Yes, XBMC will load stuff faster from an external machine, I run the mysql db on my media server and only use one instance of xbmc on my laptop, the performance gain is incredible when loading huge amounts of stuff (i've set it to merge all seasons of series for example, and that loads with lightningspeed with mysql compared to the sqlite on the local machine). Setup mysql to Use some memory so it may use its awesome querycache.