DatabaseTruncation fails when DB connection uses table prefixes

[bsacharski]

Laravel Version

10.21.1

PHP Version

8.2.9

Database Driver & Version

MariaDB 10.4 via docker

Description

When using a DatabaseTruncation trait, the tests will fail if a DB connection is configured to prefix all the tables with following errror:

Illuminate\Database\QueryException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'laravel.foo_foo_failed_jobs' doesn't exist (Connection: mysql, SQL: select exists(select * from `foo_foo_failed_jobs`) as `exists`)

It would seem that when trying to check which tables to truncate, the trait loads table names (which include prefix) via DoctrineSchemaManager and then calls\Illuminate\Database\Query\Builder::truncate which appends the prefix once again.

As a quick and dirty workaround I've implemented custom trait, that overrides \Illuminate\Foundation\Testing\DatabaseTruncation::truncateTablesForConnection method:

<?php

namespace App\Tests;

use Illuminate\Database\Connection;
use Illuminate\Database\ConnectionInterface;
use Illuminate\Foundation\Testing\DatabaseTruncation as BaseDatabaseTruncation;

/**
 * This trait is a workaround for a bug, where DatabaseTruncation trait would attempt to truncate
 * a table with prefix being applied twice (i.e. foo_foo_TableName when connection prefix is set to 'foo')
 */
trait DatabaseTruncation
{
    use BaseDatabaseTruncation;

    protected function truncateTablesForConnection(ConnectionInterface $connection, ?string $name): void
    {
        $dispatcher = $connection->getEventDispatcher();

        $connection->unsetEventDispatcher();

        collect(static::$allTables[$name] ??= $connection->getDoctrineSchemaManager()->listTableNames())
            ->when(
                property_exists($this, 'tablesToTruncate'),
                fn ($tables) => $tables->intersect($this->tablesToTruncate),
                fn ($tables) => $tables->diff($this->exceptTables($name))
            )
            ->map(fn ($table) => self::SanitizeTableName($table, $connection)) // THIS IS WHAT HAS BEEN ADDED!!!
            ->filter(fn ($table) => $connection->table($table)->exists())
            ->each(fn ($table) => $connection->table($table)->truncate());

        $connection->setEventDispatcher($dispatcher);
    }

    private static function SanitizeTableName(string $tableName, ConnectionInterface $connection): string
    {
        /**
         * We need to strip the table prefix from tableName, because doctrineSchemaManager
         * returns `foo_TableName` instead of `TableName`.
         * Without this, laravel will try to truncate `foo_foo_TableName` as it applies prefix once again
         * when it compiles a query
         */
        if (!$connection instanceof Connection) {
            // we don't have access to prefix - return $tableName as it was
            return $tableName;
        }

        $prefix = $connection->getTablePrefix();
        // replace only
        $pos = strpos($tableName, $prefix);
        if ($pos !== 0) {
            // we do not want to replace substring that looks like prefix but is not one
            return $tableName;
        }

        return substr_replace($tableName, '', $pos, strlen($prefix));
    }
}

The fix unfortunately relies on concrete implementation of ConnectionInterface to access the getTablePrefix method.

Steps To Reproduce

1. Set up a database connection with prefix set to foo_
2. Add doctrine/dbal package for tests to work
3. Create a test class with two scenarios
4. Add DatabaseTruncation trait to newly created test class
5. Start tests

See bsacharski/truncate-bug-report@4008a7a for a commit with changes that trigger the issue.

Expected outcome: tests should simply pass
Actual outcome: tests fail with following error:

Illuminate\Database\QueryException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'laravel.foo_foo_failed_jobs' doesn't exist (Connection: mysql, SQL: select exists(select * from `foo_foo_failed_jobs`) as `exists`)
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Database/Connection.php:416
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Database/Connection.php:788
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Database/Connection.php:755
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Database/Connection.php:407
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php:3093
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Foundation/Testing/DatabaseTruncation.php:88
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Collections/Arr.php:869
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Collections/Collection.php:387
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Foundation/Testing/DatabaseTruncation.php:88
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Foundation/Testing/DatabaseTruncation.php:64
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php:425
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Foundation/Testing/DatabaseTruncation.php:63
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Collections/Traits/EnumeratesValues.php:236
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Foundation/Testing/DatabaseTruncation.php:60
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Foundation/Testing/DatabaseTruncation.php:39
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:134
/Users/bartlomiej.sacharski/tmp/truncate-bug-report/vendor/laravel/framework/src/Illuminate/Foundation/Testing/TestCase.php:95

Caused by
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'laravel.foo_foo_failed_jobs' doesn't exist

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!