PHP Wrapper Class for a Read-only database

Posted on January 4th, 2010 in General,Linux System Administration,MySQL,PHP,Programming by Brandon

This is a pretty special case of a database wrapper class where I wanted to discard any updates to the database, but want SELECT queries to run against an alternative read-only database. In this instance, I have a planned outage of a primary database server, but would like the public-facing websites and web services to remain as accessible as possible.

I wrote this quick database wrapper class that will pass all SELECT queries on to a local replica of the database, and silently discard any updates. On this site almost all of the functionality still works, but it obviously isn’t saving and new information while the primary database is unavailable.

Here is my class. This is intended as a wrapper to an ADOdb class, but it is generic enough that I think it would work for many other database abstraction functions as well.

class db_unavailable {
    var $readonly_db;

    function __construct($readonly_db)
    {
        $this->query_db = $readonly_db;
    }

    function query($sql)
    {
        $args = func_get_args();
        if (preg_match("#(INSERT INTO|REPLACE INTO|UPDATE|DELETE)#i", $args[0])) {
            // echo "Unable to do insert/replace/update/delete query: $sql\n";
            return true;
        } else {
            return call_user_func_array(array($this->readonly_db, 'query'), $args);
        }
    }

    function __call($function, $args)
    {
        return call_user_func_array(array($this->readonly_db, $function), $args);
    }
}

I simply create my $query_db object that points to the read-only database. Then create my main $db object as a new db_unavailable() object. Any select queries against $db will behave as they normally do, and data-modifying queries will be silently discarded.

Booting from a Software Raid device on Ubunto Karmic (9.10)

Posted on October 26th, 2009 in General,Linux System Administration by Brandon

Its a few days away from Karmic’s official release, and I’m putting together a new computer and thought I would give the new version a try. I set everything up with Software raid, and it is still annoying to me that to do so on Ubuntu still requires the Alternate CD and a text-based install.

I configured the /boot partition to use a RAID1 array, as I normally do. After getting most of the way through the install, I got a big red screen with the following error:

====================================
Unable to install GRUB in /dev/md0
Executing 'grub-install /dev/md0 failed'
This is a fatal error
====================================

I tried several variations on continuing, but nothing seemed to work. Turns out that a known issue with Karmic is that this doesn’t work. Actually the known issue says that grub is only installed to the first drive, but that was not my experience.

Bug 420748 gives some detail on the issue, and it seems that the change to Grub2 might have something to do with it. From the red screen, I chose to proceed without installing a boot loader. I Rebooted to Alternate CD, and chose to repair a broken installation. Once to a shell, I ran these commands to install grub to the master boot records on both drives

grub-install /dev/sda
grub-install /dev/sdb

I removed the CD and rebooted. It ended up at a grub shell. At which point I ran the following to boot into the system (note the difference in grub2 commands)

set root=(hd0,1)
linux /vmlinuz-2.6.31-14-generic ro root=/dev/md1 (use tab-completion on the kernel image filename)
initrd /initrd.img-2.6.31-14-generic (again, use tab-completion)
boot

At that point, it started up normally. Once logged in, I started a terminal and then ran this command as root to create a minimal grub.cfg file (note that it is no longer called menu.lst)

grub-mkconfig > /boot/grub/grub.cfg

I’m now able to reboot into a working system. So much for a flawless install experience.

Setting Up Virtualmin on an OpenVZ Guest

Posted on September 7th, 2009 in General,Linux System Administration,MySQL,Security by Brandon

I’m experimenting with a hosting control panel and am interested in Virtualmin. I generally avoid web-based control panels, because they generally make direct configuration via the command line and manually editing config files very difficult. However one of Virtualmin’s goals is to not interfere with such manual configurations. I’ve had plenty of clients who use Webmin, and they seem to do a good job, so Virtualmin seems like a good choice.

These are the steps that I went through to get a new OpenVZ guest set up with the GPL version of Virtualmin.

Download a CentOS 5 OS template and create the guest

# wget http://download.openvz.org/template/precreated/centos-5-x86_64.tar.gz
# vzctl create <VEID> --ostemplate centos-5-x86_64

I replaced all of these limits in /etc/vz/<VEID>.conf. This is based off of a different running machine with some fairly generous limits. Most importantly, it includes 1GB of RAM.

# UBC parameters (in form of barrier:limit)
KMEMSIZE="43118100:44370492"
LOCKEDPAGES="256:256"
PRIVVMPAGES="262144:262144"
SHMPAGES="21504:21504"
NUMPROC="2000:2000"
PHYSPAGES="0:9223372036854775807"
VMGUARPAGES="65536:65536"
OOMGUARPAGES="26112:9223372036854775807"
NUMTCPSOCK="360:360"
NUMFLOCK="380:420"
NUMPTY="16:16"
NUMSIGINFO="256:256"
TCPSNDBUF="10321920:16220160"
TCPRCVBUF="1720320:2703360"
OTHERSOCKBUF="4504320:16777216"
DGRAMRCVBUF="262144:262144"
NUMOTHERSOCK="5000:5000"
DCACHESIZE="3409920:3624960"
NUMFILE="18624:18624"
AVNUMPROC="180:180"
NUMIPTENT="128:128"

Then set up some host-specific parameters and start it up.

# vzctl set <VEID> --ipadd 10.0.0.1 --hostname yourhostname.com --nameserver 4.2.2.1 --diskspace 4G --save
# vzctl start <VEID>
# vzctl enter <VEID>

You are now logged in to the guest, where you can download and install virtualmin

# yum update
# cd /root
# wget http://software.virtualmin.com/gpl/scripts/install.sh
# sh install.sh
 Continue? (y/n) y

That should install without significant errors. Finally, set a password for root, and then log in to Virtualmin to go through the post-installation configuration

passwd root

Login at https://<your-ip>:10000/ and go through the post-installation configuration

ProFTPd allows multipled DefaultRoot lines for flexible chrooting

Posted on July 2nd, 2009 in General,Linux System Administration,Security by Brandon

The ProFTPd documentation gives good examples of how to use the DefaultRoot directive to chroot users to a specific directory.

A customer today wanted to have different chroot directories for different groups of users. The documentation didn’t mention if it was okay to include multiple DefaultRoot lines. After some experimenting, I can verify that it is allowed and works well.

I used something like this in /etc/proftpd/proftpd.conf

DefaultRoot                     ~ jailed
DefaultRoot                     ~/../.. othergroup

Users in the group ‘jailed’ are chrooted to their own home directory immediately upon logging in. Users in the ‘othergroup’ are chrooted two levels up from their home directory. If you want to get really specific, each user generally has a group of their own, so you can effectively do this a the user-level as well.

PHP Code to Sign any Amazon API Requests

Posted on June 30th, 2009 in General,Linux System Administration,PHP,Programming by Brandon

Starting next month, any requests to the Amazon Product Advertising API need to be cryptographically signed. Amazon has given about three months notice and the deadline is quickly approaching. I use the Amazon web services on several sites and came up a fairly generic way to convert an existing URL to a signed URL. I’ve tested with several sites and a variety of functions, and this is working well for me so far:

function signAmazonUrl($url, $secret_key)
{
    $original_url = $url;

    // Decode anything already encoded
    $url = urldecode($url);

    // Parse the URL into $urlparts
    $urlparts       = parse_url($url);

    // Build $params with each name/value pair
    foreach (split('&', $urlparts['query']) as $part) {
        if (strpos($part, '=')) {
            list($name, $value) = split('=', $part, 2);
        } else {
            $name = $part;
            $value = '';
        }
        $params[$name] = $value;
    }

    // Include a timestamp if none was provided
    if (empty($params['Timestamp'])) {
        $params['Timestamp'] = gmdate('Y-m-d\TH:i:s\Z');
    }

    // Sort the array by key
    ksort($params);

    // Build the canonical query string
    $canonical       = '';
    foreach ($params as $key => $val) {
        $canonical  .= "$key=".rawurlencode(utf8_encode($val))."&";
    }
    // Remove the trailing ampersand
    $canonical       = preg_replace("/&$/", '', $canonical);

    // Some common replacements and ones that Amazon specifically mentions
    $canonical       = str_replace(array(' ', '+', ',', ';'), array('%20', '%20', urlencode(','), urlencode(':')), $canonical);

    // Build the sign
    $string_to_sign             = "GET\n{$urlparts['host']}\n{$urlparts['path']}\n$canonical";
    // Calculate our actual signature and base64 encode it
    $signature            = base64_encode(hash_hmac('sha256', $string_to_sign, $secret_key, true));

    // Finally re-build the URL with the proper string and include the Signature
    $url = "{$urlparts['scheme']}://{$urlparts['host']}{$urlparts['path']}?$canonical&Signature=".rawurlencode($signature);
    return $url;
}

To use it, just wrap your Amazon URL with the signAmazonUrl() function and pass it your original string and secret key as arguments. As an example:

$xml = file_get_contents('http://webservices.amazon.com/onca/xml?some-parameters');

becomes

$xml = file_get_contents(signAmazonUrl('http://webservices.amazon.com/onca/xml?some-parameters', $secret_key));

Like most all of the variations of this, it does require the hash functions be installed to use the hash_hmac() function. That function is generally available in PHP 5.1+. Older versions will need to install it with Pecl. I tried using a couple of versions that try to create the Hash in pure PHP code, but none worked and installing it via Pecl was pretty simple.

(Note that I’ve slightly revised this code a couple of times to fix small issues that have been noticed)

Synchronize Remote Memcached Clusters with memcache_sync

Posted on May 12th, 2009 in General,Linux System Administration,MySQL,Programming by Brandon

The problem: Servers in two separate geographic locations each have their own memcached cluster. However, there doesn’t currently exist (that I know of) a good way to copy data from one cluster to the other cluster.

One possible solution is to configure the application to perform all write operations in both places. However, each operation requires a round-trip response. If the servers are separated by 50ms or more, doing several write operations causes a noticable delay.

The solution that I’ve come up with is a perl program that I’m calling memcache_sync. It acts a bit like a proxy that asynchronously performs write operations on a remote cluster. Each geographic location runs an instance of memcache_sync that emulates a memcached server. You configure your application to write to the local memcache cluster, and also to the memcache_sync instance. memcache_sync queues the request and immediately returns a SUCCESS message so that your application can continue doing its thing. A separate thread then writes those queued operations to the remote cluster.

The result is two memcache clusters that are synchronized in near-real time, without any noticable delay in the application.

I’ve implemented ‘set’ and ‘delete’ operations thus far, since that is all that my application uses. I’ve just started using this on a production environment and am watching to see how it holds up. So far, it is behaving well.

The script is available here. I’m interested to see how much need there is for such a program. I’d be happy to have input from others and in developing this into a more robust solution that works outside of my somewhat limited environment.

MySQL [ERROR] Invalid (old?) table or database name

Posted on April 25th, 2009 in General,Linux System Administration,MySQL by Brandon

After upgrading to MySQL 5, one of my databases appeared empty even though the MyISAM tables existed in /var/lib/mysql/database-name. I found this error in the mysql log file in /var/log/mysqld.log:

090425 23:10:52 [ERROR] Invalid (old?) table or database name 'database-name'

Turns out that MySQL 5 doesn’t like database names that contain a hyphen. In the mysql directory, I see that it created a second directory for the database, but with the hyphen replaced with ‘@002d’.

drwx------  2 mysql mysql     4096 Apr 25 23:10 database-name
drwx------  2 mysql mysql     4096 Apr 24 00:30 database@002dname

To resolve that I decided to replace the hyphen with an underscore. I stopped mysql, renamed /var/lib/mysql/database-name to /var/lib/mysql/database_name, and then ran this SQL to update the permissions:

UPDATE mysql.db SET Db = 'database_name' WHERE Db = 'database\-name';
FLUSH PRIVILEGES;

I could then see the tables using mysql from the command line. From there, I just updated the setting for the application to use the new name, and everything worked out find.

What is in a gclid?

Posted on April 15th, 2009 in General,Linux System Administration,PHP,Programming by Brandon

When you use auto-tagging with your Adwords campaign, all request that are generated by Google Adwords contain a ?glcid parameter in the Request. Adwords uses this to pass some information to Analytics for traffic analysis.

I was curious, about what data the gclid parameter contained. My guess was that it contained some encoded or encrypted information regarding the origin of the click, so I did some analysis on the clicks that I received. Some discussion about it was available on this post.

I ended up writing a quick PHP script that parses through an Apache log file. It finds requests that contain a gclid and then produces a report of which letters occur in which positions of the gclid.

The script is available for download here, and it generates a report like this:

Found 32507 appropriate lines
Character  1 [ 1] C
Character  2 [ 8] IJKLMNOP
Character  3 [32] -CDGHKLOPSTWX_abefijmnqruvyz2367
Character  4 [64] -CDEFG0ABHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz123456789
Character  5 [32] -_0ghijklmnopqrstuvwxyz123456789
Character  6 [32] -IJKLMNOPYZ_abcdefopqrstuv456789
Character  7 [32] -CDGHKLOPSTWX_abefijmnqruvyz2367
Character  8 [64] -ABCDEFG0HIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz123456789
Character  9 [32] 0-_ghijklmnopqrstuvwxyz123456789
Character 10 [ 4] JZp5
Character 11 [ 8] IMQUYcgk
Character 12 [ 1] C
Character 13 [ 1] F
Character 14 [10] QRSUWYZcde
Character 15 [61] -ABCEFGHIJKLMNOPQRSTUVWXYZ_ab0cdefghiklmnopqrstuvwxy123456789
Character 16 [63] -ABCDEFGHIJKLMNOQRSTUVWXYZ_abcde0fghijklmnopqrstuvwxyz123456789
Character 17 [17] DFGHIQabgiknrsx57
Character 18 [ 4] AQgw
Character 19 [ 1] o
Character 20 [ 1] d
Character 21 [64] -ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwx0yz123456789
Character 22 [32] ABCDEFGHQRSTUVWXghijklmnwyz0x123
Character 23 [64] -ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuv0wxyz123456789
Character 24 [64] -ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrs0tuvwxyz123456789
Character 25 [62] 0-ABCDEFHIJKLMOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz123456789
Character 26 [ 4] AQgw

This makes it clear that the parameter has some structure, but I’m still no closer to determining what it contains. Counting up the unique values, it would seem that they have about 95 bits of information available, which might be enough room to store everything it would need to know about the search that created it. Based on the reporting details in Analytics, I would presume that it somehow contains at least the following information:

  • Campaign (id)
  • Keyword (id)
  • Ad Variation (id)
  • Position

I did some research by clicking an ad multiple times and examining the glcids for those:

        12345678901234567890123456
/?gclid=CNHz5eD_8pkCFRCdnAodzniYQg
/?gclid=CIX_u-X_8pkCFQKenAodlWprSg
/?gclid=CMyI_4OA85kCFRIhnAodc2_oRg
/?gclid=CO_0pYyA85kCFQghnAodDDpaRQ
/?gclid=CIXo9JeA85kCFRIhnAodc2_oRg
/?gclid=CLitgp2A85kCFQubnAod1nx7Qg
/?gclid=CN3_1aOA85kCFQghnAodDDpaRQ
/?gclid=CPyi1quA85kCFRabnAodWnZbRQ
/?gclid=COq-67OA85kCFRMhnAodyQvSRg
/?gclid=COOplrmA85kCFRCdnAodzniYQg

I noticed that most of the characters which use 32-64 characters vary quite a bit except for character #9, which was always an 8, and character #10 which was a ‘p’ for the first two clicks, and then a ’5′ for all subsequent clicks. That likely has some significance, but I’m out of time for playing with it for now.

Hopefully the script and this basic analysis might be of use for somebody else to use in digging into it further.

One other thought that I had is that the data (or each field) is somehow encrypted and when you ‘link’ your Analytics account to your Adwords account it shares the decryption key so that it can get at the detail.

SpamAssassin + Postfix to Deliver Spam to an Alternate Mailbox

Posted on April 10th, 2009 in General,Linux System Administration,Mail,Spam by Brandon

A fairly simple method of having postfix send messages through SpamAssassin, and then delivering the messages identified as spam to a separate mailbox:

Configure SpamAssassin as normal in master.cf:

smtp      inet  n       -       -       -       -       smtpd -o content_filter=spamassassin
2525      inet  n       -       -       -       -       smtpd -o content_filter=spamassassin
spamassassin unix -     n       n       -       -       pipe user=spamd argv=/usr/bin/spamc -f -e    /usr/sbin/sendmail -oi -f ${sender} ${recipient}

Configure Spamassassin to log the status and score in a new header by adding this to /etc/spamassassin/local.cf:

## Add a spamassassin header to all messages so that we can filter them out
add_header all Status _YESNO_, score=_SCORE_ required=_REQD_ tests=_TESTS_ autolearn=_AUTOLEARN_ version=_VERSION_

And then configure postfix to look for that header and deliver it to an alternate mailbox:

/etc/postfix/main.cf:

header_checks = regexp:/etc/postfix/header_checks

/etc/postfix/header_checks:

/^X-Spam-Status: Yes/ REDIRECT spam@yourdomain.com

Now your messages identified as spam will be sent to spam@yourdomain.com so that you can occasionally check for false positives.

Note that there is more work that goes into setting up SpamAssassin, but there are plenty of howtos around for getting that configured.

Upgrading an OpenVZ CentOS 5.2 guest to 5.3

Posted on April 8th, 2009 in General,Linux System Administration by Brandon

Doing a simple ‘yum update’ on a CenOS 5.2 guest server would go through the process of downloading all of the updates, but would only install libgcc and then quit suddenly without any mention of a problem.

With a little bit of experimenting, I came up with a process to get it to upgrade correctly. By updating some of the essential packages first, it somehow convinced the process to work:

# yum upgrade libgcc
# yum update yum
# yum update rpm
# yum update
« Previous PageNext Page »