Basic MySQL 'top' command

Ever wanted to keep an eye on the running processes in a MySQL database? How about something that works a little like the top command, but without any of the bells and whistles?

Well, here you go:

> while [[ 1 ]];
do
 clear;
 mysql -u <username> -p<password> -h <host> -e "show full processlist";
 sleep 1;
done

Remember to replace the <username> <password> and <host> variables with the values for your database. Also, if you don't like the bounding box on the mysql output, you can have a cleaner output by using redirection instead of the -e flag:

mysql -u <username> -p<password> -h <host> <<< "show full processlist";

Case insensitive regex in bash

By default regexes in [[ ]] are case sensitive. If you want to match in a case insensitive way you have to set the shell option nocasematch.

mytext="eXistenZ"
if [[ $mytext =~ existenz ]];
then 
 echo "yep"
else
 echo "nope"
fi

If you run the above script you should get "nope" as the output. For case insensitive matching just insert this into the script prior to the regex:

shopt -s nocasematch;

You can unset the nocasematch shell option using the following: shopt -u nocasematch

Here's a more complete example:

mytext="eXistenZ"
function testText
{
 if [[ $mytext =~ existenz ]];
 then 
  echo "yep"
 else
  echo "nope"
 fi;
}
testText
shopt -s nocasematch
testText

If you run that script then the output is:

nope
yep


R related links

These are just a few R links I think are interesting and worth a further read:

1. Colour palettes in R.


2. Using R for introductory statistics


3. Web-friendly visualisations in R


4. A different way to view probability densities


5. Thoughts on Making Data Work

Also of note is Google's announcement (on June 2nd) that they were working with the USPTO to make all granted patents, trademarks and published applications freely available for download - see this post.

I love subshells

Sometimes it's the little things in life that give us the greatest pleasures. I love subshells. There, I admitted it.

Say you want to do something with the output of a program but would like to prepend some output/text that also needs to be operated on. The standard idiom would be to create a file with the prepended output, append your output to this and then operate on the file, but you can remove the intermediate files using a subshell. Here's a contrived example:

> (echo -e "some\toutput\tto\tappend"; perl -lane 'print join("\t", log($F[0])/log(10), @F[1 .. #$F])' ;) | a2ps

Something else I use subshells for a lot is to launch programs in a different directory without having to cd there and then back again:

> (cd ~/workspace/; eclipse &)

like I said; it's the little things.

delete all empty files in a directory

Automatically generating files? Annoyed at all the empty ones? Here's how to purge them:

> for file in $( ls . ); do if [ ! -s $file ]; then rm -f $file; fi; done

Of course you could just get over your fear of find and take a look at that man page. Perfect for a recursive search and delete all in one:

> find . -empty -delete

simple, no?

escaping quotes in bash variables

I often need to escape quotation characters or other special characters that are being piped into a bash process:

Say you have a mysql table which contains protein names. Some of these have the ' character in them (e.g. "Inosine 5' monophosphate dehydrogenase") and you want to do some bulk processing on these names, you could do something like this:

> mysql -u paulbo -N <<< "SELECT name from proteins" | while read protein_name; do mysql -u paulbo -N "SELECT count(*) FROM data INNER JOIN proteins ON data.protein_id = proteins.protein_id where proteins.name = '${protein_name//\'/\\\'}'"; done

Ah, three backslashes. Why didn't I think of that?

And sometimes you just want to output the text with the special characters all converted into something a bit more amenable (like the old and trusted '_').

> mysql -u paulbo -N <<< "SELECT name from proteins" | while read protein_name; do echo ${protein_name//[-\/\:\'\"\(\) ]/_};done

Excluding certain files from a directory listing

Ok, well, this will be obvious to anyone who's really read the ls man page, but I only came across it a couple of days ago.

Say you have a directory with tons of files in it mostly with a single extension and you want to see what else is in there. Sure, you can use grep, but you can also use ls's inbuilt flat --hide.

> ls -l
arg.txt
blarg.txt
foo.txt
...
interesting.sh
... # and a ton more *.txt files
zlargyblorg.txt
> ls -l --hide=*.txt
interesting.sh
hidden.csv

Isn't that useful?