Sad face after setting up MySQL on Synology

Help and Support Vero 4K / Vero 4K +
1

Vero 4k+ with MariaDB10 running on Synology

I setup new user in phpMyAdmin and granted access. Added below advanced settings file. Open movies and did a new scan which took hours. I can see database entries made on MariaDB but when I try and view movies in kodi I get nothing. Can anyone tell what I am doing wrong from logs?

Logs
https://paste.osmc.tv/emicogaxop

My advanced settings file looks like this:

<advancedsettings>
     <videodatabase>
       <type>mysql</type>
       <host>10.0.1.22</host>
       <port>3307</port>
       <user>kodi</user>
       <pass>kodi</pass>
       <name>kodi_video</name>
    </videodatabase>
    <musicdatabase>
       <type>mysql</type>
       <host>10.0.1.22</host>
       <port>3307</port>
       <user>kodi</user>
       <pass>kodi</pass>
       <name>kodi_music</name>
    </musicdatabase>
</advancedsettings>

sorry i don’t know how to post actual xml code

2 
2019-09-02 09:07:34.695 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: select * from episode_view  ORDER BY dateAdded desc, idEpisode desc LIMIT 10
2019-09-02 09:07:34.696 T:3900699360   ERROR: GetEpisodesByWhere failed
2019-09-02 09:07:34.697 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: select * from musicvideo_view  ORDER BY dateAdded desc, idMVideo desc LIMIT 10
2019-09-02 09:07:34.697 T:3900699360   ERROR: GetMusicVideosByWhere failed
2019-09-02 09:07:34.727 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: SELECT count(1) FROM tvshow_view LIMIT 1
2019-09-02 09:07:34.727 T:3900699360   ERROR: GetSingleValue - failed on query 'SELECT count(1) FROM tvshow_view LIMIT 1'
2019-09-02 09:07:34.728 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: SELECT count(1) FROM movie_view LIMIT 1
2019-09-02 09:07:34.728 T:3900699360   ERROR: GetSingleValue - failed on query 'SELECT count(1) FROM movie_view LIMIT 1'
2019-09-02 09:07:34.729 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: SELECT count(playCount) FROM movie_view LIMIT 1
2019-09-02 09:07:34.729 T:3900699360   ERROR: GetSingleValue - failed on query 'SELECT count(playCount) FROM movie_view LIMIT 1'
2019-09-02 09:07:34.730 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: SELECT count(1) FROM musicvideo_view LIMIT 1
2019-09-02 09:07:34.731 T:3900699360   ERROR: GetSingleValue - failed on query 'SELECT count(1) FROM musicvideo_view LIMIT 1'
2019-09-02 09:07:34.731 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: SELECT count(playCount) FROM musicvideo_view LIMIT 1
2019-09-02 09:07:34.732 T:3900699360   ERROR: GetSingleValue - failed on query 'SELECT count(playCount) FROM musicvideo_view LIMIT 1'
2019-09-02 09:07:34.733 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: SELECT sum(watchedcount) FROM tvshow_view LIMIT 1
2019-09-02 09:07:34.733 T:3900699360   ERROR: GetSingleValue - failed on query 'SELECT sum(watchedcount) FROM tvshow_view LIMIT 1'
2019-09-02 09:07:34.734 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: SELECT sum(totalcount) FROM tvshow_view LIMIT 1
2019-09-02 09:07:34.734 T:3900699360   ERROR: GetSingleValue - failed on query 'SELECT sum(totalcount) FROM tvshow_view LIMIT 1'
2019-09-02 09:07:34.735 T:3900699360   ERROR: SQL: [kodi_video116] The table does not exist
                                            Query: SELECT sum(watchedcount = totalcount) FROM tvshow_view LIMIT 1

The database may be only partially created – can you drop it?
Alternatively, there may be a permissions problem.

3

What do you mean by drop it?

4

I rather think you don’t have given the kodi user the right access rights to the mysql database.
Check the docks/guide you have followed for the part where you need to create the kodi user inside the BD, and provide it special privileges.

Issue with kodi is that you can’t give it access to a shared MySQL DB. Well - you can, but then the Kodi user can do whatever he wants with alkl the other databases as you need to provide it grant privileges as it creates the tables from scratch:

ERROR: SQL: [kodi_video116] The table does not exist

If you don’t give the kodi user grant/ALL privileges, you will have a mess at the next DB version upgrade.

I solved it by created a docker-mysql Instance solely reserved for Kodi. So whatever happens to the instance, only the kodi data would be affected (Of course, I take backups)

5

You remove the database that is messed up so you can start over from scratch. In your case you would be dropping “kodi_video116” and “kodi_music72”.

But before that you would probably need to go back to the privileges and make sure you have enabled full access for the kodi user.

6

I essentially followed this guide. It’s out of date in some areas (e.g. service enabling in the Synology control panel is not needed if you installed the packages), but the key thing is around set up of a user with the correct privileges. It worked first time for me (MariaDB10, port 3307 as you have.)

7

You’re using non-standard database names, ie kodi_video116 and kodi_music72.

Can you access them from the Vero4K+? (You’ll need to install a mariadb client package to test this.)

sudo apt-get update
sudo apt-get install mariadb-client-core-10.1
mysql -u kodi -h 10.0.1.22 -P 3307 -p

If you can log on:

show databases;
use kodi_video116;
select count(*) from movie_view;
10

What is considered standard database name?

11

I deleted database and started over. Movies are working. But now I get sad face when trying to access music. I can’t even go to media settings to add music source. Any suggestions?

Log
https://paste.osmc.tv/kijojocafi

12

The standard database name is to not use the tag at all. As for the crash I would try removing the tag from the music section in your advancedsettings file (just leave it in the video section since that is already working) and see if that works for you.

13

Thanks! Dropping the name did the trick. Now Im wondering if i should drop name in video too or just leave it alone since its working

14

To be completely honest with you I have no idea why that name tag is not a recommended option. I use it on my video databases because I have more than one and I have never had an issue. I have never used the name tag on my music database though so i’m not sure if there is an issue using it there or not.

16

I agree, I’ve been using the kodi_ name for my databases for years without a glitch.