[mythtv] Ticket #3133: Schema Upgrade fails for 1180 -> 1181

[Michael T. Dean]

On 02/24/2007 01:07 PM, Jonathan wrote:
> On 24/02/07, MythTV <mythtv at cvs.mythtv.org> wrote:
>> #3133: Schema Upgrade fails for 1180 -> 1181
>>
>>
>> After SVN upgrade the myth setup (and also backend) fail to start as 
>> below. I have seen something similar on SQL bug reports, but have no 
>> idea how to work around or fix. I have tried restoring db, all checks 
>> out ok.
>>
>> Linux mythtv.storm.to 2.6.19-1.2288.fc5 #1 Sat Feb 10 14:52:17 EST 
>> 2007 i686 i686 i386 GNU/Linux
>>
>> mysql  Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using 
>> readline 5.0
>>
>> Output of mythtv-setup will be attached.
> Including mythtv-setup log.
If you run the following 3 SQL statements while mythbackend and 
mythfrontend are not running, does the DB upgrade complete successfully 
upon restarting mythbackend?  Make sure you create a DB backup ( 
http://mythtv.org/docs/mythtv-HOWTO-23.html#ss23.5 ), first, as I will 
not take responsibility for things going wrong while you're attempting 
to do the cleanup below.  (Actually, you should already have a backup 
that you made just before the upgrade, anyway, but I'm reminding you, 
just in case.)

CREATE TEMPORARY TABLE temprecordedcleanup
  SELECT DISTINCT chanid, starttime
    FROM recordedmarkup;

CREATE TEMPORARY TABLE temprecordedcleanup2
   SELECT DISTINCT p.chanid, p.starttime
     FROM temprecordedcleanup p
LEFT JOIN recorded r
       ON p.chanid = r.chanid AND p.starttime = r.progstart
    WHERE r.chanid IS NULL;

   DELETE p
     FROM recordedmarkup p
LEFT JOIN temprecordedcleanup2 r
       ON p.chanid = r.chanid AND p.starttime = r.starttime
    WHERE r.chanid IS NOT NULL;

Note:  The multi-table delete above is not supported in MySQL 3.23 and 
the SQL syntax would have to be different for MySQL 4.0, so it cannot be 
used in a patch.  Instead, if cleanup is required to ensure the safety 
of the DB upgrade, it will have to be done as in #3113 ( 
http://svn.mythtv.org/trac/ticket/3113 )--which, incidentally, adds the 
above cleanup, and the cleanup of recordedseek (which may be the 
reporter's next problem), to daily housekeeping.

Mike