When you move a WordPress blog from one folder to another, or from one site to another, you normally use the export/import functionality.
This is fine for normal blogs, but say you’ve developed a new website and set it up on your local machine – the URL for the site may be something like http://localhost/devsite and the live URL will be something like https://davidcoveney.com – you won’t want to set up all the theme options, site options, plugin options and so on all over again.
Instead, a theoretically simple approach is to do a database dump, a search and replace for all references to server paths and URLs, and then reimport that data in the new location.
Should work, but it often falls apart.
What happens is that in WordPress, its themes and its plugins, a lot of data is stored using a method known as serialization. Now, in my opinion this breaks all known good practice around data – it’s language specific, it’s not relational even though it often could be, and it’s hard to edit by hand.
One particular problem is that if you change the length of the data in a serialised string you have to change the length declared in the generated string.
That’s very painful when you have hundreds of the fields.
So, because I’d found this painful I decided to knock together a quick application to at least reduce the amount of editing I had to do. You just do your search and replace, forget about the serialized string lengths, upload your data to the new database, and run this script.
Warning: I haven’t got it to work for widgets and cForms II yet, but the latter has some export functionality anyway, which takes that particular pain away if you plan ahead. In the meantime, feel free to play with the attached file. You use it at your own risk, of course.
To use it, download the file linked in this post, extract it, open the file, edit the connection settings, tell it the table you want to scan through, the column, and the unique key field. If you somehow manage to have more than one unique key to deal with (you shouldn’t, but then it surprises me what people manage to code up), then you’ll have to modify the code accordingly. Once done, make sure you have a backup of that table, and execute the php – either at the command line or through the browser. License is WTFPL, and if you’d like to improve the code, please do and I’ll host the new version.
Serialization-fixer.zip download
BIG WARNING: I take no responsibility for what this code does to your data. Use it at your own risk. Test it. Be careful. OK? Here in the North we might describe the code as being as “Rough as a badger’s arse.” Never felt a badger’s arse, but I’ll take their word for it.
Comments
Posted: 7 April, 2009 at 8:56 PM
Sean Gravener says:
You’re a life saver, Dave. I used this script to fix serialization issues in Expression Engine… many thanks.
Posted: 8 April, 2009 at 9:39 PM
Dave says:
Thanks Sean – glad to hear it was useful for you. When you put these things out you never know whether it’ll help people or not – gives a nice warm fuzzy feeling to hear that it does :-)
Posted: 8 April, 2009 at 10:40 PM
Sean Gravener says:
You should label it not only to fix WordPress, but as a general fix for corrupted serialized database records – being able to define the db, table, index and column makes it flexible.
Perhaps the next step would be allowing a user to define an array of table columns instead of one at a time… and perhaps echoing a status at the end :)
Maybe even a clever name…
In any case, it’s a big help as is.
Posted: 9 April, 2009 at 7:49 AM
Dave says:
Aye – good point. I wrote it to solve a very specific problem, using snippets I’d found elsewhere but wrapped up in a single script.
I’m planning to write a script that lets you do a search and replace on a table or even entire database, deserializing and reserializing as necessary. Ultimately, there needs to be a simple way for people to easily and quickly change values in serialized data. Alternatively, PHP developers could just learn some good database practices, but I’m not holding my breath ;-)
Posted: 9 April, 2009 at 2:09 PM
Sean Gravener says:
“Ultimately, there needs to be a simple way for people to easily and quickly change values in serialized data”
Agreed!!
Posted: 10 April, 2009 at 2:57 PM
Burton Kent says:
Interesting! I just wrote a script to copy a wordpress install to a new domain, and am running into the exact same problem. The script works except for widgets.
Any idea exactly what part of widgets is being broken? Maybe I can figure it out for both of us.
Thanks!
Posted: 10 April, 2009 at 4:13 PM
Dave says:
Burton – I think the problem is down to nested arrays – it’s just too hard a problem for my simple regex fix to solve.
What’s needed instead is a script to read a database, pick up what’s serialized php, unserialize it, carry out the search and replace, and then re-serialize the data and put it back into the database. But that’s a considerably bigger job.
My script is, essentially, a sticking plaster to fix most of the problems. You could do the widgets by hand – at least then there’s not too much to do. Everything else seems to be OK, though I’ve noticed some plugins like cFormsII do this too, so that’s another manual job.
Posted: 5 September, 2009 at 1:07 PM
Dave says:
Worth noting that a script to fix serialized PHP is now in place: https://davidcoveney.com/mysql-database-search-replace-with-serialized-php
Posted: 22 August, 2010 at 12:46 AM
Scott Kingsley Clark says:
WOW! Thank you so much for this! I can’t believe it worked so well, I’m not even using it for WP specifically, but a plugin that stores some values in a serialized array and I had edited it in the db and adjusted the count of the string, but saving it completely busted open the array from being unserialized. Running it through this filter fixed it right up! Thanks again for your contribution Dave!
Posted: 11 September, 2010 at 1:14 AM
Devin Walker says:
We need cForms II and Widgets to be moved also! This is one of the most painful parts of any WordPress migration… any solutions?
Posted: 11 September, 2010 at 2:42 AM
Dave says:
It’s the whole point of the plugin, to allow widgets to move. You should get the latest version from spectacu.la rather than here though.
However, cForms II is ropey as hell, you may struggle with that one. There’s a php file to change but I can’t remember it I’m afraid.
Posted: 3 October, 2010 at 7:51 PM
kucrut says:
Thanks, you just saved me :)
Posted: 19 October, 2010 at 5:04 AM
Stuart Travers says:
I added some customisation that let me put multiple tables/columns into an array and processed them in a loop – as a result this just saved me a stack of time and restored a broken wordpress installation to full working order in seconds! Thanks Dave.
Posted: 24 October, 2010 at 10:00 AM
Dave says:
Hi Stuart – over at http:///interconnectit.com we’ll soon be releasing a new version of our search and replace plugin that lets you choose tables, runs faster, and is full of lovely.
Posted: 19 March, 2011 at 1:54 PM
manuel says:
You are the man!!! Great!! Thanks a lot!
Posted: 24 March, 2011 at 6:52 PM
Brice Burgess says:
Thank you my man!
Posted: 13 October, 2011 at 9:01 PM
RC Thompson says:
Hey thanks Dave.
Appreciate the help!
Posted: 15 December, 2011 at 4:34 PM
Pau Iglesias says:
Hi, I released a similar PHP script but working with database dumps and need to make global replacements, this script repair serialized length values:
https://github.com/Blogestudio/Fix-Serialization
Regards
Pau
Posted: 15 December, 2011 at 4:39 PM
David Coveney says:
Hi Pau – thanks for that looks useful.
If you’re doing migrations with WP, another option is to use the Search/Replace script we wrote at work for databases, which built on my work above: http://interconnectit.com/124/search-and-replace-for-wordpress-databases/
My script here is really just for remedial fixes where it’s all gone wrong and I can’t think what to do next!
Posted: 15 December, 2011 at 4:56 PM
Pau Iglesias says:
Thanks David, good script to make changes directly in running databases, I will try it :-)
Posted: 26 April, 2012 at 2:53 AM
Jonnyandandrea says:
HI there, just wondering if there are any updates to this posts? I see the last comments are a while ago?!
Posted: 6 July, 2012 at 1:09 AM
Hannah West says:
Hi, I moved a WordPress blog and after several failed attempts I managed to get everything working at the new url (doing this for a nonprofit that just got their domain name after hosting in a subdirectory of my site for a while). Except of course the images. I am so apprehensive about doing anything more, but need to get this done and behind me and the responses you’ve gotten in the comments look like it’s safe for me to try (I’ve worked with WP a lot, but not very sophisticated when it comes to database management and PHP). Before I go for it, I was just wondering where the best place is to download your serialization fixer? Here, or at one of the other places mentioned in the comments? Thanks so much!
Posted: 6 July, 2012 at 9:11 AM
davecoveney says:
Hi Hannah – you should probably use the WP Search and Replace tool over at http://interconnectit.com/124/search-and-replace-for-wordpress-databases/ which is a user friendly (but still high-risk) tool.
The important thing is to make sure you backup your system first!
Posted: 7 July, 2012 at 10:41 PM
Hannah West says:
Thanks Dave. I did use Search and Replace. It did a great job with everything else, but my images are still not showing up and I can’t upload new ones for whatever reason. Do you think your tool will help me restore the images, or is there another strategy I should use instead?
Posted: 8 July, 2012 at 2:50 AM
Hannah West says:
I’ll try again and let you know how it goes…
Posted: 10 March, 2013 at 12:55 AM
mike says:
Im having an problem that happens to me always.
On every project i start i work locally and once im done i export my database and search replace all localhost url’s to the new url.
then in cpanel i create a new database user name and password and modify wp-config.
the website works fine all pages working just one thing wont work and i cant find a solution:
all my Theme Options wont show
my theme options settings and s widgets wont show up and i have to redo this process from the beginning so each time i will have to redo all the settings.
i have read a lot about it:
http://codex.wordpress.org/Mov…
and even used this tool you posted:
http://interconnectit.com/prod…
but still don’t know what am i doing wrong.
same thing happens when i try to install a site on local host from a backup.
is there a tutorial on how to do this the right way or can someone please please help.
Thanks in advance
Posted: 20 March, 2014 at 3:36 PM
David Coveney says:
It could well be an encoding issue – the very latest version 3 of search/replace has some major changes in how it handles encodings. Give it a test. Also if you have sample data it could be very very useful to us in testing and finding the basic problems.
Also, do take a look at Shannon’s fix above – that could well help you out.
Posted: 23 May, 2013 at 10:32 AM
Shannon Black says:
major issue with this i finally sorted :)
$__ret =preg_replace(‘!s:(d+):”(.*?)”;!e’, “‘s:’.strlen(‘$2′).’:”$2″;'”, $sObject );
should be
$__ret =preg_replace(‘!s:(d+):”(.*?)”;!e’, “‘s:’.strlen(stripslashes(‘$2′)).’:”$2″;'”, $sObject );
serialized php strings don’t count special escaped characters as 2 letters but rather one.
example: s:1:”n”
Posted: 3 September, 2013 at 7:00 PM
Xis says:
I was working for hours repairing my database!!! THANK YOU -> this fix worked for the repair script!!!
Posted: 20 March, 2014 at 3:35 PM
David Coveney says:
Hi Shannon – I’ll see about incorporating your fix – thanks,
Posted: 7 September, 2013 at 10:54 AM
Jake says:
Her’s one that works with SQL dump: http://pastebin.ca/2444926
Posted: 16 March, 2014 at 2:40 PM
Jeff Hunsaker says:
Awesome utility. Thanks. One thing for folks to also be aware of (that tripped me up) is to check and update the number of elements in the serialized data. That the value outside the brackets: a:125{} Mine was incorrect and throwing off the engine. I simply counted the “s:” occurrences in the data, updated the “a:” value, and saved.
Posted: 11 June, 2014 at 6:28 PM
RedhopIT says:
GoLive plugin could handle the migration (all steps) and update the serialized data properly:
http://codecanyon.net/item/wp-golive/7793291
Posted: 18 September, 2014 at 3:42 PM
r33s says:
if(self::wp_is_serialized($row[$i])) { // replaceStringUrl($this->oldUrl, $this->newUrl, $value);
}, $array);
// serialize your data
$row[$i] = utf8_decode(serialize($newArray));
}
// now you can update your database field
Posted: 3 July, 2015 at 1:39 PM
Marco Torri says:
Hello, I modified a little your code. I removed the “e” modifier (now deprecated) and added “m” and “s” modifiers (to work with multi-line serialized data). Here’s the new code I used:
$__ret =preg_replace_callback(‘!s:(d+):”(.*?)”;!ms’, function($matches){return ‘s:’.strlen($matches[2]).’:”‘.$matches[2].'”;’;}, $sObject );
Posted: 1 December, 2015 at 7:16 AM
Brian says:
6 years later and you’re still saving my bacon. Thanks for this. What a relief!
Posted: 21 April, 2017 at 1:47 PM
Alexander says:
wanna hug you man!
thank you!
Have your say