MySQL synchronization between 2 databases and primary key conflict

Question

I have a problem with a MySQL database for development and production. I want to combine the two databases, but since content authors added entries to the production environment, there are about 20 overlapping entries with the same primary key.

I use Navicat to synchronize data, but it just updates the records on the production server using the ones on my development server. Primary keys mean nothing, and I would like to make these entries new primary keys.

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

Thanks.

Answer 1

I cannot answer for what Navicat is capable of, but in MySQL you can easily set the value to auto_increment. I recommend doing all this in a transaction if they live with 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 using variables.