phpsymfony1symfony-1.4propel

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


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.


Solution

  • 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.