Use CASE statement in SELECT in MySQL

Sometimes you might want to replace numbers with an enumerate value.  Let’s take an example with a BIT field for HasChildren.

SELECT Name, IsParent
CASE
WHEN HasChildren=1 THEN ‘Parent’
WHEN HasChildren=0 THEN ‘Not parent’
ELSE ‘Unknown’ END AS IsParent
FROM Person

Get source code from GitHub with git (read-only)

If you want to get source code from GitHub it’s easy enough to click ‘Download’ if you just want an archive.  However if you’re on GitHub you might want to use the proper git client.

Get git if you haven’t already:

http://git-scm.com/

Once that’s installed just run the following from a command line:

git clone git://github.com/username/project.git

To get the connect-auth source tree use:

git clone git://github.com/ciaranj/connect-auth.git

Enjoy your new source.

Building Pidgin on Ubuntu linux

To build pidgin on Ubuntu linux you’re going to need a whole bunch of libs and tools.  Let’s check you’ve got the base dev tools installed:

sudo apt-get install build-essential

Next the glib stuff and libxml:

sudo apt-get install libgtk2.0-dev

sudo apt-get install libxml2-dev

I’m not sure if you need this separately, however libpurple is the plugin system for Pidgin:

sudo apt-get install libpurple-dev

Finally mop up any specific pidgin dependencies that are missing:

sudo apt-get build-dep pidgin

If you haven’t already downloaded the Pidgin source get it here:

http://www.pidgin.im/download/source/

Uncompress it:

tar zxvf pidgin-sipe-1.11.2.tar.gz # change for your version

cd into the directory, then run:

./configure

make

make install

Enjoy!

Remove ^M with vi AKA bad interpreter: No such file or directory

Got a script with ^M at the end of each line because the file came from a dos machine?  With perl you’ll get something like:

/usr/bin/perl^M: bad interpreter: No such file or directory

First of all open this file in vi in binary mode:

vi -b yourscipt.file

Then after typing colon type the following:

%s/^M//g

NOTE. To get the ^M do NOT use ^ and M.  Use control+V then immediately control+M.

Then [enter].  Then to save and exit:

:wq [enter]

Job done!

MySQL [lack of] performance

I certainly don’t profess to be a MySQL performance tuning expert. What I am is a long-time MySQL lack of performance sufferer. In fact, writing this I’m in the process of waiting for several table deletes and MySQL WorkBench has frozen (again).

Anyway, here’s some thoughts:

  1. If you have heavy writes bear in mind this severely affects locking for MyISAM tables.  One thing that sucks with MySQL table engines is you trade off performance (MyISAM) against granular locking (InnoDB).
  2. Try to avoid tables getting too large to manage.  If you’re storing historical data write an archive / cull script early on to keep the database manageable.
  3. Use SHOW PROCESSLIST to see what’s running if you’re experiencing problems.  Processes marked ‘locked’ or ‘freeing items’ are bad.
  4. If you get into trouble and need to delete all the rows in a table, use TRUNCATE TABLE [tblname] rather than DELETE FROM.  This is significantly quicker.  (Thanks Rainer for that one.)
  5. Most obviously – use indexes judiciously.  Adding indexes slows down insert speed to allow better retrieval rate.  If the tables are too index heavy the insert speed will slow down such that it causes more locking.

MySQL SELECT equivalent of SHOW PROCESSLIST

If you want the ability to sort or filter the SHOW FULL PROCESSLIST command, here’s the SQL equivalent:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST

Regex to trim number to two decimal places

Not very generic, but at least easy to understand:

m/^(.+?\.[0-9]{2})/;

123.456789 becomes 123.45

Line-breaking long words without spaces

Often I’ll have a problem where very long strings of text (URLs for example) break my formatting. Wouldn’t it be nice if there was a way to split them over lines even if the characters weren’t line breaking?
I came across this post that shows a hidden characters that act as breaking characters:

http://www.robinlionheart.com/stds/html4/spchars

Here’s an example using ​repeated several times with the alphabet:

ABCDE​FGHIJ​KLMNO​
PQRST​UVWXYZ​​

ABCDE​FGHIJ​KLMNO​PQRST​UVWXYZ​ABCDE​FGHIJ​KLMNO​PQRST​UVWXYZ​ABCDE​FGHIJ​KLMNO​PQRST​UVWXYZ​ABCDE​FGHIJ​KLMNO​PQRST​UVWXYZ​ABCDE​FGHIJ​KLMNO​PQRST​UVWXYZ​ABCDE​FGHIJ​KLMNO​PQRST​UVWXYZ​ABCDE​FGHIJ​KLMNO​PQRST​UVWXYZ​ABCDE​FGHIJ​KLMNO​PQRST​UVWXYZ​

Fedora 15 / Gnome 3 / Gnome Shell: Where is the system tray?!

After using Fedora 15 for a few hours by sheer luck I managed to find the system tray:

Move the mouse to the bottom right corner of the screen.
Seems a bit odd to have icons that you traditionally want shown all the time hidden away…?

Fedora 15 / Gnome 3 doesn’t show Gnome Shell

Today I’ve been installing Fedora 15 as an upgrade to Fedora 14 on my Eee 1015PEM.

Two issues I had:

1) When rebooting following the preupgrade install (where the files are downloaded whilst the system is in use) the system wasn’t able to mount /tmp with the following error:

“An error occurred mounting device tmpfs as /tmp: mount failed (-2, None)”

Luckily someone came across the same issue in the F15 beta and logged the issue here:

https://bugzilla.redhat.com/show_bug.cgi?id=683434

Simple answer: comment out the tmpfs lines in /etc/fstab prior to install. DON’T try rebooting with the old OS as you’ll find X and its apps may struggle to start. If this happens and you can’t get into an X terminal window try CTRL+ALT+F2-F7, which switches consoles.

2) Having installed and rebooted I was informed that Gnome Shell (the fancy new UI) wouldn’t start as my hardware wasn’t capable of it. Having panicked and searched for drivers for my Intel GMA 3150 chipset I was sinking into my mire until I came across this post:

http://osdir.com/ml/fedora-test-list/2011-05/msg00290.html

suggesting running:

gnome-shell –replace & disown

I didn’t hold much hope other than causing more damage but having run the command the UI changed instantly into the new mode. I left it for several minutes and the UI seemed to freeze. A hard reboot saw the UI come back to life and so far it’s been working fine.

Note. you need ‘& disown’ as otherwise the gnome-shell process will die if you close the terminal window, causing Gnome to freeze.