On 04/10/2012 12:27 PM, Martin Simmons wrote:
>>>>>> On Tue, 10 Apr 2012 11:15:29 -0400, Phil Stracchino said:
>>
>> On 04/10/2012 10:51 AM, Joe Nyland wrote:
>>> I'm a bit ashamed to admit I'm still battling this! I've removed
>>> '--delete-master-logs' from my mysqldump line, but it hasn't helped.
>>>
>>> For some reason, it seems as if the dump does not contain any mention
>>> of the temporary tables being created, neither do the binary logs,
>>> however there are statements which refer to bacula.batch, as if it
>>> should be there.
>>>
>>> Could it be that these statements refer to a bacula.batch table which
>>> was created by another thread prior to the mysql dump being created?
>>> ...and that's why the "CREATE TEMPORARY TABLE bacula.batch" statement
>>> is not in the binary logs after the full backup. Surely, if this were
>>> the case, the bacula.batch table sowuld be included in the dump would
>>> they not?
>>
>> No, because a dump will not contain temporary tables.  So if you restore
>> a dump and a set of binlogs that contain transactions referring to
>> temporary tables extant when you created the dump, yes, those
>> transactions are irretrievably orphaned.
> 
> Is mysql's backup procedure really that broken?  Or maybe the problem is
> caused by Bacula misusing temporary tables (e.g. without a transaction)?

Why do you consider that "broken"?

A temporary table has meaning and visibility only to a single database
connection.  In order for there to be any use or purpose to backing up
temporary tables, the backup would have to capture, and be able to
restore, the entire state of both MySQL and whatever application was
making the connection at the time.  The mysqldump utility does not dump
temporary tables because doing so is not useful.  Expecting otherwise is
like dropping and breaking your cellphone in the middle of a call,
getting the phone replaced by your phone insurance, restoring your data
and contacts back onto the new phone, and expecting to be able to just
resume the interrupted call from the moment before you dropped the phone.

You can *start a new call* and complete the conversation, if you
remember what it was, but you can't just resume that call.  It's gone,
over, finito.  And you can't restore a database and just resume a
connection that happened to be running when you made the backup.  That
connection is gone, along with all of its thread-local state data.


-- 
  Phil Stracchino, CDK#2     DoD#299792458     ICBM: 43.5607, -71.355
  alaric AT caerllewys DOT net   alaric AT metrocast DOT net   phil AT 
co.ordinate DOT org
  Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
                 It's not the years, it's the mileage.

------------------------------------------------------------------------------
Better than sec? Nothing is better than sec when it comes to
monitoring Big Data applications. Try Boundary one-second 
resolution app monitoring today. Free.
http://p.sf.net/sfu/Boundary-dev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users