I am trying to set up multiple databases in order to split up the read and write queries between two databases, a Read-Only Database and a Write Database.

I set up the multiple databases in settings.php:

$db_url['default'] = 'mysql://username:password@localhost/databasename';
$db_url['readonly'] = 'mysql://username:password@localhost/databasename';

And the only way I could figure out how to do this without having to go through every module and manually set the database with 'db_set_active()' is to go into the core file '/includes/database.mysql-common.inc' and modify the 'db_query' function:

function db_query($query) {
  $args = func_get_args();
  array_shift($args);
  $query = db_prefix_tables($query);
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
  }
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
	
  /* Added  by apersaud
	   Integrating multiple read/write databases
  */
  if(strpos(strtolower($query),"select") === 0){
    db_set_active('readonly');  //this will not contain any data from the master (write) database untill replication happens
  }
  else {
    db_set_active('default');
  }	
		
  return _db_query($query);
  db_set_active('default');	
}

However, it's not working correctly. Whenever I try to add new content (e.g. Story), I get an error message "The post could not be saved". The post is still created, but it's corrupted and I can't open it, and also the only row in the 'node_access' table is deleted somehow (which I think is causing some of the issues). But, I can't figure out what's causing that row to be deleted.

Any suggestions?

Comments

apersaud’s picture

Also, In the log entry I get this error:

	Duplicate entry '0' for key 2 query: INSERT INTO node (vid, type, language, title, uid, status, created, changed, comment, promote, moderate, sticky, tnid, translate) VALUES (0, 'story', '', 'Test 9', 6, 0, 1242671928, 1242671928, 0, 0, 0, 0, 0, 0)