[mythtv-users] mythtv dropping mysql???

[Michael T. Dean]

On 10/22/2014 10:46 AM, Karl Newman wrote:
> On Wed, Oct 22, 2014 at 4:01 AM, Michael T. Dean wrote:
>
>     If you run any kind of REPAIR TABLE (mysqlcheck -r or
>     optimize_mythdb.pl or submit REPAIR TABLE via mysql command-line
>     client) and it fails and /anything/--that's any process at
>     all--attempts to access (even just a read query) the table before
>     you've successfully completed the repair, data will almost
>     definitely be lost, potentially even the entire table.  Therefore,
>     to automatically run REPAIR TABLE, we have to ensure no one at all
>     (no official applications, nor 3rd party clients, nor mysql
>     command-line clients, nor phpmyadmins, nor...) will use that table
>     until it's properly fixed.  Therefore, it is extremely unsafe to
>     automatically repair tables when we allow/require everyone and
>     their brother to make direct connections to the database.
>
>     Creating a backup takes time and system resources and locks
>     tables, all of which may cause problems if the system is or
>     becomes busy during the backup process.  If we're the only ones
>     accessing the database, we know exactly what database activity
>     exists (and we'll also be able to determine what other activity is
>     in progress and/or scheduled so we can determine when looks like
>     an appropriate time to start a backup).  Also (and more
>     importantly), to create a proper backup and ensure data integrity
>     in a relational database with data across multiple tables linked
>     together, it is critical that the backup be performed without
>     allowing changes to those cross-linked tables.  While backing up
>     the database locks tables, it does a table-by-table lock, meaning
>     that if you're backing up the database when something is using the
>     database, it's possible you may back up a table, then a delete is
>     performed which removes a record from that table and some
>     cross-linked tables, then you back up some of those cross-linked
>     tables, meaning the data in your backup is corrupt.  The way to
>     avoid this is to ensure that nothing is accessing the database
>     during the backup (at least making sure nothing is changing the
>     database).
>
>     So, basically, no we can't (which is the only reason we don't)
>     automatically repair tables or backup the database when access is
>     a free-for-all.
>
> Wait, are you saying that the database backup and optimize_mythdb.pl 
> that I run via cron every night could corrupt my database

Yes, see Caution and Warning at 
http://dev.mysql.com/doc/refman/5.1/en/repair-table.html (and that 
applies whether using REPAIR TABLE or mysqlcheck or myiamcheck.

> or result in a corrupt backup file? I've never had to use my database 
> backup (knock on wood) but I can't say I've ever seen any problems 
> from optimize_mythdb.pl (over the last ~9 years). If it is dangerous, 
> then what's the "right" way to run it? Shut down mythtv before running 
> the scripts? Seems a bit extreme. What do you do on your own system?

I run the optimize_mythdb.pl script on a daily cron job and don't shut 
down MythTV to do it because I've determined that the possibility of 
data loss due to a failed repair is small enough (especially with the 
hardware I'm running in my environment (electricity, etc.) that's proven 
itself pretty stable) that I'm willing to take the risk so I get the 
benefits (i.e. things fixing themselves before I realize there's a 
problem).  I also know that I'd be able to fix things if I do have 
serious data loss (thanks to backups and moving new recordings to Video 
Library or whatever) and--most importantly--that it's just TV.  However, 
I'm not willing to make that decision for every user (especially with 
varying levels of reliability of their OS/MySQL 
server/hardware/power/...)--or, more precisely, I'm not willing to force 
that decision on others.

I do shut down MythTV to make backups (and don't do backups on a cron 
job--though, actually, my shut down script for MythTV does make a backup 
when it's run on the master backend system).  However, I'll say that a 
backup with some amount of corrupt data due to changes being made while 
the backup was created is still better than no backup at all when 
there's a problem.

Mike