"
MODX Staff
I would like to open this discussion by first referring to this xPDO doc. http://rtfm.modx.com/display/xPDO20/Database+Connections+and+xPDOThat is the intended configuration. However, MODX should be initialized with the appropriate xPDO::OPT_CONN_INIT configuration, which should have a value indicating if the initial connection that is selected is mutable.
I have tested this in various configurations and overall it appears that it will allow multiple connections (at least it doesn't fail with a 500 error when set). I have tested with both master and slave in different positions, each taking a turn at being the initial connection. I had a little more luck with slave being the initial and master being secondary.
Dilemma & ProblemSounds like we need a way to try the available connections until one is good. Certainly plausible, and just the feedback I've been looking for.
This only seems to work if both connections are actually good. If once connection fails the who site fails in a 500 error. Ideally as long as there is one connection the system should stay up. If the slave (read only) is the one that is up and there is no ability to write then those would fall to exceptions but still not result in a total site failure. Also I believe a readonly connection would only effect the manager or any front end snippets that save to DB (ie: formItSaver, or something)
Solution?You are actually the first person to provide any feedback on this feature since I asked for it a year and a half ago. Let's work through your issues and get it into the xPDO (and MODX) core. Though most of xPDO is developed as a result of MODX development, it is still actively in development, despite the aging xpdo.org website. Ignore that for now, though I definitely want to address this when possible as well, because xPDO contributions are MODX contributions.
Is there anything undocumented, maybe living half done or in the nightlies that is already addressing this?
If not I may have a solution quite shortly to solve this issue myself and then willing to give it back for submission to the core. Of course any feedback as to a best practice/desire as to how this would be done is welcome. I have not yet decided which object would be best to extend, xpdo or modx. I am not even totally sure the current state of xpdo, is it still its own thing or is it no longer living on its own and absorbed into MODX. Appears to be nearly a year since the xpdo site has shown any movement.
"
MODX Staff
Jason,Unfortunately, I can't commit much time to providing more direction right this moment, but it would be great if you find a solution that works and you want to share it back. And feel free to contact me if you get stuck or find what you think is a bug or two...
Thank you for your reply. I am still very interested in solving this and wanted to give you a shout back for taking "my call". I did not get the response email so I just realized now that you had replied. Thank you again for taking the time.
I would really like to approach this from 1 of 2 directions and open to your input on either or an alternative 3rd.
1) Between my sysadmin and myself we work out a solution that works for our need and then I share back with you and you can see how it could fit or be tweeked to be accepted into the core
2) You provide me some sort of a direction as to how you wish it to happen and I will do my best to follow and then contribute back of course
3) ??
Overall I need a solution asap so I can't wait around for the solution nor can I expect that this would jump to the front of the line of TODOs with a quick turn around. So happy to solve it myself.
public function &getConnection(array $options = array()) {
$conn =& $this->connection;
$mutable = $this->getOption(xPDO::OPT_CONN_MUTABLE, $options, null);
if (!($conn instanceof xPDOConnection) || ($mutable !== null && (($mutable == true && !$conn->isMutable()) || ($mutable == false && $conn->isMutable())))) {
if (!empty($this->_connections)) {
shuffle($this->_connections);
$conn = reset($this->_connections);
## This is the LOOP ##
##################
while ($conn) {
if ($mutable !== null && (($mutable == true && !$conn->isMutable()) || ($mutable == false && $conn->isMutable()))) {
$conn = next($this->_connections);
continue;
}
$this->connection =& $conn;
break;
}
##################
} else {
$this->log(xPDO::LOG_LEVEL_ERROR, "Could not get a valid xPDOConnection", '', __METHOD__, __FILE__, __LINE__);
}
}
return $this->connection;
}
## Simulation tests
$sim_west_down = true;
$sim_east_down = false;
$sim_on_west = false;
$sim_on_east = false;
$pm_db_status['west'] = true;
$pm_db_status['east'] = true;
if(is_file('/tmp/westdown') || $sim_west_down){
$pm_db_status['west'] = false;
}
if(is_file('/tmp/eastdown') || $sim_east_down){
$pm_db_status['east'] = false;
}
## Am I West or East
switch($_SERVER['SERVER_ADDR']){
case '10.11.1.20':
$pm_webserver_local = 'west';
break;
case '10.11.2.20':
default:
$pm_webserver_local = 'east';
break;
}
if($sim_on_west){
$pm_webserver_local = 'west';
}
else if($sim_on_east){
$pm_webserver_local = 'east';
}
define('PM_DB_EAST',$pm_db_status['east']);
define('PM_DB_WEST',$pm_db_status['west']);
define('PM_WEBSERVER_LOCAL',$pm_webserver_local);
$rw_db = 'db.west.somedomain.net';
$rw_username = '####';
$rw_password = '??????';
$ro_db = 'db.east.somedomain.net';
$ro_username = '&&&&&';
$ro_password = '*******';
if(PM_WEBSERVER_LOCAL == 'west'){
if(PM_DB_WEST){
$conn_config = 'West UP ON West';
$database_server = $rw_db; // (READONLY)
$database_user = $rw_username;
$database_password = $rw_password;
$config_options = array(); // if on west and west is UP then everything stays on west on need one conn
}
else if (PM_DB_EAST){
$conn_config = 'East UP ON West';
$database_server = $ro_db; // (READONLY)
$database_user = $ro_username;
$database_password = $ro_password;
$config_options = array(
xPDO::OPT_CONN_MUTABLE => false,
xPDO::OPT_CONN_INIT => array(xPDO::OPT_CONN_MUTABLE => false),
xPDO::OPT_CONNECTIONS => array()
); // if on west and west is DOWN then everything goes to east is READONLY (OPT_CONN_MUTABLE => false)
if(substr_count($_SERVER['REQUEST_URI'],'/manager/') > 0){
header('Location:/manager/offline.html');
die;
}
}
else{ // Everything is down NOW WHAT.
}
}
else{ // we are on East OR other
if(PM_DB_WEST){
$conn_config = 'West UP ON East';
$database_server = $rw_db; // (READONLY)
$database_user = $rw_username;
$database_password = $rw_password;
$config_options = array(
xPDO::OPT_CONN_MUTABLE => true,
xPDO::OPT_CONN_INIT => array(xPDO::OPT_CONN_MUTABLE => true),
xPDO::OPT_CONNECTIONS => array(
array(
'dsn' => 'mysql:host=' . $ro_db . ';dbname=modx;charset=utf8',
'username' => $ro_username,
'password' => $ro_password,
'options' => array(
xPDO::OPT_CONN_MUTABLE => false,
),
'driverOptions' => array(),
) // if on East and West is UP then Write requests go to master (OPT_CONN_MUTABLE => true)
)
);
}
else if (PM_DB_EAST){
$conn_config = 'East UP ON East';
$database_server = $ro_db; // (READONLY)
$database_user = $ro_username;
$database_password = $ro_password;
$config_options = array(
xPDO::OPT_CONN_MUTABLE => false,
xPDO::OPT_CONN_INIT => array(xPDO::OPT_CONN_MUTABLE => false),
xPDO::OPT_CONNECTIONS => array()
); // if on west and west is DOWN then everything goes to east is READONLY (OPT_CONN_MUTABLE => false)
if(substr_count($_SERVER['REQUEST_URI'],'/manager/') > 0){
header('Location:/manager/offline.html');
die;
}
}
else{ // Everything is down NOW WHAT.
}
}
"
MODX Staff
$modx= new modX('', array(xPDO::OPT_CONN_INIT => array(xPDO::OPT_CONN_MUTABLE => true)));
"
MODX Staff
I believe I am hearing you correctly and yes I understand that having at least 1 mutable connection is the intended behavior. However it would be great to allow with limited use the other option.I agree; expanding the capabilities is certainly desirable here.
The fact that through configuration alone one can set up single or multiple connections that are all immutable and then create a endless loop could be an issue. There is also the byproduct of a setup that is not trying to be readonly as I am and is following the rules of at least 1 mutable connection, but then that mutable connection drops for a moment and the whole system locks up because of it. Overall my goal was simply to share my findings and note a potential bug to help the cause. But I am happy to have it be left as is. I just didn't want to not share what I found in an area that very few seems to have looked into.You are larger than 1% considering the number of folks asking about this feature. And your request is perfectly reasonable. I just need to understand the use case so we can cover it with the behavior.
I get that I am a 1% user base who desires to be able to run modx on across master/slave db environments in which during an outage the master may be down and readonly is required. We will be visiting master/master in the future which could resolve this issue but opens its own issues as well.
Could you help explain the other issue I noted about the immutable being written to?Unless the code you are using to make the "writes" is aware of the master/slave connection issue, i.e. using xPDOObject::save() or another core method, you need to make sure the connection is mutable before attempting the write. It might look something like this...
- Setup 2 connections
- Init Conn[1] = Mutable = false
- Conn[2] = Mutable = true
Go write something.
In my testing the data was written to the Init Conn location and not the Conn[2]. My reason for this setup is that when the slave web server is remote from the master DB. (ie: east coast web server w/ master on west coast) the desire to have the Conn[1] which is just making a connection to read initially as a request comes in should not be mutable since I wish to not pull the first read of every request across the country.
<?php
/* make sure connection is writable or switch to writable */
if (!$modx->getConnection(array(xPDO::OPT_CONN_MUTABLE => true))) {
$modx->log(xPDO::LOG_LEVEL_ERROR, "Could not get connection for writing data", '', __METHOD__, __FILE__, __LINE__);
return false;
}
/* then attempt to write... */
$modx->exec("UPDATE food SET favorite=1 WHERE type='beer'");
Thank you for you time on this. My apologies if I am somehow am seeing all this wrong.No apologies necessary, and thank you immensely for the feedback. I'd love to see a feature request for the use case( s ) you are presenting here.
"