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 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 using 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. This problem can also occur in at least two other cases, listed here:
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.
When both the master and the slave use a nontransactional
engine, but use different binary logging formats (for
example, when the master has
binlog_format=ROW and the
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,
CREATE TABLE ... SELECT is not supported
when using GTID-based replication.
TABLE statements are not supported inside
transactions when using GTIDs (that is, when the server was
started with the
option). It is possible to use use these statements with GTIDs
enabled, but only outside of any transaction, and only with
Preventing execution of unsupported statements.
In order to prevent execution of statements that would cause
GTID-based replication to fail, all servers must be started
option when enabling GTIDs. This causes statements of any of
the types discussed previously in this section to fail with an
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
GTID mode and mysqldump. In MySQL 5.6.9 and later, 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.
GTID mode and mysql_upgrade.
Prior to MySQL 5.6.7, mysql_upgrade could
not connect to a MySQL Server that was running with
mysql_upgrade was run with
(Otherwise, mysqld had to be restarted with
--gtid-mode=OFF before running
mysql_upgrade, then restarted with
--gtid_mode=ON afterwards.) This is not an
issue in MySQL 5.6.7 and later, where
mysql_upgrade runs with
--write-binlog=OFF by default. (Bug
#13833710) However, it is not recommended to do so, since
mysql_upgrade can make changes to system
tables that use the
storage engine, which is nontransactional.