WordPress Unit Testing – Cannot Create Tables

You’ve just discovered an important feature of the core test suite: it forces any tables created during the test to be temporary tables.

If you look in the WP_UnitTestCase::setUp() method you’ll see that it calls a method called start_transaction(). That start_transaction() method starts a MySQL database transaction:

        function start_transaction() {
                global $wpdb;
                $wpdb->query( 'SET autocommit = 0;' );
                $wpdb->query( 'START TRANSACTION;' );
                add_filter( 'query', array( $this, '_create_temporary_tables' ) );
                add_filter( 'query', array( $this, '_drop_temporary_tables' ) );
        }

It does this so that any changes that your test makes in the database can just be rolled back afterward in the tearDown() method. This means that each test starts with a clean WordPress database, untainted by the prior tests.

However, you’ll note that start_transaction() also hooks two methods to the 'query' filter: _create_temporary_tables and _drop_temporary_tables. If you look at the source of these methods, you’ll see that they cause any CREATE or DROP table queries to be for temporary tables instead:

        function _create_temporary_tables( $query ) {
                if ( 'CREATE TABLE' === substr( trim( $query ), 0, 12 ) )
                        return substr_replace( trim( $query ), 'CREATE TEMPORARY TABLE', 0, 12 );
                return $query;
        }

        function _drop_temporary_tables( $query ) {
                if ( 'DROP TABLE' === substr( trim( $query ), 0, 10 ) )
                        return substr_replace( trim( $query ), 'DROP TEMPORARY TABLE', 0, 10 );
                return $query;
        }

The 'query' filter is applied to all database queries passed through $wpdb->query(), which dbDelta() uses. So that means when your tables are created, they are created as temporary tables.

So in order to list those tables, I think you’d have to show temporary tables instead: $sql = "SHOW TEMPORARY TABLES LIKE '%'";

Update: MySQL doesn’t allow you to list the temporary tables like you can with the regular tables. You will have to use another method of checking if the table exists, like attempting to insert a row.

But why does the unit test case require temporary tables to be created in the first place? Remember that we are using MySQL transactions so that the database stays clean. We don’t want to ever commit the transactions though, we want to always roll them back at the end of the test. But there are some MySQL statements that will cause an implicit commit. Among these are, you guessed it, CREATE TABLE and DROP TABLE. However, per the MySQL docs:

CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used.

So to avoid an implicit commit, the test case forces any tables created or dropped to be temporary tables.

This is not a well documented feature, but once you understand what is going on it should be pretty easy to work with.