Mixing transactional and nontransactional statements within the same transaction. In general, 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 and nontransactional tables and writes to any of them.
If you update transactional tables from nontransactional tables
COMMIT sequence, updates to
the binary log may be out of synchrony with table states if the
nontransactional table is updated before the transaction
commits. This occurs because the transaction is written to the
binary log only when it is committed.
Before MySQL 4.0.15, any update to a nontransactional table is
written to the binary log at once when the update is made,
whereas transactional updates are written on
COMMIT or not written at all if
must take this into account when updating both transactional
tables and nontransactional tables within the same transaction.
(This is true not only for replication, but also if you are
using binary logging for backups.)
As of MySQL 4.0.15, we changed the logging behavior for
transactions that mix updates to transactional and
nontransactional tables, which solves the problems (order of
statements is good in the binary log, and all needed statements
are written to the binary log even in case of
problem that remains is that when a second connection updates
the nontransactional table while the first connection
transaction is not finished yet, incorrect ordering can still
occur because the second connection update is written
immediately after it is done.
Using different storage engines on master and slave.
It is possible to replicate transactional tables on the master
using nontransactional tables on the slave. For example, you
can replicate an
InnoDB master table as a
MyISAM slave table. However, there are
issues that you should consider before you do this:
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 transactional table and the slave 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.