Troubleshooting Common MySQL Migration Issues and Data Transfer Errors

MySQL migrations fail in a few familiar ways. The import stops on a foreign key. Characters turn into question marks. A dump from MySQL 5.7 does not load cleanly into MySQL 8.0. The data loads, but the application breaks because a stored routine, trigger, user, or SQL mode did not come across the way you expected. None of these problems is unusual, but they are much easier to handle when you treat the migration as a repeatable process instead of a one-time copy.

The best migration habit is to rehearse. Take a real backup, restore it into a staging target, run the same import commands you plan to use in production, and write down every warning. A rehearsal tells you whether your dump is complete, whether your target configuration is compatible, and how long the load actually takes. It also gives you a rollback plan that is more realistic than "we will figure it out during the maintenance window."

Start by Identifying the Kind of Failure

When a migration breaks, do not start changing server variables randomly. Put the error into one of these buckets:

• Compatibility: version differences, reserved words, removed features, changed defaults.
• Encoding: character set and collation mismatches.
• Constraints: foreign keys, unique keys, check constraints, generated columns.
• Object coverage: missing triggers, routines, events, views, users, or grants.
• Performance: import is too slow, disk is full, binary logs are growing, indexes take too long.
• Application behavior: data imported, but queries or writes behave differently.

That classification gives you the next command to run. A duplicate key error and a corrupted emoji are both "migration issues", but they have completely different causes.

Version Mismatches: MySQL 5.7 to 8.0 and Similar Jumps

Major version upgrades are where many surprises appear. MySQL 8.0 changed defaults, reserved words, authentication behavior, data dictionary internals, and optimizer behavior compared with 5.7. Some old syntax still works; some does not. MariaDB adds another compatibility layer because it is not a drop-in replacement for every MySQL feature.

Before the migration, capture source settings:

SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

On the target, run the same checks and compare. sql_mode deserves special attention. A dump that loads on a permissive source can fail on a stricter target with errors such as invalid dates, missing defaults for NOT NULL columns, or zero dates that are no longer accepted under the target mode.

If you hit an error like:

ERROR 1067 (42000): Invalid default value for 'created_at'

do not immediately relax sql_mode forever. First inspect the table definition and the data. You may need to fix defaults, convert zero dates, or update application assumptions. Temporarily matching the source sql_mode during import can help you complete a staged restore, but production should move toward a known, explicit mode that your application has been tested against.

Reserved words can also break old schemas. A column or table named rank, groups, or another newer reserved word may need quoting or renaming. If the error appears in DDL, inspect the exact statement from the dump and test a corrected version on the target.

Authentication Plugin Problems

A migration that includes an application cutover often fails before any query runs because clients cannot authenticate. MySQL 8.0 commonly uses caching_sha2_password by default, while older clients may expect mysql_native_password.

Check the target users:

SELECT user, host, plugin FROM mysql.user;

The better fix is usually to update the client library or driver. If that is not possible before cutover, you may need a temporary compatibility account:

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'new_secret';

Treat this as a compatibility decision, not a generic best practice. Authentication settings affect security, and the right answer depends on your client versions and risk model.

Character Set and Collation Issues

Character set problems are painful because the import can finish successfully while the data is already damaged. The classic symptom is ?, mojibake, broken accented characters, or failed inserts involving emoji.

Check the source database and table definitions:

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = 'appdb';

Check columns too:

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = 'appdb'
  AND character_set_name IS NOT NULL;

For most modern applications, utf8mb4 is the right target character set because it supports the full Unicode range, including emoji. MySQL's older utf8 name is not the same as full UTF-8 in older versions; it is commonly a 3-byte character set.

When dumping and importing, be explicit:

mysqldump --default-character-set=utf8mb4 -u user -p appdb > appdb.sql
mysql --default-character-set=utf8mb4 -u user -p appdb < appdb.sql

If the source data is actually latin1, do not blindly declare it utf8mb4 and hope. First determine whether the bytes are valid in the source encoding. Some old systems contain "double-encoded" data where the column claims one character set but the application stored bytes from another. That needs a tested conversion, not a global search and replace.

Collation differences can also change behavior. Sort order, uniqueness comparisons, and case sensitivity may differ between collations. If a unique index fails during migration, check whether the target collation treats two strings as equal when the source did not.

Foreign Key Failures

Foreign key errors usually mean one of four things:

• Child tables were imported before parent tables.
• The dump is partial and missing referenced rows.
• Source data already had inconsistent references.
• The target schema differs from the source.

The common bulk-load workaround is:

SET FOREIGN_KEY_CHECKS = 0;
-- import data
SET FOREIGN_KEY_CHECKS = 1;

This can be appropriate for a full logical restore from a trusted dump. It is not a cleanup tool. Re-enabling FOREIGN_KEY_CHECKS does not fully revalidate every existing row in the way many people assume, so you can import bad relationships and not notice until later.

If you are merging data or importing only part of a schema, keep checks enabled where possible and load parent tables first. If you must disable checks, run validation queries afterward. For example:

SELECT c.*
FROM orders c
LEFT JOIN customers p ON p.id = c.customer_id
WHERE c.customer_id IS NOT NULL
  AND p.id IS NULL
LIMIT 20;

Use queries like that for your real relationships, especially high-value tables such as orders, payments, accounts, and permissions.

Duplicate Key Errors

A duplicate key error means the target already has a value that the incoming data wants to insert:

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

If the target should be an exact copy, the clean fix is usually to drop and recreate the target database, then import again. A half-loaded target is not a good starting point for a second attempt unless your process was designed to resume.

If you are merging data, decide the conflict policy before the import. INSERT IGNORE hides duplicates by skipping rows. REPLACE INTO deletes the existing row and inserts the new one, which can fire cascades and change auto-updated columns. ON DUPLICATE KEY UPDATE is more explicit, but it still needs careful rules.

For migrations, I prefer staging tables for merges. Load incoming data into staging_* tables, inspect conflicts, then write deliberate INSERT ... SELECT or UPDATE ... JOIN statements. It is slower to design, but it avoids silently throwing away data.

Missing Triggers, Routines, Events, and Views

A migration can look successful because tables and rows exist, while important database logic is missing. mysqldump options matter:

mysqldump -u user -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  appdb > appdb.sql

Views and routines can fail on import because of definer accounts. A view may reference:

DEFINER=`old_user`@`old_host`

If that account does not exist on the target, the object may fail to create or fail when used. You can either create the needed definer account with appropriate privileges, or adjust definers during a controlled migration process. Do not strip definers blindly without understanding the security model of the application.

After import, compare object counts:

SELECT ROUTINE_TYPE, COUNT(*)
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'appdb'
GROUP BY ROUTINE_TYPE;

SELECT TRIGGER_SCHEMA, COUNT(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'appdb'
GROUP BY TRIGGER_SCHEMA;

Also check scheduled events if your application relies on them:

SHOW EVENTS FROM appdb;

Slow Imports and Large Tables

Large imports are usually limited by disk I/O, index maintenance, binary logging, foreign key checks, or transaction size. Before tuning, watch the target:

iostat -xz 1
df -h
top

For logical dumps, use extended inserts. mysqldump does this by default in most cases, but confirm you are not using --skip-extended-insert unless you need human-readable diffs more than speed.

For InnoDB imports, a larger innodb_buffer_pool_size can help if the target has memory available. Do not set it so high that the OS starts swapping. During a one-time load, some teams temporarily relax durability settings such as innodb_flush_log_at_trx_commit or disable binary logging for the import session. Those choices trade crash recovery or point-in-time recovery for speed, so they should be used only when you can afford to restart the import from a known backup.

If the target is also a replication source, be careful with binary logs. Disabling binary logging may speed up import, but downstream replicas will not receive those changes. In a topology with replicas, decide where the import should happen and how changes should flow before turning logs off.

For very large tables, consider physical backup tools or MySQL Shell dump and load utilities instead of plain mysqldump. Logical dumps are portable and easy to inspect, but they are not always the fastest path for multi-hundred-gigabyte datasets.

Disk Space Failures

Disk failures during migration are common and avoidable. You need space for the dump file, the imported data, indexes, temporary files, binary logs, and sometimes double storage while tables are rebuilt.

Check before import:

df -h
du -sh /var/lib/mysql

Inside MySQL, check table sizes:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_gb DESC;

If the import fails because disk fills up, do not just delete random files from the data directory. Free space safely, inspect whether the target is partially loaded, and decide whether to restart from scratch.

Post-Migration Validation

A migration is not done when the import command exits. Validate the result.

Start with row counts for important tables:

SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM payments;

Row counts alone are not enough. Compare sums or checks for business-critical data:

SELECT COUNT(*), SUM(total_amount), MIN(created_at), MAX(created_at)
FROM orders;

Run the same queries on source and target during the final quiet period. For tables that continue changing during migration, use a planned freeze, replication catch-up, or application-level reconciliation.

Test application workflows against the target before cutover:

• Login and session creation.
• Creating and updating core records.
• Searches and reports that rely on collations or indexes.
• Background jobs, triggers, and scheduled events.
• Permission checks and admin actions.

The application test matters because the database can be technically imported but behaviorally wrong.

A Practical Migration Triage Checklist

When a migration error appears, use this sequence:

1. Save the exact error message and the failing SQL statement if available.
2. Identify the bucket: compatibility, encoding, constraint, object coverage, performance, or application behavior.
3. Compare source and target MySQL versions, sql_mode, character sets, and collations.
4. For constraint errors, inspect the specific parent and child rows or duplicate keys.
5. For encoding issues, stop importing until you know whether the source bytes are valid and how the client connection is interpreting them.
6. For slow imports, check disk, memory, binary logs, and index maintenance before changing random variables.
7. After a fix, rerun the migration in staging before applying it to production.

The most reliable MySQL migration is the one you can throw away and repeat. Keep commands in a script, keep configuration changes documented, and make validation part of the plan rather than a hopeful final glance.