Skip navigation links
MySQL Cluster :: 6 MySQL Cluster Replication :: 6.11 MySQL Cluster Replication Conflict Resolution
7 MySQL 5.1 FAQ — MySQL Cluster »
Section Navigation      [Toggle]

6.11. MySQL Cluster Replication Conflict Resolution

When using a replication setup involving multiple masters (including circular replication), it is possible that different masters may try to update the same row on the slave with different data. Conflict resolution in MySQL Cluster Replication provides a means of resolving such conflicts by allowing a user defined resolution column to be used to determine whether or not an update to the row on a given master should be applied on the slave. (This column is sometimes referred to as a “timestamp” column, even though this column' type cannot be TIMESTAMP, as explained later in this section.) Different methods can be used to compare resolution column values on the slave when conflicts occur, as explained later in this section; the method used can be set on a per-table basis.

Important

Conflict resolution as described in this section is always applied on a row-by-row basis rather than a transactional basis. In addition, it is the application's responsibility to ensure that the resolution column is correctly populated with relevant values, so that the resolution function can make the appropriate choice when determining whether to apply an update.

Requirements.  Preparations for conflict resolution must be made on both the master and the slave:

  • On the master writing the binlogs, you must determine which columns are sent (all columns or only those that have been updated). This is done for the MySQL Server as a whole by applying the mysqld startup option -–ndb-log-updated-only (described later in this section) or on a per-table basis by entries in the mysql.ndb_replication table.

    Note

    If you are replicating tables with very large columns (such as TEXT or BLOB columns), -–ndb-log-updated-only can also be useful for reducing the size of the master and slave binary logs and avoiding possible replication failures due to exceeding max_allowed_packet.

    See Replication and max_allowed_packet, for more information about this issue.

  • On the slave, you must determine which type of conflict resolution to apply (“latest timestamp wins”, “same timestamp wins”, or none). This is done using the mysql.ndb_replication system table, on a per-table basis.

If only some but not all columns are sent, then the master and slave can diverge.

Note

We refer to the column used for determining updates as a “timestamp” column, but the data type of this column is never TIMESTAMP; rather, its data type should be INT (INTEGER) or BIGINT. This column should be UNSIGNED and NOT NULL.

Master column control.  We can see update operations in terms of “before” and “after” images — that is, the states of the table before and after the update is applied. Normally, when updating a table with a primary key, the “before” image is not of great interest; however, when we need to determine on a per-update basis whether or not to use the updated values on a replication slave, we need to make sure that both images are written to the master's binary log. This is done with the --ndb-log-update-as-write option for mysqld, as described later in this section.

Important

Whether logging of complete rows or of updated columns only is done is decided when the MySQL server is started, and cannot be changed online; you must either restart mysqld, or start a new mysqld instance with different logging options.

Logging full or partial rows (--ndb-log-updated-only option)

Version Introduced 5.1.19-ndb-6.3.0
Command-Line Format --ndb-log-updated-only
Config-File Format ndb_log_updated_only
Variable Name ndb_log_updated_only
Variable Scope Global
Dynamic Variable Yes
  Permitted Values
Type boolean
Default ON

For purposes of conflict resolution, there are two basic methods of logging rows, as determined by the setting of the --ndb-log-updated-only option for mysqld:

  • Log complete rows

  • Log only column data that has been updated — that is, column data whose value has been set, regardless of whether or not this value was actually changed. This is the default behavior.

It is more efficient to log updated columns only; however, if you need to log full rows, you can do so by setting --ndb-log-updated-only to 0 or OFF.

Logging changed data as updates (--ndb-log-update-as-write option)

Version Introduced 5.1.22-ndb-6.2.5
Command-Line Format --ndb-log-update-as-write
Config-File Format ndb-log-update-as-write
Variable Name ndb_log_update_as_write
Variable Scope Global
Dynamic Variable Yes
  Permitted Values
Type boolean
Default ON

Either of these logging methods can be configured to be done with or without the “before” image as determined by the setting of another MySQL Server option --ndb-log-update-as-write. Because conflict resolution is done in the MySQL Server's update handler, it is necessary to control logging on the master such that updates are updates and not writes; that is, such that updates are treated as changes in existing rows rather than the writing of new rows (even though these replace existing rows). This option is turned on by default; to turn it off, start the server with --ndb-log-update-as-write=0 or --ndb-log-update-as-write=OFF.

Conflict resolution control.  Conflict resolution is usually enabled on the server where conflicts can occur. Like logging method selection, it is enabled by entries in the mysql.ndb_replication table.

The ndb_replication system table.  To enable conflict resolution, it is necessary to create an ndb_replication table in the mysql system database on the master, the slave, or both, depending on the conflict resolution type and method to be employed. This table is used to control logging and conflict resolution functions on a per-table basis, and has one row per table involved in replication. ndb_replication is created and filled with control information on the server where the conflict is to be resolved. In a simple master-slave setup where data can also be changed locally on the slave this will typically be the slave. In a more complex master-master (2-way) replication schema this will usually be all of the masters involved. Each row in mysql.ndb_replication corresponds to a table being replicated, and specifies how to log and resolve conflicts (that is, which conflict resolution function, if any, to use) for that table. The definition of the mysql.ndb_replication table is shown here: 

CREATE TABLE mysql.ndb_replication  (
    db VARBINARY(63),
    table_name VARBINARY(63),
    server_id INT UNSIGNED,
    binlog_type INT UNSIGNED,
    conflict_fn VARBINARY(128),
    PRIMARY KEY USING HASH (db, table_name, server_id)
)   ENGINE=NDB
PARTITION BY KEY(db,table_name);

The columns in this table are described in the following list:

  • db.  The name of the database containing the table to be replicated.

  • table_name.  The name of the table to be replicated.

  • server_id.  The unique server ID of the MySQL instance (SQL node) where the table resides.

  • binlog_type.  The type of binary logging to be employed. This is determined as shown in the following table:

    Value Internal Value Description
    0 NBT_DEFAULT Use server default
    1 NBT_NO_LOGGING Do not log this table in the binary log
    2 NBT_UPDATED_ONLY Only updated attributes are logged
    3 NBT_FULL Log full row, even if not updated (MySQL server default behavior)
    4 NBT_USE_UPDATE (For generating NBT_UPDATED_ONLY_USE_UPDATE and NBT_FULL_USE_UPDATE values only — not intended for separate use)
    5 [Not used] ---
    6 NBT_UPDATED_ONLY_USE_UPDATE (equal to NBT_UPDATED_ONLY | NBT_USE_UPDATE) Use updated attributes, even if values are unchanged
    7 NBT_FULL_USE_UPDATE (equal to NBT_FULL | NBT_USE_UPDATE) Use full row, even if values are unchanged

  • conflict_fn.  The conflict resolution function to be applied. This function must be specified as one of the following:

    • NDB$OLD(column_name).  If the value of column_name is the same on both the master and the slave, then the update is applied; otherwise, the update is not applied on the slave and an exception is written to the log. This is illustrated by the following pseudocode: 

      if (master_old_column_value == slave_current_column_value)
  perform_update();
else
  log_exception();

      This function can be used for “same value wins” conflict resolution. This type of conflict resolution ensures that updates are not applied on the slave from the wrong master.

      Important

      The column value from the master's “before” image is used by this function.

      This conflict resolution function is available beginning with MySQL Cluster NDB 6.3.4.

    • NDB$MAX(column_name).  If the “timestamp” column value for a given row coming from the master is higher than that on the slave, it is applied; otherwise it is not applied on the slave. This is illustrated by the following pseudocode: 

      if (master_new_column_value > slave_current_column_value)
  perform_update();

      This function can be used for “greatest timestamp wins” conflict resolution. This type of conflict resolution ensures that, in the event of a conflict, the version of the row that was most recently updated is the version that persists.

      Important

      The column value from the master's “after” image is used by this function.

      This conflict resolution function is available beginning with MySQL Cluster NDB 6.3.0.

    • NDB$MAX_DELETE_WIN(column_name).  This is a variation on NDB$MAX(). Due to the fact that no timestamp is available for a delete operation, a delete using NDB$MAX() is in fact processed as NDB$OLD. Howver, for some use cases, this is not optimal. For NDB$MAX_DELETE_WIN(), if the “timestamp” column value for a given row adding or updating an existing row coming from the master is higher than that on the slave, it is applied; however, delete operations are treated as always having the higher value. This is illustrated by the following pseudocode: 

      if ( (master_new_column_value > slave_current_column_value)
        ||
      operation.type == "delete")
  perform_update();

      This function can be used for “greatest timestamp, delete wins” conflict resolution. This type of conflict resolution ensures that, in the event of a conflict, the version of the row that was deleted or (otherwise) most recently updated is the version that persists.

      This conflict resolution function is available beginning with MySQL Cluster NDB 6.3.31 and MySQL Cluster NDB 7.0.11.

      Note

      As with NDB$MAX(), the column value from the master's “after” image is the value used by this function.

    • NULL.  Indicates that conflict resolution is not to be used for the corresponding table.

    .

Status information.  Beginning with MySQL Cluster NDB 6.3.3, a server status variable Ndb_conflict_fn_max provides a count of the number of times that a row was not applied on the current SQL node due to “greatest timestamp wins” conflict resolution since the last time that mysqld was started.

Beginning with MySQL Cluster NDB 6.3.4, the number of times that a row was not applied as the result of “same timestamp wins” conflict resolution on a given mysqld since the last time it was restarted is given by the global status variable Ndb_conflict_fn_old. In addition to incrementing Ndb_conflict_fn_old, the primary key of the row that was not used is inserted into an exceptions table, as explained later in this section.

Additional requirements for “Same timestamp wins” conflict resolution.  To use the NDB$OLD() conflict resolution function, it is also necessary to create an exceptions table corresponding to each NDB table for which this type of conflict resolution is to be employed. The name of this table is that of the table for which “same timestamp wins” conflict resolution is to be applied, with the string $EX appended. (For example, if the name of the original table is mytable, the name of the corresponding exception table name should be mytable$EX.) This table is created as follows: 

CREATE TABLE original_table$EX  (
    server_id INT UNSIGNED,
    master_server_id INT UNSIGNED,
    master_epoch BIGINT UNSIGNED,
    count INT UNSIGNED,
    original_table_pk_columns,
    [additional_columns,]
    PRIMARY KEY(server_id, master_server_id, master_epoch, count)
) ENGINE=NDB;

The first four columns are required. Following these columns, the columns making up the original table's primary key should be copied in the order in which they are used to define the primary key of the original table.

Note

The names of the first four columns and the columns matching the original table's primary key columns are not critical; however, we suggest for reasons of clarity and consistency, that you use the names shown here for the server_id, master_server_id, master_epoch, and count columns, and that you use the same names as in the original table for the columns matching those in the original table's primary key.

The data types for the columns duplicating the primary key columns of the original table should be the same as for (or larger than) the original columns.

Additional columns may optionally be defined following these columns, but not before any of them; any such extra columns cannot be NOT NULL. The exception table's primary key must be defined as shown. The exception table must use the NDB storage engine. An example of use for NDB$OLD() and an exception table is given later in this section.

Important

The mysql.ndb_replication table is read when a data table is set up for replication, so the row corresponding to a table to be replicated must be inserted into mysql.ndb_replication before the table to be replicated is created.

Examples.  The following examples assume that you have already a working MySQL Cluster replication setup, as described in Section 6.5, “Preparing the MySQL Cluster for Replication”, and Section 6.6, “Starting MySQL Cluster Replication (Single Replication Channel)”.

  • NDB$MAX() example.  Suppose you wish to enable “greatest timestamp wins” conflict resolution on table test.t1, using column mycol as the “timestamp”. This can be done using the following steps:

    1. Make sure that you have started the master mysqld with -–ndb-log-update-as-write=OFF.

    2. On the master, perform this INSERT statement: 

      INSERT INTO mysql.ndb_replication
    VALUES ('test', 't1', 0, NULL, 'NDB$MAX(mycol)');

      Inserting a 0 into the server_id indicates that all SQL nodes accessing this table should use conflict resolution. If you want to use conflict resolution on a specific mysqld only, use the actual server ID.

      Inserting NULL into the binlog_type column has the same effect as inserting 0 (NBT_DEFAULT); the server default is used.

    3. Create the test.t1 table: 

      CREATE TABLE test.t1 (
    columns
    mycol INT UNSIGNED,
    columns
) ENGINE=NDB;

      Now, when updates are done on this table, conflict resolution will be applied, and the version of the row having the greatest value for mycol will be written to the slave.

    Note

    Other binlog_type options — such as NBT_UPDATED_ONLY_USE_UPDATE should be used in order to control logging on the master via the ndb_replication table rather than by using command-line options.

  • NDB$OLD() example.  Suppose an NDB table such as the one defined here is being replicated, and you wish to enable “same timestamp wins” conflict resolution for updates to this table: 

    CREATE TABLE test.t2  (
    a INT UNSIGNED NOT NULL,
    b CHAR(25) NOT NULL,
    columns,
    mycol INT UNSIGNED NOT NULL,
    columns,
    PRIMARY KEY pk (a, b)
)   ENGINE=NDB;

    The following steps are required, in the order shown:

    1. First — and prior to creating test.t2 — you must insert a row into the mysql.ndb_replication table, as shown here: 

      INSERT INTO mysql.ndb_replication
    VALUES ('test', 't2', 0, NULL, 'NDB$OLD(mycol)');

      Possible values for the binlog_type column are shown earlier in this section. The value 'NDB$OLD(mycol)' should be inserted into the conflict_fn column.

    2. Create an appropriate exceptions table for test.t2. The table creation statement shown here includes all required columns; any additional columns must be declared following these columns, and before the definition of the table's primary key. 

      CREATE TABLE test.t2$EX  (
    server_id SMALLINT UNSIGNED,
    master_server_id INT UNSIGNED,
    master_epoch BIGINT UNSIGNED,
    count BIGINT UNSIGNED,
    a INT UNSIGNED NOT NULL,
    b CHAR(25) NOT NULL,
    [additional_columns,]
    PRIMARY KEY(server_id, master_server_id, master_epoch, count)
)   ENGINE=NDB;

    3. Create the table test.t2 as shown previously.

    These steps must be followed for every table for which you wish to perform conflict resolution using NDB$OLD(). For each such table, there must be a corresponding row in mysql.ndb_replication, and there must be an exceptions table in the same database as the table being replicated.