How MySQL-Sandbox is tested, and tests MySQL in the process

MySQL-Sandbox is a great tool for testing a new release, and in fact this is what I do when a new MySQL tarball becomes available. I don't think many people are aware of the full testing capabilities of the sandbox, though.
When you think about testing, you may just think of creating a sandbox with the new tarball, and then hammering it with your pet procedure. That works, of course, as the main purpose of MySQL-Sandbox is to allow you to do just that. There is, however, a full test suite that can tell you in a short while if your tarball is compatible with the past or not.
This procedure is quite strict. It has happened several times that I caught a bug in a new release of MySQL, or Percona Server, or MariaDB, just by running this suite.

How MySQL-Sandbox gets tested

Before describing how to test, I would like to show what I do. When a new version of MySQL-Sandbox is ready (and I happen to have time, because, hey! I also have a day job!) I subject it to the full test suite, which is about 500 different tests (the number may vary depending on the operating system and the MySQL version being tested). Then I repeat the full test for every version that I have stored in my test machines. That is, from version 5.0 to 5.7, passing through Percona Server and MariaDB forks, I test about a dozen versions:

5.0.96
5.1.73
5.5.44
5.6.25
5.7.5
5.7.7
5.7.8
5.7.9
mariadb 10.0.20
mariadb 10.1.6
mariadb 5.5.40
percona server 5.5.43
percona server 5.6.25

The above versions change when new releases are available. I repeat the same test in two different machines, covering OSX and Linux, for a grand total of ≈ 12,000 tests before I upload the tarball to CPAN.

What does the MySQL-Sandbox test suite do

There are 20 files in the ./t directory, each one starting a set of thematic tests:

• t/01_modules.t tests the MySQL Sandbox module
• t/02_test_binaries.t tests the completeness of MySQL-Sandbox deployment
• t/03_test_sandbox.t is the longest and more comprehensive test in the suite. It installs every type of sandbox, and runs basic functinal tests for each. It also includes an expensive test for sandbox installation with pattern recognition. In total, it runs for more than 10 minutes. This is where new versions may fail if they are not fully compatible with previous ones.
• t/04_test_sbtool.t is a test for the companion tool, which can do many operations, including installing and testing hierarchical replication.
• t/05_test_smoke.t tests a sandbox with the basic functionalities that were considered important to test manually when I was working at MySQL. Tired of testing it manually, I scripted the procedure. It may not be so vital now, but it does not hurt to run it.
• t/06_test_user_defined.t is a demo test for the simplest user-defined tests implemented with the sandbox
• t/07_test_user_defined.t does the same as the above, but testing replication instead of a single server
• t/08_test_single_port_checking.t tests that we can install a sandbox multiple time, with automatic port choice
• t/09_test_multiple_port_checking.t tests group sandboxes for the above feature
• t/10_check_start_restart.t tests that we can restart sandboxed servers with options set on-the-fly
• t/11_replication_parameters.t tests the installation of replication groups with user parameters that affect one or more nodes.
• t/12_custom_user_pwd.t tests the ability of defining your own passwords instead of using the defaults
• t/13_innodb_plugin_install.t tests the installation of the innodb plugin (it runs only only for version 5.1)
• t/14_semi_synch_plugin_install.t tests the installation and functioning of the semi-synchronous plugin (requires version 5.5+)
• t/15_user_privileges.t tests that the sandbox users have the privileges that were expected
• t/16_replication_options.t similar to test #11, but testing a different set of options
• t/17_replication_flow.t tests that regular and circular replication groups can transfer data as expected.
• t/18_force_creation.t tests that we can create a sandbox by overwriting an existing one
• t/19_replication_gtid.t tests replication with GTID
• t/20_add_option.t tests the ability of restarting a server with a permanent new option.

How to test a new tarball with MySQL-Sandbox

You do not need to go to the full testing of every version. That's done to make sure that MySQL-Sandbox does not have regressions, and works as expected with all the versions. But if your purpose is to make sure that your new tarball is ready to be used, a simple pass of the test suite will do. Here are the steps:

1. Get the tarball. For this demonstration, we will use the latest Percona Server: Percona-Server-5.6.25-rel73.1-Linux.x86_64.ssl100.tar.gz
2. We will need to download the MySQL-Sandbox code. It is not enough to have it installed, as we will need visibility and access to the test files.

   $ git clone https://github.com/datacharmer/mysql-sandbox.git
   $ cd mysql-sandbox
   

3. We need the tarball to be extracted in the default directory ($HOME/opt/mysql):

   $ make_sandbox --export_binaries --add_prefix=Perc /path/to/Percona-Server-5.6.25-rel73.1-Linux.x86_64.ssl100.tar.gz
   

   This step moves the tarball under $HOME/opt/mysql, creates a directory named Perc5.6.25, and installs a sandbox from that new directory.
4. The previous step is only needed for two things: creating the directory in the right place, and making sure the tarball can be installed. Sometimes this step fails because of some surprising incompatibility. At this point, we can remove the new sandbox:

   $ sbtool -o delete -s ~/sandboxes/msb_Perc5_6_25
   

5. Now we are ready, and we can start the test:

   $ perl Makefile.PL
   $ make
   $ export TEST_VERSION=Perc5.6.25
   $ make test
   

This will run for quite a while. Depending on the power of your server, it can take from 20 to 40 minutes.

$ make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/01_modules.t ...................... ok
t/02_test_binaries.t ................ ok
t/03_test_sandbox.t ................. Testing <5 .6.26="">. Please wait. This will take a few minutes
t/03_test_sandbox.t ................. ok
t/04_test_sbtool.t .................. ok
t/05_test_smoke.t ................... ok
t/06_test_user_defined.t ............ ok
t/07_test_user_defined.t ............ ok
t/08_test_single_port_checking.t .... ok
t/09_test_multiple_port_checking.t .. ok
t/10_check_start_restart.t .......... ok
t/11_replication_parameters.t ....... ok
t/12_custom_user_pwd.t .............. ok
t/13_innodb_plugin_install.t ........ # Skipping version 5.6.26 for this test. It is not in the required range (5.1.6 - 5.1.99)
t/13_innodb_plugin_install.t ........ ok
t/14_semi_synch_plugin_install.t .... ok
t/15_user_privileges.t .............. ok
t/16_replication_options.t .......... ok
t/17_replication_flow.t ............. ok
t/18_force_creation.t ............... ok
t/19_replication_gtid.t ............. ok
t/20_add_option.t ................... ok
All tests successful.
Files=20, Tests=495, 1560 wallclock secs ( 0.22 usr  0.03 sys + 480.89 cusr 73.55 csys = 554.69 CPU)
Result: PASS

CAVEATS: When you run this test, you must be aware of three things:

1. If you have sandboxes running in $HOME/sandboxes, they will be stopped when the test starts. This is a necessity to avoid clashes, as the test needs to start every possible combination.
2. If your sandboxes were running in a non-standard location, you need to stop them manually, as they may make the test fail. The test will not care if the main server runs (on port 3306.) It will not clash, and it will not attempt to shut it down.
3. If you stop the test during the execution, you may end up with orphaned servers, which are installed under ./t/test_sb. If that happens, you will need to stop and eventually remove them manually.

Running only a subset of the tests

If you want to test only something specific, you can do that by invoking one of the tests mentioned above. For example, if you want to test only basic replication, you can do this:

$ perl Makefile.PL
$ make
$ export TEST_VERSION=Perc5.6.25
$ prove -b -v t/17_replication_flow.t
t/17_replication_flow.t ..
1..24
installing and starting master
installing slave 1
installing slave 2
starting slave 1
. sandbox server started
starting slave 2
. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/repl_deployment
ok - Replication directory created
# Master log: mysql-bin.000001 - Position: 3667 - Rows: 2
# Testing slave #1
ok - Slave #1 acknowledged reception of transactions from master
ok - Slave #1 IO thread is running
ok - Slave #1 SQL thread is running
ok - Table t1 found on slave #1
ok - Table t1 has 2 rows on #1
# Testing slave #2
ok - Slave #2 acknowledged reception of transactions from master
ok - Slave #2 IO thread is running
ok - Slave #2 SQL thread is running
ok - Table t1 found on slave #2
ok - Table t1 has 2 rows on #2
# TESTS :    10
# FAILED:     0 (  0.0%)
# PASSED:    10 (100.0%)
ok - Replication test was successful
# executing "stop" on $HOME/sandboxes/repl_deployment
executing "stop" on slave 1
executing "stop" on slave 2
executing "stop" on master
# executing "clear" on $HOME/sandboxes/repl_deployment
executing "clear" on slave 1
executing "clear" on slave 2
executing "clear" on master
sandbox at <$HOME/sandboxes/repl_deployment> has been removed
ok - Regular replication directory repl_deployment removed
installing node 1
installing node 2
installing node 3
# server: 1:
# server: 2:
# server: 3:
# server: 1:
# server: 2:
# server: 3:
# server: 1:
# server: 2:
# server: 3:
Circular replication activated
group directory installed in $HOME/sandboxes/repl_deployment
ok - circular replication installed
# Master log: mysql-bin.000001 - Position: 1126 - Rows: 2
# Testing slave #1
ok - Slave #1 IO thread is running
ok - Slave #1 SQL thread is running
ok - Table t1 found on slave #1
ok - Table t1 has 2 rows on #1
# Testing slave #2
ok - Slave #2 IO thread is running
ok - Slave #2 SQL thread is running
ok - Table t1 found on slave #2
ok - Table t1 has 2 rows on #2
# TESTS :     8
# FAILED:     0 (  0.0%)
# PASSED:     8 (100.0%)
ok - Replication test was successful
# executing "stop" on $HOME/sandboxes/repl_deployment
# server: 1:
# server: 2:
# server: 3:
executing "stop" on node 1
executing "stop" on node 2
executing "stop" on node 3
# executing "clear" on $HOME/sandboxes/repl_deployment
# server: 1:
# server: 2:
# server: 3:
executing "clear" on node 1
executing "clear" on node 2
executing "clear" on node 3
sandbox at <$HOME/sandboxes/repl_deployment> has been removed
ok - Circular replication directory repl_deployment removed
ok
All tests successful.
Files=1, Tests=24, 69 wallclock secs ( 0.05 usr  0.01 sys +  3.24 cusr  2.57 csys =  5.87 CPU)
Result: PASS

You can repeat the procedure for every file ./t/*.t

Writing your own tests

As a parting thought, let me mention again that you can create your own user-defined tests using the sandbox simple hooks.
Here is a sample user defined test that you can run using test_sandbox:
There are two kind of tests: shell and sql The test type is defined by a keyword followed by a colon.
The 'shell' test requires:

• a 'command', which is passed to a shell.
• The 'expected' label is a string that you expect to find within the shell output. If you don't expect anything, you can just say "expected = OK", meaning that you will be satisfied with a ZERO exit code reported by the operating system.
• The 'msg' is the description of the test that is shown to you when the test runs.

shell:
command  = make_sandbox $TEST_VERSION -- --no_confirm --sandbox_directory=msb_XXXX
expected = sandbox server started
msg      = sandbox creation

The 'sql' test requires

• a 'path', which is the place where the test engine expects to find a 'use' script.
• The 'query' is passed to the above mentioned script and the output is captured for further processing.
• The 'expected' parameter is a string that you want to find in the query output.
• The 'msg' parameter is like the one used with the 'shell' test.

sql:
path    = $SANDBOX_HOME/msb_XXXX
query   = select 10 * 10
expected = 100
msg      = checking database response

All strings starting with a $ are expanded to their corresponding environment variables. For example, if $SANDBOX_HOME is /home/sb/tests, the line below will expand to

command = /home/sb/tests/msb_5_1_30/stop

It is a good idea to finish every test with a cleanup. Here, we simply stop the server

shell:
command  = $SANDBOX_HOME/msb_XXXX/stop
expected = OK
msg      = stopped

To run this example, you have two options:

• Run it directly with test_sandbox:

  $ test_sandbox --versions=Perc5.6.25 --tests=user --user_tests=name_of_your_file.sb
  

• Or create an harness like the ones in the test suite. See for example t/06_test_user_defined.t, which then invokes the test proper, which is check_single_server.sb.

  $ export TEST_VERSION=Perc5.6.25
  $ cat t/06_test_user_defined.t
  use lib './t';
  use Test_Helper;
  test_sandbox( 'test_sandbox --user_test=./t/check_single_server.sb', 3);
  

  In this code, the '3' after the test name is the number of tests expected to run.

If this paradigm is too simple (and I know that sometimes it is) you can write your own plugins in Perl, using as examples the ones in the suite. e.g.:

$ cat t/08_test_single_port_checking.t
use lib './t';
use Test_Helper;
test_sandbox( 'test_sandbox --tests=user --user_test=./t/single_port_checking.sb.pl', 6);

The perl plugin requires, of course, some knowledge of Perl, but they allow a greater flexibility to create your own checks.