Skip to content

MySQL 5.6-RC: GTID vs. MyISAM

Posted by Kristian Köhntopp on
So we tested the 5.6.7-RC. And ran into a strange problem:

Because of a test, a preexisting configuration with GTID enabled existed, and suddenly we did not have properly initialized grants in mysql.* created for a new installation. Turns out: GTID and non-transactional tables are no friends, and that is even documented.

When using GTIDs, updates to tables using nontransactional storage engines such as MyISAM are not supported. This is because updates to such tables mixed with updates to tables that use a transactional storage engine such as InnoDB can result in multiple GTIDs being assigned to the same transaction.


Also, this is supposed to work with GRANT and REVOKE, but not with INSERT and DELETE. Now guess what mysql-install-db and friends are using?

server:~ # less /usr/share/mysql/mysql_system_tables_data.sql
...
INSERT INTO tmp_user VALUES ('localhost','root','',...);


This is a larger problem: We are supposed to use GRANT and REVOKE, but many people are using INSERT and DELETE in mysql.* all of the time, and so do many applications. And the mysql.* tables are MyISAM, and always have been (except that nowadays there is a wild mix of CSV and InnoDB tables in there as well).

MySQL cannot really ship GTID as a feature with MyISAM-tables in mysql.* and expect that to work anywhere. This is all very extremely broken and needs urgent fixing.

This is now support-case SR 3-6270525721: "MySQL 5.6.7-rc1, grants, replication and GTID cause problems" and will also soon have a bug number. And, no, fixing the mysql_system_tables_data.sql is not really removing the problem here.

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

Davi Arnaut on :

Out of curiosity, how would you expect Global _Transaction_ Identifier to work for a storage engine that does not transactions?

Justin Swanhart on :

I don't think he expects it to work with MyISAM. I think he thinks the system tables should be InnoDB and personally I agree. Since the default storage engine is innodb, no_engine_substitution is now the default, etc, the system tables should be innodb. this should have been a high priority for oracle, including getting rid of .frm madness

Kristian Köhntopp on :

I think I have to install a comment voting plugin just so that i can +1 what Justin just wrote.

Davi Arnaut on :

Sorry, it wasn't clear given that none of this is actually mentioned.

Davi Arnaut on :

> Now guess what mysql-install-db and friends are using?

It is worth noting that when mysql_install_db is to be used, mysqld is invoked with --skip-grant-tables. Hence, these tools use regular statements because the System Tables are not setup yet, and as a consequence, the privilege system is not working yet.

danny on :

You can turn it on with FLUSH PRIVILEGES once the tables exist.

Kristian Köhntopp on :

Loading of help tables also fails
http://bugs.mysql.com/bug.php?id=67073

Loading of time zone information is also bound to fail
# mysql_tzinfo_to_sql /usr/share/zoneinfo/ | head -10
...
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id);

[mysql]> show create table time_zone\G
Table: time_zone
Create Table: CREATE TABLE `time_zone` (
`Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
PRIMARY KEY (`Time_zone_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zones'
1 row in set (0.00 sec)


MyISAM in mysql.* has to go before GTID can be sucessfully deployed.

Davi Arnaut on :

> MyISAM in mysql.* has to go before GTID can be sucessfully deployed.

I think you meant to say before it can be successfully deployed in your environment as it depends on how one bootstraps/manages the database. Getting rid of MyISAM system tables is a much larger problem scope (and something that I believe is in Oracle's plans).

Kristian Köhntopp on :

It is quite a bit worse than that: Right now, the default RPM as distributed by Oracle will fail to install if it finds an otherwise valid /etc/my.cnf in place, because the installation process is subject to additional restrictions that normal operations have not.

Kristian Köhntopp on :

In fact, I am pretty certain that this also breaks mysql_upgrade or has the potential to do so.

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

BBCode format allowed