( Replication Features

Info Catalog ( Replication Upgrade ( Replication ( 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 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
      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
    * The slave can connect to the master using SSL if both are 4.1.1 or
    * 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.
      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
        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
    * 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
    * `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 ( Replication Upgrade ( Replication ( Replication Options
automatically generated byinfo2html