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:
- Install OSMC RC2 (using windows) on the card
- Insert card into RaspPi and let OSMC install
- Connect USB harddrive and mount using fstab
- ssh into the pi and āsudo apt-get update && apt-get install mysql-serverā
(this installs mysql server 5.5)
- From there I follow the instructions outlined here: http://kodi.wiki/view/MySQL/Setting_up_MySQL
- Then I restart kodi and set library paths (following /mnt/ā¦ )
- 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