How a SQL Find-Replace Broke 847 Pages on a WooCommerce Site — Fixing Serialized Data After Migration

The Call: "Everything Looks Wrong After the Migration"

A UK agency rang me about a client site they had just migrated from their old host to a new CloudPanel VPS. The WooCommerce store had been running fine for three years. Now, after the move, product pages were missing their gallery images, Elementor layouts had collapsed into raw text blocks, every widget in the sidebar had vanished, and the WooCommerce settings page showed blank fields where shipping zones and tax rates should have been.

The site was technically online — it loaded, the theme appeared, and you could navigate between pages. But the content was mangled. Not a white screen. Not a 500 error. Something subtler and harder to diagnose.

What Actually Happened

The agency's developer had migrated the site by exporting the database with mysqldump, then running sed to swap the old domain for the new one before importing:

mysqldump -u root -p oldstore_db > dump.sql
sed -i 's/oldstore.co.uk/newstore.co.uk/g' dump.sql
mysql -u root -p newstore_db < dump.sql

This is one of the most common migration mistakes I see, and it silently destroys serialized data. WordPress stores complex settings, page builder layouts, and plugin configurations as PHP serialized strings in the database. These strings encode both the data and the exact byte length of each value.

Here is a simplified example of what serialized data looks like in wp_options:

s:22:"http://oldstore.co.uk";

The s:22 means "this is a string that is exactly 22 characters long." After sed changes oldstore.co.uk (14 chars) to newstore.co.uk (14 chars), the length stays the same — and you get lucky. But when the old domain is oldstore.co.uk and the new one is shop.newagency.com (18 chars), the serialized string becomes:

s:22:"http://shop.newagency.com";

PHP tries to read 22 characters from a 25-character string. The deserialization fails. WordPress falls back to an empty value or discards the entire option. Multiply this across every row in wp_options, wp_postmeta, wp_termmeta, and wp_woocommerce_* tables, and you get exactly the symptoms the agency described: settings gone, layouts collapsed, widgets vanished.

Diagnosing the Damage

First, I confirmed the theory by looking at the raw database values. A quick query on wp_postmeta where Elementor stores its page data:

SELECT post_id, meta_key, LEFT(meta_value, 200)
FROM wp_postmeta
WHERE meta_key = '_elementor_data'
AND meta_value LIKE '%shop.newagency.com%'
LIMIT 5;

The _elementor_data field stores a JSON-encoded string, but it is itself stored inside a serialized wrapper in some contexts. More telling was the wp_options table:

SELECT option_name, LEFT(option_value, 300)
FROM wp_options
WHERE option_value LIKE '%s:%'
AND option_value LIKE '%shop.newagency.com%'
LIMIT 10;

I could see mangled serialized strings everywhere — length mismatches in widget settings, WooCommerce shipping zone configurations, and Yoast SEO metadata.

To quantify the damage, I used WP-CLI:

wp db query "SELECT COUNT(*) FROM wp_postmeta WHERE meta_value REGEXP '^[aOs]:[0-9]+:' AND meta_value LIKE '%shop.newagency.com%';" --path=/var/www/newstore

Result: 847 rows of corrupted serialized data in wp_postmeta alone. Another 134 in wp_options.

The Fix: Repairing Serialized Data

There is no "undo" button for a bad sed replacement on serialized data. The length values are wrong throughout the database, and the only reliable fix is to recalculate them.

Step 1: Take a Backup of the Current State

Even though the data was broken, I backed up the current database before attempting any repair:

wp db export /tmp/newstore-pre-repair-$(date +%Y%m%d).sql --path=/var/www/newstore

Step 2: Try the Interconnectit Search-Replace-DB Tool

For databases with widespread serialized corruption, Interconnectit's Search-Replace-DB is the industry standard. It deserializes values, performs the replacement, recalculates string lengths, and re-serializes:

cd /var/www/newstore
git clone https://github.com/interconnectit/Search-Replace-DB.git
cd Search-Replace-DB
php srdb.cli.php \
  -h localhost \
  -u dbuser \
  -p dbpassword \
  -n newstore_db \
  -s 'oldstore.co.uk' \
  -r 'shop.newagency.com' \
  --dry-run

The --dry-run flag is critical. It reports how many replacements it would make per table without touching any data. In this case, it found 0 remaining instances of oldstore.co.uk — because sed had already changed every occurrence. The problem was not that old URLs remained. The problem was that the string lengths in the serialized wrappers were now wrong.

Step 3: Fix the Serialized String Lengths

When sed has already performed the text replacement but broken the serialization, you need a tool that recalculates the byte counts without changing the URLs. I wrote a WP-CLI script to scan every serialized value and reserialize it:

<?php
/**
 * Repair broken serialized data in wp_options and wp_postmeta.
 *
 * Usage: wp eval-file repair-serialized.php --path=/var/www/newstore
 */

global $wpdb;

$tables = [
    ['table' => $wpdb->options, 'id' => 'option_id', 'value' => 'option_value'],
    ['table' => $wpdb->postmeta, 'id' => 'meta_id', 'value' => 'meta_value'],
    ['table' => $wpdb->termmeta, 'id' => 'meta_id', 'value' => 'meta_value'],
    ['table' => $wpdb->usermeta, 'id' => 'umeta_id', 'value' => 'meta_value'],
];

$fixed = 0;
$failed = 0;

foreach ($tables as $t) {
    $rows = $wpdb->get_results(
        "SELECT {$t['id']} AS id, {$t['value']} AS val FROM {$t['table']}
         WHERE {$t['value']} REGEXP '^[aOs]:[0-9]+:'"
    );

    foreach ($rows as $row) {
        $unserialized = @unserialize($row->val);

        if ($unserialized === false && $row->val !== 'b:0;') {
            // Attempt to fix string length mismatches
            $repaired = preg_replace_callback(
                '/s:(\d+):"(.*?)";/s',
                function ($matches) {
                    return 's:' . strlen($matches[2]) . ':"' . $matches[2] . '";';
                },
                $row->val
            );

            $test = @unserialize($repaired);
            if ($test !== false) {
                $wpdb->update(
                    $t['table'],
                    [$t['value'] => $repaired],
                    [$t['id'] => $row->id]
                );
                $fixed++;
            } else {
                $failed++;
            }
        }
    }
}

WP_CLI::success("Repaired {$fixed} rows. {$failed} rows could not be automatically fixed.");

Running it:

wp eval-file repair-serialized.php --path=/var/www/newstore

Success: Repaired 923 rows. 58 rows could not be automatically fixed.

923 rows repaired across all four meta tables. The 58 failures were deeply nested serialized arrays where the regex approach could not reliably recalculate lengths — those needed manual inspection.

Step 4: Handle the Remaining Failures

For the 58 rows that could not be auto-repaired, I exported them individually and inspected the structure:

wp db query "SELECT option_name, option_value FROM wp_options WHERE option_id IN (1042, 1043, 1087);" --path=/var/www/newstore > /tmp/broken-options.txt

Most turned out to be abandoned plugin settings from plugins that were no longer active. I deleted them safely. The handful that mattered — a complex WooCommerce shipping zone configuration and two Elementor global styles entries — I rebuilt manually through the WordPress admin.

Step 5: Flush All Caches

After repairing the database, every cache layer needed clearing:

wp cache flush --path=/var/www/newstore
wp transient delete --all --path=/var/www/newstore
wp elementor flush-css --path=/var/www/newstore
wp rewrite flush --path=/var/www/newstore

The Right Way to Migrate

The entire disaster was avoidable. Here is the process I use for every WordPress migration:

1. Export the database cleanly:

wp db export /tmp/site-export.sql --path=/var/www/oldsite

2. Import on the new server without any text manipulation:

wp db import /tmp/site-export.sql --path=/var/www/newsite

3. Use WP-CLI search-replace, which handles serialization correctly:

wp search-replace 'oldstore.co.uk' 'shop.newagency.com' \
  --all-tables \
  --recurse-objects \
  --precise \
  --dry-run \
  --path=/var/www/newsite

The --dry-run shows you exactly how many replacements will happen per table before anything changes. Once you are satisfied:

wp search-replace 'oldstore.co.uk' 'shop.newagency.com' \
  --all-tables \
  --recurse-objects \
  --precise \
  --path=/var/www/newsite

The key flags:

• --all-tables processes WooCommerce custom tables, not just core WordPress tables
• --recurse-objects deserializes nested objects before replacing
• --precise forces PHP-based replacement instead of SQL, which is slower but handles every edge case

4. Also replace the protocol if changing HTTP to HTTPS:

wp search-replace 'http://shop.newagency.com' 'https://shop.newagency.com' \
  --all-tables \
  --recurse-objects \
  --precise \
  --path=/var/www/newsite

5. Flush everything:

wp cache flush --path=/var/www/newsite
wp rewrite flush --path=/var/www/newsite

The whole process takes under two minutes on a database with 200,000 rows. There is no reason to use sed on a SQL dump.

Why This Matters for Maintenance

I encounter this exact problem roughly once a month across the sites I manage. Hosting providers migrate sites with basic SQL dumps. Freelance developers use phpMyAdmin's find-replace, which also breaks serialized data. Even some migration plugins handle edge cases poorly.

If you are taking over maintenance of a site that was recently migrated, check for serialized data corruption early. The symptoms — broken widgets, missing settings, page builder layouts reverting to defaults — are easy to misdiagnose as plugin conflicts or theme issues. A quick query tells you the truth:

wp db query "SELECT COUNT(*) FROM wp_options WHERE option_value REGEXP '^[aOs]:[0-9]+:' AND (option_value = '' OR LENGTH(option_value) < 5);" --path=/var/www/site

If that returns a suspiciously high number, you are probably looking at serialized data corruption, not a plugin bug.

---

Stop Firefighting. Start Maintaining.

I manage 70+ WordPress sites for UK agencies and businesses. Whether you need ongoing maintenance, emergency support, or a one-off performance fix — I can help.

View Maintenance Plans Get in Touch