[mythtv-users] Database upgrading failed after update to .27

[Michael T. Dean]

On 09/08/2013 09:05 PM, Captain Hook wrote:
>> On Sun, Sep 8, 2013 at 1:11 PM, Jean-Yves Avenard wrote:
>>> On 8 September 2013 14:34, Captain Hook wrote:
>>>
>>>> Sep  8 14:20:57 MythTV mythtv-setup.real: mythtv-setup[4299]: E CoreContext
>>>> dbcheck.cpp:417 (performActualUpdate) DB Error (Performing database
>>>> upgrade): #012Query was: ALTER TABLE program ADD INDEX title_subtitle_start
>>>> (title, subtitle, starttime); #012Error was: Driver error was
>>>> [2/1061]:#012QMYSQL: Unable to execute query#012Database error
>>>> was:#012Duplicate key name 'title_subtitle_start'#012 #012new version: 1316
>>>> Sep  8 14:20:57 MythTV mythtv-setup.real: mythtv-setup[4299]: E CoreContext
>>>> dbcheck.cpp:502 (UpgradeTVDatabaseSchema) Database schema upgrade failed.
>>> looks like you had earlier attempted an upgrade and somehow screwed up
>>> your database schema...
>>> did you run any command touching the mysql database ?
>>>
>>>
> I am quite certain that this was my first attempt at upgrading. I've only
> been using MythTV for about 7 months and was sticking with .26 until I
> started seeing all the praises. There should be nothing else on my backend
> that could be affecting MySQL at all. This is basically a dedicated backend
> and the only thing installed that I can think of that I have installed is
> VMWare Tools.  It is a myth backend exclusively.

He means you started mythbackend and it attempted--and failed--to 
upgrade the database.  Then, you (or some script on your system) 
restarted mythbackend, and it's attempting to upgrade the database--but, 
now, because of the previous attempt, the failure is different (and 
useless at helping us figure out the actual cause).

Because there's no transaction support for our database, any time you 
attempt an upgrade and it fails, the state of the database is 
indeterminate.  Therefore, you should always restore the pre-upgrade 
backup you made (or, failing that, the one that was automatically made 
for you) and then attempt an upgrade, again.  See 
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Replacing_an_existing_database 
for information on how to restore the backup.

Also, it is highly recommended to always use mythtv-setup for database 
upgrades, because it will tell you if there's a failure 
interactively--rather than waiting until after you start mythfrontend 
and MythWeb and any of a number of other applications, which may further 
confuse the state of the database.

Because you seem to have done multiple attempted upgrades, make sure you 
get the first pre-upgrade backup to restore.

I'm guessing your program table definition was corrupt and you were 
missing an index that was supposed to be there.  I'd have to see a log 
from a "first-upgrade-attempt" to know for sure, though.

Mike