Connection to MySQL DB not working anymore

Help and Support
1

Hi,

I wanted to close down the MySQL user for the kodi DB and kinda f’ed up it seams.
I created a new user and it seemed to work, so I removed the old user.
From then on it didn’t work anymore.

I have setup the following user:
mysql_user

I have the following permissions set:
mysql_privileges

When I try to connect from my Vero 4K it works from the commandline:
mysql_databases

But I keep getting errors within the kodi log:>

2021-03-21 21:50:05.308 T:3947561184 NOTICE: MYSQL: Connected to version 10.3.21-MariaDB
2021-03-21 21:50:05.359 T:3947561184 NOTICE: Running database version MyMusic72
2021-03-21 21:50:05.376 T:3947561184 NOTICE: Running database version MyVideos116
2021-03-21 21:50:05.379 T:3947561184 NOTICE: Running database version TV32
2021-03-21 21:50:05.381 T:3947561184 NOTICE: Running database version Epg12
2021-03-21 21:50:05.382 T:4066721808 NOTICE: start dvd mediatype detection
2021-03-21 21:50:05.453 T:4066721808 NOTICE: load skin from: /usr/share/kodi/addons/skin.estuary (version: 2.0.27)
2021-03-21 21:50:05.778 T:4066721808 ERROR: Window Translator: Can’t find window InfoDialog
2021-03-21 21:50:05.923 T:4066721808 ERROR: SQL: [MyMusic72] Undefined MySQL error: Code (1045)
Query: select count(idSong) as NumSongs from songview
2021-03-21 21:50:05.923 T:4066721808 ERROR: GetSongsCount() failed
2021-03-21 21:50:06.135 T:3947561184 ERROR: SQL: [MyVideos116] Undefined MySQL error: Code (1045)
Query: SELECT movie_view.idSet,COUNT(1) AS c FROM movie_view JOIN sets ON sets.idSet = movie_view.idSet GROUP BY
movie_view.idSet HAVING c>1
2021-03-21 21:50:06.135 T:3947561184 ERROR: HasSets failed

My advancedsettings.xml looks like this:

    <videodatabase>
            <name>MyVideos</name>
            <host>192.168.14.10</host>
            <user>kodi</user>
            <pass>kodi</pass>
            <type>mysql</type>
            <port>3307</port>
    </videodatabase>
    <musicdatabase>
            <name>MyMusic</name>
            <host>192.168.14.10</host>
            <user>kodi</user>
            <pass>kodi</pass>
            <type>mysql</type>
            <port>3307</port>
    </musicdatabase>

I tried with a longer password first and tried this abit later on.
I used xbmc/xbmc before this and removed the user … :cry:

2

While I typed the last sentence … I thought … what if I would create a new xbmc user.

I just did … and it worked right away!
Exacly the same privileges :dizzy_face:

Any know what this could be? :thinking:

3

Not sure, but please be aware you can NOT limit the permission for the user as for every Kodi upgrade it will create a new Database so the Kodi user require full rights. The only way to secure the Database is to have a dedicated database running for Kodi (e.g. either on one of the Kodi machines or in a Docker).

Actually one reminder, after changing any user permissions you need to reload the permissions with FLUSH PRIVILEGES;

4

I know, but I wanted to limit the network side, so only from certain clients allowed.
So change % in the hostname field to the hosts of my Kodi boxes.

Did the flush privileges part as well :slight_smile:

5

Fair enough that should work, but on what you listed below it seems too limited. Try if you can create a new database MyMusic105 with that user.

6

MariaDB [(none)]> create database MyVideos105;
Query OK, 1 row affected (0.008 sec)

MariaDB [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| MyMusic72 |
| MyVideos105 |
| MyVideos116 |
| information_schema |
±-------------------+
4 rows in set (0.002 sec)

7

All good then

8

But it doesn’t work from within Kodi, only the xbmc user works within Kodi.
And that is the weird thing I’m trying to understand :thinking:

9

So you executed the command line mysql from the Kodi box?

10

yup, as the user kodi

11

Than it should be same same. You can enable component specific logging for MySQL in Kodi to figure out whats going on. Or check binlog on the MySQL server.

12

I think your issue was the Grant permissions, as per the official setup…

Type in: CREATE USER 'kodi' IDENTIFIED BY 'kodi'; and press return
Type in: GRANT ALL ON *.* TO 'kodi'; and press return
Type in: flush privileges; and press return
13

Not really, the xbmc user and kodi user have the same privileges.
The databases have been created with the xbmc user, maybe it’s that.

What you just named is the basic user creation and setting of global privileges, but if you share your mysql server with other databases, you better set the database specific privileges from a security point of view.

14

I see. To be fair I used to get a lot of mariadb database corruption in my setup. Database would become inaccessible and I would restore from daily backups, but I never really investigated. Have since abandoned that setup for an emby backend.

I still have the database, since I use MariaDB for other things. Should attach it to a spare kodi box and check if it is the same issue you are having.
I will do that tonight.