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:
What actually happened
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.
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 (Unknown database 'MyMusic32')
# cut for brevity
21:15:36 T:7664 ERROR: Unable to open database: MyMusic (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
21:15:36 T:7664 ERROR: Unable to open database: MyVideos75 (Unknown database 'MyVideos75')
# cut for brevity
21:15:37 T:7664 ERROR: Unable to open database: MyVideos (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!
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]
I've got some ideas to try out now:
- Upgrade everything to the latest nightly (development) build for XBMC; it looks like they fixed the issue already.
- Install a nightly XBMC build long enough to create the schema, then downgrade XBMC back to Frodo and see if it works
- 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.