you're reading...

Founders BLOG

MySQL 5.6: an inside perspective

MySQL 5.6 through the eyes of a custom storage engine MySQL plugin

MySQL is famous for its pluggable storage engine architecture which allows a DBA or an application developer to choose the right engine for the task. An application uses MySQL API and is isolated from all of the low-level implementation details at the storage level. As an example, the Cloud Storage Engine (ClouSE) enables existing MySQL applications to use cloud storage such as Amazon S3 or Google Cloud Storage to store its data. The application doesn’t need to be changed or even redeployed: with ClouSE, remote cloud storage will look like a better (ultra-scalable, durable, always-on) alternative to the local storage.

As you may already know, ClouSE now supports MySQL 5.6 release series.  See this announcement for more detail. Let’s go through the set of changes that were required on the ClouSE side in order to keep up with core MySQL 5.6 changes.

We had to adapt our code to compile and work with MySQL 5.6 while keeping 100% compatibility with MySQL 5.5. As much as we could, we tried to fix the code in a way that would work with both release series, but there are cases where the code has to be conditionally compiled for each release series.

Here is the list of MySQL 5.6 breaking changes and our solutions, in no particular order.

key_parts member is removed from struct st_keys

In MySQL 5.6 struct st_keys (defined in sql/structs.h) doesn’t have the key_parts member.  Now it has user_defined_key_parts, actual_key_parts and a couple other xxx_key_parts members instead.  This change is apparently related to extended secondary keys model that the optimizer in 5.6 uses to do certain query optimizations for InnoDB.

The user_defined_key_parts member corresponds to the number of index columns that are specified by the user in the CREATE TABLE, ALTER TABLE, and etc. statement.  The actual_key_parts member also includes the number of PK columns that are implicitly present in InnoDB secondary indexes (thus user_defined_key_parts <= actual_key_parts).

To adapt ClouSE handlerton code for the new structure (in a way that would work both in MySQL 5.5 and 5.6), we replaced all key_parts usage with user_defined_key_parts and added the following code at the top of the handlerton source file:

#if MYSQL_VERSION_ID < 50610
#define user_defined_key_parts key_parts

(50610 is not necessarily the precise check, but we don’t support 5.6 versions prior to 5.6.10, so it’s precise enough for our purposes).

Unfortunately, MySQL 5.6 uses a hardcoded check for InnoDB when it decides to account for PK columns in the actual_key_parts member.  So for any other engine user_defined_key_parts is equal to actual_key_parts.  Thus even though ClouSE also has PK columns in secondary indexes (a typical design for B-tree-based access methods), it has no way of engaging the extended secondary keys logic in MySQL.

What’s worse is that the extended secondary keys are now used in optimizations that previously used different logic.  Because of that, some optimizations that work in MySQL 5.5 for all engines, only work for InnoDB in MySQL 5.6.  For more details about the regression, see this bug.  We hope that the MySQL dev team is able to fix it soon.

New *TIME*2 types

MySQL 5.6 has 3 new time data types: MYSQL_TYPE_TIMESTAMP2, MYSQL_TYPE_DATETIME2, and MYSQL_TYPE_TIME2 that represent the new time data types that are capable of storing fractional seconds.  The old time data types are still present and supported for backward compatibility, however new tables are created with the new time data types.

Adding support for the new type turned out to be fairly easy.  We added code like:

#if MYSQL_VERSION_ID >= 50610

to the switch statement where we map MySQL data type to ClouSE data types, and we treat the new time types as fixed-sized binary fields.

timestamp_field_type is removed from struct TABLE

In MySQL 5.6 struct TABLE (defined in sql/table.h) doesn’t have the timestamp_field_type member anymore.  TIMESTAMP_AUTO_SET_ON_INSERT, TIMESTAMP_AUTO_SET_ON_UPDATE etc. are gone as well.  This change is apparently related to improved automatic initialization and updating of TIMESTAMP and DATETIME.

Along with this change the code that handles automatic initialization and updating is apparently hoisted from storage engines to the core MySQL code, so the fix is trivial:

#if MYSQL_VERSION_ID < 50610
    if( table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT )

I.e. the code is just excluded from ClouSE handlerton when it’s compiled for MySQL 5.6.

MySQL 5.6 doesn’t load field values for duplicate key error on UPDATE

Consider the following example:

mysql> CREATE TABLE t1 (
          id    INT KEY,
          first CHAR(16) NOT NULL,
          last  CHAR(16) NOT NULL,
          UNIQUE KEY (first, last)
mysql> INSERT INTO t1 VALUES (1, 'foo', 'bar'), (2, 'abc', 'bar');
mysql> UPDATE t1 SET first='foo' WHERE id=2;

The result is a duplicate key error:

ERROR 1062 (23000): Duplicate entry 'foo-bar' for key 'first'

The UPDATE operation does not use the last field, so MySQL doesn’t read its values during normal processing.  Because of that, MySQL 5.5 has special logic that would explicitly load all field values of the duplicate key to show in the error message.

In MySQL 5.6 the logic is removed, so the error message in the example above would look like:

ERROR 1062 (23000): Duplicate entry 'foo-' for key 'first'

To fix this issue, we copied the code from MySQL 5.5 into the ClouSE handlerton.  The function that has this logic in MySQL 5.5 is prepare_record_for_error_message (located in sql/

In conclusion

Adding support for MySQL 5.6 release series while keeping compatibility with MySQL 5.5 release series turned out to be fairly straightforward.  We were able to adapt ClouSE handlerton and it works with MySQL 5.6 as well as or better than with MySQL 5.5.

If you have any questions or suggestions, please don’t hesitate to provide feedback in the comments or contact us directly.  Or just go ahead and download ClouSE or WorPress-to-Cloud (which uses ClouSE under the covers) to try it with MySQL 5.6.



  1. [...] Artem Livshits gives an inside perspective of MySQL 5.6 [...]

Post a Comment


Twitting ...