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
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
(described later in this section) or on a per-table basis
by entries in the
If you are replicating tables with very large 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
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
table, on a per-table basis.
If only some but not all columns are sent, then the master and slave can diverge.
We refer to the column used for determining updates as a
“timestamp” column, but the data type of this
column is never
rather, its data type should be
BIGINT. This column should be
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
for mysqld, as described later in this
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
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
Logging changed data as updates
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
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
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
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:
The name of the database containing the table to be
The name of the table to be replicated.
The unique server ID of the MySQL instance (SQL node)
where the table resides.
The type of binary logging to be employed. This is
determined as shown in the following table:
||Use server default|
||Do not log this table in the binary log|
||Only updated attributes are logged|
||Log full row, even if not updated (MySQL server default behavior)|
||Use updated attributes, even if values are unchanged|
||Use full row, even if values are unchanged|
The conflict resolution function to be applied. This
function must be specified as one of the following:
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
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.
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.
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:
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.
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.
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
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
if ( (
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.
NDB$MAX(), the column
value from the master's
“after” image is the value used by
Indicates that conflict resolution is not to be
used for the corresponding table.
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
Ndb_conflict_fn_old, the primary
key of the row that was not used is inserted into an
exceptions table, as explained later in
Additional requirements for “Same timestamp wins” conflict
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
the name of the corresponding exception table name should be
mytable$EX.) This table is created as
original_table$EX ( server_id INT UNSIGNED, master_server_id INT UNSIGNED, master_epoch BIGINT UNSIGNED, count INT UNSIGNED,
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.
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
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
NOT NULL. The exception
table's primary key must be defined as shown. The exception
table must use the
engine. An example of use for
an exception table is given later in this section.
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
before the table to be replicated is
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)”.
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
Make sure that you have started the master
On the master, perform this
INSERT INTO mysql.ndb_replication VALUES ('test', 't1', 0, NULL, 'NDB$MAX(mycol)');
Inserting a 0 into the
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
NULL into the
binlog_type column has the same
effect as inserting 0
NBT_DEFAULT); the server
default is used.
CREATE TABLE test.t1 (
columnsmycol INT UNSIGNED,
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
binlog_type options —
should be used in order to control logging on the master
ndb_replication table rather
than by using command-line options.
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:
First — and prior to
test.t2 — you
must insert a row into the
mysql.ndb_replication table, as
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
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;
Create the table
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