Database (MYSQL) not working after upgrade

Help and Support
1

After this update, my video library (on MySQL) is gone, and the unit no longer seems to respond to scan commands. The SMB network mounts are fine, but just no scan, no library.

2

Have you given it enough time (>10 Minutes) to be upgraded?
Also if you have multiple devices watch out they all use the same Kodi version but suggest not to update at the exact same moment.

3

You should make a new thread but check out Common Problems, Method 2 here http://kodi.wiki/view/MySQL/Upgrading

4

I stopped OSMC on both machines, dropped MyVideos99 and then started OSMC on one machine. Looks like it is now doing the db upgrade again. Previously, I had just rebooted the machine after seeing a black screen for a long time, assuming that something was wrong. Perhaps a message saying “Updating database, please wait…” would be in order?

5
6

Was it only the Video Database that was created? Check Music and the others http://kodi.wiki/view/Database_versions

7

Also, the update is crazy slow. They have these new things called indexes in MySQL now… :wink:

For example,consider…

UPDATE episode SET idSeason = 991 WHERE episode.idShow = 60 AND episode.c12 = 608

The problem with this update statement is as follows:

  1. You need to have indexes on both episode.idShow and episode.c12 (if these are often used together, create a combined index). That would make this statement go much faster, if not for problem two…
  2. The upgrade is supplying an integer for c12, but the field is varchar(24). That causes bad slowness because for EVERY statement like this (and there are many), MySQL will convert the entire table of varchar(24) c12 values to integers in order to match against the integer in the statement. One solution would be to supply those values in quotes, but the correct fix is to make that field an integer datatype.
8

I only have videos.

9

OMG it is still going on those same statements. Its doing like 1 per second where it should be doing like 1000 per second!

10

Okay, so the first time I ran that db upgrade, it took about 40 minutes running on a raspberry pi, connecting to a powerful mysql server. As a test, I stopped OSMC again, dropped the MyVideos99 db again, and then started OSMC again to rerun the upgrade. This time, as soon as the new database appeared, I ran this:

ALTER TABLE `MyVideos99`.`episode` 
CHANGE COLUMN `c12` `c12` INT(11) UNSIGNED NULL,
ADD INDEX `ix_show_c12` (`idShow` ASC, `c12` ASC);

After that, the db upgrade now took about 10 seconds (not even 10s).

Lesson for today: mysql data types and indexes matter. :bulb:

11

I’m sure Kodi would appreciate a patch/fix if you feel motivated

1 Like
12

I’ve never looked at the repo, but could rummage around and try to find the problem… If anyone knows where I would start looking in order to find the database upgrade code for this upgrade, please chime in.

13

My videodb upgrade ran in seconds…

14

These are all questions better directed directly to the Kodi developers over at http://forum.kodi.tv as you are talking about the basic design and implementation of MySQL on Kodi on all platforms. None of the behaviour of Kodi’s MySQL implementation is OSMC specific and the OSMC devs don’t have the manpower to do any major restructuring of Kodi’s MySQL implementation.

Kodi’s upstream Github repo is over at GitHub - xbmc/xbmc: Kodi is an award-winning free and open source home theater/media center software and entertainment hub for digital media. With its beautiful interface and powerful skinning engine, it's available for Android, BSD, Linux, macOS, iOS, tvOS and Windows. if you want to look directly at the code and submit PR’s. Arm versions of Kodi on OSMC are built from slightly different trees and have many patches applied, but I don’t think there is anything MySQL specific in these changes.

15

OMG this is still an issue in the upgrade to 17. How sad! Over 40 minutes and counting for a database migration that should take perhaps 10 seconds.

16

Submit a pull request to Kodi and get it reviewed then.

17

I have no idea where in that pile of code the index creation line would go. I spent hours looking last time.

18

If you grep for the query you’d probably find it pretty fast.

I’ll make a note of this and see if it can be PR’d.

19

Will clone the repo and grep for it AGAIN…

20

And remember what @DBMandrake wrote