[tommyhome]

Hi,
I recently migrated to a MySql database which worked fine, the performance for series with a lot of eps and when having alot of movies is awesome compared to the default method of storage.

But, for some reason, and with no exception the library "Movies" does not work any longer, it just says "invalid path", and I'm in library mode.

so, i tried to remove all movies, cleaned the library, restarted xbmc and the "movies" shortcut in the library disappears as it should, i then added a new folder to be indexed in xbmc and it finds the movies and the "movies" in library mode is again visible, but, the error persists, when trying to access the "Movies" in library mode it just says "invalid path".

What has gone wrong and how do i fix it?

I run mysql 5.5, xbmc dharma, windows xp on xbmc machine and win7 on db machine.

[Aenima99x]

Do you have a music library on MySql as well? Can that be accessed fine? Post a debug log so too.

[tommyhome]

Hi,
I have mostly tv-series and they work fine, just to test I set up music now to, and it works fine too, its just the "movies" part of the library that malfuctions.

After checking the logfile i can see that a table is missing, called "movieview" gonna create it and see what happens.

[tommyhome]

obviously i did someting wrong,
now the latest added movies work, but not the "movies" one.

the error parts in the file are;

Code:

ERROR: SQL: The table does not exist
                                            Query: SELECT sets.idSet,sets.strSet,COUNT(1) AS c,count(files.playCount) FROM sets JOIN setlinkmovie ON sets.idSet=setlinkmovie.idSet JOIN movie ON setlinkmovie.idMovie=movie.idMovie JOIN files ON files.idFile=movie.idFile  GROUP BY sets.idSet HAVING c>1
19:23:41 T:6428 M:193466368   ERROR: CVideoDatabase::GetSetsNav failed
19:23:41 T:6428 M:193466368   DEBUG: CVideoDatabase::GetMoviesByWhere query: select * from movieview WHERE movieview.idMovie NOT IN (SELECT idMovie FROM setlinkmovie s1 JOIN(SELECT idSet, COUNT(1) AS c FROM setlinkmovie GROUP BY idSet HAVING c>1) s2 ON s2.idSet=s1.idSet)
19:23:41 T:6428 M:193466368   ERROR: SQL: The table does not exist
                                            Query: select * from movieview WHERE movieview.idMovie NOT IN (SELECT idMovie FROM setlinkmovie s1 JOIN(SELECT idSet, COUNT(1) AS c FROM setlinkmovie GROUP BY idSet HAVING c>1) s2 ON s2.idSet=s1.idSet)

perhaps i didnt get the code for creating the view right? where can i find the most recent one?

[tommyhome]

alright, i downloaded the source for xbmc, did a search and found the sql that creates what's missing,

create view movieview as select movie.*,files.strFileName as strFileName,path.strPath as strPath,files.playCount as playCount,files.lastPlayed as lastPlayed from movie join files on files.idFile=movie.idFile join path on path.idPath=files.idPath

and

CREATE TABLE sets ( idSet integer primary key, strSet text)

now there are no errors, the recently added movies work but the Movies still dont work.

So, no errors and still not working.

I also tried to do a "truncate" on all tables with "movie" in their names, to make sure that no old stuff was still in the db, but, it didnt do the trick.

So, what do i do now?

[tommyhome]

ok, obviously it didn't log the error until i tried to access the movies.

Code:

21:10:07 T:6316 M:587730944  NOTICE: {'limit': '4'}
21:10:28 T:3228 M:582520832   ERROR: SQL: The table does not exist
                                            Query: SELECT sets.idSet,sets.strSet,COUNT(1) AS c,count(files.playCount) FROM sets JOIN setlinkmovie ON sets.idSet=setlinkmovie.idSet JOIN movie ON setlinkmovie.idMovie=movie.idMovie JOIN files ON files.idFile=movie.idFile  GROUP BY sets.idSet HAVING c>1
21:10:28 T:3228 M:582492160   ERROR: CVideoDatabase::GetSetsNav failed
21:10:28 T:3228 M:582492160   ERROR: SQL: The table does not exist
                                            Query: select * from movieview WHERE movieview.idMovie NOT IN (SELECT idMovie FROM setlinkmovie s1 JOIN(SELECT idSet, COUNT(1) AS c FROM setlinkmovie GROUP BY idSet HAVING c>1) s2 ON s2.idSet=s1.idSet)
21:10:28 T:3228 M:582483968   ERROR: CVideoDatabase::GetMoviesByWhere failed
21:10:28 T:3228 M:582475776   ERROR: XFILE::CDirectory::GetDirectory - Error getting videodb://1/2/
21:10:28 T:3228 M:582475776   ERROR: CGUIMediaWindow::GetDirectory(videodb://1/2/) failed

is what it says.

[tommyhome]

ok, NOW, it is working.

obviously xbmc does not log everything.

I took the sqlcode from the error and found out that the table called setlinkmovie was missing (i figured it was the view but it was more than that), after creating thatone to, it works as it should.

the only question that remains is why it happened.

obviously xbmc needs some sort of sql check that checks for all tables and views that it needs, at least at runtime so the error can be trapped fast and so that xbmc might create them by it self.

[microgluf]

Hi tommyhome,

I am missing the setlinkmovie view, would you have the view definition handy, or a db structure dump even better ??

Cheers

[microgluf]

No worries, found it ! Just had to look at the Sqlite db, it's all there !!