Goal
Problems to solve
... Varying result files
...... Solution
... Unsupported features
...... Solution
... Different primitives
...... Solution
... Filed bugs
Tuning
... Assumptions
... Common tuning steps
... Examples
...... MyISAM
...... InnoDB plugin
...... MERGE

Goal

The goal of this task is to create a set of tests which could be used for acceptance/conformance testing of a storage engine.
The suite it not supposed to provide exhaustive engine testing, and certainly cannot test non-standard engine-specific features (due to its very nature of being agnostic to the engine under test), but rather perform a relatively quick evaluation of standard functionality expected from a storage engine.

Problems to solve

Existing MTR tests are not very suitable for running on different storage engines.

Problem 1: Varying result files

Traditional MTR/mysqltest have very strict requirements in regard to test output. Since different storage engines are likely to produce a slightly different output (even if the difference is innocent), the tests start failing, which causes false positives.

Example:

OPTIMIZE TABLE t1;

# One engine might in certain situations say

Table   Op      Msg_type        Msg_text

test.t1 optimize        status  Table is already up to date

# Another always says

Table   Op      Msg_type        Msg_text

test.t1 optimize        status  OK

Neither is wrong, and yet if we have one variant in the result file, for another engine the test will fail.

Till recently the only solution was to copy the whole suite somewhere and recreate result files, thus introducing usually unwanted code duplication.

Solution

To solve this problem, we will use functionality developed in scope of MDEV-30.
With this task, the original test results can be adapted to the storage engine by creating diff files (basically, patches). These patches are stored in the engine folder rather than in the MTR suite folder, so no changes in the original test suite is performed.

Problem 2: Unsupported features

Most of tests use combinations of various features, even if not all of them are strictly necessary in scope of this exact test. It means that if an engine does not support at least one feature, either by design or because it has not been implemented yet, the whole test becomes inapplicable. For example, if a traditional MTR test for ALTER TABLE uses at least one key, and keys are not supported, the test will fail – not just produce a result mismatch which we could patch, but actually fail in the middle of execution, – so we will have to stop running it, and ALTER TABLE will not be tested at all; and the same will happen to any test which uses keys, even if it's used briefly for an unimportant part of the test.

This problem is even bigger than the first one, because not only does it currently require copying the test suite and maintaining a separate set of result files, but also modifying the test files themselves; and often modifications will be extensive enough to rule out even the possibility of future merges with the original test suite.

Solution

We will solve this problem by allowing all tests to run through intermediate failures to the end (unless the server crashes or a syntax error in the test itself occurs). Thus, the tests will also produce mismatches, and the engine maintainer will be able to decide whether the test is really inapplicable and needs to be disabled, or the partial failures are acceptable and can be stored in the result diff file.

Example:

We are testing that a table option is supported by the engine (merely is accepted and stored); lets say AUTO_INCREMENT. We create a table with the option and check that it is not lost; but we also want to see that it works with ALTER; so, we add an alter table statement where the option is modified.

Now, if we try to run such a test with FEDERATED engine, it will break on ALTER – not because the option is unsupported, but because ALTER is. In the storage engine test suite, the test will continue to the end and will produce a mismatch (because the ALTER statement will cause an error). Then, someone who tunes the suite for FEDERATED engine, can see the difference, decide that it's reasonable, since ALTER is not supposed to work, and create an rdiff file. Thus, we do not skip the test for the table option entirely, but simply limit the testing to functionality the engine supports.

The tests will try to be smart in their internal logic, but within reason. When a statement is likely to fail (e.g. a feature is often unsupported), the test will check the result of the statement and will produce a verbose error message, including versions why it could happen. Also, if a failure happened on table creation, or in some other key moment, so that a part of test becomes useless (if a table was not created, there is no point at trying to alter it, etc.), the test will ignore a part of the flow, and proceed to the next part. But it does not happen always, the checks are only performed when the probability of a failure is reasonably high.
Of course, if a part of the test is bypassed, it will also cause a mismatch, so the maintainer will be aware of that and will be able to check why it happens.

Additionally, some tests will check the value of the default index (see below notes about configuration), and if it is unset, certain tests will be completely skipped, because every part of them requires indexes (e.g. index.test).

For the functionality described in the ENGINES table (transactions, savepoints, XA), if a test requires one of the features and it is shown as unsupported in the table, the test will produce a warning. But unlike with the indexes, it will not be skipped, because we assume that for a new engine information in the ENGINES table might be inaccurate.

Problem 3: Different primitives

Some engines have specifics which makes even simplest hardcoded statements inapplicable. For example, whenever a test contains
CREATE TABLE t (i INT)
it will fail for CSV engine, because it does not allow NULL-able columns. Or, an engine might require certain table options, like a connection string for FEDERATED engine. Since there is, obviously, no connection string in generic tests, every table creation for FEDERATED will fail, and nothing will be tested. Currently, there is no way to work around that apart from copying the tests and modifying them manually.

In even more complicated scenario, in order to create a table properly, additional actions need to be performed; e.g. to create a functional MERGE table, we need to also create an underlying MyISAM table; and if we want to alter a MERGE table, we should alter the underlying table(s) accordingly.

Solution

We will provide the engine maintainer with several tools to tune the suite for their engine.

• Some variables can be set to configure the basic test behavior:
  • the engine name (to be used in CREATE TABLE and be masked in SHOW CREATE TABLE);
  • default column options (when any are required, e.g. NOT NULL for CSV);
  • default table options (when any are required, e.g. connection for FEDERATED);
  • default index (INDEX, UNIQUE INDEX, PRIMARY KEY, whichever supported, or a special index);
  • default types (int type, char type, in case standard ones are not supported);

• if any actions need to be performed before/after each test, they can be described in include files which are always executed (e.g. creation of a server for FEDERATED before a test, and dropping it after a test);

• if a server requires non-standard procedures for table creation or modification, they can be tuned in include files which tests to use to perform these actions (CREATE and ALTER table are not run directly in the tests, only by calling the include files, so they are configurable);

• the engine can have its own set of disabled files, so that there is no need to provide only selected test names on the MTR command line – the whole suite can be run, and unnecessary files can be disabled through the list;

• server options can be configured;

• non-default combinations can be configured for the engine;

• subsuites can be completely ignored for the engine, so that if there is no interest in running partitioning tests or transactional tests, they can be simply omitted without any effort.

For more details, see the 'Tuning' section.

Bugs filed while working on the suite:

LP:973039 / MDEV-211 (Assertion `share->in_trans == 0' failed in maria_close on DROP TABLE under LOCK)
LP:976104 / MDEV-216 (Assertion `0' failed in my_message_sql on UPDATE IGNORE, or unknown error on release build)
LP:990187 / MDEV-237 (Assertion `share->reopen == 1' failed at maria_extra on ADD PARTITION)
LP:994275 / MDEV-248 (Assertion `real->type() == Item::FIELD_ITEM' failed in add_not_null_conds(JOIN*))
LP:994854 / MDEV-254 (Server hangs on updating an XtraDB table after FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT)
LP:997397 (TRUNCATE on a partitioned Aria table does not reset AUTO_INCREMENT)
MDEV-365 (Assertion `block->type == PAGECACHE_EMPTY_PAGE ...' failed in pagecache_read on ADD PARTITION)
MDEV-366 (Assertion `share->reopen == 1' failed in maria_extra on DROP TABLE which is locked twice)
MDEV-388 (Creating a federated table with a non-existing server returns a random error code)
MDEV-397 (Changing a column name via ALTER ONLINE does not work for InnoDB)
MySQL:64888 (Inconsistent behavior of dynamic concurrent_insert values)
MySQL:64892 (Session-level low_priority_updates does not work for INSERT)
MySQL:65146 (WITH CONSISTENT SNAPSHOT does not work with isolation level SERIALIZABLE)
MySQL:65225 (InnoDB miscalculates auto-increment after changing auto_increment_increment)
MySQL:65429 / LP:1004910 (Assertion failure block->page.space == page_get_space_id(page_align(ptr)))
MySQL:65431 / LP:1005052 (Error 1430 while selecting from a federated table with index on a bit column)
MySQL:65846 (INSERT DELAYED on a BLACKHOLE table hangs forever)
MySQL:65901 (AUTO_INCREMENT option on InnoDB table is ignored if added before autoinc column)

The list might be incomplete

Tuning

Please note that the test suite is synthetic. It means that it contains tests for a set of features which (the whole set) is not supported by any known engine. The same is true for result files – they are taken from different engines, depending on which produce the most sensible result, and in rare cases are even artificial. This means that there is no engine which would pass the whole suite without any tuning – not even MyISAM, which contributed to the result files the most.

Assumptions

We presume that the tests are set on a source tree build (which is reasonable, considering that the main goal is to help with storage engine development). We want to configure the tests for some engine OurENGINE.

The storage engine code is located in <basedir>/storage/<ourengine> folder.

The storage_engine test suite is located in <basedir>/mysql-test/suite/storage_engine folder and contains subsuites in subfolders of the corresponding names (currently <basedir>/mysql-test/suite/storage_engine/parts and <basedir>/mysql-test/suite/storage_engine/trx.

Common tuning steps

1. Create <basedir>/storage/ourengine/mysql-test/storage_engine folder.

2. Copy <basedir>/mysql-test/suite/storage_engine/define_engine.inc to <basedir>/storage/ourengine/mysql-test/storage_engine and edit the file (at the very least set ENGINE variable; check other variables which you find there, and modify as needed.

3. If the engine requires any additional server options (e.g. to load the engine, or to tune it, or both), create <basedir>/storage/ourengine/mysql-test/storage_engine/suite.opt file and add the options there.

4. If you know in advance that the engine requires additional steps before a test, add them at the end of define_engine.inc.

5. If you created any SQL objects in define_engine.inc, create file <basedir>/storage/ourengine/mysql-test/storage_engine/cleanup_engine.inc, or copy a stub from <basedir>/mysql-test/suite/storage_engine, and add the logic to drop the objects.

6. If you know in advance that the engine requires non-standard table creation and/or modification procedure, copy <basedir>/mysql-test/suite/storage_engine/create_table.inc and <basedir>/mysql-test/suite/storage_engine/alter_table.inc into <basedir>/storage/ourengine/mysql-test/storage_engine/ and modify them as needed.

7. Try to run the 1st test:
perl ./mtr --suite=storage_engine-ourengine 1st

8. If the test produces a mismatch, analyze it and decide whether table creation or test options or server options require more tuning, or the difference is expected.

9. If the difference is expected, create an rdiff file as
{{ diff -u <basedir>/mysql-test/suite/storage_engine/1st.result <basedir>/mysql-test/suite/storage_engine/1st.reject > <basedir>/storage/ourengine/mysql-test/storage_engine/1st.rdiff

10. When the 1st test passes, run the whole suite:

perl ./mtr --suite=storage_engine-ourengine --force --max-test-fail=0

11. Analyze failures, modify parameters or include files as needed, create rdiff files.

12. If any tests requires specific non-standard server/engine options, create files <testname>.opt in <basedir>/storage/ourengine/mysql-test/storage_engine.

13. If any tests have to be skipped, add them to <basedir>/storage/ourengine/mysql-test/storage_engine/disabled.def.

14. When you are satisfied with the results of storage_engine suite, proceed to the subsuites. If you are interested in running partitions tests, create folder <basedir>/storage/ourengine/mysql-test/storage_engine/parts

15. Repeat step 3, if needed, only now create suite.opt in <basedir>/storage/ourengine/mysql-test/storage_engine/parts.

16. Run the subsuite as perl ./mtr --suite=storage_engine/parts-ourengine --force --max-test-fail=0

17. Repeat steps 11-13, only now create files in <basedir>/storage/ourengine/mysql-test/storage_engine/parts

18. If you want to run transactional tests, repeat steps 14-17 for trx subsuite.

19. To execute the whole set of tests, run perl ./mtr --suite=storage_engine-ourengine,storage_engine/*-ourengine

Examples

Below are some real-life experiences of tuning the test suite for engines that currently come with MariaDB. Actual exact steps are always unique and depend on the storage engine (otherwise it wouldn't be tuning). In some cases only very basic actions are required; in others, it takes more work. In general, the more a storage engine is "different", the trickier is the task.

Easy level: MyISAM

Lets see how to make the suite work for a relatively standard engine, in terms of behavior similar to main MySQL engines.
We will create an overlay for MyISAM.
Note: "overlay" is a term introduced by MDEV-30, and it basically means a test suite or set of suites adapted for a certain engine

cd <basedir>/mysql-test
mkdir -p ../storage/myisam/mysql-test/storage_engine
cp suite/storage_engine/define_engine.inc ../storage/myisam/mysql-test/storage_engine/

Edit the copied version of define_engine.inc to set ENGINE to MyISAM:

@@ -8,7 +8,7 @@

 # The name of the engine under test must be defined in $ENGINE variable.

 # You can set it either here (uncomment and edit) or in your environment.

 #

-# let $ENGINE =;

+let $ENGINE = MyISAM;

 #

 ################################

 #

All other parameters look good: MyISAM does not require any specific table or column options, and it supports non-unique indexes, INT and CHAR types. These all are defaults (you can see it in define_engine.inc).

Also, we do not need any additional server options to activate the engine, since MyISAM is always there.

So, now we can try to run the 1st test:

perl ./mtr --suite=storage_engine-myisam 1st

...

storage_engine-myisam.1st        [ pass ]     20

The first test passed. Okay, now we can run the whole suite. Some tests will fail, this is expected; we need to see the results
so we can decide whether we should accept the difference, or disable the test, or patch the code.
So, we will run it with --force and --max-test-fail=0, to see all at once (you might also want to redirect the output to a file, because you will need it):

perl ./mtr --force --max-test-fail=0 --suite=storage_engine-myisam

...

Spent 42.193 of 64 seconds executing testcases

Completed: Failed 7/99 tests, 92.93% were successful.

Failing test(s): storage_engine-myisam.alter_tablespace storage_engine-myisam.check_table storage_engine-myisam.foreign_keys storage_engine-myisam.index_type_hash storage_engine-myisam.show_engine storage_engine-myisam.tbl_opt_insert_method storage_engine-myisam.tbl_opt_union

Note: Total execution time depends on the engine and on the machine. For MyISAM it's pretty fast, for other engines might take longer.

7 failing tests on the first iteration is extremely good, of course it won't be that bright with other engines. But it's MyISAM, what did you expect...

(Please keep in mind that the suite results are synthetic, meaning that they belong to different engines, and in rare cases are even artificial, e.g. when the only available engine supporting the functionality currently has a bug. So, no engine is expected to pass all tests without tuning. Not even MyISAM.)

Now it's time to analyze results.

Result handling in this suite is somewhat different from standard MTR tests. Unless you managed to crash the server or to hit a syntax error in the test itself, all results will be mismatches – that is, a test will not abort on a failed statement, but instead will try to proceed. The big benefit of it is that if your engine does not support everything, the tests are still usable, you'll just need to approve the difference in the results (by creating an rdiff file).

Of course, it also means that the tests produce more noise than usual – e.g. if your engine does not support ALTER, a standard MTR test would break on the first ALTER statement, while ours will continue and will allow you to use the logic, but will also produce a bunch of mismatches due to failing statements. Internal logic in tests does its best to make it cleaner, but still, the noise is expected.

So, with this knowledge, lets find the failures and go through them one by one.
Tip: if you saved the output to a file, failures can easily be found by ' fail ' search string (without quote marks).

The first failing test is alter_tablespace. Well, naturally – no tablespaces for MyISAM. But lets look at the output.

Mismatch says that some stuff is missing, and instead the test produces this:

+ERROR HY000: Table storage engine for 't1' doesn't have this option

+# ERROR: Statement ended with errno 1031, errname ER_ILLEGAL_HA (expected to succeed)

+# ------------ UNEXPECTED RESULT ------------

+# [ ALTER TABLE t1 DISCARD TABLESPACE ]

+# The statement|command finished with ER_ILLEGAL_HA.

+# Tablespace operations or the syntax or the mix could be unsupported. 

+# You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.

+# Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.

+# Also, this problem may cause a chain effect (more errors of different kinds in the test).

+# -------------------------------------------

Since MyISAM is not supposed to support tablespaces, changing the engine code is not an option. You can decide whether to disable the test entirely, or to create an rdiff file for it. Unless you have really tough restrictions on suite execution time, I recommend keeping tests and creating rdiffs. Even if you don't care about the result, the test, running as a regression check, will at least show that this very first statement has not suddenly started crashing the server, or something unexpectedly changed in the behavior.

Creating an rdiff file is simple:

diff -u suite/storage_engine/alter_tablespace.result suite/storage_engine/alter_tablespace.reject > ../storage/myisam/mysql-test/storage_engine/alter_tablespace.rdiff

Next failed test: check_table.

Its difference is simple:

 INSERT INTO t1 (a,b) VALUES (6,'f');

 CHECK TABLE t1 FAST;

 Table  Op      Msg_type        Msg_text

-test.t1        check   status  OK

+test.t1        check   status  Table is already up to date

 INSERT INTO t1 (a,b) VALUES (7,'g');

 INSERT INTO t2 (a,b) VALUES (8,'h');

 CHECK TABLE t2, t1 MEDIUM;

@@ -52,7 +52,7 @@

 INSERT INTO t1 (a) VALUES (17),(120),(132);

 CHECK TABLE t1 FAST;

 Table  Op      Msg_type        Msg_text

-test.t1        check   status  OK

+test.t1        check   status  Table is already up to date

 INSERT INTO t1 (a) VALUES (801),(900),(7714);

 CHECK TABLE t1 MEDIUM;

 Table  Op      Msg_type        Msg_text

No harm if the engine realizes that the table is up to date and says so; adding a diff.

diff -u suite/storage_engine/check_table.result suite/storage_engine/check_table.reject > ../storage/myisam/mysql-test/storage_engine/check_table.rdiff

Next failing test: foreign_keys

Just like alter_tablespace, it produces a lot of messages about possibly unsupported functionality, which is natural, since MyISAM doesn't have foreign keys. Adding a diff.

Next failing test: index_type_hash

It produces mismatches where HASH type is replaced by BTREE type:

 SHOW KEYS IN t1;

 Table  Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_comment

-t1     1       a       1       a       #       #       NULL    NULL    #       HASH            

+t1     1       a       1       a       #       #       NULL    NULL    #       BTREE           

Since MyISAM doesn't support HASH type, it is fine. Adding a diff.

Next failing test: show_engine

This test is optimistic and expects that SHOW ENGINE <engine name> STATUS command returns something – that is, the test obfuscates the output, but expects that it contains a row. For MyISAM it is not the case, hence the diff looks like a missing row:

@@ -4,7 +4,6 @@

 SHOW ENGINE <STORAGE_ENGINE> STATUS;

 Type   Name    Status

-<STORAGE_ENGINE>               ### Engine status, can be long and changeable ###

 # For SHOW MUTEX even the number of lines is volatile, so the result logging is disabled,

Adding a diff.

Next failing tests: tbl_opt_insert_method, tbl_opt_union

MyISAM does not use the table option INSERT_METHOD, it's the MERGE engine thing. But table creation does not normally fail on unsupported options, they are simply ignored. That's what we see here:

@@ -5,7 +5,7 @@

 t1     CREATE TABLE `t1` (

   `a` int(11) DEFAULT NULL,

   `b` char(8) DEFAULT NULL

-) ENGINE=<STORAGE_ENGINE> DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST

+) ENGINE=<STORAGE_ENGINE> DEFAULT CHARSET=latin1

SHOW ENGINE does not show the option. This is fine, adding rdiff. Exactly the same for UNION.

These were all 7 failures.

Now it's time to take care of subsuites. Currently there are two of them: parts (stands for 'partitions'), and trx (stands for 'transactions').

MyISAM definitely supports partitioning, so lets try them first.

(we are still in <basedir>/mysql-test)

mkdir ../storage/myisam/mysql-test/storage_engine/parts

This will show MTR that our engine is interested in the storage_engine/parts subsuite.

No additional parameters or tricks should be needed for MyISAM to run partition tests, since the suite already sets --partition option. So, we'll just run it:

perl ./mtr --force --max-test-fail=0 --suite=storage_engine/parts-myisam

... 

Spent 1.168 of 5 seconds executing testcases

Completed: All 8 tests were successful.

Note: For now, it is a very basic suite, only contains a few tests and literally takes several seconds

All good, tests passed, nothing needs to be done here.

Finally, there is the transactions subsuite (also contains tests for XA and snapshots). It is also very basic, but we know that MyISAM doesn't support transactions, XA and snapshots, so results won't be this pretty. Maybe we don't even need to run it, but why not try.

mkdir ../storage/myisam/mysql-test/storage_engine/trx

perl ./mtr --force --max-test-fail=0 --suite=storage_engine/trx-myisam

Spent 0.000 of 10 seconds executing testcases

Completed: Failed 13/13 tests, 0.00% were successful.

Failing test(s): storage_engine/trx-myisam.cons_snapshot_repeatable_read storage_engine/trx-myisam.cons_snapshot_serializable storage_engine/trx-myisam.delete storage_engine/trx-myisam.insert storage_engine/trx-myisam.level_read_committed storage_engine/trx-myisam.level_read_uncommitted storage_engine/trx-myisam.level_repeatable_read storage_engine/trx-myisam.level_serializable storage_engine/trx-myisam.select_for_update storage_engine/trx-myisam.select_lock_in_share_mode storage_engine/trx-myisam.update storage_engine/trx-myisam.xa storage_engine/trx-myisam.xa_recovery

The results are mess, as expected. All differences start with an extra warning (sometimes more than one, as the same is produced for snapshots and XA):

+# -- WARNING ----------------------------------------------------------------

+# According to I_S.ENGINES, MyISAM does not support transactions.

+# If it is true, the test will most likely fail; you can 

+# either create an rdiff file, or add the test to disabled.def.

+# If transactions should be supported, check the data in Information Schema.

+# ---------------------------------------------------------------------------

The rest is a mix of wrong results (missing rows, extra rows, different rows), warnings about rollback not working, etc. With 3rd-party engines, it would be up to a maintainer whether to add diffs for all tests or just ignore the whole thing since transactions are not supported. If you choose to ignore, simply delete the newly created ../storage/<your_engine>/mysql-test/storage_engine/trx, and the subsuite won't be executed for the engine. I choose to keep it, so I'll add rdiffs for all 13 files, it's not that much work, after all.

diff -u suite/storage_engine/trx/cons_snapshot_repeatable_read.result suite/storage_engine/trx/cons_snapshot_repeatable_read.reject > ../storage/myisam/mysql-test/storage_engine/trx/cons_snapshot_repeatable_read.rdiff

...

diff -u suite/storage_engine/trx/xa_recovery.result suite/storage_engine/trx/xa_recovery.reject > ../storage/myisam/mysql-test/storage_engine/trx/xa_recovery.rdiff

Tip: If you decide to do the same, take time to go through the reject files and see that your engine works as expected, under the circumstances; sometimes making things do what they are not normally expected to do reveals hidden problems.

Now we can run everything together:

perl ./mtr --suite=storage_engine-myisam,storage_engine/*-myisam

...

Spent 46.249 of 70 seconds executing testcases

Completed: All 120 tests were successful.

That's all. Now just stay out of failures.

Intermediate level: InnoDB plugin

A little bit more work is required to create an overlay for InnoDB. Lets try to do it for InnoDB plugin (which is not loaded by default as of 5.5.25, but is built there).

Again, start with creating the overlay directory:

mkdir -p ../storage/innobase/mysql-test/storage_engine
cp suite/storage_engine/define_engine.inc ../storage/innobase/mysql-test/storage_engine/
Edit ../storage/innobase/mysql-test/storage_engine/define_engine.inc

@@ -8,7 +8,7 @@

 # The name of the engine under test must be defined in $ENGINE variable.

 # You can set it either here (uncomment and edit) or in your environment.

 #

-# let $ENGINE =;

+let $ENGINE = InnoDB;

 #

 ################################

 #

As for MyISAM, all defaults are fine for InnoDB. But now we also need to server startup options to run server with the InnoDB plugin.

create the file ../storage/innobase/mysql-test/storage_engine/suite.opt:

--ignore-builtin-innodb

--plugin-load=ha_innodb

--innodb

It should be enough for the base suite. Lets run the 1st test now:

perl ./mtr --suite=storage_engine-innobase 1st

...

storage_engine-innobase.1st        [ pass ]    852

And then the whole suite:

perl ./mtr --suite=storage_engine-innobase --max-test-fail=0 --force

...

Spent 153.712 of 402 seconds executing testcases

Completed: Failed 28/99 tests, 71.72% were successful.

Failing test(s): storage_engine-innobase.alter_table_online storage_engine-innobase.alter_tablespace storage_engine-innobase.autoinc_secondary storage_engine-innobase.autoinc_vars storage_engine-innobase.cache_index storage_engine-innobase.checksum_table_live storage_engine-innobase.delete_low_prio storage_engine-innobase.fulltext_search storage_engine-innobase.index_enable_disable storage_engine-innobase.index_type_hash storage_engine-innobase.insert_delayed storage_engine-innobase.insert_high_prio storage_engine-innobase.insert_low_prio storage_engine-innobase.lock_concurrent storage_engine-innobase.optimize_table storage_engine-innobase.repair_table storage_engine-innobase.select_high_prio storage_engine-innobase.tbl_opt_ai storage_engine-innobase.tbl_opt_data_index_dir storage_engine-innobase.tbl_opt_insert_method storage_engine-innobase.tbl_opt_key_block_size storage_engine-innobase.tbl_opt_row_format storage_engine-innobase.tbl_opt_union storage_engine-innobase.type_char_indexes storage_engine-innobase.type_float_indexes storage_engine-innobase.type_spatial_indexes storage_engine-innobase.update_low_prio storage_engine-innobase.vcol

Not as great as it was with MyISAM. Lets see the details.

Some mismatches are either identical or similar to those in MyISAM, and caused by unsupported functionality (e.g. fulltext search, hash indexes, optimize_table, etc.). I won't go through them here, will just add rdiff files.

But some deserve attention.

alter_table_online:

 ALTER ONLINE TABLE t1 CHANGE b new_name <INT_COLUMN>;

+ERROR HY000: Can't execute the given 'ALTER' command as online

+# ERROR: Statement ended with errno 1915, errname ER_CANT_DO_ONLINE (expected to succeed)

+# ------------ UNEXPECTED RESULT ------------

+# The statement|command finished with ER_CANT_DO_ONLINE.

+# Functionality or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors. 

+# You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.

+# Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.

+# Also, this problem may cause a chain effect (more errors of different kinds in the test).

+# -------------------------------------------

It's hard to say whether all engines that support ALTER ONLINE should support them for the same set of changes; most likely not, and what we see here is just an InnoDB limitation. On the other hand, we know that MariaDB supports ALTER ONLINE, and namely renaming a column (see http://kb.askmonty.org/en/alter-table), and InnoDB supports at least some ALTER ONLINE operations (e.g. CHANGE COLUMN i i INT DEFAULT 1 works); so I think it's worth filing it as a low-priority bug, at least to make sure it works as expected: https://mariadb.atlassian.net/browse/MDEV-397

For now, I will add the test to ../storage/innobase/mysql-test/storage_engine/disabled.def list (need to create it, since it's the first test we disable for the engine):

alter_table_online : MDEV-397 (Changing a column name via ALTER ONLINE does not work for InnoDB)

If later it turns out to be expected behavior or limitation, I will remove the line from disabled.def, and will instead add an rdiff file.

alter_tablespace:

+# ERROR: Statement ended with errno 1030, errname ER_GET_ERRNO (expected to succeed)

+# ------------ UNEXPECTED RESULT ------------

+# [ ALTER TABLE t1 DISCARD TABLESPACE ]

+# The statement|command finished with ER_GET_ERRNO.

+# Tablespace operations or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors. 

+# You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.

+# Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.

+# Also, this problem may cause a chain effect (more errors of different kinds in the test).

+# -------------------------------------------

Now, that seems unexpected. But then again, tablespace operations are only applicable when InnoDB works in innodb-file-per-table mode, which we did not set in our options. Unless we want to use it for all tests, lets set it for this one only:

../storage/innobase/mysql-test/storage_engine/alter_tablespace.opt

--innodb-file-per-table=1

autoinc_vars:

 INSERT INTO t1 (a,b) VALUES (NULL,'g'),(NULL,'h'),(NULL,'i');

 SELECT LAST_INSERT_ID();

 LAST_INSERT_ID()

-850

+1100

 SELECT * FROM t1;

 a      b

 1      a

+1100   g

+1150   h

+1200   i

 2      b

 200    d

 3      c

 500    e

 800    f

-850    g

-900    h

-950    i

 DROP TABLE t1;

 SET auto_increment_increment = 500;

 SET auto_increment_offset = 300;

This is weird. Now real investigation starts – there is a good reason to look at the reject file to see the continuous flow:

...

SET auto_increment_increment = 300;

INSERT INTO t1 (a,b) VALUES (NULL,'d'),(NULL,'e'),(NULL,'f');

SELECT LAST_INSERT_ID();

LAST_INSERT_ID()

200

SELECT * FROM t1;

a       b

1       a

2       b

200     d

3       c

500     e

800     f

SET auto_increment_increment = 50;

INSERT INTO t1 (a,b) VALUES (NULL,'g'),(NULL,'h'),(NULL,'i');

SELECT LAST_INSERT_ID();

LAST_INSERT_ID()

1100

SELECT * FROM t1;

a       b

1       a

1100    g

1150    h

1200    i

2       b

200     d

3       c

500     e

800     f

DROP TABLE t1;

The first insert works all right with auto_increment_increment = 300. Then we change it to 50, but the following insert still uses 300 for the first value it inserts, and only then switches to 50. Thus we get 1100 instead of 850, and following values also differ. This smells like a bug, although not a very serious one. Since a brief check shows it's also reproducible on Oracle MySQL, we will file it on bugs.mysql.com: http://bugs.mysql.com/bug.php?id=65225 (I actually did it some time ago, when I tried to run the storage engine suite for InnoDB for the first time, that's why it's not brand new).

And we will also add the test to ../storage/innobase/mysql-test/storage_engine/disabled.def:

alter_table_online : MDEV-397 (Changing a column name via ALTER ONLINE does not work for InnoDB)

autoinc_vars : MySQL:65225 (InnoDB miscalculates auto-increment)

delete_low_prio, insert_high_prio, insert_low_prio, select_high_prio, update_low_prio:

They all have similar fragments in their output:

+# Timeout in include/wait_show_condition.inc for = 'DELETE FROM t1'

+#         show_statement : SHOW PROCESSLIST

+#         field          : Info

+#         condition      : = 'DELETE FROM t1'

+#         max_run_time   : 3

+# ------------ UNEXPECTED RESULT ------------

+# The statement|command finished with timeout in wait_show_condition.inc.

+# DELETE or table locking or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors. 

+# You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.

+# Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.

+# Also, this problem may cause a chain effect (more errors of different kinds in the test).

+# -------------------------------------------

As the documentation says, the high|low priority functionality (e.g. DELETE LOW_PRIORITY) only works for table-level locking, and the whole test is based on this assumption. InnoDB uses row-level locking, so the entire flow does not work quite as expected. We still can add rdiff files, but, unlike the most of other tests, these ones take relatively long (probably over 10 seconds each). Besides, since locking works entirely different here, the test results are likely to be unstable, as it will be all about timing. So, it makes more sense to disable the tests by adding them to ../storage/innobase/mysql-test/storage_engine/disabled.def:

alter_table_online : MDEV-397 (Changing a column name via ALTER ONLINE does not work for InnoDB)

autoinc_vars : MySQL:65225 (InnoDB miscalculates auto-increment)

delete_low_prio : InnoDB does not use table-level locking

insert_high_prio : InnoDB does not use table-level locking

insert_low_prio : InnoDB does not use table-level locking

select_high_prio : InnoDB does not use table-level locking

update_low_prio : InnoDB does not use table-level locking

tbl_opt_ai:

 Table  Create Table

 t1     CREATE TABLE `t1` (

   `a` int(11) DEFAULT NULL

-) ENGINE=<STORAGE_ENGINE> AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

+) ENGINE=<STORAGE_ENGINE> DEFAULT CHARSET=latin1

 ALTER TABLE t1 AUTO_INCREMENT=100;

 SHOW CREATE TABLE t1;

 Table  Create Table

 t1     CREATE TABLE `t1` (

   `a` int(11) DEFAULT NULL

-) ENGINE=<STORAGE_ENGINE> AUTO_INCREMENT=100 DEFAULT CHARSET=latin1

+) ENGINE=<STORAGE_ENGINE> DEFAULT CHARSET=latin1

 DROP TABLE t1;

We already looked at ignored table options in MyISAM tests, but this one is different. Why would AUTO_INCREMENT be ignored, it should be supported all right by InnoDB? (Brief manual check confirms it). Some digging shows, however, that in our case it is truly ignored. It is reproducible with Oracle MySQL, filing a bug on bugs.mysql.com: http://bugs.mysql.com/bug.php?id=65901

Adding the test to ../storage/innobase/mysql-test/storage_engine/disabled.def:

alter_table_online : MDEV-397 (Changing a column name via ALTER ONLINE does not work for InnoDB)

autoinc_vars : MySQL:65225 (InnoDB miscalculates auto-increment)

delete_low_prio : InnoDB does not use table-level locking

insert_high_prio : InnoDB does not use table-level locking

insert_low_prio : InnoDB does not use table-level locking

select_high_prio : InnoDB does not use table-level locking

update_low_prio : InnoDB does not use table-level locking

tbl_opt_ai : MySQL:65901 (AUTO_INCREMENT option on InnoDB table is ignored if added before autoinc column)

tbl_opt_key_block_size, tbl_opt_row_format:

 CREATE TABLE t1 (a <INT_COLUMN>, b <CHAR_COLUMN>) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> KEY_BLOCK_SIZE=8;

+Warnings:

+Warning        1478    InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.

+Warning        1478    InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.

+Warning        1478    InnoDB: ignoring KEY_BLOCK_SIZE=8.

Doing the same as we did for alter_tablespace, only now adding both innodb_file_per_table and innodb_file_format:

../storage/innobase/mysql-test/storage_engine/tbl_opt_key_block_size.opt:

--innodb-file-per-table=1

--innodb-file-format=Barracuda

type_char_indexes:

 SET SESSION optimizer_switch = 'engine_condition_pushdown=on';

 EXPLAIN SELECT * FROM t1 WHERE c > 'a';

 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra

-#      #       #       range   c_v     c_v     #       #       #       Using index condition

+#      #       #       range   c_v     c_v     #       #       #       Using where

 SELECT * FROM t1 WHERE c > 'a';

 c      c20     v16     v128

 b      char3   varchar1a       varchar1b

@@ -135,7 +135,7 @@

 r3a

 EXPLAIN SELECT * FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16;

 id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra

-#      #       #       range   #       v16     #       #       #       #

+#      #       #       ALL     #       NULL    #       #       #       #

 SELECT * FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16;

 c      c20     v16     v128

 a      char1   varchar1a       varchar1b

Note: For now we assume that inside one engine, statistics is stable enough to produce consistent results on each test run, which is why we show certain fields in explain to let you decide whether you are satisfied with them or not. If further experience shows that even for the same engine, these tests routinely produce different results, and more often than not it's valid behavior, we might change it.

For now, I will consider these results acceptable, and will add rdiff.

As I said before, the rest of failures do not deserve verbose analysis, they are pretty straightforward, I just added rdiff for each of them.

Now working with storage_engine/parts and storage_engine/trx.

mkdir ../storage/innobase/mysql-test/storage_engine/trx
mkdir ../storage/innobase/mysql-test/storage_engine/parts

Copy your previously created suite.opt file to each of the subfolders: as far as MTR is concerned, they are separate suites.

cp ../storage/innobase/mysql-test/storage_engine/suite.opt ../storage/innobase/mysql-test/storage_engine/trx/
cp ../storage/innobase/mysql-test/storage_engine/suite.opt ../storage/innobase/mysql-test/storage_engine/parts/

Maybe you'll want to add something else to those options. I, for one, will add --innodb-lock-wait-timeout=1 to ../storage/innobase/mysql-test/storage_engine/trx/suite.opt. Probably it should have been done for other suites, too – but it's never late, if there are any timeout issues observed.

When you add rdiff files for subsuites, don't forget to put them in the subfolders:

diff -u suite/storage_engine/parts/checksum_table.result suite/storage_engine/parts/checksum_table.reject > ../storage/innobase/mysql-test/storage_engine/parts/checksum_table.rdiff
etc.

Again, mostly failures are mismatches due to different output or unsupported functionality.
Note: Also note that repair_table test results are likely to differ, even if repair is supported, since the test tries to corrupt existing table files, which are different for each engine.

trx/cons_snapshot_serializable:

 # If consistent read works on this isolation level (SERIALIZABLE), the following SELECT should not return the value we inserted (1)

 SELECT * FROM t1;

 a

+1

 COMMIT;

It is a bug. Filing as http://bugs.mysql.com/bug.php?id=65146 and adding to disabled.def (don't forget that it should be under trx folder now:
../storage/innobase/mysql-test/storage_engine/trx/disabled.def:

cons_snapshot_serializable : MySQL:65146 (CONSISTENT SNAPSHOT does not work with SERIALIZABLE)

Now, running the whole set:

perl ./mtr --suite=storage_engine-innobase,storage_engine/*-innobase 

...

Spent 300.715 of 364 seconds executing testcases

Completed: All 111 tests were successful.

Much slower than for MyISAM, but that's how it is usually is.

Advanced level: MERGE

Yet more tricks would be required to tune the same suite for the MERGE engine, because now we will also have to think about how a table is created.
We can't just create a plain MERGE table and work with it, it needs to have an underlying table, at least one; and if we alter the merge table, underlying tables need to be altered accordingly, otherwise the merge table will become non-functional.

Start the same way as we started for other engines, by creating the overlay folder:

mkdir -p ../storage/myisammrg/mysql-test/storage_engine
cp suite/storage_engine/define_engine.inc ../storage/myisammrg/mysql-test/storage_engine/

We know that we'll need INSERT_METHOD and UNION in our table options; in other circumstances, they should have been added to $default_tbl_opts; but we cannot set a global UNION, because it will contain different underlying tables, and since we will be modifying the creation procedure anyway, there is no point at adding INSERT_METHOD here, either.

@@ -8,7 +8,7 @@

 # The name of the engine under test must be defined in $ENGINE variable.

 # You can set it either here (uncomment and edit) or in your environment.

 #

-# let $ENGINE =;

+let $ENGINE = MRG_MYISAM;

 #

 ################################

 #

What happens if we now run the 1st test if we did before?

perl ./mtr --suite=storage_engine-myisammrg 1st

 SHOW COLUMNS IN t1;

 INSERT INTO t1 VALUES (1,'a');

+ERROR HY000: Table 't1' is read only

+# ------------ UNEXPECTED RESULT ------------

+# The statement|command finished with ER_OPEN_AS_READONLY.

+# INSERT INTO .. VALUES or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors. 

+# You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.

+# Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.

+# Also, this problem may cause a chain effect (more errors of different kinds in the test).

+# -------------------------------------------

That's because we don't have underlying tables under the merge table. We need to modify table creation procedure.
First, we need to decide how to do it. There can be many ways, I will choose a simple one, as I think:

• before each test, I will create a special mrg schema, which will contain underlying tables, so I don't need to remember all the names when it's time to cleanup;
• at the end of the test, i will drop the mrg schema, and thus will get rid of all additional objects at once;
• whenever a new test table has to be created, I will create a MyISAM table with the same name in mrg schema, and will point my test table at it;
• whenever a test table has to be altered, I will also alter the MyISAM table with the same name in mrg schema.

In order to achieve this, we need to override 3 files, and modify our already created ../storage/myisammrg/mysql-test/storage_engine/define_engine.inc. Lets start with the latter.

define_engine.inc is the include file which is executed before each test. So, it's the place to put the logic which precedes a test.
At the end of ../storage/myisammrg/mysql-test/storage_engine/define_engine.inc I will mrg schema creation:

@@ -40,6 +40,10 @@

 # Here you can place your custom MTR code which needs to be executed before each test,

 # e.g. creation of an additional schema or table, etc.

 # The cleanup part should be defined in cleanup_engine.inc

+--disable_warnings

+DROP DATABASE IF EXISTS mrg;

+--enable_warnings

+CREATE DATABASE mrg;

Now, it's time for the 3 files to override:

cp suite/storage_engine/cleanup_engine.inc ../storage/myisammrg/mysql-test/storage_engine/
cp suite/storage_engine/create_table.inc ../storage/myisammrg/mysql-test/storage_engine/
cp suite/storage_engine/alter_table.inc ../storage/myisammrg/mysql-test/storage_engine/

cleanup_engine.inc is the file which is executed after each test; so, in ../storage/myisammrg/mysql-test/storage_engine/cleanup_engine.inc I will be dropping my mrg schema:

@@ -8,4 +8,9 @@

 # Here you can add whatever is needed to cleanup 

 # in case your define_engine.inc created any artefacts,

 # e.g. an additional schema and/or tables.

+--disable_query_log

+--disable_warnings

+DROP DATABASE IF EXISTS mrg;

+--enable_warnings

+--enable_query_log

Now, the actual table creation.
Tests do not run CREATE TABLE / ALTER TABLE statements directly, they always call create_table.inc or alter_table.inc, correspondingly. So, if we edit them properly, it will affect all tests at once – the gain is worth spending some effort.

Below I will show the changes I had made; in fact, there are many ways to achieve the same goal, probably some of them more efficient. Be creative when the time comes.

--- suite/storage_engine/create_table.inc	2012-07-15 17:46:03.638461728 +0400

+++ ../storage/myisammrg/mysql-test/storage_engine/create_table.inc	2012-07-15 22:08:29.324511647 +0400

@@ -54,6 +54,15 @@

   --let $table_name = t1

 }

+# Child statement is a statement that will create an underlying table.

+# From this point, it will deviate from the main statement, that's why

+# we start creating it here in parallel with the main one.

+# For underlying tables, we will create a table in mrg schema, e.g. 

+# for table t1 the underlying table will be mrg.t1, etc.

+# Since we will only create one child here, it should be enough. If we want more,

+# we can always add a suffix, e.g. mrg.t1_child1, mrg.t1_child2, etc.

+

+--let $child_statement = $create_statement mrg.$table_name

 --let $create_statement = $create_statement $table_name

 if (!$create_definition)

@@ -70,6 +79,9 @@

 if ($create_definition)

 {

   --let $create_statement = $create_statement ($create_definition)

+  # Table definition for the underlying table should be the same

+  # as for the MERGE table

+  --let $child_statement = $child_statement ($create_definition)

 }

 # If $default_engine is set, we will rely on the default storage engine

@@ -78,6 +90,12 @@

 {

   --let $create_statement = $create_statement ENGINE=$storage_engine

 }

+# Engine for an underlying table differs

+--let $child_statement = $child_statement ENGINE=MyISAM

+

+# Save default table options, we will want to restore them later

+--let $default_tbl_opts_saved = $default_tbl_opts

+--let $default_tbl_opts = $default_tbl_opts UNION(mrg.$table_name) INSERT_METHOD=LAST

 # Default table options from define_engine.inc

 --let $create_statement = $create_statement $default_tbl_opts

@@ -86,6 +104,7 @@

 if ($table_options)

 {

   --let $create_statement = $create_statement $table_options

+  --let $child_statement = $child_statement $table_options

 }

 # The difference between $extra_tbl_opts and $table_options

@@ -98,16 +117,19 @@

 if ($extra_tbl_opts)

 {

   --let $create_statement = $create_statement $extra_tbl_opts

+  --let $child_statement = $child_statement $extra_tbl_opts

 }

 if ($as_select)

 {

   --let $create_statement = $create_statement AS $as_select

+  --let $child_statement = $child_statement AS $as_select

 }

 if ($partition_options)

 {

   --let $create_statement = $create_statement $partition_options

+  --let $child_statement = $child_statement $partition_options

 }

 # We now have the complete CREATE statement in $create_statement.

@@ -120,6 +142,12 @@

 # Surround it by --disable_query_log/--enable_query_log

 # if you don't want it to appear in the result output.

 #####################

+--disable_warnings

+--disable_query_log

+eval DROP TABLE IF EXISTS mrg.$table_name;

+eval $child_statement;

+--enable_query_log

+--enable_warnings

 if ($disable_query_log)

 {

@@ -166,6 +194,10 @@

 --let $temporary = 0

 --let $disable_query_log = 0

+# Restore default table options now

+--let $default_tbl_opts = $default_tbl_opts_saved

+

+

 # Restore the error codes of the main statement

 --let $mysql_errno = $my_errno

 --let $mysql_errname = $my_errname

We know we also need to modify alter_table.inc, but it's interesting to see if our changes actually work.

perl ./mtr --suite=storage_engine-myisammrg 1st

...

storage_engine-myisammrg.1st             [ pass ]     26

Great. Lets now modify ../storage/myisammrg/mysql-test/storage_engine/alter_table.inc:

@@ -20,9 +20,12 @@

 # --let $alter_definition = ADD COLUMN b $char_col DEFAULT ''

 # 

+--let $child_alter_definition = $alter_definition

+

 if ($rename_to)

 {

   --let $alter_definition = RENAME TO $rename_to

+  --let $child_alter_definition = RENAME TO mrg.$rename_to

 }

 if (!$alter_definition)

@@ -43,6 +46,9 @@

 }

 --let $alter_statement = $alter_statement TABLE $table_name $alter_definition

+# We don't want to do ONLINE on underlying tables, we are not testing MyISAM

+--let $child_statement = ALTER TABLE mrg.$table_name $child_alter_definition

+

 # We now have the complete ALTER statement in $alter_statement.

@@ -75,6 +81,20 @@

 # Surround it by --disable_query_log/--enable_query_log

 # if you don't want it to appear in the result output.

 #####################

+--disable_query_log

+--disable_warnings

+

+# We will only try to alter the underlying table if the main alter was successful

+if (!$my_errno)

+{

+  if ($rename_to)

+  {

+    eval ALTER TABLE $rename_to UNION(mrg.$rename_to);

+  }

+  eval $child_statement;

+}

+--enable_warnings

+--enable_query_log

 # Unset the parameters, we don't want them to be accidentally reused later

 --let $alter_definition = 

Note that in both create_table and alter_table we run our additional code with disable_query_log / disable_result_log. It's a tradeoff: this way we reduce the number of mismatches (because our additional code does not produce anything), but it will also make investigation more difficult, should a problem start somewhere in this code. It's up to the person who maintains the engine suite to decide what's best.

Example:
We have a MERGE table which points to an underlying table containing non-unique values. Normally, the test assumes that the table under test contains these values, of course; but in our case it's actually the underlying MyISAM table.
Then, the test performs ALTER TABLE .. ADD UNIQUE INDEX ... and expects it to fail.
In our case, the statement on the MERGE table will succeed, but the statement on the underlying table will fail quietly; if the test tries to do something else afterwards, it reveal that the merge table and the underlying table diverged, but it won't be clear from the test output why it happened.

Now lets try to run the suite:

perl ./mtr --suite=storage_engine-myisammrg --force --max-test-fail=0

Spent 34.141 of 80 seconds executing testcases

Completed: Failed 41/98 tests, 58.16% were successful.

Not great, but not that bad either, considering. Lets look at the results.

alter_table and some other tests produce the following mismatch on SHOW CREATE TABLE:

@@ -127,7 +127,7 @@

   `a` int(11) DEFAULT NULL,

   `b` char(8) DEFAULT NULL,

   `c` char(8) DEFAULT NULL

-) ENGINE=<STORAGE_ENGINE> DEFAULT CHARSET=utf8

+) ENGINE=<STORAGE_ENGINE> DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`mrg`.`t1`)

 ALTER TABLE t1 DEFAULT CHARACTER SET = latin1 COLLATE latin1_general_ci;

Quite as expected, since we have additional options on our tables; requires adding an rdiff.

alter_table_online:

 ALTER ONLINE TABLE t1 MODIFY b <INT_COLUMN> DEFAULT 5;

-ERROR HY000: Can't execute the given 'ALTER' command as online

+# ERROR: Statement succeeded (expected results: ER_CANT_DO_ONLINE)

+# ------------ UNEXPECTED RESULT ------------

+# The statement|command succeeded unexpectedly.

+# Functionality or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors. 

+# You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.

+# Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.

+# Also, this problem may cause a chain effect (more errors of different kinds in the test).

+# -------------------------------------------

This is all right I guess. It's good that online alter can be done, right?
But this is bad:

 ALTER ONLINE TABLE t1 CHANGE b new_name <INT_COLUMN>;

-ERROR HY000: Can't execute the given 'ALTER' command as online

+ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

+# ERROR: Statement ended with errno 1168, errname ER_WRONG_MRG_TABLE (expected results: ER_CANT_DO_ONLINE)

 ALTER ONLINE TABLE t1 COMMENT 'new comment';

Looking earlier in the test output, we find out that we are working with temporary tables here. And there is the bug MySQL:57657 which says that altering a temporary MERGE table is broken in 5.5. Whether to add an rdiff or disable the test – it's a question. I think I will disable, after all, although it's a bit sad. You can choose to be smarter, and since you have your own alter_table.inc anyway, add some logic in there, checking whether a table is temporary or not.

create_table:

 CREATE TABLE t1 ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS> AS SELECT 1 UNION SELECT 2;

-SHOW CREATE TABLE t1;

-Table  Create Table

-t1     CREATE TABLE `t1` (

-  `1` bigint(20) NOT NULL DEFAULT '0'

-) ENGINE=<STORAGE_ENGINE> DEFAULT CHARSET=latin1

-SELECT * FROM t1;

-1

-1

-2

-DROP TABLE t1;

+ERROR HY000: 'test.t1' is not BASE TABLE

+# ERROR: Statement ended with errno 1347, errname ER_WRONG_OBJECT (expected to succeed)

+# ------------ UNEXPECTED RESULT ------------

+# The statement|command finished with ER_WRONG_OBJECT.

+# CREATE TABLE .. AS SELECT or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors. 

+# You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.

+# Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.

+# Also, this problem may cause a chain effect (more errors of different kinds in the test).

+# -------------------------------------------

AS SELECT doesn't work with MERGE tables; we didn't consider it in our simple changes of create_table.inc, because we only do AS SELECT a few times in the suite, so it seems easier just to accept this difference here. Although in general, it's up to the person who modifies the creation procedure.

lock:

The test is quite messed up, because merge children are locked through the parent tables, which the test of course does not expect. E.g. if it locks two tables and then drops them, it expects that nothing is locked any longer, which is not true for the merge tables. Adding an rdiff, anyway locking is very specific for merge tables and needs to be tested as an engine feature rather than as basic functionality.

The rest are usual mismatches due to unsupported functionality etc.

MERGE engine doesn't support partitions and transactions, but again, lets see what happens, since it's nearly for free:

mkdir ../storage/myisammrg/mysql-test/storage_engine/parts
mkdir ../storage/myisammrg/mysql-test/storage_engine/trx

perl ./mtr --suite=storage_engine/*-myisammrg --force --max-test-fail=0

All tests failed, of course.

For all partitioned tables:

+ERROR HY000: Engine cannot be used in partitioned tables

+# ERROR: Statement ended with errno 1572, errname ER_PARTITION_MERGE_ERROR (expected to succeed)

+# ------------ UNEXPECTED RESULT ------------

+# [ CREATE TABLE t1 (a INT(11) /*!*/ /*Custom column options*/) ENGINE=MRG_MYISAM /*!*/ /*Custom table options*/ UNION(mrg.t1) INSERT_METHOD=LAST PARTITION BY HASH(a) PARTITIONS 2 ]

+# The statement|command finished with ER_PARTITION_MERGE_ERROR.

+# Partitions or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors. 

+# You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.

+# Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.

+# Also, this problem may cause a chain effect (more errors of different kinds in the test).

+# -------------------------------------------

Transactional tests run somehow, but of course diffs are as extensive as they were for MyISAM. All this is expected, and can be solved either by removing the nearly created trx and parts subdirs, or adding rdiffs. It seems reasonable to remove parts and keep trx, but with the paranoic assumption that one day an attempt to create a partitioned MERGE table will crash the server, I will keep parts too; anyway they all together take less than a second (rejecting table creation and failing everything with "table doesn't exist" is fast). So, I will add rdiffs for each file.

Running all at once now:

perl ./mtr --suite=storage_engine-myisammrg,storage_engine/*-myisammrg

Spent 46.994 of 70 seconds executing testcases

Completed: All 119 tests were successful.