[godfreydanials]

I have been sharing my music and movies from a Netgear ReadyNAS Linux-based server successfully for the last year. I decided I wanted to move the thumbnails to the server, along with music and video libraries, to a mysql database on this server. The thumbnails were a piece of cake with nfs, the libraries are a pain.

I first configured a local mysql server on my Ubuntu Eden RC2 box. I scraped the movies and music to the local mysql and all was well for 5 days. I thought I had the remote mysql server configured, but I can't get it to work. I have searched here and across the web. I have read everything I can find that seems related. All I can find is references to CREATE & GRANT for the appropriate user.

I won't post the xbmc or mysql logs here, just a couple of snipets, since there are already many examples. It starts with:

Code:

ERROR: Unable to open database: MyVideos60 [1044](Access denied for user 'xbmc'@'192.168.1.26

The database name counts down to zero, then xbmc tries and fails to create MyVideos60.

Code:

ERROR: SQL: Can't create new database: 'MyVideos60' (1044)

My Eden box is 192.168.1.26 and the NAS server is 192.168.1.200.

This is what I did via ssh and mysql client, at the nas server:

Code:

CREATE USER 'xbmc'@'192.168.1.26/255.255.255.0' IDENTIFIED BY 'xbmc';
GRANT ALL on *.* TO 'xbmc'@'192.168.1.26/255.255.255.0';

When that didn't work, I tried this:

Code:

CREATE USER 'xbmc'@'%' IDENTIFIED BY 'xbmc';
GRANT ALL on *.* TO 'xbmc'@'%';

XBMC still can't create the remote mysql database. Can somebody please tell me what I am doing wrong?
The local database was a breeze compared to this one.

[jervine]

Is MySQL started on the system? Can the xbmc user logon to the MySQL server locally, i.e. when you SSH on to the NAS system, can you connect to MySQL using the xbmc user you have created?

You might want to consider loosening your restrictions for testing, as it seems that yo uwish to restrict xbmc to *only* connect from the XBMC server. I would allow it to connect from the network 192.168.1.0 which will allow you to test from other hosts.

First check should definitely be if mysqld is listening to the network on the NAS though; netstat -tunap should show this (apologies if this painfully obvious)

[godfreydanials]

I appreciate the questions. Mysql is definatly running on the ReadyNAS.

Code:

nas:~# netstat -tunap | grep mysql
tcp        0      0 192.168.1.200:3306      0.0.0.0:*               LISTEN      12304/mysqld
nas:~# mysql -u xbmc -p
Enter password:

mysql> SHOW GRANTS FOR xbmc;
+-------------------------------------------------------------------------------------+
| Grants for xbmc@%                                                                   |
+-------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'xbmc'@'%' IDENTIFIED BY PASSWORD '4b536f804153ab3e' |
| GRANT ALL PRIVILEGES ON `xbmc_video`.* TO 'xbmc'@'%'                                |
+-------------------------------------------------------------------------------------+

mysql> SHOW GRANTS FOR xbmc@'192.168.1.26';
+---------------------------------------------------------------------------------------+
| Grants for [email protected]                                                          |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xbmc'@'192.168.1.26' IDENTIFIED BY PASSWORD '4b536f804153ab3e' |
| GRANT ALL PRIVILEGES ON `xbmc_video`.* TO 'xbmc'@'192.168.1.26'                       |
| GRANT ALL PRIVILEGES ON `xbmc_music`.* TO 'xbmc'@'192.168.1.26'                       |
+---------------------------------------------------------------------------------------+

As you can see, the ReadyNAS mysql server is running and the rights should be sufficient for the user xbmc to create and populate the database. I removed the database names from the xbmc my.cnf file. I will revoke specific database grants once I get this thing working.

[.:B:.]

Clean out your MySQL setup, recreate an xbmc user in MySQL - according to [url=
http://wiki.xbmc.org/index.php?title=HOW...Linux]wiki instructions[/url]. So just an 'xbmc' user, no specifying the host (don't know if it matters, but you never know).

Then set up your client according to these instructions.

[bahman2000]

So the way it worked for me was:
-do not manually create databases like xbmc_video and xbmc_music
-do not include the <name> parameter in advancedsettings.xml

I tried those instructions while the forum was down and had problems:
http://lifehacker.com/5634515/how-to-syn...eedfetcher

[generious]

Might come in handy
make sure you grant all permissions to the xbmc account that you have created.
run
GRANT ALL ON *.* TO 'xbmc';

Also verify that you can connect to the instance using workbench/pmpadmin/heidi before trying it out.

http://wiki.xbmc.org/index.php?title=HOW...v10_to_v11

<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>10.0.0.24</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>10.0.0.24</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
</musicdatabase>
<pathsubstitution>
<substitute>
<from>special://masterprofile/Thumbnails/</from>
<to>smb://xbmc:[email protected]/thumbs/</to>
</substitute>
</pathsubstitution>
<gui>
<algorithmdirtyregions>1</algorithmdirtyregions>
</gui>
</advancedsettings>

[godfreydanials]

Thanks for the help folks, I really appreciate it...

Clean out your MySQL setup, recreate an xbmc user in MySQL - So just an 'xbmc' user, no specifying the host (don't know if it matters, but you never know).

I found 4 'xbmc' users in the mysql.user table. Two had '%' for the Host, one with '192.168.1.26', and the last with '192.168.1.26/255.255.255.0'. I was surprised mysql let me create the first 2 identical users.

Then set up your client according to these instructions.

As I said in my original post, I have been running xbmc with a local mysql service for 5 days. The only changes I made on the client side is the IP address within the advancedsettings.xml <host> tag. I knew the xbmc box was set up correctly.

So the way it worked for me was:
-do not manually create databases like xbmc_video and xbmc_music
-do not include the <name> parameter in advancedsettings.xml

In my second post, I said there is no database name defined my.cnf. I meant to say advancedsettings.xml.

make sure you grant all permissions to the xbmc account that you have created.
run
GRANT ALL ON *.* TO 'xbmc';

As you can see from the SHOW GRANTS query in my second post, this had been done.

The fix
I deleted all of the 'xbmc' users identified above and created a new user with CREATE USER 'xbmc' IDENTIFIED BY 'xbmc'; the user was assigned the default Host of '%'. My paranoia tells me this is not a good thing to let the user xbmc on any host have full privileges. I will let it run for a few days, then try to at least limit connections to the subnet.

Can I do this with:

CREATE USER 'xbmc'@'192.168.1.%' IDENTIFIED BY 'xbmc';
or do I need to include the netmask in the Host field?

[nico1375]

I had the same problem.

I solved it by deleting the db I previously created (MyVideos and MyMusic) and the xbmc user. I then just created a xbmc user with all priviledges for data and structure (not administration).
Now it works

[muldereric]

To give nodes on a subnet permission on MySQL DB's, use this command in mysql:

Code:

GRANT ALL PRIVILEGES ON mydb TO 'username'@'192.168.1.0/255.255.255.0';