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. - 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