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

[Michael T. Dean]

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).

(The code in Myth actually deletes the DBSchemaVer setting and then 
inserts a new one, so if there's some reason that's required, it would 
be more correct than the UPDATE above.)

Mike