Using multiple entity managers and connections in Symfony with different users-CodePudding

I am creating a project (Symfony 5.4 LTS / PHP 8) and I will need to use two different Entity Managers (and two doctrine connections). The problem is, both need to access two different databases (although in the same host), and as such, they have different users to access them. I cannot use the same user.

I have both users declared in my .env file in different variables, so that shouldn't be a problem.

I am trying something like this:

connections:
        default:
            url: '%env(resolve:DATABASE_URL)%'
            driver: 'xxx_mysql'
            # server_version: 'mariadb-XX.X.XX'
            charset: utf8mb4
        second:
            url: '%env(resolve:DATABASE_URL_SECOND)%'
            driver: 'xxx_mysql'
            # server_version: 'mariadb-XX.X.XX'
            charset: utf8mb4
            mapping_types:
                enum: string

With no luck. If I call the second manager with $this->doctrine->getManager('second') works like a charm, but the default one, is trying to access it's database with the user from the second one, and as such, giving an access denied prompt.

The DATABASE_URL variable makes use of DATABASE_USER as it should, as I have checked from the controller.

I have already checked the documentation here: https://symfony.com/doc/current/doctrine/multiple_entity_managers.html

Whole doctrine.yaml file:

doctrine:
  dbal:
    default_connection: default
    connections:
        default:
            url: '%env(resolve:DATABASE_URL)%'
            driver: 'pdo_mysql'
            # server_version: 'mariadb-xx.x.xx'
            charset: utf8mb4
        second:
            url: '%env(resolve:DATABASE_URL_SECOND)%'
            driver: 'pdo_mysql'
            # server_version: 'mariadb-xx.x.xx'
            charset: utf8mb4
            mapping_types:
                enum: string
           
  orm:
    default_entity_manager: default
    auto_generate_proxy_classes: true
    entity_managers:
        default:
            connection: default
            naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
            # auto_mapping: true
            mappings:
                App:
                    is_bundle: false
                    type: annotation
                    dir: '%kernel.project_dir%/src/Entity/Main/'
                    prefix: 'App\Entity\Main'
                    alias: Main
        second:
            connection: second
            naming_strategy: doctrine.orm.naming_strategy.underscore_number_aware
            # auto_mapping: true
            mappings:
                App:
                    is_bundle: false
                    type: annotation
                    dir: '%kernel.project_dir%/src/Entity/Second/'
                    prefix: 'App\Entity\Second'
                    alias: Second

Simplified controller:

private ObjectManager $entityManager;

public function __construct(Connection $connection,SessionInterface $session, ManagerRegistry $doctrine){
    $this->doctrine = $doctrine;
}

 /* Route blah blah blah */
 public function index(LoggerInterface $logger, Request $request) : Response {
  $this->logger = $logger;
  $this->initialize($request, /* Some vars for the log setters */);
  
  /* It would continue and finally return a response but code fails in the initialize method */
}

public function initialize(Request $request, /* Some vars for the Log setters */): void {
    $defaultDatabase= $this->doctrine->getManager();
    
    $this->log = new Log();
    /* Some setters here */

    $defaultDatabase->persist($this->log); // <- Code failing here
    $defaultDatabase->flush();
}

CodePudding user response：

Have you tried remove user line from doctrine.yaml And just use Database URL with user included in your .env file :

DATABASE_URL=mysql://[user]:[password]@localhost:3306/[database]
DATABASE_URL_SECOND=mysql://[user2]:[password2]@localhost:3306/[database2]

CodePudding user response：

For some reason, deleting and recreating the user in the server worked, but I still do not understand why, neither why did it have such a weird behaviour, so I'll leave this open in case someone can give us some insight.