4

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!

  • I stumbled upon this looking at possibilities for clustered HA/Performance. I tried installing with 4.7 with a Galera cluster. Unfortunately the install failed. looks like something requires auto increment value to be one somewhere, at least in 4.7 – Jessie Lee Mar 3 '16 at 17:13
  • This is more of a comment than an Answer – petednz - fuzion Mar 3 '16 at 18:55
  • I unfortunately don't have the reputation to comment on the previous answer – Jessie Lee Mar 3 '16 at 19:18
  • sorry - wish that was more obvious to users like me. perhaps i need to learn to start looking at people's reps. will up your comment – petednz - fuzion Mar 4 '16 at 1:56
2

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)

  • Good idea - will try and let you know soon! – ExGerman Oct 30 '15 at 16:30
  • 1
    Xavier - indeed this seems to work! I did not install CiviCRM again but just changed the my.cnf of one of my servers and activated "master replication" with the lines – ExGerman Oct 30 '15 at 16:56
  • 1
    log-bin = mysql-bin server-id = 1 auto_increment_offset = 2 auto_increment_increment = 2 binlog-do-db = civicrm replicate-do-db = civicrm binlog_format=mixed I added 6 contacts with multiple addresses and other 1:n data - no error occurred and looking into the database I can confirm that the id's of the new created contacts, adresses etc. were generated in steps of 2 - all equal numbers. This was not a complete test of all functions, of course. But the great question is: why the hell the installation requires auto_increment_increment = 1 while auto_increment_increment = 2 seems to work? – ExGerman Oct 30 '15 at 17:04
  • Awesome that is passed that first test. Unfortunately, I can't think of anything that I can suggest you to test specifically, but would be good you spend a fair amount of time doing various manipulations and creating various things to see if it doesn't break. – Xavier Oct 30 '15 at 17:05
  • 1
    @ExGerman - It would be nice to hear a report on how this went. Did it continue to work OK? – mc0e Dec 30 '16 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.