mySQL Database Search & Replace With Serialized PHP [Updated]

Ever needed to migrate a database to a new server or website (especially with WordPress and other PHP applications) and been stuck because when you do a search and replace some of the data seems to get corrupted?

Please note that a newer version of this code is now available from my Interconnect’s site over at https://interconnectit.com/search-and-replace-for-wordpress-databases/ 

Ever needed to migrate a database to a new server or website (especially with WordPress and other PHP applications) and been stuck because when you do a search and replace some of the data seems to get corrupted?

Serialized PHP Arrays Cause Problems

In PHP one of the easiest ways of storing an array in a database is to use the serialize function.  Works a treat, but the downside is that you’re not storing data with a cross platform method.  In many product development environments this would get you a stern talking to, but in the world of web development where deadlines are tight and betas are the norm, this seems to be overlooked somewhat.

So what we have are tables full of data that can’t be easily edited by hand.  For example:

;a:3:{s:5:"title";s:17:"This Week\'s Poll";s:18:"poll_multiplepolls";s:0:"";s:14:"multiple_polls";N;}

Say you had thousands of records like the one above, and the word ‘multiple’ needs to be changed to ‘happy’.  Two bits would change – poll_multiplepolls would now read poll_happypolls and multiple_polls would read happy_polls.  In both cases you would have three characters fewer to deal with.

Fine, you may think, but you can only do the change by hand because where it says s:18:"poll_multiplepolls" it now has to say s:15:"poll_happypolls" – see the difference?  S18 spells out the length of the following string, and it has to be changed to s:15

I’ll say right now, that that was a pain.  For simple arrays I wrote the straightforward PHP Serialization fixer code, which got me out of many a pickle – do the search and replace without worrying, and then run the script.  Fixed about 90% of problems.

Multidimensional Array Problem

Sadly those 10% of problems left were a real pain.  I needed something more robust.  Something more powerful.  And finally today it was a Bank Holiday in the UK – that means no phone calls… I could have a quiet day of coding and concentrate on the best solution to this problem.

What I’ve done is to write a database search and replace utility in PHP that scans through an entire database (so use with care!) which is designed for developers to use on database migrations.  It’s definitely not what you’d call an end-user tool, though I may sanitize it at some point and turn it into an easy to use WordPress plugin.  Thing is – this is dangerous code – sometimes I think it’s better to make it deliberately a bit tricky, don’t you?

It’s not that bad though – if you can manually install WordPress, you can easily configure the database connection settings.

What the code does is to look at the database, analyse the tables, columns and keys, and then starts reading through it.  It will attempt to unserialize any data it finds, and if it succeeds it will modify that data then reserialize it and pop it back in the database where it found it.  If it finds unserialized data it will still carry out the search and replace.

Use in WordPress

In most WordPress migrations you tend to have the primary problem of changing the domain name entries in content, settings and widgets – you simply need to put in the $search_for string the old domain address (including the http if it’s there) as seen on the database, and the new one into $replace_with.  Then put this script onto your server, and run it by visiting it in your browser or inputting the appropriate command line – depending on your server configuration.

Other things you may want to check are for plugins or themes that have made the mistake of storing the full server path to the installation – cFormsII does this, for example.  You will need to find out your old and new server paths and use those, in full, for another iteration of this script.

After less than a second of running, you should have a freshly edited database.  It may take a little longer on slow or share hosting, or if you have a very large database, but on my laptop I can manage around 60,000 items of data per second.

I’ve just used the script to migrate, in its entirety, with content, settings, 87 widgets (yes, really!) and hundreds of images to my localhost server.  It took moments, and the site is perfectly preserved.

Search and Replace Database download.
download file

Search and Replace Database 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.

Pure. Comedy. Gold. Jozin z Bazin

This is a glorious song about the Swamp Man Jozin… Ropey English subtitles below. It’s well worth watching throughout. Well, to me it is…

This is a glorious song about the Swamp Man Jozin…  Ropey English subtitles below.  It’s well worth watching throughout.  Well, to me it is…

http://www.youtube.com/watch?v=S4aqM_wu6Ns

Boo to MPs! Boo to Presenters!

MPs are getting it in the neck as a result of some of their more wild expenses claims. Lord Foulkes got very uppity.

Seriously, both of them are as bad as each other. Lord Foulkes for failing to understand the difference between expenses and salary, and the presenter for being rude.

Kudos to Carrie Gracie for answering the question though – she could have given a typical politician’s answer and fudged the response.

But you know, MPs (or anybody else for that matter) – when you make an expenses claim that could, potentially, come under public scrutiny at some point you have to think about how it could look. And if you’re not paid enough, then sort out the pay. But given that each constituency manages to have lots of applicants for the job I suspect that the job is seen as a rather attractive one. I would be quite happy to receive an MP’s income along with money to pay for an office and staff. Lucky them.

So here’s a gentle nudge to MPs to have a think about how you look to the public you serve, and the public who pay your salary. And yes, I do appreciate that many MPs are decent and hardworking – but they need to be open so we can see this.

Nice

Nice is one of the playgrounds of the French Riviera – but it can look cold in Winter. Still a damn sight warmer than Liverpool, however…

I found a nice picture I took in Nice back in 2004 that I thought I might as well share:

img_4102s

Slow Posting Performance in WordPress 2.7

An interesting problem today – WordPress posting on this site was sloooooow.  Some other back-end tasks were also incredibly slow.

Adding a post or page would take about 30 to 60 seconds.  Unacceptable.  I did the usual job of deactivating plugins, and even resorted to a different theme for a few minutes but no, it was all still very slow.

That’s when the work starts.  On this server we haven’t opened up port 3600 to allow remote connection with mySQL Administrator, so instead it’s straight into phpMyAdmin.

The process is then pretty simple – for each table you have an Operations tab.  Here you can see some information about the state of the table as well as quick links at the bottom to allow you to run various tools.  The process I use is to backup the site then run a Repair then an Optimize on the _posts (typically tables are prefixed WP_) , _postmeta and _options tables.

Why the Slowdown?

Well, I didn’t bother investigating properly – without full server access it’s often hard to get full timings.  You can put traces into code and look at logs, or you can very quickly run the Repair process.  But what I do know is that databases can slow down for inserts more easily than for reads.  This is usually because something’s become messed up in an Index, or the whole table’s become inefficiently organised over time.  By repairing and optimizing you get to quickly tidy things up and restore the performance of the site.

For a minor site like this, it’s about the best approach to take.  If you’re running something like Facebook you’ll probably want to investigate things properly – but then you’ll have the money to be able to do so as well!

Handy Tip for CSS Reloading

CSS not refreshing in the browser can be a pain – Mark Jaquith has come up with an elegant solution to this for WordPress coders.

I saw this rather handy tip on CSS reloading on Mark Jaquith’s blog today.  Such a simple approach, but a great one for when you’re making CSS changes and need browsers to pick up on it immediately, rather than some hours down the line.

Some changes are critical.  Now you can version your css by hand, but you don’t have to.

The basis of the change is to use the code:

<link rel="stylesheet" href="<?php bloginfo('template_url'); ?>/style.css?v=<?php echo filemtime(TEMPLATEPATH . '/style.css'); ?>" type="text/css" media="screen, projection" />

But for the detail, go read Mark’s post.