[mythtv-users] mythtv dropping mysql???
Michael T. Dean
mtdean at thirdcontact.com
Wed Oct 22 16:50:40 UTC 2014
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
More information about the mythtv-users
mailing list