So I've changed my micro server from Windows (which was running a MySQL server) to Linux (lubuntu) which is sharing to 1 OpenElec system, 2 AFTVs and about 3 (Windows) laptops. Kodi can see the nfs shares and play the files but it doesn't seem to be able to access the SQL DB. I can ping the server but kodi isn't creating the databases. Any ideas?
Debug log (wiki).
Did the ip change from the mysql server ?
Does it have access ?
The ip is the same (saved by MAC address)
Does what have access?
From the log: mysql server does not listen on the network IP ... fix that first.
Hint: try to connect with mysql_query_browser from windows - until that does not work, kodi also won't work.
Okay, thanks for the help on that I got it working. I was editing the wrong .cnf file.
It has now created the databases, however when adding TV shows they are in the database but nothing is showing up in Kodi (films are okay).
I've read that this may be to do with only_full_group_by, could that be the case?
I have tried to turn it off but everytime I restart MySQL the setting reverts.
(2016-07-10, 21:15)TRaSH Wrote: [ -> ]http://kodi.wiki/view/MySQL/Setting_up_M...ab=Windows
Do some reading here and try these steps,
And Grant your media device access to your mysql database.
The SQL DB is on Linux not windows.
I have granted the devices access to the DB, the data has been written to it, Kodi just wont read it for TV shows, films are showing up fine.
Okay, was definitely to do with the only_full_group_by setting.
I removed it using:
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
However this resets with every SQL restart, any idea how to make it permanent?
So now my windows laptop can fully access the NFS shares and the MySQL DB.
Having issues getting my AFTVs to access the DB though, they access NFS fine as I can play files, I'll post a log when I get back. I'll also do my OpenELEC box tonight too.
This was all so much easier using windows server! Haha.
edit:
Okay, just read here (
http://forum.kodi.tv/showthread.php?tid=247907) that I can add;
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
To my .cnf which should sort it out at start up, will test later.
Managed to set the SQL mode to stick after restart.
Done a fresh install on my OpenELEC machine, can see the NFS shares and play files just fine but I'm getting SQL errors in the log.
mysql> select host, user from mysql.user;
+-------------+------------------+
| host | user |
+-------------+------------------+
| 192.168.0.% | kodi |
| localhost | debian-sys-maint |
| localhost | mysql.sys |
| localhost | root |
+-------------+------------------+
4 rows in set (0.00 sec)
So kodi should have access from everything on my network, however only my laptop is working,
This is the log from my fresh OpenELEC:
www.pastebin.com/HegE4aZf
Okay, I've fixed it!
Thanks for helping guys.
It turns out there was an issue with the kodi user in the table (not sure what but I tried to run GRANT ALL ON *.* TO 'kodi'; I got the error: ERROR 1133 (42000): Can't find any matching row in the user table)
I've had to drop the user, databases and recreate.