MySQL warning: Deprecated usage of mixed qualified and unqualified column names.

[wkania]

Related to PR.
My current setup consists of one database with the central schema and additional schemas (one per client). Until now, I was able to define entities in the code using the central schema. The rest did not have a defined schema — each client had a different one — and there is a single code repository.

Now, when I run doctrine:schema:validate, I get the following warning:

User Deprecated: Using unqualified names to create or reference objects in a schema that uses qualified names and lacks a default namespace configuration is deprecated.
(Schema.php:254 called by Schema.php:145, [Doctrine PR doctrine/dbal#6677](https://github.com/doctrine/dbal/pull/6677#user-content-unqualified-names), package doctrine/dbal)

1. How can I fix this, and is it even possible?
2. I understand that the PR fixes problems with SQL Server, but it breaks other configurations. Is there a way to maybe add a parameter that would allow mixing schemas as before?

My config and code:

// doctrine.yaml
doctrine:
  dbal:
    default_connection: default
    connections:
      default:
        host: '%database_host%'
        port: '%database_port%'
        dbname: '%database_name%'
        user: '%database_user%'
        password: '%database_password%'
        server_version: '8.0.32'
        charset: utf8mb4
        use_savepoints: true
        schema_manager_factory: App\Component\Doctrine\CustomSchemaManagerFactory
        default_table_options:
          charset: utf8mb4
          collate: utf8mb4_unicode_ci

// services.yaml
services:
  App\Component\Doctrine\CustomSchemaManager:
    autowire: true
    arguments:
      - '@doctrine.dbal.default_connection'
    tags:
      - { name: 'doctrine.dbal.schema_manager', priority: 1 }

  App\Component\Doctrine\CustomSchemaManagerFactory:
    autowire: true

// CustomSchemaManagerFactory.php
namespace App\Component\Doctrine;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Platforms\AbstractMySQLPlatform;
use Doctrine\DBAL\Schema\AbstractSchemaManager;
use Doctrine\DBAL\Schema\DefaultSchemaManagerFactory;
use Doctrine\DBAL\Schema\SchemaManagerFactory;

final readonly class CustomSchemaManagerFactory implements SchemaManagerFactory
{
    private SchemaManagerFactory $defaultFactory;

    public function __construct()
    {
        $this->defaultFactory = new DefaultSchemaManagerFactory();
    }

    public function createSchemaManager(Connection $connection): AbstractSchemaManager
    {
        $platform = $connection->getDatabasePlatform();
        if ($platform instanceof AbstractMySQLPlatform) {
            return new CustomSchemaManager($connection, $platform);
        }

        return $this->defaultFactory->createSchemaManager($connection);
    }
}

// CustomSchemaManager.php
namespace App\Component\Doctrine;

use Doctrine\DBAL\Exception;
use Doctrine\DBAL\Exception\DatabaseRequired;
use Doctrine\DBAL\Schema\MySQLSchemaManager;
use Doctrine\DBAL\Schema\Table;

class CustomSchemaManager extends MySQLSchemaManager
{
    public function listTables(): array
    {
        $database = $this->getDatabase(__METHOD__);

        $tables = $this->listTablesForDatabase($database);
        $centralTables = $this->listTablesForDatabase('central');
        /** @var Table $centralTable */
        foreach ($centralTables as $centralTable) {
            $name = 'central.'.$centralTable->getName();
            $tables[$name] = new Table(
                $name,
                $centralTable->getColumns(),
                $centralTable->getIndexes(),
                $centralTable->getUniqueConstraints(),
                $centralTable->getForeignKeys(),
                $centralTable->getOptions(),
            );
        }

        return $tables;
    }

    private function listTablesForDatabase(string $database): array
    {
        $tableColumnsByTable = $this->fetchTableColumnsByTable($database);
        $indexColumnsByTable = $this->fetchIndexColumnsByTable($database);
        $foreignKeyColumnsByTable = $this->fetchForeignKeyColumnsByTable($database);
        $tableOptionsByTable = $this->fetchTableOptionsByTable($database);

        $filter = $this->connection->getConfiguration()->getSchemaAssetsFilter();
        $tables = [];

        foreach ($tableColumnsByTable as $tableName => $tableColumns) {
            if (!$filter($tableName)) {
                continue;
            }

            $tables[] = new Table(
                $tableName,
                $this->_getPortableTableColumnList($tableName, $database, $tableColumns),
                $this->_getPortableTableIndexesList($indexColumnsByTable[$tableName] ?? [], $tableName),
                [],
                $this->_getPortableTableForeignKeysList($foreignKeyColumnsByTable[$tableName] ?? []),
                $tableOptionsByTable[$tableName] ?? [],
            );
        }

        return $tables;
    }

    /** @throws Exception */
    private function getDatabase(string $methodName): string
    {
        $database = $this->connection->getDatabase();

        if (null === $database) {
            throw DatabaseRequired::new($methodName);
        }

        return $database;
    }
}

[derrabus]

I don't really understand what you're trying to do here. You're merging the tables of two different schemas when the schema manager is asked for all tables in the current database? That API is used a lot by the DBAL and ORM internally and we can't guarantee that your hack won't break in a future bugfix release.

[wkania]

@derrabus

Yes, I fully understand that this hack may break in a future release — in fact, it already broke a few versions ago.

This workaround is only used in the development environment. It allows us to:

run bin/console make:migration and detect diffs across both schemas,
run bin/console doctrine:schema:validate to check mappings and ensure synchronization across both schemas.

The trick essentially makes Doctrine believe that all tables belong to a single schema, while still preserving the central prefix in table names.

1. This is the main reason for using it.
2. The problem is that in production, MySQL creates cross-database joins between the two schemas. The current setup requires qualified names for both schemas, but why only defining one schema statically. The second one is dynamic, and as far as I know, Doctrine doesn't provide any mechanism to set the schema name dynamically.

[derrabus]

Doctrine doesn't provide any mechanism to set the schema name dynamically

What do you mean by "dynamic"? Do you change the database after having opened the entity manager?

[wkania]

What do you mean by "dynamic"? Do you change the database after having opened the entity manager?

During runtime.

This change forcing me to to set schema name in the default schema. Default schema can have per client names. By default mysql return current schema based on connection params SELECT database().

I was wondering if it’s possible to set the schema name by default, e.g., using an ENV variable. Before the warning: "User Deprecated: Using unqualified names to create or reference objects in a schema that uses qualified names and lacks a default namespace configuration is deprecated."

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'users')]
class User

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'locations', schema: 'central')]
class Location

[derrabus]

What do you mean by "dynamic"? Do you change the database after having opened the entity manager?

During runtime.

When during runtime?

I was wondering if it’s possible to set the schema name by default, e.g., using an ENV variable.

There's nothing "dynamic" in reading a piece of configuration from an environment variable, is it? So yeah, you can totally do that.