Symfony / Propel 1.4: read from one, write to another database

Question

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

We are experiencing additional load on the database server (say, DB1). We are doing database optimization, but as an immediate solution, we decided to create another database server so that DB2 is an exact replica of DB1 all the time. Currently, we only have DB1 used for read and write operations.

Now we need to move all the read operations to DB2 and save the write operations (usually in transactions) to DB1, as of now.

What are the possible ways to make these changes (on a production server without a lot of downtime) and, if possible, with minimal code changes.

Edit after first comment

Based on the link given by J0k and some other links, I did the following in a local dev environment.

• Created a symfony 1.4 test project

• Updated database.yml as follows

  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 the wzoslv database is an exact copy of the wzo database, except for a change in one test record. In the odd_play table, odd_play column 26 (PK) result : WON1 and WON respectively.

• run symfony tasks

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

• Created a module and added the 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, the output was,

  when recording a connection, result = WON // Correct expected output

  on Read connection, result = WON // Incorrect. It must be WON1

I think that passing OddPlayPeer::DATABASE_NAME while creating a connection for reading and writing is a problem, but as suggested in the online examples. Can someone please suggest where I am making a mistake?

Edit: a bit of input

I updated the debug echo files in lib\vendor\symfony\lib\plugins\sfPropelPlugin\lib\vendor\propel\Propel.php to check how it returns the connection. Found that he enters the following if code (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 empty, so they return a record connection. Now the question is why slaveconfigs is empty?

I am also trying to edit sfDatabaseConfigHandler.class.php as defined in the old forums , but at the same time break symfony somewhere and nothing is displayed on the Internet and even in the logs.

Answer 1

I'm sure I'm making some kind of mistake, but whatever suggested in the official Propel / symfony docs and even here in stackoverflow doesn't seem to work for me. Official docs should probably take better care of programmers who have little symfony experience.

Although we do not prefer to edit the core files of any framework / third-party libraries, but this forces me to edit the kernel files to make me a working solution. The solution that worked for me is the following:

database.yml My database.yml file looks like this:

 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 the Propel.php file as follows

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; 

c (added one line between them)

 // 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 the same file, line 560 changed

 $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 propeller 1.6 (We upgraded the engine just to make it work, but went back to propel 1.4 later, as the upgrade in production should be well tested.)
File: plugins / sfPropelORMPlugin / lib / vendor / propel / runtime / lib / Propel.php
Modified line 601

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

to (added one line earlier)

 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 the same file, line 629 changed

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

to

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

Then the following test file gave the 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 the main files, but this solution worked for us as if in disrepair. Someone else, if necessary, can use it in disrepair . Still looking for the perfect solution.

Answer 2

You should add a new layer in slaves plus a connection entry, as per the document (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