Coupon Code: santa14
Hurry up, offer expires soon!
Blog

Suddenly MyISAM became transaction-aware.

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

CREATE TABLE `tab1` (
`a` INT
) 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.

4 thoughts on “Suddenly MyISAM became transaction-aware.

  1. Pingback: Suddenly MyISAM became transaction-aware. | InsideMySQL

  2. I personally prefer the new behavior, as I don’t think mixing Innodb and MyISAM updates in the same Innodb transaction makes sense.

    Sure, those of us who have been working with MySQL for long enough know the difference, but I think most people coming from other database managers would expect your example to either work completely for all tables, or not at all.

    What if the update statement fails and the transaction is rolled back? Would having the value ’1′ inserted in tab1 make sense? Not to me. This is right up there with allowing invalid dates or doing implicit conversions.

  3. For SJA users….
    This issue occurs when the SJA job contains mixed engines. Therefore if your SJA job file has a mix of InnoDB and MyISAM tables, split the job into two separate SJA job files: one for the InnoDB tables and one for MyISAM tables. This should avoid the issue.

  4. Just for clarification: conditions where *it does not happen* are further:

    * Not with any [target] server version before 5.6. The [source] server version does not matter obviously (as we only read there).
    * Not with MySQL 5.6+ either, if GTIDs are not enabled.
    * Not with GTIDs in MariaDB – and not either if there is a mix of transactional and non-transactional tables in the job.

    Van’s workaround will work as long as we don’t have a more elegant solution. We are discussing to provide an option to split a sync job into more transactions (one per table, for instance). But I don’t really need how many people will need it.

    Feel free to comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>