Sync Databases With Overlapping Primary Keys

[Drin Brown]

Hi,

So I have a web app that runs off a database which stores content and user data. I have a dev and a prod copy of the app, and their databases. On dev I build out new content, which I want to move to prod without disturbing the content which is already on prod, or the user data. I am trying to use database sync with SQLYog but when I generate the sync script and then run it, it errors out on duplicate primary key entries. I inherited this database schema and it doesn't have any referential constraints build in – is this the problem, or is something else the problem?

thanks,

ab

[peterlaursen]

Please details “it errors out on duplicate primary key entries”.  Exactly what error do you get?

It he problem is that for rows with identical PK-values 'source will overwrite target' then we have replied to this questions many times now.  The purpose of a PK is and always was to identify rows uniquely. All reasonable sync tools assume this and even the SQL language itself does in some contexts (example:  INSERT ..  ON DUPLICATE KEY …).  Sync'ing means “making identically identified rows identical”.  In that case you will need to define the PK in such a way that the same PK-value is not created on different hosts. At least two ways to do this:

1) Add a column to each table, make it constant on each host (ie: define a DEFAULT) but different between hosts.  Add that column to the PK.

2) Define auto_increment_increment and auto_increment_offset server variables so that auto_increment values will be created as even values on one host and odd values on the other (or start with “1” on one server and “1000000” on the other. This requires of course that the PK is an autoincrement column.

If you have started collecting data both solutions will require some work – in particular if you have Foreign Keys to the PK.

[Author]

Posts