Slaves not used in Symfony 4 with Raw Queries

Question

I'm using Symfony 4 to interface with an existing Master/Slave MySQL setup and am executing queries against the server using raw sql. Raw SQL is the only option at the moment.

I'm using show full processlist; on the DB server to monitor which DB is used, and I am only seeing connections to the master server. It doesn't appear that any of the slaves are ever used.

For reference, I have two dbal connections setup, the default is NOT master/slave, and uses orm mapping. The second is the master/slave which I'm having issues with, and this is the server I'm executing raw sql queries against.

Below is my doctrine.yml:

doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                driver:   pdo_mysql
                host:     "%env(DATABASE_HOST)%"
                dbname:   "db1"
                user:     "%env(DATABASE_USER)%"
                password: "%env(DATABASE_PASS)%"
                charset:  UTF8
            ds:
                driver:   pdo_mysql
                host:     "%env(DS_DATABASE_HOST)%"
                dbname:   "db2"
                user:     "%env(DS_DATABASE_USER)%"
                password: "%env(DS_DATABASE_PASS)%"
                slaves:
                    slave1:
                        host:     "%env(DS_DATABASE_SLAVE1_HOST)%"
                        user:     "%env(DS_DATABASE_USER)%"
                        password: "%env(DS_DATABASE_PASS)%"
                        dbname:   "db2"
                    slave2:
                        host:     "%env(DS_DATABASE_SLAVE2_HOST)%"
                        user:     "%env(DS_DATABASE_USER)%"
                        password: "%env(DS_DATABASE_PASS)%"
                        dbname:   "db2"

orm:
    default_entity_manager: default
    entity_managers:
        default:
            connection: default
            mappings:
                Main:
                    is_bundle: false
                    type: annotation
                    dir: '%kernel.project_dir%/src/Entity/Main'
                    prefix: 'App\Entity\Main'
                    alias: Main
        ds:
            connection: ds

I have configured my entity managers in my services.yml as follows:

# Entity managers
App\Service\Database\MainEntityManager:
    arguments:
        $wrapped: '@doctrine.orm.default_entity_manager'
App\Service\Database\DSEntityManager:
    arguments:
        $wrapped: '@doctrine.orm.ds_entity_manager'

The entity manager (in this case DSEntityManager) is injected into the constructor of a class, then the query is executed as such:

    $result = $this->em->getConnection()->prepare($sql);
    $result->execute($args);

Please let me know if I'm missing any helpful configuration.

Thanks a lot for the help.

Answer 1

Thanks @Cerad for the tip, that got me in the correct direction. Since I was no longer trying to use an entity manager for raw queries that were not mapped to entities, I could work with the connection directly.

I Created a wrapper class which extended MasterSlaveConnection. That worked as long as I was using executeQuery(). Per the docs, that must be used to query the slaves. However, my query required the use of prepare() and query() which both force the master connection.

So inside my new wrapper class I created two new methods, prepareSlave() and querySlave() which do the same as the original; however, they do $this->connect('slave'); instead of $this->connect('master');

Now all my read queries hit slave and everything else hits master.

So here are the following updates I've made to the configuration above to achieve this:

doctrine.yml

        ds:
            driver:   pdo_mysql
            host:     "%env(DS_DATABASE_HOST)%"
            dbname:   "db2"
            user:     "%env(DS_DATABASE_USER)%"
            password: "%env(DS_DATABASE_PASS)%"
            wrapper_class: "%env(DS_DATABASE_PASS)%"
            slaves: App\Service\Database\DSWrapper
                slave1: ...

services.yml

# DBAL connections
App\Service\Database\DSWrapper: '@doctrine.dbal.ds_connection'

My new wrapper class

class DSWrapper extends MasterSlaveConnection
{
public function prepareSlave($statement)
{
    $this->connect('slave');

    try {
        $stmt = new Statement($statement, $this);
    } catch (\Exception $ex) {
        throw DBALException::driverExceptionDuringQuery($this->_driver, $ex, $statement);
    }

    $stmt->setFetchMode($this->defaultFetchMode);

    return $stmt;
}

public function querySlave()
{
    $this->connect('slave');

    $args = func_get_args();

    $logger = $this->getConfiguration()->getSQLLogger();
    if ($logger) {
        $logger->startQuery($args[0]);
    }

    $statement = $this->_conn->query(...$args);

    if ($logger) {
        $logger->stopQuery();
    }

    return $statement;
}
}

So now if I need to execute a query which would normally require the use of prepare() and query(), I instead use prepareSlave() and querySlave().