[mythtv-users] mythtv dropping mysql???

[Michael T. Dean]

On 10/22/2014 04:24 AM, Michael Watson wrote:
> On 22/10/2014 12:19 AM, Michael T. Dean wrote:
>>
>> More than just set up--as you say below, distros like Mythbuntu or 
>> LinHES have done an excellent job of making setup nearly foolproof.  
>> However, even just maintaining the database is difficult enough for 
>> non-DBA's (even those using distros like Mythbuntu/LinHES that do 
>> their best to help maintain the DB). See, for example, the tip of the 
>> iceberg at:
>>
>> https://code.mythtv.org/trac/ticket/12299
>> https://code.mythtv.org/trac/ticket/10831
>> https://code.mythtv.org/trac/ticket/10348
>> https://code.mythtv.org/trac/ticket/10177
>> https://code.mythtv.org/trac/ticket/9978
>> https://code.mythtv.org/trac/ticket/9300
>> https://code.mythtv.org/trac/ticket/8404
>> https://code.mythtv.org/trac/ticket/8473
>> https://code.mythtv.org/trac/ticket/8089
>> https://code.mythtv.org/trac/ticket/6429
>> https://code.mythtv.org/trac/ticket/5415
>> https://code.mythtv.org/trac/ticket/3804
>> (That being just a sampling of the ones that were actually reported 
>> as bugs that doesn't even touch the significantly greater number of 
>> posts on list with the same issue, let alone the questions in IRC, or 
>> the people who didn't know what was going on and also never asked on 
>> a MythTV list/forum/irc channel and eventually either figured it out 
>> themselves or deleted their DB and started over or waited long enough 
>> for the scheduled optimize_mythdb.pl their distro had set up to fix 
>> it or ...)
> Does MySQL need to be embedded for the MBE to regularly run 
> optimize_mythdb.pl or a backup by itself?????

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.

Mike