Sharing MySQL with XBMC (1 Viewer)

[doveman]

I want to use my PC to store a Central database for XBMC as described here http://lifehacker.com/5634515/how-t...c-media-center-across-every-room-in-the-house but I'm mindful that MP also uses MySQL and I don't want to mess up it's database.

I've tried using the commands given in that guide and setting up advancedsettings.xml but now it doesn't appear to be able to create a library at all. I wonder if MP has MySQL listening on a different port to the default 3306?

 

[davidf]

I don't believe that MP uses a port other than the default (I use MySQL running on my NAS). I think that either you have a firewall preventing access or that you've got the advancesettings wrong (or put it in the wrong directory).

If your MySQL install is on the TVServer then it's most likely the firewall as only TVService requires access to it, so ports aren't opened for it by the Mediaportal installer.

 

[legnod]

Do you use root user to login from a remote machine? If so...There is the Problem...normally the root user only habe access Form localhost. You can add a second user vor Grant access for the root user from remote machines. For both you need the MySQL workbench (download it at mysql.com) or the MySQL comand line tool (included inside the bin folder oft the MySQL Server installationpath)

 

[doveman]

I don't believe that MP uses a port other than the default (I use MySQL running on my NAS). I think that either you have a firewall preventing access or that you've got the advancesettings wrong (or put it in the wrong directory).

If your MySQL install is on the TVServer then it's most likely the firewall as only TVService requires access to it, so ports aren't opened for it by the Mediaportal installer.

I'm using Comodo Firewall and I have it set to Allow All on LAN so that should allow access to MySQL from both the local machine and the RPi. As scanning doesn't work anymore even from XBMC running on my PC, you may be right that I've got the advancedsettings.xml wrong (I'm pretty sure it's in the right place though, as otherwise it wouldn't have changed anything and stopped scanning from working by using the normal XBMC database). This is what I've used.

 <advancedsettings>
  <videodatabase>
	<type>mysql</type>
	<host>192.168.1.64</host>
	<port>3306</port>
	<user>xbmc</user>
	<pass>xbmc</pass>
	<name>xbmc_video</name>
  </videodatabase>
  <musicdatabase>
	<type>mysql</type>
	<host>192.168.1.64</host>
	<port>3306</port>
	<user>xbmc</user>
	<pass>xbmc</pass>
	<name>xbmc_music</name>
  </musicdatabase>
</advancedsettings>

However, Comodo shows me the listening connections and I don't see Mysql in the list at all so that doesn't seem right.[DOUBLEPOST=1361884134][/DOUBLEPOST]

Do you use root user to login from a remote machine? If so...There is the Problem...normally the root user only habe access Form localhost. You can add a second user vor Grant access for the root user from remote machines. For both you need the MySQL workbench (download it at mysql.com) or the MySQL comand line tool (included inside the bin folder oft the MySQL Server installationpath)

I'm not sure as normally I don't login to it, that's all taken care of automatically by MP I guess. However, as per the guide I've done:

CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
CREATE database xbmc_video;
CREATE database xbmc_music;
GRANT ALL ON *.* TO 'xbmc';

and doing
SELECT host,user from mysql.user;
SHOW DATABASES;

returns

+-----------+------+
| host | user |
+-----------+------+
| % | root |
| % | xbmc |
| 127.0.0.1 | root |
| localhost | |
| localhost | root |
+-----------+------+
5 rows in set (0.01 sec)

+--------------------+
| Database |
+--------------------+
| information_schema |
| argustv |
| mptvdb |
| mysql |
| test |
| xbmc_music |
| xbmc_video |
+--------------------+
7 rows in set (0.02 sec)

I note that the guide refers to setting the password when installing MySQL though. As there's already a password of MediaPortal set (I have to use this when opening the commandline tool), perhaps I need to specify this in my xml instead of xbmc. The guide suggests the CREATE USER creates the user/password but perhaps that's wrong and it just creates the user.

 

[davidf]

That explains it.

MySQL is only listening on localhost. You need to set it to listen on 192.168.1.64, look in my.cnf (bind address I think).

 

[davidf]

looks like you use 0.0.0.0 http://serverfault.com/questions/139323/mysql-bind-to-more-than-one-ip-address

 

[doveman]

That explains it.

MySQL is only listening on localhost. You need to set it to listen on 192.168.1.64, look in my.cnf (bind address I think).

Ah, thanks.

I've search for my.cnf and *.cnf though and don't appear to have one. In C:\Program Files\MySQL\MySQL Server 5.1 there is a my.ini and a few others like my-huge.ini as well. Assuming my.ini is what's used, it seems adding bind-address = 0.0.0.0 should make it available on both the external and internal/localhost address. Indeed, after adding that and restarting mysql, Comodo shows it's listening on 3306.

However, XBMC still can't seem to access it. I tried changing the host in the .xml to 127.0.0.1 and 192.168.1.64. If I set it to localhost, XBMC can't even launch. I also tried with the password xbmc and MediaPortal.

EDIT: I note that I have MySQL 5.1 installed and the XBMC guide links to 5.5 or 5.6, so perhaps I need a newer version. It seems I should be able to run two instances of MySQL in a so-called "side-by-side" config on different ports and even keep 5.1 for MP and install 5.5 to a different folder for XBMC but I'm not sure how to go about this as I've only found a linux-based guide so far http://addumb.com/blog/2011/03/02/mysql-5-0-and-5-1-side-by-side/ (obviously installing the software to a different folder isn't a problem, just ensuring that it uses a different data folder).

 

[davidf]

You need to start checking the mysql logs now
http://forum.xbmc.org/showthread.php?tid=156246 also possibly describes your problem, I think you'd be better on the XBMC forum with the problem as the mysql basics are now working.

 

[doveman]

Thanks. I'm not so sure we've got the basics right yet though as I still get this:

+-----------+------+
| host	  | user |
+-----------+------+
| %		| root |
| %		| xbmc |
| 127.0.0.1 | root |
| localhost |	  |
| localhost | root |
+-----------+------+
5 rows in set (0.00 sec)
 
mysql> SHOW DATABASES;
+--------------------+
| Database		  |
+--------------------+
| information_schema |
| argustv			|
| mptvdb			|
| mysql			  |
| test			  |
| xbmc_music		|
| xbmc_video		|
+--------------------+
7 rows in set (0.00 sec)

which isn't any different from before (even though Comodo shows it's listening on port 3306 which it didn't before) and only the root user is shown against even 127.0.0.1 and localhost, with the xbmc user just shown against % (whatever that signifies).

 

[doveman]

It seems doing all that caused some problems for TVservice and I was unable to start Mediaportal (see attached logs) although it seems to be working again after taskkilling TVservice.exe.