Strategies for MySQL Primary Key Updates and Duplicate Data Handling

Problem Context and Challenges

Modifying primary keys in MySQL requires careful consideration, especially in production environments. When changing the order of composite primary keys, such as from (user_1, user_2, type) to (user_2, user_1, type), executing DROP PRIMARY KEY and ADD PRIMARY KEY statements directly can lead to significant data consistency issues.

In live server environments, during the temporary absence of primary key constraints, new duplicate records continuously enter the database. These duplicates typically share the same user_2, user_1, and type combinations but have different timestamp values. The primary challenge is to effectively identify and clean these duplicates while maintaining business continuity.

Core Steps of the Solution

To address this issue, we employ a phased approach. First, create a temporary table fixit to store information about duplicate records requiring processing:

CREATE TABLE fixit (
    user_2 INT,
    user_1 INT,
    type VARCHAR(50),
    timestamp DATETIME,
    n INT,
    PRIMARY KEY (user_2, user_1, type)
);

This table includes the original data columns, adds a count column n to track duplication frequency, and defines the primary key constraint in the new order.

Data Locking and Isolation

Before starting data processing, implement table-level locking to prevent concurrent operations from interfering:

LOCK TABLES fixit WRITE, user_interactions WRITE, user_interactions AS u WRITE;

This locking statement ensures that during data processing, the user_interactions table, its alias u, and the temporary table fixit are all write-locked, effectively blocking new data inserts and updates.

Duplicate Data Identification and Processing

Next, use an aggregate query to identify all duplicate records, preserving the entry with the latest timestamp:

INSERT INTO fixit 
SELECT user_2, user_1, type, MAX(timestamp), COUNT(*) AS n 
FROM user_interactions u 
GROUP BY user_2, user_1, type
HAVING n > 1;

This query groups data by the new primary key combination, uses the MAX(timestamp) function to determine the latest timestamp in each group, and filters groups with duplicates via the HAVING n > 1 condition.

Data Cleanup Operation

Based on the information in the temporary table, execute a delete operation to remove all duplicate records except those with the latest timestamp:

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;

This delete statement uses a multi-table join to precisely match records for deletion, retaining only the record with the maximum timestamp for each primary key combination.

Primary Key Update and Recovery

After data cleanup is complete, safely apply the new primary key constraint:

ALTER TABLE user_interactions ADD PRIMARY KEY (user_2, user_1, type);

Finally, release the table locks to resume normal database operations:

UNLOCK TABLES;

Performance Considerations and Potential Issues

The execution time of the entire process depends mainly on the table size and the number of duplicate records. For large tables, it is advisable to perform this operation during off-peak business hours to minimize impact on user experience.

A critical edge case arises when duplicate records share identical timestamps. In such scenarios, the described method cannot determine which record to retain, potentially requiring additional business logic or manual intervention. For instance, secondary sort criteria could be added, or human decision-making might be necessary.

Best Practice Recommendations

To avoid similar issues in the future, use atomic ALTER TABLE statements for primary key changes:

ALTER TABLE user_interactions 
DROP PRIMARY KEY, 
ADD PRIMARY KEY (user_2, user_1, type);

This single-statement approach minimizes the time window during which primary key constraints are absent, significantly reducing the risk of data inconsistency. Additionally, comprehensive data backups and testing environment validations are essential before implementing any structural changes.