[Donsch]

Hello,

First a quick intro. I've been using Kodi (xbmc) since version 9.04 and familiar with all the OS's it can be installed on. I've been using mysql for the library database since Darwin. My point, not really a noob, even though I have never joined the forum until now.


Enter Helix (14 and 14.1) I originally tried another upgrade which worked relatively well through many versions of kodi, but Helix failed on so many counts I decided to do a fresh install. I switched my OS to try Kodiubuntu (which was a very impressive installer, well done Kodiubuntu team).

I have tried everything in the wiki and forums, but can't get mysql to work with Helix. I can log into mysql with

Code:

mysql -u kodi -p

Enter Password: kodi

mysql>

I get a mysql prompt with full access to mysql, but no databases added by kodi.

My advancedsettings.xml looks like this

Code:

<advancedsettings>
  <videodatabase>
    <type>mysql</type>
    <host>10.0.0.7</host>
    <port>3306</port>
    <user>kodi</user>
    <pass>kodi</pass>
  </videodatabase>
  <musicdatabase>
    <type>mysql</type>
    <host>10.0.0.7</host>
    <port>3306</port>
    <user>kodi</user>
    <pass>kodi</pass>
  </musicdatabase>
  <videolibrary>
    <importwatchedstate>true</importwatchedstate>
    <importresumepoint>true</importresumepoint>
  </videolibrary>
  <useddsfanart>true</useddsfanart>
  <cputempcommand>cputemp</cputempcommand>
  <gputempcommand>gputemp</gputempcommand>
  <samba>
    <clienttimeout>30</clienttimeout>
  </samba>
  <network>
    <disableipv6>true</disableipv6>
  </network>
</advancedsettings>

When I start kodi, there is no library and library update does nothing. I should note that I exported my library to multiple files, so .nfo files are all over the place. I tried renaming sources.xml and starting a fresh one, but same results.

I can take the mysql tags out of advancedsettings.xml and revert back to using the .db files in the Databases folder. I thought about making the databases manually, but everything suggests that kodi should be able to do this. Any ideas?

[Prof Yaffle]

Is the db server on the same machine? And have you changed the bind address so that it's not just listening on localhost?

I don't think sources.xml matters until you update/re-import the library... I don't use it on most of my clients as I update the database from the server which hosts it. Your config obviously matters here, though.

[Donsch]

Yes, only one install at the moment, and yes I updated the bind from 127.0.0.1 to 10.0.0.7 which is the ip of machine.

I changed sources.xml because of suspecting it might contain an error, It turned out to be fine.

[Prof Yaffle]

and the kodi user has permissions to create dbs and tables? you granted permissions when initially logging in through mysql -u root?

(I'm not claiming to be an expert here, just logically backtracking through what I'd check having ploughed through this a couple of times)

[Donsch]

yes, I logged in as kodi and created testdb, did a show databases; to see it, then dropped it. No problems with access as kodi.

[Prof Yaffle]

Then it's logfile o'clock - I'd start to follow the trail through what mysql is writing to syslog and/or security logs (to see what activity it thinks it's being asked to perform) and a debug log in Kodi (to see what it thinks is going on, e.g. is advancedsettings.xml being read correctly, is it trying to connect to the mysql server).

It sounds like you've got the basics set up okay... which I'm sure you knew, given your history and experience here.

[Donsch]

mysql log is clean, it did remind me that I bound to 0.0.0.0 instead of 10.0.0.7 because I wanted to be able to access from any IP on the network. It says ready to receive connections, which works fine from command line.

syslog is clean, mostly ufw doing its job.
dmesg is clean, more of the same

The kodi log has errors inline with expectations, but I'll swap advancedsettings.xml to try mysql again and report back soon.

[Donsch]

looks like user kodi as the wiki says is not correct, Rather 'kodi'@ubuntu is what kodi uses. Where "ubuntu" is hostname of machine.

This was the line in the kodi log that gave the clue

ERROR: Unable to open database: MyMusic48 [1045](Access denied for user 'kodi'@ubuntu (using password: YES))

so the solution was to put the machine hostname in with the username, my hostname is ubuntu as the log shows:

Code:

mysql -u root -p
[enter mysql root password]

DROP USER 'kodi';
DROP USER 'kodi'@'localhost';
CREATE USER 'kodi'@'ubuntu'  IDENTIFIED BY 'kodi';
GRANT ALL ON *.* TO 'kodi'@'ubuntu';
exit or \q (to quit)

Then after restarting kodi, edit each source, set the type of content, and let it refresh the data, which supposedly scans all the .nfo files from a multiple file export.

Thank you Prof Yaffle for leading me to the solution. Much appreciated.

[Prof Yaffle]

Interesting - I've not seen that on my 14.04 system, so I wonder if it's an artefact of mysql version - perhaps security has tightened so you need to explicitly name hosts, for example.

Glad you found it, but it'd be worth seeing if anyone else gets this for obvious reasons.

[Donsch]

Since figuring this out I added two more machines to the mix. These were Windows machines, but I don't think that is relevant.

I got the same errors, but at first the error was 'kodi@'[ip address]' of the machine I was adding. So I added a few entries in the /etc/hosts file like this:

127.0.0.1 localhost
10.0.0.7 ubuntu
10.0.0.8 don
10.0.0.9 drobo
10.0.0.10 teresa

Then the error switched from [ip address] to [hostname], So I added mysql users for these user / machine combinations 'kodi'@'don' 'kodi'@teresa and it worked.

By default, Ubuntu 14.04 does not look at the /etc/hosts file, but if you add or edit

/etc/NetworkManager/dnsmasq.d/hosts.conf

and add

addn-hosts=/etc/hosts

then it will work like it used to in previous versions.