Easier-to-read MySQL “show table status”

[code lang=”bash”]mysqlshow –status db_name |sort -n -k10 |awk -F\| ‘($6 !~ /0/)’ |awk -F\| ‘{print $2 ” ” $6 ” ” $7 ” ” $14}’ |egrep -v “^ “[/code]

Creates a much easier-to-read view of the output of “show table status”:

Name                    Rows   Avg_row_length   Update_time         
 wp_users                1      140              2009-08-08 04:13:07 
 wp_links                9      106              2009-10-16 12:57:32 
 wp_comments             14     464              2009-11-28 16:09:43 
 wp_usermeta             15     166              2009-11-29 06:41:19 
 wp_term_taxonomy        53     40               2009-11-20 14:06:21 
 wp_postmeta             141    46               2009-11-29 06:44:05 
 wp_options              172    4624             2009-11-29 06:40:59 
 wp_term_relationships   357    21               2009-11-21 02:35:42 

Push email on iPhone and other smartphones… without Exchange

Tonight, I found a clever open-source project entitled Z-Push. This small collection of PHP sits in a web directory and responds to ActiveSync queries — the protocol used for Exchange. It then checks and delivers email.

This is useful because of the limitations of some smartphones — such as the iPhone — wherein Exchange-hosted mail is delivered instantly, while standard POP3 or IMAP mail accounts suffer a long polling delay.

On the server side, configuration is fairly simple:

  1. wget http://download.berlios.de/z-push/z-push-1.3RC.tar.gz
  2. tar xzvf z-push-1.3RC.tar.gz
  3. mv z-push /var/www/html
  4. yum install php-imap
  5. chown apache:apache /var/www/html/z-push/state
  6. vi /var/www/html/z-push/config.php and configure the following:
    define(’IMAP_SERVER’, ‘localhost’);
    define(’IMAP_PORT’, 143);
    define(’IMAP_OPTIONS’, ‘/notls/norsh’);

  8. Add the following Alias to an Apache SSL VirtualHost:
  9. Alias /Microsoft-Server-ActiveSync /var/www/html/z-push/index.php

  10. Restart Apache

On your phone, simply create a new Exchange-type account that points to your server as if it was an Exchange server. Send a test mail and marvel at how fast it appears on your phone! Tested on iPhone and Motorola Droid with excellent success.

Greasemonkey script to make Youtube better

I hacked up a Greasemonkey script tonight to address my very real problem with Youtube: Flash.

I use a Macbook Air and dearly love it, though I’m rather cognizant of the fact that it’s powered by a dying hamster. Playing a video in Youtube will inevitably stutter or cause my lap to catch fire, both undesirable. There are also advertisements and Flash sucks up memory and CPU like crazy.

I attempted to address a different issue before arriving at this solution, namely, Youtube’s new throttling algorithm by. I realized, however, that I’m simply not smart enough to figure that puzzle out, though I learned quite a bit about HTML5 and its <video> tag along the way.

I’d like to point out first that I’m not a JavaScript coder — I’ve actually never used it until tonight, so please don’t judge too harshly. This Greasemonkey script is a mishmash of about 3 other Greasemonkey scripts and some regexes I used in a PHP script.

Install Youtube Flash-No-Mo

Do note that while the script works like a champ on Safari and Chrome (and likely any other webkit-based browser), Firefox 3.5 does not have a built-in handler for mp4 content. You may need to install an additional plugin (mplayer maybe?) to get things to work right on Firefox.

Calculate SMTP and POP3/IMAP bandwidth from qmail logs

[code lang=”bash”](echo “smtp: `(cat maillog maillog.processed && zcat maillog.processed.*) | grep bytes | grep qmail: | awk ‘{sum=sum+$11} END { print sum}’`” && (cat maillog maillog.processed && zcat maillog.processed.*) | grep pop3 | grep LOGOUT | awk ‘{print $13,$14}’ | sed ‘s/,//g;s/….=//g’ | awk ‘{sumrcvd=sumrcvd+$1; sumsent=sumsent+$2} END {print “rcvd: “,sumrcvd,”\n” “sent: “,sumsent}’) | awk ‘{total=total+$2; print} END {print “total: “,total/1024/1024 “MB”}'[/code]

This ugly one-liner comes to us courtesy Chuck. Plesk calculates bandwidth statistics by literally reading the raw log files and performing math based on the byte totals noted in the log entries. This beast will run against the Plesk maillogs and give you a pretty summary of mail bandwidth:

[code]smtp: 397852373
rcvd: 228219
sent: 211813204
total: 581.64MB[/code]

Rewriting URLs based on domain name

While Apache’s mod_rewrite is fairly powerful, it does have some limitations (mostly to not break the HTTP spec). While you can specify any number of esoteric conditions, it’s not possible to rewrite anything but the URI. If you want to redirect sillydomaintwo.com to two.sillydomain.com/blog, you’ll have to use an intermediary processor like PHP.

[code lang=”PHP”]

Setting this code as the index.php for sillydomaintwo.com will suffice, as it will perform a 301 redirect to the target location.

In Apache, the simplest way to support this sort of configuration is to have the master sillydomain.com as its own VirtualHost with a ServerAlias of *.sillydomain.com. In another VirtualHost, set the ServerName as * with the above index.php in its DocumentRoot sillydomaintwo.com won’t match the first, but will match the * (catch-all) VirtualHost, run the index.php, then redirect to the first VirtualHost:

<VirtualHost *:80>
ServerName sillydomain.com
ServerAlias *.sillydomain.com
DocumentRoot /var/www/sillydomain.com

<VirtualHost *:80>
ServerName *
DocumentRoot /var/www/sillydomain-redirect
#in this directory is the index.php with the redirection as described above

Enable WebDAV with Plesk

Configuring WebDAV in Apache is simple, but it’s even easier to configure and manage with Plesk!

1. Create a Protected Directory
Log into Plesk and select the domain that is to receive the DAV repository. Click on “Protected Directories” and create a new one – name it as the DAV share will be named, for they are one and the same.

2. Configure WebDAV Users
Add users who should have access to this DAV repo.

3. Edit vhost.conf and Reconfigure Plesk
On the server, edit the domain’s vhost.conf and enter the following:

[code]<Directory “/var/www/vhosts/domain.com/httpdocs/DAVdir”>
DAV on
AllowOverride None

Regenerate Apache’s configuration and you’re golden:

[code]/usr/local/psa/admin/bin/websrvmng -av[/code]

4. Test
You can easily test DAV configuration by using a DAV client such as `cadaver’.

[code][kale@superhappykittymeow ~]$ cadaver http://www.domain.com/DAVdir
Authentication required for on server `domain.com’:
Username: kale
dav:/DAVDir/> ls
Listing collection `/DAVDir/’: collection is empty.[/code]

Success! You can manage access to the DAV share through the Plesk interface.

mod_auth_mysql and segfaults

Symptom: seemingly random PHP scripts are causing Apache to segfault.

Looking deeper: all the PHP scripts that are causing segfaults make database queries (specifically, MySQL).

Look even closer: the following line is in your Apache configuration:

[code]LoadModule auth_mod_mysql modules/mod_auth_mysql.so[/code]

Solution: comment that line out of your Apache configuration and restart Apache.

Why: If the PHP code is run through Apache, you’ve essentially got one process making the SQL queries (if your PHP code makes it so). However, while your code made the connection and is expecting responses and whatnot, Apache, with mod_auth_mysql loaded, is ready and willing to make and take database connections. When a connection that returns a response is made from your PHP code, Apache will attempt to accept the response and handle it itself, instead of passing it to PHP. Since Apache is not expecting the data it’s getting, it has no error handling code for this situation and simply segfaults.

Disable mod_auth_mysql by commenting it out and everything will work without issue.

Apache MultiViews and RewriteRules

Don’t work together.

I think it’s a bug in mod_rewrite, to be honest, though more of a “not thinking these two modules would ever be used together” kind of oversight, rather than a full bug.

Essentially, if you are using MultiViews to make for pretty URLs (say, http://www.foo.com/bar, where ‘bar’ doesn’t exist, but instead loads the content from bar.php), and you attempt to implement RewriteRules to modify the URL, you will see erratic results.

If, for example, you have a RewriteRule as follows:

[code]RewriteCond %{HTTP_HOST} !^www\.foo\.com
RewriteRule (.*) http://www.foo.com/$1 [R=301,L][/code]

which, essentially, takes all non-WWW requests and makes them www.foo.com, you will find that MultiView URLs will be redirected to their real resources if the URL matches a rule. For example,


will become


after going through the MultiView filter and the RewriteRules. This is due to the way the rules work — essentially, the request will be parsed through mod_rewrite to find a match. If no match against the URL, the MultiView is processed to get the real resource which is then presented to the end user. If a match is made, however, mod_rewrite has mod_negotiation process the MultiView to find the real resource so it can properly do the rewrite — it is never changed back, however, to the pretty MultiView URL. If your goal is pretty URLs without any effort expended, relying on MultiView, you will find that RewriteRules are your nemesis.

There are a few routes available to get around this odd behavior, but my favorite (and easiest to implement) is to move the RewriteRule logic to the site code. It’s much harder to implement MultiView-esque functionality than it is to re-implement RewriteRules.

To implement the above RewriteRule, redirecting non-www to www, simply add an auto_prepend_file to your .htaccess in lieu of the RewriteRule as such:

[code]php_value auto_prepend_file “/var/www/html/prepend.php” [/code]

This file contains simply:

[code lang=”php”][/code]

With this code prepended to every PHP script (assuming your site is written in PHP, of course), all non-www requests will be redirected to www — *after* the MultiView is processed and not interfering with its inner workings.

Auto-iptables off IPs with high connection counts

via Paul (lovepig.org):

[code lang=”bash”]netstat -npa –inet | grep :80 | sed ‘s/:/ /g’ | awk ‘{print $6}’ | sort | uniq -c | sort -n | while read line; do one=`echo $line | awk ‘{print $1}’`; two=`echo $line | awk ‘{print $2}’`; if [ $one -gt 100 ];
then iptables -I INPUT -s $two -j DROP; fi; done; iptables-save | grep -P ‘^-A INPUT’ | sort | uniq -c | sort -n | while read line; do oneIp=`echo $line | awk ‘{print $1}’`; twoIp=`echo $line | awk ‘{print $5}’`; if [ $oneIp -gt 1 ]; then iptables -D INPUT -s $twoIp -j DROP; fi; done[/code]

This one-liner is quite effective when tossed into a file and run as a cronjob once per minute. Any IP with more than 100 concurrent connections — which, quite honestly, is far more than any one IP should ever have on a standard webserver — will be blocked via iptables. This script as a cronjob is extremely effective dealing with small-to-midsize DDoSes (too much traffic for Apache/whatever service to handle, but not saturating the pipe).

Email alerts on new virus with Sophos

Sophos’s Linux antivirus product is an interesting beast, but I’ll reserve opinion. We offer a web interface wherein the end-user may review alerts, though some also wish an email alert. This can be configured through savwebd, the web GUI provided with the Sophos antivirus client, or configured on the command line:

[code lang=”bash”]cd /opt/sophos-av/bin
./savconfig -v # review current configuration settings
./savconfig set Email email@address.com # recipient
./savconfig set EmailNotifier true
./savconfig set EmailDemandSummaryIfThreat true
./savconfig set EmailServer localhost
./savconfig set SendThreatEmail true
./savconfig set ThreatMessage “A virus has been detected and blocked. Please contact your support team for more information.”

Enable core dumps with apache, RHEL5

From this post on Jared’s tech blog:

[code lang=”bash”]echo “ulimit -c unlimited >/dev/null 2>&1” >> /etc/profile
echo “DAEMON_COREFILE_LIMIT=’unlimited'” >> /etc/sysconfig/init
echo 1 > /proc/sys/fs/suid_dumpable
echo “core.%p” > /proc/sys/kernel/core_pattern
echo “CoreDumpDirectory /var/apache-core-dumps” > \
mkdir /var/apache-core-dumps
chown apache: /var/apache-core-dumps
source /etc/profile
/etc/init.d/httpd restart[/code]

Now you can test it by sending a SIGSEGV to a random apache child process:

[code lang=”bash”]tail -f /var/log/httpd/error_log | grep -i seg &
ps auxwww |grep httpd (pick a random pid not owned by root)
kill -11 2014
[Mon Jul 06 21:05:39 2009] [notice] child pid 2014 exit signal
Segmentation fault (11), possible coredump in /var/apache-core-dumps
cd /var/apache-core-dumps

You can then get a backtrace using gdb:

[code lang=”bash”]gdb /usr/sbin/httpd core.2014
(gdb) > bt full[/code]

Brilliant – thanks Jared, I fought Apache for an hour to enable CoreDumps before putting my fist through the monitor!

Mount NTFS drive in RHEL5

Grab fuse, fuse-ntfs-3g and dkms-fuse from Dag’s repo:

[code lang=”bash”]wget http://www.mirrorservice.org/sites/apt.sw.be/redhat/el5/en/x86_64/rpmforge/RPMS/fuse-2.7.3-1.el5.rf.x86_64.rpm
wget http://www.mirrorservice.org/sites/apt.sw.be/redhat/el5/en/x86_64/rpmforge/RPMS/fuse-ntfs-3g-1.2310-1.el5.rf.x86_64.rpm
wget http://www.mirrorservice.org/sites/apt.sw.be/redhat/el5/en/x86_64/rpmforge/RPMS/dkms-fuse-2.7.2-1.nodist.rf.noarch.rpm[/code]


[code lang=”bash”]rpm -Uvh fuse-2.7.3-1.el5.rf.x86_64.rpm fuse-ntfs-3g-1.2310-1.el5.rf.x86_64.rpm dkms-fuse-2.7.2-1.nodist.rf.noarch.rpm[/code]


[code lang=”bash”]mount.ntfs-3g /dev/sdc1 /mnt/usb/ -o force[/code]

Add a new hotswap SCSI drive

If you’ve got a SCSI card, SCA backplane, and a chassis that supports hotswappable drives, you can easily add a new drive without a reboot. Simply insert the drive and

[code lang=”bash”]cat /proc/scsi/scsi[/code]

Note the host, bus (channel), ID and LUN of the existing drive(s).

[code lang=”bash”]echo “scsi add-single-device h b i l” > /proc/scsi/scsi[/code]

where “h b i l” is the host, bus, id and lun of the new drive. If it’s going on the same adapter as the rest of the drive(s) listed already, use the same host, bus and lun — the id will be incremented by one:

[code lang=”bash”]echo “scsi add-single-device 1 0 1 0” > /proc/scsi/scsi[/code]

cat /proc/scsi/scsi and you should see the new device:

[code][root@foo scsi]# cat /proc/scsi/scsi
Attached devices:
Host: scsi1 Channel: 00 Id: 00 Lun: 00
Vendor: HITACHI Model: HUS103073FL3800 Rev: SA1B
Type: Direct-Access ANSI SCSI revision: 03
Host: scsi1 Channel: 00 Id: 01 Lun: 00
Vendor: SEAGATE Model: ST3146707LC Rev: 0005
Type: Direct-Access ANSI SCSI revision: 03[/code]

fdisk and make a filesystem as usual!

Obtaining Plesk user for a domain

…for a list of domains, without digging through the database!

[code lang=”bash”]cat domains | sort |uniq |while read line ; do ls -ld /home/httpd/vhosts/$line/httpdocs |awk ‘{print $3}'[/code]

‘domains’, of course, is a text file with a list of domains hosted on the server. Can be populated in whatever way you need. Easily plugged into other Plesk utilities (such as changing Plesk FTP passwords).