(mysql.info) replication-features
Info Catalog
(mysql.info) replication-upgrade
(mysql.info) replication
(mysql.info) replication-options
6.7 Replication Features and Known Problems
===========================================
In general, replication compatibility at the SQL level requires that
any features used be supported by both the master and the slave
servers. If you use a feature on a master server that is available only
as of a given version of MySQL, you cannot replicate to a slave that is
older than that version. Such incompatibilities are likely to occur
between series, so that, for example, you cannot replicate from MySQL
5.0 to 4.1. However, these incompatibilities also can occur for
within-series replication. For example, the `SLEEP()' function is
available in MySQL 5.0.12 and up. If you use this function on the
master server, you cannot replicate to a slave server that is older
than MySQL 5.0.12.
If you are planning to use replication between 5.0 and a previous
version of MySQL you should consult the edition of the MySQL Reference
Manual corresponding to the earlier release series for information
regarding the replication characteristics of that series.
The following list provides details about what is supported and what is
not. Additional `InnoDB'-specific information about replication is
given in innodb-and-mysql-replication.
Replication issues with regard to stored routines and triggers is
described in stored-procedure-logging.
* *Known issue*: In MySQL 5.0.17, the syntax for `CREATE TRIGGER'
changed to include a `DEFINER' clause for specifying which access
privileges to check at trigger invocation time. (See
create-trigger, for more information.) However, if you attempt
to replicate from a master server older than MySQL 5.0.17 to a
slave running MySQL 5.0.17 or up, replication of `CREATE TRIGGER'
statements fails on the slave with a `Definer not fully qualified'
error. A workaround is to create triggers on the master using a
version-specific comment embedded in each `CREATE TRIGGER'
statement:
CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER ... ;
`CREATE TRIGGER' statements written this way will replicate to
newer slaves, which pick up the `DEFINER' clause from the comment
and execute successfully.
* Replication of `AUTO_INCREMENT', `LAST_INSERT_ID()', and
`TIMESTAMP' values is done correctly.
* The `USER()', `UUID()', and `LOAD_FILE()' functions are replicated
without change and thus do not work reliably on the slave.
* User privileges are replicated only if the `mysql' database is
replicated. That is, the `GRANT', `REVOKE', `SET PASSWORD', `CREATE
USER', and `DROP USER' statements take effect on the slave only if
the replication setup includes the `mysql' database.
If you're replicating all databases, but don't want statements
that affect user privileges to be replicated, set up the slave to
not replicate the `mysql' database, using the
-replicate-wild-ignore-table=mysql.% option. The slave will
recognize that issuing privilege-related SQL statements won't have
an effect, and thus not execute those statements.
* The `GET_LOCK()', `RELEASE_LOCK()', `IS_FREE_LOCK()', and
`IS_USED_LOCK()' functions that handle user-level locks are
replicated without the slave knowing the concurrency context on
master. Therefore, these functions should not be used to insert
into a master's table because the content on the slave would
differ. (For example, do not issue a statement such as `INSERT
INTO mytable VALUES(GET_LOCK(...))'.)
* The `FOREIGN_KEY_CHECKS', `SQL_MODE', `UNIQUE_CHECKS', and
`SQL_AUTO_IS_NULL' variables are all replicated in MySQL 5.0. The
`storage_engine' system variable (also known as `table_type') is
not yet replicated, which is a good thing for replication between
different storage engines.
* Starting from MySQL 5.0.3 (master and slave), replication works
even if the master and slave have different global character set
variables. Starting from MySQL 5.0.4 (master and slave),
replication works even if the master and slave have different
global timezone variables.
* The following applies to replication between MySQL servers that
use different character sets:
1. If the master uses MySQL 4.1, you must _always_ use the same
_global_ character set and collation on the master and the
slave, regardless of the MySQL version running on the slave.
(These are controlled by the -character-set-server and
-collation-server options.) Otherwise, you may get
duplicate-key errors on the slave, because a key that is
unique in the master character set might not be unique in the
slave character set. Note that this is not a cause for
concern when master and slave are both MySQL 5.0 or later.
2. If the master is older than MySQL 4.1.3, the character set of
any client should never be made different from its global
value because this character set change is not known to the
slave. In other words, clients should not use `SET NAMES',
`SET CHARACTER SET', and so forth. If both the master and the
slave are 4.1.3 or newer, clients can freely set session
values for character set variables because these settings are
written to the binary log and so are known to the slave. That
is, clients can use `SET NAMES' or `SET CHARACTER SET' or can
set variables such as `COLLATION_CLIENT' or
`COLLATION_SERVER'. However, clients are prevented from
changing the _global_ value of these variables; as stated
previously, the master and slave must always have identical
global character set values.
3. If you have databases on the master with character sets that
differ from the global `character_set_server' value, you
should design your `CREATE TABLE' statements so that tables
in those databases do not implicitly rely on the database
default character set (see Bug #2326
(http://bugs.mysql.com/2326)). A good workaround is to state
the character set and collation explicitly in `CREATE TABLE'.
* If the master uses MySQL 4.1, the same system time zone should be
set for both master and slave. Otherwise some statements will not
be replicated properly, such as statements that use the `NOW()' or
`FROM_UNIXTIME()' functions. You can set the time zone in which
MySQL server runs by using the -timezone=TIMEZONE_NAME option of
the `mysqld_safe' script or by setting the `TZ' environment
variable. Both master and slave should also have the same default
connection time zone setting; that is, the -default-time-zone
parameter should have the same value for both master and slave.
Note that this is not necessary when the master is MySQL 5.0 or
later.
* `CONVERT_TZ(...,...,@@global.time_zone)' is not properly
replicated. `CONVERT_TZ(...,...,@@session.time_zone)' is properly
replicated only if the master and slave are from MySQL 5.0.4 or
newer.
* Session variables are not replicated properly when used in
statements that update tables. For example, `SET
MAX_JOIN_SIZE=1000' followed by `INSERT INTO mytable
VALUES(@@MAX_JOIN_SIZE)' will not insert the same data on the
master and the slave. This does not apply to the common sequence
of `SET TIME_ZONE=...' followed by `INSERT INTO mytable
VALUES(CONVERT_TZ(...,...,@@time_zone))', which replicates
correctly as of MySQL 5.0.4.
* It is possible to replicate transactional tables on the master
using non-transactional 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.
* Update statements that refer to user-defined variables (that is,
variables of the form `@VAR_NAME') are replicated correctly in
MySQL 5.0. However, this is not true for versions prior to 4.1.
Note that user variable names are case insensitive starting in
MySQL 5.0. you should take this into account when setting up
replication between MySQL 5.0 and older versions.
* Slaves can connect to masters using SSL.
* In MySQL 5.0 (starting from 5.0.3), there is a global system
variable `slave_transaction_retries': If the replication slave SQL
thread fails to execute a transaction because of an `InnoDB'
deadlock or because it exceeded the `InnoDB'
`innodb_lock_wait_timeout' or the NDBCluster
`TransactionDeadlockDetectionTimeout' or
`TransactionInactiveTimeout' value, the transaction automatically
retries `slave_transaction_retries' times before stopping with an
error. The default value is 10. Starting from MySQL 5.0.4, the
total retry count can be seen in the output of `SHOW STATUS'; see
server-status-variables.
* If a `DATA DIRECTORY' or `INDEX DIRECTORY' table option is used in
a `CREATE TABLE' statement on the master server, the table option
is also used on the slave. This can cause problems if no
corresponding directory exists in the slave host filesystem or if
it exists but is not accessible to the slave server. MySQL
supports an `sql_mode' option called `NO_DIR_IN_CREATE'. If the
slave server is run with this SQL mode enabled, it ignores the
`DATA DIRECTORY' and `INDEX DIRECTORY' table options when
replicating `CREATE TABLE' statements. The result is that `MyISAM'
data and index files are created in the table's database directory.
* It is possible for the data on the master and slave to become
different if a statement is designed in such a way that the data
modification is non-deterministic; that is, left to the will of
the query optimizer. (This is in general not a good practice, even
outside of replication.) For a detailed explanation of this issue,
see open-bugs.
* _The following applies only if either the master or the slave is
running MySQL version 5.0.3 or older_: If on the master a `LOAD
DATA INFILE' is interrupted (integrity constraint violation, killed
connection, and so on), the slave skips the `LOAD DATA INFILE'
entirely. This means that if this command permanently inserted or
updated table records before being interrupted, these
modifications are not replicated to the slave.
* Some forms of the `FLUSH' statement are not logged because they
could cause problems if replicated to a slave: `FLUSH LOGS', `FLUSH
MASTER', `FLUSH SLAVE', and `FLUSH TABLES WITH READ LOCK'. For a
syntax example, see flush. The `FLUSH TABLES', `ANALYZE
TABLE', `OPTIMIZE TABLE', and `REPAIR TABLE' statements are
written to the binary log and thus replicated to slaves. This is
not normally a problem because these statements do not modify
table data. However, this can cause difficulties under certain
circumstances. If you replicate the privilege tables in the
`mysql' database and update those tables directly without using
`GRANT', you must issue a `FLUSH PRIVILEGES' on the slaves to put
the new privileges into effect. In addition, if you use `FLUSH
TABLES' when renaming a `MyISAM' table that is part of a `MERGE'
table, you must issue `FLUSH TABLES' manually on the slaves. These
statements are written to the binary log unless you specify
`NO_WRITE_TO_BINLOG' or its alias `LOCAL'.
* MySQL only supports one master and many slaves. In the future we
plan to add a voting algorithm for changing the master
automatically in the event of problems with the current master. We
also plan to introduce agent processes to help perform load
balancing by sending `SELECT' queries to different slaves.
* When a server shuts down and restarts, its `MEMORY' (`HEAP' tables
become empty. The master replicates this effect to slaves as
follows: The first time that the master uses each `MEMORY' table
after startup, it logs an event that notifies the slaves that the
table needs to be emptied by writing a `DELETE' statement for that
table to the binary log. See memory-storage-engine, for
more information.
* Temporary tables are replicated except in the case where you shut
down the slave server (not just the slave threads) and you have
replicated temporary tables that are used in updates that have not
yet been executed on the slave. If you shut down the slave server,
the temporary tables needed by those updates are no longer
available when the slave is restarted. To avoid this problem, do
not shut down the slave while it has temporary tables open.
Instead, use the following procedure:
1. Issue a `STOP SLAVE' statement.
2. Use `SHOW STATUS' to check the value of the
`Slave_open_temp_tables' variable.
3. If the value is 0, issue a `mysqladmin shutdown' command to
stop the slave.
4. If the value is not 0, restart the slave threads with `START
SLAVE'.
5. Repeat the procedure later until the `Slave_open_temp_tables'
variable is 0 and you can stop the slave.
* The syntax for multiple-table `DELETE' statements that use table
aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you
should use the true table name to refer to any table from which
rows should be deleted:
DELETE test FROM test AS t1, test2 WHERE ...
In MySQL 4.1, you must use the alias:
DELETE t1 FROM test AS t1, test2 WHERE ...
If you use such `DELETE' statements, the change in syntax means
that a 4.0 master cannot replicate to 4.1 (or higher) slaves.
* It is safe to connect servers in a circular master/slave
relationship if you use the -log-slave-updates option. That means
that you can create a setup such as this:
A -> B -> C -> A
However, many statements do not work correctly in this kind of
setup unless your client code is written to take care of the
potential problems that can occur from updates that occur in
different sequence on different servers.
Server IDs are encoded in binary log events, so server A knows
when an event that it reads was originally created by itself and
does not execute the event (unless server A was started with the
-replicate-same-server-id option, which is meaningful only in rare
cases). Thus, there are no infinite loops. This type of circular
setup works only if you perform no conflicting updates between the
tables. In other words, if you insert data in both A and C, you
should never insert a row in A that may have a key that conflicts
with a row inserted in C. You should also not update the same rows
on two servers if the order in which the updates are applied is
significant.
* If a statement on a slave produces an error, the slave SQL thread
terminates, and the slave writes a message to its error log. You
should then connect to the slave manually and determine the cause
of the problem. (`SHOW SLAVE STATUS' is useful for this.) Then fix
the problem (for example, you might need to create a non-existent
table) and run `START SLAVE'.
* It is safe to shut down a master server and restart it later.
When a slave loses its connection to the master, the slave tries
to reconnect immediately and retries periodically if that fails.
The default is to retry every 60 seconds. This may be changed with
the -master-connect-retry option. A slave also is able to deal
with network connectivity outages. However, the slave notices the
network outage only after receiving no data from the master for
`slave_net_timeout' seconds. If your outages are short, you may
want to decrease `slave_net_timeout'. See
server-system-variables.
* Shutting down the slave (cleanly) is also safe because it keeps
track of where it left off. Unclean shutdowns might produce
problems, especially if the disk cache was not flushed to disk
before the system went down. Your system fault tolerance is
greatly increased if you have a good uninterruptible power supply.
Unclean shutdowns of the master may cause inconsistencies between
the content of tables and the binary log in master; this can be
avoided by using `InnoDB' tables and the -innodb-safe-binlog
option on the master. See binary-log.
* -innodb-safe-binlog is unneeded as of MySQL 5.0.3, having
been made obsolete by the introduction of XA transaction support.
* Due to the non-transactional nature of `MyISAM' 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 the -slave-skip-errors option.
* If you update transactional tables from non-transactional tables
inside a `BEGIN'/`COMMIT' sequence, updates to the binary log may
be out of synchrony with table states if the non-transactional
table is updated before the transaction commits. This occurs
because the transaction is written to the binary log only when it
is committed.
* In situations where transactions mix updates to transactional and
non-transactional 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 non-transactional 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.
Info Catalog
(mysql.info) replication-upgrade
(mysql.info) replication
(mysql.info) replication-options
automatically generated byinfo2html