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

Kevin Kuphal kuphal at dls.net
Wed Oct 26 14:42:00 EDT 2005


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


More information about the mythtv-users mailing list