Caching the results of a PDO Query in Memcache

This is a quick example of code that will prepare a PDO statement and cache the results in Memcache.

It is easiest to use the two following sets of instructions to set up a LAMP environment that will handle the PHP, PDO, and Memcache packages that the code uses:

  1. How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu
  2. How To Install and Use Memcache on Ubuntu 12.04

Code

<?php
function db_connect() {
	global $memcache, $db;

	// Connect to the Memcache server
	$memcache = new Memcache();
	$memcache->pconnect('localhost', 11211);

	// Connect to the Database using PDO
	$host = "localhost";
	$dbname = "test";
	$user = "test";
	$pass = "test";

	try {
		$db = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
	} catch (PDOException $e) {
		die($e->getMessage());		
	}
}

function db_fetch_all($sql, $params = array(), $use_cache = true) {
	global $memcache, $db, $cache_expiration;

	$result = false;
	if ($use_cache) {
		// Generate a key for the cache
		$cache_key = md5($sql . serialize($params));
		$result = $memcache->get($cache_key);
	}

	if (!$result) {
		// Cache Miss: Prepare the sql and recache it
		$sth = $db->prepare($sql);
		
		// This handles ? within a sql query
		// i.e. "SELECT id FROM example WHERE name = ?";
		$i = 0;
		foreach ($params as $param) {
		    $sth->bindParam(++$i, $param);
		}

		// This handles :params within queries
		// "SELECT id FROM example WHERE name = :name";
		foreach ($params as $key => $value) {
		    // keys must be in the form :key within the query
		    $sth->bindParam($key, $value);
		}

		$sth->execute();
		$result = $sth->fetch(); // Fetch the entire result into an array
		
		// Cache expires in 10 seconds
                $cache_key = md5($sql . serialize($params));
		$cache_expiration = 10;
		$memcache->set($cache_key, serialize($result), MEMCACHE_COMPRESSED, $cache_expiration);

		echo "used mysql";
	} else {
		// Cache Hit
		echo "used memcache";
	}

	return $result;
}

echo "Time: " . time() . "<br />";
if (class_exists("Memcache")) {
	db_connect();

	// The query only works with ? as variables in the prepared statement
	$query = "SELECT id FROM example WHERE name = ?";
	$result = db_fetch_all($query, array("new_data"));
} else {
	echo "Memcache not installed :(";
}
?>

Fix MySQL corrupted configuration on Ubuntu

Today, I ran into a problem involving a corrupted MySQL server 5.5 configuration in Ubuntu 13.04. I am not sure how it got to be corrupted in the first place but whenever I ran “mysql” to open a connection to the server, I would get a very generic MySQL error code:


ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

As far as I can tell, the only way to fix that particular problem is to remove and purge configuration so I attempted to remove the server using “sudo apt-get –purge remove mysql-server-5.5” and clean up the other packages that depend on mysql-server-5.5. I also tried to reinstall the packages but I still ran into the same problem. After reinstalling the package, I noticed a problem where a root user had already been created on the server and my password wasn’t working with the account nor did I know what the original password was. I am not sure if this particular issue is related or not. However, after Googling the problem and trying many different things and combining solutions I finally found something that seems to have worked:


sudo apt-get --purge remove mysql-server mysql-common mysql-client
sudo mv /var/lib/mysql /var/lib/mysql.backup
sudo apt-get install mysql-server mysql-common mysql-client

I believe that the problem involves a corrupted table in the directory where MySQL stores the database files so you have to do additional file cleanup when trying to remove MySQL.

I wish you luck if you ever find yourself in this situation and hopefully you can easily remove the server. If not, the commands will create a backup of the files which you may be able to restore after you have reinstalled MySQL.