Mysql - TV shows not working after update

Help and Support
1

Updated this morning, TV shows no longer work - my database is hosted in mysql on a NAS.

I’ve updated all instances, turned them off, except one, tried deleting the latest version database, restarted, the database upgrade screen comes up, but afterwards TV shows still dont work.

If you click on a show from the start-up screen, it takes you to the Videos screen… if you click on TV Shows > Show Name, the screen just refreshes, but doesn’t do anything.

Movies works fine, and going to the TV show via file sources works fine?

2

Logs please. Probably best to delete the upgraded version of the database, start Kodi and let the database migration run, then post the logs.

3

Here you go:

4

If I run the query that fails - I get a " [error writing file (Errcode: 28)]" - this seems to refer to a space issue, but the NAS has about 10TB free.

If I revert back to an older version of OSMC, the older database works fine.

Browsing the mysql database - all other tables and views I can select from, besides season_view - if I try, I get the above error.

5

That was not a debug log. Please post a kodi debug log.

From that log it would seem that the database upgrade completed. Can you use a tool like phpmyadmin and view the upgraded database?

6

Whats the location of the debug log? I enabled debug logging before deleting the database and restarting, the above log is the location given in the front end. /home/osmc/.kodi/temp/kodi.log

Yes -i can view the database, and all tables/views except season_view

7

It’s kodi.log, but the log you posted did not have Kodi debugging turned on, it’s just a normal Kodi log. SInce you need to restart Kodi, you may need to add this to your advancedsettings.xml

<loglevel>1</loglevel>

So, stop kodi, add the above to advancedsettings. Remove the migrated database and restart Kodi. Post the logs again when the migration is complete.

8

Take two:

9

So that shows the migration working and the view being created with no errors. Very odd. Have you tried rebooting the NAS? If rebooting the NAS doesn’t help, you probably need to bring this up on the Kodi forums.

10

Yes, tried rebooting the NAS01, no difference.

If I view the new database in phpmyadmin - the season_view view shows 0 rows, and I can’t select from it without error - I’ve now tried recreating the view with the definition from the old database - and it now returns data, next step to see if Kodi still functions.

OLD DEFINITION:

CREATE ALGORITHM=UNDEFINED DEFINER=kodi@% SQL SECURITY DEFINER VIEW season_view AS
SELECT seasons.idSeason AS idSeason,seasons.idShow AS idShow,seasons.season AS season,seasons.name AS name,seasons.userrating AS userrating,tvshow_view.strPath AS strPath,tvshow_view.c00 AS showTitle,tvshow_view.c01 AS plot,tvshow_view.c05 AS premiered,tvshow_view.c08 AS genre,tvshow_view.c14 AS studio,tvshow_view.c13 AS mpaa, COUNT(DISTINCT episode.idEpisode) AS episodes, COUNT(files.playCount) AS playCount, MIN(episode.c05) AS aired
FROM (((seasons
JOIN tvshow_view ON((tvshow_view.idShow = seasons.idShow)))
JOIN episode ON(((episode.idShow = seasons.idShow) AND (episode.c12 = seasons.season))))
JOIN files ON((files.idFile = episode.idFile)))
GROUP BY seasons.idSeason;

NEW DEFINITION:

CREATE ALGORITHM=UNDEFINED DEFINER=`kodi`@`%` SQL SECURITY DEFINER VIEW `season_view` AS
SELECT `seasons`.`idSeason` AS `idSeason`,`seasons`.`idShow` AS `idShow`,`seasons`.`season` AS `season`,`seasons`.`name` AS `name`,`seasons`.`userrating` AS `userrating`,`tvshow_view`.`strPath` AS `strPath`,`tvshow_view`.`c00` AS `showTitle`,`tvshow_view`.`c01` AS `plot`,`tvshow_view`.`c05` AS `premiered`,`tvshow_view`.`c08` AS `genre`,`tvshow_view`.`c14` AS `studio`,`tvshow_view`.`c13` AS `mpaa`, COUNT(DISTINCT `episode`.`idEpisode`) AS `episodes`, COUNT(`files`.`playCount`) AS `playCount`, MIN(`episode`.`c05`) AS `aired`
FROM (((`seasons`
JOIN `tvshow_view` ON((`tvshow_view`.`idShow` = `seasons`.`idShow`)))
JOIN `episode` ON(((`episode`.`idShow` = `seasons`.`idShow`) AND (`episode`.`c12` = `seasons`.`season`))))
JOIN `files` ON((`files`.`idFile` = `episode`.`idFile`)))
GROUP BY `seasons`.`idSeason`,`seasons`.`idShow`,`seasons`.`season`,`seasons`.`name`,`seasons`.`userrating`,`tvshow_view`.`strPath`,`tvshow_view`.`c00`,`tvshow_view`.`c01`,`tvshow_view`.`c05`,`tvshow_view`.`c08`,`tvshow_view`.`c14`,`tvshow_view`.`c13`;
1 Like
11

Just to confirm, everything seems functioning again using the old definition.

12

That’s strange. I didn’t see an issue like this when I migrated my MySQL database. You may still want to bring this up on the Kodi forums to see if they have any idea what may have happened.

13

Same issue here after I upgraded. I’ll look later on and see what I can find. I’ll share the best logs too.

14

The only thing of significance that was changed in the two definitions is the newer one has more GROUP BY fields.

The new definition also forces every table and field name to be treated as an identifier and not a built-in, which isn’t really necessary, but should do no harm.

15

When trying to view the individual videos the log shows the following error which suggests the group by could be an issue

2019-04-23 23:26:04.744 T:1915549232   ERROR: CGUIMediaWindow::GetDirectory(videodb://tvshows/titles/26/) failed
2019-04-23 23:26:05.004 T:1685074656   ERROR: SQL: Missing result set!
2019-04-23 23:26:05.005 T:1685074656   ERROR: GetSeasonsByWhere failed
2019-04-23 23:26:05.015 T:1685074656   ERROR: GetDirectory - Error getting videodb://tvshows/titles/26/
2019-04-23 23:26:05.143 T:1685074656   ERROR: SQL: Missing result set!
2019-04-23 23:26:05.144 T:1685074656   ERROR: GetSeasonsByWhere failed
2019-04-23 23:26:05.155 T:1685074656   ERROR: GetDirectory - Error getting videodb://tvshows/titles/24/
2019-04-23 23:26:05.288 T:1685074656   ERROR: SQL: Missing result set!
2019-04-23 23:26:05.289 T:1685074656   ERROR: GetSeasonsByWhere failed
2019-04-23 23:26:05.300 T:1685074656   ERROR: GetDirectory - Error getting videodb://tvshows/titles/1/
2019-04-23 23:26:05.433 T:1685074656   ERROR: SQL: Missing result set!
2019-04-23 23:26:05.434 T:1685074656   ERROR: GetSeasonsByWhere failed
2019-04-23 23:26:05.446 T:1685074656   ERROR: GetDirectory - Error getting videodb://tvshows/titles/1/
16

My guess is that the skin is using the season_view as a base and filtering against it in some way and those filters are failing to return any results, but I can’t be sure without logs from the MySQL server.

17

Interesting, when I use MysSQL front to look at the season_view it fails saying Error "1038 Out of sort memory, consider increasing server sort buffer size

It could be related. Happy to share MySQL logs too if that helps?

18

Would it happen to be related to this thread…
https://forum.kodi.tv/showthread.php?tid=340095&pid=2823811#pid2823811

19

Hmm, increased to 32MB, still the same issue.

20

Since Kodi constantly connects and disconnects, the variable must be changed on a global basis, which can impact performance otherwise.

One thing you could try is to add indexes to the columns that are included in the GROUP BY clause if they don’t already exist. This will reduce the amount of sort memory needed. I don’t have a v18 install, but on v17, the following are candidates for adding indexes:

seasons.name
seasons.userrating
tvshow.c00
tvshow.c01
tvshow.c05
tvshow.c08
tvshow.c13
tvshow.c14