Modifying records on Yii2 Mater Slave Steup

In our commercial app, we are using master/slave feature in database configuration and running a MSSQL(business requirement) db as the master and MySQL db as a slave. We want to do read requests from the slave(MySQL) and write requests to the master(MSSQL). We have configured a background job to copy data from master to slave.

I tried to do an update to the master and it failed since the SQL generated was having MySQL syntax but not the MSSQL syntax. The connection is switching correctly to the master. When I checked update() function in the db/QueryBuilder.php file,




public function update($table, $columns, $condition, &$params)

    {


        if (($tableSchema = $this->db->getTableSchema($table)) !== null) {

            $columnSchemas = $tableSchema->columns;

        } else {

            $columnSchemas = [];

        }


        $lines = [];

        foreach ($columns as $name => $value) {

            if ($value instanceof Expression) {

                $lines[] = $this->db->quoteColumnName($name) . '=' . $value->expression;

                foreach ($value->params as $n => $v) {

                    $params[$n] = $v;

                }

            } else {

                $phName = self::PARAM_PREFIX . count($params);

                $lines[] = $this->db->quoteColumnName($name) . '=' . $phName;

                $params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;

            }

        }


        $sql = 'UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . implode(', ', $lines);

        $where = $this->buildWhere($condition, $params);

        return $where === '' ? $sql : $sql . ' ' . $where;

    }




$this->db->quoteColumnName($name)

always gets the MySQL format. We need to have it with MSSQL format added in the QueryBuilder class in mssql/QueryBuilder.php file. I’m bit confused on how the schema is selected in this scenario for


$this->db

.

Please let me know how to fix this issue. Thanks in advance!

Sam

That’s very unusual setup. Usually both master and slave are of the same type i.e. two MySQLs or two MSSQLs.

Anyway… what’s your config for ‘db’ component (please don’t post passwords)?

Hi @samdark,

Thanks for your response. Yes it is an unusual setup due to a business requirement. Following is the database configuration.


return [

    'class' => 'yii\db\Connection',

    'charset'  => 'utf8',

    'tablePrefix'  => 'prefix',

    'enableSlaves' => true,

    'serverRetryInterval' => 1,

    'enableQueryCache' => false,

    'enableSchemaCache' => false,


    // configuration for the master

    'masterConfig' => [

        'class' => 'app\components\MSSQLConnection',

        'username' => 'username',

        'password' => 'password',

        'enableQueryCache' => false,

        'enableSchemaCache' => false,

    ],


    // list of master servers

    'masters' => [

        ['dsn' => 'dblib:host=<MS SQL HOST>;dbname='.$dbName],

    ],


    // common configuration for slaves

    'slaveConfig' => [

        'class' => 'app\components\MySQLConnection',

        'username' => 'username',

        'password' => 'password',

        'enableQueryCache' => false,

        'enableSchemaCache' => false,

        'attributes' => [

            // use a smaller connection timeout

            PDO::ATTR_TIMEOUT => 10,

            PDO::ATTR_DRIVER_NAME => 'mysql',

        ],

    ],


    // list of slave configurations 

    'slaves' => [

        ['dsn' => 'mysql:host=<AN AURORA DB SITTING IN AWS>;dbname='.$dbName],

    ],

];

Try specifying classes for each item in the master and slave lists.

If it won’t help, I guess the only way to find out is to debug connection selection part…