List:Internals« Previous MessageNext Message »
From:Ann W. Harrison Date:November 9 2009 4:36pm
Subject:Re: how does one know if their storage engine supports replication?
View as plain text  
Thomas Jones-Low wrote:
> 
> 	The only case I can find where STMT_CAPABLE isn't a good ideas is 
> when you have a transactional storage engine, the user is running with
> autocommit on and at read-uncommitted transaction level. Here two users
> could be updating over each other and you want the data to come out the
> same way. This scenario isn't a good idea in any case, but you don't
> want to mess up the user's data.
> 

Statement based logging is problematic for transactional storage
engines in general.  The most obvious case is auto-increment.  In
row-based logs, the auto-increment value is generated once and
replicated with the rest of the data.  In statement-based logs, if
a single statement generates more than one row, the engine must
guarantee that the auto-increment values will be generated the
same way on the master and target.  InnoDB solved that in several ways:

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

An INSERT ... SELECT statement can produce non-deterministic results if
another transaction is changing the records in the SELECT set.  InnoDB
handles that record and next-key locks that prevent concurrent 
transactions from changing records in that set or adding records to
it.

InnoDB added the READ COMMITTED isolation mode when row-base
logging became available. The results of a read committed
transaction depend on the order of reads, writes, and commits
and cannot be reliably replayed from a statement-based log.


Best regards,

Ann
Thread
how does one know if their storage engine supports replication?Zardosht Kasheff9 Nov
  • Re: how does one know if their storage engine supports replication?Sergei Golubchik9 Nov
    • Re: how does one know if their storage engine supports replication?Mats Kindahl9 Nov
  • Re: how does one know if their storage engine supports replication?Mats Kindahl9 Nov
    • Re: how does one know if their storage engine supports replication?MARK CALLAGHAN9 Nov
      • Re: how does one know if their storage engine supports replication?Sergei Golubchik9 Nov
        • Re: how does one know if their storage engine supports replication?MARK CALLAGHAN9 Nov
          • Re: how does one know if their storage engine supports replication?Sergei Golubchik9 Nov
            • Re: how does one know if their storage engine supports replication?Ann W. Harrison9 Nov
              • Re: how does one know if their storage engine supports replication?Zardosht Kasheff9 Nov
                • Re: how does one know if their storage engine supports replication?Sergei Golubchik9 Nov
                • Re: how does one know if their storage engine supports replication?Ann W. Harrison9 Nov
        • Re: how does one know if their storage engine supports replication?Paul McCullagh11 Nov
  • RE: how does one know if their storage engine supports replication?Thomas Jones-Low9 Nov
  • Re: how does one know if their storage engine supports replication?Ann W. Harrison9 Nov
Re: how does one know if their storage engine supports replication?Paul McCullagh11 Nov