(2019-06-29, 15:06)Razze Wrote: Can you take an example movie that doesn't work and see what the `uniqueid` value in the database for that movie is?
After a bit of investigation and playing around earlier today, I think I've managed to solve this via applying some updates directly to the Kodi database. I'm using a shared MySQL database rather than individual sqlite ones, so it was pretty straightforward to get it applied everywhere at once.
Basically, I had lots of records in the uniqueid table with a type of unknown instead of tvdb, imdb or tmdb. I have no idea how they had gotten into that state and why there was such a mix, where some movies and tv shows had unknown types and others didn't. As I came to post this update, I noticed that Razze has also mentioned the uniqueid table so it seems we're on the same wavelength :-)
I believe there are two ways to solve this; via Kodi and directly via the database.
I'm sure that option 1 (doing so via Kodi itself) would be safer for the majority of people. I believe that changing the content type of your mounted share to None (clicking no when prompted to remove items) and then back to 'TV Shows' or 'Movies' should re-scan your library using whatever settings are in place. I did start this and it looked to be working, but was just taking a lot longer than I thought it needed to and stopped it a third of the way in. I wouldn't recommend that others stop it part-way, as I only did so because I knew that I could easily recover my database to a semi-working state. I basically just deleted it and let Kodi migrate a previous version that I had hanging around, then had to scan for new video content.
I ended up going with option 2 (directly in the database) and everything seems to be working so far. I'll include the queries I used below, but would suggest that only people with at least a basic understanding of SQL make use of them. You should at least know what affect executing them could have on your data and how to back up your database prior to doing so.
sql:
-- firstly, get an idea of how many records are currently unknown and what they contain
select count(*) from uniqueid where type = 'unknown';
select * from uniqueid where type = 'unknown';
-------------------------------------------
-- movies
-------------------------------------------
-- see if the records with the highest ids have the correct type (I'd expect anything added recently to have two rows; one with type imdb and one with tmdb)
select * from uniqueid where media_type = 'movie' order by uniqueid_id desc;
-- filter to see if there's any imdb records with an unknown type
select * from uniqueid where type = 'unknown' and media_type = 'movie' and value like 'tt%';
-- filter to see if there's any other records with an unknown type (likely to be tmdb, but could also be from another site - I don't have an exhaustive list of values)
select * from uniqueid where type = 'unknown' and media_type = 'movie' and value not like 'tt%';
-- apply the required changes (the only rows that needed to be updated for me were imdb ones. I've left the tmdb line commented out, but it could be used at your own risk)
update uniqueid set type = 'imdb' where media_type = 'movie' and type = 'unknown' and value like 'tt%';
--update uniqueid set type = 'tmdb' where media_type = 'movie' and type = 'unknown' and value not like 'tt%';
--commit; -- uncomment if you're happy for the changes to be made final
-------------------------------------------
-- tv shows
-------------------------------------------
-- see if the records with the highest ids have the correct type (I'd expect anything added recently to have two rows; one with type imdb and one with tvdb)
select * from uniqueid where media_type = 'tvshow' order by uniqueid_id desc;
-- filter to see if there's any imdb records with an unknown type
select * from uniqueid where type = 'unknown' and media_type = 'tvshow' and value like 'tt%';
-- filter to see if there's any other records with an unknown type (likely to be tvdb, I've just assumed they all are...)
select * from uniqueid where type = 'unknown' and media_type = 'tvshow' and value not like 'tt%';
-- displays a list of tv shows and indicates whether each one has a tvdb id, imdb id and/or an unknown id
select
s.idShow s_id,
s.c00 title,
uid_tvdb.value tvdb_id,
uid_imdb.value imdb_id,
uid_unknown.value unknown_id
from tvshow s
left join uniqueid uid_tvdb on uid_tvdb.media_id = s.idShow and uid_tvdb.media_type = 'tvshow' and uid_tvdb.type = 'tvdb'
left join uniqueid uid_imdb on uid_imdb.media_id = s.idShow and uid_imdb.media_type = 'tvshow' and uid_imdb.type = 'imdb'
left join uniqueid uid_unknown on uid_unknown.media_id = s.idShow and uid_unknown.media_type = 'tvshow' and uid_unknown.type = 'unknown'
order by s.c00;
-- apply the required changes (the only rows that needed to be updated for me were non-imdb ones that I've assumed to be tvdb)
--update uniqueid set type = 'imdb' where type = 'unknown' and media_type = 'tvshow' and value like 'tt%';
update uniqueid set type = 'tvdb' where type = 'unknown' and media_type = 'tvshow' and value not like 'tt%';
--commit; -- uncomment if you're happy for the changes to be made final
