WordPress Search and Replace the Database URL Without Breaking Serialized Data

Do not run a plain UPDATE ... SET column = REPLACE(column, 'oldurl', 'newurl') across a WordPress database. It will change the text correctly and destroy your settings at the same time, because WordPress stores arrays and objects as PHP-serialized strings, and that format records the byte length of every string it contains. A raw SQL replace rewrites the text but never touches the recorded length, and the value stops being readable.

Use a serialization-aware tool instead. On the command line that is wp search-replace. Back up the database first, run it as a dry run, then run it for real.

What actually breaks

A PHP-serialized string looks like this:

s:18:"http://example.com"

The 18 is the byte length of the text that follows. Count it — http://example.com is exactly 18 characters.

Now imagine a stored theme setting:

a:1:{s:4:"logo";s:18:"http://example.com";}

Run a plain SQL replace of http:// to https:// and you get:

a:1:{s:4:"logo";s:19:"https://example.com";}

Except you do not. You get this:

a:1:{s:4:"logo";s:18:"https://example.com";}

The string is now 19 bytes but still claims to be 18. PHP reads the declared length, walks 18 bytes forward, does not find the closing quote and semicolon where it expects them, and gives up. The whole array fails to unserialize.

Why it fails silently

This is the part that makes the bug so expensive. WordPress does not throw an error and stop. When it reads an option it passes the value through its unserialize helper, PHP returns false for the malformed data, and WordPress hands your code a value that behaves as if the option was never set. The plugin or theme then falls back to its default.

So the symptom is not an error page. The symptom is:

• Widgets disappear from sidebars
• Customizer settings reset to theme defaults
• A page builder’s layout data comes back blank and the page renders empty
• Plugin settings pages look freshly installed
• Menus lose their assigned locations

Nothing in the admin says anything is wrong. The row is still in the database, still full of what looks like correct text. Only the length prefix is off by a byte or two. PHP does emit a notice when unserialization fails, but on a production site with display errors off, nobody sees it. Turning on WP_DEBUG with WP_DEBUG_LOG enabled and WP_DEBUG_DISPLAY set to false will put those notices in a log file where you can read them.

The damage is also not evenly spread. Simple scalar values — siteurl, home, plain post content, a URL sitting in its own column — survive a naive replace perfectly. Only serialized values break. That is why a bad replace often looks like it worked at first glance.

Check wp-config.php before touching anything

If wp-config.php defines WP_HOME or WP_SITEURL, those constants win over whatever is in the database. Changing wp_options will appear to do nothing at all. Look for lines like these before you conclude the database is the problem:

define( 'WP_HOME', 'http://example.com' );
define( 'WP_SITEURL', 'http://example.com' );

Either update them to the new URL or remove them and let the database drive.

Back up first, and mean it

A serialization break is not always easy to reverse. Once the length prefix and the content disagree, the original value is not recoverable from the broken value — you cannot know what the correct length was supposed to be without knowing the original string. A backup is the only real undo.

wp db export backup-before-replace.sql

Or with mysqldump if WP-CLI is not available:

mysqldump -u USER -p DBNAME > backup-before-replace.sql

Keep it off the server if you can. A backup sitting in the web root is both a restore point and a security problem.

The safe command

WP-CLI’s search-replace unserializes each value, walks the decoded structure, replaces inside it, and re-serializes with correct lengths. Dry run first:

wp search-replace 'http://example.com' 'https://example.com' --dry-run --all-tables --skip-columns=guid

Read the report. It tells you how many replacements will happen per table and column. If the table list looks wrong, or a table you do not recognise has thousands of hits, stop and look before running it for real.

Then run it:

wp search-replace 'http://example.com' 'https://example.com' --all-tables --skip-columns=guid --report-changed-only

Notes on the flags, honestly:

• --all-tables covers tables WordPress does not register itself, which is where plugin data often lives. Without it, some plugin tables are skipped entirely.
• --skip-columns=guid matters. The guid is a permanent identifier for feed readers, not a live URL. Rewriting it can make feed subscribers see your entire archive as new posts.
• Search without the trailing slash and without the protocol variants you do not want to touch. Replacing bare example.com will also hit email addresses and any mention in post text.

If you cannot use the command line, serialization-aware migration plugins do the same decoding work through the admin screen. The behaviour you are looking for is explicitly described as handling serialized data; if a tool does not say so, assume it does not.

Which tables and columns are involved

Table	Column	Serialized risk
wp_options	option_value	High — widgets, theme mods, transients, most plugin settings
wp_postmeta	meta_value	High — page builder layouts, custom field arrays
wp_usermeta	meta_value	High — capabilities, screen and dashboard preferences
wp_termmeta	meta_value	Medium — depends on plugins
wp_posts	post_content, guid	Low for content, but shortcode attributes and block markup carry URLs
wp_comments	comment_content, comment_author_url	Low
wp_links	link_url, link_image	Low, and often unused

On multisite, add wp_blogs, wp_site, wp_sitemeta, and the per-site wp_2_options, wp_3_options and so on. Multisite URL changes have extra rules beyond a search and replace — treat that as a separate job.

The cases search-replace does not fully solve

Being honest about the limits:

JSON stored inside the database. Some page builders store layout data as JSON with escaped forward slashes, so your URL appears as https:\/\/example.com rather than https://example.com. A search for the normal form will miss it. You may need a second pass on the escaped string. Test on a copy.

Base64-encoded values. If a plugin encodes settings before storing them, no text-based replace can see the URL at all. Those settings usually need to be re-entered through the plugin’s own screen.

Hardcoded URLs in files. Anything written into functions.php, a child theme template, or a hardcoded asset path is not in the database and will not be touched. Grep the theme directory separately.

Cached and generated output. Object cache, page cache, and CDN copies keep serving the old URLs after a correct replace. Flush them, then check again before assuming the replace failed.

If you already ran the bad query

Restore the backup. That is the answer, and it is worth saying plainly rather than offering a repair procedure that mostly does not work.

If there is no backup, the recoverable path is narrow: values that were never serialized are fine, and the broken serialized ones have to be reconstructed by hand or reset by re-saving the relevant settings screen. Re-saving a widget area, re-selecting a menu location, or re-entering a plugin’s settings writes a fresh, correctly-serialized value over the broken one. Tedious, but it works, and it is better than leaving corrupted rows in place where they will confuse the next person who looks at the site.

To generate the correct statements before running anything, build them with the WordPress search and replace SQL tool — it shows which columns are safe to hit with plain SQL and which ones require serialization-aware handling, so you can see the split before you commit to a command.