+- 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: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: MySQL Database: Multiple XBMC HTPC's with 1 Shared Library (/showthread.php?tid=85654)
- tamplan - 2011-08-05
TugboatBill Wrote:I'm using the MySQL centralized database and sets seem to be working when managed through EMM. I did have some issues if the movies were already in the system - I had to remove them & then rescan. They then appear correctly.
Hi,
If you want to modify the database with phpmyadmin :
- table 'movies', field 'c10' is the sort title and note the 'idMovie' field.
- table sets, you can add a set (entering the field 'strSet') and note the 'idSet' field.
- table 'setlinkmovie', add the previous 'idSet' and 'idMovie' fields.
Hope this helps.
Regards,
- canadave - 2011-08-15
**** EDIT ****
Never mind, figured out the question I had.
- phsyraxion - 2011-08-19
This is a great solution and I have been testing this out between 2 pcs with success so far. I have only tested with a music library of 4000 songs and I haven't loaded much detail into the database but as soon as details are updated on one machine it's available on the other.
On the side of the thumbs directory, I am on WinXP on my HTPC machines and my server has a number of looping scripts (batch files) that do backups and monitoring of all machines on my network. I constructed a simple looping script that checks every 2 minutes for a HTPC online. It copies the thumb files to a central location on the server and ONLY copies files that are found to be NEWER. It then copies back any files it has that are newer to the HTPC.
This allows every machine to keep a local thumb directory but remain synched. If I change a fanart image, cover, banner or even folder image it is instantly updated on every other machine in a couple of minutes.
The settings are all separate for each HTPC and the script has the ability to have set what thumbs you would like to sync (Video, Music, Generated and General). Additionally either another script or an extra few entries could also easily sync other files such as RSSFeeds, Add-ons and favourites.
Now this all works great on a music library and I have 2 machines that stay identically setup no matter what changes I make to one of them (GREAT!!!).
QUESTIONS
I want to schedule regular backups of the SQL database. I found in this thread a simple way of backing up the whole database but I would like to know how you backup just a table (such as just videos)? This would be a handy thing in case something went wrong with one table you could restore just that one.
Secondly, how do you restore a table to mysql once it's been extracted?
Thirdly, if you need to edit details for an item, how is this easiest done. My example is a music album thats been scrapped has the wrong album overview and I want to change it. How do people tackle this?
Lastly for now, what is the tool for maintaining your TV Show, Movie and TV Shows that covers all of them? I'd like to look at generating as much data to go on the media drives so re-scanning is quicker.
I'd love to hear from anyones usage of this SQL setup that has large librarys over:
5000 in music files
10,000 in TV shows
300 in Movies
If anyone is using this SQL setup with librarys of about that size, what kind of performance are you getting and what is your setup.
If anyone is interested in the batch script process I use to keep thumbs synched I am happy to post that once it's been tested on a larger library. Baby steps at the moment.
- ederson - 2011-08-19
I have similar db
~300movies
~30000 music tracks
~3000 tv episodes
the files are stored on win7 machine
the htpc is ... so old i can`t remember
.. winxp they connect over a 54mbps wifi (the real speed is mch less but i can watch hd content without problems)
i wasn`t able to use symbolic links
whenever i update the database (i always update from the win7 machine) i sync the thumbs using a simple free programm i`ve found.
the moment i pressed submit i got an idea ... the problem is tha i used localhost instead of ip... everything is ok now
i guess it`s not the best way but it gets the job done
but i`d like to test your script
and something else
I`ve decided to use a nightly build but i can`t get it to connect with the db
i know the dharma and pre-eden dbs are not compatible so i tried it from scratch but still the same problem. any ideas?
- azrelix - 2011-08-20
jcato Wrote:Not sure if this is related, but I recently setup a central MySQL database on my unRaid server. I imported my library and everything works fine. This didn't setup my sources, so I'm trying to do that and have a problem. I set the content, but it won't stick. I add my movie source, set content to Movies, click Ok. If I go back into set content, it is set to None. I can't scan the source, since there isn't a content type set. Any ideas?
Thanks
Hi,
Im getting the same issue, add source and set the content, when u click ok and it asks if you want to refresh the database now you click "yes" and takes about 1/2 a second to scan the database and then when you goto the set content again its not set...
the log file is coming up with an error:
13:20:40 T:1760 M:3221958656 ERROR: SQL: Undefined MySQL error: Code (1062)
Query: insert into path (idPath, strPath, strContent, strScraper) values (NULL,'C:\\TVShows\\','','')
13:20:40 T:1760 M:3221958656 ERROR: CVideoDatabase::AddPath unable to addpath (insert into path (idPath, strPath, strContent, strScraper) values (NULL,'C:\\TVShows\\','',''))
13:20:40 T:3996 M:3221774336 NOTICE: VideoInfoScanner: Starting scan ..
13:20:40 T:3996 M:3221774336 NOTICE: VideoInfoScanner: Finished scan. Scanning for video info took 00:00
Currently using XBMC for windows Version 10.1 (compiled: Mar 8 2011)
if any could point me in the right direction that would be good.
EDIT:
fixed the problem was a very simple one in the end.. you cannot add "Local" path to mysql.. you have to add a shared path so instead of adding "c:\tvshows\" add "//servername/tvshows/" you cannot use "\" in a mysql database.
Cheers,
Azrelix
shared thumbnails - a crude way - boblablah - 2011-09-26
I use xbmclive on 3 different pcs in the house. I have them all backended to a mysql instance running on freenas (freebsd)
using tutorials elsewhereI got the freenas box doing all the downloading and renaming I could handle via sab, couchpotato, headphones, sickbeard etc etc...
my movies and tvshows are all centrally located on this server which i struggled to get mysql running right on....
anyways, on xbmclive, i was able to brutalize the samba install to just get the mount.cifs VFS helper (that is missing from xbmclive).
i downloaded the deb pkg from here:
http://archive.ubuntu.com/ubuntu/pool/main/s/samba/smbfs_3.4.7~dfsg-1ubuntu3_i386.deb or sub in _amd64.deb depending on your arch...
when i ran the dpkg -i command it failed of course as this has so many dependencies, but it did extract mount.cifs into /sbin.
when I attempted the command line mount, it actually worked on a system level outside XBMC, which means at boot, I can have this run in /etc/fstab to automount a shared cifs share as /home/~user/.xbmc/userdata/Thumbnails/
The only reason you can't do this very easily s because the live version is stripped down so far that a lot of the dependencies that are required to do things like this on a system level outside xbmc are simply not there....
My suggestion would be to simply add the vfs helpers from a full blown i386 or amd64 ubuntu distro to your path (somewhere like /sbin) and this should allow you to properly automount a share in place of the default local xbmc thumbnails folder.
though I would share
I know my installations of xbmclive all have no mount.xxxx helper files - maybe yours does..
try find / -name mount.*
This only showed me that the system can locally mount an ntfs drive or an ssh filesystem (fuse) by default.
anyways, it's a crappy hack but if you're able to dump your thumb cache to a samba/cifs share, this will ate least get you there....
- StanLee - 2011-09-26
Hi I would like to get some help.. The infomus "THE"..
So I have alot of movies starting with THE.. in the local DB of Mysql using the Remove THE statment in the Menu.. worked. but in MYSQL not working for me..
So is there a way I can do a query that wil list all movies in Table movie collum c00 and then make a copy to c10 with a , ant the end and then THE..
Like this c00 The Beaver > c10 Beaver, The
?Would this sort out the The problem when listing movies in Libary mode.. ?
- jchaupitre - 2011-09-27
Working great to manage my media library in one location, play all media across the house, centralize play count and resume settings, etc, love it! Now, I have a very large movie library (>1000 movies) and I've been experiencing slow performances loading any lists, be it by genre, years, etc since I switched to this setting contrarily to a local install. Loading can take from a few secs to several minutes depending on the number of movies in scope of the request.
Basically anytime you load a list;
1- the "working..." message shows up with a spinning circle
2- the circle will then freeze, essentially XBMC is unresponsive
3- and then the list shows up
I first thought MySQL was the culprit, I was noticing some slow queries, so I upgraded the memory of my NAS thus giving more caching memory to MySQL, and it did improve the performances, but only of 1 above. I tweaked some more MySQL settings and got so more juice out of 1 above, but 2 was still as slow. So I'm wondering what I can do to speed up 2, which seems unrelated to MySQL.
The other part of my new settings are the thumbnails which are shared across all computers through a directory symbolic link (mklink /D). The posts here seem to indicate slow performance when loading the image but not the list. Is XBMC caching those thumbnails when loading the list? Can anyone help me understand what happens at step 2 above?
- StanLee - 2011-09-27
StanLee Wrote:Hi I would like to get some help.. The infomus "THE"..
So I have alot of movies starting with THE.. in the local DB of Mysql using the Remove THE statment in the Menu.. worked. but in MYSQL not working for me..
So is there a way I can do a query that wil list all movies in Table movie collum c00 and then make a copy to c10 with a , ant the end and then THE..
Like this c00 The Beaver > c10 Beaver, The
Would this sort out the The problem when listing movies in Libary mode.. ?
Yes it did.. BUMP.. I tested it manualy editing the MySQL tabel movie.. But still if theres a MYSQL Query guru out ther please make the script for this.. or better Let it be part of XBMC. when you tick the Settings | Apperance | File Lists "Ignore articles when sorting (e.g "the")
Ther should be a MYSQL line there that does the copy of c00 to c10 with , The on the end insted of beginning...
Hmm scratch what I said her maby.. (looks like in Eden all is working) but I had to Empty the DB totaly loosing all Playcount etc.. because it would not uppgrade the DB to latest ver..
Still better..
I still use Ember MM.. and I went to UMM.. I like the feel and gui of Ember alot better. So scraping the DB was not to much pain.. But loosing the Play count IS....
- seth.feinberg - 2011-09-29
craigd Wrote:1. Delay of 3 minutes when you click on an Artist in Library mode.
Fixed by adding index: CREATE INDEX test_idx ON song(idAlbum);
2. Delay of 20 seconds when you click on "Artists"
Fixed byadding index: alter table song add index idArtist(idArtist);
3. Delay of 10 seconds when you click on "Genres"
Fixed by: alter table song add index idGenre(idGenre);
****update 13/04/2011: indexes how to by Krazypoloc****
********
First, off thanks to everyone in the xbmc community for all the help.
I actually have this all set up and love the functionality. I'm running MySQL on a synology 1511+ serving the media (just movies and TV shows right now) to a HTPC and an ATV2 both running the sep 22nd night build of xbmc. The HTPC works snappy as ever but the ATV2 has the delays that others have been complaining about. There is about a 30sec delay when clicking on Movies or TV Shows from the home screen for the actual library to show up and it is pretty slow when scrolling through once in. I have also experienced a good number of crashes. Here is a paste of my crashreporter (I'm sorry if I didn't do this right): http://pastebin.com/wr1DgB25. I'm not sure how to read it but I see the line memorypressure so I'm assuming something in my setup is testing the limits of the crappy ATV2 specs, but have seen multiple people say its working great on an ATV2.
I applied all the suggestions in @craigds post (http://forum.xbmc.org/showpost.php?p=646799&postcount=5) from @Krazypoloc but I'm pretty new to MySQL and am not sure if I did it correctly. I clicked on the SQL tab of the appropriate database in myPHPadmin and put them there and they said they were applied successfully. I don't know how to check but when I tried to reenter them because I wasn't sure if I'd done all of them, myPHPadmin said it was a duplicate. I also added a my.cnf file to the etc directory on my Synology with the putty and the vi command to mimic this one (http://forum.xbmc.org/showpost.php?p=703298&postcount=3). It saved fine, but I still haven't experienced any increase in performance.
Does anybody have any suggestions for how I might speed up the database access on my ATV2? I used a pathsubstitution in the advancedsettings.xml instead of a symlink for thumbnails, is it possible that's the culprit? Also I read some people say that they had slow response when they imported their old library after creating the MySQL databases instead of creating them from scratch. I'm hesitant to redo the whole process since its working so well on my HTPC and because I'm not excited about remarking everything as watched. Should I try this?
If someone could help me identify how I view a log (or something) that would give me better insight to exactly what in the process is slowing everything down. I will paste my ATV2's xbmc log if requested, but that doesn't seem to show much. I'm assuming there is some log in MySQL that will show me how long each query (?) is taking. Any help would be, and already is, greatly appreciated. Thanks again all
- Headcase_Fargon - 2011-09-29
Not sure how I missed this thread before, but I've been running into all kinds of library problems using a MySQL database and only just recently determined that it was the cause of all my woes. A whole thread of me whining about these issues can be found here.
The long and short of it is that when I use the MySQL database my Movies and TV libraries look like absolute ass. Random movies refuse to display cover posters, instead showing squished screencaps from the movie file. TV series display the season-all image for ALL seasons of the show rather than individual season posters (despite the presence of season01.tbn, season02.tbn, etc).
Skimming through this thread am I correct in assuming that this is still an issue and that there's no simple fix/work-around? I'm seeing posts about scripts that automate the copying of posters back and forth across HTPCs and the like, solutions that I wouldn't call simple. Is that the case?
Honestly the only feature of MySQL that I'm interested in is the sync'ed watched flags and resume points. Is there another way to accomplish this without completely turning my libraries (including images) over to MySQL?
Appreciate any feedback.
- atari800 - 2011-09-29
I dont have any of those issues
I am using MySql - shared between 3 Apple TV1's running Ubuntu (well look at my signature for my setup)
I am sharing my Thumbnails directory on all machines
and
I am using Transparency!
I get all covers on Random movies, and last played
I get Seasonal covers for tv shows (The Honeymooners, Three Stooges, Office, MASH, blah blah blah)
I am using default builds of XBMC (not nightly) on all machines
Let me read your rant and comment again.
I just wanted to state I dont have any issues like you are experiencing at all
- jays - 2011-09-30
Do the nightly builds use a different layout than 10.1 for the data?
I ask because right now I have XBMC shared in my living room, den and bedroom. The den server holds the MySQL database and is the "master" xbmc install (receives update commands from Sickbeard etc).
I recently found out I needed to run a nightly build on the living room PC if I wanted hardware acceleration for my Intel h67 chipset but when I installed the nightly build it wouldn't see my library. I verified the advancedsettings.xml file was still there after the upgrade but didn't dig much deeper into it. I could see my files by going into videos but the option to show Movies and TV Shows on the main menu was disabled and I had no way to view the various library views.
After reinstalling 10.1 on the living room htpc everything went back to working perfectly (except obviously the hardware acceleration that isn't supported for my chipset in that version). I'm using symlinks for my thumbnails folders on the two remote PC's if that matters.
- Headcase_Fargon - 2011-09-30
I noticed this during my exhaustive experimentation with two HTPCs. I seem to recall there was a very obvious-looking error in the log file about the database not adhering to expected structure or somesuch. Pre-Eden and Dharma definitely appear to use different formats.
- jays - 2011-09-30
Any way to import my library data into the new data layout? If I do an export on the main server install, install the nightly build, then do an import will that take care of it?