Introduction

During large MySQL imports, a few bad rows can stop the whole process unless you choose an error-tolerant strategy. The right approach depends on whether you can skip invalid rows, rewrite duplicates, or must fail immediately for data quality. This guide covers practical ways to continue imports while still tracking what was skipped.

Choose the Right Ignore Strategy

MySQL offers multiple mechanisms, each with different behavior.

• mysql --force: continue executing SQL statements after errors.
• INSERT IGNORE: convert certain errors to warnings and skip problematic rows.
• LOAD DATA ... IGNORE: ignore duplicate key conflicts during bulk file load.
• ON DUPLICATE KEY UPDATE: keep import flowing by updating existing rows.

Use the least permissive strategy that matches your migration rules.

Continue a SQL Script with mysql --force

If your import is a SQL dump with many statements, --force keeps going when one statement fails.

mysql --host=127.0.0.1 --user=app --password --database=mydb --force < dump.sql

This is useful for non-critical historical data loads where partial success is acceptable.

Capture output to review failures later.

mysql --user=app --password --database=mydb --force < dump.sql > import.log 2>&1

Skip Row-Level Insert Errors with INSERT IGNORE

INSERT IGNORE turns some errors into warnings, including duplicate key conflicts and certain invalid conversions.

1INSERT IGNORE INTO customers (id, email, age)
2VALUES
3  (1, '[email protected]', 31),
4  (1, '[email protected]', 22),
5  (2, '[email protected]', -1);
6
7SHOW WARNINGS;

Rows that violate unique constraints are skipped instead of aborting the statement.

Bulk CSV Import with LOAD DATA ... IGNORE

For high-volume file imports, this is typically fastest.

1LOAD DATA LOCAL INFILE '/tmp/customers.csv'
2IGNORE
3INTO TABLE customers
4FIELDS TERMINATED BY ','
5OPTIONALLY ENCLOSED BY '"'
6LINES TERMINATED BY '\n'
7IGNORE 1 LINES
8(id, email, age);

IGNORE helps with duplicate keys. For bad formats, behavior depends on SQL mode and target column types.

Control Strictness with SQL Mode

Error handling changes significantly under strict mode. Check current mode first.

SELECT @@sql_mode;

If strict mode is enabled, invalid values may throw errors. In less strict modes, MySQL may coerce values and emit warnings. For migration jobs, decide mode intentionally and document it in runbooks.

Prefer Staging Tables for Risky Imports

When quality is uncertain, import into staging first, then validate and merge.

1CREATE TABLE staging_customers LIKE customers;
2
3LOAD DATA LOCAL INFILE '/tmp/customers.csv'
4INTO TABLE staging_customers
5FIELDS TERMINATED BY ','
6OPTIONALLY ENCLOSED BY '"'
7LINES TERMINATED BY '\n'
8IGNORE 1 LINES;

Then run explicit cleanup queries, reject bad rows, and finally merge into production tables with audited logic.

Auditing Skipped or Updated Rows

Ignoring errors without auditing can hide data corruption. After each import, collect counts and warnings.

SELECT ROW_COUNT() AS affected_rows;
SHOW WARNINGS LIMIT 50;

You can also store rejects in a dedicated table by preprocessing files or using ETL tooling before MySQL ingestion.

Use Upsert When Skipping Is Not Acceptable

Sometimes duplicates should update existing rows instead of being dropped. In that case, upsert is safer than IGNORE.

1INSERT INTO customers (id, email, age)
2VALUES (1, '[email protected]', 33), (2, '[email protected]', 28)
3ON DUPLICATE KEY UPDATE
4  email = VALUES(email),
5  age = VALUES(age);

This keeps imports idempotent and preserves latest values. It also reduces manual reconciliation when rerunning migration scripts after partial failures.

Common Pitfalls

• Using --force in production imports without reviewing logs afterward.
• Assuming INSERT IGNORE handles every error type.
• Importing directly into live tables when source quality is unknown.
• Changing SQL mode temporarily and forgetting to restore it.
• Skipping duplicate rows silently when business rules require updates instead.

Summary

• MySQL can continue imports with --force, INSERT IGNORE, or LOAD DATA ... IGNORE.
• Each option trades data strictness for throughput and resilience.
• Use staging tables when data quality is uncertain.
• Inspect warnings and import logs after every run.
• Treat error ignoring as controlled exception handling, not default behavior.