RaspBMC not creating view/triggers on MySQL

To offload my RaspBerry Pi with RaspBMC, I move away from the internal SQLite database to my MySQL server (as described on the XBMC Wiki).


After adding new sources and scraping the content... nothing showed up in the Movies overview... while the tables were populated...


In the xbmc.log file I found the following clue:


21:06:59 T:2928669760   ERROR: SQL: The table does not exist
                                      Query: select * from movieview
21:06:59 T:2928669760   ERROR: GetMoviesByWhere failed


 And after investigation, I noticed that there were no views defined in the schema.


To do a quick analysis on the sqllite database, I installed the SQLite Manager plugin for Firefox and found 4 views... and there I found the create statement for these views... so I simply copy-and-paste it into mysql... and it worked...


Unfortunately I was not able to insert the triggers that way... still have to find it out..


According to Bug ticket #13959 on the XBMC-Trac site, this was caused because I was a MySQL version < 5.16.... which was true... it was 5.0.something, so I upgraded my MySQL to version 5.1.67, and guess what... that did not work either...


So something goes "weird", but looking into the source code didn't gave me a clue so far... so up for further investigation...



The code to create the views:

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;