Issue with MySQL on unRAID - (Access denied for user 'xbmc'@'%')

[Living Legend]

EDIT:  I am attempting to sync to the database using Frodo 12.2, both on my Windows 7 based PC and Openelec Raspberry Pi.

 

The files aren't scraping for some reason when the advancedsettings.xml file is directing its database search to the unRAID server so I know there's a problem.  I set up the same mySQL database on my PC and it worked perfectly fine.  I'd like to put the database on unRAID however since this is on 24/7.  Here are some lines from the log that I felt were noteworthy:

 

14:39:55 T:8132  NOTICE: Contents of special://profile/advancedsettings.xml are...

                                            <advancedsettings>

                                              <videodatabase>

                                                <type>mysql</type>

                                                <host>192.168.1.3</host>

                                                <port>3306</port>

                                                <user>xbmc</user>

                                                <pass>xbmc</pass>

                                              </videodatabase>

                                              <musicdatabase>

                                                <type>mysql</type>

                                                <host>192.168.1.3</host>

                                                <port>3306</port>

                                                <user>xbmc</user>

                                                <pass>xbmc</pass>

                                              </musicdatabase>

                                              <videolibrary>

                                                <importwatchedstate>true</importwatchedstate>

                                              </videolibrary>

                                            </advancedsettings>

14:39:55 T:8132 WARNING: VIDEO database configuration is experimental.

 

So with this, it is seeing that it's supposed to be going to .3 which is the unRAID server.  It appears all is correct on the above.

 

Later on however is apparently the issue:

 

14:39:56 T:8132  ERROR: Unable to open database: MyMusic32 [1044](Access denied for user 'xbmc'@'%' to database 'MyMusic32')

 

When I telnet into the unraid server and type in the command to show the users, I get this:

 

 

It does list xbmc as a user so I'm not sure where the problem lies.

 

Am I making a silly typo somewhere?  Any thoughts?

 

[Living Legend]

When I click "manage user authentication and privelages" on HeidiSQL I get the below message

 

 

I also noticed that it says the host is 192.168.1.55.  That's my home PC.  But when I log into heidisql I use 192.168.1.3 with user and pass of 'xbmc'.  Could this be the issue??

 

Maybe I need to erase MySQL from unRAID and start again.  Not sure how to do that either though!!

[DoeBoye]

In case you haven't seen it yet, here's a thread discussing XBMC and mySQL for unRaid (It's specifically for sharing a centralized db, but has some good info):

 

http://lime-technology.com/forum/index.php?topic=11473.0

 

Make sure you mention which version of XBMC you are using, as Eden and Frodo play differently with mySQL. Also, xbmcbuntu, OpenElec, Windows etc...

 

Also, I had a similar issue as you are describing when I updated from Eden to Frodo, and eventually I solved it by adding a pause when XBMC booted, as my XBMC boxes would boot up before the connection to mySQL was established. Since I've done that, I've had no more connection errors, and my XBMC boxes are happily sharing the same DB.

 

Here's the original thread where I found the answer @ xbmc.org:

http://forum.xbmc.org/showthread.php?tid=149071

 

Here's the posts that I made here that document what I did to solve it:

http://lime-technology.com/forum/index.php?topic=11473.msg216230#msg216230

 

Not saying that you are having the same problem, but might be worth a look...

[Living Legend]

Yes, I've set up MySQL using the linked tutorial.

 

I will take a look into things.

 

There is definitly something odd going on.  If I try to access MySQL on my unRAID server which is located @ 192.168.1.3 with the user root and pass root, I get the below error:

 

 

Why is it referring me to 192.168.1.55?  That is the IP address of my home PC which is also running MySQL, but I clearly am trying to direct it to 192.168.1.3.  Is there a typo somewhere?

[DoeBoye]

The error is referring to .55 because I'm assuming you are using putty on that PC to connect to mySQL... If you plug a monitor/keyboard into your unraid box, and try to connect to mySQL using root, you'll get the same error, except the ip address will show .3 like you expect...

 

What version of XBMC are you running?

[Living Legend]

The error is referring to .55 because I'm assuming you are using putty on that PC to connect to mySQL... If you plug a monitor/keyboard into your unraid box, and try to connect to mySQL using root, you'll get the same error, except the ip address will show .3 like you expect...

 

What version of XBMC are you running?

 

Frodo 12.2

[Living Legend]

I sent you a PM Doe

[DoeBoye]

Did you manually create those dbs? Many of the guides floating around are outdated (I think the one here as well). Frodo likes to make the dbs on its own. I ask this because your dbs do not have a version number after them (which makes me think they are either old Eden dbs, or you followed an older guide).

 

Note the version number after the DB name in the following sample:

 

+----------------------+
| Database |
+----------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| xbmc_music32 |
| xbmc_video75 |
+----------------------+

 

If you backed up the DB already (Or don't mind re-scraping all your music/video info), I would delete all the dbs and reboot Frodo. Hopefully it will recreate them properly in MySql, and assign the correct names... Your advancedsettings.xml looks to be correctly formatted, so we can hope...

 

I'm at work right now, so I'm working mostly from memory. Once I'm home I may be able to provide more info to help with this, assuming the above steps don't solve the problem...

 

Good luck!

 

[Living Legend]

I will give that a go.

 

I have a feeling there's something wrong with my user however.  When I try to modify my users privelages using HeidiSQL it says:

 

SQL Error (1045): Access denied for user 'xbmc'@'%' (Using password: YES)

[Living Legend]

I now get this

 

 

 

I still get tons of these in the log:

 

16:32:59 T:6408  ERROR: Unable to open database: MyMusic [1049](Unknown database 'MyMusic')

16:32:59 T:6408  DEBUG: Mysql Start transaction

16:32:59 T:6408    INFO: creating version table

16:32:59 T:6408  DEBUG: Mysql execute: CREATE TABLE version (idVersion integer, iCompressCount integer)

                                            CHARACTER SET utf8 COLLATE utf8_general_ci

16:32:59 T:6408  ERROR: SQL: Undefined MySQL error: Code (1)

                                            Query: CREATE TABLE version (idVersion integer, iCompressCount integer)

                                            CHARACTER SET utf8 COLLATE utf8_general_ci

16:32:59 T:6408  ERROR: CMusicDatabase::CreateTables unable to create tables:0

16:32:59 T:6408  DEBUG: Mysql rollback transaction

16:32:59 T:6408  ERROR: Unable to open database: MyVideos75 [1049](Unknown database 'MyVideos75')

[Living Legend]

So opening up XBMC on my PC is definitely populating the database with new tables.  For some reason though there are still problems.  It's not scraping and it's not visible on the Pi, unlike before, when I was running MySQL off of the PC vs unRAID.

[Living Legend]

Now for some reason when I boot up the Pi with the same files I've been using off my PC (advancedsettings.xml and sources.xml) I'm getting an infinite reboot at the Frodo v12.2 screen.

 

EDIT!:There's something up with the advancedsettings file now because with that file I can no longer load up on the pc!  One step forward, two steps back.

[Living Legend]

I definitely found I problem that I can continually recreate.

 

If I open up XBMC for the first time from my PC with a clean database, it will populate with tables "MyMusic32" and "MyVideos75".  If I try to leave and re-enter, I get the error message:

 

ERROR: Unable to Initalize. Exiting

 

If I then go into HeidiSQL and delete those tables, I can then re-enter XBMC once.  If I try to re-enter again, same error message until I remove the tables.

 

EDIT:  Modified and reinstalled the package and no longer have this issue.

[Living Legend]

Hmm, now it's working.  I guess I made too many changes trying to figure things out and had to re install the package to get it to work.

 

I should make a comprehensive tutorial for super n00bs who really don't know much like myself.  A lot of the tutorials are written for someone who has a rudimentary understanding of this stuff which is fine for probably 3/4ths (my random guess) of the people doing this.

[Living Legend]

So once I booted up the Raspberry Pi with the proper advancedsettings.xml file, movies was already completely populated with all of the artwork.  I thought there was a separate step necessary for this?

[mobias1313]

With frodo you no longer need to have artwork stored in a specific location. It will download artwork directly to the device whenever a new show is added. If one of your boxes is turned off then when it next loads xbmc the artwork will be downloaded at that time.