MySQL Frodo and music library.
#16
(2013-04-21, 15:55)D0B0Y Wrote: the schema's are already set to Latin1 though
You need to have the DB and the MySQL server both set to the same collation.
Reply
#17
For some reason it scans in fine once, then later on the DB becomes corrupt I think.. I have 4 Xbmc clients, 1 PC with 12.1 , 1 openelec with 12.1 and 2 Raspbmc's with 12.0. could it be a client overwriting the DB? for now I have only the PC configured to use SQL and will see if the DB stays intact. then will add the openelec machine to test
Reply
#18
(2013-04-22, 15:19)D0B0Y Wrote: For some reason it scans in fine once, then later on the DB becomes corrupt I think.. I have 4 Xbmc clients, 1 PC with 12.1 , 1 openelec with 12.1 and 2 Raspbmc's with 12.0. could it be a client overwriting the DB? for now I have only the PC configured to use SQL and will see if the DB stays intact. then will add the openelec machine to test
It's hard to tell what's happening without a debug log
Reply
#19
Yeah i will try and enable debug I guess. i can find the instructions to turn that on on the wiki i guess
Reply
#20
You can find the instructions here: debug log (wiki)
Reply
#21
I don't know man, we've had a lot of people have issues just with 5.6, but no issues with 5.5.
Reply
#22
(2013-04-22, 19:46)Ned Scott Wrote: I don't know man, we've had a lot of people have issues just with 5.6, but no issues with 5.5.
I'm using MySQL server 5.6.10 with XBMC 13a2 without any problems
Reply
#23
(2013-04-19, 22:06)charrua Wrote: This issue was reported a long time ago, and has nothing to do with the version of the MySQL server.
The problem is related to the collation defined for the MySQL server.
For more details on the cause of the issue, please check this old post.

Can you help me understand whatever I'm missing from the earlier explanation in this thread?

I originally had a goof with MySQL since my server is also an HTPC, but I found that I only needed to add folder locations from their "network paths" rather than their local paths (seemed odd, but fixed the video library)

Now as follows
  1. Using margro's 12.1 pvr build on "server"
  2. running MySQL 5.6
  3. running OpenELEC 3.0
  4. both had advanced settings xml (copied from the setting up MySQL wiki)
  5. videodatabase worked fine and imported all watched status, etc
  6. music library would never build (network paths or local - tried local paths just for kicks)
  7. received "progress" of library scans and they would complete, but library mode would stay blank

Why does the problem not exist for the videodatabase but only the music database?
Reply
#24
(2013-05-02, 20:54)Dark_Slayer Wrote:
(2013-04-19, 22:06)charrua Wrote: This issue was reported a long time ago, and has nothing to do with the version of the MySQL server.
The problem is related to the collation defined for the MySQL server.
For more details on the cause of the issue, please check this old post.
Why does the problem not exist for the videodatabase but only the music database?
The problem used to exist in the video DB too, but they fixed it.
It remains in the music DB due to the creation of an index that is problematic.

If you want you can try to create the music DB with the following sql script, then run XBMC and see if it populates the tables while scanning for music.
Code:
-- --------------------------------------------------------
-- server:         5.6.10-log - MySQL Community Server (GPL)
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE DATABASE IF NOT EXISTS `mymusic32` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mymusic32`;


CREATE TABLE IF NOT EXISTS `album` (
  `idAlbum` int(11) NOT NULL AUTO_INCREMENT,
  `strAlbum` varchar(256) DEFAULT NULL,
  `strArtists` text,
  `strGenres` text,
  `iYear` int(11) DEFAULT NULL,
  `idThumb` int(11) DEFAULT NULL,
  `bCompilation` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`idAlbum`),
  KEY `idxAlbum` (`strAlbum`(255)),
  KEY `idxAlbum_1` (`bCompilation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE IF NOT EXISTS `albuminfo` (
  `idAlbumInfo` int(11) NOT NULL AUTO_INCREMENT,
  `idAlbum` int(11) DEFAULT NULL,
  `iYear` int(11) DEFAULT NULL,
  `strMoods` text,
  `strStyles` text,
  `strThemes` text,
  `strReview` text,
  `strImage` text,
  `strLabel` text,
  `strType` text,
  `iRating` int(11) DEFAULT NULL,
  PRIMARY KEY (`idAlbumInfo`),
  KEY `idxAlbumInfo` (`idAlbum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `albuminfosong` (
  `idAlbumInfoSong` int(11) NOT NULL AUTO_INCREMENT,
  `idAlbumInfo` int(11) DEFAULT NULL,
  `iTrack` int(11) DEFAULT NULL,
  `strTitle` text,
  `iDuration` int(11) DEFAULT NULL,
  PRIMARY KEY (`idAlbumInfoSong`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE `albumview` (
    `idAlbum` INT(11) NOT NULL,
    `strAlbum` VARCHAR(256) NULL COLLATE 'utf8_general_ci',
    `strArtists` TEXT NULL COLLATE 'utf8_general_ci',
    `strGenres` TEXT NULL COLLATE 'utf8_general_ci',
    `iYear` INT(11) NULL,
    `idAlbumInfo` INT(11) NULL,
    `strMoods` TEXT NULL COLLATE 'utf8_general_ci',
    `strStyles` TEXT NULL COLLATE 'utf8_general_ci',
    `strThemes` TEXT NULL COLLATE 'utf8_general_ci',
    `strReview` TEXT NULL COLLATE 'utf8_general_ci',
    `strLabel` TEXT NULL COLLATE 'utf8_general_ci',
    `strType` TEXT NULL COLLATE 'utf8_general_ci',
    `strImage` TEXT NULL COLLATE 'utf8_general_ci',
    `iRating` INT(11) NULL,
    `bCompilation` INT(11) NOT NULL,
    `iTimesPlayed` INT(11) NULL
) ENGINE=MyISAM;


CREATE TABLE IF NOT EXISTS `album_artist` (
  `idArtist` int(11) DEFAULT NULL,
  `idAlbum` int(11) DEFAULT NULL,
  `boolFeatured` int(11) DEFAULT NULL,
  `iOrder` int(11) DEFAULT NULL,
  UNIQUE KEY `idxAlbumArtist_1` (`idAlbum`,`idArtist`),
  UNIQUE KEY `idxAlbumArtist_2` (`idArtist`,`idAlbum`),
  KEY `idxAlbumArtist_3` (`boolFeatured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `album_genre` (
  `idGenre` int(11) DEFAULT NULL,
  `idAlbum` int(11) DEFAULT NULL,
  `iOrder` int(11) DEFAULT NULL,
  UNIQUE KEY `idxAlbumGenre_1` (`idAlbum`,`idGenre`),
  UNIQUE KEY `idxAlbumGenre_2` (`idGenre`,`idAlbum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `art` (
  `art_id` int(11) NOT NULL AUTO_INCREMENT,
  `media_id` int(11) DEFAULT NULL,
  `media_type` text,
  `type` text,
  `url` text,
  PRIMARY KEY (`art_id`),
  KEY `ix_art` (`media_id`,`media_type`(20),`type`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `artist` (
  `idArtist` int(11) NOT NULL AUTO_INCREMENT,
  `strArtist` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`idArtist`),
  KEY `idxArtist` (`strArtist`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `artistinfo` (
  `idArtistInfo` int(11) NOT NULL AUTO_INCREMENT,
  `idArtist` int(11) DEFAULT NULL,
  `strBorn` text,
  `strFormed` text,
  `strGenres` text,
  `strMoods` text,
  `strStyles` text,
  `strInstruments` text,
  `strBiography` text,
  `strDied` text,
  `strDisbanded` text,
  `strYearsActive` text,
  `strImage` text,
  `strFanart` text,
  PRIMARY KEY (`idArtistInfo`),
  KEY `idxArtistInfo` (`idArtist`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE `artistview` (
    `idArtist` INT(11) NOT NULL,
    `strArtist` VARCHAR(256) NULL COLLATE 'utf8_general_ci',
    `strBorn` TEXT NULL COLLATE 'utf8_general_ci',
    `strFormed` TEXT NULL COLLATE 'utf8_general_ci',
    `strGenres` TEXT NULL COLLATE 'utf8_general_ci',
    `strMoods` TEXT NULL COLLATE 'utf8_general_ci',
    `strStyles` TEXT NULL COLLATE 'utf8_general_ci',
    `strInstruments` TEXT NULL COLLATE 'utf8_general_ci',
    `strBiography` TEXT NULL COLLATE 'utf8_general_ci',
    `strDied` TEXT NULL COLLATE 'utf8_general_ci',
    `strDisbanded` TEXT NULL COLLATE 'utf8_general_ci',
    `strYearsActive` TEXT NULL COLLATE 'utf8_general_ci',
    `strImage` TEXT NULL COLLATE 'utf8_general_ci',
    `strFanart` TEXT NULL COLLATE 'utf8_general_ci'
) ENGINE=MyISAM;


CREATE TABLE IF NOT EXISTS `art_missing` (
  `art_id` int(11) NOT NULL AUTO_INCREMENT,
  `media_id` int(11) DEFAULT NULL,
  `media_type` text,
  `type` text,
  `url` text,
  `strArtist` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`art_id`),
  KEY `ix_art` (`media_id`,`media_type`(20),`type`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `content` (
  `strPath` text,
  `strScraperPath` text,
  `strContent` text,
  `strSettings` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `discography` (
  `idArtist` int(11) DEFAULT NULL,
  `strAlbum` text,
  `strYear` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `genre` (
  `idGenre` int(11) NOT NULL AUTO_INCREMENT,
  `strGenre` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`idGenre`),
  KEY `idxGenre` (`strGenre`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `karaokedata` (
  `iKaraNumber` int(11) DEFAULT NULL,
  `idSong` int(11) DEFAULT NULL,
  `iKaraDelay` int(11) DEFAULT NULL,
  `strKaraEncoding` text,
  `strKaralyrics` text,
  `strKaraLyrFileCRC` text,
  KEY `idxKaraNumber` (`iKaraNumber`),
  KEY `idxKarSong` (`idSong`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `path` (
  `idPath` int(11) NOT NULL AUTO_INCREMENT,
  `strPath` varchar(512) DEFAULT NULL,
  `strHash` text,
  PRIMARY KEY (`idPath`),
  KEY `idxPath` (`strPath`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `song` (
  `idSong` int(11) NOT NULL AUTO_INCREMENT,
  `idAlbum` int(11) DEFAULT NULL,
  `idPath` int(11) DEFAULT NULL,
  `strArtists` text,
  `strGenres` text,
  `strTitle` varchar(512) DEFAULT NULL,
  `iTrack` int(11) DEFAULT NULL,
  `iDuration` int(11) DEFAULT NULL,
  `iYear` int(11) DEFAULT NULL,
  `dwFileNameCRC` text,
  `strFileName` text,
  `strMusicBrainzTrackID` text,
  `strMusicBrainzArtistID` text,
  `strMusicBrainzAlbumID` text,
  `strMusicBrainzAlbumArtistID` text,
  `strMusicBrainzTRMID` text,
  `iTimesPlayed` int(11) DEFAULT NULL,
  `iStartOffset` int(11) DEFAULT NULL,
  `iEndOffset` int(11) DEFAULT NULL,
  `idThumb` int(11) DEFAULT NULL,
  `lastplayed` varchar(20) DEFAULT NULL,
  `rating` char(1) DEFAULT '0',
  `comment` text,
  PRIMARY KEY (`idSong`),
  KEY `idxSong` (`strTitle`(255)),
  KEY `idxSong1` (`iTimesPlayed`),
  KEY `idxSong2` (`lastplayed`),
  KEY `idxSong3` (`idAlbum`),
  KEY `idxSong6` (`idPath`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE `songview` (
    `idSong` INT(11) NOT NULL,
    `strArtists` TEXT NULL COLLATE 'utf8_general_ci',
    `strGenres` TEXT NULL COLLATE 'utf8_general_ci',
    `strTitle` VARCHAR(512) NULL COLLATE 'utf8_general_ci',
    `iTrack` INT(11) NULL,
    `iDuration` INT(11) NULL,
    `iYear` INT(11) NULL,
    `dwFileNameCRC` TEXT NULL COLLATE 'utf8_general_ci',
    `strFileName` TEXT NULL COLLATE 'utf8_general_ci',
    `strMusicBrainzTrackID` TEXT NULL COLLATE 'utf8_general_ci',
    `strMusicBrainzArtistID` TEXT NULL COLLATE 'utf8_general_ci',
    `strMusicBrainzAlbumID` TEXT NULL COLLATE 'utf8_general_ci',
    `strMusicBrainzAlbumArtistID` TEXT NULL COLLATE 'utf8_general_ci',
    `strMusicBrainzTRMID` TEXT NULL COLLATE 'utf8_general_ci',
    `iTimesPlayed` INT(11) NULL,
    `iStartOffset` INT(11) NULL,
    `iEndOffset` INT(11) NULL,
    `lastplayed` VARCHAR(20) NULL COLLATE 'utf8_general_ci',
    `rating` CHAR(1) NULL COLLATE 'utf8_general_ci',
    `comment` TEXT NULL COLLATE 'utf8_general_ci',
    `idAlbum` INT(11) NULL,
    `strAlbum` VARCHAR(256) NULL COLLATE 'utf8_general_ci',
    `strPath` VARCHAR(512) NULL COLLATE 'utf8_general_ci',
    `iKaraNumber` INT(11) NULL,
    `iKaraDelay` INT(11) NULL,
    `strKaraEncoding` TEXT NULL COLLATE 'utf8_general_ci',
    `bCompilation` INT(11) NOT NULL,
    `strAlbumArtists` TEXT NULL COLLATE 'utf8_general_ci'
) ENGINE=MyISAM;


CREATE TABLE IF NOT EXISTS `song_artist` (
  `idArtist` int(11) DEFAULT NULL,
  `idSong` int(11) DEFAULT NULL,
  `boolFeatured` int(11) DEFAULT NULL,
  `iOrder` int(11) DEFAULT NULL,
  UNIQUE KEY `idxSongArtist_1` (`idSong`,`idArtist`),
  UNIQUE KEY `idxSongArtist_2` (`idArtist`,`idSong`),
  KEY `idxSongArtist_3` (`boolFeatured`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `song_genre` (
  `idGenre` int(11) DEFAULT NULL,
  `idSong` int(11) DEFAULT NULL,
  `iOrder` int(11) DEFAULT NULL,
  UNIQUE KEY `idxSongGenre_1` (`idSong`,`idGenre`),
  UNIQUE KEY `idxSongGenre_2` (`idGenre`,`idSong`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




CREATE TABLE IF NOT EXISTS `version` (
  `idVersion` int(11) DEFAULT NULL,
  `iCompressCount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `version` VALUES (32,0);


SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `delete_album` AFTER DELETE ON `album` FOR EACH ROW BEGIN
DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album';
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;


SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `delete_genre` BEFORE DELETE ON `genre` FOR EACH ROW BEGIN
DELETE FROM song_genre WHERE idgenre=old.idgenre;
DELETE FROM album_genre WHERE idgenre=old.idgenre;
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;


SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `delete_song` AFTER DELETE ON `song` FOR EACH ROW BEGIN
DELETE FROM art WHERE media_id=old.idSong AND media_type='song';
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;


DROP TABLE IF EXISTS `albumview`;
CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` VIEW `mymusic32`.`albumview` AS SELECT  album.idAlbum AS idAlbum, strAlbum,   album.strArtists AS strArtists,  album.strGenres AS strGenres,   album.iYear AS iYear,  idAlbumInfo, strMoods, strStyles, strThemes,  strReview, strLabel, strType, strImage, iRating,   bCompilation,   MIN(song.iTimesPlayed) AS iTimesPlayed FROM album   LEFT OUTER JOIN albuminfo ON    album.idAlbum=albuminfo.idAlbum  LEFT OUTER JOIN song ON    album.idAlbum=song.idAlbum GROUP BY album.idAlbum ;


DROP TABLE IF EXISTS `artistview`;
CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` VIEW `mymusic32`.`artistview` AS SELECT  artist.idArtist AS idArtist, strArtist,   strBorn, strFormed, strGenres,  strMoods, strStyles, strInstruments,   strBiography, strDied, strDisbanded,   strYearsActive, strImage, strFanart FROM artist   LEFT OUTER JOIN artistinfo ON    artist.idArtist = artistinfo.idArtist ;


DROP TABLE IF EXISTS `songview`;
CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` VIEW `mymusic32`.`songview` AS SELECT   song.idSong AS idSong,   song.strArtists AS strArtists,  song.strGenres AS strGenres,  strTitle, iTrack, iDuration,  song.iYear AS iYear, dwFileNameCRC, strFileName, strMusicBrainzTrackID,  strMusicBrainzArtistID, strMusicBrainzAlbumID, strMusicBrainzAlbumArtistID,  strMusicBrainzTRMID, iTimesPlayed, iStartOffset, iEndOffset, lastplayed,  rating, comment, song.idAlbum AS idAlbum, strAlbum, strPath,  iKaraNumber, iKaraDelay, strKaraEncoding,  album.bCompilation AS bCompilation,  album.strArtists AS strAlbumArtists FROM song  JOIN album ON    song.idAlbum=album.idAlbum  JOIN path ON    song.idPath=path.idPath  LEFT OUTER JOIN karaokedata ON    song.idSong=karaokedata.idSong ;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Reply
#25
had same issue with 12.2 and latest mysql on windows new install.
thanks, sql script worked for me.
Reply
#26
Thank a lot for the script, it's resolve the problem
Reply
#27
I'm having the same problem. I don't know s&*t about SQL. Here is what I did, deleted the existing musicdb, started mysql service. Opened the shell and typed source c:\music.sql;, it ran the script, I then tried to start XBMC and I get an error: "ERROR: Unable to initialize, exiting." If I stop the service, delete the database and open XBMC it fires right up. What am I doing wrong? Using MYSQL 5.61 and Windows 8.1.
Reply
#28
Anyone feel like trying this with a nightly build (wiki) as a test? It's probably still an issue, since I don't think anyone has specifically looked at the MySQL 5.6 issue, but we need to verify that it's still an issue.

I'll set up a test server myself and check it out, but the more feedback we have, the better. What might seem fixed for one person might still be broken for another, etc.
Reply
#29
Hi!
I setup a new XBMC server with MySQL and I had the same issue. I tried create manually the music db using the script above, but I had errors creating the 'artistvieew' table. After changing the db engine in the script from MyISAM to InnoDB, all the tables was created succeffully.
I hope this may help you.
My setup:
XBMC 12.3 Git:20131212-9ed3e58
MySQL 5.6.15
Reply
#30
I've run into the same problem, but I know virtually nothing about running scripts. Could someone please walk me through the process? Thx.

Edit: nm, guys, I found the command and ran the script. The music db was created.

@max3mo: did you change all instances of MyISAM to InnoDB? I ran the script both ways and got errors each time. Ended up using the original one, but so far the db looks fine (?).
Reply

Logout Mark Read Team Forum Stats Members Help
MySQL Frodo and music library.0