[Closed] Libraries not working on mysql (missing views?)

Hello, I was using an older version of Raspbmc on my Raspberry Pi model B+
until for some reason my SD card was fried. So I got out a new SD card and installed the RC2 of OSMC. I am using mysql for the kodi database because I have another RPi that I would like to share the library with.

My movies and music are stored on an external USB harddrive, that I mounted using fstab. This works, and I am able to add the locations as sources for the library. When I tell kodi to start scanning to library, I see the directory titles pass by on the screen. However, no movies or music is actually visible in the library, when I go to Movies > Title, nothing happens (on screen).

I have a sneaking suspicion this has something to do with a number of views not being present in the database, because I see a lot of THIS in the logs:

22:44:15 T:2884056096   ERROR: SQL: The table does not exist
                                            Query: select * from movieview  WHERE ((movieview.playCount IS NULL OR movi$
22:44:15 T:2884056096   ERROR: GetMoviesByWhere failed
22:44:16 T:2884056096   ERROR: SQL: The table does not exist
                                            Query: select * from episodeview  WHERE ((episodeview.playCount IS NULL OR $
22:44:16 T:2884056096   ERROR: GetEpisodesByWhere failed
22:44:16 T:2884056096   ERROR: SQL: The table does not exist
                                            Query: select * from musicvideoview
22:44:16 T:2884056096   ERROR: GetMusicVideosByWhere failed

In fact, there doesn’t seem to be any views at all in the db?

 show full tables;
+------------------------+------------+
| Tables_in_MyVideos90   | Table_type |
+------------------------+------------+
| actorlinkepisode       | BASE TABLE |
| actorlinkmovie         | BASE TABLE |
| actorlinktvshow        | BASE TABLE |
| actors                 | BASE TABLE |
| art                    | BASE TABLE |
| artistlinkmusicvideo   | BASE TABLE |
| bookmark               | BASE TABLE |
| country                | BASE TABLE |
| countrylinkmovie       | BASE TABLE |
| directorlinkepisode    | BASE TABLE |
| directorlinkmovie      | BASE TABLE |
| directorlinkmusicvideo | BASE TABLE |
| directorlinktvshow     | BASE TABLE |
| episode                | BASE TABLE |
| files                  | BASE TABLE |
| genre                  | BASE TABLE |
| genrelinkmovie         | BASE TABLE |
| genrelinkmusicvideo    | BASE TABLE |
| genrelinktvshow        | BASE TABLE |
| movie                  | BASE TABLE |
| movielinktvshow        | BASE TABLE |
| musicvideo             | BASE TABLE |
| path                   | BASE TABLE |
| seasons                | BASE TABLE |
| sets                   | BASE TABLE |
| settings               | BASE TABLE |
| stacktimes             | BASE TABLE |
| streamdetails          | BASE TABLE |
| studio                 | BASE TABLE |
| studiolinkmovie        | BASE TABLE |
| studiolinkmusicvideo   | BASE TABLE |
| studiolinktvshow       | BASE TABLE |
| tag                    | BASE TABLE |
| taglinks               | BASE TABLE |
| tvshow                 | BASE TABLE |
| tvshowlinkpath         | BASE TABLE |
| version                | BASE TABLE |
| writerlinkepisode      | BASE TABLE |
| writerlinkmovie        | BASE TABLE |
+------------------------+------------+
39 rows in set (0.16 sec)

version of kodi:

~$ grep Kodi .kodi//temp/kodi.log | head -2
20:39:46 T:3024515072  NOTICE: Starting Kodi (14.2 Git:Unknown). Platform: Linux ARM 32-bit
20:39:46 T:3024515072  NOTICE: Using Release Kodi x32 build (version for Raspberry Pi)

My version of mysql:

Server version: 5.5.43-0+deb8u1 (Raspbian)

This is my advancedsettings.xml
http://paste.osmc.io/yuqiluzahu.xml

This is my kodi.log
http://paste.osmc.io/raw/azogekifiv

I hope someone knows what could be going on here. Is it possible the views are not created? if so, why not?

I started from scratch today again and was able to reproduce the problem.

I was just able to get movies working by manually adding the views in mysql.

CREATE VIEW episodeview AS SELECT   episode.*,  files.strFileName AS
strFileName,  path.strPath AS strPath,  files.playCount AS playCount,  
files.lastPlayed AS lastPlayed,  files.dateAdded AS dateAdded,  
tvshow.c00 AS strTitle,  tvshow.c14 AS strStudio,  tvshow.c05 AS 
premiered,  tvshow.c13 AS mpaa,  tvshow.c16 AS strShowPath,   
bookmark.timeInSeconds AS resumeTimeInSeconds,   
bookmark.totalTimeInSeconds AS totalTimeInSeconds,   seasons.idSeason AS
idSeason FROM episode  JOIN files ON    files.idFile=episode.idFile  
JOIN tvshow ON    tvshow.idShow=episode.idShow  LEFT JOIN seasons ON    
seasons.idShow=episode.idShow AND seasons.season=episode.c12  JOIN path 
ON    files.idPath=path.idPath  LEFT JOIN bookmark ON    
bookmark.idFile=episode.idFile AND bookmark.type=1;


CREATE VIEW 
movieview AS SELECT  movie.*,  sets.strSet AS strSet,  files.strFileName
AS strFileName,  path.strPath AS strPath,  files.playCount AS 
playCount,  files.lastPlayed AS lastPlayed,   files.dateAdded AS 
dateAdded,   bookmark.timeInSeconds AS resumeTimeInSeconds,   
bookmark.totalTimeInSeconds AS totalTimeInSeconds FROM movie  LEFT JOIN 
sets ON    sets.idSet = movie.idSet  JOIN files ON    
files.idFile=movie.idFile  JOIN path ON    path.idPath=files.idPath  
LEFT JOIN bookmark ON    bookmark.idFile=movie.idFile AND 
bookmark.type=1;


CREATE VIEW movieview AS SELECT  movie.*,  
sets.strSet AS strSet,  files.strFileName AS strFileName,  path.strPath 
AS strPath,  files.playCount AS playCount,  files.lastPlayed AS 
lastPlayed,   files.dateAdded AS dateAdded,   bookmark.timeInSeconds AS 
resumeTimeInSeconds,   bookmark.totalTimeInSeconds AS totalTimeInSeconds
FROM movie  LEFT JOIN sets ON    sets.idSet = movie.idSet  JOIN files 
ON    files.idFile=movie.idFile  JOIN path ON    
path.idPath=files.idPath  LEFT JOIN bookmark ON    
bookmark.idFile=movie.idFile AND bookmark.type=1;

CREATE VIEW 
tvshowview AS SELECT   tvshow.*,  path.strPath AS strPath,  
path.dateAdded AS dateAdded,  MAX(files.lastPlayed) AS lastPlayed,  
NULLIF(COUNT(episode.c12), 0) AS totalCount,  COUNT(files.playCount) AS 
watchedcount,  NULLIF(COUNT(DISTINCT(episode.c12)), 0) AS totalSeasons 
FROM tvshow  LEFT JOIN tvshowlinkpath ON    
tvshowlinkpath.idShow=tvshow.idShow  LEFT JOIN path ON    
path.idPath=tvshowlinkpath.idPath  LEFT JOIN episode ON    
episode.idShow=tvshow.idShow  LEFT JOIN files ON    
files.idFile=episode.idFile GROUP BY tvshow.idShow; 

With music no such luck yet …

I’m running Kodi on a Vero, Pi 2 and Windows 7 and it works great with a mysql database.

My advanced settings looks like this, if it helps:

Link

What does look strange to me is that I have 45 tables in my database.

Could it be that kodi is incompatible with this version of mysql? Is there some way to force recreation of the database schema plus it’s views and tables?

Did you maybe migrate from a “xbmc” database user? If so, maybe the Kodi user you defined in your advancedsettings.xml has insufficient rights on your sql database?

Hi Exo,
yes you can get kodi to recreate the data base by dropping the one you have now. If kodi is not able to find a database on start, it will make a new empty one.

I’m using
libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id: bf9ad53b11c9a57efdb1057292d73b928b8c5c77 $

My kodi user on the DB is granted all rights

Nope I didn’t migrate. I installed OSMC from scratch.

I dropped both MyVideos90 and MyMusic48 and rebooted. Now, I after the OSMC splash screen am greeted with a black screen. I can SSH into the system though, and the log reveals this

20:21:02 T:3023863808  NOTICE: Running database version MyMusic48
20:21:02 T:3023863808   ERROR: Unable to open database: MyVideos90 [1049](Unknown database 'MyVideos90')
20:21:02 T:3023863808   ERROR: Unable to open database: MyVideos89 [1049](Unknown database 'MyVideos89')
20:21:02 T:3023863808   ERROR: Unable to open database: MyVideos88 [1049](Unknown database 'MyVideos88')
(etc, etc, etc)
20:21:03 T:3023863808   ERROR: Unable to open database: MyVideos60 [1049](Unknown database 'MyVideos60')

So I went back to mysql, wrote:
GRANT ALL ON * .* TO ‘kodi’;

Now, kodi would start, but I’m still spammed with messages like this:

                                            Query: SELECT count(1) FROM songview LIMIT 1
20:25:59 T:2865755168   ERROR: GetSingleValue - failed on query 'SELECT count(1) FROM songview LIMIT 1'
20:25:59 T:2865755168   ERROR: SQL: The table does not exist
                                            Query: SELECT count(distinct strAlbum) FROM songview LIMIT 1
20:25:59 T:2865755168   ERROR: GetSingleValue - failed on query 'SELECT count(distinct strAlbum) FROM songview LIMIT 1'
20:25:59 T:2865755168   ERROR: SQL: The table does not exist

http://paste.osmc.io/jitejuloki.vhdl (full log)

And, obviously, the libraries do not work in OSMC. So I’m back to square one.

I can confirm that the tables MyVideos90.movie and MyVideos90.files have actual data inside. I’ve been looking a bit through the source code and it looks to me there should be a log entry saying “create song view” etc when those views are created. I do not see those in my logs (perhaps I’m wrong - I don’t know the kodi internals very well)

In the network settings: did you check the box for osmc to “wait for network” at boot?

I have now, but no change. Once again, I think this is because no views are created in the db.

When I write " show full tables in MyMusic48; " in mysql (logged in as root or kodi, same result)

+---------------------+------------+
| Tables_in_MyMusic48 | Table_type |
+---------------------+------------+
| album               | BASE TABLE |
| album_artist        | BASE TABLE |
| album_genre         | BASE TABLE |
| artist              | BASE TABLE |
| genre               | BASE TABLE |
| path                | BASE TABLE |
| song                | BASE TABLE |
| song_artist         | BASE TABLE |
| version             | BASE TABLE |
+---------------------+------------+

there should be some views there, but there aren’t … I’d be willing to try a different version of mysql server, but I’m not sure on how to install it.

Sorry. I’m out of clues. Maybe someone else…
Only thing I can think of is to drop the database again and reboot with “wait for network” checked, but little chance I guess…

Are there any earliert database versions being shared by MySql? If there are Kodi will try to update them rather than start fresh. Drop allMySql shares and let kodi create the database from scratch

That’s the thing, there isn’t because I’m starting from scratch (i.e. a new SD card). This is literally what I’m doing:

  1. Install OSMC RC2 (using windows) on the card
  2. Insert card into RaspPi and let OSMC install
  3. Connect USB harddrive and mount using fstab
  4. ssh into the pi and “sudo apt-get update && apt-get install mysql-server”
    (this installs mysql server 5.5)
  5. From there I follow the instructions outlined here: http://kodi.wiki/view/MySQL/Setting_up_MySQL
  6. Then I restart kodi and set library paths (following /mnt/… )
  7. I scan the libraries and this happens

I don’t think I’m doing anything out of the ordinary and it is 100% reproducable (by me). If it helps I can try to set up ssh access for someone that wants to have a poke?

Is there no one that can help me? Or at least has some suggestion for me to try something? I tried again with RC1, same result. I tried with a different mysql user, to no avail.

Once again, the database is being updated whenever I scan items to library, but i am unable to retrieve them because of missing views.

What do you mean “missing views” ? Have you tried a different skin?

Views in the mysql database.

20:25:59 T:2865755168   ERROR: SQL: The table does not exist
Query: SELECT count(distinct strAlbum) FROM songview LIMIT 1

songview, movieview, albumview etc I do not seem to have any of them.

Hi exo,
I really dont know, have you tired to set up the database on another device?

Actually that was a pretty good idea. I was able to export the database and reimport it from osmc. That seemed to have done the trick.

Case closed for me. I hope noone else has this problem.

1 Like