Database sync: why a raw SQL replace corrupts serialised data

Pulling production data down to a local machine is one of the most repeated jobs in a development cycle, and it is where the classic accident happens: you replace the domain with SQL and afterwards the widget settings, theme options and plugin configuration have all vanished. Understand the cause and the accident becomes entirely avoidable.

Serialisation writes the length inside the value

When WordPress stores an array or object in wp_options or wp_postmeta, it uses PHP’s serialisation format. The important property of that format is that every string carries its own length.

-- original
a:1:{s:8:"site_url";s:19:"https://example.com";}

-- after a SQL REPLACE — the string is now 27 bytes, the declaration still says 19
a:1:{s:8:"site_url";s:19:"https://staging.example.com";}

-- after wp search-replace — the length is rewritten too
a:1:{s:8:"site_url";s:27:"https://staging.example.com";}

The middle line is the whole accident. s:19: declares “the next 19 bytes are the string”, and the content is 27. PHP’s unserialize() cannot recover that value, returns false, and WordPress behaves as though the option does not exist.

That is what makes the symptom so unpleasant: nothing errors. The page returns 200, only the widgets are empty, only the theme options have reverted to defaults. It reads as “we lost some settings during the migration” rather than the data is corrupt. And because a replacement of identical length (example.com to example.dev) works perfectly, teams keep using the same method until the day a length changes.

The correct procedure

wp search-replace unserialises, replaces and re-serialises, so the lengths are always right, and it recurses into nested arrays and objects.

# 1) dump on production
wp db export prod.sql --add-drop-table

# 2) load it locally
wp db import prod.sql

# 3) always rehearse first
wp search-replace 'https://example.com' 'https://example.test' --skip-columns=guid --report-changed-only --dry-run

# 4) then do it for real
wp search-replace 'https://example.com' 'https://example.test' --skip-columns=guid --report-changed-only

wp cache flush

Do not omit --skip-columns=guid. The guid is an identifier, not a URL. It merely looks like an address, and feed readers use it to decide what they have already seen — rewrite it and every subscriber receives your entire archive as new posts. WordPress has said to leave it alone for a very long time.

For multisite, or when plugins keep their own tables, reach for --all-tables-with-prefix or --all-tables. The default touches only the current site’s tables, so URLs parked in a plugin’s custom table survive a default run untouched.

Direction is a rule too

One rule outranks the procedure: code flows up, data flows down. The moment you push a local database up to production, every order, enquiry and signup that arrived since you took the dump is gone.

There is also work to do straight after the import: switch off indexing (wp option update blog_public 0), disable payment and mail integrations, and deal with the personal data now sitting on a laptop. Making sure a copy taken for convenience does not become a copy of your customers’ data is closer to an obligation than a step.

Migration and deployment continue in the development workflow archive, and if you would rather hand the migration, optimisation and security review over as one job, that is the scope of our optimization program.

Next part

You can now move data safely; where to test it is what remains. The next part is staging — and why a staging environment that differs from production turns your tests into lies.