Suddenly MyISAM became transaction-aware._MySQL

DROP DATABASE IF EXISTS `gtid_test`;
CREATE DATABASE `gtid_test`;
USE `gtid_test`;

CREATE TABLE `tab1` (
`a` INT DEFAULT NULL
) ENGINE=MYISAM ;

CREATE TABLE `tab2` (
`b` INT
) ENGINE=INNODB;

SET autocommit=0;

INSERT INTO `tab1` VALUES (1);
INSERT INTO `tab2` VALUES (1);

UPDATE `tab1` SET `a` = 5 WHERE `a` = 1;

– Error Code: 1785
— When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

This happens with MySQL 5.6 GTIDs (Global Transaction IDs) enabled and it is documented here:https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html: “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.”

To reproduce the above test case you need the options –log-bin, –log-slave-updates, –gtid_mode=ON and –enforce_gtid_consistency=ON

If you have some tool doing UPDATES to multiple tables in a single transaction and if you have schemas using a mix of InnoDB and MyISAM tables the tool will fail.

This is just a (one more!) reminder that upgrading to MySQL 5.6 is*a major thing to do*if you intend to use new features added. Be careful to test on a staging environment that all tools and scripts, that you need for your daily survival, still work as expected.

GTIDs in MariaDB 10.0 are not affected and I find the MariaDB GTID implementation superior. MyISAM always was ‘transaction-agnostic’. The storage engine as such still probably is (the new restriction must have been implemented in the server layer and not the storage engine layer). But Oracle managed (a very bad way IMO) to introduce a GTID implementation that kills a major MyISAM feature (its ‘transaction-agnosticity’) and introduced potential risks when using legacy tools and scripts.