Watched Episode Status not updated (SQL Setup)

Help and Support
1

When using an SQL setup the watched status count might not be updated when you e.g. leave from inside a season few to the season view itself if it takes longer than one second.

See: Watched count of episodes is not getting updated on seasons-list and titles-list for reference and details. Such hardcoded stuff in related to SQL query is probably at more locations.

Any chance OSMC could be patched to expose a configurable setting?

The 1 second doesn’t really cut it with a larger library. Would be a shame to buy another small dedicated PC just for running an SQL that is more powerful than my Vero just to circumvent the hardcoded 1 second introduced with Krypton.

I can’t really recompile Kodi for the Vero 4K. No real clue how…

2

We believe this is a Kodi issue or a specific Kodi function and as such you should look at addressing the issue there.

3

Yep, it’s a Kodi issue.

From a brief read of the thread you linked to, it points to some code in xbmc/windows/GUIMediaWindow.cpp that reads:

// took over a second, and not normally cached, so cache it
 if ((XbmcThreads::SystemClockMillis() - time) > 1000  && items.CacheToDiscIfSlow())
   items.Save(GetID());

I haven’t delved much further but it seems that Kodi has a list of things that are sensible to cache and those that aren’t, probably things such as changeable data, like view counts. Of the second group, some (or perhaps all) are classified as “cache to disc if slow”. This is a design decision on the part of the Kodi developers and I very much doubt if OSMC is going to patch it (and then redesign the interface to make it a configurable setting).

If you really want to remove this “feature”, you’re going to have to patch and build Kodi yourself. It’s actually not that difficult but you’ll of course lose the change each time a new update of Kodi gets installed.

4

Thanks…

Guess it’s setting up an osmc dev VM for me and getting into compiling xbmc in general for the the desktops here as well. Well have my work cut out then for the weekend…

5

Got it working after a lot of work. So sloooooow compiling in a VM.

Is there a way not to compile ibavcodec, addons and stuff, and just the Kodi binary easily? In case I want to make more changes and not to wait several hours for the build to finish in the VM. Even if I do not do a “make clean”, everything is basically built from scratch for Kodi every time.

Anyway, it is just the line quoted above the Kodi devs added using a cached view with Krypton if query took longer than 1 second (hardcoded sigh). Didn’t find such a thing anywhere else. Show me the average NAS or Pi that can do the involved query on the season_view if you have a somewhat medium to large library (plus the other queries executed). Nah, doesn’t exist.

This is not the way to address shortcomings of sloppy DB design and unoptimized queries. There is a reason it was never cached for years: because watched status. But well Krypton broke so much stuff, hope Leia gets better again. But well we know optimizing is not high priority for the Kodi devs…

The season_view is extremely bad as it basically requires the SQL DB to read the whole episode table, which can take a few seconds. In fact after some testing this view is the worst of all - followed by the tvshow view (though it is not as bad).

So after commenting out the line (didn’t bother to add a config option, as I would never turn it on because of inconsistencies displayed at the user end - watched status is important), everything works as it should. Unspecified Behavior fixed.

Now I can work on speeding the stuff up.

Probably not bothering optimizing the SQL queries as the main flaw is using a view there in the first place because of laziness. Guess I replace it with a table and add some triggers so that this table is properly updated automatically, that should make the extremely slow season view several times faster. Might make sense to do the tvshow view as well - the rest seams OK as the views should be fast enough as they are very simple - though maybe I remove all of them just for good measure in the future as well.

6

I thought that unless you did make clean that only changes would be compiled. Sam can answer that for sure.

I’ve always said that the Kodi database design needs lots of work. When you see a database with columns names C01 etc…

1 Like
7

If compiling for ARM, don’t use a VM. Use the target device.

We are open to downstream improvements :slight_smile:
See Update 2017.10 (Kodi 17.5) is behaving strangely with webdav

9

You can bet I get another Vero 4K just as a device for testing, so I do not ruin the one in the living room by accident. Best small box for Kodi I ever bought. Cannot repeat that often enough.

10

If you build on the device it will not ruin it.
It’s a one line command to clean up the target environment.

You can also reinstall trivially from Download - OSMC.

We encourage tinkering. You may earn yourself a reinstall at times, but you would struggle to brick the device.

Sam

11

OK, will do a recompile on the device itself then if you say that is better. Don’t want something unstable in the living room. You’re the pro here, and I am the noob.

…and it didn’t work on the device (Vero 4K) itself.

Just to make clear what I did on the device itself:

sudo apt-get update
sudo apt-get install build-essential git
git clone https://github.com/osmc/osmc
cd osmc
git checkout tags/2017.10-1
cd package/mediacenter-osmc
make vero3

…wait…wait…wait…slow, but significant faster than in the VM, probably because all cores are properly utilized. VM never used more than one core of the host, even though I assigned more to it. Not investigating if I can compile on the device without negative side-effects.

sudo systemctl stop mediacenter
sudo cp files/usr/lib/kodi/kodi.bin /usr/lib/kodi/kodi.bin
sudo systemctl start mediacenter

Kodi itself is running and seems to work without any issues. But sad face when starting playback! Guess I did something wrong when building on the device itself.

Any suggestions? Anything else needed beside kodi.bin if I just modified GUIMediaWindow.cpp - Maybe I copied other stuff over before from the VM but don’t remember.

I am puzzled…For saving time building on the Vero 4K itself would be way better…

PS: when removing the build environment I just remove /opt/osmc-tc and the git clone, right?

12

Sure, just install the package with dpkg -i package.deb after compile is finished

13

You misunderstood me. The other files from the addons are binary indentical. No need to install them.

But of course was first thing I tried: Binary not working, so I installed the full package.

So it looks like - for whatever reason - another one of the packages is referenced by Kodi that I also need to built. Any idea what it might be when the player starts? FYI: using refresh rate changer.

On VM I built all packages (basically the whole image) initially, but don’t know anymore if I transferred more than one to the actual device.

Any idea? If not guess I have to wipe the device as something is clearly not ok to use the compiled binary from the box. Original binary or the one from the VM work fine. Yeah, it’s strange. Sure, a mistake I made somewhere.

14

You only need to build the Kodi package

15

Solved it. I somehow had touched the Kodiconfig.cmake by accident with wrong version information.

All good now…

If anyone else is really interested in not caching the windows, I can create a proper PR and make it an option for the advancedsettings based on the upcoming November update. Just let me know. Not a biggie to add another option.

16

Update on my efforts to get rid of the views:

Well what should I say, there is basically no distinction anymore between a local DB and my SQL DB for TV Shows. Exactly as it should be given the actually small amount of information stored in the DB.

Accessing file lists of the network is slower. Exactly as it should be. DB should be faster always. And in case something might take longer than expected I also turned off the caching for GUIWindows, so they are always updated.

Before loading the season view took several seconds with my quite large library of TV shows. That’s how bad the design is.

Probably no need to do tvshowcounts, but will do it anyway I guess.
Might also consider doing the movie_view, as it feels slow now, never did before.

So more triggers to do…

17

Great work! You should share what you’ve done with the Kodi team. Maybe they will include your improvements.

18

Interesting. Could you give a bit more detail about what you’ve done?

19

Sure, here is some wall of text explaining what I did:

Renamed “tvshow_view” and “season_view” so I have them just in case I screw up.
Then copied the content of these views to new tables replacing the old views.
I now have “tvshow_view” and “season_view” as a table

At this point you already can test the speed difference if fiddling with the DB is worth your time.

For me on with over 30K TV episodes opening the season view took very long. Reason is the way the views/tables are designed, and even worth season_view is referencing the tvshow_view. On top of that the queries are quite bad as well.

For me opening a larger TV show with a lot of season and episodes could take several seconds. Very annoying delay. Sure you get used to it. Felt often like a HDD is waking up…

So far so good.

Next I created an update procedure for the both views, so changes to the underlying tables are reflected. Just formalism, nothing special here. They are basically doing the query the view did before but now doing an update as well afterwards to the new table.

These two procedures take naturally the season ID or the TV Show ID as a parameter, which is also the primary key on the tables I created.

Also I needed to create some helper procedures that take File ID, Path ID, Show ID, or ShowID plus Season ID as a parameter. These do nothing more than a quick SQL query getting me the season ID and then call the main procedure (TV Shows only needed wrapper for File ID and Path ID).

Then setting up the update, insert and delete triggers for the various scenarios (e.g. watched status in files changed) and call the appropriate procedures I create before.

Sounds more complex than it is: Think of it like this

  1. Trigger called
  2. Trigger calls helper procedure with e.g. File ID
  3. Helper procedure gets TV Show ID from File ID
  4. Helper procedure calls main TV Show update procedure to update a row in TV Show View.

and steps 2 to 4 and repeated with the season then.

Now the two new tables get updated whenever something changed. No view needed anymore. That means reading is really fast compare to before.

How did I know which tables need a trigger?

That is simple: I looked at the view and from which underlying table the data came from. And naturally then that table needed a trigger.

Now it is really fast navigating… The “wait icon” though pops up for a fraction of a second. Need to check where to turn that off. Probably hardcoded in relation to SQL queries maybe. Need to check.

it is as fast basically as a local DB. Well almost.

We talking now 250ms for example when opening Cheers with 11 season and 273 episodes. I think that one took 5+ seconds or so before.

As said the way the DBs and the queries are designed your SQL database is basically forced to get all epsiode information to create the season view.

The “tvshowcounts” view is OK. But it is responsible right now for most of the delay :slight_smile:
I will do that probably as well just for good measure.

Then apply the same logic to movies.

If you know SQL basics this is nothing complex. Just a lot of work to type it down and test stuff of course.

Will not do music videos as I do not have any. And probably in the future music. Though I do not store much music right now in Kodi. Though have a ton of it, I might scrape into Kodi now. Tried once and it was unbearable slow.

Once I am done I can share my SQL script here (use at own risk of course). Just execute it and you are done. Naturally with DB schema updates on Leia some adjustments might be needed. But Kodi will update perfectly fine! The changes would just go poof.

Anyway off doing the other views for videos now…

20

A big thanks for the excellent write-up.

I totally agree that the Kodi database design is in need of some TLC - and I suspect that it suffers to some degree from being patched and extended by different people over the years. The net result is it will (and has) inevitably become a bit clunky over time - but works for most people.

I don’t have 30,000 episodes. In fact, I only have 288 and my sqlite3 database can pull back season_view in 35 milliseconds on my Vero4K (and 6 milliseconds once the file has been cached). I suspect that this is one of the reasons why the database hasn’t received much attention - very few people have such a large collection as you, and for most users it just about does the job.

I can’t run any tests using 30,000 episodes but my first port of call in such a situation would have been to see if one or two judiciously placed indexes would have improved matters, and to ensure that table statistics have been collected and are up-to-date. It would probably perform less well than your solution but have a higher chance of making it through to the Kodi build.

I guess it goes without saying that your SQL script should work both with mysql/mariadb and sqlite3.

21

I once tried adding some indexes, did near to nothing.

Also need to investigate something else:

01:43:37.434 T:3140482032  NOTICE: WakeOnAccess [192.168.100.36] trigged by accessing : MySQL : MyVideos107
01:43:37.514 T:3140482032  NOTICE: WakeOnAccess success exit, server already running

Hmmm. I was sure I have turned it off in the Power Management settings. Need to check…

Edit: Yeah, was on in guisettings for whatever reasons… So user error :):rofl: