Lost Widgets When Migrating WordPress Domains (dev to production server)

This is something I ran into long into working on WordPress sites. Here’s how what happens:

  • I work locally with a MAMP setup
  • I create virtual hosts per project
  • Each virtual host corresponds to a host name I add to my /etc/hosts file
  • Sometimes I mirror this to a staging server/domain for the client to prime content before launch
  • When launching, I take the staging database in a SQL export and import it into the production database and run a query to update the domains

Many times, the last item is a query like this (assuming default database prefix):

UPDATE wp_options
SET option_value = REPLACE(option_value, 'http://dev.mycoolwebsite.com', 'http://www.mycoolwebsite.com');

This typically did the trick. I rarely had issues, until one day, I migrated that had a widgetized sidebar from my local machine, let’s say the hostname was localhost and the production domain was www.mycoolsite.com.

Everything worked as planned, but I lost all my widgets.

¿Por Que, Widgets?

Here’s what I figured: WordPress uses a serialized PHP array to store widget settings in the database. This array also contains the site’s domain, an abbreviated example would be:

a:2:{s:3:"url";s:17:"http://localhost/";s:4:"meta";s:17:"some kind of meta";}

a:2 represents an array with 2 elements; s:3 says this is a string that’s 3 characters long and so forth. When I run my replace SQL, it changes http://localhost/ to http://www.mycoolsite.com/ but never updates the string’s length from s:17 to what it should be, which is s:26.

This simple inconsistency will invalidate that serialized array, returning false when it is unserialized — meaning no widgets or settings.

Any Solution?

Two solutions I use, short of some make-shiv helper script:

  1. Use a development/staging hostname that’s the same length so you can do a global replace (e.g. dev.mycoolsite.com, stg.mycoolsite.com, etc.)
  2. Run a more target query

Target Your Query, Hit the Widgets Page

Now, the following steps have worked for me and seem to be easier for me to be lazy with my organization up front. I recommend option 1 to be safe, but you can try this too:

UPDATE wp_options 
SET option_value = REPLACE(option_value, 'http://localhost', 'http://www.mycoolwebsite.com') 
WHERE option_value LIKE 'http://%';

This will restrict the query only to WordPress option records that <strong>start</strong> with the http:// protocol, thus ignoring serialized arrays. Once this runs, hit your widgets settings page and re-save things, and if you’re as lucky as me, everything is kosher.

Yet Another Option

This may actually be simpler. Go into your dev/staging/etc. site’s options and change the domain. You’ll get logged out. Go make your database dump and use that. You’ll probably need to do the previous steps to fix your dev/staging database though.

Hope this helps a few of you if you’re in a frustrated panic.

17 Comments

  1. Marc says…

    I’ve had the problem of losing just the Text Widgets when exporting my database from mamp to production database. But I don’t know if these options would help stop that from happening. Any thoughts on why this would happen?

  2. andy says…

    Not 100% sure. Are you running a SQL query to change anything within the DB?

  3. Marc says…

    No. I’m importing an sql file directly with the phpmyadmin importer.

  4. zane matthew says…

    I develop locally as well, but I don’t use MAMP, I just plan old apache + WordPress Networking + Domain Mapping (sometimes). I used to do the same, but found it easier to do the following:

    1. export/import content (using the official plugin)
    2. zip up theme, then upload theme
    3. change some site settings
    and Done!

    If I need to I’ll use the search and replace plugin: http://wordpress.org/extend/plugins/search-and-replace/

    I used to run into a similar situation, but I take a different philosophical approach to the dilemma.

    Widgets are “site specific settings”, i.e. not content (database driven) or code that can be pushed/pulled with a repository. Therefore it should be expected that either:

    1. Have documentation stating on how widgets need to be set-up and set them up when porting a site.
    2. Do not have a theme that depends solely on widgets, there an “option”, hence that’s why they are stored in the “options” table.

    I leaned towards this direction after asking Matt Mullenweg when he came to DC if WordPress planned to build widgets settings into the import/export plugin. His response was that, currently they’re not supporting that and its not on the list, but you never know.

  5. zane matthew says…

    Also this:
    WHERE option_value LIKE ‘http://%’;

    Might not always find everything for you. Some widgets store directory paths and not just url’s in the options table like.

    (my local host)
    /opt/local/apache2/wp/

    (my server)
    /var/www/site.com/html/

  6. andy says…

    Agreed. Best practice for Widgets IMO is re-creating them from scratch, if you’re going to do that, then I would totally go official export/import route.

    Many sites I work on aren’t using a dynamic sidebar, so the DB export and REPLACE SQL works like a charm and typically takes me less time (personally) than the other route

  7. Jenny Lynn says…

    Thank you so much! I would have never realized the serialized array was causing my problem had I not read this.

  8. Colin Ligertwood says…

    There is a much easier solution.

    If you’re migrating the site, just change the Site Address and WordPress Address options in the General Settings panel to the new site’s as the last step before you migrate.

    You’ll lose access to the dashboard immediately after doing it, but all of the packed PHP structures will get updated with the new info.

    After that, just do a mysqldump, search and replace the document-root values to the new site’s and fire it up!

  9. andy says…

    @colin awesome tip and a great solution that is a bit more elegant and probably a bit more of an approved WP way of doing it.

    I should be doing that. Another option would be modifying your hosts file and starting with the production URL.

  10. Jeremy Carlson says…

    Andy’s writeup helped me track down this annoying problem. But then Colin’s comment – oh, that makes it so much simpler! Thank you both…

  11. Aram Kocharyan says…

    Also, try this awesome plugin that replaces the serialized arrays for you: http://wordpress.org/extend/plugins/wp-migrate-db/

  12. Lost widgets | Selimcivelek says…

    […] Migrated WordPress to New Domain & Lost My WidgetsMay 23, 2011 … If you’ve lost widgets when migrating your site between domains, then here’s a solution and most-likely reason why. […]

  13. Chad says…

    What about doing a find/replace for the s:17 to s:26 ? Has anyone tried that?

  14. Andy says…

    @Chad I’d go with @colin’s option above. It’s what I’ve been doing more recently, especially with complex sites.

  15. Sidd says…

    @Aram Kocharyan – easiest way out mate. Thanks. The standard answer is http://wordpress.org/extend/plugins/wp-migrate-db/ 😀

  16. Why WordPress Widgets Vanish When Migrating to New URL | Theme Lab says…

    […] luckily stumbled across this post by Andy Stratton: Lost Widgets When Migrating WordPress Domains (dev to production server). In the post, he explains how WordPress stores widget data in a serialized […]

  17. Why WordPress Widgets Vanish When Migrating to New URL | wpyeti.com says…

    […] luckily stumbled across this post by Andy Stratton: Lost Widgets When Migrating WordPress Domains (dev to production server). In the post, he explains how WordPress stores widget data in a serialized […]

RSS feed for comments on this post. TrackBack URL

Leave a Comment

May 23, 2011

Filed in Wordpress

There are 17 comments »


« Back to the Blog