<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Wed, Oct 22, 2014 at 4:01 AM, Michael T. Dean <span dir="ltr"><<a href="mailto:mtdean@thirdcontact.com" target="_blank">mtdean@thirdcontact.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
If you run any kind of REPAIR TABLE (mysqlcheck -r or <a href="http://optimize_mythdb.pl" target="_blank">optimize_mythdb.pl</a> 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.<br>
<br>
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).<br>
<br>
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.<div class="HOEnZb"></div></blockquote><div> </div><div>Wait, are you saying that the database backup and <a href="http://optimize_mythdb.pl">optimize_mythdb.pl</a> 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 <a href="http://optimize_mythdb.pl">optimize_mythdb.pl</a> (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?<br><br></div><div>Karl<br></div></div></div></div>