+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: SQL DB Help (/showthread.php?tid=334649)
SQL DB Help - Nckchris - 2018-08-15
I've recently setup mariadb 10 and phpmyadmin in Docker on a new Synology to host my Kodi DB for a variety of devices (AFTV and Mac mostly). The Synology side of things seems to be working properly. The DB populated with all of my media info automatically as the guides say it should, but when I try to get any of my devices to use the DB there are no TV Shows or Movies listed in Kodi. I've removed the advancedsettings.xml, removed my sources, cleaned the library, re-added the advancedsettings.xml, then re-added the sources to no avail. After adding the sources back in they will scan and show that they see all of the movie/tv titles, but nothing actually populates in to Kodi. I should add that scanning the sources without having the advancedsettings.xml file in place works fine and all video gets pulled in to Kodi normally, so it's not an issue with sharing permissions or how the sources are set up.
For what it's worth:
Mariadb running in Docker - latest release
phpmyadmin running in Docker - latest release
Kodi 17.6 on all devices
advancedsettings.xml:
<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>10.0.1.50</host>
<port>3307</port>
<user>kodi</user>
<pass>kodi</pass>
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>10.0.1.50</host>
<port>3307</port>
<user>kodi</user>
<pass>kodi</pass>
</musicdatabase>
<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
</videolibrary>
</advancedsettings>
I'm sure this is something small and simple, but it's driving me nuts. Thanks for any help/suggestions.
RE: SQL DB Help - Klojum - 2018-08-15
(2018-08-15, 20:47)Nckchris Wrote: but nothing actually populates in to Kodi.We're gonna need at least a full debug log (wiki) for that, just to see what works and what doesn't.
RE: SQL DB Help - Nckchris - 2018-08-15
Thanks for the quick response. Here's what I get on a fresh install of Kodi for Mac: uhewetinip (paste)
It's definitely listing errors with tables in the SQL DB. I haven't cleared that out and set it up fresh again yet. Might that be my best bet?
Thanks again for any help!
RE: SQL DB Help - Nckchris - 2018-08-15
Well, it looked like the last log I posted was incomplete. I did it all over again letting it scan my movie source and then the log was too big to post. Here's a log where it re-scanned my movies and started in on TV shows.
ropisikida (paste)
If I go to Movies > Files > Movies everything pulls up from the source, but no movies are listed at the top level of Movies and if I go to Movies > Movies > Title (or any other category for that matter) nothing happens. I'm sure I'm doing something very obviously wrong, but it's been about 6 years since the last time I set up a SQL DB for Kodi.
Thanks again for any help.
RE: SQL DB Help - Klojum - 2018-08-16
Your database setup in MariaDB hit a glitch, apparently the view tables (and who knows what else) were not created properly. Likely a database user rights problem.
sql:16:06:21.979 T:123145505615872 ERROR: SQL: [MyVideos107] The table does not exist
Query: select * from movie_view WHERE ((movie_view.dateAdded > '1900-01-01')) AND ((movie_view.playCount IS NULL OR movie_view.playCount < 1))
...
16:06:22.566 T:123145499635712 ERROR: SQL: [MyVideos107] The table does not exist
Query: SELECT count(1) FROM tvshow_view LIMIT 1
...
RE: SQL DB Help - Nckchris - 2018-08-16
The "kodi" user from my advancedsettings.xml has all rights granted to the MyVideos107 table through phpmyadmin. I'm not even slightly familiar with SQL. Do you think I'm better off trying to fix things or just clear out the MyVideos107 table and let Kodi re-create things?
RE: SQL DB Help - Nckchris - 2018-08-17
Well, I may be speaking too soon, but I just dropped the MyVideos107 table from the SQL database and after adding my advancedsettings.xml file to a fresh install of Kodi, re-adding my Movie source and telling it to scrape, things are looking to be working. I've got logging going while it goes through everything again, but so far things are looking good.
Thanks for your help!
RE: SQL DB Help - AMoo-Miki - 2018-08-24
(2018-08-16, 23:25)Nckchris Wrote: The "kodi" user from my advancedsettings.xml has all rights granted to the MyVideos107 table through phpmyadmin. I'm not even slightly familiar with SQL. Do you think I'm better off trying to fix things or just clear out the MyVideos107 table and let Kodi re-create things?The easiest thing I would propose you do is:
1) Drop the db you created.
2) Delete the kodi user you created on the db.
3) Using PHPMyAdmin, create a new user called kodi and make sure to only select Grant all privileges on wildcard name (username\_%) and nothing else; no other checkbox needs to be selected on the page.
4) change your advancedsettings.xml file to include:
5) Run Kodi.xml:
<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>10.0.1.50</host>
<port>3307</port>
<name>kodi_video</name>
<user>kodi</user>
<pass>kodi</pass>
</videodatabase>
<musicdatabase>
<type>mysql</type>
<host>10.0.1.50</host>
<port>3307</port>
<name>kodi_music</name>
<user>kodi</user>
<pass>kodi</pass>
</musicdatabase>
<videolibrary>
<importwatchedstate>true</importwatchedstate>
<importresumepoint>true</importresumepoint>
</videolibrary>
<videoscanner>
<ignoreerrors>true</ignoreerrors>
</videoscanner>
</advancedsettings>
The databases, kodi_video107 and kodi_music60 will automatically be created because the user kodi will have permissions needed to correctly create any database whose name starts with kodi_.
In step 3, just enter a username and a password, retype password, select the mentioned checkbox, and hit go; nothing else.
Good luck,
Miki