Oracle 5.0, Reference Manual

Page: 1526 / 3232

Replication Features and Issues
1506
Older versions of MySQL (prior to 4.1) differed significantly in several ways in their handling of the
TIMESTAMP
data type from what is supported in MySQL versions 5.0 and newer; these include syntax
extensions which are deprecated in MySQL 5.1, and that no longer supported in MySQL 5.5. This this
can cause problems (including replication failures) when replicating between MySQL Server versions,
if you are using columns that are defined using the old
TIMESTAMP(N)
syntax. See Section 2.19.1.2,
“Upgrading from MySQL 4.1 to 5.0” , for more information about the differences, how they can impact
MySQL replication, and what you can do if you encounter such problems.
16.4.1.24. Replication and Time Zones
The same system time zone should be set for both master and slave. Otherwise, statements
depending on the local time on the master are not replicated properly, such as statements that use
the
NOW()
[926] or
FROM_UNIXTIME()
[923] functions. You can set the time zone in which MySQL
server runs by using the
--timezone=timezone_name
[248] option of the
mysqld_safe
script or by
setting the
TZ
environment variable. See also Section 16.4.1.8, “Replication and System Functions” .
If the master is MySQL 4.1 or earlier, both master and slave should also use the same default
connection time zone. That is, the
--default-time-zone
[406] parameter should have the same
value for both master and slave.
CONVERT_TZ(...,...,@@session.time_zone)
[918] is properly replicated only if both master
and slave are running MySQL 5.0.4 or newer.
16.4.1.25. Replication and Transactions
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.
In MySQL 5.0 the server uses this rule 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.)
To apply this rule, the server considers a statement nontransactional if the first changes it makes
change nontransactional tables, transactional if the first changes it makes change transactional tables.
“First” applies in the sense that a statement may have several effects if it involves such things as
triggers, stored functions, or multiple-table updates.
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
ROLLBACK
. However, when a second connection updates the nontransactional table before
the first connection transaction is complete, statements can be logged out of order because the second
connection update is written immediately after it is performed, regardless of the state of the transaction
being performed by the first connection.
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, if you do this, there are problems if the slave
is stopped in the middle of a
BEGIN
...
COMMIT
block because the slave restarts at the beginning of the
BEGIN
block.
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