(mysql.info.gz) Replication Features
Info Catalog
(mysql.info.gz) Replication Upgrade
(mysql.info.gz) Replication
(mysql.info.gz) 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. For
example, the `GROUP_CONCAT()' function is available in MySQL 4.1 and
up. If you use this function on the master server, you cannot replicate
to a slave server that is older than MySQL 4.1.
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 InnoDB and MySQL
Replication.
* Replication will be done correctly with `AUTO_INCREMENT',
`LAST_INSERT_ID()', and `TIMESTAMP' values.
* The `USER()', `UUID()', and `LOAD_FILE()' functions are replicated
without changes and will thus not work reliably on the slave. This
is also true for `CONNECTION_ID()' in slave versions older than
4.1.1. The *new* `PASSWORD()' function in MySQL 4.1 is well
replicated in masters from 4.1.1 and up; your slaves also must be
4.1.1 or above to replicate it. If you have older slaves and need
to replicate `PASSWORD()' from your 4.1.x master, you must start
your master with the `--old-password' option, so that it uses the
old implementation of `PASSWORD()'. (Note that the `PASSWORD()'
implementation in MySQL 4.1.0 differs from every other version of
MySQL. It is best to avoid 4.1.0 in a replication situation.)
* The `FOREIGN_KEY_CHECKS' variable is replicated as of MySQL 4.0.14.
The `SQL_MODE', `UNIQUE_CHECKS', and `SQL_AUTO_IS_NULL' variables
are replicated as of 5.0.0. The `table_type' variables is not yet
replicated, which is a good thing for replication between
different storage engines.
* Replication between MySQL servers using different character sets
is discussed here. First, you must ALWAYS use the same *global*
character set and collation (`--default-character-set',
`--default-collation') on the master and the slave. Otherwise, you
may get duplicate-key errors on the slave, because a key that is
regarded as unique in the master's character set may not be unique
in the slave's character set. Second, if the master is strictly
older than MySQL 4.1.3, the character set of the session should
never be made different from its global value (in other words,
don't use `SET NAMES', `SET CHARACTER SET' etc) because this
character set change will not be known to the slave. If the master
is 4.1.3 or newer, and the slave too, the session can freely set
its local value of character set variables (`NAMES', `CHARACTER
SET', `COLLATION_CLIENT', `COLLATION_SERVER' etc) as these
settings will be written to the binary log and then known to the
slave. The session will however be prevented from changing the
*global* value of these; as said previously the master and slave
must always have identical global character set values. There also
is one last limitation: if on the master you have databases with
different character sets from the global `collation_server' value,
you should design your `CREATE TABLE' statements so that they
don't implicitly rely on the default database's character set,
because there currently is a bug (Bug #2326); a good workaround is
to explicitly state the character set and collation in a clause of
the `CREATE TABLE'.
* For both master and slave same system time zone should be set
(otherwise some statements, for example statements using `NOW()'
or `FROM_UNIXTIME()' functions, won't be replicated properly). One
could set time zone in which MySQL server runs by using
`--timezone=timezone_name' option of `mysqld_safe' script or by
setting `TZ' environment variable. Also starting from version
4.1.3 both master and slave should have same default connection
time zone set, i.e. `--default-time-zone' parameter should have
the same value for both master and slave.
* 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, you will have problems if the slave is
stopped in the middle of a `BEGIN/COMMIT' block, because the slave
will restart at the beginning of the `BEGIN' block. This issue is
on our TODO and will be fixed in the near future.
* Update statements that refer to user variables (that is, variables
of the form `@VAR_NAME') are badly replicated in 3.23 and 4.0.
This is fixed in 4.1. Note that user variable names are case
insensitive starting from MySQL 5.0. You should take this into
account when setting up replication between 5.0 and an older
version.
* The slave can connect to the master using SSL if both are 4.1.1 or
newer.
* If a `DATA DIRECTORY' or `INDEX DIRECTORY' clause is used in a
`CREATE TABLE' statement on the master server, the clause is also
used on the slave. This can cause problems if no corresponding
directory exists in the slave host filesystem or exists but is not
accessible to the slave server. Starting from MySQL 4.0.15, there
is a `sql_mode' option called `NO_DIR_IN_CREATE'. If the slave
server is run with its SQL mode set to include this option, it
will simply ignore the clauses before replicating the `CREATE
TABLE' statement. The result is that the `MyISAM' data and index
files are created in the table's database directory.
* Although we have never heard of it actually occurring, it is
theoretically possible for the data on the master and slave to
become different if a query is designed in such a way that the
data modification is non-deterministic; that is, left to the will
of the query optimizer. (That generally is not a good practice
anyway, even outside of replication!). For a detailed explanation
of this issue, see Open bugs.
* If on master a `LOAD DATA INFILE' is interrupted in the middle
(integrity constraint violation, killed connection...), the slave
will skip this `LOAD DATA INFILE' entirely. It means that if this
command permanently inserted/updated some table records before
being interrupted, these modifications won't be replicated to the
slave. This will be fixed when MySQL features a record-level
binary log format, in development.
* Before MySQL 4.1.1, `FLUSH', `ANALYZE TABLE', `OPTIMIZE TABLE',
and `REPAIR TABLE' statements are not written to the binary log
and thus are not replicated to the slaves. This is not normally a
problem because these statements do not modify table data. However,
it can cause difficulties under certain circumstances. If you
replicate the privilege tables in the `mysql' database and update
those tables directly without using the `GRANT' statement, you
must issue a `FLUSH PRIVILEGES' statement on your slaves to put
the new privileges into effect. Also if you use `FLUSH TABLES'
when renaming a `MyISAM' table that is part of a `MERGE' table,
you will have to issue `FLUSH TABLES' manually on the slaves. As
of MySQL 4.1.1, these statements are written to the binary log
(unless you specify `NO_WRITE_TO_BINLOG', or its alias `LOCAL').
Exceptions are that `FLUSH LOGS', `FLUSH MASTER', `FLUSH SLAVE',
and `FLUSH TABLES WITH READ LOCK' are not logged in any case.
(Any of them may cause problems if replicated to a slave.) For a
syntax example, see `FLUSH' FLUSH.
* MySQL only supports one master and many slaves. Later we will add
a voting algorithm to automatically change master if something goes
wrong with the current master. We will also introduce "agent"
processes to help do load balancing by sending `SELECT' queries to
different slaves.
* When a server shuts down and restarts, its `MEMORY' (`HEAP')
tables become empty. As of MySQL 4.0.18, the master replicates
this effect as follows: The first time that the master uses each
`MEMORY' table after startup, it notifies slaves that the table
needs to be emptied by writing a `DELETE FROM' statement for the
table to its binary log. See `MEMORY' storage engine
MEMORY storage engine. for more details.
* Temporary tables are replicated with the exception of the case
that you shut down the slave server (not just the slave threads)
and you have some replicated temporary tables that are used in
update statements that have not yet been executed on the slave.
If you shut down the slave server, the temporary tables needed by
those updates no longer are available when the slave starts again.
To avoid this problem, do not shut down the slave while it has
temporary tables open. Instead, use this 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
shut down the slave.
4. If the value is not 0, restart the slave threads with `START
SLAVE'.
5. Repeat the procedure later to see if you have better luck
next time.
We have plans to fix this problem in the near future.
* It is safe to connect servers in a circular master/slave
relationship with the `--log-slave-updates' option specified.
Note, however, that many statements will 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.
This means that you can create a setup such as this:
A -> B -> C -> A
Server IDs are encoded in the binary log events, so server A will
know when an event that it reads was originally created by itself
and will not execute the event (unless server A was started with
the `--replicate-same-server-id' option, which is meaningful only
in rare setups). Thus, there will be no infinite loop. But this
circular setup will work 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 the 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, fix the problem (for
example, a non-existent table), and then run `START SLAVE'.
* It is safe to shut down a master server and restart it later. If
a slave loses its connection to the master, the slave tries to
reconnect immediately. If that fails, the slave retries
periodically. (The default is to retry every 60 seconds. This may
be changed with the `--master-connect-retry' option.) The slave
will also be able to deal with network connectivity outages.
However, the slave will notice 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'. Server system variables.
* Shutting down the slave (cleanly) is also safe, as it keeps track
of where it left off. Unclean shutdowns might produce problems,
especially if disk cache was not flushed to disk before the system
went down. Your system fault tolerance will be 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. Binary log.
* 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 will exit
and wait for the database administrator to decide what to do about
it unless the error code is legitimate and the statement execution
results in the same error code. If this error code validation
behavior is not desirable, some or all errors can be masked out
(ignored) with the `--slave-skip-errors' option. This option is
available starting with MySQL 3.23.47.
* If you update transactional tables from non-transactional tables
inside a `BEGIN/COMMIT' segment, updates to the binary log may be
out of sync if some thread changes the non-transactional table
before the transaction commits. This is because the transaction
is written to the binary log only when it is committed.
* Before version 4.0.15, any update to a non-transactional 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 you use `ROLLBACK'. You must take this into account when
updating both transactional tables and non-transactional tables
within the same transaction. (This is true not only for
replication, but also if you are using binary logging for
backups.) In version 4.0.15, we changed the logging behavior for
transactions that mix updates to transactional and
non-transactional 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 `ROLLBACK'). The
problem that remains is when a second connection updates the
non-transactional table while the first connection's transaction
is not finished yet; wrong order can still occur, because the
second connection's update will be written immediately after it is
done.
* When a 4.x slave replicates a `LOAD DATA INFILE' from a 3.23
master, the values of the `Exec_Master_Log_Pos' and
`Relay_Log_Space' columns of `SHOW SLAVE STATUS' become incorrect.
The incorrectness of `Exec_Master_Log_Pos' will cause a problem
when you stop and restart replication; so it is a good idea to
correct the value before this, by doing `FLUSH LOGS' on the master.
These bugs are fixed in MySQL 5.0.0 slaves.
The following table lists replication problems in MySQL 3.23 that are
fixed in MySQL 4.0:
* `LOAD DATA INFILE' is handled properly, as long as the data file
still resides on the master server at the time of update
propagation.
* `LOAD DATA LOCAL INFILE' is no longer skipped on the slave as it
was in 3.23.
* In 3.23, `RAND()' in updates does not replicate properly. Use
`RAND(some_non_rand_expr)' if you are replicating updates with
`RAND()'. You can, for example, use `UNIX_TIMESTAMP()' as the
argument to `RAND()'.
Info Catalog
(mysql.info.gz) Replication Upgrade
(mysql.info.gz) Replication
(mysql.info.gz) Replication Options
automatically generated byinfo2html