Symfony/Propel 1.4: Read from one, write to other DB

Question

We have an existing project (SNS website+android/Iphone games) in Symfony 1.4/ Propel 1.4

We are experiencing extra load on DB server (say DB1). We are doing DB Optimization but as immediate solution we decided to create one more DB server in the way DB2 is exact replica of DB1 all the time. Currently we have only DB1, used for both read and write operations.

Now we need to move all read operations to DB2 and keep write operations (generally in transactions) on DB1 as it is now.

What are the possible ways to make those changes (On production server without much downtime) and if possible, with minimal code changes.

Edit after first comment

Based on link given by J0k and some other links, I'd done following on local dev environment.

1. Created a test symfony 1.4 project

2. Updated database.yml as follow

   all:
     propel:
       class: sfPropelDatabase
       param:
         classname: PropelPDO
         dsn: 'mysql:host=localhost;dbname=wzo;'
         username: root
         password: mysql
         encoding: utf8
         persistent: true
         pooling: true
       slaves:
         slave1:
           dsn:      'mysql:host=localhost;dbname=wzoslv;'
           username: root
           password: mysql
           encoding: utf8
   

   Where database wzoslv is exact replica of database wzo except change in one test entry. On table odd_play row 26 (PK) column result entries are WON1 and WON respectively.

3. run symfony tasks

   php symfony propel:build-schema
   php symfony propel:build-model
   php symfony cc
   

4. Created a module and added following code:

   class wzoActions extends sfActions
   {
     public function executeIndex(sfWebRequest $request)
     {
       $con_write = Propel::getConnection(OddPlayPeer::DATABASE_NAME, Propel::CONNECTION_WRITE);
       $con_read = Propel::getConnection(OddPlayPeer::DATABASE_NAME, Propel::CONNECTION_READ);
       $oddPlay = OddPlayPeer::retrieveByPK(26,0,$con_write);
       echo "on write connection, result=".$oddPlay->getResult();
       $oddPlayRead = OddPlayPeer::retrieveByPK(26,0,$con_read);
       echo "<br/>on Read connection, result=".$oddPlayRead->getResult();
       exit;
       $this->setLayout('layout');
     }
   }
   

   Run http://local.sftest.com/index.php/wzo/index in the browser, output was,

   on write connection, result=WON //Correct expected output

   on Read connection, result=WON //Not correct. That should be WON1

I guess passing OddPlayPeer::DATABASE_NAME while creating both read/write connection is the issue but that how it was suggested in online examples. Can someone please suggest where I'm making the mistake?

Edit: Few more input

I updated debug echos in lib\vendor\symfony\lib\plugins\sfPropelPlugin\lib\vendor\propel\Propel.php to check how it is returning the connection. Found that it is entering in following if (line 544-549)

$slaveconfigs = isset(self::$configuration['datasources'][$name]['slaves']) ? self::$configuration['datasources'][$name]['slaves'] : null;

if (empty($slaveconfigs)) {
  echo "inelseifif<br/>";// no slaves configured for this datasource
  self::$connectionMap[$name]['slave'] = false;
  return self::getConnection($name, Propel::CONNECTION_WRITE); // Recurse to get the WRITE connection
}

where $slaveconfigs are empty so returning write connection. Now the question is, why slaveconfigs is empty?

I also try editing sfDatabaseConfigHandler.class.php as defined in old forums but doing so, break symfony somewhere and nothing gets display on web and even in logs.

Answer 1

I'm sure I'm doing some mistake but whatever suggested on official documents of Propel/symfony and even here at stackoverflow, seems not working for me. Probably official documents should take better care of programmers who do not have lot of symfony experience.

Although we do not prefer to edit core files of any framework/third party libraries but this force me to edit core files to make a working solution for me. The solution that worked for me is as follow:

database.yml My database.yml file is as follow:

all:
  propel:
    class: sfPropelDatabase
    param:
      classname: PropelPDO
      dsn: 'mysql:host=localhost;dbname=wzo;'
      username: testuserwzo
      password: 
      encoding: utf8
      persistent: true
      pooling: true

  slave:
    class: sfPropelDatabase
    param:
      classname: PropelPDO
      dsn: 'mysql:host=localhost;dbname=wzoslv;'
      username: testuserwzoslv
      password: 
      encoding: utf8
      persistent: true
      pooling: true

After that, I edited Propel.php file as follow

For Propel 1.4
File: lib/vendor/symfony/lib/plugins/sfPropelPlugin/lib/vendor/propel/Propel.php
Change line 542-543

// we've already ensured that the configuration exists, in previous if-statement
$slaveconfigs = isset(self::$configuration['datasources'][$name]['slaves']) ? self::$configuration['datasources'][$name]['slaves'] : null;

with (added one line inbetween)

// we've already ensured that the configuration exists, in previous if-statement
self::$configuration['datasources'][$name]['slaves'] = isset(self::$configuration['datasources']['slave']) ? self::$configuration['datasources']['slave'] : null;
$slaveconfigs = isset(self::$configuration['datasources'][$name]['slaves']) ? self::$configuration['datasources'][$name]['slaves'] : null;

Then in same file, changed line 560

$con = Propel::initConnection($conparams, $name);

to

$con = Propel::initConnection($conparams, 'slave'); //I know its bad practive to put hard-coded value but at that moment, I was more interested in working solution without caring about best practices.

For propel 1.6 (We upgraded propel just to make this working but reverted back to propel 1.4 later as upgrade on production needs to be well tested.)
File: plugins/sfPropelORMPlugin/lib/vendor/propel/runtime/lib/Propel.php
Changed line 601

$slaveconfigs = isset(self::$configuration['datasources'][$name]['slaves']) ? self::$configuration['datasources'][$name]['slaves'] : null;

to (Added one line before)

self::$configuration['datasources'][$name]['slaves'] = isset(self::$configuration['datasources']['slave']) ? self::$configuration['datasources']['slave'] : null;
$slaveconfigs = isset(self::$configuration['datasources'][$name]['slaves']) ? self::$configuration['datasources'][$name]['slaves'] : null;

Then in same file, changed line 629

$con = Propel::initConnection($conparams, $name);

to

$con = Propel::initConnection($conparams, 'slave');

Then following test file was giving expected result

class kapsActions extends sfActions
{
  public function executeIndex(sfWebRequest $request)
  {
      $con_write = Propel::getConnection(OddPlayPeer::DATABASE_NAME, Propel::CONNECTION_WRITE);
      $con_read = Propel::getConnection(OddPlayPeer::DATABASE_NAME, Propel::CONNECTION_READ);

      $oddPlay = OddPlayPeer::retrieveByPK(28,0,$con_write);
      echo "on write connection, result=".$oddPlay->getResult().$oddPlay->getPlayscore();

      $oddPlayRead = OddPlayPeer::retrieveByPK(27,0,$con_read);
      echo "<br/>on Read connection, result=".$oddPlayRead->getResult().$oddPlayRead->getPlayscore();
      exit;
      //$this->setLayout('layout');
  }
}

I still do not recommend editing core files but this solution worked for us as in emergency condition. Someone else, if needed, may use it in emergency condition. Still looking for perfect solution.

Answer 2

You should add a new level to slaves plus a connection entry, according to the doc (on github)

all:
  propel:
    class: sfPropelDatabase
    param:
      classname: PropelPDO
      dsn: 'mysql:host=localhost;dbname=wzo;'
      username: root
      password: mysql
      encoding: utf8
      persistent: true
      pooling: true

      slaves:
        connection:
          slave1:
            dsn:      'mysql:host=localhost;dbname=wzoslv;'
            username: root
            password: mysql
            encoding: utf8