[mythtv-users] Cleaning my DB?
Michael T. Dean
mtdean at thirdcontact.com
Fri May 28 15:00:29 UTC 2010
On 05/28/2010 09:39 AM, Derek Stark wrote:
> I'm about to move to .23. Notice how I didn't say "upgrade"? I'd like
> to clean a lot of things out of my database. I've got a phantom tuner,
> tuner IDs in the high teens,
This is the only thing you've mentioned that warrants cleaning up. You
can clean it with
> settings from old front ends that are gone, etc etc. The only thing I
> really want to keep are my recordings.
There is absolutely no benefit to "cleaning" the rest of the database.
MythTV automatically cleans up anything that's not supposed to to be
there. Anything else that it leaves (settings, etc.) take up virtually
no space on the hard drive and don't affect performance.
The only benefit of doing a database "cleanup" is the placebo effect. A
sugar pill gives the same benefit, and is a lot less likely to cause
problems with your MythTV system. (Therefore, regardless of how many
people tell you that their systems ran faster after doing a partial
restore, your system will not run faster after a partial restore. It
may, in fact, run faster, but the only reason it's running faster is
because you did a complete distro/OS upgrade/reinstall. Other than
that, you may /perceive/ MythTV to run faster, but it has nothing to do
with the database cleanup.
> Does anyone have an idea how I should go about this? Should I do the
> schema upgrade & then start blowing records away from tables that
> offend me? Attempt to reinsert the recordings into an empty .23
> I am not afraid of the MySQL command-line and have written a few SQL
> queries in my day (previous life as a web programmer), but that
> doesn't mean I'd know what to do within the MythTV context.
OK, you know MySQL and you're not afraid of it. Do you have a
comprehensive understanding of the MythTV database schema, all the data
integrity requirements, all the foreign key references and data
dependencies? If not, you should /never/ touch the database data or
schema directly. (FWIW, I happen to understand the database schema well
enough to consider myself to fall into the "should not touch the
database directly" category.)
> Or should I just punt and export all my recordings out to MythVideo?
IMHO, you should do a full backup (
http://www.mythtv.org/wiki/Database_Backup_and_Restore ), install the
new distro and make sure you use the exact same host names for all
MythTV systems, and then do a full restore (
) along with the capture card/video source cleanup (
A partial restore requires that you completely reconfigure your system
(mythtv-setup, mythfrontend settings, and all plugin settings). If you
make any mistakes, your system will be non-functional until you find
what you've misconfigured and reconfigure it properly.
Any other approach you use to move the database data is a) a waste of
your time (and, possibly, of the list's time if you have problems and
ask questions about how to fix them), b) significantly more trouble than
a proper restore, c) likely to leave your system broken--at least for a
time. Most people who attempt to do what you're attempting to do break
their systems because they don't understand the proper procedure--and
all the MythTV requirements for the data.
Note that the partial restore instructions state that, "A partial
restore...is primarily useful as a last-resort when some of the data in
the database is corrupt (or, more precisely, a 2nd-to-last resort--as
starting over from scratch would be a true last resort). In general, do
not do a partial restore--instead do a full restore." Please do not
even consider doing a partial restore until you completely read and
Then, once you understand that section, do a full restore as mentioned
above. Cleaning up your OS, then cleaning your Input Connections (
http://www.gossamer-threads.com/lists/mythtv/users/264034#264034 ) will
give you a very clean/fast MythTV system.
More information about the mythtv-users