Question 409110 in PHP Question posted in
Official manual can be found here.

Php – "Table not found" error when querying second database connection

I’m using Symfony 7.1.3 with Doctrine DBAL 4.1 and have configured multiple database connections in my doctrine.yaml. My default connection (npreports) works fine, but when I try to run queries on a second connection (npfactory), I encounter the following error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'npreports.scheduled_tasks' doesn't exist

The error seems to indicate that it’s still trying to use the default connection (npreports) even though the query is meant for npfactory.

Here’s my database configuration in doctrine.yaml:

doctrine:
    dbal:
        default_connection: npreports
        connections:
            npreports:
                url: '%env(resolve:DATABASE_REPORTS_URL)%'
            npfactory:
                url: '%env(resolve:DATABASE_FACTORY_URL)%'

I inject the npfactory connection in my repository and controller like this:

public function __construct(Connection $npfactory)
{
    $this->npfactory = $npfactory;
}

Environment:

  • Symfony: 7.1.3
  • Doctrine DBAL: 4.1.0
  • Doctrine ORM: 3.2.2

What I’ve tried:

  • I can successfully query npreports without any issues.
  • Changing the default connection to npfactory works, but this is not ideal as I need both connections.
  • I’ve confirmed that the table scheduled_tasks exists in the npfactory database.

Is there something wrong with how Symfony is managing the database connections, or am I missing something with Doctrine’s configuration? How can I ensure that the correct connection is being used for each query?

enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was able to resolve the issue by explicitly injecting the correct database connection using an alias in services.yaml. Here's how I did it:

    services:
    AppRepositoryFactoryRepository:
        arguments:
            $npfactory: '@doctrine.dbal.npfactory_connection'

    While this solves the problem, I'm not entirely happy with this approach. Having to explicitly define the connection in the service container feels cumbersome, especially for cases where flexibility with multiple connections should be more seamless.


  2. 0

    I found a solution that meets my requirements for simplicity and flexibility without additional configuration in services.yaml. This approach is more maintainable and allows easy addition of new database connections. Here’s what I did:

    Improved Solution

    1. I kept my current doctrine.yaml configuration:
    doctrine:
    dbal:
        default_connection: npreports
        connections:
            npreports:
                url: '%env(resolve:DATABASE_REPORTS_URL)%'
            npfactory:
                url: '%env(resolve:DATABASE_FACTORY_URL)%'
    1. I created a ConnectionResolver service:
    namespace AppService;

use DoctrineDBALConnection;
use SymfonyComponentDependencyInjectionContainerInterface;

class ConnectionResolver
{
    private $container;

    public function __construct(ContainerInterface $container)
    {
        $this->container = $container;
    }

    public function getConnection(string $name): Connection
    {
        return $this->container->get("doctrine.dbal.{$name}_connection");
    }
}
    1. I configured the ConnectionResolver in services.yaml:
    services:
    AppServiceConnectionResolver:
        arguments: ['@service_container']
    1. I modified my repository classes as follows:
    namespace AppRepository;

use AppServiceConnectionResolver;
use DoctrineDBALConnection;

class FactoryRepository
{
    private Connection $npfactory;
    private Connection $npreports;

    public function __construct(ConnectionResolver $connectionResolver)
    {
        $this->npfactory = $connectionResolver->getConnection('npfactory');
        $this->npreports = $connectionResolver->getConnection('npreports');
    }

    // My methods here...
}

    With this approach, I achieved the following:

    1. I don’t need to add additional entries in services.yaml for each new repository class.
    2. The syntax in my repository classes remains almost identical to my original desired example.
    3. New database connections can be easily added in doctrine.yaml without requiring further changes to the configuration or repository classes.
    4. I retain the flexibility to use different connections in different repositories.

    This solution required only minimal changes to my existing code while providing the desired flexibility and simplicity. I can continue to use my repository classes as I’m accustomed to, with the only difference being that I inject the ConnectionResolver instead of the direct Connection.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search