Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

How do I make Magento use two MySQL Servers
 
WildWire
Jr. Member
 
Total Posts:  19
Joined:  2008-03-31
 

I setup a replicated mysql server in magento.. following this blog post: http://www.magentocommerce.com/blog/comments/performance-is-key-notes-on-magentos-performance/

I added this into my /app/etc/local.xml

<default_setup>
                <
connection>
                    <
host><![CDATA[hostname1]]></host>
                    <
username><![CDATA[user]]></username>
                    <
password><![CDATA[pass]]></password>
                    <
dbname><![CDATA[magento]]></dbname>
                    <
active>1</active>
                </
connection>
            </
default_setup>
        <
default_read>
                <
connection>
                    <
host><![CDATA[hostname2]]></host>
                    <
username><![CDATA[user]]></username>
                    <
password><![CDATA[pass]]></password>
                    <
dbname><![CDATA[magento]]></dbname>
                    <
active>2</active>
                </
connection>
            </
default_read>

After watching load on the secondary mysql server.. I can see no queries are sent to it.  Caching is off.  What am I missing?

Thanks!

 
Magento Community Magento Community
Magento Community
Magento Community
 
Moshe
Magento Team
 
Avatar
Total Posts:  1770
Joined:  2007-08-07
Los Angeles
 

Try this:

<default_setup>
                <
connection>
                    <
host><![CDATA[hostname1]]></host>
                    <
username><![CDATA[user]]></username>
                    <
password><![CDATA[pass]]></password>
                    <
dbname><![CDATA[magento]]></dbname>
                    <
active>1</active>
                </
connection>
            </
default_setup>
        <
default_read>
                <
connection>
                    <use /> <!-- 
to clear already declared tag -->
                    <
host><![CDATA[hostname2]]></host>
                    <
username><![CDATA[user]]></username>
                    <
password><![CDATA[pass]]></password>
                    <
dbname><![CDATA[magento]]></dbname>
                    <
active>1</active> <!-- not 2it's just a flag -->
                </connection>
            </default_read>
 
Magento Community Magento Community
Magento Community
Magento Community
 
WildWire
Jr. Member
 
Total Posts:  19
Joined:  2008-03-31
 

Thanks for the quick reply Moshe.  Oh and setting the active flag to ‘2’ was just to see what kind of cool errors I could summon wink

Adding <use /> created this error for me.
Fatal error: Class name must be a valid object or a string in C:\wlanmaul\app\code\core\Mage\Core\Model\Resource.php on line 99

So I added <type>pdo_mysql</type>

Now I get

PDOException Object
(
[message:protected] => SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘core_resource’ already exists

...etc (long error)

(and this down the bottom)…
Fatal error: Uncaught exception ‘Mage_Core_Model_Store_Exception’ in C:\wlanmaul\app\code\core\Mage\Core\Model\App.php:1056 Stack trace: #0 C:\wlanmaul\app\code\core\Mage\Core\Model\App.php(545): Mage_Core_Model_App->throwStoreException() #1 C:\wlanmaul\app\Mage.php(577): Mage_Core_Model_App->getStore() #2 C:\wlanmaul\app\Mage.php(439): Mage::printException(Object(Mage_Core_Exception)) #3 C:\wlanmaul\index.php(40): Mage::run() #4 {main} thrown in C:\wlanmaul\app\code\core\Mage\Core\Model\App.php on line 1056

If I rename the table ‘core_resource’ on my default_read I still get the error, but if I rename it on my default_setup it creates ‘core_resource’ on default_setup and then complains about ‘core_website’ ..  Like its going down the list trying to create these tables.
I’m assuming it thinks its a fresh install.. tried defining a default_write instead of default_setup, but that didn’t work.. any more ideas?  I’m going to wipe my default_setup db server and see if it will go all the way through… Thanks !

<default_read>
            <
connection>
                    <use />
                    <
host><![CDATA[otherhost]]></host>
                    <
username><![CDATA[user]]></username>
                    <
password><![CDATA[pass]]></password>
                    <
dbname><![CDATA[magento]]></dbname>
                    <
active>1</active>
                    <
type>pdo_mysql</type>
                </
connection>
            </
default_read>

EDIT:
I deleted all tables and just had my magento schema and it goes through creation, but still errors out eventually.. similar database duplicate error.. just this time it was a column instead of a table.

 
Magento Community Magento Community
Magento Community
Magento Community
 
WildWire
Jr. Member
 
Total Posts:  19
Joined:  2008-03-31
 

bump ; )

 
Magento Community Magento Community
Magento Community
Magento Community
 
WildWire
Jr. Member
 
Total Posts:  19
Joined:  2008-03-31
 

Last Bump :/

 
Magento Community Magento Community
Magento Community
Magento Community
 
acarlton
Jr. Member
 
Total Posts:  4
Joined:  2008-09-17
 

anybody have any information on this? or able to point us in the direction of some documentation?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Discovery
Enthusiast
 
Total Posts:  767
Joined:  2007-12-29
 

Bumpety bump! - Does anyone have replication working with local.xml writing to one db and reading from t’other?

 
Magento Community Magento Community
Magento Community
Magento Community
 
nheinrichs
Jr. Member
 
Total Posts:  10
Joined:  2010-03-10
 

Bit of an old thread, but this worked for me on Magento 1.4.1.
Note that I enabled this on an up-and-running instance (i.e., Magento and all modules had been installed already, and the site was running smoothly.)

I did get the “Fatal error: Class name must be a valid object or a string” error, but adding <type>pdo_mysql</type> as well as <model> and <initStatements> (which I found in app/etc/config.xml’s <default_setup> block) fixed things for me.

I never got any “table already exists” errors, which occur when Magento thinks it needs to install/upgrade a module that has already been installed.

<default_read>
                <
connection>
                    <use/>
                    <
host><![CDATA[localhost]]></host>
                    <
username><![CDATA[magento-admin]]></username>
                    <
password><![CDATA[admin-password]]></password>
                    <
dbname><![CDATA[magento-db]]></dbname>
                    <
model>mysql4</model>
                    <
initStatements>SET NAMES utf8</initStatements>
                    <
type>pdo_mysql</type>
                    <
active>1</active>
                </
connection>
            </
default_read>

... And don’t forget to refresh your Config cache, if you’re using it!

 
Magento Community Magento Community
Magento Community
Magento Community
 
nheinrichs
Jr. Member
 
Total Posts:  10
Joined:  2010-03-10
 

A quick word of warning:

I just ran into an issue under Magento 1.4.1 where having both a “default_setup”, as well as a “default_read” pointing at the same database caused data to not be saved to the session properly (eg Mage::getSingleton(’customer/session’) )

I have not found the underlying cause yet, but if you (for example) use a production local.xml on your local box and point default_setup/read/write at the same database, you may experience issues.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top