[mythtv-users] Database upgrade 0.21 -> 0.22: issue to avoid
Michael T. Dean
mtdean at thirdcontact.com
Mon Nov 16 20:52:50 UTC 2009
<fixed top posting and broken quoting>
On 11/16/2009 03:25 PM, James Warden wrote:
> On Mon, 11/16/09, David Liana wrote:
>> On Mon, Nov 16, 2009 at 1:58 PM, Michael T. Dean wrote:
>>> On 11/16/2009 04:01 AM, James Warden wrote:
>>>> I went through an upgrade
>>>> from mythtv 0.21 to 0.22 yesterday and found an issue with
>>>> the following (probably already mentioned somewhere else but
>>>> this is how I fixed it):
>>>> the dtv_multiplex and channelscan_dtv_multiplex tables
>>>> cannot be upgraded from db schema 1230 to 1231 and makes the
>>>> whole upgrade fail.
>>>> This is because the table already contains a field (mysql
>>>> column) that the upgrade is trying to add again when
>>>> upgrading to 1231.
>>> You restored a 0.21-fixes DB backup /on top of/ a
>>> 0.22-fixes DB schema. That means that MySQL converted any
>>> data to fit (and may have broken your data). That means
>>> you should /immediately/ go back to your 0.21-fixes database
>>> backup and restore it properly and then let MythTV upgrade
>>> it properly.
>>> (Specifically, read the section, "If you're not
>>> doing an "in-place" upgrade (so you need to
>>> restore your DB backup), it is critical that you DROP any
>>> existing database /before/ restoring your database
>>> backup." )
>>> and http://svn.mythtv.org/trac/ticket/6923#comment:4
>>> If you don't do this, all of your data is suspect, and
>>> bad things may happen.
>> I'm with Mike here. You want to load the old DB
>> before you upgrade. I tried doing it in your order -
>> overlaying a 0.21 DB over 0.22. Seemed to work okay until
>> I kept getting query errors in my logs.
> As I (implicitely) said, I did not give any specific technical details. I actually did not roll an old DB over a new one. I slightly modified the DB at schema v1230 so that the upgrade would make it to v1231, and then it went all fine. I looked into the code
> (mythtv/libs/libmythtv/dbcheck.cpp I believe or something similar).
No it didn't. Your DB got broken.
> I did start from schema v1214. It just fell short of working at v1230, thus my looking into the mysql statement that made it fail in the first place.
That failure can /never/ occur in a proper DB schema. It can /only/
occur if someone or some patch modified and /broke/ your DB schema
or--what a bunch of users have been doing wrong--by restoring a
0.21-fixes DB backup into a 0.22 DB schema.
How does this happen? Well, as mentioned in a bunch of my posts about
this specific problem, if you a) copy binary MySQL data files
(/var/lib/mythtv/mythconverg or whatever) on top of the MySQL data files
from your new system install (which contains a pre-packaged 0.22
database schema), or b) restore a SQL backup file on top of the
pre-packaged 0.22 database schema.
In either of those 2 cases, you'll get the failure you see. In any
upgrade path from a /valid/ 0.21-fixes DB schema /or/ from a new
database schema created by mythtv-setup or mythbackend, the failure you
mention is impossible.
> I had not experienced any oddities once the upgrade was complete.
> however, I did roll back to 0.21 (see my other discussion thread about PVR500) because any updates after svn at july2008 has a weird effect on ivtv (at least in my backend server).
When you do upgrade in the future, please make sure you do the restore
properly. (And, I hope here you also did a proper restore of the
0.21-fixes DB--otherwise you left garbage from 0.22 (tables that exist
in 0.22 but not in 0.21) in place that will break future restores.)
More information about the mythtv-users