My Thoughts About MySQL 5.6

If you are reading this blog post, you are probably not at MySQL Connect. You may have heard about today’s new release – MySQL 5.6.7. This is a release candidate quality release, and if Oracle treats MySQL like the rest of its software, that means that there will very likely be a 5.6 GA by the end of 2012.

That all being said, is MySQL 5.6 worth upgrading to, once it’s GA? Probably the most compelling reason to upgrade is InnoDB online DDL – including online add/drop indexes (including foreign keys) and online add/drop/rename columns.

There are some great InnoDB performance enhancements, which you can read about if you are inclined to look further into it. Those are interesting, but it’s hard to say how much improvement any one organization will get until they actually test their system. So I won’t go into it too much until I have had time to see if Mozilla would benefit from it. Similarly, the fact that MySQL can now support parallel threading up to 48 cores is also great – Oracle tested on a 96-core server and got 48 cores working in parallel.

One of the most commonly used SQL extensions has gotten lots of new features added – EXPLAIN. In MySQL 5.6 you can now use EXPLAIN on SELECT, UPDATE and DELETE queries. There is also a visual EXPLAIN output and the output can be stored in JSON format. Here is a simple example of the new syntax and format:

mysql> EXPLAIN FORMAT=JSON DELETE FROM dup_index WHERE id=1\G
*************************** 1. row ***************************
EXPLAIN: {
   "query_block": {
     "select_id": 1,
     "table": {
       "delete": true,
       "table_name": "dup_index",
       "access_type": "range",
       "possible_keys": [
         "id",
         "id_2"
       ],
       "key": "id",
       "key_length": "5",
       "rows": 1,
       "filtered": 100,
       "attached_condition": "(`version`.`dup_index`.`id` = 1)"
     }
   }
}
1 row in set (0.00 sec)

Personally, I am pretty excited about the new security features of MySQL 5.6. The biggest one, which is a pretty big change to watch out for when upgrading, is that secure_auth defaults to on, unless you specify skip-secure-auth in the configuration. This means that when you upgrade, any user in the old password format (the password hash is 16 characters) will be blocked.

Other security features have to do with passwords – in MySQL 5.6 you can force a user to do a password change the next time they login (great for first-time logins, and no other commands will work until the password is changed), you can set a password expiration, and you can set a password strength that has to be met.

MySQL will also warn you when you set a replication password without using SSL, or when it is stored in cleartext. For example, the normal setting of replication’s username and password will generate the following 2 notes:

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2 rows in set (0.00 sec)

In MySQL 5.6, you can now store replication information in a table, not just in master.info.

I am also excited about having checksums in replication. Using pt-table-checksum can get tedious, and it only finds inconsistencies after the fact, it doesn’t prevent the inconsistencies or give an error exactly when the inconsistency occurs.

Another really nice replication change is that you can control row-based binary logging so it only logs a change in a row, not the entire changed row itself. This reduces overhead in row-based replication by a lot.

There are some nice little touches that show that Oracle is going in the right direction with MySQL – for example, in MySQL 5.6, innodb_file_per_table is enabled by default. And there is a new feature that warns you with a “note” if you create a duplicate index:

mysql> ALTER TABLE dup_index ADD INDEX(id);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1831
Message: Duplicate index 'id_2' defined on the table 'version.dup_index'. This is deprecated and will be disallowed in a future release.
1 row in set (0.00 sec)

This note only appears if you make an index with the same fields as another index; if you create an index that’s a prefix subset of another index, there is no warning (e.g. if you have an index on (a,b) and create an index on (a), there is no warning). Still, it is a good step in the right direction.

By default, sql_mode is no longer blank:
mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
Value: NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

If you use statements like UPDATE...LIMIT x and fill up your error logs with messages that the transaction is “unsafe”. There is now a warning suppression system, so that after 50 warnings in 50 seconds, the warnings will be aggregated with X warnings in Y seconds.

Other neat features I think I will make use of are:
– sync_binlog is less resource-intensive
– transportable tablespaces
– being able to specify locations for .ibd files
– multiple InnoDB buffer pools

All in all, MySQL 5.6 is a release to look forward to. I have not covered every change in MySQL 5.6, but the major ones that I am looking forward to. Others may have different priorities and reasons for wanting to move to MySQL 5.6. You can see the full MySQL 5.6.7 changelog, or read about the major changes in MySQL 5.6.