[alexb38]

Hello,

I have been having a few problems with the latest version of video database for xbmc when updating to the latest git yesterday, but I managed to solve everything, so I think this thread might help a few people.

My video database was previously in version 60 and when updating to version 64 I had a lot of errors in the logs, some things went wrong but not everything.

For what I've seen, the changes for the video database in version 64 are as follows :
- new art and seasons tables (in my case they were created fine)
- "dateAdded (text)" column added in files and path tables
- "idShow (int(11))" column added in episode table (+ 2 keys)
- the table tvshowlinkepisode is not needed anymore
- the views changed due to these modifications

First, we need to add the missing tables, columns and keys if the update didn't work :

Code:

CREATE TABLE `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=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `seasons` (
  `idSeason` int(11) NOT NULL AUTO_INCREMENT,
  `idShow` int(11) DEFAULT NULL,
  `season` int(11) DEFAULT NULL,
  PRIMARY KEY (`idSeason`),
  KEY `ix_seasons` (`idShow`,`season`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ALTER TABLE `episode` ADD `idShow` int(11) DEFAULT NULL;
ALTER TABLE `episode` ADD KEY `ix_episode_show1` (`idEpisode`,`idShow`);
ALTER TABLE `episode` ADD KEY `ix_episode_show2` (`idShow`,`idEpisode`);

ALTER TABLE `path` ADD `dateAdded` text;
ALTER TABLE `files` ADD `dateAdded` text;

Then we need to fill "idShow" with the correct data in the episode table :

Code:

UPDATE `episode`, `tvshowlinkepisode` SET `episode`.`idShow` = `tvshowlinkepisode`.`idShow` WHERE `episode`.`idEpisode` = `tvshowlinkepisode`.`idEpisode`;

If needed, use these queries to update/insert missing episode thumbs :

Code:

UPDATE `art` a, `episode` e SET a.`url` = REPLACE(REPLACE(e.`c06`,"<thumb>",""),"</thumb>","") WHERE a.`media_id` = e.`idEpisode` AND a.`url` = '';

INSERT INTO `art`(`media_id`,`media_type`,`type`,`url`) (SELECT idEpisode, "episode", "thumb", REPLACE(REPLACE(`c06`,"<thumb>",""),"</thumb>","") FROM `episode` WHERE c06 LIKE "<thumb%" AND idEpisode NOT IN (SELECT `media_id` FROM `art` WHERE `media_type`="episode" AND `type`="thumb"));

To be continued below... (too much characters in my post)

[alexb38]

And if your views are not up to date or doesn't exists, you can use these queries to recreate them :

Code:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `episodeview` AS select `episode`.`idEpisode` AS `idEpisode`,`episode`.`idFile` AS `idFile`,`episode`.`c00` AS `c00`,`episode`.`c01` AS `c01`,`episode`.`c02` AS `c02`,`episode`.`c03` AS `c03`,`episode`.`c04` AS `c04`,`episode`.`c05` AS `c05`,`episode`.`c06` AS `c06`,`episode`.`c07` AS `c07`,`episode`.`c08` AS `c08`,`episode`.`c09` AS `c09`,`episode`.`c10` AS `c10`,`episode`.`c11` AS `c11`,`episode`.`c12` AS `c12`,`episode`.`c13` AS `c13`,`episode`.`c14` AS `c14`,`episode`.`c15` AS `c15`,`episode`.`c16` AS `c16`,`episode`.`c17` AS `c17`,`episode`.`c18` AS `c18`,`episode`.`c19` AS `c19`,`episode`.`c20` AS `c20`,`episode`.`c21` AS `c21`,`episode`.`c22` AS `c22`,`episode`.`c23` AS `c23`,`episode`.`idShow` AS `idShow`,`files`.`strFilename` AS `strFileName`,`path`.`strPath` AS `strPath`,`files`.`playCount` AS `playCount`,`files`.`lastPlayed` AS `lastPlayed`,`files`.`dateAdded` AS `dateAdded`,`tvshow`.`c00` AS `strTitle`,`tvshow`.`c14` AS `strStudio`,`tvshow`.`c05` AS `premiered`,`tvshow`.`c13` AS `mpaa`,`tvshow`.`c16` AS `strShowPath` from (((`episode` join `files` on((`files`.`idFile` = `episode`.`idFile`))) join `tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) join `path` on((`files`.`idPath` = `path`.`idPath`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `movieview` AS select `movie`.`idMovie` AS `idMovie`,`movie`.`idFile` AS `idFile`,`movie`.`c00` AS `c00`,`movie`.`c01` AS `c01`,`movie`.`c02` AS `c02`,`movie`.`c03` AS `c03`,`movie`.`c04` AS `c04`,`movie`.`c05` AS `c05`,`movie`.`c06` AS `c06`,`movie`.`c07` AS `c07`,`movie`.`c08` AS `c08`,`movie`.`c09` AS `c09`,`movie`.`c10` AS `c10`,`movie`.`c11` AS `c11`,`movie`.`c12` AS `c12`,`movie`.`c13` AS `c13`,`movie`.`c14` AS `c14`,`movie`.`c15` AS `c15`,`movie`.`c16` AS `c16`,`movie`.`c17` AS `c17`,`movie`.`c18` AS `c18`,`movie`.`c19` AS `c19`,`movie`.`c20` AS `c20`,`movie`.`c21` AS `c21`,`movie`.`c22` AS `c22`,`movie`.`c23` AS `c23`,`files`.`strFilename` AS `strFileName`,`path`.`strPath` AS `strPath`,`files`.`playCount` AS `playCount`,`files`.`lastPlayed` AS `lastPlayed`,`files`.`dateAdded` AS `dateAdded` from ((`movie` join `files` on((`files`.`idFile` = `movie`.`idFile`))) join `path` on((`path`.`idPath` = `files`.`idPath`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `tvshowview` AS select `tvshow`.`idShow` AS `idShow`,`tvshow`.`c00` AS `c00`,`tvshow`.`c01` AS `c01`,`tvshow`.`c02` AS `c02`,`tvshow`.`c03` AS `c03`,`tvshow`.`c04` AS `c04`,`tvshow`.`c05` AS `c05`,`tvshow`.`c06` AS `c06`,`tvshow`.`c07` AS `c07`,`tvshow`.`c08` AS `c08`,`tvshow`.`c09` AS `c09`,`tvshow`.`c10` AS `c10`,`tvshow`.`c11` AS `c11`,`tvshow`.`c12` AS `c12`,`tvshow`.`c13` AS `c13`,`tvshow`.`c14` AS `c14`,`tvshow`.`c15` AS `c15`,`tvshow`.`c16` AS `c16`,`tvshow`.`c17` AS `c17`,`tvshow`.`c18` AS `c18`,`tvshow`.`c19` AS `c19`,`tvshow`.`c20` AS `c20`,`tvshow`.`c21` AS `c21`,`tvshow`.`c22` AS `c22`,`tvshow`.`c23` AS `c23`,`path`.`strPath` AS `strPath`,`path`.`dateAdded` AS `dateAdded`,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`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `musicvideoview` AS select `musicvideo`.`idMVideo` AS `idMVideo`,`musicvideo`.`idFile` AS `idFile`,`musicvideo`.`c00` AS `c00`,`musicvideo`.`c01` AS `c01`,`musicvideo`.`c02` AS `c02`,`musicvideo`.`c03` AS `c03`,`musicvideo`.`c04` AS `c04`,`musicvideo`.`c05` AS `c05`,`musicvideo`.`c06` AS `c06`,`musicvideo`.`c07` AS `c07`,`musicvideo`.`c08` AS `c08`,`musicvideo`.`c09` AS `c09`,`musicvideo`.`c10` AS `c10`,`musicvideo`.`c11` AS `c11`,`musicvideo`.`c12` AS `c12`,`musicvideo`.`c13` AS `c13`,`musicvideo`.`c14` AS `c14`,`musicvideo`.`c15` AS `c15`,`musicvideo`.`c16` AS `c16`,`musicvideo`.`c17` AS `c17`,`musicvideo`.`c18` AS `c18`,`musicvideo`.`c19` AS `c19`,`musicvideo`.`c20` AS `c20`,`musicvideo`.`c21` AS `c21`,`musicvideo`.`c22` AS `c22`,`musicvideo`.`c23` AS `c23`,`files`.`strFilename` AS `strFileName`,`path`.`strPath` AS `strPath`,`files`.`playCount` AS `playCount`,`files`.`lastPlayed` AS `lastPlayed`,`files`.`dateAdded` AS `dateAdded` from ((`musicvideo` join `files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `path` on((`path`.`idPath` = `files`.`idPath`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `albumview` AS select `album`.`idAlbum` AS `idAlbum`,`album`.`strAlbum` AS `strAlbum`,`album`.`strExtraArtists` AS `strExtraArtists`,`album`.`idArtist` AS `idArtist`,`album`.`strExtraGenres` AS `strExtraGenres`,`album`.`idGenre` AS `idGenre`,`artist`.`strArtist` AS `strArtist`,`genre`.`strGenre` AS `strGenre`,`album`.`iYear` AS `iYear`,`thumb`.`strThumb` AS `strThumb`,`albuminfo`.`idAlbumInfo` AS `idAlbumInfo`,`albuminfo`.`strMoods` AS `strMoods`,`albuminfo`.`strStyles` AS `strStyles`,`albuminfo`.`strThemes` AS `strThemes`,`albuminfo`.`strReview` AS `strReview`,`albuminfo`.`strLabel` AS `strLabel`,`albuminfo`.`strType` AS `strType`,`albuminfo`.`strImage` AS `strImage`,`albuminfo`.`iRating` AS `iRating` from ((((`album` left join `artist` on((`album`.`idArtist` = `artist`.`idArtist`))) left join `genre` on((`album`.`idGenre` = `genre`.`idGenre`))) left join `thumb` on((`album`.`idThumb` = `thumb`.`idThumb`))) left join `albuminfo` on ((`album`.`idAlbum` = `albuminfo`.`idAlbum`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `songview` AS select `song`.`idSong` AS `idSong`,`song`.`strExtraArtists` AS `strExtraArtists`,`song`.`strExtraGenres` AS `strExtraGenres`,`song`.`strTitle` AS `strTitle`,`song`.`iTrack` AS `iTrack`,`song`.`iDuration` AS `iDuration`,`song`.`iYear` AS `iYear`,`song`.`dwFileNameCRC` AS `dwFileNameCRC`,`song`.`strFileName` AS `strFileName`,`song`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,`song`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,`song`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,`song`.`strMusicBrainzAlbumArtistID` AS `strMusicBrainzAlbumArtistID`,`song`.`strMusicBrainzTRMID` AS `strMusicBrainzTRMID`,`song`.`iTimesPlayed` AS `iTimesPlayed`,`song`.`iStartOffset` AS `iStartOffset`,`song`.`iEndOffset` AS `iEndOffset`,`song`.`lastplayed` AS `lastplayed`,`song`.`rating` AS `rating`,`song`.`comment` AS `comment`,`song`.`idAlbum` AS `idAlbum`,`album`.`strAlbum` AS `strAlbum`,`path`.`strPath` AS `strPath`,`song`.`idArtist` AS `idArtist`,`artist`.`strArtist` AS `strArtist`,`song`.`idGenre` AS `idGenre`,`genre`.`strGenre` AS `strGenre`,`thumb`.`strThumb` AS `strThumb`,`karaokedata`.`iKaraNumber` AS `iKaraNumber`,`karaokedata`.`iKaraDelay` AS `iKaraDelay`,`karaokedata`.`strKaraEncoding` AS `strKaraEncoding` from ((((((`song` join `album` on((`song`.`idAlbum` = `album`.`idAlbum`))) join `path` on((`song`.`idPath` = `path`.`idPath`))) join `artist` on((`song`.`idArtist` = `artist`.`idArtist`))) left join `genre` on((`song`.`idGenre` = `genre`.`idGenre`))) left join `thumb` on((`song`.`idThumb` = `thumb`.`idThumb`))) left join `karaokedata` on((`song`.`idSong` = `karaokedata`.`idSong`)));

If all worked fine (especially the idShow import), you can drop the tvshowlinkepisode table :

Code:

DROP TABLE `tvshowlinkepisode`;

[jmarshall]

All of which should have perfectly updated by XBMC.

If it doesn't work we need to know why. We need people that can reproduce it so that we can check whether there's any issues and if so fix them.

Drop back down to v60 and see if you can reproduce. Note _exactly_ what happens.

Cheers,
Jonathan

[alexb38]

Of course, I understand, I'll try to reproduce the problem today as I've made a backup of the v60 database and I'll post a debug log.

What's strange is that even with root privileges, XBMC doesn't create the views, even if the database is empty.

The tables are correctly created if the database is empty. But if there is already a database that needs to be updated, then only the new tables are created.

I've been using XBMC git for a long time and that's not the first time this happens on my linux setup.

I installed a fresh XBMC nightly and a mysql server on a windows computer and all worked fine, that's how I was able to know what needed to be changed on my broken database.

[alexb38]

Well, after some testing, it was because the mysql user that was used for xbmc didn't have the right to create triggers. When the first trigger creation failed, xbmc stopped executing queries and put the database in a half updated state. I tried with the root user and the update worked perfectly.

Here is the error shown by phpmyadmin when creating triggers with the xbmc user :

Code:

#1419 - You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

And the xbmc log if needed :
https://gist.github.com/2784225


Here are the new triggers if anyone needs it :

Code:

CREATE TRIGGER delete_movie AFTER DELETE ON movie FOR EACH ROW DELETE FROM art WHERE media_id=old.idMovie AND media_type='movie';
CREATE TRIGGER delete_tvshow AFTER DELETE ON tvshow FOR EACH ROW DELETE FROM art WHERE media_id=old.idShow AND media_type='tvshow';
CREATE TRIGGER delete_musicvideo AFTER DELETE ON musicvideo FOR EACH ROW DELETE FROM art WHERE media_id=old.idMVideo AND media_type='musicvideo';
CREATE TRIGGER delete_episode AFTER DELETE ON episode FOR EACH ROW DELETE FROM art WHERE media_id=old.idEpisode AND media_type='episode';
CREATE TRIGGER delete_season AFTER DELETE ON seasons FOR EACH ROW DELETE FROM art WHERE media_id=old.idSeason AND media_type='season';
CREATE TRIGGER delete_set AFTER DELETE ON sets FOR EACH ROW DELETE FROM art WHERE media_id=old.idSet AND media_type='set';
CREATE TRIGGER delete_person AFTER DELETE ON actors FOR EACH ROW DELETE FROM art WHERE media_id=old.idActor AND media_type IN ('actor','artist','writer','director');

To be complete, if the database is half updated, the "seasons" table should also be filled with the correct data.

[jmarshall]

If you don't have the correct permissions there's not much XBMC can do about it. However, your logs highlight several issues, the main one being:

If a database update fails, the new database is left in place rather than being removed. This causes the "create a new database" to fail.

If the database update fails, the newly copied database needs to be removed so that a brand new database can be created in it's place - at least then you don't have constant failures.

I believe I've commented before on how much of a great addition mysql has been...

Cheers,
Jonathan

[alexb38]

That's right, I've seen a few times (but not always) that a database is not deleted correctly after a drop database query. There must be a problem with my mysql setup.

I understand that a mysql database can cause a few problems like this sometimes, but it's not useless though. For example I'm using the same database on 2 computers, one linux and one windows and now it works fine. That way I don't need to scrape things two times each.

[jmarshall]

"now it works fine". Just wait for the next update :p

[alexb38]

Well I added the missing permissions to the mysql user, so it should be ok now, I hope...

And if something like this happens again I should be able to solve it faster now.

Anyway I don't update XBMC very often, so it's not a big problem