Archive for March, 2010

Posted by 6bytes at 30, March, 2010

Category: MySQL, PHP

Tags: , , , ,

The bug

If you’re running PHP 5.1.6 and just started using PDO for your database connection, it’s likely you’ll run into quite an annoying bug.
Lets test a simple query partly taken from PHP documentation.

$calories = 150;
$colour = 'red';
$sth = $myPDO->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
if ($sth->rowCount() >= 1) {
    // iterate through results
}

Everything seems great. The problem is that $sth->rowCount() will always return 0. No matter how many results your query returns, the value of rowCount() will always be 0.

Full bug report can be found here: http://bugs.php.net/40822.

Solution

Upgrade PHP 🙂

If that’s not an option read on.

Lets create our own simple PDO and PDOStatement classes.

class myPDO extends PDO {
	function __construct($name_host, $username='', $password='', $driverOptions=array()) {
		parent::__construct($name_host, $username, $password, $driverOptions);
		$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('myPDOStatement', array($this)));
	}
}

class myPDOStatement extends PDOStatement {
	public $db;

	protected function __construct($db) {
		$this->db = $db;
	}
}

Above will not fix anything. This is just a start so we can overload some PDO methods to apply the fix.

To fix the bug we need to tell MySQL to use the buffered versions of the MySQL API by setting attribute MYSQL_ATTR_USE_BUFFERED_QUERY to true.
For some reason, still unknown to me setting this option like that

class myPDO extends PDO {
	function __construct($name_host, $username='', $password='', $driverOptions=array()) {
		parent::__construct($name_host, $username, $password, $driverOptions);
		$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('myPDOStatement', array($this)));
		$this->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
	}
}

will not work.

After many hours of tearing my hair out I found out that all you need to do is this:

class myPDO extends PDO {
	function __construct($name_host, $username='', $password='', $driverOptions=array()) {
		$driverOptions[PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = true;
		parent::__construct($name_host, $username, $password, $driverOptions);
		$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('myPDOStatement', array($this)));
	}
}

You have to set MYSQL_ATTR_USE_BUFFERED_QUERY before instantiating a connection. Setting it after the connection was made will not work.

When this is done we need to modify our myPDOStatement class.

class myPDOStatement extends PDOStatement {
	public $db;
	// other attributes
	private $foundRows; // will hold number of affected rows

	protected function __construct($db) {
		$this->db = $db;
	}

	public function execute($array = null) {
		if ($array === null) {
			$result = parent :: execute();
		} else {
			$result = parent :: execute($array);
		}

		// fix for PHP 5.1.6 rowCount error
		$this->foundRows = $this->db->query("SELECT FOUND_ROWS()")->fetchColumn();

		return $result;
	}

	public function rowCount() {
		return $this->foundRows;
	}
}

After above changes our rowCount() method will return proper values.
 

Posted by 6bytes at 24, March, 2010

Category: Linux

Tags: , ,

If you want to avoid situation when after server restart you have to start lots of services manually, a little thing like chkconfig would come in handy.
First you might want to take a look at a list of services handled by chkconfig:

chkconfig --list

You should see something like this:

mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off
named           0:off   1:off   2:off   3:on    4:off   5:off   6:off
sshd            0:off   1:off   2:on    3:on    4:on    5:on    6:off

To add for example mysql to autostart list simply type:

chkconfig --add mysql on

To remove a service from autostart list type:

chkconfig --del mysql

For full list of chkconfig options go here.

Posted by 6bytes at 16, March, 2010

Category: PHP

Tags: , ,

In last two posts I described how to install memcached daemon and memcache extension for PHP. Today I’ll show you how to configure your PHP to use memcache to handle sessions.

One thing I should mention is that when installing memcache you should answer yes when asked

1. Enable memcache session handler support? : yes

First of all lets start two memcached processes just in case one crashes, we’ll have a second one ready right away.

memcached -u root -d -m 512 -l 127.0.0.1 -p 11211
memcached -u root -d -m 512 -l 127.0.0.1 -p 11212

Worth mentioning is that when one memcached process crashes session information will not be transferred to another. New session will be started.

Next edit your php.ini file. Comment the line with your current session handler setting

; session.save_handler = files

and add two new lines

session.save_handler = memcache
session.save_path = "tcp://localhost:11211, tcp://localhost:11212"

If for any reason you don’t want to edit php.ini file you can set those options directly in your PHP script.

ini_set('session.save_handler', 'memcache');
ini_set('session.save_path', 'tcp://localhost:11211, tcp://localhost:11212');

Restart your web server

service httpd restart

For full list of runtime configuration options go here.

Posted by 6bytes at 13, March, 2010

Category: CentOS, Linux, PHP

Tags: , , ,

After installing memcached daemon we have to install memcache PHP extension.

yum install php-pecl-memcache

or

apt-get install php5-memcache

If above is not an option try below method.

pecl install memcache

After successful installation add memcache.so extension to your php.ini file.

vim /etc/php.ini

Add this line:

extension=memcache.so

Restart web server

service httpd restart

Test out our installation.

<?php
$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ('Can\'t connect!');
$version = $memcache->getVersion();
echo 'Server version: '.$version;

You should see your server’s version number. If you see a blank page make sure that memcached daemon is working. If you’re getting “Can’t connect!” message despite running daemon try changing ‘localhost’ to ‘127.0.0.1’.

Posted by 6bytes at 13, March, 2010

Category: CentOS, Linux

Tags: , , , ,

Before we install memcached we need a dependency library libevent. Check for latest stable version at http://monkey.org/~provos/libevent/

cd /usr/local/src
wget http://monkey.org/~provos/libevent-1.4.13-stable.tar.gz
tar -xzvf libevent-1.4.13-stable.tar.gz
cd libevent-1.4.13-stable
./configure --prefix=/usr/local
make
make install
cd ..

Now lets install memcached. Check for latest stable version at http://memcached.org/

wget http://memcached.googlecode.com/files/memcached-1.4.4.tar.gz
tar -xzvf memcached-1.4.4.tar.gz
cd memcached-1.4.4
LDFLAGS='-Wl,--rpath /usr/local/lib' ./configure --prefix=/usr/local
make
make install

More information about LDFLAGS http://en.wikipedia.org/wiki/Linker

Run memcached as a daemon. Basic options: d = daemon, m = memory, u = user, l = IP to listen to, p = port

memcached -u root -d

or

memcached -u root -d -m 512 -l 127.0.0.1 -p 11211

To stop daemon type

pkill memcached

Now we’re off to installing php extension memcache.