Login at Kodi Home

SandmanCL · 2011-06-23, 02:25

I'm intentionally not asking this question in the AE sticky since it's not directly AE related Smile

I've installed the AE build from 22-Jun-2011 on one of my ATVs and I'm getting Navigation sounds and the media I've tested so far works. However, I am no longer able to connect to the MySQL database running on my Linux box.

So, I decided to export the master DB from my Linux build, purge the database, install the most recent Ubuntu build and then re-import the database. On the Linux side, things work well, with the following xbmc packages installed:

Code:
ii  xbmc 2:11.0pvr+odk40~git20110530.615e173-0ubuntu1~ppa+odk40~maverick 

ii  xbmc-bin 2:11.0-pvr+odk40~git20110530.615e173-0ubuntu1~ppa+odk40~maverick

rc  xbmc-data 2:10.5-dharma+pvr~odk48+maverick

On the ATV side, however, things aren't so great.
The relevant errors in xbmc.log on the ATV:

Code:
48:37 T:162140160 M:122904576  NOTICE: Attempting to update the database xbmc_video from version 51 to 53

48:37 T:162140160 M:122904576   DEBUG: Mysql Start transaction

48:37 T:162140160 M:122904576   DEBUG: Mysql execute: ALTER TABLE movie ADD c23 text

48:37 T:162140160 M:122904576   ERROR: SQL: Undefined MySQL error: Code (1142)

                                            Query: ALTER TABLE movie ADD c23 text

48:37 T:162140160 M:122904576   ERROR: Error attempting to update the database version!

48:37 T:162140160 M:122904576   DEBUG: Mysql rollback transaction

48:37 T:162140160 M:122904576   ERROR: Can't update the database xbmc_video from version 51 to 53

48:37 T:162140160 M:122904576   ERROR: Unable to open database xbmc_video

The ALTER TABLE attempt fails because I don't allow generous enough grants by the xbmc user on my ATVs. This is to avoid that I accidentally delete the DB. So I tried manually executing that statement as an admin user in mysql:

Code:
mysql> ALTER TABLE movie ADD c23 text;

Query OK, 773 rows affected (0.10 sec)

.... but that doesn't work.

What do I need to do to my mysql master to make sure that I can continue testing the AE build for stability while still have database connectivity ?

jmarshall · 2011-06-23, 03:27

You need to regenerate the views as well.

SandmanCL · 2011-06-23, 06:56

Thanks, eventually got it working by piecing together other posts here Smile

Doing this, however, (not surprisingly) broke the database for XBMC on my Linux box. Making that one bold to make sure people realize that this is not the ideal solution since it means I am unable to update my library. For now, this is better than the alternative, since I wouldn't hear the end of it if I broke XBMC in the living room :-)

Summary of the database changes I did:

1) add column "c23" to 4 tables

Code:
ALTER TABLE movie ADD c23 text;

ALTER TABLE episode ADD c23 text;

ALTER TABLE tvshow ADD c23 text;

ALTER TABLE musicvideo ADD c23 text;

2) Drop 4 views

Code:
drop view movieview;

drop view episodeview;

drop view tvshowview;

drop view musicvideoview;

3) Recreate these 4 views

- movieview

Code:
CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` 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` from ((`movie` join `files` on((`files`.`idFile` = `movie`.`idFile`))) join `path` on((`path`.`idPath` = `files`.`idPath`)));

- episodeview

Code:
CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` 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`,`files`.`strFilename` AS `strFileName`,`path`.`strPath` AS `strPath`,`files`.`playCount` AS `playCount`,`files`.`lastPlayed` AS `lastPlayed`,`tvshow`.`c00` AS `strTitle`,`tvshow`.`c14` AS `strStudio`,`tvshow`.`idShow` AS `idShow`,`tvshow`.`c05` AS `premiered`,`tvshow`.`c13` AS `mpaa` from ((((`episode` join `files` on((`files`.`idFile` = `episode`.`idFile`))) join `tvshowlinkepisode` on((`episode`.`idEpisode` = `tvshowlinkepisode`.`idEpisode`))) join `tvshow` on((`tvshow`.`idShow` = `tvshowlinkepisode`.`idShow`))) join `path` on((`files`.`idPath` = `path`.`idPath`)));

- tvshowview

Code:
CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` 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`,`files`.`strFilename` AS `strFileName`,`path`.`strPath` AS `strPath`,`files`.`playCount` AS `playCount`,`files`.`lastPlayed` AS `lastPlayed`,`tvshow`.`c00` AS `strTitle`,`tvshow`.`c14` AS `strStudio`,`tvshow`.`idShow` AS `idShow`,`tvshow`.`c05` AS `premiered`,`tvshow`.`c13` AS `mpaa` from ((((`episode` join `files` on((`files`.`idFile` = `episode`.`idFile`))) join `tvshowlinkepisode` on((`episode`.`idEpisode` = `tvshowlinkepisode`.`idEpisode`))) join `tvshow` on((`tvshow`.`idShow` = `tvshowlinkepisode`.`idShow`))) join `path` on((`files`.`idPath` = `path`.`idPath`)));

- musicvideoview
Hmm, guess I didn't recreate this one. Will update the post later Smile

I'm not really using music videos though

jmarshall · 2011-06-23, 07:30

If you broke the database on your Linux box, then your Linux box is not up to date.

SandmanCL · 2011-06-23, 07:52

I guess not. The version I have is referred to in my original post. The SVN PPA referred to in the Linux Section Sticky Thread hasn't been updated since October of last year Smile

I've still got some catching up to do around here.

mikeybhoy · 2011-12-06, 01:38

SandmanCL Wrote:- episodeview

Code:
CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` 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`,`files`.`strFilename` AS `strFileName`,`path`.`strPath` AS `strPath`,`files`.`playCount` AS `playCount`,`files`.`lastPlayed` AS `lastPlayed`,`tvshow`.`c00` AS `strTitle`,`tvshow`.`c14` AS `strStudio`,`tvshow`.`idShow` AS `idShow`,`tvshow`.`c05` AS `premiered`,`tvshow`.`c13` AS `mpaa` from ((((`episode` join `files` on((`files`.`idFile` = `episode`.`idFile`))) join `tvshowlinkepisode` on((`episode`.`idEpisode` = `tvshowlinkepisode`.`idEpisode`))) join `tvshow` on((`tvshow`.`idShow` = `tvshowlinkepisode`.`idShow`))) join `path` on((`files`.`idPath` = `path`.`idPath`)));

- tvshowview

Code:
CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` 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`,`files`.`strFilename` AS `strFileName`,`path`.`strPath` AS `strPath`,`files`.`playCount` AS `playCount`,`files`.`lastPlayed` AS `lastPlayed`,`tvshow`.`c00` AS `strTitle`,`tvshow`.`c14` AS `strStudio`,`tvshow`.`idShow` AS `idShow`,`tvshow`.`c05` AS `premiered`,`tvshow`.`c13` AS `mpaa` from ((((`episode` join `files` on((`files`.`idFile` = `episode`.`idFile`))) join `tvshowlinkepisode` on((`episode`.`idEpisode` = `tvshowlinkepisode`.`idEpisode`))) join `tvshow` on((`tvshow`.`idShow` = `tvshowlinkepisode`.`idShow`))) join `path` on((`files`.`idPath` = `path`.`idPath`)));

Hi, Im following your guide, but the code for episodeview and tvshowview are the same. Can you repost tvshowview?