The scenario:
I have a local DB and a remote public DB. Both are synced using SQLyog SJA job files - which sync both DB's to be the same. It works well.
The part of the DB with the issue is a user comments table.
The local DB contains thousands of user comments, and more are always being added through various means. These are all synced to the remote DB comments table.
The remote comments table DB accepts direct user comments to be entered. These then sync to the local comments DB.
It is a two way sync, where neither one deletes from the other. This actually seems to work well and is automated through SJA.
The problem:
The primary key ID's for the comments are auto incremented on both sides.
So if both tables are in exact sync and the key count is at 50, and a user makes a remote entry it will be key 51. Now the local DB is also growing and a different entry is made under key 51. So when the next sync is called there will be a problem as the keys are conflicting.
Possible solutions:
So I thought A good idea would be to add a large number to the remote comments PK ID as they are added. That way when a sync is called the primary keys will not conflict as the local PK ID would never get that high.
It worked well on the first sync. But the problem is that the auto increment feature will increment off the highest value, even if there is a large gap in between the keys. So this solution does not work.
I would like to maintain a single table for user comments and have a seamless sync but the issue of conflicting primary keys is a problem.
I am interested if other people have some thoughts on the matter.
I hope I described the problem clearly.
Thanks.
------ EDIT -----------
I have found a solution that works.
I changed the primary key ID to just a normal INT with auto increment. I then created a second ID INT field which consists of a random INT about 10 in length. I now use the two ID fields together to form a PK ID. Now the chance of a conflict between the two DB's is essentially none existent. The auto incremented ID and the long random INT ID would both have to be the same on the same entry, highly unlikely with the volume I'm dealing with.
Not the best solution but it works well.
Hope this helps someone else out.
I have found a solution that works.
I changed the primary key ID to just a normal INT with auto increment. I then created a second ID INT field which consists of a random INT about 10 in length. I now use the two ID fields together to form a PK ID. Now the chance of a conflict between the two DB's is essentially none existent. The auto incremented ID and the long random INT ID would both have to be the same on the same entry, highly unlikely with the volume I'm dealing with.
Not the best solution but it works well.
Hope this helps someone else out.