maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxxx>]

Hi, Weldon!

On Sep 15, Weldon Whipple wrote:
> 
> > See where mysql_bin_log.write is called.
> 
> Excellent information! (I was hoping it might be that easy ???). If I
> succeed in making a DB_BIN_LOG class derive from MYSQL_BIN_LOG (with an
> instance of DB_BIN_LOG for each database that is currently binlogging),
> I could--in theory, at least--just call something like
> db_binlog->write () at those places.

Better make this write method virtual and implement it in your derived
class.

> >> Questions I Have.
> >> ----------------
> >>
> >> 1. I notice that binlog is in the list of engines and plugins
> >>   (displayed by SHOW ...). The bottom of log.cc has
> >>   "mysql_declare_plugin(binlog)". Do I need one for db_plugin?
> >>   (probably not?)
> >
> > depends.
> > binlog needs to be declated a plugin, to be able to pretent being a
> > storage engine, to be able to force two phase commit (2PC).
> >
> > 2PC is used to commit or rollback a transaction atomically in more
> > than one engine. And MySQL only uses 2PC when a transaction spans
> > more than one engine. But we want a transaction to be committed in
> > the engine and written to a binlog - and it should be done
> > atomically too. So, binlog pretends to be an engine, pretends to
> > participate in a transaction, which forces MySQL to use 2PC which
> > can guarantee that a transaction is either committed and written to
> > a binlog - or not committed and not written. Reliably, even if MySQL
> > crashes in the middle.
> >
> > So, if you don't use innodb, or don't use
> > innodb_flush_log_at_trx_commit=1 or don't use sync_binlog=1 you
> > probably don't need to support 2PC as your setup is not crash proof
> > anyway.
> >
> > If you care about innodb<->binlog consistency in the presence of
> > crashes, you should support 2PC in your binlog, as explained above.
> 
> We DO have a small percentage of innodb databases. If 2PC commits
> (rolls back, etc.) atomically to more than one engine, does that mean
> TWO (only)? ... or could it happen for three (specifically, innodb,
> MYSQL_BIN_LOG's binlog, and DB_BIN_LOG's binlog)?

See http://en.wikipedia.org/wiki/Two-phase_commit_protocol

It's two phases, not two engines, any number of engines can participate
in a transaction.

But! All of them but one have to support two-phase commit. Binlog does
not support it. Apparently, DB_BIN_LOG will not support it either -
adding two-phase commit support to binlog will surely be beyond the
scope of your work.

It means, you cannot have InnoDB, binlog, and DB_BIN_LOG to participate
in a two-phase commit. But you can disable MYSQL_BIN_LOG altogether and
only use DB_BIN_LOG - in that case you can have a two-phase commit
between DB_BIN_LOG and InnoDB.

> The two kinds of binlog files would have the same format (except that
> one is for only one database). Are they considered two storage
> engines/plugins?

If you want to write to both - yes. A transaction will need to be
written to both independently, so you want them to be in sync, and MySQL
uses 2PC to synchronize different data storages.

You can solve that by

1) not using MYSQL_BIN_LOG. In this case there's only InnoDB and
   DB_BIN_LOG to keep in sync, and InnoDB can do 2PC.

2) not making DB_BIN_LOG independent from MYSQL_BIN_LOG. For example,
   everything is written to binlog only. Then you have a background
   thread (or even a separate process) that reads binlog and writes your
   per-db binlogs. In that case you have only InnoDB and MYSQL_BIN_LOG
   to keep in sync, and MySQL already does that. Your background thread
   is only catching up, it is allowed to fall behind.

> (I notice that the "mysql_declare_plugin(binlog)" declaration is at
> the bottom of log.cc. Most of the other storage engines' corresponding
> declarations are in files in the storage subdirectories. If I were to
> need a "mysql_declare_plugin(db_binlog)" declaration, could it also go
> at the bottom of log.cc?  ... Or (since a DB_BIN_LOG is AKO [a kind of]
> MYSQL_BIN_LOG [in the OO sense]), are MYSQL_BIN_LOGs and DB_BIN_LOGs
> both kinds of binlog plugins ...?

if you want to have two plugins in one file you still need to use one
mysql_declare_plugin(), not two. See example at the end of
http://www.packtpub.com/article/look-inside-mysql-daemon-plugin
 
Regards,
Sergei