[mythtv-users] Mysql cleanup wipe/restore

[Bruce Markey]

MegaDeTH wrote:
> I've got quite a bit of garbage in my database, due to all the upgrades 
> over the past few months.
> 
> I managed to get the settings reset, by forcing upgrades to the 
> database, somehow it was missing some.
> 
> However, I really need to clean out all the tables and start fresh.
> 
> What are the bare minimum tables, to backup and restore?

Of course, the first thing to do is get a full mysqldump and
stuff it away. Not only will this help you get back to where
you were if things go terribly wrong but it is ASCII SQL commands
so you can cut'n'paste anything that you later discover that
you want to put back or even to just lookup what you had previously.
 
> I was thinking the only 2 tables I really care about, are oldrecorded 
> and recorded.

You absolutely want recorded, yes. Ironically, oldrecorded is the
table most in need of periodic cleanup. I do some clean up every
month or two because most of the entries are of no use for finding
duplicates. After you have a good backup, you may want to:

delete from oldrecorded where subtitle='' and description='';
select title from oldrecorded;

then start deleting things like 50 entries for SportsCenter, etc.
Usually 80-90% of the entries can be thrown away.

The other table you may want to keep is recordedmarkup. If
you don't restore this, you would need to run mythcommflag
to flag commercial skipping and seek info for files without
seektables. This may take many, many hours (or days =) if
you have lots of recordings. Also, see the recent discussion
between Cedar and Chris Pinkham about mythcommflag when the
channel table has changed.

You may also want to keep your 'record' table with the shows
you want to record and their attributes. Here again there is
an issue if the channel table changes.

> However, all the chanid's will be off, currently they are all 70xx, will 
> this really matter?  If so I can update them to the new channelid's 
> after I reload.

This may be a good reason to reload your database. Your first
source would once again be 10xx. This won't matter going forward
and your recordings with 70xx can still be played. However,
mythcommflag won't work for 0.12 if the 70xx entries are not
in the channel table. If you want to restore your record table,
you would need to make a copy of the INSERT lines from your
backup into a temp file and edit all the 70xxs to 10xx so that
the chanids will match the 'program' data.

--  bjm