[mythtv-users] Mythtv 0.22 upgrade problems

[Michael T. Dean]

On 11/10/2009 07:56 PM, Nick Morrott wrote:
> 2009/11/8 Michael T. Dean:
>   
>> On 11/07/2009 08:58 PM, mythtv a blandford wrote:
>>     
>>> I have been running mythtv for several years and am currently at 0.21.
>>>
>>> I am trying to upgrade to 0.22 using the atrpms packages.
>>>
>>> After the upgrade, running mythvsetup appears to go fine and I see the
>>> schema updates occur.
>>>
>>> However, when I start the front end, it tries to update the schema for
>>> mythvideo but fails.  On the screen it says:
>>>
>>> Warning: MythTV wants to upgrade your database for the Video schema, from
>>> 1016 to 1028.
>>>
>>> Messages from the frontend log say:
>>> Query was:
>>> ALTER DATABASE mythconverg DEFAULT CHARACTER SET latin1;
>>> Driver error was [2/1146]:
>>> QMYSQL3: Unable to execute statement
>>> Database error was:
>>> Table 'mythconverg.videocountry' doesn't exist
>>>
>>> 2009-11-07 18:55:09.110 Couldn't upgrade video database schema, exiting.
>>> 2009-11-07 18:55:09.110 Unable to initialize plugin 'mythvideo'.
>>>
>>> Looking at the database, that table does not appear to exist.
>>>
>>> Any suggestions on how to proceed?
>>>       
>> Backup your database (
>> http://www.mythtv.org/wiki/Database_Backup_and_Restore ).
>>
>> Then:
>>
>> cat << "EOF" | mysql -umythtv -p mythconverg
>> DROP TABLE IF EXISTS videocast;
>> DROP TABLE IF EXISTS videocategory;
>> DROP TABLE IF EXISTS videocountry;
>> DROP TABLE IF EXISTS videogenre;
>> DROP TABLE IF EXISTS videometadata;
>> DROP TABLE IF EXISTS videometadatacast;
>> DROP TABLE IF EXISTS videometadatacountry;
>> DROP TABLE IF EXISTS videometadatagenre;
>> DROP TABLE IF EXISTS videotypes;
>> DROP TABLE IF EXISTS filemarkup;
>> DROP TABLE IF EXISTS dvdinput;
>> DROP TABLE IF EXISTS dvdtranscode;
>> DELETE FROM settings WHERE value
>>   IN ('mythvideo.DBSchemaVer',
>>       'VideoDBSchemaVer',
>>       'DVDDBSchemaVer');
>> EOF
>>
>> Then start mythfrontend and it should re-create the video/DVD schema for
>> you.
>>
>> I'm guessing at one point in the past you had only MythDVD or MythVideo
>> installed back when they were separate plugins.
>>     
>
> Is there a reason why a check for missing tables (and their creation
> if found to be missing) was not the first schema update once MythVideo
> and MythDVD were integrated. I'm sure Michael will not be the last
> user to have used one or the other plugin exclusively in the past.
>
> Is it possible to retrospectively alter the MythVideo schema for 1016
> to "CREATE TABLE IF NOT EXISTS..." first, so that we check for any
> missing tables and create them instead of having the upgrade fail?

Mine was a guess as to where we might have a bug--that's all I'm 
motivated to provide because:

a) the MythVideo/MythDVD integration occurred prior to MythTV 0.21 
(therefore, nearly /all/ users have already been through it, so if there 
is a bug, their schemas are already broken),
b) if there was a problem with it, not only would we have to fix the 
problem, but because everyone's already used the buggy code to upgrade, 
we'd have to identify all possible failure modes, the extent of the 
problems they caused, and write code to programmatically identify which 
failure occurred and to fix all those possible failures,
c) since we've already released MythTV 0.22, we can't add new DB schema 
changes even if we fix the upgrade that's failing to ignore failures
d) because of the above, there's not a good place to add new fixes
e) resetting MythVideo's data (and MythDVD really has none) is not a bad 
idea with the upgrade to MythTV 0.22--due to all the new support for 
advanced and image metadata, nearly everyone will be redoing all their 
video metadata, anyway, and clearing out all the old MythVideo data is 
pretty much the only way to switch to using MythVideo Storage Groups
f) if someone does the reset the MythVideo schema, they can be 
absolutely certain that their MythVideo schema is correct--regardless of 
what's happened in the past.

If someone else tracks down the bug(s) and finds a good solution that 
fixes all possible problems caused by it, I'd be happy to take a look at 
it.  I just don't feel it's worth the time (mine or, for that matter, 
anyone else's) to do so when copy/pasting the above mysql command into a 
terminal fixes the issue in 1/2 second.  :)

The only downside to this approach is that users have to find the 
instructions for handling this issue.  :(

Mike