Say hello to Search Replace DB V3.1

.blog_error_srdb {
font-size: 0.9em;
font-family: ‘gill sans mt bold’, ‘courier new’, monospace;
color: white;
background-color: #a00;
border: 2px solid black;
padding: 0.2em 0.5em;
}

.narrowrule{width:20%;}

We’re proud to announce the release of version 3.1 of Search Replace DB. This release focuses on common issues some users have encountered with our tool. If you’ve had problems with V3.0.0 BETA and had to go back to V2.1.0, we definitely recommend trying this new version instead.

I’ve been working through the bug reports and pull requests on our Github issue tracker, looking at some problems, incompatibilities and oddities as well as some other issues that we’ve discovered in our own use of the script.

Self uninstallation

Although the BETA tag is no more, Search Replace DB remains a powerful yet dangerous tool if used incorrectly. While it’s present on your server, it can potentially be misused by anyone with access to the URL. It’s vitally important that you only have it installed while you’re using it.

Search Replace DB is able to remove itself with a single click (well, two really). Previously, Search Replace DB would delete its own folder without checking, which would have disastrous results if the correct installation instructions weren’t followed. As a new safety feature in V3.1, when you click ‘Delete Me’ the page will check what files would be deleted and will refuse to delete anything if it finds an unexpected file. This should only be relied on as a warning:

Manually verify whether Search Replace DB was removed from your server, and read the information messages and instructions carefully; you are responsible for your data and files while using this tool!

Port numbers

One much-requested feature was the ability to specify a different port number than the MySQL default. V3.0.0 would sometimes let you supply a hostname and port in the form 'mysql.example.com:3307', but only as a curious side-effect of some legacy code.

PHP’s old (now deprecated) mysql_ interface would let you specify your hostname and port together as 'hostname:port'. The new interface PDO specifies it in a different way. Since there was no PDO support in SRDB V2.n, 'hostname:port' would fit into the hostname parameter of mysql_connect(), and it would usually work.

In V3.0.0 we added PDO support to SRDB, and use it instead of mysql_ if possible. If your server had an up-to-date version of PHP (and you definitely should!), SRDB V3.0.0 would try to force 'hostname:port' into the hostname parameter in PDO and it would fail. If you were running PHP 5.0 or earlier, you wouldn’t have PDO available, so the system would fall back to mysql_ and the 'hostname:port' method would work again.

These issues are fixed in V3.1! The port is now a parameter which you can set yourself in both the web and CLI interfaces, and SRDB will attempt to auto-detect your port if it’s installed in a subdirectory of your WordPress or Drupal installation.

‘could not find driver’??

Some users saw the unfortunately cryptic ‘could not find driver’ error when trying to use Search Replace DB.

Our code would try to use the PDO interface if it was available, but didn’t check if there was a ‘mysql’ driver installed for it. Although a standard component in a common-or-garden PHP install, certain custom installations (ones running Windows, or ones upgraded from prior versions of PHP) wouldn’t have it and SRDB would refuse to function without really telling you why. We’ve now fixed this bug in V3.1; our code will now check more rigorously what database drivers are available, using PDO and the mysql module if they are available, falling back to the new mysqli_ interface if they are not.

Improved ‘view changes’

The ‘view changes’ interface has been vastly improved in V3.1. In V3.0.0 BETA, the previews were constructed by performing the search and replace operation in JavaScript and doing a little shuffling to produce the highlight text behind the found terms. The old interface used the JavaScript regular expression search and replace, even if your search wasn’t a regular expression type. If you’re familiar with regular expressions, you could imagine how this would go awry.

If your search or replace terms included a special regular expression character (such as an asterisk *, a back or forward slash \ /, or even the humble full stop .), the preview would interpret it in unexpected ways, sometimes giving you a screenful of dots, random highlights, or nothing at all! Submitting ‘blank’ for the ‘replace’ field also had some surprises, as the code tried to insert highlight tags between every instance of ‘nothing’.

We’ve gone back over the preview panes and completely rewritten how the previews are put together. Now the highlights and previews are much more representative of how the data will be stored in your database. Of course, you should always manually verify your data before and after using Search Replace DB!

You may notice we don’t include highlights when a replace operation is performed on serialised objects. Manipulating the contents of nested serialised objects is very error prone. If we were to manipulate their contents to include highlights, the lengths would be inaccurate. We could request a second set of ‘replaced’ values from the server, containing hidden markers which could then be stripped and replaced with highlight SPAN tags… but by that point you’ve written most of a PHP serializer/unserializer in JavaScript, and everything becomes brittle. Sadly, the JavaScript implementations of serialize() and unserialize() on PHPJS.org aren’t quite ready yet, so we’ve decided to simply display the final contents of the database cell without highlights to prevent any errors from sneaking in.

Drupal initialisation

Although Search Replace DB can detect a WordPress installation, it isn’t just limited to manipulating WordPress database tables. It can work on any set of MySQL tables with columns containing text. The method we use to detect a Drupal installation is to look for a Drupal installation in a nearby directory and try to bootstrap it. Once it’s started, we inspect its Database:: getConnection()-> getConnectionOptions() to retrieve the database details.

In V3.0.0, if Drupal was detected, Search Replace DB would commit to trying a Drupal bootstrap even if the configuration was broken (perhaps if you’d simply copied it from one site to another). This would result in a frustrating message where Search Replace DB would ignore valid database credentials entered on the web form, as it would attempt a bootstrap first and then completely shut down SRDB when the bootstrap failed. This is fixed in V3.1.

Authentication methods

One last issue that we’ve received messages about is the following:

SQLSTATE[HY000] [2054] The server requested
authentication method unknown to the client

This isn’t an SRDB error. This is due to one of the PHP 5.3 Backward Incompatible Changes. If you’re migrating your site to a new installation running different versions of MySQL and PHP, you should be aware that there are some differences in the way MySQL password hashes are stored. Modern PHP MySQL drivers (and by extension SRDB V3.1) require the ‘new password style’. The above error appears when you try to connect to a database that is set up to use the wrong ‘password style’. Please read the MySQL documentation for more details on this.

V2.1 uses the very old mysql_ interface, and may help if you need to connect to an ‘old password style’ MySQL database.

Thanks for reading! Special thanks go to our users who submitted bug reports to us through Github or e-mail. Search Replace DB is licensed under the GPL v3 and we welcome your contributions.