🐥 Caveat with MySQL full-text search and testing

Today, I was writing a test for a Laravel application that uses MySQL full-text search. I was testing a search query that uses the MATCH and AGAINST operators. The test was failing as no results were returned from the search.

Running the same code outside the context of the test did work correctly.

The search I was doing looked like this:

public static function search($query)
{
    $results = self::whereRaw("Match(name,body) AGAINST('$query')")->get();

    return self::processSearchResults($results, $query);
}

The reason why is not directly clear, but it is related to the way MySQL handles full-text search queries versus database transactions. As the MySQL manual explains it:

InnoDB full-text indexes have special transaction handling characteristics due its caching and batch processing behavior. Specifically, updates and insertions on a full-text index are processed at transaction commit time, which means that a full-text search can only see committed data. The following example demonstrates this behavior. The full-text search only returns a result after the inserted lines are committed.

So, if you happen to use the trait RefreshDatabase in your test, the full-text search will not work as expected. This trait uses database transascions to reset the database to its original state after each test.

To make the test work, you can use trait DatabaseMigrations instead of RefreshDatabase. This trait resets the complete database after each test. This is quite a bit slower though, so you might want to consider using a separate test for the full-text search functionality.

use Illuminate\Foundation\Testing\DatabaseMigrations;
use Tests\TestCase;

class FullTextSearchTest extends TestCase
{
    use DatabaseMigrations; // Reset the complete database after each test

    public function testFullTextSearch()
    {
        // Your test code here
    }
}