Skip navigation links

Additional languages
MySQL 5.1 Reference Manual :: 20 MySQL Cluster :: 20.11 MySQL Cluster Replication :: 20.11.3 Known Issues in MySQL Cluster Replication
20.11.4 Cluster Replication Schema and Tables »
Section Navigation      [Toggle]

20.11.3. Known Issues in MySQL Cluster Replication

The following are known problems or issues when using replication with MySQL Cluster in MySQL 5.1:

  • Loss of master-slave connection.  Prior to MySQL 5.1.18, a MySQL Cluster replication slave mysqld had no way of detecting that the connection from the master had been interrupted (due to, for instance, the master going down or a network failure). For this reason, it was possible for the slave to become inconsistent with the master.

    Beginning with MySQL 5.1.18, the master issues a “gap” event when connecting to the cluster. When the slave encounters a gap in the replication log, it stops with an error message. This message is available in the output of SHOW SLAVE STATUS, and indicates that the SQL thread has stopped due to an incident registered in the replication stream, and that manual intervention is required. In order to restart the slave, it is necessary to issue the following commands: 

    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

    The slave then resumes reading the master binlog from the point where the gap was recorded.

    Important

    If high availability is a requirement for the slave server or cluster, then it is still advisable to set up multiple replication lines, to monitor the master mysqld on the primary replication line, and to fail over to a secondary line if and as necessary. For information about implementing this type of setup, see Section 20.11.7, “Using Two Replication Channels”, and Section 20.11.8, “Implementing Failover with MySQL Cluster”.

    However, if you are replicating from a standalone MySQL server to a MySQL Cluster, one channel is usually sufficient.

  • Multi-byte character sets.  There are several known issues with regard to the use of multi-byte characters sets with MySQL Cluster Replication. See Bug#27404 (fixed in MySQL 5.1.21), Bug#29562, Bug#29563, and Bug#29564 for more information.

  • Circular replication.  Prior to MySQL 5.1.18, circular replication was not supported with MySQL Cluster replication, due to the fact that all log events created in a particular MySQL Cluster were wrongly tagged with the server ID of the MySQL server used as master and not with the server ID of the originating server.

    Beginning with MySQL 5.1.18, this limitation is lifted, as discussed in the next few paragraphs, in which we consider the example of a replication setup involving three MySQL Clusters numbered 1, 2, and 3, in which Cluster 1 acts as the replication master for Cluster 2, Cluster 2 acts as the master for Cluster 3, and Cluster 3 acts as the master for Cluster 1. Each cluster has two SQL nodes, with SQL nodes A and B belonging to Cluster 1, SQL nodes C and D belonging to Cluster 2, and SQL nodes E and F belonging to Cluster 3.

    Circular replication using these clusters is supported as long as:

    • the SQL nodes on all masters and slaves are the same

    • All SQL nodes acting as replication masters and slaves are started using the --log-slave-updates option

    This type of circular replication setup is shown in the following diagram:

    Cluster circular replication scheme in which all master SQL nodes are also slaves.

    In this scenario, SQL node A in Cluster 1 replicates to SQL node C in Cluster 2; SQL node C replicates to SQL node E in Cluster 3; SQL node E replicates to SQL node A. In other words, the replication line (indicated by the red arrows in the diagram) directly connects all SQL nodes used as replication masters and slaves.

    It should also be possible to set up circular replication in which not all master SQL nodes are also slaves, as shown here:

    Cluster circular replication scheme in which all master SQL nodes are not also necessarily slaves.

    In this case, different SQL nodes in each cluster are used as replication masters and slaves. However, you must not start any of the SQL nodes using --log-slave-updates (see the description of this option for more information). This type of circular replication scheme for MySQL Cluster, in which the line of replication (again indicated by the red arrows in the diagram) is discontinuous, should be possible, but it should be noted that it has not yet been thoroughly tested and must therefore still be considered experimental.

    Important

    Beginning with MySQL 5.1.24, you should execute the following statement before starting circular replication: 

    mysql> SET GLOBAL SLAVE_EXEC_MODE = 'IDEMPOTENT';

    This is necessary to suppress duplicate-key and other errors that otherwise break circular replication of MySQL Cluster. IDEMPOTENT mode is also required for multi-master replication when using MySQL Cluster. (Bug#31609)

    See Slave_exec_mode, for more information.

  • DDL statements.  The use of data definition statements, such as CREATE TABLE, DROP TABLE, and ALTER TABLE, are recorded in the binary log for only the MySQL server on which they are issued.

  • Cluster replication and primary keys.  In MySQL 5.1.6, only those NDB tables having explicit primary keys could be replicated. This limitation was lifted in MySQL 5.1.7. However, in the event of a node failure, errors in replication of NDB tables without primary keys can still occur, due to the possibility of duplicate rows being inserted in such cases. For this reason, it is highly recommended that all NDB tables being replicated have primary keys.

  • Restarting with --initial.  Restarting the cluster with the --initial option causes the sequence of GCI and epoch numbers to start over from 0. (This is generally true of MySQL Cluster and not limited to replication scenarios involving Cluster.) The MySQL servers involved in replication should in this case be restarted. After this, you should use the RESET MASTER and RESET SLAVE statements to clear the invalid ndb_binlog_index and ndb_apply_status tables. respectively.

  • auto_increment_offset and auto_increment_increment variables.  The use of the auto_increment_offset and auto_increment_increment server system variables is supported beginning with MySQL 5.1.20. Previously, these produced unpredictable results when used with NDB tables or MySQL Cluster replication.

  • Replication from NDBCLUSTER to other storage engines.  If you attempt to replicate from a MySQL Cluster to a slave that uses a storage engine that does not handle its own binary logging, the replication process aborts with the error Binary logging not possible ... Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging (Error 1595). It is possible to work around this issue in one of the following ways:

    • Turn off binary logging on the slave.  This can be accomplished by setting SQL_LOG_BIN = 0.

    • Change the storage engine used for the mysql.ndb_apply_status table.  Causing this table to use an engine that does not handle its own binary logging can also eliminate the conflict. This can be done by issuing a statement such as ALTER TABLE mysql.ndb_apply_status ENGINE=MyISAM on the slave. It is safe to do this when using a non-NDB storage engine on the slave, since you do not then need to worry about keeping multiple slave SQL nodes synchronized.

    • Filter out changes to the mysql.ndb_apply_status table on the slave.  This can be done by starting the slave SQL node with the option --replicate-ignore-table=mysql.ndb_apply_status. If you need for other tables to be ignored by replication, you might wish to use an appropriate --replicate-wild-ignore-table option instead.

    Important

    You should not disable replication or binary logging of mysql.ndb_apply_status or change the storage engine used for this table when replicating from one MySQL Cluster to another. See Replication and binary log filtering rules with replication between MySQL Clusters elsewhere in this section for details.

  • Replication and binary log filtering rules with replication between MySQL Clusters.  If you are using any of the options --replicate-do-*, --replicate-ignore-*, --binlog-do-db, or --binlog-ignore-db to filter databases or tables being replicated, care must be taken not to block replication or binary logging of the mysql.ndb_apply_status, which is required for replication between MySQL Clusters to operate properly. In particular, you must keep in mind the following:

    1. Using --replicate-do-db=db_name (and no other --replicate-do-* or --replicate-ignore-* options) means that only tables in database db_name are replicated. In this case, you should also use --replicate-do-db=mysql, --binlog-do-db=mysql, or --replicate-do-table=mysql.ndb_apply_status to insure that mysql.ndb_apply_status is populated on slaves.

      Using --binlog-do-db=db_name (and no other --binlog-do-db options) means that changes only to tables in database db_name are written to the binary log. In this case, you should also use --replicate-do-db=mysql, --binlog-do-db=mysql, or --replicate-do-table=mysql.ndb_apply_status to insure that mysql.ndb_apply_status is populated on slaves.

    2. Using --replicate-ignore-db=mysql means that no tables in the mysql database are replicated. In this case, you should also use --replicate-do-table=mysql.ndb_apply_status to insure that mysql.ndb_apply_status is replicated.

      Using --binlog-ignore-db=mysql means that no changes to tables in the mysql database are written to the binary log. In this case, you should also use --replicate-do-table=mysql.ndb_apply_status to insure that mysql.ndb_apply_status is replicated.

    You should also remember that:

    1. Each replication filtering rule requires its own --replicate-do-* or --replicate-ignore-* option, and that multiple rules cannot be expressed in a single replication filtering option. For information about these rules, see Section 19.1.3, “Replication Options and Variables”.

    2. Each binary log filtering rule requires its own --binlog-do-db or --binlog-ignore-db option, and that multiple rules cannot be expressed in a single binary log filtering option. For information about these rules, see Section 5.2.4, “The Binary Log”.

    Note

    If you are replicating a MySQL Cluster to a slave that uses a storage engine other than NDBCLUSTER, the considerations just given previously may not apply. See Replication from NDBCLUSTER to other storage engines elsewhere in this this section for details.