[mythtv-users] mythtv dropping mysql???

[Karl Newman]

On Wed, Oct 22, 2014 at 4:01 AM, Michael T. Dean <mtdean at thirdcontact.com>
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 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?

Karl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20141022/3060e291/attachment.html>