Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

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?

share|improve this question
    
I've been doing some digging along the way, and the only thing I can think of is that the widget information is being stored in the wp_options table, which looks to encode some of its data in a weird way. I haven't been able to try this with a different theme yet to see if it is theme related. –  Dillie-O Feb 10 '11 at 15:39
add comment

8 Answers

up vote 28 down vote accepted

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.

share|improve this answer
    
@Otto excellent answer. Quick question, would modifying a non serialized blob/text table like wp_posts outside of MySql effect any of the serialized data in wp_post_meta or wp_options? I had the same problem with the text widget but I didn't touch wp_options I only modified wp_posts. –  Chris_O Feb 10 '11 at 19:40
    
Wow, I never realized that is what was happening with the data, but it makes complete sense! Many thanks! –  Dillie-O Feb 10 '11 at 20:52
    
This is NOT a viable long term solution. - not even short time. –  hakre Feb 11 '11 at 1:18
    
Interesting; how does one edit the hosts file to point example.com at localhost 127.0.0.1? –  songdogtech Feb 11 '11 at 5:28
2  
Another workaround that some people use is to make their development system have a domain name of "example.dev" instead of "example.com". That way, lengths don't change for strings when they move them to production. I prefer the HOSTS file method. –  Otto Feb 11 '11 at 21:28
show 3 more comments

I found the cause of the problem. If you have multiple widgets, they are pasted together in the database in one field, that's why they are not displayed.

share|improve this answer
add comment

To deal with this issue I always use WordPress Serialized Search & Replace tool provided here. It works perfectly fine. I have been using this for sites. This really take care of the issues with migrating development database to production.

http://interconnectit.com/products/search-and-replace-for-wordpress-databases/

share|improve this answer
    
Yes been using this script for years and highly recommend it –  davemac Jun 4 '13 at 22:45
add comment

We discovered this issue with regards to a plugin that used serialized data. So we wrote two posts about what we learned and how to migrate your database's serialized data on WPGarage:

Data portability and data serialization in WordPress

This post discusses why serialized data is used, and recommends some scripts and tools to use for exporting serialized data.

Data Serialization, WordPress, and my new best friend

This post is about a few scripts we tried and what worked best for us.

share|improve this answer
add comment

It seems there's now a plugin available to handle this pesky problem. http://wordpress.org/extend/plugins/wp-migrate-db/ will fix things nicely. This was the only thing I could find out there that worked for me.

share|improve this answer
add comment

I used WP Migrate plugin, witch replaces http and folder patches. I got a single problem when importing, but resolved putting the following lines at the top of the generated sql:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

I also tried with the Search And Replace tool (v2.1) replied by @Yoav, but it still breaks my serialized data.

share|improve this answer
    
Hi Ricardo, Welcome to WordPress Answers! The area you posted in is reserved for answers to the original question. Even though your question is related you should post it as a separate question. You'll get a much better chance of having it answered that way. –  Chris_O Jul 11 '12 at 10:02
add comment

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
share|improve this answer
1  
I know this is kind of old, but where do I supposed to specify the new database name if I restoring the dump as it is? shouldn't I at least put the new database name in the second step? Thank you for this info –  andresmijares25 Jan 5 '12 at 19:22
    
I'm not sure I fully understand your question. Restoring the database can be done with tools like phpmyadmin and you can give it a new name, or use the old name. The script I mentioned simply changes text inside the database after it is already restored. –  Yoav Aner Jan 8 '12 at 16:45
    
Hi Yoav, thanks for the answer, I mean, when I export a DB I normally change the database name to the new one and changes the domain links. Said this, on ur step number two you say restore the dump as-is w/o modifications, I just wanted to know if that literally, or I have to change the database name at least. I know it can a be a dummy question, I'm just kind of lost, thanks again for ur answer –  andresmijares25 Jan 8 '12 at 18:19
    
I don't know how you dump your database, but if you use phpmyadmin 'export' tool, then it doesn't matter which database name it is. You can use the export and import it back to any other database. Generally, regarding bullet-point 2, I think that's ok to change the database name. –  Yoav Aner Jan 9 '12 at 15:09
add comment

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_%'
share|improve this answer
add comment

protected by Community Feb 9 at 7:52

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.