[mythtv-users] new recordings missing database fields after R5A22 upgrade

[Byron Poland]

On 10/26/05, Kevin Kuphal <kuphal at dls.net> wrote:
> Michael T. Dean wrote:
>
> > Kevin Kuphal wrote:
> >
> >> Sounds like a DB update was missed.  If you do not have the basename
> >> field in your DB, execute this SQL statement against it:
> >>
> >> ALTER TABLE recorded ADD COLUMN basename varchar(128) NOT NULL DEFAULT;
> >>
> >> and then, once it is in your database, run this:
> >>
> >> UPDATE recorded SET basename =
> >>           CONCAT(chanid, '_', DATE_FORMAT(starttime,
> >>                  '%Y%m%d%H%i00'), '_',
> >>                  DATE_FORMAT(endtime, '%Y%m%d%H%i00'), '.nuv');
> >
> >
> > Correct me if I'm wrong, but isn't it dangerous to run updates this
> > way?  In this case, won't it mean that future updates are guaranteed
> > to fail?  Since this approach does not update the DBSchemaVer in the
> > database (settings table), next time the backend gets started, it will
> > try to update from the DBSchemaVer it thinks it's using (which is
> > probably the prior version), and will fail because the update contains
> > DDL.  If the update contained only DML, it wouldn't make any
> > difference (assuming the DML is idempotent), but DDL changes are never
> > idempotent.
> >
> > Specifically, since the basename column was there, but wasn't properly
> > filled, the DBSchemaVer is probably 1094, so next time mythbackend is
> > started, it will try to update to 1095, and will fail on the "ADD
> > COLUMN" because the basename column exists ("ERROR 1060: Duplicate
> > column name 'basename'"), so it will bail out and stop trying to
> > update (i.e. never going to 1099--not even making it to 1096).
> >
> > It seems like the user should shut down mythbackend, then submit:
> >
> > UPDATE settings SET data = '1095' WHERE value = 'DBSchemaVer';
> >
> > then restart the backend and verify--using the logs--that any
> > requested schema version upgrades succeeded (i.e. "Database Schema
> > upgrade complete, unlocking.") or at least that it doesn't say,
> > "Current Schema Version: XXXX," followed by "Newest Schema Version :
> > XXXX," (meaning we have the current schema version for the version of
> > Myth in use, so no upgrade was required).
>
> depends.  I ran into this very problem moving up SVN versions and my
> DBSchemaVer was well beyond what was necessary for that update to
> trigger but it never did so I was running into the same problem.  I
> think there is something broken with that particular update but I can't
> see anything wrong with it from looking at the code.  I think you will
> find that his DBSchemaVer is correct and the statement didn't execute.
> I wouldn't recommend anyone setting their DBSchemaVer manually.
>
> Kevin
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>

Here is the relevant part of my backend log on on startup:

2005-10-26 15:02:15.804 Using runtime prefix = /usr
QSettings::sync: filename is null/empty
2005-10-26 15:02:15.953 New DB connection, total: 1
2005-10-26 15:02:15.991 Setting Lock for Database Schema upgrade. If
you see a long pause here it means the Schema is already locked and is
being upgraded by another Myth process.
QSettings::sync: filename is null/empty
2005-10-26 15:02:16.005 New DB connection, total: 2
2005-10-26 15:02:16.013 Database Schema upgrade complete, unlocking.

no errors... it does do this on every start of the backend though.