MySQL sync between 2 databases and primary key clash

Question

I have an issue with a development and production environment MySQL database. I want to merge the 2 databases, but since content authors have been adding records to the production environment, there are about 20 overlapping records with the same primary key.

I am using Navicat for my data sync, but it just updates the records on the production server with those from my development server. The primary keys don't mean anything, and what I would like to do is give those records new primary keys.

Is this possible through Navicat? If not, would it be a bad idea to manually go into the db and change the primary keys? Or does this affect the auto-increment ability of MySQL?

Thanks.

Answer 1

I can't answer for what Navicat is capable of, but in MySQL, you can easily set the auto_increment value. I recommend doing the whole thing in a transaction if it's live data:

START TRANSACTION;
SELECT max(id)+1 INTO @new_id FROM mytable;
SET @range_start = [first ID you'd like to change];
SET @range_end = [last ID you'd like to change];
SET @offset = @new_id - @range_start;
UPDATE mytable SET id = id + @offset WHERE id BETWEEN @range_start AND @range_end;
SELECT @range_end + @offset + 1;
ALTER TABLE mytable AUTO_INCREMENT=[value returned by above statement];
COMMIT;

Note - the ALTER TABLE statement requires a constant instead of the use of variables.