Why is my database import losing text widget data?

3 Views

I’ve created a site in WordPress on our development machine. In the theme we’re using there are numerous widget zones to display text in (sidebar and front page). I’ve used simple Text widgets in all of these zones to put our display information.

When I migrated the site to production, I used the WP-DB-Backup plugin to take a snapshot of the database. I then edited the resulting .sql file to update all of the file paths and URL references to point to our production site.

Read More

After creating the database, website, and copying all of the files over to the production site, I run the .sql file from the mysql command prompt to import the data into the new database.

However, when I go to the production site, some of the text shows up and some of it doesn’t. When I look into the widgets section of the site, the text widgets are missing from some of the widget zones. The text widgets aren’t even visible in the “Inactive Widget” zone, they simply aren’t there.

I’ve even tried to repeat the process using the BackWPup plugin, noticing that the SQL syntax is different when it dumps the database out.

Why am I losing text widget data during the import?

Post Views: 3

Related posts

Leave a Reply

5 comments

  1. This is where your problem is:

    I then edited the resulting .sql file
    to update all of the file paths and
    URL references to point to our
    production site.

    You can’t do that. WordPress stores many options as “serialized data”, which contains both the string content of things and their length. So when you modify the URL and the length changes, then the serialized data is no longer correct, and PHP rejects it.

    The longer term problem is that, basically, you’re doing it wrong. If you are setting up a development site that will have its data migrated, then it should have the exact same URL as your production site to begin with. You can manually edit your HOSTS file to give that production domain (like example.com) a different IP address (like 127.0.0.1) and thus the “production” URL will become the development site, for you only. Then you can create your data and links and everything else using that production URL, and when you migrate the data, nothing about it has to be altered.

    In the short term, however, don’t use a simple text search/replace on the SQL file. As you have discovered, this breaks things.

    And while I hesitate to suggest it, there is a way to alter the WordPress core code to handle these broken serializations. You have to modify the wp-includes/functions.php file, and change the maybe_unserialize() function to this:

    function maybe_unserialize( $original ) {
    if ( is_serialized( $original ) ) {
        $fixed = preg_replace_callback(
            '!(?<=^|;)s:(d+)(?=:"(.*?)";(?:}|a:|s:|b:|i:|o:|N;))!s',
            'serialize_fix_callback',
            $original );
        return @unserialize( $fixed );
    }
    return $original;
}
function serialize_fix_callback($match) { return 's:' . strlen($match[2]); }

    This is NOT a viable long term solution. It should only be used to get you up and working right now. In the long run, you need to fix your development process so that you don’t have to do this sort of URL munging to begin with.

  3. Otto’s answer is spot-on. I also discovered this the hard way.

    However, I managed to work around this using a cool script at http://spectacu.la/search-and-replace-for-wordpress-databases/

    To migrate your wordpress and to a new url / domain name, do the following:

    1. Take a DB dump (e.g. using phpmyadmin) of the existing wordpress
    2. Restore the dump as-is, (no need for modifications) to your new location
    3. Unzip the script from spectacu.la into your wordpress home folder (it’s not a plugin…)
    4. Run the script on your new site by pointing your browser to it, e.g. http://new-website.url/searchreplacedb.php
    5. Don’t forget to delete the script from your new wordpress home

  4. The OP was overzealous when doing a search-and-replace on the database export file, and ended up changing occurrences of “wp_” within some of the serialized data. The solution is to be more parsimonious in the search-and-replace by including the backtick within the regular expression, and then manually updating the remaining keys within the database after import.

    If you are migrating and changing the prefix, and like a more manual approach, do the following (this only addresses the OPs concerns and does not deal with updating the site URL)

    1. Backup and move your database export
      SQL file to the new environment (my example assumes a filename of backup_YYYY-MM-DD.sql)
    2. Do a mass search-and-replace on the
      SQL file to change the table names
      to use your new prefix (PRIOR to
      importing your SQL file!). One way
      to do this would be to use a Perl
      one-liner like: perl -p -i.bak -e
      “s/`wp_/`myprefix_/g”
      backup_YYYY-MM-DD.sql
    3. Import your SQL data into the database
    4. Update any keys within _options that
      contain the prefix hard-coded:
      update myprefix_options set
      option_name =
      concat(‘myprefix_’,substr(option_name,4))
      where option_name like ‘wp_%’
    5. Update any keys within _user_meta
      that contain the hard-coded prefix:
      update myprefix_usermeta set
      meta_key =
      concat(‘myprefix_’,substr(meta_key,4))
      where meta_key like ‘wp_%’