Serialized Data Repair and Search Replace: The Bytes That Break a Migration

You move a site to a new domain. The export goes across, you run one UPDATE with REPLACE() over wp_options and wp_postmeta to swap the old host for the new one, you import, and the home page loads. No error, no warning, no stack trace. Then you notice the sidebar has no widgets, the logo is missing, and the plugins screen says nothing is active.

Nothing in the log names a row. The database connection is fine, the files are fine, the credentials are fine. What broke is arithmetic. PHP’s serialization format writes the byte length of every string into the string itself, and your REPLACE() changed the text without touching the number.

s:11:"hello world"; is not a description of what follows it. It is a promise that exactly eleven bytes come after the opening quote, and unserialize() takes the promise literally. Break it by one byte and the entire value comes back as false, not just the part you edited.

The number is a promise, not a label

Serialized PHP is a flat text format. A type letter, a colon, a payload, a terminator. There is no framing, no checksum and no way for a reader to resynchronise once it has lost its place.

s:11:"hello world";
i:42;
d:1.5;
b:1;
N;
a:2:{i:0;s:3:"one";i:1;s:3:"two";}
O:8:"stdClass":1:{s:3:"foo";s:3:"bar";}

Two of those numbers carry load. The one after s: is a byte count, not a character count, so a German umlaut in UTF-8 costs two and an emoji costs four. The one after a: and after the class name in O: is an element count, and the parser stops taking members the moment it reaches that many. Everything else is punctuation. Get either number wrong and the value is unreadable, which is exactly what a hand edit in phpMyAdmin does.

The Dynamic Design and Automation Studio

WunderPaint is a layered image editor for your WordPress media library. WunderPaint Studio is the same thing in any browser, free and without an account.

Open the Studio

Download the free plugin

What one REPLACE() does to a length prefix

Take a plugin option holding a single URL. Before the migration it looks like this, and it parses:

a:1:{s:8:"logo_url";s:47:"http://oldsite.test/wp-content/uploads/logo.png";}

Now run the query everybody runs:

UPDATE wp_options
SET option_value = REPLACE(option_value, 'http://oldsite.test', 'https://newsite.example')
WHERE option_value LIKE '%oldsite.test%';

MySQL does precisely what you asked. The URL is now 51 bytes long and still declares 47. unserialize() reads the opening quote, counts 47 bytes forward and expects to find a quote and a semicolon. It finds .png. It does not skip the member and carry on, because it cannot: it has no idea where the next member starts. It abandons the whole value and returns false. Your one key array is gone, along with everything else that row held.

The direction of the change does not matter. Shorter is as fatal as longer, because the parser then finds the closing quote early and reads punctuation as text. What does matter is that a lucky pair of hosts with the same byte count sails straight through, which is why this failure feels random across projects and why nobody learns the rule the first time.

Why the failure arrives without an error message

get_option() hands the raw column value to maybe_unserialize(). That function first asks is_serialized() whether the string looks serialized: does it have a colon in the second position, does it end in ; or }, does it start with a type letter that preg_match recognises. Looking right is the entire test. It then calls @unserialize( trim( $data ) ), and the @ in front means the “Error at offset” warning never reaches your error log.

So get_option( 'active_plugins' ) returns the boolean false. Core then does (array) get_option( 'active_plugins', array() ), and casting false to an array in PHP gives you an array holding one element, the boolean itself. That element is not a path ending in .php, so the loop skips it, and WordPress finishes booting with zero plugins loaded and nothing to say about it. The result is a site that looks like a fresh install wearing your theme.

The white screen, when it comes, comes from somebody else’s code a moment later. A foreach over false is only a warning. count( false ) has been a TypeError since PHP 8, and an uncaught TypeError is fatal. The fatal names the plugin file that called count(), never the option row that fed it, which is how people spend an afternoon deactivating innocent plugins. If you are staring at a blank page rather than a half dressed site, the white screen of death has its own diagnostic order, and it is worth walking that before assuming the data is at fault. If the page says “Error establishing a database connection” instead, nothing here applies: that message is about credentials and hosts in wp-config.php, not about the contents of a row.

Where WordPress keeps serialized data

Any value that arrives at update_option(), update_post_meta(), update_user_meta() or update_term_meta() as an array or an object goes through maybe_serialize() on the way in. That covers more of a WordPress database than most people expect.

• wp_options. active_plugins, sidebars_widgets, the roles map named with your table prefix plus user_roles, every plugin settings blob, and one widget_* row per widget type holding all its instances. The autoload column decides how loud the breakage is: wp_load_alloptions() pulls every row whose autoload value is one of yes, on, auto or auto-on on every single request, so a broken autoloaded option is a site wide problem from the first page view, while a broken non autoloaded one waits quietly until something asks for it, sometimes days later.
• Theme mods. Everything the Customizer holds for the active theme lives in a single option named theme_mods_ plus the stylesheet slug. One bad byte in there and the logo, the colours and the menu locations all revert to theme defaults at once, which reads as “the theme broke” rather than “one row broke”.
• wp_postmeta. _wp_attachment_metadata is the one that hurts. It is a serialized array holding the width, the height, the relative file path and the whole sizes map of generated thumbnails. When it stops parsing, wp_get_attachment_image_src() cannot resolve a size, the thumbnail disappears and the srcset core builds from that sizes array is simply not emitted. If your pictures went missing after a move, work through the five layers that make images vanish before you blame the uploads folder.
• wp_usermeta. The capabilities row, named with your table prefix followed by capabilities, is a serialized array like a:1:{s:13:"administrator";b:1;}. Break it and one user loses every role while the rest of the site behaves normally.

wp search-replace, and the case it cannot cover

If you have shell access on the machine holding the database, use wp search-replace and do not think about any of this again. It works at a different layer to your UPDATE: it reads each row, unserializes the value, walks the resulting array or object recursively, replaces inside string values only, and lets PHP’s own serialize() write the structure back out. The lengths are recalculated by the same code that wrote them in the first place, so they cannot drift.

The flags worth knowing are --dry-run, which reports what it would change and touches nothing, --precise, which forces the PHP path for every row instead of letting MySQL handle the rows with no serialized data in them, --regex for a pattern rather than a literal, and --export, which writes a modified dump to a file instead of updating anything. That last one is the safest shape for a migration: export from the old host with the replacement already applied, then import a file that was never wrong.

The tool above is for the other situation, and it is more common than the tidy one. You have a .sql export and a text editor. The host gives you phpMyAdmin and no shell. The site is already down, so there is nothing to run WP-CLI against. Or somebody before you already ran the naive UPDATE and you are holding the wreckage with no backup. In all of those you have a single broken value in your clipboard and you need to know what it should say. Paste it in, read the tree, take the repaired string back out. It runs entirely in your browser: nothing is uploaded, no request of any kind is made, and the data never leaves the tab.

The cases that make repair fiddly

Recomputing a length sounds trivial and is, when the string is intact. The hard part is deciding where a string ends once its declared length is a lie. The parser cannot trust the number, so the real end has to be found by looking for a closing quote followed by a semicolon that leaves the rest of the document readable, and then choosing the candidate nearest the declared length. That is a heuristic and it is honest about being one. A string that contains a serialized fragment of its own and a wrong length at the same time can fool it. When the declared length fits exactly, no guessing happens at all.

Two failures are reported and never invented back. A truncated string, where the export was cut off mid value, keeps whatever survived and gets a correct length for it: the missing bytes are gone and no tool can know what they were. A container whose closing brace never arrived is flagged the same way. Anything that claims to reconstruct those is guessing at your content, which is worse than telling you the truth.

Double serialization is a real WordPress behaviour, not a bug in your export. maybe_serialize() deliberately serializes a second time when the value it receives already looks serialized, for backward compatibility reasons that date back to a core ticket from 2010. So you find a string whose contents are themselves a complete serialized document. Reading it as its own tree is optional and off by default, detection goes three levels deep, and it only fires when the inner text both looks serialized and parses with nothing left over. Repair the inner document and the outer length has to be recomputed too, which is the case a hand edit almost always gets wrong.

Then there are the NUL bytes. PHP writes a private property as the class name wrapped in two NUL bytes followed by the property name, and a protected property as an asterisk wrapped the same way. Those bytes are counted in the declared length. Plenty of export and transfer paths strip them, so you get a property whose name measures two bytes short of what it claims. Only two shapes are unambiguous, a name starting with an asterisk and a name starting with the object’s own class name, and only when the shortfall is exactly two. Anything else is left alone. The restore is listed as a correction and raised as a warning rather than applied quietly, and you can switch it off.

A handful of smaller rules matter when you are reading the tree. References written as R: and r: point at a position in the value rather than carrying data. A C: payload comes from a class implementing Serializable and its body is an opaque blob: it is measured and passed through unchanged, never interpreted. Integers larger than JavaScript counts exactly are kept as written rather than converted and quietly rounded. Nesting deeper than 64 levels is a warning, deeper than 220 stops the parse instead of risking the stack. There are working limits too, 60000 elements per container, 500 tree rows drawn, 60 findings and 200 diff rows listed, and a scan budget that gives up rather than guessing forever through a document too damaged to read.

One asymmetry in the replace is worth internalising before you use it in anger: renaming a class does not rename that class inside a private property name unless you also tick keys, because the class name in a mangled property is a key, not a value. And if the value you are staring at turns out to be JSON rather than serialized PHP, this is the wrong tool entirely. JSON has no length prefixes, so a plain search and replace survives it, but the escaped forward slashes bite instead. A validator that shows you what the parser actually saw is the right stop for that.

Objects, and why unserialize on untrusted data is a real risk

An object looks like O:8:"stdClass":1:{...}. The number before the class name is its byte length, and the number after it is the property count. When PHP unserializes that, it does not merely build a data structure. It creates an instance of that class if the class is defined, and magic methods run: __wakeup() or __unserialize() during construction, __destruct() later, with every property value set to whatever was written in the string.

That is PHP object injection, and it is a recognised vulnerability class rather than a style complaint. The attacker does not supply code. They supply a shape that makes code already present in your plugins do something on their behalf while it tidies itself up. PHP’s own answer is the second argument, unserialize( $data, [ 'allowed_classes' => false ] ), which turns every object into a harmless placeholder. WordPress’s maybe_unserialize() does not pass it, because option and meta values are assumed to be trusted, and the interesting incidents are always the ones where that assumption turned out to be false.

Which is the reason the tool parses instead of unserializing. It reads the bytes into a node model and prints them: no unserialize, no evaluation, no eval, no new Function, no innerHTML, nothing random. When it meets an object of a class WordPress core does not define, it does not call that an error, because in a database you are repairing after somebody else touched it, an unexpected class in a row is worth a second look rather than an automatic fix.

What to do before the next import

The rule that prevents all of this is short: never let a text substitution touch a column that can hold serialized data unless something recomputes the lengths afterwards. In practice that means wp search-replace when you can reach a shell, ideally with --export so the dump you carry across was correct before it ever hit the new database, and a proper migration plugin when you cannot. A raw UPDATE ... REPLACE() is safe on post_content and post_excerpt, and it is a coin toss on wp_options, wp_postmeta, wp_usermeta and wp_termmeta.

When it has already happened, the diagnosis is faster than it looks, because the symptom tells you the row. Widgets gone means a widget_ option. Logo and colours gone means the theme mods. Every plugin apparently deactivated means active_plugins. Thumbnails gone on one attachment means that attachment’s _wp_attachment_metadata. Pull the single value, repair it, write it back with the UPDATE statement the tool builds for you, and check the next symptom. It is one row at a time, but each row takes about a minute.

What is worth carrying away is the smaller idea underneath. Serialized PHP is not a document format that happens to be brittle. It is a format that trades every safety property for speed of parsing, and it hands the reader a number it has to believe. Once you know the number is a promise rather than a label, the migration bug stops being mysterious and becomes what it actually is, an off by four error that nobody counted.