I recently developed a WordPress website with various custom post types. Some of these CPTs had a metadata field called attachment, and it would store the URL and filesize of a PDF file attached to the post. All was well until I uploaded the website to a new server, with a different domain name. All of a sudden, none of the thousands of custom posts had an attachment.

My normal procedure when copying a website to a remote server is dumping the WordPress database to file using mysqldump, the doing a search-replace, replacing the local development domain (e.g. http://testsite) with the production domain (e.g. http://www.myclient.com), then upload the database dump to the server and import it there. This usually works fine, barring the occasional problem with Avada options or other, unrelated to the database.

This time, the site looked fine until I found that none of the custom post type instance had an attachment. I debugged the code, and found that WordPress couldn’t find a value for the atachment metadata. The following statement would return null, rather than the attachment meta array.

	
$attachment = get_post_meta($document->ID, 'attachment', true);

I looked through the database, specifically the wp_postmeta table, to see if the metadata records actually made it to the server. Of course they were there, because other metadata (not PDF related) was there also.

post_id	meta_key	meta_value
6758	author	John Smith
6758	language	en
6758	attachment	a:2:{s:3;”url”,s:38:”http://www.myclient.com/2017/06/mydocument.pdf”;s:8;”filesize”;i:764000;}

Meta records like author and language, as shown in the SQL dump in the table, showed up fine on the website. But not the attachment. Looking very closely, I finally understood what was going on. The attachment record stores an array of strings, encoded by WordPress. WordPress uses a JSON-like encoding style, using a to indicate an array, followed by its number of elements (two elements, in this case). For each element, it them encodes its name (using s to encode a string) and its value (using s, again, to encode a string and i to encode an integer). And here it comes:

For string encoding in particular, WordPress also stores the length of the encoded string.

In the table, you can see that the encoded URL is http://www.myclient.com/2017/06/mydocument.pdf. That string has a length of 46 characters. But WordPress has encoded a length of 38 characters. And that’s because I did a search for all http://testsite strings and replaced them with http://www.myclient.com. Clearly, while decoding the records, WordPress doesn’t like this mismatch and acts as if the data wasn’t there.

It turns out, then that to correctly change URLs in a database, you must not only update the URLs, but also the encoded length, which is more than a trivial search-and-replace. I wrote a small PHP script to do the work.

$file = file_get_contents("dump.sql");
 
function replace_code($match) {
  $val = strlen($match[2]);
  return "s:3:\"url\";s:{$val}:\"{$match[2]}";
}
 
$file = preg_replace_callback(
  "/s\:3\:\\\\\"url\\\\\";s\:(\d+)\:\\\\\"([^\\\]+)/", 
  "replace_code", 
  $file);
 
file_put_contents("dump2.sql", $file);

This scripts takes a database dump where you’ve already done the search-and-replace for the URLs. It finds all bits that look like s:3:"url";s:38:"http://whatever and fixes the stored length. It then writes the corrected dump to a file so you can import in into WordPress.