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

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
rubenm Offline
Junior Member
Posts: 36
Joined: Jul 2010
Reputation: 0
Location: beijing, china
Post: #46
hi everyone.

[RANT]

so. ok. some filesystems mess up "special" characters. (you know, for chinese, there's nothing 'special' about the majority chinese characters, and me, i dont feel like an 'umlaut' is in any way weird.

now. pardon my french, but 1992 is long gone, so why the fuck would anyone do anything in anything but utf-8 (or utf-16)?

i mean, this is such an AWFUL thing to do.

really, i could hardly believe it.

no 'sorry, but until we figured out some potential problems, it's latin1 only' ... not even a 'it might be unsuitable for some of you, but there's no other way'

i mean, come on guys - latin1 doesnt even include russian or greek. let alone japanese, chinese, korean, thai. if you count all people in the world that DONT use latin1 characters on a day-to-day basis, than YOU are the fucking MINORITY ... by far.

[/RANT]

sorry. but euro/us centric stuff really bugs me.
find quote
Plaguester Offline
Senior Member
Posts: 262
Joined: May 2010
Reputation: 7
Post: #47
castortray Wrote: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 ?

Linux would be
Code:
$ mysql --user=xbmc --password=xbmc < path/to/mysqlBackup

From what you have there, I assume the Windows equivalent is:
Code:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=xbmc --password=xbmc < "\\192.168.2.11\Downloads\MySQLBackup"

The backup is basically a big SQL file. The "<" operator sends a file to the program as input.

HTPC 1 - Zotac ZBOX ID80U | 4GB RAM | 64GB SSD | Openelec | Confluence
HTPC 2 - Zotac ZBOX ID41U | 4GB RAM | 60GB SSD | Openelec | Confluence
Server - unRAID Server | 3 x 2TB WD Green HDD, 1TB WD Black HDD (Cache) | Sabnzbd | CouchPotato | Sickbeard
find quote
TugboatBill Offline
Posting Freak
Posts: 829
Joined: Oct 2009
Reputation: 7
Post: #48
rubenm Wrote:hi everyone.

[RANT]

so. ok. some filesystems mess up "special" characters. (you know, for chinese, there's nothing 'special' about the majority chinese characters, and me, i dont feel like an 'umlaut' is in any way weird.

now. pardon my french, but 1992 is long gone, so why the fuck would anyone do anything in anything but utf-8 (or utf-16)?

i mean, this is such an AWFUL thing to do.

really, i could hardly believe it.

no 'sorry, but until we figured out some potential problems, it's latin1 only' ... not even a 'it might be unsuitable for some of you, but there's no other way'

i mean, come on guys - latin1 doesnt even include russian or greek. let alone japanese, chinese, korean, thai. if you count all people in the world that DONT use latin1 characters on a day-to-day basis, than YOU are the fucking MINORITY ... by far.

[/RANT]

sorry. but euro/us centric stuff really bugs me.

You seem to have the profanity down quite well. Confused
find quote
fattoony Offline
Junior Member
Posts: 35
Joined: Jan 2011
Reputation: 0
Post: #49
Hi, would it be possible to have the database only take care of...

 Stop watching a video and resume from same point in any room
 Watched flags sync across all htpc's

leaving each media center to scan and store new content (thumbs posters fan-art etc) localy.

Would this not cut out the overhead/lag in load times. but keep the main advantages of having "flags" and "resume from same point" synced across xbmc's.
find quote
castortray Offline
Fan
Posts: 501
Joined: May 2009
Reputation: 0
Post: #50
Plaguester Wrote:From what you have there, I assume the Windows equivalent is:
Code:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=xbmc --password=xbmc < "\\192.168.2.11\Downloads\MySQLBackup"

The backup is basically a big SQL file. The "<" operator sends a file to the program as input.

many thanks,
I will check it, and let you know about results.

Regards
find quote
cmonpellier Offline
Member
Posts: 83
Joined: Oct 2009
Reputation: 0
Post: #51
rubenm Wrote:hi everyone.

[RANT]

so. ok. some filesystems mess up "special" characters. (you know, for chinese, there's nothing 'special' about the majority chinese characters, and me, i dont feel like an 'umlaut' is in any way weird.

now. pardon my french, but 1992 is long gone, so why the fuck would anyone do anything in anything but utf-8 (or utf-16)?

i mean, this is such an AWFUL thing to do.

really, i could hardly believe it.

no 'sorry, but until we figured out some potential problems, it's latin1 only' ... not even a 'it might be unsuitable for some of you, but there's no other way'

i mean, come on guys - latin1 doesnt even include russian or greek. let alone japanese, chinese, korean, thai. if you count all people in the world that DONT use latin1 characters on a day-to-day basis, than YOU are the fucking MINORITY ... by far.

[/RANT]

sorry. but euro/us centric stuff really bugs me.

noone is forcing you to use it, nor preventing you to contribute to it, nor to write to own. It's done by dedicated individuals for everyone to enjoy, if the only contribution you can give is RANT then I think that this product and forum will live without it.
find quote
castortray Offline
Fan
Posts: 501
Joined: May 2009
Reputation: 0
Post: #52
Plaguester Wrote:From what you have there, I assume the Windows equivalent is:
Code:
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe" --user=xbmc --password=xbmc < "\\192.168.2.11\Download\MySQLBackup\filename.sql"

The backup is basically a big SQL file. The "<" operator sends a file to the program as input.

thanks,
today I tested your backup restore solution for MySQL in Windows 7 and it's working prefectly !!
thank you very much, you save me many hours of work Big Grin
find quote
tormi Offline
Senior Member
Posts: 106
Joined: Nov 2010
Reputation: 0
Post: #53
Hi!

I just tried out this, not knowing multiple profiles was not supported. Giving me a headache at my end, but I have reverted my system to before my testing began.

Is there at all any sensible way to have multiple profiles in this setup? I DO need my own movies separated from my kids movies so without that possibility I have to skip this.

So can anybody explain to me how I setup MySQL database sharing and multiple user profiles? I would be very appreciative.

If not is there any other systems like XBMC out there with this supported? I really don't want to switch but I'd like to have my watched status changed + possibility to resume playback from one HTPC to another.
find quote
tormi Offline
Senior Member
Posts: 106
Joined: Nov 2010
Reputation: 0
Post: #54
Seems like this thread is dying? Is everybody really getting this to work?

I'm getting quite frustrated now, I think I have tried everything.

I setup my advancedsettings file with info to my server as this:

Code:
<type>mysql</type>
<host>localhost</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>xbmc_master_video</name>
</videodatabase>

When I import my movie/tv show database it is only partly filled. For instance the tv show Band of Brothers only came up with one episode allthough I have it complete.

I tried from scratch, but when I try to add my sources it does not scan them. They are only accesible through file mode not library.

The mysql database is populated with 40 tables each with 0 rows, I guess there should have been some more data there.

I have turned off firewall, and also portforwarded my router with port 3306 to my HTPC. Ofcourse sofar I am only trying this localy.

Is there anybody that would be able to help me out with this? I'd love to post my debug.log here if anybody want to see it.
(This post was last modified: 2011-02-16 01:25 by tormi.)
find quote
tormi Offline
Senior Member
Posts: 106
Joined: Nov 2010
Reputation: 0
Post: #55
FINALLY I got it.
ofcourse it was my own mistake Smile

If others come across this I'll let you know what I did wrong.

1. I did not understand that I had to use SMB share to get the movies scraped and to populate the library also on the computer running the DB + storage of movies.

2. Not getting the synchronization done was due to me forgetting to include the <advancedsettings> tag in the client xml file.

Really newbie mistakes. But finally i can go to bed and sleep now Smile
find quote
castortray Offline
Fan
Posts: 501
Joined: May 2009
Reputation: 0
Post: #56
I've got about 600 movies and when I go to Movies --> Actors it took around 13 minutes (4882 actors) Sad
Any chance to speed up ? Maybe I have something wrong in MySQL configuration. Confused
Everybody has the same issue ? What are your times ?


hopefully someone can help.



• Running Dharma rev.35744 on Windows 7 (ASRock ION 330HT)
find quote
stevenD Offline
Junior Member
Posts: 35
Joined: Apr 2010
Reputation: 0
Post: #57
I have 290 movies and it takes about 10 seconds to load. It's not 13 minutes, but I guess some kind of intelligent buffer have to be implemented.
find quote
malibu66 Offline
Junior Member
Posts: 4
Joined: Feb 2011
Reputation: 0
Post: #58
I am having a problem setting up XBMC with a central database and was wondering if anyone could help. Basically when I configure MySQL, sources won't scan into my library. When I go back to sqlite it works fine again.

I've connected from the XBMC machine to mysql with mysql workbench using the xbmc user that I created and I can connect and see that XBMC created tables in the DBs. I'm no stranger to MySQL and I think everything is ok connection-wise.

XBMC system:
windows 7, XBMC 10.0 r35647

MySQL system:
SUSE 10, mysql 5.1

Contents of my advancedsettings.xml:

<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>6.6.0.6</host>
<port>3306</port>
<user>xbmc_parents</user>
<pass>****</pass>
<name>xbmc_parents_video</name>
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>6.6.0.6</host>
<port>3306</port>
<user>xbmc_parents</user>
<pass>****</pass>
<name>xbmc_parents_music</name>
</musicdatabase>
</advancedsettings>


Can anyone help? Thanks.
--------------------
EDIT: An interesting finding... XBMC 10 doesn't seem to be fully creating the database schema. I turned on the debug logging and took a peek and it seems that the tvshow tables are missing.

Here are the tables that I have in my video database:
+------------------------------+
| Tables_in_xbmc_parents_video |
+------------------------------+
| actorlinkmovie |
| actors |
| bookmark |
| country |
| countrylinkmovie |
| directorlinkmovie |
| genre |
| genrelinkmovie |
| movie |
| path |
| settings |
| stacktimes |
| version |
| writerlinkmovie |
+------------------------------+

+------------------------------+
| Tables_in_xbmc_parents_music |
+------------------------------+
| album |
| albuminfo |
| albuminfosong |
| artist |
| artistinfo |
| content |
| discography |
| exartistalbum |
| exartistsong |
| exgenrealbum |
| exgenresong |
| genre |
| karaokedata |
| path |
| song |
| thumb |
| version |
+------------------------------+

Here are my errors:
14:49:12 T:3772 M:909455360 ERROR: SQL: The table does not exist
Query: select count(1) from tvshow
14:49:12 T:3772 M:909426688 ERROR: CVideoDatabase::HasContent failed
-----
I also seem to have errors for table songview... Ok let me just grep out the missing table errors and list some of the tables I see:
tvshow
songview
musicvideo
... hm that is all I see. enough to prevent anything from working.

Perhaps songview is a stored view? Still it is not there.

Also I have a missing 'actorlinktvshow' table.. I have now just tried to use my root user and running into the same problem.
I have dropped the schemas, recreated, and restarted xbmc several times.

------
EDIT:

I turned on query log for my server and the problem may be related to the following:
4 Query CREATE TRIGGER tgrAlbumInfo AFTER delete ON albuminfo FOR EACH ROW BEGIN delete from albuminfosong where albuminfosong.idAlbumInfo=old.idAlbumInfo; END
4 Query rollback

When I tried to run this CREATE TRIGGER it indeed failed. MySQL seems to have a problem with BEGIN and END around one statement. I ran the following query fine:
CREATE TRIGGER tgrAlbumInfo AFTER delete ON albuminfo FOR EACH ROW delete from albuminfosong where albuminfosong.idAlbumInfo=old.idAlbumInfo;
(This post was last modified: 2011-02-21 00:48 by malibu66.)
find quote
castortray Offline
Fan
Posts: 501
Joined: May 2009
Reputation: 0
Post: #59
stevenD Wrote:I have 290 movies and it takes about 10 seconds to load. It's not 13 minutes, but I guess some kind of intelligent buffer have to be implemented.

after adding skip-name-resolve in my.ini

Code:
[mysqld]
.....
......
skip-name-resolve

and the creation of the index:

Code:
mysql> ALTER TABLE movie ADD INDEX ix_idFile(idFile);
Query OK, 681 rows affected (1.01 sec)
Records: 681  Duplicates: 0  Warnings: 0

my time change from 13 min to 6 min for 4899 actors, it's better but should be much faster I think Sad
find quote
czfj5r Offline
Member
Posts: 58
Joined: Jul 2010
Reputation: 0
Location: Sweden
Post: #60
I think it starts to come down to the speed of your db server combined with the right indexing as well as the network overall and that is where it starts to become tricky.

I am using the MySQL db and it works great with the possibility to move between rooms and continue where you are. You just update from one computer and all PC's get the updated library and thumbnails.

For me it is superb but just as you point out... some "views" are really really slow.

I know from Oracle days that you can prep "predefined" views and similar to speed up the reading and thereby avoid that the db have to create the view based out of a query each and every time. Bad thing is I do not know mySQL that well and for sure the XBMC approach on reading the db is a bit unknown so perhaps we should try and ask for enhancements in XBMC OR for the developers to possibly share more insight as well as increased interest (if it is of interest for the broader community?).

As said, for me it works well but I do avoid some views (like actors Smile ) and it would be great if we could get more details on indexes and possible views to get in for even higher speeds... perhaps it is out there or there is some db guru who wants to jump in?
find quote
Post Reply