Understanding MySQL --force and extended‑insert behavior during data import

In data‑compensation scenarios, the MySQL --force option is often combined with backup imports to skip rows that conflict with existing data, preventing batch termination; however, this can lead to partially imported backup data.

We reproduce the problem step by step:

Prepare a sbtest1 table and verify it contains 10,000 rows.

mysql> select count(*) from testdb.sbtest1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

mysql> desc sbtest1;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id    | int(11)   | NO   | PRI | NULL    |       |
| k     | int(11)   | NO   |     | 0       |       |
| c     | char(120) | NO   |     |         |       |
| pad   | char(60)  | NO   |     |         |       |
+-------+-----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

Backup the table data with mysqldump --no-create-info .

mysqldump -h127.0.0.1 -P7777 -uroot -p --default-character-set=utf8mb4 \
  --set-gtid-purged=off --single-transaction --no-create-info \
  --tables testdb sbtest1 > sbtest1.sql

Manually delete most rows, keeping only a few to simulate conflicts.

mysql> delete from testdb.sbtest1 where id !=5000;
Query OK, 9999 rows affected (0.08 sec)

mysql> select * from testdb.sbtest1;
+------+------+---------------------------------------------------------------+---------------------------------------------------------------+
| id   | k    | c                                                             | pad                                                         |
+------+------+---------------------------------------------------------------+---------------------------------------------------------------+
| 5000 | 4997 | 88923911551-62811085158-27667899446-...                       | 39063801052-61512609987-53254072797-...                     |
+------+------+---------------------------------------------------------------+---------------------------------------------------------------+
1 row in set (0.00 sec)

Import the backup using --force .

[root@jy-dmp08 ~]# mysql -h127.0.0.1 -P7777 -uroot -p testdb --force < sbtest1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 24: Duplicate entry '5000' for key 'PRIMARY'
# execution stops at the conflicting row

Check how many rows were imported.

mysql> select count(*) from testdb.sbtest1;
+----------+
| count(*) |
+----------+
|     4684 |
+----------+
1 row in set (0.00 sec)

The --force option skips the entire INSERT statement that contains the conflict because the dump uses the default --extended-insert mode, which groups many rows into a single multi‑row INSERT.

The relevant mysqldump option is:

-e, --extended-insert
    Use multiple-row INSERT syntax that include several VALUES lists.
    (Defaults to on; use --skip-extended-insert to disable.)

Counting the INSERT statements in the dump shows only two multi‑row statements; the first contains the duplicate id=5000 , so all rows in that statement are skipped, leaving only 4,684 rows.

# Count INSERT statements in the dump
[root@jy-dmp08 ~]# grep -i 'insert' sbtest1.sql | wc -l
2

When the dump is created with --skip-extended-insert , each row is inserted individually, and --force only skips the conflicting row, preserving the rest of the data.

mysqldump -h127.0.0.1 -P7777 -uroot -p --default-character-set=utf8mb4 \
  --set-gtid-purged=off --single-transaction --no-create-info \
  --skip-extended-insert --tables testdb sbtest1 > sbtest1.sql

[root@jy-dmp08 ~]# mysql -h127.0.0.1 -P7777 -uroot -p testdb --force < sbtest1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 5023: Duplicate entry '5000' for key 'PRIMARY'
# only the conflicting row is reported

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

Usage Recommendations

When to use --force

Generally, avoid using --force for backup imports.

Use it only when you know the target table already contains conflicting rows and you want the import to continue without stopping.

It provides error messages for each conflicting row, aiding later investigation.

Limitations of --force

It discards the entire INSERT statement that contains a conflict in multi‑row mode, leading to data loss.

To avoid this, the dump must be created with --skip-extended-insert , which disables multi‑row inserts.

Disabling extended inserts increases dump size and import time, so it is suitable only for relatively small tables.