MySQL Database: Multiple XBMC HTPC's with 1 Shared Library

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
protogen Offline
Junior Member
Posts: 5
Joined: Jan 2011
Reputation: 0
Post: #31
czfj5r Wrote:"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=xbmc --password=xbmc --add-drop-table --all-databases --result-file=C:\Temp\backup-%DATE%.sql --dump-date

Since you're dumping everything in one go, you should probably add --lock-all-tables as well, just to be safe.
find quote
JohnVM Offline
Junior Member
Posts: 2
Joined: Jan 2011
Reputation: 0
Post: #32
I have about 1400 videos in my movie library and about 5000 tv episodes. It's pretty slow to load the initial list of movies/tv shows. Do you guys have any reccos for improving tv/movie performance with indexes to create?
find quote
JohnVM Offline
Junior Member
Posts: 2
Joined: Jan 2011
Reputation: 0
Post: #33
Looking at mysql profiler, some of the following queries look partciularly bad. I'm a mysql noob though, not sure what indexes to make to fix...


Quote:select strPath,noUpdate from path where idPath in (select idPath from files join movie on movie.idFile=files.idFile) and idPath NOT in (select idPath from tvshowlinkpath) and idPath NOT in (select idPath from files where strFileName like 'video_ts.ifo') and idPath NOT in (select idPath from files where strFileName like 'index.bdmv') and strPath NOT like 'multipath://%%' and strContent NOT in ('movies', 'tvshows', 'None') order by strPath

Quote:select * from movieview WHERE movieview.idMovie NOT IN (SELECT idMovie FROM setlinkmovie s1 JOIN(SELECT idSet, COUNT(1) AS c FROM setlinkmovie GROUP BY idSet HAVING c>1) s2 ON s2.idSet=s1.idSet)

Quote:select idActor from actors where strActor like 'Brian van Holt'

Quote:select * from episodeview order by idEpisode desc limit 25
find quote
Tight_wad Offline
Fan
Posts: 361
Joined: Oct 2010
Reputation: 4
Post: #34
craigd Wrote:There are a number of common issues when first setting up I’ll track as many of them here...

...2. Synchronise folders so copies of files are held centrally and copies are cascaded to all other htpc's. Using tools such as synctoy, rsynch, dropbox folders can be set to replicate manually or automatically depending on selected tool.

pros:
guaranteed best performance as files are cached locally
simple to set up

cons:
images not available until sync completes
not all tools automate easily and some cost money

Question about this part of doing Mysql.

I would like to keep my Thumbs local on each of my 3 zboxes, but I don't thing that I add stuff often enough to warrant using and kind of resync. What I do now is to use a Windows vista with XBMC to do all of my backend work like rips and scaping. Then I export the library (separate files), modify and re-scape as needed the new movies on to my zboxes.

Is it just a matter of copying the Thumbs folder from the vista computer to each of my zboxes to get started? And when I want to add a movie, just go to each zbox and in file view, do "i" and then a "refresh" to gain the Thumbs?
find quote
haifischjunge Offline
Member
Posts: 85
Joined: Oct 2008
Reputation: 0
Post: #35
you should have a look over the queries and set some indizes, this should speed things up.

episodeview and movieview seem like good candidates.
find quote
haifischjunge Offline
Member
Posts: 85
Joined: Oct 2008
Reputation: 0
Post: #36
no matter what I do, it seems that some tables are not created when I try to start it seems that theres a problem with accessing the music db

PHP Code:
ERRORSQLThe table does not exist Queryselect from songview where strPath like 'smb://10.10.10.100:445/Musik/iTunes/' 
find quote
TiGmsm Offline
Junior Member
Posts: 19
Joined: Dec 2010
Reputation: 0
Location: San Rafael, CA USA
Post: #37
Hello,

An xbmc newbie here and I'm having trouble getting this to work with two instances of xbmc installed on Windows 7 (my laptop and my desktop) are using the mySQL database, the shared aspect is missing an any noticeable way.

I have checked every setting that I can find in the GUI but for the life of me cannot find how the two machines are sharing any data. I can see that both machines hit the mySQL database using mySQL Workbench. Both machines show connections to the same database when expected (xbmc_video when accessing movies).

The very least of what I expect to see but do not is "watched" status in common between the two machines.

I am also not seeing any of the following:

1. Both machines show the the same sources where I added the source to only one of the two.

2. Paused status for any particular movie on one instance show in some meaningful way on the other.

I suspect there is some fundamental setting that I am missing. Anyone have any tips that might help track down this problem?

Thanks!

-Bob
find quote
RockDawg Offline
Posting Freak
Posts: 1,431
Joined: Jun 2005
Reputation: 1
Post: #38
TiGmsm Wrote:Hello,

An xbmc newbie here and I'm having trouble getting this to work with two instances of xbmc installed on Windows 7 (my laptop and my desktop) are using the mySQL database, the shared aspect is missing an any noticeable way.

I have checked every setting that I can find in the GUI but for the life of me cannot find how the two machines are sharing any data. I can see that both machines hit the mySQL database using mySQL Workbench. Both machines show connections to the same database when expected (xbmc_video when accessing movies).

The very least of what I expect to see but do not is "watched" status in common between the two machines.

I am also not seeing any of the following:

1. Both machines show the the same sources where I added the source to only one of the two.

2. Paused status for any particular movie on one instance show in some meaningful way on the other.

I suspect there is some fundamental setting that I am missing. Anyone have any tips that might help track down this problem?

Thanks!

-Bob

1. You need to add the sources seperately for each machine.

2. Not sure exactly what you mean by paused status. You can't pause on one box and resume on another per se. What you can do is stop the movie in one room and resume from the same point on another box.

You should definitely see synchronized watch status. If you're not getting that, I would say your central db isn;t set up correctly on all machines.
find quote
TiGmsm Offline
Junior Member
Posts: 19
Joined: Dec 2010
Reputation: 0
Location: San Rafael, CA USA
Post: #39
This is a helpful reply. Thank you.

RockDawg Wrote:1. You need to add the sources seperately for each machine.

2. Not sure exactly what you mean by paused status. You can't pause on one box and resume on another per se. What you can do is stop the movie in one room and resume from the same point on another box.

You should definitely see synchronized watch status. If you're not getting that, I would say your central db isn;t set up correctly on all machines.

Not only setting up the sources on each machine separately but also setting the view on each machine to be similar enough that the synchronized status can be noticed.

The subtlety between "paused" and "stopped" alluded me until you mentioned it in the previous post. If I'm watching a video downstairs and 'pause" it then go upstairs to finish it in the bedroom, it would only work if I "resume" playback. This is a well hidden setting that appears in the Settings-Video-Playback screen (I think it was that screen..) It's not easy to resume a movie without discovering this well hidden setting.

I have added the same sources to all four of my xbmc machines (downstairs AppleTV, upstairs ATV, laptop Winddows7, desktop W7). and they all seem to in synch regarding watched,/unwatched status using Dharma and Alask Revisited.

Thank for you help.

-Bob
find quote
Avigrace Offline
Senior Member
Posts: 132
Joined: Mar 2009
Reputation: 0
Post: #40
I would greatly appreciate if someone with good Mysql knowledge could write up a quick guide on how to set up indexing to speed up the SQL XBMC video database.
find quote
Philmatic Offline
Fan
Posts: 451
Joined: Nov 2009
Reputation: 0
Location: Santa Barbara, CA
Post: #41
I just create symlinks for each of the XBMC data folders to point to a share on my WHS file server. Go to your user profile folder on Windows (Vista/Win 7: C:\Users\Username\AppData\Roaming\XBMC) and delete all the files (Or move them if this is the master database) then run the following from a command prompt.

Code:
mklink /d addons \\WHSServer\Users\HTPC\addons
mklink /d cache \\WHSServer\Users\HTPC\cache
mklink /d media \\WHSServer\Users\HTPC\media
mklink /d sounds \\WHSServer\Users\HTPC\sounds
mklink /d system \\WHSServer\Users\HTPC\system
mklink /d userdata \\WHSServer\Users\HTPC\userdata

This solves the issue of the log file needing to be exclusively locked by the XBMC executable, but still lets me share the thumbs and fanart across all my instances.

Disclaimer: I would NOT try this over wireless, I barely notice a difference on 100mbit LAN, but I imagine wireless would be extremely sluggish.

Living Room: AppleTV 1st Gen / Broadcom CrystalHD / XBMC 12.3 Frodo on Crystalbuntu 2.0
Bedroom: AppleTV 1st Gen / Broadcom CrystalHD / XBMC 12.3 Frodo on Crystalbuntu 2.0
Home Office: AppleTV 1st Gen / Broadcom CrystalHD / XBMC 12.3 Frodo on Crystalbuntu 2.0
Windows Home Server: Intel Core i3-2100T / 8GB Ram / 32TB / MySQL / StableBit DrivePool on WHS 2011
(This post was last modified: 2011-01-19 21:07 by Philmatic.)
find quote
alexxxo Offline
Junior Member
Posts: 6
Joined: Jan 2011
Reputation: 0
Post: #42
hi,

is there a way to get user/password passed to my linux server when browsing / playing files in database mode (file mode working perfectly). i already posted more details in http://forum.xbmc.org/showpost.php?p=695...stcount=83

i do not want to store credentials on windows, any other chance to get this working?

thanks in advance
find quote
avus m3 Offline
Senior Member
Posts: 246
Joined: May 2010
Reputation: 0
Post: #43
Is anyone having issues creating movie sets once switched over to SQL? I created one set that was imported into the SQL DB a long time ago. I have recently tried creating other sets (like all the Bond movies) and when the movies are added into the DB the set info is ignored or something?
find quote
castortray Offline
Fan
Posts: 501
Joined: May 2009
Reputation: 0
Post: #44
hi,

Maybe someone know how to export just Watched Data from MySQL as in this plugin:
http://forum.xbmc.org/showthread.php?tid=62874

addon for Dharma (working with MyVideos34.db)
http://xbmchuscraper.googlecode.com/file...d.flag.zip

it create simple watched.xml file in:

C:\Users\%username%\AppData\Roaming\XBMC\userdata\Database
find quote
castortray Offline
Fan
Posts: 501
Joined: May 2009
Reputation: 0
Post: #45
protogen Wrote:Since you're dumping everything in one go, you should probably add --lock-all-tables as well, just to be safe.

works perfectly:

Code:
@echo off
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" --user=xbmc --password=xbmc --lock-all-tables --add-drop-table --all-databases --result-file=C:\MySQLBackup\backup-%DATE%.sql --dump-date
forfiles /p c:\MySQLBackup\ /s /m backup-*.* /d -7 /c "cmd /c del @path"
XCOPY "c:\MySQLBackup" "\\192.168.2.11\Download\MySQLBackup" /D /E /C /R /I /K /Y

stupid question, how to import dumped database ?
find quote
Post Reply