Avoiding Hidden Duplicate Key Errors During Character Set Migration (latin1 / utf8mb3 to utf8mb4)

Database migrations often look simple on paper: move schema, move data, validate counts, and cut over. But in real production environments, migrations involving character set and collation changes can introduce issues that are easy to miss until the restore starts failing.

One common problem appears when moving data from older MySQL environments using latin1 or utf8mb3 into newer environments standardised on utf8mb4 with utf8mb4_0900_ai_ci (or similar case-insensitive collations).

At first glance, everything may seem compatible. But hidden inside the data, values that were previously treated as distinct can suddenly become duplicates.

Example:

John.Smith@company.com
john.smith@company.com

In the source system, these two values may coexist in a column protected by a unique key, depending on the original collation. But in the target system using a case-insensitive collation, both values compare as equal.

That means the restore can fail with duplicate key errors.

Why This Happens

Source Environment

Many legacy systems use:

• latin1
• utf8mb3
• older collations that may be binary or case-sensitive

This allows values such as:

John.Smith@company.com
JOHN.SMITH@company.com
john.smith@company.com

to be stored as separate rows if the collation treats them differently.

Target Environment

Modern MySQL deployments usually move to the following:

CHARACTER SET utf8mb4

COLLATE utf8mb4_0900_ai_ci

Where:

• ai = accent insensitive
• n- ci = case insensitive

So these values are considered the same:

John.Smith@company.com = JOHN.SMITH@company.com = john.smith@company.com

If a unique index exists, MySQL rejects duplicates during import.

Real Migration Error Example

During restore or replication catch-up, you may see errors like:

ERROR 1062 (23000): Duplicate entry 'john.smith@company.com' for key 'uk_email'

Or while loading a dump:

ERROR: [Worker12] Failed processing table appdb. Users:Duplicate entry 'John.Smith@company.com' for key 'uk_email'

Where This Causes Problems Most Often

Watch these objects carefully:

• Primary keys using VARCHAR columns
• Unique indexes on usernames / emails / codes
• Natural keys from business applications
• Composite unique indexes containing text columns
• Lookup tables with short code values

Examples:

UNIQUE KEY uk_email(email)
UNIQUE KEY uk_code(region_code, customer_code)
PRIMARY KEY(username)

Best Practice: Validate Before Migration

The most effective approach is to identify and resolve collation conflicts before the migration begins rather than during the restore process.

Before migrating, perform a thorough validation of the source data to detect rows that would become duplicates under the target character set and collation rules. This is particularly important when migrating from case-sensitive collations (such as latin1 or utf8mb3 case-sensitive collations) to case-insensitive collations like utf8mb4_0900_ai_ci.

Validation Query for Single Column Unique Key

Suppose this table has a unique key on email_id.

CREATE TABLE UserLogin (
  id INT,
  email_id VARCHAR(150),
  UNIQUE KEY uk_email(email_id)
);

Run: The query below identifies case-sensitive duplicate values that will become duplicates in a case-insensitive collation.

SELECT LOWER(email_id) AS normalized_value,
       COUNT(*) AS duplicates,
       GROUP_CONCAT(email_id ORDER BY email_id) AS conflicting_rows
FROM UserLogin
GROUP BY LOWER(email_id)
HAVING COUNT(*) > 1;

Sample Output:

+------------------------+------------+-------------------+
| normalized_value       | duplicates | conflicting_rows  |
+------------------------+------------+-------------------+
| john.smith@company.com |          2 | John.Smith@company.com,john.smith@company.com |
| hr.support@company.com |          2 | HR.Support@company.com,hr.support@company.com |
+------------------+------------+-------------------+

This immediately shows rows that may fail after migration.

Better Validation Using Target Collation Directly

LOWER() detects only case-insensitive duplicates. Therefore, using the target collation directly provides a more accurate pre-migration validation and helps identify all values that would become duplicates after converting to utf8mb4_0900_ai_ci, preventing restore failures and duplicate-key errors during migration.

SELECT email_id COLLATE utf8mb4_0900_ai_ci AS normalized_value,
       COUNT(*) AS duplicates,
       GROUP_CONCAT(email_id) AS conflicting_rows
FROM UserLogin
GROUP BY email_id COLLATE utf8mb4_0900_ai_ci
HAVING COUNT(*) > 1;

This is more accurate because it also catches accent-insensitive matches.

Example:

resume
résumé

These may compare equal in some collations.

Validation for Composite Unique Keys

If the index is:

UNIQUE KEY uk_customer(country_code, customer_name)

Use:

SELECT country_code,
       customer_name COLLATE utf8mb4_0900_ai_ci AS normalized_name,
       COUNT(*) AS duplicates,
       GROUP_CONCAT(customer_name) AS rows_found
FROM customers
GROUP BY country_code,
         customer_name COLLATE utf8mb4_0900_ai_ci
HAVING COUNT(*) > 1;

How to Fix Conflicts

Option 1: Clean Duplicate Data

Decide which row should remain.

John.Smith@company.com
john.smith@company.com

Keep one standard value:

john.smith@company.com

Option 2: Merge Business Records

If duplicates belong to the same user/customer, merge references first.

Recommended Migration Workflow

Step 1 – Assess the Source Schema

Identify the current character set and collation used by the tables and columns.

SHOW CREATE TABLE UserLogin;

Understanding the source collation helps determine whether migrating to the target collation could introduce comparison rule changes and potential data conflicts.

Step 2 – Identify Unique Text Columns

Use information_schema.STATISTICS to identify columns that participate in PRIMARY KEY or UNIQUE indexes.

These columns require special attention because values that are currently distinct may become duplicates under the target collation, causing migration or index creation failures.

Step 3 – Validate Data Using the Target Collation

Run duplicate detection queries by explicitly applying the target collation (for example, utf8mb4_0900_ai_ci) during comparison.

This accurately simulates how MySQL will treat the data after migration and helps identify conflicts caused by case or accent insensitivity.

Step 4 – Resolve Data Conflicts at the Source

Standardise, merge, rename, or remove conflicting values before exporting the data.

Resolving duplicates at the source ensures that the target environment can enforce constraints successfully without manual intervention during the migration.

Step 5 – Perform a Test Migration in a Lower Environment

Execute a complete export and import in a non-production environment and validate data, indexes, application functionality, and object creation.

Testing beforehand significantly reduces risk and helps uncover issues that may not be evident during pre-checks.

Step 6 – Execute the Production Migration

Once validation and testing are successfully completed, perform the final export and import into the production environment.

Since the schema and data have already been verified, the production migration becomes more predictable, minimizes downtime, and greatly reduces the likelihood of restore failures or duplicate-key errors.

Real-World Lesson

Many validate only row counts, object counts, and checksum totals during migrations. While these checks confirm that data has been copied correctly, they do not detect issues introduced by changes in character set and collation rules.

Character set migrations also require comparison-rule validation. Values that are considered distinct in the source environment may become identical under the target collation. As a result, two rows that coexist today can be treated as a single duplicate after migration, leading to duplicate-key errors, failed imports, or unsuccessful index creation.

These hidden comparison-rule conflicts are one of the most common reasons character set and collation migrations fail unexpectedly.

Final Thoughts

Upgrading from latin1 or utf8mb3 to utf8mb4 provides full Unicode compliance, modern emoji support, and multi-language compatibility. However, character set upgrades change how strings are compared and indexed.

Validating unique key columns with target collations before cutover ensures predictable database migrations.

Need Help Planning Your MySQL Database Migration?

Upgrading character sets or migrating production MySQL databases requires careful planning to prevent unexpected downtime and data conflicts. The database engineers at Mydbops provide end-to-end support for schema optimization, version upgrades, and cloud migrations.