[papymaj5]

Hello,

I am getting this error while xbmc is creating a mysql music db. v12 RC3

14:30:00 T:6724 INFO: create karaokedata table
14:30:00 T:6724 DEBUG: Mysql execute: CREATE TABLE karaokedata ( iKaraNumber integer, idSong integer, iKaraDelay integer, strKaraEncoding text, strKaralyrics text, strKaraLyrFileCRC text )
CHARACTER SET utf8 COLLATE utf8_general_ci
14:30:00 T:6724 INFO: create album index
14:30:00 T:6724 DEBUG: Mysql execute: CREATE INDEX idxAlbum ON album(strAlbum)
14:30:00 T:6724 ERROR: SQL: Undefined MySQL error: Code (1071)
Query: CREATE INDEX idxAlbum ON album(strAlbum)
14:30:00 T:6724 ERROR: CMusicDatabase::CreateTables unable to create tables:0
14:30:00 T:6724 DEBUG: Mysql rollback transaction

I have seen solutions by making the db latin1, however this crashes xbmc. Any solutions to this?

[papymaj5]

Unable to create music db in mysql

[papymaj5]

Any Ideas, or should I post this somewhere else?

[Ned Scott]

Can you post the full debug log via a paste site like http://xbmclogs.com ?

[papymaj5]

Can you post the full debug log via a paste site like http://xbmclogs.com ?

Here is my full debug log... Thanks

http://xbmclogs.com/show.php?id=26569

[jmarshall]

Delete the database fully and try again. This code has been the same since Eden.

[papymaj5]

Delete the database fully and try again. This code has been the same since Eden.

I have deleted the music mysql database multiple times, it gives me the same error. It does work correctly with a local db. XBMC creates a correct mysql video db also.

I have tried letting XBMC create the db name (mymusic32) and i also have used the name convention (xbmc_music32), neither work.

The error's definition is:
SQL Error (1071): Specified key was too long; max key length is 767 bytes.

I have heard of using latin1. Also, where can I find the table names and columns so I could maybe create my own instead if there is no solution?

[papymaj5]

Any solutions?

[papymaj5]

Delete the database fully and try again. This code has been the same since Eden.

Any other solutions?

[papymaj5]

Delete the database fully and try again. This code has been the same since Eden.

I have been unable to find a solution to this problem.

[Reverent001]

me neither.

Deleted the muisc32 and all other xbmc_music dbs, but everytime i start xbmc on my pc, it crashes!

Before u ask, here is my logfile: http://xbmclogs.com/show.php?id=31585

Any ideas

EDIT:

I had to delete ALL databases including the xbmc_movie dbs to get it work! Now everything is fine, all three clients are update, and scraping is still in work till.....dono!

[noenflux]

Fixed the issue

To work with a default install of mysql 5.6.11 there needs to be an update to the index creation as it exceeds the InnoDB key size with default collation:

CREATE INDEX idxAlbum ON album(strAlbum);
CREATE INDEX idxGenre ON genre(strGenre);
CREATE INDEX idxArtist ON artist(strArtist);
CREATE INDEX idxPath ON path(strPath);
CREATE INDEX idxSong ON song(strTitle);

Just needs to be tweaked to

CREATE INDEX idxAlbum ON album(strAlbum(255));
CREATE INDEX idxGenre ON genre(strGenre(255));
CREATE INDEX idxArtist ON artist(strArtist(255));
CREATE INDEX idxPath ON path(strPath(255));
CREATE INDEX idxSong ON song(strTitle(255));

Or, alternatively, each of these strings should be cut to varchar 255's, which they should have been in the first place.

[jhhoffma]

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

5.5.3 works fine.

[nikagl]

Hmmm... seems this is still not solved. For whomever wants to use Mysql 5.6 recreating the views manually seems to work:

Code:

USE mymusic32;


CREATE VIEW artistview AS
SELECT `mymusic32`.`artist`.`idArtist` AS `idArtist`,
       `mymusic32`.`artist`.`strArtist` AS `strArtist`,
       `mymusic32`.`artistinfo`.`strBorn` AS `strBorn`,
       `mymusic32`.`artistinfo`.`strFormed` AS `strFormed`,
       `mymusic32`.`artistinfo`.`strGenres` AS `strGenres`,
       `mymusic32`.`artistinfo`.`strMoods` AS `strMoods`,
       `mymusic32`.`artistinfo`.`strStyles` AS `strStyles`,
       `mymusic32`.`artistinfo`.`strInstruments` AS `strInstruments`,
       `mymusic32`.`artistinfo`.`strBiography` AS `strBiography`,
       `mymusic32`.`artistinfo`.`strDied` AS `strDied`,
       `mymusic32`.`artistinfo`.`strDisbanded` AS `strDisbanded`,
       `mymusic32`.`artistinfo`.`strYearsActive` AS `strYearsActive`,
       `mymusic32`.`artistinfo`.`strImage` AS `strImage`,
       `mymusic32`.`artistinfo`.`strFanart` AS `strFanart`
FROM (`mymusic32`.`artist`
      LEFT JOIN `mymusic32`.`artistinfo` on((`mymusic32`.`artist`.`idArtist` = `mymusic32`.`artistinfo`.`idArtist`)))

Code:

USE mymusic32;


CREATE VIEW songview AS
SELECT `mymusic32`.`song`.`idSong` AS `idSong`,
       `mymusic32`.`song`.`strArtists` AS `strArtists`,
       `mymusic32`.`song`.`strGenres` AS `strGenres`,
       `mymusic32`.`song`.`strTitle` AS `strTitle`,
       `mymusic32`.`song`.`iTrack` AS `iTrack`,
       `mymusic32`.`song`.`iDuration` AS `iDuration`,
       `mymusic32`.`song`.`iYear` AS `iYear`,
       `mymusic32`.`song`.`dwFileNameCRC` AS `dwFileNameCRC`,
       `mymusic32`.`song`.`strFileName` AS `strFileName`,
       `mymusic32`.`song`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
       `mymusic32`.`song`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
       `mymusic32`.`song`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,
       `mymusic32`.`song`.`strMusicBrainzAlbumArtistID` AS `strMusicBrainzAlbumArtistID`,
       `mymusic32`.`song`.`strMusicBrainzTRMID` AS `strMusicBrainzTRMID`,
       `mymusic32`.`song`.`iTimesPlayed` AS `iTimesPlayed`,
       `mymusic32`.`song`.`iStartOffset` AS `iStartOffset`,
       `mymusic32`.`song`.`iEndOffset` AS `iEndOffset`,
       `mymusic32`.`song`.`lastplayed` AS `lastplayed`,
       `mymusic32`.`song`.`rating` AS `rating`,
       `mymusic32`.`song`.`comment` AS `comment`,
       `mymusic32`.`song`.`idAlbum` AS `idAlbum`,
       `mymusic32`.`album`.`strAlbum` AS `strAlbum`,
       `mymusic32`.`path`.`strPath` AS `strPath`,
       `mymusic32`.`karaokedata`.`iKaraNumber` AS `iKaraNumber`,
       `mymusic32`.`karaokedata`.`iKaraDelay` AS `iKaraDelay`,
       `mymusic32`.`karaokedata`.`strKaraEncoding` AS `strKaraEncoding`,
       `mymusic32`.`album`.`bCompilation` AS `bCompilation`,
       `mymusic32`.`album`.`strArtists` AS `strAlbumArtists`
FROM (((`mymusic32`.`song`
        JOIN `mymusic32`.`album` on((`mymusic32`.`song`.`idAlbum` = `mymusic32`.`album`.`idAlbum`)))
       JOIN `mymusic32`.`path` on((`mymusic32`.`song`.`idPath` = `mymusic32`.`path`.`idPath`)))
      LEFT JOIN `mymusic32`.`karaokedata` on((`mymusic32`.`song`.`idSong` = `mymusic32`.`karaokedata`.`idSong`)))

Code:

USE mymusic32;


CREATE VIEW albumview AS
SELECT `mymusic32`.`album`.`idAlbum` AS `idAlbum`,
       `mymusic32`.`album`.`strAlbum` AS `strAlbum`,
       `mymusic32`.`album`.`strArtists` AS `strArtists`,
       `mymusic32`.`album`.`strGenres` AS `strGenres`,
       `mymusic32`.`album`.`iYear` AS `iYear`,
       `mymusic32`.`albuminfo`.`idAlbumInfo` AS `idAlbumInfo`,
       `mymusic32`.`albuminfo`.`strMoods` AS `strMoods`,
       `mymusic32`.`albuminfo`.`strStyles` AS `strStyles`,
       `mymusic32`.`albuminfo`.`strThemes` AS `strThemes`,
       `mymusic32`.`albuminfo`.`strReview` AS `strReview`,
       `mymusic32`.`albuminfo`.`strLabel` AS `strLabel`,
       `mymusic32`.`albuminfo`.`strType` AS `strType`,
       `mymusic32`.`albuminfo`.`strImage` AS `strImage`,
       `mymusic32`.`albuminfo`.`iRating` AS `iRating`,
       `mymusic32`.`album`.`bCompilation` AS `bCompilation`,
       min(`mymusic32`.`song`.`iTimesPlayed`) AS `iTimesPlayed`
FROM ((`mymusic32`.`album`
       LEFT JOIN `mymusic32`.`albuminfo` on((`mymusic32`.`album`.`idAlbum` = `mymusic32`.`albuminfo`.`idAlbum`)))
      LEFT JOIN `mymusic32`.`song` on((`mymusic32`.`album`.`idAlbum` = `mymusic32`.`song`.`idAlbum`)))
GROUP BY `mymusic32`.`album`.`idAlbum`

Not sure why XBMC wouldn't be able to create the views themselves, but I'll leave that to the masterminds

Regards,

Nika.