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
