[SOLVED] MySQL troubleshooting

Help and Support Raspberry Pi
1

Hi all,

This one is getting the better of me!

Recently, I needed to replace the BSD jail running my Kodi MySQL database. So, backed up all databases with mysqldump, and once the new jail was created I restored the sql dump file. Created the user and pass, as best I could with (IMO) limited knowledge of mysql (enough to get by, though). Confirmed that I could connect and browse the db and tables from another computer on the local network, using the kodi user/pass. IP address has remained the same.

The problem is, both the Movies and TV library in Kodi have a single item in them, “1”. Double checked advancedsettings.xml and that’s all good - I recreated everything so that this file really didn’t need to be changed.

It’s my understanding that Kodi should just reconnect to the existing database, and resume without any real input. All the media paths have remained the same, and indeed work. Can play video’s without a problem.

Log file indicates at one point that it was unable to connect to the database, but then other lines (to me) show that it has connected to at least the Main_Video99 db - which is all I use really, don’t have a music source.

Here are some pastes of logs…

Kodi log from last night when I was trying to figure this one out, showing some errors in relation to the SQL connection;
19:59:09 34.888760 T:3023659792 NOTICE: Running database version Addons20 19:59:09 34.897522 T:3023659792 NOTICE: Running database version ViewModes6 19:59:09 34.908333 T:3023659792 NOTICE: Running database version Textures13 19:59:12 37.920975 T:3023659792 ERROR: Unable to open database: Main_Music56 [2003](Can't connect to MySQL server on '192.168.10.19' (113)) 19:59:15 40.921009 T:3023659792 ERROR: Unable to open database: Main_Music55 [2003](Can't connect to MySQL server on '192.168.10.19' (113)) 19:59:18 43.921043 T:3023659792 ERROR: Unable to open database: Main_Music54 [2003](Can't connect to MySQL server on '192.168.10.19' (113)) 19:59:21 46.921715 T:3023659792 ERROR: Unable to open database: Main_Music53 [2003](Can't connect to MySQL server on '192.168.10.19' (113)) 19:59:22 48.060944 T:3023659792 NOTICE: Old database found - updating from version 52 to 56 19:59:22 48.194241 T:3023659792 ERROR: SQL: Can't create database for copy: 'Main_Music52' (1007) 19:59:22 48.196358 T:3023659792 ERROR: Unable to copy old database Main_Music52 to new version Main_Music56 19:59:22 48.213264 T:3023659792 NOTICE: Running database version Main_Video99 19:59:23 48.526951 T:3023659792 NOTICE: Running database version TV29 19:59:23 48.539249 T:3023659792 NOTICE: Running database version Epg11 19:59:23 48.549706 T:3023659792 NOTICE: start dvd mediatype detection 19:59:25 50.886883 T:3023659792 WARNING: JSONRPC: Could not parse type "Setting.Details.SettingList" 19:59:27 53.158939 T:3023659792 NOTICE: ActiveAE DSP - starting 19:59:27 53.196033 T:3023659792 NOTICE: initialize done 19:59:27 53.196407 T:3023659792 NOTICE: Running the application... 19:59:27 53.261234 T:3023659792 NOTICE: starting zeroconf publishing 19:59:27 53.272526 T:3023659792 NOTICE: WebServer: Started the webserver 19:59:27 53.285820 T:3023659792 NOTICE: starting upnp client 19:59:28 53.357140 T:3023659792 NOTICE: starting upnp server 19:59:28 53.429028 T:3023659792 NOTICE: starting upnp renderer 19:59:28 53.566170 T:2644505584 NOTICE: ES: Starting UDP Event server on 0.0.0.0:9777 19:59:28 53.566708 T:2644505584 NOTICE: UDP: Listening on port 9777

Here’s my advancedsettings.xml file for reference;
<advancedsettings> <videodatabase> <name>Main_Video</name> <type>mysql</type> <host>192.168.10.19</host> <port>3306</port> <user>kodi</user> <pass>kodipass</pass> </videodatabase> <musicdatabase> <name>Main_Music</name> <type>mysql</type> <host>192.168.10.19</host> <port>3306</port> <user>kodi</user> <pass>kodipass</pass> </musicdatabase> <videolibrary> <importwatchedstate>true</importwatchedstate> <importresumepoint>true</importresumepoint> </videolibrary> </advancedsettings>

And, this morning I tested connecting to the mysql server from the RPi to double check that this device could in fact reach the database, connect with the user details in advancedsettings.xml, use a database and show tables;

osmc@osmc:~$ mysql -u kodi -pkodipass -h 192.168.10.19
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2870
Server version: 5.7.10 Source distribution

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Main_Music48       |
| Main_Music52       |
| Main_Music56       |
| Main_Video90       |
| Main_Video93       |
| Main_Video99       |
| MyMusic48          |
| MyVideos90         |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
13 rows in set (0.00 sec)

mysql> use Main_Video99;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------+
| Tables_in_Main_Video99 |
+------------------------+
| actor                  |
| actor_link             |
| art                    |
| bookmark               |
| country                |
| country_link           |
| director_link          |
| episode                |
| episode_view           |
| files                  |
| genre                  |
| genre_link             |
| movie                  |
| movie_view             |
| movielinktvshow        |
| musicvideo             |
| musicvideo_view        |
| path                   |
| season_view            |
| seasons                |
| sets                   |
| settings               |
| stacktimes             |
| streamdetails          |
| studio                 |
| studio_link            |
| tag                    |
| tag_link               |
| tvshow                 |
| tvshow_view            |
| tvshowcounts           |
| tvshowlinkpath         |
| version                |
| writer_link            |
+------------------------+
34 rows in set (0.01 sec)

I am at a bit of a loss … the db, in my opinion, seems to be working, accepting connections, and allowing the kodi user access. I am not interpreting the logfile very well though…

I would really appreciate a bit of help troubleshooting this - my googling hasn’t come up with much help regards troubleshooting this.

Thanks in advance …

2

Opps, ignore that post, i didn’t scroll down in your entry to see that you had done a use to open the database.

3

First please avoid posting logs into the thread either use the log uploader or paste.osmc.io

Secondly in your logs you can see that the databases are updating. Could it be that you just in the meantime upgraded your kodi version from 15 to 16?
I suggest you stop all machines running Kodi in your LAN. Then delete the databases of the v16 version, start one single v16 Kodi and let it update the database. This can take a couple of minutes and you only will see a black screen
http://kodi.wiki/view/Database_versions

4

Actually check that the Kodi user has ALL rights on the complete database server.

5

Error 113 indicates ‘No Route To Host’. Check the IP configuration on the MySQL server, and make sure that it can ping the OSMC Pi.

You may consider just putting the MySQL database on the OSMC Pi. I do it that way, and it works great.

6

Thanks for the extremely fast responses, guys …

Firslty, I apologize for the logs in the thread - I had them in pastebin but was getting an error about being a new user and psting links. I’ll use paste.osmc.io from now on. Sorry, not at home atm, so doing everything via SSH so can’t do log uploader (unless, I can do this from shell?).

So here are the privileges for kodi user;
http://paste.osmc.io/adinayupow.vhdl

And, can successfully ping from MySQL server to RPi;
http://paste.osmc.io/ecahocucir.vbs

And, can ping from RPi back to MySQL server on 192.168.10.19.

Regards the upgrade. Yes, recently the OSMC UI popped up with an update available, and I did that update. That was, however, prior to me backing up the MySQL database so the database upgrade should’ve already happened. It is true, I have some Android devices with Kodi on them as well, using this same mysql server and they are probably a higher version. Either way, everything was upgraded and working as normal prior to any of this work happening.

I have the database stored on a NAS with redundant drives - this allows the Kodi endpoints to be fairly dumb in regards to the database. The idea was that I would sync all devices onto a single db, and that I could reinstall any device on a whim without fear of having to re-add or lose the database (thus, making reinstalls a little easier).

I haven’t deleted the database, and started again because I really didn’t want to lose my watched status on episodes. Just a pain in the butt to have to go thru the media library changing all this again - so, if i can help it, ideally want to re-connect to the existing database but if I can’t really figure out what’s going on then well, no other option might be to delete the db and start over…

Edit: I have no Music source setup (apart from MVids) so I assume that Main_Music should be empty. Main_Video is where all the goods are at. Also, assuming that the higher the number database means the higher the Kodi version? ie. Main_Video99 = Kodi v16?

7

I posted you a link that gives the exact number per version

grab-logs and paste-log are your friends

8

So is only the OSMC Kodi having problems with the database? How about the other devices?
Also as indicated mixing different versions of Kodi will lead to inconsistent databases. Still possible to run but they will not be synced.

Ok, now coming back to the original issue. So let’s ignore the Music Database for a minute. You mentioned only one item is shown, did you check what is the content of the tables in Main_Video99?

mysql> show grants for ‘kodi’;
±------------------------------------------+
| Grants for kodi@% |
±------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘kodi’@‘%’ |
±------------------------------------------+
1 row in set (0.00 sec)

Are you sure you have configured a password for kodi?

9

Yeah, I was thinking this only a few moments ago :slight_smile: I’ll ask the wife to check her tablet.

Here is a link to the output of the tvshow table, under Main_Video99. Used the mysql client on the RPi to connect remotely as kodi user - so, I’m assuming if I can do this that the user/pass is correct? I used (um) SET PASSWORD FOR ‘kodi’ = PASSWORD(‘kodipass’); to set the password on the kodi user, then FLUSH PRIVILEGES;.

http://paste.osmc.io/asedopoqoh.vhdl

And, here is the complete output from grab-logs -X → http://paste.osmc.io/ohidihoqim

Edit: Wife can’t check tablet at the moment … so that’s pending.

10

Okay, got home and fired up the tablet running v16.1 and exactly the same behaviour. Both Movies and TV library have a single item - “1”.

Now, I believe that the Android tablets will have been running v16 for longer than OSMC. I’m not keeping track of when updates were done on the tablet. I wonder - if for some reason when I updated OSMC and it tried to upgrade the database, not realising that there was already a v16 database available - maybe this is some pitfall of “multi-tenanting” differing Kodi versions in a single mysql server.

fzinken’s earlier post mentioned that he noticed OSMC Kodi was upgrading the database - imo, this should’ve happened a while ago. His suggestion was to delete the v16 db, and let a single device upgrade.

So, I am going to delete all v16 db’s then try again…

11

No if there is already a v16 database (e.g. for Videos 99) then no second upgrading would happen.
I think the upgrading notice I saw was on the music database that might have not been upgraded as it is empty (based on what you say).

Suggest to check all the tables of the version of the database.
You might use e.g. phpmyadmin to do that more comfortable.

Thats an option but take a look at the old databases (e.g. last entry) to check if they have the data you want.
Also as mentioned when you do that first shutdown all Kodi devices and only start a single one and let it finish the upgrade

12

Did exactly that - removed the v16 databases and turned off all Kodi devices on the network.

Restarted JUST the OSMC device and it successfully upgraded from the v15 db that still existed.

Now everything is working, and the Android devices are synced, maybe - haven’t really checked out the watched status to see if that’s syncing but meh it’s not the end of the world if that doesn’t work.

There didn’t seem to be a big discrepancy in the data between databses - couple of unwatched items as they were added back in - probably only about a week of discrepancy so that was no biddie compared to starting over.

fzinken - kind of kicking myself as this was your suggestion in your first post. I thought that it might’ve been more destructive that it really was. I appreciate your quick help and persistence with me. Cheers!