Permission denied when creating schema for primary/replicas

[fmarchalemisys]

Bug Report

Q	A
BC Break	no
Version	2.11

Summary

Creating the schema from the command line fails if a primary/replica configuration is used.

php bin/console doctrine:schema:create

SQL commands are sent to the replica instead of the primary db.

The culprit is SchemaTool

It executes CREATE statements with executeQuery($sql) instead of executeStatement($sql).

Current behavior

Creating the schema fails due to CREATE statement being denied on replica. The replica is read only. The schema can't be created on the replica.

How to reproduce

Unless you have two db servers configured as master/slave, the setup can be replicated on a single db server. Create a R/W user to simulate the primary and a R/O user with SELECT permission to simulate the replica.

doctrine:
    dbal:
        driver: 'pdo_mysql'
        server_version: 'mariadb-10.3.11'
        charset: utf8mb4
        url: 'mysql://live_fred:[email protected]:3306/live_fred'
        replicas:
            replica1:
                url: 'mysql://live_fred_ro:[email protected]:3306/live_fred'

Run the command:

php bin/console doctrine:schema:create

In ToolsException.php line 19:
                                                                                                                                                                                                                                            
  Schema-Tool failed with Error 'An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1142 CREATE command denied to user 'live_fred_ro'@'localhost' for table 'core_print_ticket_translation'  
  ' while executing DDL: CREATE TABLE core_print_ticket_translation … 

Expected behavior

SQL CREATE statements must be sent to the primary.

CREATE statements are not queries. SchemaTool must call executeStatement($sql).

[leuchtdiode]

Experienced this issue as well. Schema tool is trying to execute statements on replicas (which are in read-only mode in my setup). It should only be executed on primary only.

[eichie]

+1

[elpoutro]

Same issue with "php bin/console doctrine:schema:update" since update form doctrine 2.3 to 2.8.

[pich]

+1

[greg0ire]

Fixed in https://github.com/doctrine/orm/releases/tag/2.14.2

[pich]

Thanks. Great job @simPod & @greg0ire . I owe you guys a beer :)