Updating MySQL primary key

Next time, use a single "alter table" statement to update the primary key.

alter table xx drop primary key, add primary key(k1, k2, k3);

To fix things:

create table fixit (user_2, user_1, type, timestamp, n, primary key( user_2, user_1, type) );lock table fixit write, user_interactions u write, user_interactions write;insert into fixit select user_2, user_1, type, max(timestamp), count(*) n from user_interactions u group by user_2, user_1, typehaving n > 1;delete u from user_interactions u, fixit where fixit.user_2 = u.user_2   and fixit.user_1 = u.user_1   and fixit.type = u.type   and fixit.timestamp != u.timestamp;alter table user_interactions add primary key (user_2, user_1, type );unlock tables;

The lock should stop further updates coming in while your are doing this. How long this takes obviously depends on the size of your table.

The main problem is if you have some duplicates with the same timestamp.

If the primary key happens to be an auto_increment value, you have to remove the auto increment, then drop the primary key then re-add the auto-increment

ALTER TABLE `xx`MODIFY `auto_increment_field` INT, DROP PRIMARY KEY, ADD PRIMARY KEY (new_primary_key);

then add back the auto increment

ALTER TABLE `xx` ADD INDEX `auto_increment_field` (auto_increment_field),MODIFY `auto_increment_field` int auto_increment;

then set auto increment back to previous value

ALTER TABLE `xx` AUTO_INCREMENT = 5;

You can use the IGNORE keyword too, example:

 update IGNORE table set primary_field = 'value'...............