Because GTID-based replication is dependent on transactions, some features otherwise available in MySQL are not supported when using it. This section provides information about restrictions on and limitations of replication with GTIDs.
Updates involving nontransactional storage engines.
When using GTIDs, updates to tables using nontransactional
storage engines such as
cannot be made in the same statement or transaction as updates
to tables using transactional storage engines such as
This restriction is due to the fact that updates to tables that use a nontransactional storage engine mixed with updates to tables that use a transactional storage engine within the same transaction can result in multiple GTIDs being assigned to the same transaction.
Such problems can also occur when the master and the slave use different storage engines for their respective versions of the same table, where one storage engine is transactional and the other is not. Also be aware that triggers that are defined to operate on nontransactional tables can be the cause of these problems.
In any of the cases just mentioned, the one-to-one correspondence between transactions and GTIDs is broken, with the result that GTID-based replication cannot function correctly.
CREATE TABLE ... SELECT statements.
TABLE ... SELECT is not safe for statement-based
replication. When using row-based replication, this statement is
actually logged as two separate events—one for the
creation of the table, and another for the insertion of rows
from the source table into the new table just created. When this
statement is executed within a transaction, it is possible in
some cases for these two events to receive the same transaction
identifier, which means that the transaction containing the
inserts is skipped by the slave. Therefore,
TABLE ... SELECT is not supported when using
GTIDS and ALTER TABLE statements.
ALTER TABLE ... ADD, if the column has an
expression default value that uses a nondeterministic function,
the statement may produce a warning or error:
With mixed-based or row-based replication, an
ER_BINLOG_UNSAFE_SYSTEM_FUNCTIONerror occurs, regardless of GTID settings.
With statement-based replication, the statement result depends on GTID settings:
binlog_format is set to
TABLE statements cannot be used inside transactions,
procedures, functions, and triggers when GTIDs are in use on the
server (that is, when the
variable is set to
ON). They can be used
outside these contexts when GTIDs are in use, provided that
autocommit=1 is set. From MySQL
TABLE statements are allowed inside a transaction,
procedure, function, or trigger when GTIDs are in use. However,
the statements are not written to the binary log and are
therefore not replicated to slaves. If the removal of these
statements from a transaction results in an empty transaction,
the transaction is not written to the binary log. If a
transaction involving these statements is rolled back, a warning
message is issued stating that the creation or dropping of the
temporary tables could not be rolled back.
Preventing execution of unsupported statements.
To prevent execution of statements that would cause GTID-based
replication to fail, all servers must be started with the
when enabling GTIDs. This causes statements of any of the types
discussed previously in this section to fail with an error.
takes effect if binary logging takes place for a statement. If
binary logging is disabled on the server, or if statements are not
written to the binary log because they are removed by a filter,
GTID consistency is not checked or enforced for the statements
that are not logged.
For information about other required startup options when enabling GTIDs, see Section 126.96.36.199, “Setting Up Replication Using GTIDs”.
sql_slave_skip_counter is not
supported when using GTIDs. If you need to skip transactions,
use the value of the master's
gtid_executed variable instead;
see Injecting empty transactions, for more
The IGNORE_SERVER_IDS option of the
MASTER TO statement is deprecated when using GTIDs,
because transactions that have already been applied are
automatically ignored. Before starting GTID-based replication,
check for and clear all ignored server ID lists that have
previously been set on the servers involved. The
which can be issued for individual channels, displays the list
of ignored server IDs if there is one. If there is no list, the
Replicate_Ignore_Server_Ids field is blank.
GTID mode and mysqldump. It is possible to import a dump made using mysqldump into a MySQL Server running with GTID mode enabled, provided that there are no GTIDs in the target server's binary log.