Thursday, January 30, 2014

Migration of MTR suites to use Innodb

In MySQL 5.7.2 a new include file “–source include/“ made its appearance in most of the .test files in the MTR suite. If you were wondering about this read on.
I will explain this change in two blogs. The first will describe why we are doing this and the next will explain how it is being done.

In order to set the context let me delve a bit into history. Starting from MySQL 5.5, the default storage engine for new tables is InnoDB. This change was coupled with a reverse switch in mysql-test-run, which made MyISAM the default storage engine for the server started through MTR(mysql-test-run). As a result default storage engine in the server was innodb, but most tests were run with the old default MyISAM.

Let me explain why such a switch was required. The usual practice in MTR test development was to not specify an engine in create table statement, unless the test is specifically for a non-default engine like merge, archive, innodb etc. As a result tables in most tests were created with the default engine which is MyISAM. From a test coverage perspective this was a perfectly sound strategy. Tests for engine specific properties were run with the appropriate engines and tests for engine independent properties were run with the default engine, MyISAM.  However this strategy became a problem when the default engine was changed. Tables in tests that do not specify any engine started getting created with the new default innodb leading to result differences and in turn test failures. There were hundreds of failing tests and fixing all this was an enormous task.

To overcome this situation following 2 options were considered:
  • Option 1 : Migrate all tests to run with innodb engine. This would involve analyzing all test failures and modifying the test or result files as required.
  • Option 2: Switch the default storage engine in MTR so that there is no need to change the test or result files.
Option 1 would have been the ideal, but it became quickly obvious that this will be a time and resource intensive solution. On the other hand option 2 was easy, but risky. A careful evaluation of the test suite showed that the risk with option 2 is not very high. The tests that did not have any engine specified were either tests for MyISAM only features or tests that were not considered to be sensitive to the engine. For everything else there were engine specific variants of the test. For example the partitioning test has 6 flavors – partition_archive, partition_federated, partition_csv, partition_innodb, partition_blackhole and partition_myisam . So the risk with option 2 was not very significant and was acceptable at least in the short term.

After evaluating the pros and cons of both options  it was decided to go with option 2 for 5.5 release and to implement option 1 in a future release. MySQL 5.5 and 5.6 were released using MyISAM as the default in MTR suite. In 5.7 we have started implementing option 2 and this is is the reason for the

Did I confuse you? I said migrate to innodb and the inc file says force_myisam_default. Well, if you are confused wait for my next blog :)