Login at Kodi Home

PainToad · 2010-10-16, 02:06

Hi guys,
Dharma Beta 3.

Music imports into the DB, but nothing shows up in Library. Turn off mysql and it imports/displays fine...

Let me know if you need a log.

Cheers.

PainToad · 2010-10-16, 02:17

Plus recentlyadded.py still isn't compatible with mysql. It's calling

select * from movieview where playCount isnull order by idMovie desc limit 8

rather than

'is null'

PainToad · 2010-10-16, 02:29

10:21:37 T:5652 M:1930215424 ERROR: SQL: The table does not exist
Query: select count(idSong) as NumSongs from songview where idAlbum in (select idAlbum from album where strAlbum='')
10:21:37 T:5652 M:1930215424 ERROR: CMusicDatabase::GetSongsCount(where idAlbum in (select idAlbum from album where strAlbum='')) failed
10:21:37 T:5652 M:1927450624 ERROR: SQL: The table does not exist
Query: select count(idSong) as NumSongs from songview where idAlbum in (select idAlbum from album where strAlbum='')
10:21:37 T:5652 M:1927450624 ERROR: CMusicDatabase::GetSongsCount(where idAlbum in (select idAlbum from album where strAlbum='')) failed
10:21:37 T:5652 M:1927450624 DEBUG: CGUIMediaWindow::GetDirectory ()
10:21:37 T:5652 M:1927450624 DEBUG: ParentPath = []
10:21:37 T:5652 M:1927450624 ERROR: SQL: The table does not exist
Query: select count(idSong) as NumSongs from songview where idAlbum in (select idAlbum from album where strAlbum='')
10:21:37 T:5652 M:1927450624 ERROR: CMusicDatabase::GetSongsCount(where idAlbum in (select idAlbum from album where strAlbum='')) failed
10:21:38 T:5652 M:1927434240 ERROR: SQL: The table does not exist
Query: select count(idSong) as NumSongs from songview
10:21:38 T:5652 M:1927434240 ERROR: CMusicDatabase::GetSongsCount() failed
10:21:38 T:5652 M:1927434240 ERROR: SQL: The table does not exist
Query: select count(idSong) as NumSongs from songview where idAlbum in (select idAlbum from album where strAlbum='')
10:21:38 T:5652 M:1927434240 ERROR: CMusicDatabase::GetSongsCount(where idAlbum in (select idAlbum from album where strAlbum='')) failed

So the views missing in mysql....help?

PainToad · 2010-10-17, 08:04

Really, no reply?

Jonny_137 · 2010-10-19, 14:16

Hi,
I had a similar problem.

For me it worked to rename the Music table:
<musicdatabase>
<type>mysql</type>
<host>192.168.178.10</host>
<port>3306</port>
<user>root</user>
<pass>xbmc</pass>
[b]<name>xbmc_music2</name>[/b]
</musicdatabase>

craigd · 2010-10-19, 14:23

your db hasn't created properlly. drop the existing music schema and restart xbmc and it should automatically create it with all required tables.

for it to work you'll need to make sure you have a user with required permission to both the sql server and the schema.

not sure if it's been sorted but there was an issue with very slow response in music db the existing mysql thread for a fix if that happens

***edit***
jonny's method would also work as effectively does the same but will leave the old db behind

Jonny_137 · 2010-10-19, 14:45

Well after scanning i still can't acces my library so I replaced mysql with xampp (which includes mysql) and now everything works fine.

darwin · 2011-01-27, 02:47

PainToad Wrote:[The View Doesn't Exist]

Code:
create view songview as select song.idSong as idSong, song.strExtraArtists as strExtraArtists, song.strExtraGenres as strExtraGenres, 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, song.idArtist as idArtist, strArtist, song.idGenre as idGenre, strGenre, strThumb, iKaraNumber, iKaraDelay, strKaraEncoding from song join album on song.idAlbum=album.idAlbum join path on song.idPath=path.idPath join  artist on song.idArtist=artist.idArtist join genre on song.idGenre=genre.idGenre join thumb on song.idThumb=thumb.idThumb left outer join karaokedata on song.idSong=karaokedata.idSong;

Of course if your DB created without this view, there might be other stuff that required SUPER that also wasn't created. I just kludged it by creating the table as above, YMMV.

=darwin

Darkside138 · 2011-04-29, 23:46

darwin Wrote:
Code:
create view songview as select song.idSong as idSong, song.strExtraArtists as strExtraArtists, song.strExtraGenres as strExtraGenres, 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, song.idArtist as idArtist, strArtist, song.idGenre as idGenre, strGenre, strThumb, iKaraNumber, iKaraDelay, strKaraEncoding from song join album on song.idAlbum=album.idAlbum join path on song.idPath=path.idPath join artist on song.idArtist=artist.idArtist join genre on song.idGenre=genre.idGenre join thumb on song.idThumb=thumb.idThumb left outer join karaokedata on song.idSong=karaokedata.idSong;

Of course if your DB created without this view, there might be other stuff that required SUPER that also wasn't created. I just kludged it by creating the table as above, YMMV.

=darwin

I was not able to select library mode after switching to a mysql DB. I would scan in my music files and then get an error in the log as stated above. Simply running the select statement from darwin in my existing music DB fixed my problem after a restart of XBMC.

Just wanted to post to let people know that this fix works and say thanks!

davor · (This post was last modified: 2012-03-30, 16:54 by davor.)

(2011-01-27, 02:47)darwin Wrote:
PainToad Wrote:[The View Doesn't Exist]

Code:
create view songview as select song.idSong as idSong, song.strExtraArtists as strExtraArtists, song.strExtraGenres as strExtraGenres, 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, song.idArtist as idArtist, strArtist, song.idGenre as idGenre, strGenre, strThumb, iKaraNumber, iKaraDelay, strKaraEncoding from song join album on song.idAlbum=album.idAlbum join path on song.idPath=path.idPath join artist on song.idArtist=artist.idArtist join genre on song.idGenre=genre.idGenre join thumb on song.idThumb=thumb.idThumb left outer join karaokedata on song.idSong=karaokedata.idSong;

Of course if your DB created without this view, there might be other stuff that required SUPER that also wasn't created. I just kludged it by creating the table as above, YMMV.

=darwin

I am using v11 Eden, and have exacly this problem. In the video library, the views are made correctly, but not in the music library.

When I look tot the MySQL general log, I see these queries being executed while making the xbmc_audio18 database:

Code:
        59590 Connect    xbmc@????????????? on 

        59590 Query    SET NAMES utf8

        59590 Query    show databases like 'xbmc_audio18'

        59590 Query    CREATE DATABASE `xbmc_audio18`

        59590 Init DB    xbmc_audio18

        59590 Query    show databases like 'xbmc_audio18'

        59590 Query    show tables

        59590 Query    CREATE TABLE version (idVersion integer, iCompressCount integer)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    INSERT INTO version (idVersion,iCompressCount) values(18,0)

        59590 Query    CREATE TABLE artist ( idArtist integer primary key auto_increment , strArtist varchar(256))

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE album ( idAlbum integer primary key auto_increment , strAlbum varchar(256), idArtist integer, strExtraArtists text, idGenre integer, strExtraGenres text, iYear integer, idThumb integer)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE genre ( idGenre integer primary key auto_increment , strGenre varchar(256))

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE path ( idPath integer primary key auto_increment , strPath varchar(512), strHash text)

CHARACTER SET utf8 COLLATE utf8_general_ci

120330 14:13:32    59590 Query    CREATE TABLE song ( idSong integer primary key auto_increment , idAlbum integer, idPath integer, idArtist integer, strExtraArtists text, idGenre integer, strExtraGenres text, strTitle varchar(512), iTrack integer, iDuration integer, iYear integer, dwFileNameCRC text, strFileName text, strMusicBrainzTrackID text, strMusicBrainzArtistID text, strMusicBrainzAlbumID text, strMusicBrainzAlbumArtistID text, strMusicBrainzTRMID text, iTimesPlayed integer, iStartOffset integer, iEndOffset integer, idThumb integer, lastplayed varchar(20) default NULL, rating char default '0', comment text)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE albuminfo ( idAlbumInfo integer primary key auto_increment , idAlbum integer, iYear integer, idGenre integer, strExtraGenres text, strMoods text, strStyles text, strThemes text, strReview text, strImage text, strLabel text, strType text, iRating integer)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE albuminfosong ( idAlbumInfoSong integer primary key auto_increment , idAlbumInfo integer, iTrack integer, strTitle text, iDuration integer)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE thumb (idThumb integer primary key auto_increment , strThumb varchar(256))

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE artistinfo ( idArtistInfo integer primary key auto_increment , idArtist integer, strBorn text, strFormed text, strGenres text, strMoods text, strStyles text, strInstruments text, strBiography text, strDied text, strDisbanded text, strYearsActive text, strImage text, strFanart text)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE content (strPath text, strScraperPath text, strContent text, strSettings text)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE discography (idArtist integer, strAlbum text, strYear text)

CHARACTER SET utf8 COLLATE utf8_general_ci

120330 14:13:33    59590 Query    CREATE TABLE exartistsong ( idSong integer, iPosition integer, idArtist integer)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE exgenresong ( idSong integer, iPosition integer, idGenre integer)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE exartistalbum ( idAlbum integer, iPosition integer, idArtist integer)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE exgenrealbum ( idAlbum integer, iPosition integer, idGenre integer)

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE TABLE karaokedata ( iKaraNumber integer, idSong integer, iKaraDelay integer, strKaraEncoding text, strKaralyrics text, strKaraLyrFileCRC text )

CHARACTER SET utf8 COLLATE utf8_general_ci

        59590 Query    CREATE INDEX idxExtraArtistSong ON exartistsong(idSong)

        59590 Query    CREATE INDEX idxExtraArtistSong2 ON exartistsong(idArtist)

        59590 Query    CREATE INDEX idxExtraGenreSong ON exgenresong(idSong)

120330 14:13:34    59590 Query    CREATE INDEX idxExtraGenreSong2 ON exgenresong(idGenre)

        59590 Query    CREATE INDEX idxExtraArtistAlbum ON exartistalbum(idAlbum)

        59590 Query    CREATE INDEX idxExtraArtistAlbum2 ON exartistalbum(idArtist)

        59590 Query    CREATE INDEX idxExtraGenreAlbum ON exgenrealbum(idAlbum)

        59590 Query    CREATE INDEX idxExtraGenreAlbum2 ON exgenrealbum(idGenre)

        59590 Query    CREATE INDEX idxAlbum ON album(strAlbum)

        59590 Query    CREATE INDEX idxAlbum2 ON album(idArtist)

120330 14:13:35    59590 Query    CREATE INDEX idxGenre ON genre(strGenre)

        59590 Query    CREATE INDEX idxArtist ON artist(strArtist)

        59590 Query    CREATE INDEX idxPath ON path(strPath)

        59590 Query    CREATE INDEX idxSong ON song(strTitle)

        59590 Query    CREATE INDEX idxSong1 ON song(iTimesPlayed)

        59590 Query    CREATE INDEX idxSong2 ON song(lastplayed)

        59590 Query    CREATE INDEX idxSong3 ON song(idAlbum)

120330 14:13:36    59590 Query    CREATE INDEX idxSong4 ON song(idArtist)

        59590 Query    CREATE INDEX idxSong5 ON song(idGenre)

        59590 Query    CREATE INDEX idxSong6 ON song(idPath)

        59590 Query    CREATE INDEX idxThumb ON thumb(strThumb)

        59590 Query    CREATE INDEX idxArtistInfo on artistinfo(idArtist)

        59590 Query    CREATE INDEX idxAlbumInfo on albuminfo(idAlbum)

        59590 Query    CREATE INDEX idxKaraNumber on karaokedata(iKaraNumber)

120330 14:13:37    59590 Query    CREATE INDEX idxKarSong on karaokedata(idSong)

        59590 Query    CREATE TRIGGER tgrAlbumInfo AFTER delete ON albuminfo FOR EACH ROW delete from albuminfosong where albuminfosong.idAlbumInfo=old.idAlbumInfo

        59590 Query    rollback

        59590 Query    SELECT idVersion FROM version

        59590 Query    select count(idSong) as NumSongs from songview

        59590 Quit

As you can see, the last query executed during the connection is "select count(idSong) as NumSongs from songview", but VIEW songview was never made... Same holds for albumview and who knows what else.

My privileges are this:

Code:
GRANT ALL PRIVILEGES ON `xbmc_%`.* TO 'xbmc'@'192.168.1.0/255.255.255.0';

Why aren't these views made for music library?

---> SOLUTION: see next post!

davor · (This post was last modified: 2012-03-30, 16:55 by davor.)

Ok, There is a quick way to solve this without manualy making the views, as darwin suggested.

Log in to your mysql server through console.

Exectute:

Code:
SET GLOBAL log_bin_trust_function_creators=1;

Run xbmc. It should now be able to make all the required views and triggers.

Revert to the console and restore the default:

Code:
SET GLOBAL log_bin_trust_function_creators=0;

For more info, see here: http://dev.mysql.com/doc/refman/5.5/en/s...gging.html (Error 1419)

devkid · 2013-05-22, 10:50

That did not solve it for me. Downgrading Mysql 5.6 to 5.5.3 as stated here did the trick:
http://forum.xbmc.org/showthread.php?tid...pid1409113

nikagl · 2013-10-19, 00:04

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 Big Grin

Regards,

Nika.

gnarf1975 · (This post was last modified: 2015-05-03, 21:30 by gnarf1975.)

Hello i´m new with Kodi.

- How can i filter and play the Music Library in Genre, Artist etc. . Can´t find any settings for that. (Win 8.1)

Have since used before MCE.