[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 
http://www.gossamer-threads.com/lists/mythtv/users/264034#264034 .

> 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 
> mythconverg?
>
> 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 ( 
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Database_Restore 
) along with the capture card/video source cleanup ( 
http://www.gossamer-threads.com/lists/mythtv/users/264034#264034 ).

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 
understand 
http://www.mythtv.org/wiki/Database_Backup_and_Restore#Partial_restore_of_a_backup 
.

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.

Mike


More information about the mythtv-users mailing list