Articles, Bazaar, Drizzle, Launchpad, MySQL

Enabling and Fixing Drizzle Test Cases

When Brian began the work on refactoring the MySQL 6.0 Server source code into what has now become the Drizzle Project, a number of code pieces were removed, including some major MySQL functionality such as stored procedures, server-side prepared statements, SQL Mode, some legacy code, and a variety of data types. The goal, of course, was to reduce the server code base down to a more streamlined and eventually modular kernel.

Of course, that vision is great, but it’s got some side effects! One of those side effects is a dramatic reduction in the number of test cases that pass the test suite in their current form, and an increase in the number of tests that have been disabled. I re-enabled and fixed a few tests yesterday, but as of this writing, there are only 54 of 408 tests currently passing in the test suite.

This is to be expected. You can’t just go and strip a huge chunk of the parser and functionality out of the server and expect the original test suite to run without problems 🙂 So, Brian disabled some tests while removing code sections anticipating that these tests would eventually be re-enabled and any regressions fixed. Well, we are now at that point. With Brian’s work this week to remove the last vestiges of non-UTF8 character set support, fixing and re-enabling disabled tests in the test suite is now a high priority project. Luckily, this is a project which almost anyone — even non-coders — can get involved in and make a difference.

This article will explain the process of running the Drizzle test suite and identifying test cases which can be re-enabled or should be fixed. We’ll focus on stuff that you can help with as a contributor who wants to start getting involved in Drizzle and making an impact without having C/C++ coding experience. If you haven’t caught my previous articles on using Launchpad.net for code management, I’d suggest reading those now. In addition, although we won’t be doing C/C++ coding, you’ll need a build environment established in order to properly run the test suite. So, I’d also suggest reading my article on setting up a C/C++ development environment for Drizzle.

The Test Suite Basics

NOTE: This section describes the Drizzle test suite. However, if you are contributing to the MySQL Server project, the instructions in this section are exactly the same if you are working with the MySQL Server. Just change dtr to mtr. 🙂

The Drizzle test suite is a composite of a main Perl script — tests/test-run.pl — and a couple other tools. After you have built Drizzle with the standard build process, you will see a program in the /tests source directory called dtr. This is the test suite runner. When you issue the command:


make test

This test runner is called with some command-line options and a list of tests to run. You can verify this behaviour by looking at /tests/Makefile.am and seeing the actual command for the test make target.

The general form for running a test case is the following:


cd tests
./dtr $testname1 $testname2 ... $testnameN

There are a number of command-line options that the test suite runner accepts, and I’ll cover a smattering of them in this article.

Running a Test

So, how do you know what the names of the tests are? Good question! 🙂 In the /tests directory, you will find a /t directory which contains all the test cases contained in the main test suite. The main test suite are tests not specific to a functionality of the server like replication. For instance, the /t/select.test is the test case in the main test group corresponding to testing of the SELECT syntax and functionality. Other test cases for specific functional pieces can be found in /tests/suite. Running this t/select.test test case through the runner, we’d do this:


./dtr select

Note that you do not need to add the .test suffix. You should see results similar to the following:

[505][jpipes@serialcoder: /home/jpipes/repos/drizzle/trunk/tests]$ ./dtr select
Logging: ./dtr select
<snip>
MySQL Version 7.0.0
Using dynamic switching of binlog format
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Saving snapshot of installed databases
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.select                    [ pass ]           9673
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers were restarted 1 times
Spent 9.673 of 21 seconds executing testcases

As you can see, the test suite fires up a Drizzle server, loads the test file and performs the tests contained in the file. The tests in the file generally consist of SQL statements that are executed against one or more servers, but they can also be commands such as creating a new connection, logging output, and other things. For this article, we’ll be focusing on the SQL command tests. In a followup article, I may highlight some of the other test-case commands available to you.

Failing Test Cases

Well, it’s all fine and dandy if a test case succeeds like in the example above, but like I mentioned in the introduction of this article, we’re focused on the test cases that aren’t succeeding and getting these test cases to succeed! So, how do we find those tests which are failing? One method is to look at the Drizzle Build Farm and track down failures occurring in the test runs. Another way is to simply run a series of tests and see what fails. For simplicity’s sake, I’ve done a little research already and know a number of tests that are failing. So, we’ll go ahead and take a look at a test case that I know needs some TLC.

The test case I’ve chosen is the func_math test from the main test suite. It’s small and provides a good example of how we can work to fix up the failures. Here is what I get when running this test:

[505][jpipes@serialcoder: tests]$ ./dtr func_math
Logging: ./dtr func_math
<snip>
MySQL Version 7.0.0
Using dynamic switching of binlog format
Using MTR_BUILD_THREAD      = 0
Using MASTER_MYPORT         = 9306
Using MASTER_MYPORT1        = 9307
Using SLAVE_MYPORT          = 9308
Using SLAVE_MYPORT1         = 9309
Using SLAVE_MYPORT2         = 9310
Killing Possible Leftover Processes
Removing Stale Files
Creating Directories
Saving snapshot of installed databases
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.func_math                 [ fail ]

drizzletest: At line 134: query 'create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb 
default charset=utf8' failed: 1064: You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 'charset=utf8' at line 1

The result from queries just before the failure was:
< snip >
656	405
122	405
645	405
INSERT INTO t1 VALUES (3);
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
FROM t1;
CAST(RAND(2) * 1000 AS UNSIGNED)	CAST(RAND(a) * 1000 AS UNSIGNED)
656	405
122	405
645	405
858	656
354	906
SELECT CAST(RAND(2) * 1000 AS UNSIGNED), CAST(RAND(a) * 1000 AS UNSIGNED) 
FROM t1 WHERE a = 1;
CAST(RAND(2) * 1000 AS UNSIGNED)	CAST(RAND(a) * 1000 AS UNSIGNED)
656	405
122	405
645	405
DROP TABLE t1;
create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8;

More results from queries before failure can be found in 
/home/jpipes/repos/drizzle/trunk/tests/var/log/func_math.log

Stopping All Servers
Restoring snapshot of databases
Resuming Tests

-------------------------------------------------------
Stopping All Servers
Failed 1/1 tests, 0.00% were successful.

The log files in var/log may give you some hint
of what went wrong.
If you want to report this error, please read first the documentation at
http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html
The servers were restarted 1 times
Spent 0.000 of 7 seconds executing testcases

mysql-test-run in default mode: *** Failing the test(s): main.func_math
mysql-test-run: *** ERROR: there were failing test cases

As you can see, the test fails and outputs the source of the failure.

Fixing a Broken Test

Now that we’ve identified a failing test, we need to follow a process in order to fix it. The process you should follow is this:

  1. Make a change to the test case file
  2. Re-run the test through dtr using the --record option
  3. If any failure occurs, go back to #1
  4. Once the test succeeds under the --record option, a test result file will be written to the /tests/r/ directory. We’ll need to bzr commit the changes to the test and the result file and push to a branch on Launchpad.
  5. Edit tests/Makefile.am and ensure the newly-passing test is included in the make test target

In this case, the failure is due to a mere syntax issue. We’ve removed character set support and standardized entirely on UTF8, and so the support in the parser syntax for the phrase DEFAULT CHARSET=utf8 is gone. To fix this test, we need to remove the pieces of the old MySQL syntax which are no longer supported in Drizzle.

So, we pop open our favorite editor and open up the /tests/t/func_math.test file. Go ahead and remove all instances of default charset=utf8. And then re-run the test with the --record. You should see the following:

[508][jpipes@serialcoder: tests]$ ./dtr --record func_math
<snip>
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.func_math                 [ fail ]

drizzletest: At line 160: query 'create table t1
(f1 varchar(32) not null,
f2 smallint(5) unsigned not null,
f3 int(10) unsigned not null default '0')
engine=myisam' failed: 1064: You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use near '(5) unsigned not null,
f3 int(10) unsigned not null default '0')
engine=myisam' at line 2
<snip>

Again, it looks like we’ve run into another syntax problem. Above, the test case contains the old ZEROFILL syntax, which allows you to specify a number in parentheses after an integer data type. This functionality, a legacy from Unireg times, is not supported in Drizzle. So, we must remove it. After removing the (XX) ZEROFILL syntax from the CREATE TABLE definitions in the test case file, I re-run the test:

<snip>
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.func_math                 [ fail ]

drizzletest: At line 230: query 'CREATE TABLE t1(a SET('a','b','c'))' failed: 1064: 
You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'SET('a','b','c'))' at line 1
<snip>

Once again, we’ve run into a failure. This time, it’s because of the SET data type. This data type has been removed from Drizzle. So, we must remove it from the test case here. After doing so, I re-run the test case, and finally we see a success:

[509][jpipes@serialcoder: tests]$ ./dtr --record func_math
Logging: ./dtr --record func_math
<snip>
=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

main.func_math                 [ pass ]            107
-------------------------------------------------------
Stopping All Servers
All 1 tests were successful.
The servers were restarted 1 times
Spent 0.107 of 8 seconds executing testcases

Cool. Looks good. Now we edit tests/Makefile.am and add the newly successful test to the make test target.


cd tests
vim Makefile.am

Here is what the section in the Makefile.am looks like, with the bolded line being the line I add for our newly successful test:

test-drizzle:
	  $(PERL) -I$(top_srcdir)/tests/lib \
		$(top_srcdir)/tests/test-run.pl --fast --reorder --force \
        1st \
        alter_table \
        bench_count_distinct \
	bulk_replace \
	comment_column2 \
	comments \
	consistent_snapshot \
        count_distinct \
        count_distinct2 \
        count_distinct3 \
	create_select_tmp \
        ctype_filename \
        delete \
        distinct \
	drizzleslap \
        endspace \
        flush2 \
        func_equal \
        func_group_innodb \
        func_isnull \
        func_like \
	func_math \
        greedy_optimizer \
        group_min_max_innodb \
        heap_auto_increment \

Alright, cool. OK, now we simply need to verify our test case and result file changes, edit our make test target, and commit our changes. First, verification:

[511][jpipes@serialcoder: tests]$ bzr status
modified:
  tests/Makefile.am
  tests/r/func_math.result
  tests/t/func_math.test

Looks good. The final step is committing our work and then pushing to a code branch on Launchpad.net. Below, I am pushing to the branch lp:~drizzle-developers/drizzle/enable-tests, which is a team branch used to push code for the various test cleanups.

[514][jpipes@serialcoder: tests]$ bzr commit Makefile.am t/func_math.test \
> r/func_math.result -m "Fixed syntax errors in func_math test and re-enable \
> the test in the make test target"
Committing to: /home/jpipes/repos/drizzle/trunk/
modified tests/Makefile.am
modified tests/r/func_math.result
modified tests/t/func_math.test
Committed revision 405.                                                                                                                  
[515][jpipes@serialcoder: tests]$ bzr push lp:~drizzle-developers/drizzle/enable-tests
Pushed up to revision 405.

And that’s that! Test fixed, case, result and Makefile.am edited, and changes committed.