[mythtv-users] Fixing minor errors in Myth database

[Michael T. Dean]

On 04/19/2013 08:22 PM, Fred Hamilton wrote:
> I'm running Myth 0.26 on Ubuntu 12.04.  Over the years the migrations 
> appear to have taken their toll on the database.  So even though 
> everything seems to be working well, mythweb gags when I select 
> "Recorded Programs", saying:
>
> Fatal Error  !!NoTrans: SQL Error: Unknown column 
> 'recordedmarkup.data' in 'field list' [#1054]!!

This isn't a "minor error" in the database--it would be classed as a 
corrupt schema (meaning the structure of your database is wrong).  A 
corrupt schema is generally caused by invalid upgrade procedures (i.e. 
by stopping the upgrade in progress or by an upgrade's failing and then 
not restoring the pre-upgrade backup before attempting the upgrade 
again).  Since there's no transactional integrity in the upgrade 
procedure, it must complete without error from start to finish or else 
the state of your database schema and data is suspect (and, quite 
possibly corrupt).

To fix corruption, the only supported solution is to throw away all 
re-creatable data (settings/configuration/plugin data/...) and keep only 
the essential non-re-creatable data (recording information and 
history).  You do so with a partial restore (and, FWIW, this is the 
/only/ reason for a partial restore).  See

http://www.mythtv.org/wiki/Database_Backup_and_Restore#Partial_restore_of_a_backup

for more information.  Note, though, that since your recordedmarkup 
table is corrupt, there's a possibility that even a partial restore will 
fail.  If so, please let me know and I'll help you get the partial 
restore working.  Note, also, that even if the partial restore succeeds, 
you should re-create all markup data--i.e. re-run mythcommflag --rebuild 
and mythcommflag on every single recording you have after you 
reconfigure all of MythTV.  See:

http://www.mythtv.org/wiki/Repairing_the_Seektable

>
> with the backtrace shown below.  I'm looking for some advice on how to 
> clean that up.  The MythWeb "Repair Tables" function doesn't seem to 
> do it.

Right.  Repair tables is more precisely "repair MySQL binary data file 
format" and has nothing to do with schema structure nor data 
integrity--and can actually result in loss of data (one row or all rows 
in the table), depending on the type of data file corruption.

Mike