Ben Hamilton

*nix, SQL

How long running are those queries?

At the MySQL commandline, you can enter
show processlist;
and see what processes are running.
If you’re tracking down some problematic code and you’ve got a query (or set of queries) that don’t end, consume CPU cycles and generally bog everything down to a crawl, it can be helpful to know what time you’ve run the command above. Thusly, I now run this:

show processlist; select now as current_date_time;

and it rather nicely tells me the current date/time on the MySQL server, using the Time column from the processlist, you can work out a tad more accurately when a query started, giving you a starting point to look in other logs.
Now those long running sql queries are easier to track down.

| Tagged , | Comments Off on How long running are those queries?

Hotkey for New Day One entry from selection

I use Day One to journal. There is a service that creates a new Day One entry from the selection, but it didn’t have a hotkey associated with it. So I’ve set one up.

First (optional) step is to use to make your CAPS LOCK key useful. Big thanks to Brett Terpstra for that, I use that for loads of other shortcuts.

Next step is to add a shortcut for the New Day One Entry With Selection using the instructions to create keyboard shortcuts in the Apple knowledge base.

I used CAPSLOCK D as the hotkey to send whatever I’ve got currently selected to Day One as a new entry, works a treat.

Note: this is ^⇧⌥⌘D using the optional first step, so it is the same as pressing Control, Shift, Option, Command, D all at the same time.

| Tagged , | Comments Off on Hotkey for New Day One entry from selection

Encrypt and decrypt .zip files on Mac OS X

I just needed to compress and encrypt some files on Mac OS X. The following command does that:
This will ask for a password (twice to confirm it), encrypt and compress the sourcefolder and put it into a file named target (and auto add the .zip extension).

To unzip it do this:
This will ask for the password, decrypt and uncompress it into the targetfolder.

| Tagged , , | Comments Off on Encrypt and decrypt .zip files on Mac OS X

VPS Hosting Deal 20% off

This is a time limited deal, if you go to and sign up, use the coupon code ‘benhamilton’ to get 20% off your order.

I have a VPS hosted with Cloudatcost myself and I know a few others that do as well, so I can vouch for their service.

| Tagged , , , | Comments Off on VPS Hosting Deal 20% off
How to

How to get Hazel to run rules in subfolders

This article here describes how to get Hazel to run rules in sub folders, very handy. I’m using this to move any screenshots taken on the iPhone out of the photo folder into a separate folder.

| Tagged , , | Comments Off on How to get Hazel to run rules in subfolders

Advanced Bash-Scripting Guide

Advanced Bash-Scripting Guide. The name says it all.

| Tagged , , | Comments Off on Advanced Bash-Scripting Guide
How to, SugarCRM

Allow a Checkbox to be Mass Update-able in SugarCRM

Here’s how to enable a checkbox to be updated enmasse.

First create a new extvardef.php file containing the following:

Then put that file into at the following location


Of course, you’d want to check any existing files in the folder to make sure none of them already deal with that field.

| Tagged , , , | Comments Off on Allow a Checkbox to be Mass Update-able in SugarCRM
*nix, SQL

How to backup SugarCRM from the command line

From the linux command line, these commands let you backup and restore a SugarCRM database.

Update 2016-04-26: Just saw this SugarCRM KB Providing a Backup Without Sensitive Data which is really useful. Thanks Jared.

Firstly, for a proper back up of SugarCRM you’ll need two files, one containing the application files, one containing the SQL database.

First up, the backup…

Backup files:

Change to the folder you want to backup, then…

tar -zcvf CRM-BACKUP-FILES.tar.gz .

Edit: if you get an error ‘Permission Denied’ you may be trying to write to a folder you don’t have permission for, instead try writing to ~/CRM-BACKUP-FILES.tar.gz and it will likely work.
Edit: see also this StackOverflow article.

Backup sql (empty copy of the database):

mysqldump -u USERNAME -p -–no-data DATABASENAME > CRM-BACKUP-SQL.sql

Backup sql (with the data) :


Then you can ‘tar’ the .sql file with

tar -zcvf DATABASENAME-mysql.tar.gz DATABASENAME-mysql.sql

Backup just a single table

mysqldump db_name table_name | gzip > table_name.sql.gz

If you only want the database schema, then in the SugarCRM web application you can do the following:

  • Admin
  • Diagnostic Tool
  • db schema
  • Download that file

Then for the restoration…

Restore files (to current folder):

tar -zxvf CRM-BACKUP-FILES.tar.gz

Restore sql:


Restore just a single table

gunzip < table_name.sql.gz | mysql -u username -p db_name

These SugarCRM Knowledge Base articles may also be of use:

| Tagged , , , , | Comments Off on How to backup SugarCRM from the command line
*nix, How to

Linux Load Averages

For when you’re trying to explain what the load average figure means (which you get from running either top or w from the linux command line) this link will help explain it easily. Easier than spending 10 minutes talking about it.

| Tagged , | Comments Off on Linux Load Averages

Faster Numbering of OmniFocus Tasks

I wrote up how I’m numbering tasks in OmniFocus, but I’ve now modified the Keyboard Maestro recipe so it’s just a touch faster.

Here’s a screenshot:

Updated, faster recipe for numbering tasks in OmniFocus

Updated, faster recipe for numbering tasks in OmniFocus

I’ve also since created a separate recipe in Keyboard Maestro for each key Project I have in OmniFocus, each with it’s own .txt file that it refers to. This lets me do


and it’ll spit out “(Admin #7)” or if I type


it’ll spit out “(CRM #15)”.

| Tagged , , , , | 1 Comment
How to, Productivity

Numbering tasks in OmniFocus 2 for Mac OS X

I’m using OmniFocus 2 on my MacBook Pro and wanting to ‘number’ tasks because at the end of each week, I print a PDF of the tasks completed and outstanding for a particular client. Task numbers make it much easier for the client to reconcile the work I’ve done with the lists they use.

The problem is that OmniFocus doesn’t have a ‘task number’ function.

Keyboard Maestro and a shell script to the rescue. This allows me to get the next sequential task number. Now all I do is type


and it expands out to the next available task number.

In a nutshell I use the shell script to get a variable from a text file (which is the last task number I used), and increment it. Keyboard Maestro is wrapped around this to both trigger it and make it look pretty.

The shell script itself is quite basic, any improvements you can suggest, please do.

Here is a screenshot of the Keyboard Maestro recipe (edit: I’ve since modified this recipe to be a little faster)

Keyboard Maestro recipe to increment task number

Get last task number, increment it, save it.

This relies on you having a text file named


in your home folder.

The format of this file is just a single line of text as follows:


| Tagged , , , | 1 Comment

Find Duplicate Contacts in SugarCRM

Find duplicate contact records, by contact name, in SugarCRM using MySQL.

| Tagged , , | Comments Off on Find Duplicate Contacts in SugarCRM

SQL Query ORDER BY last_name, then first_name

This snippet of MySQL code shows how to concatenate two fields into one column, also shows how to reverse the concatenation and order by last name then first name.

| Tagged , | Comments Off on SQL Query ORDER BY last_name, then first_name

MySQL – Counting how many rows have a particular value

Working on a SugarCRM dashboard today, and I needed to count how many records had one value and how many records had a second value. I then wanted to know what percentage the first value was of the total. So there’s a bit of MySQL code that helps make this easy to do.

  SUM(IF(field_name = 'value_one',1,0)) AS 'Value One',
  SUM(IF(field_name = 'value_two',1,0)) AS 'Value Two'
FROM table_name

What this does is counts up how many times value_one and value_two appear in field_name in the table table_name.

You can then take this a step further, if you wanted to, and I did…

you can use a line like this within your query:

This gives you the percentage that value_one is of (value_one + value_two).

Hat tip to this stackoverflow answer by eisberg.

Edit: 2014-05-26-21h53m put the final code snippet into a ‘Gist’ on GitHub.

| Tagged , , , | Comments Off on MySQL – Counting how many rows have a particular value
How to

Show hidden files/folders in the File Open dialog box on Mac OSX

I was needing to navigate to a file that was in a ‘hidden’ folder, but the File Open dialog box won’t show the hidden folder.

The answer was found here: Press Command, Shift, Period keys together, the hidden folders/files will appear.

| Tagged , , | Comments Off on Show hidden files/folders in the File Open dialog box on Mac OSX
How to

HTML Emails – How to do them well

Great article over at A List Apart: Can Email Be Responsive. If you do HTML email newsletters, you should go read this.

| Tagged , , | Comments Off on HTML Emails – How to do them well
*nix, How to

Securely delete files from the command line

Just discovered the srm command on *nix systems.

srm will securely delete files, dependant on the switches you give it either one overwrite with random data, 7 passes, or 35 passes.

Hat tip to

| Tagged , | Comments Off on Securely delete files from the command line
How to

Short list of software I’m using

Just a quick post of some software that I use, that I’ve just discussed with Seth (he knows who he is).

All useful, but the starting point is better task management, so start with Nozbe. PS: also see remapping the CapsLock key.

| Tagged , | Comments Off on Short list of software I’m using

Truth, Justice and Coffee

We all want to achieve. We want the truth, we want justice. But above all, we want our coffee. Coffee is the fuel that powers our quest for truth and justice. Let the world know that you are in pursuit of truth, justice and coffee by ordering your own tshirt by clicking here to order yours.

Get your tshirt now. Order today. There are no free steak knives.

Get your tshirt now. Order today. There are no free steak knives, only the deep satisfying feeling that comes from knowing you’ve helped.

PS. I wanted a tshirt, but couldn’t find what I was looking for, so I’ve made it here. If the goal is reached (50 tshirts) at teespring, then they’ll make ’em. Go on, I know you want one, order one for yourself and your neighbour. While you’re at it, get one for your neighbours nephew too.

This offer/campaign ends May 22nd 2014.

| Tagged , | Comments Off on Truth, Justice and Coffee

The Expert

The Expert is a short youtube clip that illustrates what being billed as the “the expert” can feel like sometimes. Fortunately it’s not a common occurrence.

| Tagged , | Comments Off on The Expert