[mythtv-users] Can I check my database structure is correct (0.24fixes)?

[Michael T. Dean]

On 04/11/2012 02:26 PM, jacek burghardt wrote:
> On 4/11/12, nospam312 wrote:
>> Does anyone know if it is possible to check if my MythTV database
>> structure is correct?
>>
>> i.e. all tables, fields, types and indexes are as they should be?
>>
>> An export from phpMyAdmin of the structure only of mythconverg from a
>> 100% correct 0.24fixes database would be great if there is no easier
>> way?
> http://www.mythtv.org/mailman/listinfo/mythtv-users
>
> There is optiminization script included with mythv  that will optimize
> and check structure of database.

Actually, optimize_mythdb.pl doesn't check the structure of the 
schema--it only checks that database data files are usable by MySQL.

We don't have any way to check structure.  The theory is that people 
won't do things to mess with the database and its structure.  (Though, 
as might be obvious from recent threads, it's possible that in reality 
something else could interfere with database upgrades and cause problems.)

If you really want to check your structure, you can back up your 
database, then drop the database and start up mythtv-setup to create a 
new database, then export its structure, then restore your database and 
export its structure and compare the 2.  Because of how often the 
database structure changes, and because checking structure doesn't 
verify data, and because of how easily one could misinterpret the 
results of a check (i.e. you find that something is wrong with the 
schema and correct it, thinking it "fixed" your database, when that 
brokenness resulted in a bunch of broken data, which is still broken or 
something), and because comparing structure is extremely difficult 
(without code written specifically to verify a given structure) across 
different systems/different MySQL versions/..., I don't plan to make any 
such tool available.  (In other words, I seriously believe that such a 
tool would cause more problems than it fixes.)

If you think you may have an invalid/corrupt/broken database schema, the 
"supported" approach for fixing it is to do a full backup and then a 
partial restore ( 
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Partial_restore_of_a_backup 
) of some of that data into a completely new database schema, and then 
completely reconfigure your MythTV system from scratch (all 
backends--including capture cards, video sources, input connections, 
storage directories, ...--and frontends).

Mike