MySQL – Find and replace string (Useful for WordPress migrations)

Posted by & filed under PHP, Programming, Web Development.

I recently deployed a WordPress site. As part of the development cycle, we first built the site on staging.example.com, then moving it to the primary domain at launch. One issue that this can cause is when creating content, WordPress will create links with the full site’s URL. In our case the staging domain was linked on most images and links. When we went live, this caused some issues. It’s not a uncommon thing to run into, and fortunately there is a simple solution. The following PHP script will connect to the database, searching all tables for the specified string (in our instance a domain name) and replacing it with another string.

Simply update the username, password, database, string_to_replace and new_string with the appropriate values and you are off! I would recommend backing up the database to be safe.

 

Thanks to jimmy.zoger on Stack Overflow for the useful solution.

 

A follow up to this, is that if the values in the database are serialized, a find/replace can wreak havoc on things as it will likely break the serialization unless the character count is the same. A very nice utility I found is the following and it handles the serialization perfectly: https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

Migrating MySQL databases from one server to another

Posted by & filed under Server Admin.

Today I needed to begin migrating databases from my live server to the new dedicated database server. The first step is to identify the tables to be moved.

Login to the server as per normal

Then list out the tables to find the one(s) we need to move:

For this example, we will move the “test” database. The next step is to create the new empty test database on the remote server:

The next step is to use the mysqldump command to extract the contents of the current test database and pipe it to the new server database:

Once this command completes, the database will be on both servers.

Repairing a corrupted MySQL database table

Posted by & filed under Server Admin, Web Development.

Ran into a issue where I wanted to do a mysqldump of a database in order to transfer it to a new server.

This failed saying that three of the tables were corrupted. I ran the mysqlcheck utility to see if it could be repaired:

It outputted the following errors among checking the rest of the tables successfully:

I was strongly suspecting that these tables were old remnants of a old software version or something along those lines.

I tried to re-run the command, telling it to repair the tables. It kicked out the same errors about the tables not being found.

Went ahead and issues a drop command for the three tables, as I suspect this is unused and leftover from a previous upgrade.

After each of the drop statements, MySQL reported a error that it was unable to delete as it could not find the table. I re-ran mysqlcheck and found that it actually did remove them, and it reported no issues. I was then able to go ahead and re-run my mysqldump command and completed extracting the database.

MySQL Data Imports

Posted by & filed under PHP, Programming.

I recently needed to import a large CSV file into MySQL. After trying unsuccessfully to import the CSV through phpmyadmin’s web interface, and then trying equally as unsuccessfully to increase PHP’s size limitations, I decided to go right to the source.

After running some data cleanup commands, I had my CSV file ready to go on the server — /tmp/data.csv:

As you can see, I have “LINES TERMINATED BY” set to “<>” which is the special character I needed to use at the end of my rows in my special situation. The field delimiter has also been changed to a pipe (|) symbol to alleviate issues with commas in the fields.

Lastly, I returned quite a few errors, and we can look at these by issuing:

And I see that the warnings are fine, and due to empty fields in the source csv.

MySQL: Create a Unique Composite Key

Posted by & filed under Uncategorized.

I had a scenario where I want to ensure that multiple values are unique in a table. Meaning I want to allow this:

But not this:

This is suprisingly easy to use when we implement unique composite keys. For an existing table:

ALTER TABLE TableName ADD UNIQUE KEY (KeyOne, KeyTwo, ...);

Now if a insert is attempted the following error is thrown:

Integrity constraint violation: 1062 Duplicate entry ‘Nathan Riley-4’ for key ‘name’

In yii, we trap this using a custom validator class in components/CompositeUniqueKeyValidator.php:


keyColumns);
if (count($keyColumns) == 1) {
throw new CException('CUniqueValidator should be used instead');
}
$columnsLabels = $object->attributeLabels();

$criteria = new CDbCriteria();
$keyColumnsLabels = array();
foreach ($keyColumns as &$column) {
$column = trim($column);
$criteria->compare($column, $object->$column);
$keyColumnsLabels[] = $columnsLabels[$column];
}
unset($column);
$criteria->limit = 1;

if ($class::model()->count($criteria)) {
$message = Yii::t('yii', $this->errorMessage, array(
'{columns_labels}' => join(', ', $keyColumnsLabels)
));
if ($this->addErrorToAllColumns) {
foreach ($keyColumns as $column) {
$this->addError($object, $column, $message);
}
}
else {
$this->addError($object, $attribute, $message);
}
}
}

}

?>

Then in our model’s rules():


public function rules()
{
return array(
array('name', 'CompositeUniqueKeyValidator', 'keyColumns' => 'name, tournamentId'),
);
}

MySQL – Monitor live SQL queries as they process

Posted by & filed under Uncategorized.

I have read this can cause adverse performance affects and therefore should only be used for troubleshooting or on a dev box (as is my case)

=> Open your MySQL configuration file, normally it located at /etc/mysql/my.cnf

=> Look for a this line

# log=/var/log/mysql/mysql.log

=> remove the # and save the file

log=/var/log/mysql/mysql.log

=> Now, restart your mysql server to take immediate effect

$ /etc/init.d/mysql restart

=> Now we just tail the log file:

$ tail -f /var/log/mysql/mysql.log