XBMC (Frodo) and MySQL (5.6) woes

This week I decided it's time to move my XBMC library away from the SD card and into a shareable database. As tends to be the case with most offerings, the options were MySQL or nothing.

I figured I could live with this if it Just Worked. It didn't.

I'm in the process of trying to understand, diagnose and kludge my way round this.

What should have happened

Here's the plan of action I had after researching a little on the internet:

1. install MySQL on my NAS
2. configure XBMC to use MySQL
3. (re)import library data into XBMC

What actually happened

Installing MySQL on my NAS

The details of this might make a full entry of it's own. Suffice to say there were more hoops to jump through to install and configure MySQL. Not all of the hoops were round. It took longer than expected but it's up and running now.

Configure XBMC to use MySQL

This was one of the easiest steps. Following instructions found online I just had to paste some XML into a new file and change a value to the IP address of the MySQL server.

Get data into MySQL

Before trying anything with the live data I wanted to test with 'throwaway data'. I installed XBMC (Frodo) on my desktop PC, configured it to use MySQL and added a folder with media to XBMC.

Checking xbmc.log showed some unexpected things:

21:15:35 T:7664   ERROR: Unable to open database: MyMusic32 [1049](Unknown database 'MyMusic32')

# cut for brevity

21:15:36 T:7664   ERROR: Unable to open database: MyMusic [1049](Unknown database 'MyMusic')

21:15:36 T:7664   ERROR: SQL: Undefined MySQL error: Code (1071)

                                            Query: CREATE INDEX idxAlbum ON album(strAlbum)

21:15:36 T:7664   ERROR: CMusicDatabase::CreateTables unable to create tables:0

and:

21:15:36 T:7664   ERROR: Unable to open database: MyVideos75 [1049](Unknown database 'MyVideos75')
# cut for brevity
21:15:37 T:7664   ERROR: Unable to open database: MyVideos [1049](Unknown database 'MyVideos')
21:15:45 T:7664   ERROR: SQL: Undefined MySQL error: Code (1055)
                                            Query: 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;
21:15:45 T:7664   ERROR: CVideoDatabase::CreateTables unable to create tables:0

Initially I was fooled and misled by the dozens of lines informing me it couldn't find various databases. This just appears to be a really clunky way of working out if a database exists (newest to oldest), and presumably when it doesn't call the routine to create one.

After this has run I do have two new databases that match the highest numbered unfound database:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MyMusic32          |
| MyVideos75         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

I also got two (real?) errors in the log:

• ERROR: SQL: Undefined MySQL error: Code (1071)
• ERROR: SQL: Undefined MySQL error: Code (1055)

Having to search the web to find out what the description of "undefined" errors, presented with error codes is an amazing implementation idea. Thank you!

• 1071 seems to be an issue with index keys
• 1055 appears to be a problem with the GROUP BY

More searches revealed that this is in part due to behaviour changes in index creation; it's no longer failing silently when the key length is too large. This aborts the database creation process leaving me with a number of tables that look plausible, but errors in the log where the system is trying to query tables and views that don't exist.

The suggested solution?

Don't use MySQL 5.6 is is the issue...this is a known bug.
5.5.3 works fine.

Amazing! Thanks! [source]

Alternative Solutions?

I've got some ideas to try out now:

1. Upgrade everything to the latest nightly (development) build for XBMC; it looks like they fixed the issue already.
2. Install a nightly XBMC build long enough to create the schema, then downgrade XBMC back to Frodo and see if it works
3. Find a working schema on the internet and install it manually; or find SQL to fill in the missing pieces.

I think I'll start with #2 and see how much pain and suffering it causes.