[mythtv-users] Full or Partial Database restore?

Michael T. Dean mtdean at thirdcontact.com
Tue Sep 14 18:05:36 UTC 2010


  On 09/14/2010 01:41 PM, John Haywood wrote:
> On 15/09/2010, at 3:18 AM, Michael T. Dean wrote:
>> On 09/14/2010 09:04 AM, John Haywood wrote:
>>> After much instability going from Mythbuntu 9.10 to 10.04, even 
>>> trying a database backup, clean install and restore the inevitable 
>>> finally happened:
>>> 0.23.1 fixes, PPA repo
>>>
>>> I was experiencing several freezes and lockups, usually one or two 
>>> per day.
>>>
>>> Finally the whole box just started up to a setup screen - and 
>>> refused to update the schema from<blank>  to 1025 (i.e. no value to 
>>> current)
>>> I ran the db optimise script to check, then the mysqlcheck, as per 
>>> the wiki
>>> mysqlcheck found problems which it repaired
>>>
>>> I then thought I'd check the xfs filesystem on which the recordings 
>>> are stored and found quite a few orphaned files, which it placed in 
>>> Lost + Found
>>>
>>> I'm now faced with reinstalling the OS, and restoring the db from a 
>>> backup. I'd like to keep my database of recordings, videos and 
>>> schedules if possible, but if I have to lose the rest, I can live 
>>> with setting the combined fe/be up again
>>>
>>> Should I try a full restore from a couple of days before the final 
>>> crash first - or am I just prolonging the agony given the previous 
>>> issues?
>>>
>>> Or should I just do a partial restore from the last backup
>>
>> There's really no benefit to doing a partial restore if you have a 
>> good backup of a non-corrupt schema.
>
> This I understand, and thanks for the advice. What i'm concerned about 
> is the quality of the backup,  and hence the restore I guess ....

Yeah, that question is a bit more difficult to answer.

Based on the symptoms you've described, the most-likely problem you were 
encountering was that your settings table (where the current DB schema 
version is stored) was corrupt.  In this case, optimize_mythdb.pl will 
be unable to run, and mythtv-setup and mythbackend would both start up 
and think that no database exists and try to update from <blank> to 
current DB schema version.

If that's the case, the most-likely worst-case result is that you lost a 
single setting value from the DB when settings was repaired.  That 
setting value will be replaced with its default value the next time you 
run the program that uses the setting.  Therefore, if the above is true 
(and is the /only/ problem), a backup from the repaired DB is usable 
(and, IMHO, you should use the full restore).

However, it's also possible that you had other crashed tables, too.  If 
that's the case, you may have lost more data.  Even so, the worst-case 
scenario is likely that you lost a row from each of those tables.  And, 
if any of those tables is in the list of tables restored with a partial 
restore, you lost the row whether you do a full or partial restore.  
And, if the tables aren't in the list of tables restored by a partial 
restore, then you still have to re-create the lost data--as well as all 
the data you're throwing away with a partial restore--so, the partial 
restore really doesn't do anything other than force you to re-create 
data now (rather than when you find it's missing--i.e. doing 
http://www.gossamer-threads.com/lists/mythtv/users/264034#264034 when 
you find out one or more capture card isn't working; or going into 
mythtv-setup and fixing Storage Groups when you find out one or more 
directories is missing; or running mythcommflag --rebuild on a recording 
when you find it isn't seeking properly; or ...).

Note, also, that you can do a full restore, now, run for a couple weeks, 
and then change your mind (and do a partial restore from the new 
system's backup).  If you do a partial restore, now, you can't change 
your mind--you'll lose all the recording information from the point of 
the backup until you change your mind.

It's completely up to you, but if it were me, I'd do a full restore, and 
if things don't work, then I'd likely fix the issue(s), as above.  If a 
bunch of things don't work--and I fix a few of them over a period of a 
few weeks--and I stop trusting my data, then I'd consider a partial 
restore.  That said, I doubt that you'll have any problems with a full 
restore (it will likely "just work").

FWIW, I used to do partial restores all the time to "clean up" my DB.  
Then I started doing a lot of work with the DB-related code in MythTV 
and with the database itself, and I found that partial restores are 
almost always a lot of work with no benefit.

Mike


More information about the mythtv-users mailing list