TV shows only accessible through Videos, not TV Shows menu item

pinkfloydfan · 10 October 2018 20:46

Hi there,

A rather strange issue has just reared itself on my system last night and I am absolutely stumped on what might be wrong and how to fix it.

I have 3 Vero4K+ and 1 PC (running Windows 10) that are all linked to a shared MySQL Kodi database that sits on a Synology 1812+ NAS. Generally I use the Vero’s - the PC is my normal working machine so I don’t often use it to access Kodi unless I want to more speedily update the database. It is worth noting that each computer is assigned a static internal IP address.

I’ve never had a problem with this setup before. Everyone in the house is able to control them all using the official Kodi remote app for their phones - this is generally a lot quicker to use to select what you want to watch than using the Kodi remote and going through all the onscreen menus. Each computer is assigned a static internal IP address.

Last night, we were watching one TV episode absolutely fine. We finished that, did some jobs around the house and came back around an hour later to select another to watch. For no apparent reason none of the TV Shows were available to select: the app was giving a JSON error and couldn’t find any at all (sorry I didn’t keep a record of the particular error), the onscreen menu had no pictures of the TV shows I had in the database and when trying to access them through Videos I just got a terminally blank, blue screen. The Movies however were available although every machine acted the same way and was painfully slow even after multiple reboots.

Thinking there might be a problem with the NAS drive, I checked on my PC and had the same error on the Kodi there - but all the files were still available through Windows Explorer.

I updated the NAS drive and all the apps on it, I tried removing the TV Show source and re-adding it, I checked with phpMyAdmin that the MySQL database was still intact and rebooted everything again. I’ve updated and cleaned the database a few times and am now at the point where all the Movies are available on the front menu of every instance of Kodi but there is nothing showing under TV Shows except Categories, Genres & Studios although trying to access a show through one of these items doesn’t give any results onscreen but if you check the filter it implies there should be some.

Interestingly, the Kodi remote app does show all the TV Show banners and will refresh but when you select a show from there it says “No items found”.

I can still find any episode via Video–>TV Shows–>Title or Videos–>Files–>TV Shows but that’s not the user experience I am used to. It is also strange that every episode of every show is marked as “watched” - I would have thought that having removed and re-added the source that they would all be marked as “unwatched”.

It all seems rather weird to me. I supposed I could try creating a fresh database but I was wondering if anyone here had any other ideas how to tackle this?

ActionA · 10 October 2018 20:58

To get a better understanding of the problem you are experiencing we need more information from you. The best way to get this information is for you to upload logs that demonstrate your problem. You can learn more about how to submit a useful support request here.

So, in summary:

activate the logging
reboot the OSMC device
reproduce the issue
upload the log set either using the Log Uploader method within the My OSMC menu in the GUI or the ssh method invoking command grab-logs -A
publish the provided URL from the log set upload, here

Thanks for your understanding. We hope that we can help you get up and running again shortly.

pinkfloydfan · 10 October 2018 21:26

Sorry about that, here’s the log I just uploaded: http://paste.osmc.tv/mavihasace

I went to TV Shows, tried to select a file in the genres/comedy category and there were none available. Then I used the app to refresh the TV shows and tried to select one to watch and again none were available.

I hope you manage to find something

Thanks very much for investigating

pinkfloydfan · 10 October 2018 21:47

Here are some screenshots, the first one shows an empty TV Shows menu item, the second was after selection the genre Action and you can see that whilst there were no results it recognises there are 152 actors and 8 studios to filter on and the third is via Videos where I selected season 1 of Alias.

Thanks again

ActionA · 10 October 2018 21:59

There seem to be some errors associated with your MySQL db. Are you able to use some type of SQL explorer to confirm things are in order there?

sam_nazarko · 10 October 2018 22:07

Have you had a recent powercut?

pinkfloydfan · 10 October 2018 22:14

Hi ActionA & Sam

No powercut recently and no unexplained shutdown of the NAS drive.

I also used phpMyAdmin to confirm that the database is still there and it is, please see the attached screenshot.

Ultimately, if you think the database is corrupted and can’t be fixed I guess I’ll just create a new one and link to that instead.

Thanks very much

sam_nazarko · 10 October 2018 22:15

That’d be my first suggestion.
You could backup the DB for safe keeping

Sam

dillthedog · 11 October 2018 19:23

This looks more like a Kodi bug to me. Your log is showing a number of MySQL 1066 errors, and fortunately the offending SQL is also listed.

Here’s the first error (reformatted for clarity):

22:20:34.629 T:4076401232   ERROR: SQL: [kodi_video107] Undefined MySQL error: Code (1066)
Query: 
SELECT genre.genre_id, genre.name 
FROM genre 
JOIN genre_link ON genre.genre_id = genre_link.genre_id 
JOIN tvshow_view ON genre_link.media_id = tvshow_view.idShow AND genre_link.media_type='tvshow' 
JOIN genre_link ON genre_link.media_id=tvshow_view.idShow AND genre_link.media_type='tvshow' 
WHERE genre_link.genre_id = 2 
GROUP BY genre.genre_id;

When I plug this same code into my sqlite3 database, it also produces an error:

Error: ambiguous column name: genre_link.genre_id

The error seems to be related to the third JOIN clause, which (again) joins genre_link and tvshow_view on the basis of genre_link.media_id=tvshow_view.idShow AND genre_link.media_type='tvshow'.

If the apparently redundant third join clause is removed, the code works:

sqlite> SELECT genre.genre_id, genre.name 
FROM genre 
JOIN genre_link ON genre.genre_id = genre_link.genre_id 
JOIN tvshow_view ON genre_link.media_id = tvshow_view.idShow AND Genre_link.media_type='tvshow' 
WHERE genre_link.genre_id = 2 
GROUP BY genre.genre_id;
2|Mystery

The same redundant join statement appears in all 8 1066 errors.

Can you confirm that it works if you don’t select on the basis of genre?

(I would try it myself but currently don’t have access to the TV. )

Edit: I also noticed a few 1064 errors in the log, which are to do with invalid SQL syntax. Not sure what’s going on, but I’m unfortunately out of time for today.