Here’s the syntax of the search/replace SQL:
UPDATE files SET filepath = REPLACE(filepath,'path/to/search','path/to/replace');
UPDATE node_revisions SET body = REPLACE(body, 'path/to/search', 'path/to/replace')
UPDATE node_revisions SET teaser = REPLACE(teaser, 'path/to/search', 'path/to/replace')
Or it can be done from within the mysql gui admin tool too, but the command line is probably faster/easier.
Also, check the mini-panels, but they can’t be updated as quickly:
select * from panels_pane where configuration like '%path/to/search%';
The reason changing data directly in the mini-panels table (panels_pane) doesn’t work is that drupal stores serialized arrays of PHP in a single DB field. (It’s one of the not-so-great things about drupal’s underlying structure.). E.g. the field looks like this:
a:4:{s:11:”admin_title”;s:18:”Did You Know title”;s:5:”title”;s:6:”<none>”;s:4:”body”;s:152:”<img src=”/site_name/sites/site_path/files/heading_did-you-know_orange.jpg” alt=”Did You Know” height=”35″ width=”254″>”;s:6:”format”;s:1:”2″;}
The ‘152’ indicates the numbers of characters in that array element. It’s essentially a simple checksum. If the amount of text following the colon after 152 doesn’t add up to the number, it breaks the array when it’s unserialized. So, you can change the data within the array element, but you also have to calculate the difference and change the checksum. It’s possible, but it’s a hassle and difficult to do automagically via a query.
UPDATE:
My co-worker found a script that handles the issues with serialized php arrays. Yeah! You can find it at this site: http://www.davesgonemental.com/mysql-database-search-replace-with-serialized-php/. I’m including the code here because this is the kind of thing you try to find later and oops! the site doesn’t exist! Too bad wordpress doesn’t keep spacing.
<?php
// Safe Search and Replace on Database with Serialized Data v1.0.1
// This script is to solve the problem of doing database search and replace
// when developers have only gone and used the non-relational concept of
// serializing PHP arrays into single database columns. It will search for all
// matching data on the database and change it, even if it’s within a serialized
// PHP array.
// The big problem with serialised arrays is that if you do a normal DB
// style search and replace the lengths get mucked up. This search deals with
// the problem by unserializing and reserializing the entire contents of the
// database you’re working on. It then carries out a search and replace on the
// data it finds, and dumps it back to the database. So far it appears to work
// very well. It was coded for our WordPress work where we often have to move
// large databases across servers, but I designed it to work with any database.
// Biggest worry for you is that you may not want to do a search and replace on
// every damn table – well, if you want, simply add some exclusions in the table
// loop and you’ll be fine. If you don’t know how, you possibly shouldn’t be
// using this scr
// To use, simply configure the settings below and off you go. I wouldn’t
// expect the script to take more than a few seconds on most machines.
// BIG WARNING! Take a backup first, and carefully test the results of this code.
// If you don’t, and you vape your data then you only have yourself to blame.
// Seriously. And if you’re English is bad and you don’t fully understand the
// instructions then STOP. Right there. Yes. Before you do any damage.
// USE OF THIS SCRIPT IS ENTIRELY AT YOUR OWN RISK. I/We accept no liability from its use.
// Written 20090525 by David Coveney of Interconnect IT Ltd (UK)
// http://www.davesgonemental.com or http://www.interconnectit.com or
// http://spectacu.la and released under the WTFPL
// ie, do what ever you want with the code, and I take no responsibility for it OK?
// If you don’t wish to take responsibility, hire me through Interconnect IT Ltd
// on +44 (0)151 331 5140 and we will do the work for you, but at a cost, minimum 1hr
// To view the WTFPL go to http://sam.zoy.org/wtfpl/ (WARNING: it’s a little rude, if you’re sensitive)
// Version 1.0.1 – styling and form added by James R Whitehead.
// Credits: moz667 at gmail dot com for his recursive_array_replace posted at
// uk.php.net which saved me a little time – a perfect sample for me
// and seems to work in all cases.
// Start TIMER
// ———–
$stimer = explode( ‘ ‘, microtime() );
$stimer = $stimer[1] + $stimer[0];
// ———–
// Database Settings
$host = ‘localhost’; // normally localhost, but not necessarily.
$usr = ”; // your db userid
$pwd = ”; // your db password
$db = ”; // your database
// Replace options
$search_for = ”; // the value you want to search for
$replace_with = ”; // the value to replace it with
if (isset($_POST['search']) && $search_for == ”) {
$search_for = stripcslashes($_POST['search']);
}
if (isset($_POST['replace']) && $replace_with == ”) {
$replace_with = stripcslashes($_POST['replace']);
}
if ($search_for == ” || $replace_with == ” || $replace_with == $search_for) {
header(‘Content-Type: text/html; charset=UTF-8′);?>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
<title>Search and replace DB.</title>
<style type=”text/css”>
body {
background-color: #E5E5E5;
font-size: 12px
}
form {
display:block;
width: 400px;
padding: 10px;
margin: 50px auto;
border:solid 10px #ccc;
background-color: #F5F5F5;
}
fieldset {
border: 0 none;
}
label {
font-weight: bold;
display:block;
line-height: 2em;
}
input.text {
margin-bottom: 1em;
display:block;
width: 90%;
}
input.button {
}
div.help {
border-top: 1px dashed #999999;
margin-top: 20px;
padding-top: 10px
}
</style>
</head>
<body>
<form action=”<?php echo basename(__FILE__)?>” method=”post”>
<fieldset>
<label for=”search_text”>Search for:</label>
<input id=”search_text” type=”text” name=”search” value=”<?php echo $search_for; ?>”/>
<label for=”replac_text”>Replace with:</label>
<input id=”replac_text” type=”text” name=”replace” value=”<?php echo $replace_with; ?>”/>
<input type=”submit” value=”Search and replace” onclick=”if (confirm(‘Are you really REALLY sure you want to do that?’)){return true;}return false;”/>
<div>
<h4>Spectacu.la Safe Search and Replace on Database with Serialized Data v1.0.1</h4>
<p>This script is to solve the problem of doing database search and replace
when developers have only gone and used the non-relational concept of
serializing PHP arrays into single database columns. It will search for all
matching data on the database and change it, even if it’s within a serialized
PHP array.</p>
<p>The big problem with serialised arrays is that if you do a normal DB
style search and replace the lengths get mucked up. This search deals with
the problem by unserializing and reserializing the entire contents of the
database you’re working on. It then carries out a search and replace on the
data it finds, and dumps it back to the database. So far it appears to work
very well. It was coded for our WordPress work where we often have to move
large databases across servers, but I designed it to work with any database.
Biggest worry for you is that you may not want to do a search and replace on
every damn table – well, if you want, simply add some exclusions in the table
loop and you’ll be fine. If you don’t know how, you possibly shouldn’t be
using this script anyway.</p>
<p>To use, simply configure the settings below and off you go. I wouldn’t
expect the script to take more than a few seconds on most machines.</p>
<p><strong style=”color:red”>BIG WARNING!</strong> Take a backup first, and carefully test the results of this code.
If you don’t, and you vape your data then you only have yourself to blame.
Seriously. And if you’re English is bad and you don’t fully understand the
instructions then STOP. Right there. Yes. Before you do any damage.
And don’t forget – <strong style=”color:red”>delete this utility from your
server after use. It represents a major security threat to your database if
maliciously used.</strong></p>
<p><strong>USE OF THIS SCRIPT IS ENTIRELY AT YOUR OWN RISK. <br/> We accept no liability from its use.</strong></p>
</div>
</fieldset>
</form>
</body>
</html>
<?php die;
}
$cid = mysql_connect($host,$usr,$pwd);
mysql_set_charset(‘utf8′);
if (!$cid) { echo(“Connecting to DB Error: ” . mysql_error() . “<br/>”); }
// First, get a list of tables
$SQL = “SHOW TABLES”;
$tables_list = mysql_db_query($db, $SQL, $cid);
if (!$tables_list) {
echo(“ERROR: ” . mysql_error() . “<br/>$SQL<br/>”); }
// Loop through the tables
while ($table_rows = mysql_fetch_array($tables_list)) {
$count_tables_checked++;
$table = $table_rows['Tables_in_'.$db];
echo ‘<br/>Checking table: ‘.$table.’<br/>***************<br/>’; // we have tables!
$SQL = “DESCRIBE “.$table ; // fetch the table description so we know what to do with it
$fields_list = mysql_db_query($db, $SQL, $cid);
// Make a simple array of field column names
$index_fields = “”; // reset fields for each table.
$column_name = “”;
$table_index = “”;
$i = 0;
while ($field_rows = mysql_fetch_array($fields_list)) {
$column_name[$i++] = $field_rows['Field'];
if ($field_rows['Key'] == ‘PRI’) $table_index[$i] = true ;
}
// print_r ($column_name);
// print_r ($table_index);
// now let’s get the data and do search and replaces on it…
$SQL = “SELECT * FROM “.$table; // fetch the table contents
$data = mysql_db_query($db, $SQL, $cid);
if (!$data) {
echo(“ERROR: ” . mysql_error() . “<br/>$SQL<br/>”); }
while ($row = mysql_fetch_array($data)) {
// Initialise the UPDATE string we’re going to build, and we don’t do an update for each damn column…
$need_to_update = false;
$UPDATE_SQL = ‘UPDATE ‘.$table. ‘ SET ‘;
$WHERE_SQL = ‘ WHERE ‘;
$j = 0;
foreach ($column_name as $current_column) {
$j++;
$count_items_checked++;
// echo “<br/>Current Column = $current_column”;
$data_to_fix = $row[$current_column];
$edited_data = $data_to_fix; // set the same now – if they’re different later we know we need to update
// if ($current_column == $index_field) $index_value = $row[$current_column]; // if it’s the index column, store it for use in the update
// unserialise – if false returned we don’t try to process it as serialised
$unserialized = @unserialize( $data_to_fix ); // unserialise – if false returned we don’t try to process it as serialised
if ( ‘b:0;’ === $data_to_fix || false !== $unserialized ) {
// echo “<br/>unserialize OK – now searching and replacing the following array:<br/>”;
// echo “<br/>$data_to_fix”;
//
// print_r($unserialized);
recursive_array_replace($search_for, $replace_with, $unserialized);
$edited_data = serialize($unserialized);
// echo “**Output of search and replace: <br/>”;
// echo “$edited_data <br/>”;
// print_r($unserialized);
// echo “———————————<br/>”;
}
else {
if (is_string($data_to_fix)) $edited_data = str_replace($search_for,$replace_with,$data_to_fix) ;
}
if ($data_to_fix != $edited_data) { // If they’re not the same, we need to add them to the update string
$count_items_changed++;
if ($need_to_update != false) $UPDATE_SQL = $UPDATE_SQL.’,'; // if this isn’t our first time here, add a comma
$UPDATE_SQL = $UPDATE_SQL.’ ‘.$current_column.’ = “‘.mysql_real_escape_string($edited_data).’”‘ ;
$need_to_update = true; // only set if we need to update – avoids wasted UPDATE statements
}
if ($table_index[$j]){
$WHERE_SQL = $WHERE_SQL.$current_column.’ = “‘.$row[$current_column].’” AND ‘;
}
}
if ($need_to_update) {
$count_updates_run;
$WHERE_SQL = substr($WHERE_SQL,0,-4); // strip off the excess AND – the easiest way to code this without extra flags, etc.
$UPDATE_SQL = $UPDATE_SQL.$WHERE_SQL;
echo $UPDATE_SQL.’<br/><br/>’;
$result = mysql_db_query($db,$UPDATE_SQL,$cid);
if (!$result) {
echo(“ERROR: ” . mysql_error() . “<br/>$UPDATE_SQL<br/>”); }
}
}
}
// Report
$report = $count_tables_checked.” tables checked; “.$count_items_checked.” items checked; “.$count_items_changed.” items changed;”;
echo ‘<p style=”margin:auto; text-align:center”>’;
echo $report;
mysql_close($cid);
// End TIMER
// ———
$etimer = explode( ‘ ‘, microtime() );
$etimer = $etimer[1] + $etimer[0];
printf( “<br/>Script timer: <b>%f</b> seconds.”, ($etimer-$stimer) );
echo ‘</p>’;
// ———
function recursive_array_replace($find, $replace, &$data) {
if (is_array($data)) {
foreach ($data as $key => $value) {
if (is_array($value)) {
recursive_array_replace($find, $replace, $data[$key]);
} else {
// have to check if it’s string to ensure no switching to string for booleans/numbers/nulls – don’t need any nasty conversions
if (is_string($value)) $data[$key] = str_replace($find, $replace, $value);
}
}
} else {
if (is_string($data)) $data = str_replace($find, $replace, $data);
}
}
?>
