The restore that dropped our staging tables: a foreign key nobody restored

On 2026-09-28 I restored a dump over a staging MySQL database with my own tool, TunnelForge. The restore stopped partway with this error:

ERROR 6125 (HY000): Failed to add the foreign key constraint.
Missing unique key for constraint '...' in the referenced table '...'

That error alone would have cost me a retry. But the import mode I used had already dropped every selected table up front. It then created and loaded them one by one, and it stopped on the first error with no rollback. At that point every selected table it had not reloaded yet was gone.

The source dump was intact, so I could restore the data again. A later restore into an isolated database loaded every table, though attributes the old dump never recorded could not come back. The failure mode does not depend on my tool. Any restore that drops and recreates tables can walk into it.

A table the dump does not know about

Staging databases drift. Someone adds a table there that production does not have, and that table has a foreign key into a table you are about to restore.

In my case the target-only foreign key did not point at a primary key. It pointed at a column covered by a secondary UNIQUE key, which MySQL allows. The restore recreated the parent table with only its primary key and planned to add the UNIQUE key after loading the data, a common way to make loads faster.

On CREATE TABLE, MySQL re-binds any existing foreign keys that reference that table name, even with foreign_key_checks = 0. If the key they need is not there yet, CREATE TABLE fails. By then the old parent table is already dropped.

A minimal repro

This reproduces on stock MySQL 8.0 and 8.4 servers.

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB;

-- A table that exists only in the target, with an FK to a non-PK UNIQUE column
CREATE TABLE audit_log (
  id INT PRIMARY KEY,
  user_email VARCHAR(100),
  CONSTRAINT fk_audit_user_email FOREIGN KEY (user_email) REFERENCES users (email)
) ENGINE=InnoDB;

-- What a restore of "users" does:
SET foreign_key_checks = 0;
DROP TABLE users;              -- succeeds
CREATE TABLE users (           -- fails: the UNIQUE key would only come after loading
  id INT PRIMARY KEY,
  email VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

On MySQL 8.4 the last statement fails with ERROR 6125 ... Missing unique key for constraint 'fk_audit_user_email'. On 8.0 the same script fails with ERROR 1822 ... Missing index for constraint. Either way, users is gone and audit_log is still there, pointing at nothing.

If you put the UNIQUE KEY inside the CREATE TABLE, the same sequence succeeds and audit_log keeps its row.

Why it hurt so much

The error concerned one parent table. The order of operations turned it into a much bigger loss:

1. Drop every selected table.
2. Create and load them one at a time.
3. Stop at the first error.

With that order, any failure in step 2 leaves the target missing every selected table the restore had not reached yet. My blast radius was the whole selection.

Lessons that apply to any restore

Run a preflight before you change anything. Before dropping a table, look for foreign keys outside the restore set that reference it (information_schema.KEY_COLUMN_USAGE / REFERENTIAL_CONSTRAINTS on MySQL). For each one, check that the table you are about to create will have a matching primary key or full-column UNIQUE key at CREATE TABLE time. If not, refuse with a clear message while the target is still untouched.

Keep the blast radius to one table. If you must restore in place, do it the way mysqldump output does: for each table, DROP, CREATE, load, then move on. A failure then costs you the table in progress, not everything after it.

Better still, restore into a new, empty schema instead of in place. Nothing else depends on that schema, so you can drop and recreate tables there as you like.

Verify before you swap. Once the new copy is loaded, compare it with the dump: row counts, plus content checksums and schema objects if you can. Only then swap it in under the original name, and keep the old data under a backup name instead of deleting it. On MySQL a multi-table RENAME TABLE is atomic, so the swap is one statement.

Also keep a backup of the target you are about to overwrite. The steps above do not replace it.

What TunnelForge does now

TunnelForge is an open-source (MIT) desktop app for MySQL and PostgreSQL behind SSH bastions. Its dump/import runs in a Rust core. After the incident I made these changes.

• Safe restore is the default. It restores the whole dump into a new namespace, verifies rows, values (streamed SHA-256 digests) and schema, and leaves the existing database unchanged while it does that.
• Overwrite runs safe restore first, then swaps the verified copy in under the original name. On MySQL the swap is one atomic multi-table RENAME. The replaced data stays under a backup name. If the swap plan is not clearly safe, it falls back to asking you.
• Replace is still there as an advanced option for people who want mysqldump behavior, but it now works table by table: drop, create, load. A failure leaves at most the table in progress missing or partial. Tables it had not reached keep their data, but can lose foreign keys to parents that were already replaced. The result lists the lost tables, the untouched tables, and the foreign keys removed from untouched tables.
• A surviving-FK preflight runs before any table is dropped. Foreign keys from tables outside the restore set (including other schemas) must reference a primary key or full-column UNIQUE key that exists at create time, and MySQL UNIQUE keys are now created inside CREATE TABLE. Otherwise TunnelForge refuses the import and changes nothing.

These checks run in CI against live MySQL and PostgreSQL servers on every pull request. The new MySQL tests fail on the old code and pass on the new code.

The code is on GitHub, and docs/export_import_policy.md explains how each import mode behaves.

If you restore over shared databases, check what else points at the tables you are about to drop before you drop them.

Comments

[Vlad Tkachenko]

Postgres gets a quieter version of this if you restore the way Supabase's guide does: roles, schema and data as separate files, with SET session_replication_role = replica before the data so load order doesn't matter.
Foreign keys are triggers in Postgres, so replica mode switches those off too (the docs say so in the entry for that setting). If a selection has a child table but not its parent, the rows go in pointing at nothing, without an error, and row counts and digests against the dump all match, because the rows really are the ones in the dump. I'd count orphans per FK after any replica-mode load.
Does the safe-restore path check that on the Postgres side?

[Bulgasari]

Yes. TunnelForge doesn't load in replica mode. It loads the data first, then adds each foreign key with a plain ALTER TABLE ... ADD CONSTRAINT (no NOT VALID), so Postgres validates the existing rows at that point. Safe restore then counts orphans per FK on the restored copy with a LEFT JOIN from child to parent, and it won't swap the copy in if any count is above zero. The export side can't produce a child without its parent either: selecting a child table pulls its FK parents into the dump.

Thanks for writing this up.