I'm trying to build a script that imports items from a production database to a local one, but am having problems switching the connection form production to local.
Doctrine_Manager::getInstance()->setCurrentConnection('prod-slave');
// execute query to get data from production, confirm it worked
Doctrine_Manager::getInstance()->setCurrentConnection('local');
// insert data into local database
When I run this I'm getting this error:
PHP Fatal error: Uncaught exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement' in...
If I close the connection to prod-slave before setting it to local I get:
PHP Fatal error: Uncaught exception 'Doctrine_Manager_Exception' with message 'Unknown connection: prod-slave' in...
Notice that in both of these cases, it's still trying to connect to the slave even though I changed the connection to local (In the first case, it must be the slave since it is read-only).
I have also tried manually setting the connections with:
Doctrine_Manager::connection('mysql://path');
Doctrine version: 1.2.3
Symfony version: 1.3.9
Any help would be much appreciated.
Thank you
If you check generated base classes for your models, at the very beginning you will see each of your model is bound to specific connection. So even if you set current connection to another one, the one bound is used.
Solution is to call Doctrine_Manager::getInstance()->bindComponent($component, 'local'); for each of your models.
Instead of using setCurrentConnection try using getConnection
$slave = Doctrine_Manager::getInstance()->getConnection('prod-slave');
$result1 = $slave->execute($some_sql);
$local = Doctrine_Manager::getInstance()->getConnection('local');
$result2 = $local->execute($some_other_sql);
works for me on a symfony 1.4 project.
This should work providing that, in your config/databases.yml, you have the two connections prod-slave and local properly setup. And in your schema files you have bound your tables to the appropriate connections. (Just in case a doc on connections and all that can be found here )