Recovering a malformed wp_options.active_plugins: the SQL REPLACE() trap and how to rebuild

You have a WordPress site that’s returning HTTP 200, the homepage renders, but something’s quietly off. WooCommerce features aren’t loading. LiteSpeed Cache settings page is empty. The Mailpoet sender isn’t sending. None of these would normally fail at the same time — until you realize none of those plugins are actually active anymore. The plugin directories are still there. Wordfence’s “active plugins” count is still high. But every single one is silently inert.

The cause is almost always a malformed serialized string in wp_options.active_plugins. PHP’s unserialize() chokes on it, returns false, WordPress treats false the same as “no plugins active,” and your site silently runs theme-only mode without telling anyone.

How a serialized array gets malformed

WordPress stores active_plugins as a PHP-serialized array. The format is rigid:

a:3:{i:0;s:19:"akismet/akismet.php";i:1;s:35:"litespeed-cache/litespeed-cache.php";i:2;s:19:"jetpack/jetpack.php";}

The a:3: at the start is the array length header. Each s:N: is the byte length of the next string. If any number doesn’t match the actual content — too few elements, a wrong string length, an extra semicolon — unserialize() returns false.

The most common way this gets corrupted: someone tries to “deactivate” a plugin from the database with a SQL REPLACE():

-- DON'T do this
UPDATE wp_options
SET option_value = REPLACE(option_value, 'wp-file-manager/file_folder_manager.php";', '')
WHERE option_name = 'active_plugins';

The intent is reasonable — strip a bad plugin from the active list. The problem is that this removes the string but doesn’t fix the array length header (a:5: still says “5 elements” when there are now 4) and doesn’t renumber the integer keys (i:0;, i:1;, then suddenly i:3;). PHP’s strict serializer rejects both. The site keeps running because themes don’t depend on this option, but every plugin disappears.

How to detect it

Three quick checks:

# 1. Look at the raw stored value
SELECT LEFT(option_value, 200), LENGTH(option_value)
FROM wp_options WHERE option_name = 'active_plugins';

# 2. Try to unserialize from the command line — fastest test
php -r '
  $v = file_get_contents("php://stdin");
  $u = @unserialize($v);
  var_dump($u);
'
# Paste the option_value as input. If output is bool(false), it is corrupted.

# 3. From WordPress itself, check via wp-cli
wp option get active_plugins --format=json --skip-plugins --skip-themes
# If this returns "false" or empty, your option is malformed.

The “no plugins are loading” symptom is the lived signal: anything that should be running on every request (a security plugin, a cache plugin, an analytics plugin) is silently absent.

How to rebuild it cleanly

Don’t try to hand-edit the serialized string. Even one off-by-one byte will leave it broken in a different way. Rebuild from the file system instead — the truth is “what plugins exist on disk and have a Plugin Name header.” A short Python script:

import os, re, subprocess

PLUGIN_DIR = "/path/to/site/wp-content/plugins"
DB_NAME    = "your_db"
DB_USER    = "wp_yoursite"
DB_PASS    = "..."
TABLE      = "wp_options"  # adjust prefix if needed
EXCLUDE    = {
    # Plugins you don't want auto-activated even if they're on disk
    "wp-file-manager", "pexlechris-adminer", "wp-console",
    "header-and-footer-scripts", "wordpress-mu-domain-mapping",
}

def find_main_file(plugin_dir):
    for fn in sorted(os.listdir(plugin_dir)):
        if fn.endswith(".php"):
            try:
                head = open(os.path.join(plugin_dir, fn), errors="replace").read(8000)
                if re.search(r"Plugin Name:", head, re.I):
                    return fn
            except: pass
    return None

actives = []
for entry in sorted(os.listdir(PLUGIN_DIR)):
    if entry in EXCLUDE: continue
    full = os.path.join(PLUGIN_DIR, entry)
    if not os.path.isdir(full): continue
    main = find_main_file(full)
    if main:
        actives.append(f"{entry}/{main}")

# Serialize properly — count = len(actives), each string with byte-accurate length
ser = f"a:{len(actives)}:" + "{"
for i, s in enumerate(actives):
    ser += f'i:{i};s:{len(s.encode())}:"{s}";'
ser += "}"

# Write to DB using hex-string syntax to avoid quoting hell
hex_value = ser.encode().hex()
subprocess.run([
    "mysql", f"-u{DB_USER}", f"-p{DB_PASS}", "-h127.0.0.1", DB_NAME,
    "-e", f"UPDATE `{TABLE}` SET option_value = UNHEX('{hex_value}') "
          f"WHERE option_name = 'active_plugins';"
])
print(f"Wrote {len(actives)} plugins to active_plugins.")

Two key details:

• len(s.encode()) for the string length, not len(s). PHP’s s:N: counts bytes, not characters. If your plugin file path contains anything multi-byte, character count will be wrong and you’ll create a malformed string in a new way.
• UNHEX('...') in the UPDATE instead of putting the serialized string directly into the SQL. The serialized form contains both single and double quotes; trying to embed it in a quoted SQL literal is asking for escaping bugs.

After rebuilding

Hit one URL on the site (any URL, even the homepage). WordPress reads active_plugins, loads each plugin file, and re-runs each plugin’s activation hook. Plugins that need to register cron events do so. Plugins that need to run database migrations on activation do so. Within seconds the site is back to its actual state.

Then verify nothing’s missing in WP Admin → Plugins. If a plugin you expected to be active isn’t, it’s because either (a) it was already deactivated before the corruption, or (b) it’s in your EXCLUDE set.

The lesson

Never edit a serialized PHP option with a string-level operation. Every “I’ll just REPLACE() this one slug” turns into a multi-hour debug session 30% of the time. The right tool for modifying a serialized array is always: read it, unserialize, modify the array in-memory, re-serialize, write it back. That round-trip is two extra lines of code and saves a whole class of “the site looks fine but nothing works” outages.

Voice of experience.