[jlo88]

I'm running kodi on a rpi4 but I can't get the mysql part working. I'm running mysql on my ubuntu NAS where I followed the guide from https://kodi.wiki/view/MySQL/Setting_up_MySQL with a bind-address 0.0.0.0 to make sure it accepts everything. I then did: 

Code:


CREATE USER 'kodi' IDENTIFIED BY 'kodi';
GRANT ALL ON *.* TO 'kodi';
flush privileges;


My advanced settings on the rpi kodi: 

Code:


<advancedsettings>
  <loglevel hide="true">1</loglevel>
  <videodatabase>
    <type>mysql</type>
    <host>192.168.0.16</host>
    <port>3306</port>
    <user>kodi</user>
    <pass>kodi</pass>
  </videodatabase> 
  <videolibrary>
    <importwatchedstate>true</importwatchedstate>
    <importresumepoint>true</importresumepoint>
  </videolibrary>
</advancedsettings>


I have no firewall and I can login to mysql from the ubuntu machine using user kodi pw kodi. 

Looking through the log of the rpi kodi I see a lot of this: 

Code:


2020-03-09 18:28:18.768 T:2876646256 ERROR: Unable to open database: MyVideos116 [1045](Plugin caching_sha2_password could not be loaded: lib/mariadb/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory)


The complete log is posted here: http://ix.io/2dP2

The databases in mysql: 

Code:


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


Trying from another ubuntu machine shows: 

Code:


mysql -u kodi -p kodi -h 192.168.0.16 -P 3306 -D sys
Enter password: 
ERROR 1049 (42000): Unknown database 'kodi'


I don't understand why it thinks I want to connect to 'kodi' and why it asks for a password when I'm using the -p option? 

The mysql log on the ubuntu machine shows: 

Code:


2020-03-09T17:28:18.688185Z 9 [Warning] [MY-010056] [Server] Host name 'kodi.local' could not be resolved: Name or service not known
2020-03-09T18:02:47.612005Z 98 [Warning] [MY-010055] [Server] IP address '192.168.0.20' could not be resolved: Name or service not known


It looks like some sort of permission error or network error?

[Klojum]

Which MySQL/MariaDB server version is running on the Ubuntu 'nas' ?

[black_eagle]

Change the password declaration in mariadb. Something like this ALTER USER 'kodi'@ 'localhost' IDENTIFIED WITH mysql_native_password BY 'password';  Then try and reconnect.

[jlo88]

Which MySQL/MariaDB server version is running on the Ubuntu 'nas' ?

Code:


mysql  Ver 8.0.19-0ubuntu0.19.10.3 for Linux on x86_64 ((Ubuntu))


I also just enabled this option mysql to deal with the warning I got: 

Code:


# Skip reverse DNS lookup of clients
skip-name-resolve


 

The mysql output changes but the kodi logs stays the same: http://ix.io/2dPm 

mysql: 

Code:


tail -f /var/log/mysql/error.log
2020-03-09T17:24:58.757385Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-03-09T17:24:58.779714Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.19-0ubuntu0.19.10.3'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).
2020-03-09T17:24:58.953551Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
2020-03-09T17:28:18.688185Z 9 [Warning] [MY-010056] [Server] Host name 'kodi.local' could not be resolved: Name or service not known
2020-03-09T18:02:47.612005Z 98 [Warning] [MY-010055] [Server] IP address '192.168.0.20' could not be resolved: Name or service not known 
2020-03-09T18:14:48.637646Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19-0ubuntu0.19.10.3)  (Ubuntu).
2020-03-09T18:14:49.083220Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19-0ubuntu0.19.10.3) starting as process 32345
2020-03-09T18:14:49.499259Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-03-09T18:14:49.520701Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.19-0ubuntu0.19.10.3'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).
2020-03-09T18:14:49.689534Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060


[jlo88]

Change the password declaration in mariadb. Something like this ALTER USER 'kodi'@ 'localhost' IDENTIFIED WITH mysql_native_password BY 'password';  Then try and reconnect.

Do I use ALTER USER 'kodi'@ 'localhost' IDENTIFIED WITH mysql_native_password BY 'kodi'; ? This throws me a syntax error 

Code:


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''localhost' IDENTIFIED WITH mysql_native_password BY 'kodi'' at line 1


Sorry, I'm not really experienced with the SQL syntax..

[black_eagle]

You have two ' before localhost, that's why.

[jlo88]

You have two ' before localhost, that's why.

Changed it to ALTER USER 'kodi'@localhost IDENTIFIED WITH mysql_native_password BY 'kodi'; but now I'm getting ERROR 1396 (HY000): Operation ALTER USER failed for 'kodi'@localhost

[black_eagle]

Drop the localhost bit then.  Looking at your first post it appears you just defined your user as kodi.

[jlo88]

Adding the option default-authentication-plugin=mysql_native_password to the [mysqld] section of the mysql config file seems to solve the authentication problem. I can now connect to the database and kodi sees it as well. I did have to add the kodi user again and grant it privileges again. My kodi log now says: http://ix.io/2dPH 

I says 

Code:


2020-03-09 20:19:42.527 T:2875597680 NOTICE: MYSQL: Connected to version 8.0.19-0ubuntu0.19.10.3


So that's good! 

I do think that this should maybe be in the wiki as well with this option? 

However no content shows up in Kodi. When I use update nothing happens, I made an export of the library and I can also see the exported files in the respective movie/tv-shows folders on the NAS. 

The manual says to just update the library..

[asavah]

@jlo88 Mysql 8 has another problem, database creation actually fails (triggers creation fails, after that error view creation is not performed) and does not work with out of the box mysql server configuration.

Close Kodi.
Drop both video and music databases.

Add the following to the end of /etc/mysql/mysql.conf.d/mysqld.cnf

Code:

log_bin_trust_function_creators = 1

and restart mysql server.
Start Kodi again.
Rescan everything.

[jlo88]

@jlo88 Mysql 8 has another problem, database creation actually fails (triggers creation fails, after that error view creation is not performed) and does not work with out of the box mysql server configuration.

Close Kodi.
Drop both video and music databases.

Add the following to the end of /etc/mysql/mysql.conf.d/mysqld.cnf

Code:

log_bin_trust_function_creators = 1

and restart mysql server.
Start Kodi again.
Rescan everything. 

Thanks for your reply! I just saw that the content of the folder on my NAS in Kodi got set to None, changing that back to movies and tv shows makes my content show up again. 

Do you still recommend this fix in that case?

[asavah]

Do you still recommend this fix in that case?

If it works - don't fix it, I've had the problem I described, but I adopted mysql 8 long ago, maybe the option I'm talking abouut is already present, ot it was flipped as default in the server code.

[asavah]

To confirm it's ok do:

Code:

sudo mysql -u root
show databases
#pick the highest number kodi_videoXXX you have, I'm using Kodi 19 so it is kodi_video116 for me
use kodi_videoXXX;
show triggers;

You shoud see a bunch of codes like

Code:

| delete_person     | lot's of crap here
9 rows in set (0.02 sec)

if the triggers are present - you are good to go, if result of show triggers is empty - you need the fix described above.

[jlo88]

@asavah, I can confirm that the triggers are present. I appreciate your help!

[MasterCATZ]

can someone please add the 

sql:
log_bin_trust_function_creators = 1

sql:
show global variables like 'log_bin_trust_function_creators';

sql:
SET GLOBAL log_bin_trust_function_creators = 1;

to the official documentation

I have been trying to work out whats wrong with the sql 8 install for months now 

use old db and things worked (until a kodi update) tried fresh db and kodi would keep crashing 

new db have no triggers



I still wish kodi would just work inside its own db instead of needing full access to keep creating new db's on every update , plus I wish its password system was updated to the newer secure format
my db is on remote hosts that run other db's
percona-xtradb-cluster-server_8.0.29-21 12x nodes