2

I have a mysql\PHP application hosted on intranet and on internet. Both mysql servers are replicated i.e., synchronized on real time.

I have some tables which have auto increment id as primary key. When sync goes off, for new transactions same auto increment value is used on online as well as intranet server. So even when servers get connected and sync starts; records with same auto increment id do not get sync. Ids with non overlapping values get synced soon the servers get connected.

To resolve this issue, I am thinking of using manual increment values with different range on intranet and online.

Please suggest, what could be the best solution for this problem.

Also if I have to go with manual increment ids, what would be the best technique OR algo to assign ids separately on online and on intranet.

Charles
  • 50,943
  • 13
  • 104
  • 142
rkmourya
  • 446
  • 4
  • 7

2 Answers2

1

There are a two things you can do. The first would be to change the starting value of the live server to a very high number (higher then the expected number of rows)

EG:

ALTER TABLE tbl AUTO_INCREMENT = 10000;

Now the numbers wont overlap. If that is not an option you can change the interval with 

SET @@auto_increment_increment=10;

But this would also mean there is an overlap at one point. because the server with increment steps of 1 will catch up with the steps of 10 after.. you guessed.. 10 rows! But you could bypass this by setting one server to start increment at 1 and the other at 2, and then make both have increment steps of 2.

That would make something like

intranet 1, 3, 5, 7, 9
live 2, 4, 6, 8, 10

You could also use a two column primary key to prevent duplication. Now you have an auto increment field in combination with a varchar field (live and intr) and that is your unique key.

CREATE TABLE `casetest`.`manualid` (
    `id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
    `server` VARCHAR( 4 ) NOT NULL DEFAULT 'live',
    `name` INT NOT NULL ,
    PRIMARY KEY ( `id` , `server` )
) ENGINE = MYISAM ;
Hugo Delsing
  • 13,803
  • 5
  • 45
  • 72
  • What I assume due to replication query "ALTER TABLE tbl AUTO_INCREMENT = 10000;" fired online will also get replicated on the intranet due to sync. – rkmourya Apr 02 '13 at 14:27
  • Yes, if the intranet server would get more then 10000 rows in it, then it will be duplicated. That why I mentioned using a realy high value you dont expect (EG: 1.000.000 for employees) Updated with a solution for duplicate keys – Hugo Delsing Apr 02 '13 at 14:31
  • For two servers, an increment of 2 and offset of 1 and 2 will work fine :) – Ja͢ck Apr 03 '13 at 09:47
  • But that is no real difference then 5 and 10, although the numbers will increase faster – Hugo Delsing Apr 03 '13 at 10:03
1

I figured out the solution to this problem.

While configuring the replication of the mysql servers auto increment settings should be adjusted such the ids on the servers never overlap. Example if you have 2 servers replicated one server should only generate even auto increment ID's and other only odd ids.

Here's the link for detail information on this.

http://jonathonhill.net/2011-09-30/mysql-replication-that-hurts-less/

Updating the settings on both the servers resolved this issue.

rkmourya
  • 446
  • 4
  • 7
  • See also: http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_increment – Ja͢ck Apr 03 '13 at 09:45