Setup slaves for each shard

[aleksot]

Hi, is it possible to make slave configuration for each shard of connection?

For example (using Symfony 4.2)

I have very big table "users". I want to separate this table by 10 instances.

Structure will be such this:
Database "users_0", table "users_0"
Database "users_1", table "users_1"
Database "users_2", table "users_2"
Database "users_3", table "users_3"
Database "users_4", table "users_4"
Database "users_5", table "users_5"
Database "users_6", table "users_6"
Database "users_7", table "users_7"
Database "users_8", table "users_8"
Database "users_9", table "users_9"

Users will be spreaded by remainder of the division their ids (user with id 10 - to "users_0" database, user with id 106 - to "users_6" database, etc.).

config/packages/doctrine.yaml currently this:

doctrine:
    dbal:
        # configure these for your database server
        #driver: 'pdo_mysql'
        #server_version: '5.7'
        #charset: utf8mb4
        #default_table_options:
        #    charset: utf8mb4
        #    collate: utf8mb4_unicode_ci

        #url: '%env(resolve:DATABASE_URL)%'

        connections:
            default:
                charset: UTF8
                dbname: '%env(resolve:DATABASE_NAME_COMMON)%'
                driver: '%env(resolve:DATABASE_DRIVER_COMMON)%'
                host: '%env(resolve:DATABASE_HOST_COMMON)%'
                password: '%env(resolve:DATABASE_PASSWORD_COMMON)%'
                port: '%env(resolve:DATABASE_PORT_COMMON)%'
                user: '%env(resolve:DATABASE_USER_COMMON)%'

            user:
                shard_choser_service: App\Sharding\ShardChooser
                shard_manager_class: App\Sharding\ShardManager
                charset: UTF8
                dbname: '%env(resolve:DATABASE_NAME_USER_0)%'
                driver: '%env(resolve:DATABASE_DRIVER_USER_0)%'
                host: '%env(resolve:DATABASE_HOST_USER_0)%'
                password: '%env(resolve:DATABASE_PASSWORD_USER_0)%'
                port: '%env(resolve:DATABASE_PORT_USER_0)%'
                user: '%env(resolve:DATABASE_USER_USER_0)%'
                shards:
                    shard0:
                        id: 1
                        host: '%env(resolve:DATABASE_HOST_USER_0)%'
                        user: '%env(resolve:DATABASE_USER_USER_0)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_0)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_0)%'
                        charset: UTF8
                    shard1:
                        id: 2
                        host: '%env(resolve:DATABASE_HOST_USER_1)%'
                        user: '%env(resolve:DATABASE_USER_USER_1)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_1)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_1)%'
                        charset: UTF8
                    shard2:
                        id: 3
                        host: '%env(resolve:DATABASE_HOST_USER_2)%'
                        user: '%env(resolve:DATABASE_USER_USER_2)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_2)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_2)%'
                        charset: UTF8
                    shard3:
                        id: 4
                        host: '%env(resolve:DATABASE_HOST_USER_3)%'
                        user: '%env(resolve:DATABASE_USER_USER_3)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_3)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_3)%'
                        charset: UTF8
                    shard4:
                        id: 5
                        host: '%env(resolve:DATABASE_HOST_USER_4)%'
                        user: '%env(resolve:DATABASE_USER_USER_4)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_4)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_4)%'
                        charset: UTF8
                    shard5:
                        id: 6
                        host: '%env(resolve:DATABASE_HOST_USER_5)%'
                        user: '%env(resolve:DATABASE_USER_USER_5)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_5)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_5)%'
                        charset: UTF8
                    shard6:
                        id: 7
                        host: '%env(resolve:DATABASE_HOST_USER_6)%'
                        user: '%env(resolve:DATABASE_USER_USER_6)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_6)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_6)%'
                        charset: UTF8
                    shard7:
                        id: 8
                        host: '%env(resolve:DATABASE_HOST_USER_7)%'
                        user: '%env(resolve:DATABASE_USER_USER_7)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_7)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_7)%'
                        charset: UTF8
                    shard8:
                        id: 9
                        host: '%env(resolve:DATABASE_HOST_USER_8)%'
                        user: '%env(resolve:DATABASE_USER_USER_8)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_8)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_8)%'
                        charset: UTF8
                    shard9:
                        id: 10
                        host: '%env(resolve:DATABASE_HOST_USER_9)%'
                        user: '%env(resolve:DATABASE_USER_USER_9)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_9)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_9)%'
                        charset: UTF8

        default_connection: default
    orm:
        auto_generate_proxy_classes: true
        #auto_mapping: true
        #naming_strategy: doctrine.orm.naming_strategy.underscore

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

            user:
                connection: user
                mappings:
                    User:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/User'
                        prefix: 'App\Entity\User'
                        alias: User

How i can to setup slaves for each shard?

For example it can be seems like this:

shards:
                    shard0:
                        id: 1
                        host: '%env(resolve:DATABASE_HOST_USER_0)%'
                        user: '%env(resolve:DATABASE_USER_USER_0)%'
                        password: '%env(resolve:DATABASE_PASSWORD_USER_0)%'
                        dbname: '%env(resolve:DATABASE_NAME_USER_0)%'
                        charset: UTF8
                        slaves:
                              slave0:
                                   host: '%env(resolve:DATABASE_HOST_USER_0_SLAVE_0)%'
                                   user: '%env(resolve:DATABASE_USER_USER_0_SLAVE_0)%'
                                   password: '%env(resolve:DATABASE_PASSWORD_USER_0_SLAVE_0)%'
                                   dbname: '%env(resolve:DATABASE_NAME_USER_0_SLAVE_0)%'
                              slave1:
                                   host: '%env(resolve:DATABASE_HOST_USER_0_SLAVE_1)%'
                                   user: '%env(resolve:DATABASE_USER_USER_0_SLAVE_1)%'
                                   password: '%env(resolve:DATABASE_PASSWORD_USER_0_SLAVE_1)%'
                                   dbname: '%env(resolve:DATABASE_NAME_USER_0_SLAVE_1)%'

Is it planned to support this in the future if it is not possible now?

[morozov]

Irrelevant as of #3906.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.