It is possible to replicate transactional tables on the master
using nontransactional tables on the slave. For example, you can
InnoDB master table as a
MyISAM slave table. However, if you do this,
there are problems if the slave is stopped in the middle of a
block because the slave restarts at the beginning of the
Mixing transactional and nontransactional statements within the same
The semantics of mixing nontransactional and transactional
tables in a transaction in the first statement of a
transaction changed in MySQL 5.1.31. Previously, if the first
statement in a transaction contained nontransactional changes,
the statement was written directly to the binary log, in an
attempt to mimic the nontransactional behavior of the
statement. Beginning with MySQL 5.1.31, any statement
appearing after a
always considered part of the transaction and cached. This
means that nontransactional changes do not propagate to the
slave until the transaction is committed and thus written to
the binary log. In addition (also beginning with MySQL
AUTOMCOMMIT is set to 0, any
statement appearing immediately following a
COMMIT is handled in the same
Previously, a statement was considered nontransactional if it changed a nontransactional table. This behavior had the following subtle but nontrivial consequences:
A statement containing only nontransactional changes was written immediately to the binary log (sometime referred to as “write-ahead”).
A statement containing only transactional changes was always cached while waiting for the transaction to be committed.
A statement containing a mix of transactional and nontransactional changes (that is, a statement updating both transaction and nontransactional tables) could lead to mismatched tables on the master and the slave.
In situations where transactions mix updates to transactional
and nontransactional tables, the order of statements in the
binary log is correct, and all needed statements are written to
the binary log even in case of a
However, when a second connection updates the nontransactional
table before the first connection's transaction is complete,
statements can be logged out of order, because the second
connection's update is written immediately after it is
performed, regardless of the state of the transaction being
performed by the first connection.
Due to the nontransactional nature of
tables, it is possible to have a statement that only partially
updates a table and returns an error code. This can happen, for
example, on a multiple-row insert that has one row violating a
key constraint, or if a long update statement is killed after
updating some of the rows. If that happens on the master, the
slave thread exits and waits for the database administrator to
decide what to do about it unless the error code is legitimate
and execution of the statement results in the same error code on
the slave. If this error code validation behavior is not
desirable, some or all errors can be masked out (ignored) with
You should avoid transactions that update both transactional and nontransactional tables in a replication environment.
When the storage engine type of the slave is nontransactional, transactions on the master that mix updates of transactional and nontransactional tables should be avoided because they can cause inconsistency of the data between the master's transactional table and the slave's nontransactional table. That is, such transactions can lead to master storage engine-specific behavior with the possible effect of replication going out of synchrony. MySQL does not issue a warning about this currently, so extra care should be taken when replicating transactional tables from the master to nontransactional ones on the slaves.