[mythtv-users] database version upgrade fail - 25->27

[Michael T. Dean]

On 12/14/2014 07:06 PM, Dave Matthews wrote:
> I did an upgrade from Ubuntu 12.02 32/MythTV .25 bit to Ubuntu 14.04 
> 64 bit/MythTV .27 and now have a database upgrade failure.  I think it 
> actually started at 1299 but there is no message about going from 1299 
> to 1300.  Just that it tried to get to 1301.  The .27 is from the 
> Ubuntu software center.
>
> Text out of mythtv-setup:
>
> Shall I upgrade this database? [yes]  2014-12-14 17:26:29.874638 C  
> Database Backup complete.
> 2014-12-14 17:26:29.875128 C  Backed up database to file: 
> '/var/lib/mythtv/recordings/mythconverg-1300-20141214222611.sql.gz'
> yes
> 2014-12-14 17:50:06.643255 C  Upgrading to MythTV schema version 1301
> 2014-12-14 17:50:06.643802 E  DB Error (Performing database upgrade):
> Query was: ALTER TABLE channel ADD COLUMN iptvid SMALLINT(6) UNSIGNED;
> Error was: Driver error was [2/1060]:
> QMYSQL: Unable to execute query
> Database error was:
> Duplicate column name 'iptvid'
>
> new version: 1301
> 2014-12-14 17:50:06.643840 E  Database schema upgrade failed.
> 2014-12-14 17:50:06.644392 E  Couldn't upgrade database to new schema
> htpc at htpc:/home/mythtv$
>
> I originally did the upgrade to Ubuntu 14.04 32 bit with a MythTV 
> build out of the repositories.  The db did a forward convert with no 
> problems but ALSA was not working.  I noticed the 32 bit (real old 
> install, started around MythTV 20 or less) and decided to do a clean 
> install.  I am using a backup of the 32 bit database.
>
> What would be useful would be:
> 1) What is the db upgrade script so I can run it and watch what is 
> happening?  If I just need to fix a few tables to let it run I am good.
> 2) Alternatively all I really care about is access to the old 
> recordings.  I could let it build an empty .27 database and then 
> insert the data from the backup into the tables that point at the 
> recordings.  Can someone give the list of tables that need to be 
> populated to point at the old recordings?

This almost always means that you ran mythbackend on the master host and 
it started upgrading the database, but before it could complete the 
process, something killed it--like a "just to make sure everything is 
working properly" monitor program or upstart's built-in daemon monitor 
or ... or even an impatient user who thinks "it must be locked up" or 
(and this may well be the problem) mythtv-setup (or some script that 
starts mythtv-setup/mythtv-setup.real, as provided by some distros) if 
it was started while the backend was doing the upgrade and it killed (on 
its own, as some scripts, or after prompting, if canonical mythtv-setup) 
mythbackend for the user.  Then, when you started mythtv-setup, it tried 
to upgrade the partially-upgraded-and-now-completely-broken database and 
failed because of the failed upgrade.

So, the proper thing to do now is:

a) shut down everything related to MythTV (all 
frontends/backends/clients/...)
b) do a complete restore ( 
https://www.mythtv.org/wiki/Database_Backup_and_Restore ) of the last 
database backup to occur before *all* upgrade attempts (meaning before 
the original upgrade attempt that got cancelled, probably started by the 
master backend)
c) start mythtv-setup and let it upgrade the database without interruption

Since your initial database upgrade attempt(s) was interrupted, the 
state of your database (schema and data) is indeterminate, so the only 
proper solution is to go back to a known-good backup 
(pre-upgrade-attempts), and then do a successful upgrade.

This is why I still want to remove all ability for mythbackend (on any 
host) to upgrade the database and require explicit upgrades.  Until I 
get permission, it's up to the users to make sure they (or their 
systems/scripts/...) don't interrupt upgrades (and/or don't ever start 
mythbackend until after the database upgrade has been performed using 
mythtv-setup).

Mike