Using MySQL transactions with Drupal unit tests

January 19th 2009 —   Comments and   Reactions

Last time I wrote about how to use an entirely separate MySQL database to hold test data for Drupal unit tests, similar to the approach that the Ruby on Rails framework uses for test data. In this post I’ll look at another Rails innovation that can be applied equally well to unit testing with Drupal: running each unit test in a separate database transaction.

First let’s take a quick look at what database transactions are, and how we would use them while running unit tests. In a nutshell, a database transaction is just a way to group a series of SQL operations together and insuring that they are all run together as a single unit – either all of them are executed, or none of them. Let’s take an example. Here are some of the SQL statements Drupal executes when you save a new node in the database:

BEGIN
INSERT INTO node_revisions (nid, uid, title, body, teaser, log, timestamp...
INSERT INTO node (vid, type, language, title, uid, status, created, changed...
UPDATE node_revisions SET nid = 2 WHERE vid = 2
COMMIT

Normally Drupal does not use transactions, but I've inserted the “BEGIN” and “COMMIT” commands here as an example: the transaction starts with the BEGIN command, and ends with the COMMIT command. When MySQL receives the COMMIT command, it allows other database clients (future Drupal HTTP requests, or possibly future command line unit tests) to see the new inserted and updated node data. However, if the transaction were rolled back like this:

BEGIN
INSERT INTO node_revisions (nid, uid, title, body, teaser, log, timestamp...
INSERT INTO node (vid, type, language, title, uid, status, created, changed...
UPDATE node_revisions SET nid = 2 WHERE vid = 2
ROLLBACK

… then none of the changes would be made to the node and node_revisions tables. Instead when MySQL receives the ROLLBACK command it will discard the changes and these tables will appear the same way they did before the transaction started. Therefore, by running each unit test in a separate transaction and rolling it back at the end of each test, we can insure that any changes made to the database by that test are discarded… before the next test is run. Below I’ll explain how to actually do this with PHPUnit and Drupal.

But first let me quickly mention another huge benefit to using transactions with unit test suites: test performance. To learn more about why your tests will run a lot faster using MySQL transactions, read this great article by Mike Clark from the period when Rails 1.0 was released, way back in 2005. What Mike wrote about Rails in 2005 is still true today for Drupal: your unit tests will run faster because fewer SQL statements are required. You won’t need to execute DELETE SQL statements to remove the data after each test since rolling back each transaction accomplishes the same thing.

Now let’s get it to work with Drupal… first let me add a second unit test to my simple PHPUnit test class from last time:

<?php
require_once './includes/phpunit_setup.inc';
class TestDataExampleTest2 extends PHPUnit_Framework_TestCase
{  
  public function create_test_blog_post()
  {
    $node = new stdClass();
    $node->title = "This is a blog post";
    $node->body = "This is the body of the post";
    $node->type = "Story";
    $node->promote = 1;
    node_save($node);
    return $node;
  }
  public function test_there_is_one_post()
  {
    $this->create_test_blog_post();
    $this->assertEquals(1, db_result(db_query("SELECT COUNT(*) FROM {NODE}")));
  }
  public function test_there_are_two_posts()
  {
    $this->create_test_blog_post();
    $this->create_test_blog_post();
    $this->assertEquals(2, db_result(db_query("SELECT COUNT(*) FROM {NODE}")));
  }
}
?>

In this example, I use the phpunit_setup.inc file I wrote in my last post to clear out and setup a new Drupal schema each time I run the tests. Even though I have a clean test database each time I run PHPUnit, without using transactions one of these two unit tests will fail since each one creates its own test data, and assumes no other test data exist in the node table:

$ phpunit TestDataExampleTest2
          modules/test_data_module/TestDataExampleTest2.php 
PHPUnit 3.2.21 by Sebastian Bergmann.
.F
Time: 0 seconds
There was 1 failure:
1) test_there_are_two_posts(TestDataExampleTest2)
Failed asserting that <string:3> matches expected value <integer:2>.
/Users/pat/htdocs/drupal4/modules/test_data_module/TestDataExampleTest2.php:24
FAILURES!
Tests: 2, Failures: 1.

Here the second test fails since the blog post created in the first test is still present in the database. The simplest way to start a new database transaction before each test is run, and to rollback after each test is completed, is with the PHPUnit setup/teardown methods as follows:

public function setup()
{
  db_query("BEGIN");
}
public function teardown()
{
  db_query("ROLLBACK");
}

If you add these functions to the “TestDataExampleTest2” class above both tests should now pass since the ROLLBACK call will delete the nodes created by each test each time teardown is called…

$ phpunit TestDataExampleTest2
          modules/test_data_module/TestDataExampleTest2.php 
PHPUnit 3.2.21 by Sebastian Bergmann.
.F
…
FAILURES!
Tests: 2, Failures: 1.

Wait… what happened? It failed!

The problem is that MySQL does not support transactions using the MyISAM database engine, which is what Drupal uses by default. What we need to do is to convert all of the Drupal MySQL tables to use the InnoDB database engine instead. Unfortunately, there are many implications to using InnoDB vs. MyISAM in Drupal or with any MySQL based application. See "MySQL InnoDB: performance gains as well as some pitfalls" to read more. Specifically, there can be performance issues and degradation when using InnoDB incorrectly, or depending on the type of application you have. Drupal was actually designed and developed with MyISAM in mind, and not InnoDB, although there is some chance this might change for Drupal 7 someday.

Despite all of this, using InnoDB in a test database is a great idea since you will get all of the benefits of isolating tests from each other without having to worry about how InnoDB will effect your production site’s performance. In fact, the performance of your tests will actually be dramatically improved, as Mike Clark explained.

With all of this in mind, I wrote some code to convert the newly created Drupal tables in the test database from MyISAM to InnoDB right after we clear out and reload the test database. Here’s how it works; this code is from phpunit_setup.inc, which I included at the top of my PHPUnit test file:

function enable_mysql_transactions()
{
  convert_test_tables_to_innodb();
  db_query("SET AUTOCOMMIT = 0");  
}
function convert_test_tables_to_innodb()
{
  each_table('convert_to_innodb');  
} 
function each_table($table_callback)
{
  global $db_url;
  $url = parse_url($db_url['test']);
  $database = substr($url['path'], 1);
  $result = db_query("SELECT table_name FROM information_schema.tables
                      WHERE table_schema = '$database'");
  while ($table = db_result($result)) {
    $table_callback($table);
  }
}
function convert_to_innodb($table)
{
  db_query("ALTER TABLE $table ENGINE = INNODB");
}

This iterates over the Drupal tables in the test database and executes ALTER TABLE … ENGINE = INNODB on each one. The SET AUTOCOMMIT=0 command is used to prevent SQL statements from being committed immediately after they are executed, and to allow the InnoDB transactions to work properly.

To repeat and summarize how to employ and separate MySQL test database and transactions in your PHPUnit tests for Drupal, just follow these steps:

  1. Edit settings.php and use an array of two values for $db_url:

    $db_url["default"] = 'mysql://user:password@localhost/drupal;
$db_url["test"] = 'mysql://user:password@localhost/drupal_test';

  2. Create a new test database in MySQL:

    CREATE DATABASE drupal_test DEFAULT CHARACTER SET utf8
                            COLLATE utf8_unicode_ci;
  3. Download and save phpunit_setup.inc somewhere in your Drupal application; for example in the “includes” folder.
  4. Include phpunit_setup.inc at the top of each of your PHPUnit test classes.

  5. Execute your PHPUnit test class from the root folder of your Drupal app:

    $ cd /path/to/your/drupal-site
$ phpunit YourClass modules/your_module/YourClassFileName.php 
PHPUnit 3.2.21 by Sebastian Bergmann.
..
Time: 0 seconds
OK (2 tests)

Here’s my finished test class:

<?php
require_once './includes/phpunit_setup.inc';
class TestDataExampleTest2 extends PHPUnit_Framework_TestCase
{  
  public function setup()
  {
    db_query("BEGIN");
  }
  public function teardown()
  {
    db_query("ROLLBACK");
  }
  public function create_test_blog_post()
  {
    $node = new stdClass();
    $node->title = "This is a blog post";
    $node->body = "This is the body of the post";
    $node->type = "Story";
    $node->promote = 1;
    node_save($node);
    return $node;
  }
  public function test_there_is_one_post()
  {
    $this->create_test_blog_post();
    $this->assertEquals(1, db_result(db_query("SELECT COUNT(*) FROM {NODE}")));
  }
  public function test_there_are_two_posts()
  {
    $this->create_test_blog_post();
    $this->create_test_blog_post();
    $this->assertEquals(2, db_result(db_query("SELECT COUNT(*) FROM {NODE}")));
  }
}
?>