0

I have a bit of a strange situation. We currently have two databases, our STAGE DB and our LIVE DB. Both databases have the same schema but somewhat different data.

So the way everything works is, a large data set is processed and the results are updated to the STAGE DB. This database is on a closed network with no outside access (security issues, don't ask). Binary logging has been turned on for this database, so to make these changes available on the LIVE DB, a dump file of the changes is taken and then it is manually run on the LIVE DB.

This was all working fine until a change was made on the LIVE DB to a table that has an AUTO_INCREMENT key column. So when we went to apply the changes the column id value already existed.

My question is, is there a way around this? Is there some option that allows for just inserts without the need for keys to be the same?

  • It appears it's a statement based replication. You make one database use odd numbers and the other one uses even numbers for auto_increment. – N.B. Apr 07 '15 at 12:00

1 Answers1

0

It appears it's a statement based replication. You make one database use odd numbers and the other one uses even numbers for auto_increment. - NB

Thanks NB,

That worked a treat. This is what I did to implement the fix.

Add the following to your my.cnf/my.ini file.

STAGE DB

auto_increment_increment = 2

auto_increment_offset = 1

LIVE DB

auto_increment_increment = 2

auto_increment_offset = 2