MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 MyISAM
cannot be made in the same statement or transaction as updates
to tables using transactional storage engines such as
InnoDB
.
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 source and the replica use different storage engines for their respective versions of the same table, where one storage engine is transactional and the other is not.
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.
CREATE
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 replica. Therefore,
CREATE TABLE ... SELECT
is not supported
when using GTID-based replication.
Temporary tables.
CREATE TEMPORARY
TABLE
and
DROP TEMPORARY
TABLE
statements are not supported inside
transactions when using GTIDs (that is, when the server was
started with the
--enforce-gtid-consistency
option). It is possible to use these statements with GTIDs
enabled, but only outside of any transaction, and only with
autocommit=1
.
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
with the
--enforce-gtid-consistency
option when enabling GTIDs. This causes statements of any of
the types discussed previously in this section to fail with an
error.
For information about other required startup options when enabling GTIDs, see Section 17.1.3.2, “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 source's
gtid_executed
variable instead;
see Injecting empty transactions, for more
information.
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.
Prior to MySQL 5.6.9, mysqldump did not record global transaction IDs, and it was necessary to use the binary log and mysqlbinlog to restore GTIDs. (Bug #14797808, Bug #14832472)
GTID mode and mysql_upgrade.
Prior to MySQL 5.6.7, mysql_upgrade could
not connect to a MySQL Server that was running with global
transaction identifiers (GTIDs) enabled
(gtid_mode=ON
) unless
mysql_upgrade was run with
--write-binlog=OFF
.
Otherwise, mysqld had to be restarted with
gtid_mode=OFF
before running
mysql_upgrade, then restarted with
gtid_mode=ON
afterwards. In
MySQL 5.6.7 and later, where mysql_upgrade
runs with --write-binlog=OFF
by default. (Bug
#13833710). Do not enable this option when the server is
running with (gtid_mode=ON
).