Migration of MTR suites to use InnoDB (continued …)

This is a continuation of my post on migration-of-mtr-suites-to-use-innodb . To set the context here is a quick recap.
MySQL 5.5 had the following changes with respect to the default engine

• Default engine in the server changed from MyISAM to InnoDB
• MTR modified to start server with the old default MyISAM. (This was required because historically most test results were recorded with MyISAM and many of them would fail if tests were run with the new server default,  InnoDB)
• Tests retained as is in 5.5 and planned to migrate them to run with default engine in a future release

In MySQL 5.7 release we have started the migration project. Right in the beginning we realized that the switch of default engine had an unexpected side effect. MTR tests that were developed post 5.5 were also getting recorded and run with MyISAM unless an engine was explicitly specified in the test.  I.e. we were in a state where not only old tests, but also new tests were running with the old default engine MyISAM. As a result the backlog of tests that had to be migrated was growing over time. We decided to fix the problem with new tests first and old tests later.

The only way we could force new tests to run with InnoDB by default was to remove the engine switch in MTR. This however would bring back the problem of existing tests failing with result difference. We decided to tackle this problem with a different approach. Instead of switching the engine in the test tool itself, it was switched in the existing tests. This is where the force_myisam_default.inc makes its entry. If this file is included it will tell MTR to start server with MyISAM. It was added to all existing tests that did not have any explicit engine specification at the test level. To be more precise it was added to tests that did not have have_innodb.inc, have_archive.inc etc. We then removed the switch in MTR, so that by default it starts the server with its default engine which is InnoDB. With these 2 changes we got to the state where new tests by default were running with InnoDB.This was a big step forward and on hindsight I think we should have done this instead of the engine switch in 5.5 .

Having fixed the problem with new tests, we have now shifted our focus to migration of old tests. The migration project does not in any way reduce the test coverage on MyISAM; rather it just adds InnoDB coverage where it was missing. Post migration tests for features which are dependent on the engine will have MyISAM and InnoDB variants. Tests for features that are expected to have same behavior on all engines may run only with the default engine InnoDB.
We have outlined following strategy for the migration project:

• Maintain MyISAM and innodb variants for tests and subtests that are dependent on the engine
• Remove MyISAM dependencies from tests that are not meant for testing MyISAM features
• Retain tests for MyISAM only features in MyISAM

There is an element of risk in this project since any small mistake can lead to missing or even worse wrong tests. However be rest assured that this is being handled with utmost care by our strong QA team. To ensure correctness and stability all result differences are analyzed and discussed with the concerned people before making any changes.  Some of the differences were due to bugs and these are fixed before test migration. Some others are expected and are accepted as they are. Listed below are a few examples of the changes made to the result files:

• Engine name in SHOW CREATE TABLE
• Adding “sort by” to SELECT queries
• Adding “analyze table” statement before EXPLAIN to get consistent test output
• Minor differences to EXPLAIN output

As of 5.7.3 release migration is complete for auth_sec, federated, funcs_1, funcs_2, jp, opt_trace, perfschema, stress and sys_vars suites. It is not all done yet and you will see more and more tests getting migrated in the upcoming milestone releases.

If you have any questions or feedback regarding this project please post them here. I would love to hear what the community thinks about this.