Description:
The MySQL documentation makes the claim that single transactions including statements updating both transactional and non-transactional tables are prohibited:
https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html
However, within a single explicit transaction, the order of operations seems to matter:
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.14 sec)
mysql> CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE = MyISAM;
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE = InnoDB;
Query OK, 0 rows affected (0.52 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t2 VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.02 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (2);
ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-t
ransactional tables can only be done in either autocommitted statements or singl
e-statement transactions, and never in the same statement as updates to transact
ional tables.
This is quite possibly intentional, as the InnoDB transaction hasn't started when t1 is updated first, and MyISAM's non-transactional behavior means transactional context isn't tracked after execution of the UPDATE completes. If this is the case, the documentation needs to be clarified.
This seems that it has the potential to cause the GTID consistency problems described in the documentation, and if that's the case, it should be considered a code bug for resolution.
How to repeat:
See above.
Suggested fix:
* Fix documentation (if docs bug)
* Fix behavior (if a code bug)
If the latter, please note that this behavior, while not explicitly documented, may be considered desirable for certain use cases. Further restrictions may break implicit compatibility, and if needed, perhaps a server option would be appropriate to allow continued existing behavior.
