Search and Replace URLs in a WordPress database: serialized data problems

Sometimes when doing a “search and replace” of URLs in a WordPress .sql database, we think that everything will be fine and that we can use the new database in a new site (for example, a development site, or, on the contrary, a production site).

However the reality is trickier than that. Some URLs on the database (oftentimes plugins and theme’s settings) are stored as “serialized data”, which means that if your new URL “number of characters” differs from the original URL number of characters, some settings that depend on the exact numbers of characters to be deployed will get out of synch.

The result of such a problem: theme settings, widgets, and plugin settings unavailable (apparently empty).

There are several plugins that deal with this, to do perfectly neat site migrations. I do recommend this one: https://github.com/wp-sync-db/wp-sync-db/

However sometimes is too late and the original install is gone, and we have at our disposal just an old .sql file that we have the mission to “revive” in another domain.

This script (https://interconnectit.com/products/search-and-replace-for-wordpress-databases/) can help to update an sql database with proper URLs everywhere, without encountering serialized data issues, because it will update the “characters count” that could throw your URLs out of sync whenever serialized data occurs.

The steps would be:

  1. if you already have imported a messed up database (widgets not working, theme options not there, etc), just drop that database using PhpMyAdmin. That is, remove everything on it. Then export and have at hand an un-edited dump of the old database.
  2. Now you have to import the (un-edited) old database into the newly created one. You can do this via an import, or copying over the db from PhpMyAdmin. Notice that so far, we haven’t done any search and replace yet; we just have an old database content and structure into a new database with its own user and password. Your site will be probably unaccessible at this point.
  3. Make sure you have your WordPress files freshly uploaded to the proper folder on the server, and edit your wp-config.php to make it connect with the new database.
  4. Upload the script into a “secret” folder – just for security reasons – at the same level than wp-admin, wp-content, and wp-includes. Do not forget to remove it all once the search and replace have taken place, because you risk to offer your DB details open to the whole internet.
  5. Now point your browser to the secret folder, and use the script’s fine interface. It is very self-explanatory. Once used, we proceed to completely remove it from the server.

This should have your database properly updated, without any serialized data issues around: the new URL will be set everywhere, and serialized data characters counts will be accordingly updated.

Widgets will be passed over, and theme settings as well – two of the typical places that use serialized data in WordPress.

Done and tested solution!

0 Give it a like!

Leave a Reply

Your email address will not be published. Required fields are marked *