I’ve written a small shell script to report how much disk space each table in a given MySQL database is using. For example, below is the output of the script when run against this site’s database:

bmd /~: ./dbSize.sh coderholic root ********
wp_comments Data: 6.60MB Indexes: .15MB Total: 6.75MB
wp_links Data: 0MB Indexes: 0MB Total: 0MB
wp_options Data: 1.57MB Indexes: .01MB Total: 1.58MB
wp_postmeta Data: .01MB Indexes: .01MB Total: .02MB
wp_posts Data: .57MB Indexes: .02MB Total: .60MB
wp_term_relationships Data: 0MB Indexes: .01MB Total: .02MB
wp_term_taxonomy Data: 0MB Indexes: 0MB Total: 0MB
wp_terms Data: 0MB Indexes: 0MB Total: 0MB
wp_tla_data Data: 0MB Indexes: 0MB Total: 0MB
wp_tla_rss_map Data: 0MB Indexes: 0MB Total: 0MB
wp_usermeta Data: 0MB Indexes: 0MB Total: 0MB
wp_users Data: 0MB Indexes: 0MB Total: 0MB
*** 12 Tables | Data: 8.78MB Indexes: .25MB Total: 9.03MB ***

With a few small modifications I’m sure it’d be possible to get the script working PostgreSQL or other RDBMSes. The full code is below:

#!/bin/bash
# Calculate the storage space used up by all tables in a given MySQL database
# Ben Dowling - www.coderholic.com
database=$1
username=$2
password=$3

if [ ${#database} -eq 0 ]
then
	echo "Usage: $0 <database> [username [password]]"
	exit
fi

if [ "$password" ]
then
   password="-p$password"
fi

mysql="mysql -u $username $password $database"

$mysql -se "USE $database";

tables=$($mysql -se "SHOW TABLES")

totalData=0
totalIndex=0
totalTables=0

for table in $tables
do
   output=$($mysql -se "SHOW TABLE STATUS LIKE \"$table\"\G")
   data=$(echo "$output" | grep Data_length | awk -F': ' '{print $2}')
   dataMegs=$(echo "scale=2;$data/1048576" | bc)
   index=$(echo "$output" | grep Index_length | awk -F': ' '{print $2}')
   indexMegs=$(echo "scale=2;$index/1048576" | bc)
   total=$(($index+$data))
   totalMegs=$(echo "scale=2;$total/1048576" | bc)

   echo "$table Data: ${dataMegs}MB Indexes: ${indexMegs}MB Total: ${totalMegs}MB"

   totalData=$(($totalData+$data))
   totalIndex=$(($totalIndex+$index))
   totalTables=$(($totalTables+1))
done

dataMegs=$(echo "scale=2;$totalData/1048576" | bc)
indexMegs=$(echo "scale=2;$totalIndex/1048576" | bc)
total=$(($totalIndex+$totalData))
totalMegs=$(echo "scale=2;$total/1048576" | bc)

echo "*** $totalTables Tables | Data: ${dataMegs}MB Indexes: ${indexMegs}MB Total: ${totalMegs}MB ***"

I often use the linux watch command to monitor the status of certain commands. When I’m copying lots of files say, I’d watch the files in the target directory to see what files have already been copied across with the following command:

watch ls -l

The watch program clears the screen and displays the output of “ls -l” every 2 seconds.

Sometimes I’ll want to monitor a command that only outputs a single line. If I wanted to see the total number of files in a directory rather than the files themselves I could use the command “ls -l | wc -l”. The fact that watch clears the whole screen can be a little annoying here though, because the command is only outputting a single line. That is why I came up with the following small bash script, linewatch.

Linewatch repeatedly calls any arguments passed to it every 2 seconds (in the same way watch does), but only clears a single line rather than the whole screen. Here is the code:

#!/bin/bash
clearline="\b\033[2K\r"
command=$@

while true
do
    eval "$command"
    sleep 2
    echo -n -e "$clearline"
done

And here is an example of how to call it:

$ ./linewatch "ls -l | wc -l"
24

The number of files in the current directory (24 in the example) will keep update every 2 seconds. Just hit Ctrl-C when you want to quit,

I came up with the following shell script recently to monitor code changes in a subversion repository. On the first run it will emails out the 10 most recent changes. After that the script mails out all changes since the last time it was run. You can set it up to run as a daily cron job which mails you all changes made to you favourite open source project!

It wouldn’t take much to get it working with other version control systems such as Git or Bazaar, or to do some nice formatting of the output instead of outputting the raw svn log as-is. Let me know if you find it useful!

#!/bin/bash
# Shell script to email the latest changes in an SVN
# repsitory to a specified email address.
# Ben Dowling - wwww.coderholic.com

svnUrl="http://anonsvn.wireshark.org/wireshark/trunk/"
lastRevisionFile="./.last-revision"
mailto="ben@coderholic.com"

function getCurrentRevision {
  # Get the current SVN revision, eg. "r4670"
  currentRevision=$(svn log "$svnUrl" -r HEAD 2>/dev/null | head -n2 | grep -v -- "-------" | awk '{ print $1 }')
  # Strip off the 'r'
  currentRevision="${currentRevision:1}"
  echo "$currentRevision"
}

currentRevision=$(getCurrentRevision)

# If we've run this program before then we've stored the SVN revision at the time
if [ -f "$lastRevisionFile" ]
then
  lastRevision=$(cat "$lastRevisionFile")
  #  Check what the current revision is, and exit if there
  # haven't been any changes since we last checked
  if [ $currentRevision -lt $lastRevision ]
  then
      echo "No changes since last check"
      exit
  fi
else
  # We haven't run this program before, so set the last revision to the current revision - 10
  lastRevision=$(echo "$currentRevision - 10" | bc)
fi

# Mail the SVN changes
svn log "$svnUrl" -r "HEAD:${lastRevision}" | mail -s "SVN changes for $svnUrl" $mailto

# Store the current revision + 1 as the last revision
revision=$(echo "$currentRevision + 1" | bc)
echo "$revision" > "$lastRevisionFile"

With the configuration option “log_errors” PHP will log all errors to the Apache error log file, allowing you to see all of the errors that have occurred while people have been using your site. Given the snippet of code below:

<?php
	// Ensure logging is enabled. Ideally this will be set in php.ini
	ini_set('log_errors', 'On');
	// Call a non-existant function to generate an error
	doesNotExist();
?>

You’ll see something like the following entry in the error log, which includes a description of the problem and the file and line number of where the problem occurred:


[Tue May 26 19:54:53 2009] [error] [client 127.0.0.1] PHP Fatal error: Call to undefined function doesNotExist() in /var/www/index.php on line 6


I’ve written a small bash script to mail me all of the PHP errors that it finds in the log. The results are sorted and piped through the “uniq” command to remove any duplicate errors. I’ve set it up to run as a cron task once a day, so if there are ever any problems it isn’t long before I know about them. The code is below:

#!/bin/bash
# Mail out PHP errors that are in the apache error log.
# Note PHP's log_errors must be turned on
# Ben Dowling - www.coderholic.com

errorLog=/var/log/apache2/error.log # Error log location
email=you-email-address@example.com # Send report here

# Pull out the lines that mention PHP, and use AWK to get the column we're interested in
errors=$(cat $errorLog | grep PHP | awk -F'] ' '{print $4}')
# Remove referer information, sort, and remove duplicate entries
errors=$(echo "$errors" | awk -F', referer' '{print $1}' | sort | uniq)
# Check that we actually have some errors
if [ -n "$errors" ]
then
	echo "$errors" | mail "$email" -s "PHP Errors"
fi

If you need to it should be quite easy to modify the script for a slightly different use, such as printing out all the fatal PHP errors that are in the log.

Hope you find it useful!

Inspired by a movable tripe blog post I came up with the following shell script to convert an AVI file to an ISO image that can be burned to disc and played back on a standard DVD player.

The script requires mencoder, ffmpeg, dvdauthor and mkisofs, and will let you know you if any of these are missing. Use it like follows:

./dvd.sh input.avi

Once the script has finished a “dvd.iso” file will be created, which can then be burned to DVD using your favourite disc burning tool.

#!/bin/bash
# AVI to DVD Script
# Ben Dowling - www.coderholic.com

# Change to "ntsc" if you'd like to create NTSC disks
format="pal"

# Check we have enough command line arguments
if [ $# != 1 ]
then
	echo "Usage: $0 <input file>"
	exit
fi

# Check for dependencies
missing=0
dependencies=( "mencoder" "ffmpeg" "dvdauthor" "mkisofs" )
for command in ${dependencies[@]}
do
	if ! command -v $command &>/dev/null
	then
		echo "$command not found"
		missing=1
	fi
done

if [ $missing = 1 ]
then
	echo "Please install the missing applications and try again"
	exit
fi

function emphasise() {
	echo ""
	echo "********** $1 **********"
	echo ""
}

# Check the file exists
input_file=$1
if [ ! -e $input_file ]
then
	echo "Input file not found"
	exit
fi

emphasise "Converting AVI to MPG"

ffmpeg -i finalmovie.avi -y -target ${format}-dvd -sameq -aspect 16:9 finalmovie.mpg

if [ $? != 0 ]
then
	emphasise "Conversion failed"
	exit
fi

emphasise "Creating DVD contents"

dvdauthor --title -o dvd -f finalmovie.mpg
first=$?
dvdauthor -o dvd -T
second=$?

if [ $first != 0 || $second != 0 ]
then
	emphasise "DVD Creation failed"
	exit
fi

emphasise "Creating ISO image"

mkisofs -dvd-video -o dvd.iso dvd/

if [ $? != 0 ]
then
	emphasise "ISO Creation failed"
	exit
fi

# Everything passed. Cleanup
rm -f finalmovie.mpg
rm -rf dvd/

emphasise "Success: dvd.iso image created"