Install and Sync FA in 2 locations (HQ and branch) for one company

I have done exactly this. I'm using master/master replication on the MySQL db. I did this because our internet connection in both of our branches is very flakey, and also so that we have an automatic offsite backup that we can use live if a machine goes out for some reason. There is, however, an issue you should know about....

When the internet goes down, if both branches make, say, a general ledger entry then you get a replication error because the primary key is the next one, so both branches use the same number for their GL entries. It's probably the same with sales/purchases, but so far I've only seen the GL issue, and it's a nightmare to correct.

So, the replication works nicely as long as the connection is there, but that's catch 22 because if the connection is there then you don't need the replicated database - you could just work from one instance of FA. If the connection goes down then the 2 locations will be writing different GL data with the same transaction number and you'll get replication errors and lose data.

What we need (in the next version?) is the primary keys written with a machine specific number, i.e 102323, 4, 5 etc from server 1, and 2022323,4,5 etc from server 2.

FA team, this won't take much programming... are you OK to include this in the core? It would be a major feature to have a system that can be replicated and on top of the IT benefit it's like automatic consolidation from a Finance point of view. I am happy to help with the coding if you like.

Thanks for your input. I think what you are saying is that we are currently using the inbuilt AUTO_INCREMENT functionality of MySQL, and you are quite correct that this won't work as you have explained.

What I'm suggesting is that we hold (perhaps in the config file) a server number. So the config files on each server would be different, with one having  server=1 and the other server=2. Then we physically write the transaction id, instead of auto incrementing and append the server number to the end (I initially proposed prefixing but I think now that a postfix would be better). This could possibly also solve the problems we have with "Reference number already in use" if we adopted the same procedure for reference numbers. It's probably relatively easy to add this code, but I would like hear from you gurus before I even look at it, in case there's a clever way to do it that I haven't thought of.

What I do know is that the ability to have a replicated database is going to enhance FA, and I can give you 10 reasons why someone would want to do it. You probably already know the reasons...

Regards,

@Digger the reason why auto increment is used is to prevent a race condition.  Suppose two users both action an insert at the same time.  The only mechanism for serializing the request is the mysql server itself which will put the two requests in sequence.  The server then returns the id that it chose to identify the record it just wrote.  If the php code were to do this then there is the remote possibility that a write would be attempted with two identical keys.

The usual way to code around this is to get a key on creation.  This is called a GUID (or a UUID).  These are typically many bits long and created taking into account things like the local (to the FA web server) ethernet card MAC address and time.

IMHO this is not such a trivial change as you might think.

@Digger that looks like it would work.  auto_increment_increment = 2 on both machines.  auto_increment_offset = 1 on the first machine and auto_increment_offset = 2 on the second machine.  The first machine would generate 1,3,5,7... the second machine would do 2,4,6,8 etc.

No code change required.  That's a nice easy option :-)

Here's the mySQL reference.