>>>>> On Fri, 13 Apr 2012 14:13:41 -0400, Phil Stracchino said:
> 
> On 04/13/2012 01:02 PM, Martin Simmons wrote:
> >>>>>> On Tue, 10 Apr 2012 15:27:22 -0400, Phil Stracchino said:
> >> You shouldn't think of a temporary table as persistent DB data.  Think
> >> of them instead as part of the transient state of a single ongoing
> >> transaction.  Is it a reasonable expectation for a DB restore to be able
> >> to restore any part of the transient state of a transaction that
> >> happened to be running when the backup was made?  Even if it could,
> >> where would the results go?
> >>
> >> There's no way to resume an interrupted transaction from the middle, and
> >> so there's no point in backing up any of its state except to roll back
> >> anything it's already partly done.  If you want to repeat the
> >> transaction, you have to restart it from the beginning, which will
> >> recreate any temporary tables it was using anyway.
> > 
> > OK, so it looks like Bacula's use of temporary tables outside a transaction 
> > is
> > at best incompatible with "live" backups of MySQL and at worst incorrect.
> 
> I'm not understanding what you think is "incorrect" here.
> 
> Let's try it this way:  How are you thinking that temporary tables
> should work, and what do you think "should happen" if you try to restore
> your Bacula catalog to a state that represents a point in the middle of
> a job that is not currently running?

I think it should be impossible to make a database backup that represents a
point in the middle of anything.  By "should be impossible" I mean that either
MySQL should always prevent it or database clients should use MySQL in such a
way that allows MySQL to prevent it.

The design of the binlogs seems to rule out the first option.

However, the second option looks possible if the client uses temporary tables
within a single transaction.  I.e. it starts a transaction, creates its
temporary tables, fills/uses them to update the permanent tables, drops the
temporary tables and then commits the transaction.

Can that work with mysqldump --single-transaction?  Note that it allows MySQL
to forcibly abort the client's transaction if necessary, so it can be
rerecorded in a binlog that comes after the backup.

Bacula's use of temporary tables doesn't follow this approach because it
doesn't start a transaction before creating the table.

__Martin

------------------------------------------------------------------------------
For Developers, A Lot Can Happen In A Second.
Boundary is the first to Know...and Tell You.
Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
http://p.sf.net/sfu/Boundary-d2dvs2
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users