MySQL Master-Master Replication on 2 servers

Question

For our installation in a "Third World Country" we need 2 replicated Master-Master Mysql-Servers:

• one in a European data center to serve the public website and allow access from mobile devices to CiviCRM
• one local server to serve the office staff (as the internet line of the office often does not work and the staff cannot depend on the server in the data center)

We run 2 "master-master-replicated" MySQL-Server

• one with auto_increment_offset = 1
• the other with auto_increment_offset = 2
• both with auto_increment_increment = 2

in my.cnf so that one server uses odd ids and the other one even ids when inserting record. This works perfectly in other PHP/Mysql based software we are using. We do not have a problem with the replication of our running servers.

When installing CiviCRM we received the message

"An auto_increment_increment value greater than 1 is not currently supported. Please see issue CRM-7923 for further details and potential workaround."

https://issues.civicrm.org/jira/browse/CRM-7923 not really is helpful with that issue - it explains how to undergo the configuration of replicated servers which does not help in our case as we need the master-master replication with CiviCRM. This pages also shows that this topic seems to exist since several years without any change.

We googled a lot and did not find any solution for this important feature to use CiviCRM on 2 (or even more) replicated master database servers.

So I address the following questions:

1. why CiciCRM only supports id increments of 1 when inserting records? Which technical reason defines this limit?
2. are there any modules to change this handicap? if not:
3. where in the code, where is the point to change this behavior? We have a team of PHP developers to work on this problem if we would know where to start
4. is there - may be - a plan to change this problem in the near future e.g. in 4.7?
5. what other recommendation do you have for our organizational need of 2 servers - without setting up CiviCRM on 2 database servers with "master-master replication"?

Kind regards and thanks a lot for your answer!

Answer 1

This is an interesting approach!

As you rightly noted, that bug report was wrong a long while ago and didn't get traction.

As you mentioned you have developers able to help, I would suggest you do a quick evaluation to identify if the install procedure (without installing sample data) breaks still with an autoincrement >1

Obviously, in a separate install that you might end up trashing.

in your install/index.php line 1007 or so alter requireMySQLAutoIncrementIncrementOne so it's first line is

return; // bypass the test, the sky might fall, but at least we'll know how exactly.

I think the general plan for inclusion of a new feature is: if you do the work of identifying the issue and code the fix(or fund the work if needed), it's likely to be accepted ;)

Assuming that it doesn't have any impact for "normal" users that have an increment of 1, I personally would want to try (or at least master-slave for a starting point)