Using XBMC (Frodo) with MySQL (5.6)

As I mentioned recently I've had some trouble with the SQL that XBMC runs to create the schema for a MySQL store.

I've tried installing the latest (alpha) of 'Gotham', which I thought might have resolved this problem. It hasn't. I'm still seeing issues with the views being created. I tried some statements in I found in a post but they aren't working either - problems with the GROUP BY clause.

Some thinking and internet searches has led me to try fixing the view creation manually. I might as well work through it myself from scratch instead of hacking together a solution from other posts/

Install Stable XBMC


After making sure I'd removed any traces of XBMC and the previously created MySQL databases I installed XBMC 'Frodo'.

Once installed I navigated to "userdata\Database", dropped the sqlite3.exe file I'd downloaded earlier in the same location, opened up a terminal/console, navigated to the right place and inspected a copy of "MyVideos75.db":

The formatted SQL output was as follows:

episodeview:
movieview:
tvshowview:

I repeated the process for the views in a copy of  "MyMusic32.db"

The formatted SQL for the music database views are:

albumview:
artistview:
songview:

With this data I then configured XBMC to connect to my MySQL instance, restarted XBMC. This created the schema in the MySQL database.

I  inspected XBMC to confirm there were errors on creation:

As I'd seen a post about the indexes in the music database I fixed these first:


I was able to directly paste the CREATE statements for 'artistview' and 'songview' into MySQL. 'albumview' returned an error:

    ERROR 1055 (42000): 'MyMusic32.album.strAlbum' isn't in GROUP BY

I started to look into some of the SQL issues, decided it was getting too late and this was a problem best mulled over and tackled another day.