MySQL has a kill command which lets you kill a specific query or connection. This is useful it situations where you’ve accidentally issued a really complex query, or an application created a persistent connection and forgot to close it.

Killing lots of connections can be tedious though. Say an application has created lots of persistent connections and not closed any of them, you’d have to go through the following steps:


mysql> SHOW FULL PROCESSLIST; /* shows all connections */
mysql> KILL <process_id>; /* for EVERY process you want to kill */


To make this process much simpler I wrote a little bash script that will kill all connections for a given MySQL user.

#!/bin/bash
# Kill all MySQL connections for a given user
# Ben Dowling - Apr 2009
# www.coderholic.com

user="root" # MySQL super user
pass="" # Above user's password
kill_user="myapp" # User who you want to kill all the connections for

# Get the connection IDs for all connections from the user $kill_user
processList=$(mysql -u$user -p$pass -s -e "SHOW FULL PROCESSLIST" | grep $kill_user | awk '{print $1}')
# Loop through all the connection IDs and kill the connection
for id in $processList
do
       result=$(mysql -u$user -p$pass -s -e "KILL $id")
       echo $result
done

The MySQL superuser account name and password, along with the user you want to kill all the connection for, are embedded within the script. It would be quite simple to pass these in as command line arguments if you wanted to though.

One thing I really like about CakePHP is that when it’s in debug mode it prints out a list of all the database queries used to generate a page, along with the time it took to run each query. This makes it really easy to find problem queries that are slowing down your site. It’s such a great feature that I wanted something similar for projects that don’t use CakePHP.

What I came up with was a LoggedPDO class that extends PHP’s PDO class. If you’re already using PDO in your project you only need to change a single line of code to enable database query logging:

// Standard PDO...
$db = new PDO($dsn, $username, $password);
// ...should be changed to
$db = new LoggedPDO($dsn, $username, $password);

And then all your database queries will be logged, timed and all information printed out at the end of each page. My code also includes a decorator for PDOStatements so any prepared statements that get executed also get logged. Below is a small sample of the code in use:

$db = new LoggedPDO("mysql:dbname=test;host=localhost", "root");
$results = $db->query("SHOW TABLES");
$tables = $results->fetchAll(PDO::FETCH_COLUMN);
foreach($tables AS $table) {
	$statement = $db->prepare("DESCRIBE :table");
	$statement->bindParam(":table", $table, PDO::PARAM_STR);
	$results = $statement->execute();
	$columns = $statement->fetchAll(PDO::FETCH_ASSOC);
}

Which would output:

Full code for the both the LoggedPDO and LoggedPDOStatement classes is below:

/**
* Extends PDO and logs all queries that are executed and how long
* they take, including queries issued via prepared statements
*/
class LoggedPDO extends PDO
{
	public static $log = array();

	public function __construct($dsn, $username = null, $password = null) {
		parent::__construct($dsn, $username, $password);
	}

	/**
	 * Print out the log when we're destructed. I'm assuming this will
	 * be at the end of the page. If not you might want to remove this
	 * destructor and manually call LoggedPDO::printLog();
	 */
	public function __destruct() {
		self::printLog();
	}

	public function query($query) {
		$start = microtime(true);
		$result = parent::query($query);
		$time = microtime(true) - $start;
		LoggedPDO::$log[] = array('query' => $query,
				                  'time' => round($time * 1000, 3));
		return $result;
	}

	/**
	 * @return LoggedPDOStatement
	 */
	public function prepare($query) {
		return new LoggedPDOStatement(parent::prepare($query));
	}

	public static function printLog() {
		$totalTime = 0;
		echo '<table border=1><tr><th>Query</th><th>Time (ms)</th></tr>';
		foreach(self::$log as $entry) {
			$totalTime += $entry['time'];
			echo '<tr><td>' . $entry['query'] . '</td><td>' . $entry['time'] . '</td></tr>\n';
		}
		echo '<tr><th>' . count(self::$log) . ' queries</th><th>' . $totalTime . '</th></tr>\n';
		echo '</table>';
	}
}

/**
* PDOStatement decorator that logs when a PDOStatement is
* executed, and the time it took to run
* @see LoggedPDO
*/
class LoggedPDOStatement {
	/**
	 * The PDOStatement we decorate
	 */
	private $statement;

	public function __construct(PDOStatement $statement) {
		$this->statement = $statement;
	}

	/**
	* When execute is called record the time it takes and
	* then log the query
	* @return PDO result set
	*/
	public function execute() {
		$start = microtime(true);
		$result = $this->statement->execute();
		$time = microtime(true) - $start;
		LoggedPDO::$log[] = array('query' => '[PS] ' . $this->statement->queryString,
				                  'time' => round($time * 1000, 3));
		return $result;
	}
	/**
	* Other than execute pass all other calls to the PDOStatement object
	* @param string $function_name
	* @param array $parameters arguments
	*/
	public function __call($function_name, $parameters) {
		return call_user_func_array(array($this->statement, $function_name), $parameters);
	}
}

There is an article over a DZone today “What if your contact form fails?”. The article discusses what to do if the contact form on your website fails to send a message. The approach they suggest is to display a message to the user saying it failed, and presenting them with a mailto link so they can send you an email themselves.

This is one approach to the failed contact form problem, but it isn’t going to help much if the problem is actually with your email address. For example, you may have exceeded your disk quota, or you might have a badly configured mail server.

Another problem with the mail solution is that it requires the user to perform another action. They’ve gone to the trouble of filling out a contact form. If that fails they might not bother to send to send you an email too (although the suggested approach in the article does reduce the effort required).

An Alternative Approach

So what other approaches are there? What I do is store all contact form information in a database. Below is the SQL to create the basic messages table:

CREATE TABLE messages (
    id int auto_increment NOT NULL,
    sent timestamp NOT NULL,
    sender varchar(255) NOT NULL,
    message text,

    PRIMARY KEY(id)
);

Then when a contact form gets submitted you can store the information in the database before sending an email. Below is some sample PHP code, using PDO to access the database:

// Get the form data
$sender = $_POST['sender'];
$message = $_POST['message'];

// Add the message to the database
$query = "INSERT INTO messages(sender, message) VALUES (:sender, :message);
$stmt = $pdo->prepare($query);
$stmt->bindParam(':sender', $sender, PDO::PARAM_STR);
$stmt->bindParam(':message', $message, PDO::PARAM_STR);
$stmt->execute();

// Send the mail
mail("me@my.domain", "Contact Form Message", $message, "From: {$sender}");

All that is left to do is create an admin page that lists all messages, and allows us to delete them. We could even include a link to the admin page in the emails we send to ourselves. By using this approach we never lose the message content, even if the mail call fails, because they all get stored in the database. All we need to do is check the admin page every so often and we’ll soon see if there are any new messages there.

Of course, this approach doesn’t have to be used in isolation. We can combine it with the approach suggested in the DZone article, and present the user with a mail link if sending the mail does fail. If the user decides not to send a mail though, we haven’t lost anything.

If you don’t already know about it, Dia is an open source diagram drawing application, similar to Microsoft’s Visio. It makes it really simple to create all sorts of diagrams, including UML diagrams, flowcharts, and network diagrams. It also allows diagrams to be exported to a number of formats, including PNG images (which can easily be converted to GIF images using my handy PNG2GIF utility).

Using a third party tool tedia2sql, you can automatically generate SQL schema for a number of databases, including Oracle, MySQL, Postgres, based on your database diagram. In this post I’ll describe exactly how.

The diagram

To create the database diagram we’ll be using Dia’s UML tools. Classes represent database tables, and associations between classes represent foreign key constrains.

In this post I’ll go over a simple example database with just three tables: Film, Film_Actor and Actor.

To create our database diagram we’re going to use Dia’s UML shapes. When you first start Dia it defaults to displaying the “Assorted” set of shapes, so you’ll need to change the drop down to “UML”

Then we can click on the UML class shape, which is at the top left.Once we’ve done that we should have a diagram that looks like the one below:

Double clicking on the class will bring up a properties window, which has several tabs. On the first “Class” tab we can set the name. For the first table that is “Film”. On the “Attributes” tab we can enter the rows that this table will have. Setting the attribute visibility to protected signifies that it is a primary key.

After adding all three tables and attributes we end up with a diagram like the one below:

At this point we could already generate a database schema, but it wouldn’t contain any foreign key contraints. If you’re not interested in adding them then you can skip straight to the generation part.

Foreign key contraints must be modelled using the UML aggregation tool: The line with the white diamond on one end. Select that tool, and then click on one table and drag and drop onto another table. When you let go of the mouse button a link should be drawn between the two. Double clicking on that line brings up a properties dialog.

Enter the foreign key in one end, and the row to which the foreign key referrs. The link between the Film_Actors table and the Films table is shown above, where the link is from film_id to id.

After adding a similar link between the Film_Actors and Actor table we end up with our complete diagram:

Generating the SQL

Now that our database diagram is compete we can generate the SQL commands to create the database. The command to generate the code for MySQL InnoDB is:

tedia2sql -i diagram.dia -o schema.sql -t innodb -f

If we want to generate SQL for a different database (such as Oracle) then we just need to change the -t argument. The generated SQL contains lots of comments, but the main sections are shown below:

-- Film
create table Film (
  id                        int not null,
  title                     varchar,
  year                      int,
  constraint pk_Film primary key (id)
) type = InnoDB ;

-- Actor
create table Actor (
  id                        int not null,
  name                      varchar,
  dob                       date,
  constraint pk_Actor primary key (id)
) type = InnoDB ;

-- Film_Actors
create table Film_Actors (
  film_id                   int not null,
  actor_id                  int not null,
  constraint pk_Film_Actors primary key (film_id,actor_id)
) type = InnoDB ;

alter table Film_Actors add constraint film_Actors_fk_Film_id
  foreign key (film_id)
  references Film (id)  ;
alter table Film_Actors add constraint film_Actors_fk_Actor_id
  foreign key (actor_id)
  references Actor (id)  ;

Other Dia tools

In this post I described how easy it is to generate SQL from a Dia diagram using tedia2sql. There are lots of other great third party tools to automatically generate output based on your diagram. The official Dia links page lists many of them.

I’ve recently needed to move some databases from one server to another. I found the simplest way to do this was to use mysqldump. The small shell script below copies a given database from a remote host to the local host. Just fill in the variables and run it to copy the whole database:

#!/bin/bash
# Copy a remote database to a local one

REMOTE_HOST=''
REMOTE_USER=''
REMOTE_PWD=''
REMOTE_DB=''

LOCAL_USER=''
LOCAL_PWD=''
LOCAL_DB=''

mysqldump -h$REMOTE_HOST -u$REMOTE_USER -p$REMOTE_PWD --opt --compress $REMOTE_DB | mysql -u$LOCAL_USER -p$LOCAL_PWD $LOCAL_DB