89397

Mixing transactional and nontransactional statements within the same transaction. In

generał, you should avoid transactions that update both transactional and nontransactional tables in a

replication environment. You should also avoid using any statement that accesses both transactional

(or temporary) and nontransactional tables and writes to any of them.

As of MySQL 5.5.2, the server uses these rules for binary logging:

•    If the initial statements in a transaction are nontransactional, they are written to the binary log

immediately. The remaining statements in the transaction are cached and not written to the binary

log until the transaction is committed. (If the transaction is rolled back, the cached statements are

written to the binary log only if they make nontransactional changes that cannot be rolled back.

Otherwise, they are discarded.)

•    For statement-based logging, logging of nontransactional statements is affected by the

binlog_direct_non_transactional_updates[2046] system variable. When this variable

is OFF (the default), logging is as just described. When this variable is ON, logging occurs

immediately for nontransactional statements occurring anywhere in the transaction (not just initial

nontransactional statements). Other statements are kept in the transaction cache and logged when

the transaction commits. binlog_direct_non_transactional_updates[2046] has no effect

for row-format or mixed-format binary logging.