>>>>> On Tue, 10 Apr 2012 12:44:25 -0400, Phil Stracchino said:
> 
> 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.

Sorry, I didn't mean mysqldump on its own.

The MySQL documentation suggests that you can make a backup from the output of
mysqldump plus later binlogs and then use them to restore a database, but what
use is that if it doesn't work with temporary tables?

For this to work, all transactions should either be entirely in the output of
mysqldump or entirely in the later binlogs.  This could probably work if
temporary tables are used within a transaction, because hopefully mysqldump
will wait for it to finish.  It probably can't work if temporary tables are
used without a transaction.

__Martin

------------------------------------------------------------------------------
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