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.
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.
Meta records like
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.
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.