Clone a site on a WordPress multisite and, if the plugin is any good, you get a copy. If the plugin is mine and the source is the main site, you get a SQL error and a half-built subsite.
I’d have bet money it was one bug. It was three, stacked, and I only met the second and third because I fixed the first.
The error
The plugin copies every table from the source site to the new one, renaming the prefix as it goes. Main site tables start with wp_. The new subsite’s tables start with wp_101_. After the copy, a clean-up pass fixes anything that still mentions the old prefix, and that’s where it fell over:
Duplicate entry 'wp_101_user_roles' for key 'option_name' for query UPDATE wp_101_options SET option_name = REPLACE(option_name, 'wp_', 'wp_101_') WHERE option_name LIKE '%wp\_%'
Cloning from a subsite worked fine. Cloning from the main site didn’t. That asymmetry is the clue, and I walked straight past it for a bit.
Why the main site is special
Options and meta tables store some keys with the prefix baked into the name. wp_user_roles is the famous one. When WordPress creates the new subsite it writes its own wp_101_user_roles straight away. Then my copy brings across wp_user_roles from the source, and the clean-up renames it to… wp_101_user_roles. Which already exists. The unique index on option_name says no.
The query had a second flaw hiding behind the first. REPLACE() swaps every occurrence of the string, not just the one at the start. Any key with wp_ somewhere in the middle gets rewritten too. And because the new prefix begins with the old one, running it twice turns wp_101_x into wp_101_101_x. It was never safe to re-run, I just hadn’t re-run it.
An anchored rename
For identifier columns, the names of options and meta keys, I wanted three properties: only touch rows that start with the old prefix, skip rows that already carry the new one, and clear out collisions before renaming.
DELETE a FROM wp_101_options a
JOIN wp_101_options b
ON a.option_name = CONCAT('wp_101_', SUBSTRING(b.option_name, 4))
WHERE b.option_name LIKE 'wp\_%'
AND b.option_name NOT LIKE 'wp\_101\_%';
UPDATE wp_101_options
SET option_name = CONCAT('wp_101_', SUBSTRING(option_name, 4))
WHERE option_name LIKE 'wp\_%'
AND option_name NOT LIKE 'wp\_101\_%';
The delete is the part I’m slightly uneasy about. It throws away destination rows that the source is about to overwrite, so the source wins. For the stuff WordPress seeds on site creation that’s exactly right, since I want the clone to match the original. But it is a delete, and I’d rather say so than bury it.
Values, post content and everything else stay on the old broad REPLACE(), because serialized data that embeds the prefix still needs updating and a global find-and-replace is the right tool there.
The duplicate-key error vanished. I was pleased with myself for about four minutes.
The next error was one I’d never seen
Because the rename no longer died, the clean-up pass carried on into territory it had never reached on this path: rows where the value is a serialized PHP object. Those go through a row-by-row function rather than SQL. And that function had a bug that, as far as I can tell, had been sitting there since long before I inherited the code.
// $field is the column name, passed in from the caller
$array_object = (array) $row[$field];
$array_object = replace_recursive($array_object, $from, $to);
foreach ($array_object as $key => $field) {
$row[$field]->$key = $field;
}
Spot it? The loop uses $field as its value variable. That overwrites the column name from the outer scope. First iteration, $field is now some property’s value. If that value is an array, PHP 8 stops with “Cannot access offset of type array on array”. If it’s a string it limps along using a string as a column name, which is wrong in a quieter way.
The fix is a rename. $value. Four characters.
foreach ($array_object as $key => $value) {
$row[$field]->$key = $value;
}
There’s a French comment right above that block, left by whoever first wrote it, saying roughly “strange behaviour with a certain sitemap plugin”. Strange indeed. It had presumably worked by accident on older PHP, or never run at all.
And then the third one
With the variable fixed, the loop ran properly for the first time in its life and promptly hit another wall.
Cannot access property starting with "\0"
Cast an object to an array and its private and protected properties come out with mangled keys: a NUL byte, the class name, another NUL byte, then the property name. Protected ones are "\0*\0name". Write one of those keys back through -> and PHP 8 refuses outright. Under older versions it was a warning and the property quietly didn’t get set.
Plenty of serialized plugin options contain objects with non-public properties. So the whole data-update phase aborted on any site that had one.
My fix is pragmatic rather than proud. Skip any key that starts with a NUL byte, because from outside the class you can’t set those anyway. And leave __PHP_Incomplete_Class objects alone entirely, since the real class isn’t loaded when the data is unserialised and nothing you write will land correctly.
if ($row[$field] instanceof __PHP_Incomplete_Class) {
// class wasn't loaded during unserialize; leave the value untouched
} else {
foreach ($array_object as $key => $value) {
if (!is_string($key) || $key === '' || $key[0] === "\0") {
continue;
}
$row[$field]->$key = $value;
}
}
It does mean a private property holding an old URL won’t get rewritten. Public ones do. I haven’t found a real case where it matters yet, which is exactly how these things start.
What I took from it
The first fix was correct. It also turned a crash into a green light, and the green light let the code walk into rooms nobody had been in for years.
When you fix an error that stopped a code path early, you’ve just promoted everything after it from “unreachable” to “untested”. Expect more. I should have gone looking for the next one deliberately: cloned from the main site again, with a serialized object in the options table on purpose, before shipping the first patch.
I shipped three patches in about twenty minutes and bumped the version twice along the way. For the record, that’s not a method.