Well so much for getting a Sony Viao. I bit the bullet and bought a new Santa Rosa 15″ Macbook Pro in the end. After a patient 18 day wait between ordering and delivery it finally arrived last Friday.

As I’ve mentioned previously, I’m no fan of OSX so I also picked up a copy of Windows Vista. For the 48 hours I’ve used Vista it doesn’t seem to be too different. It does feel like there’s now an additional click to get everywhere, a lot of things have been moved around (but in a methodical manner).

A lot of the nerdy stuff has been hidden, Add Hardware is not an easy spot unless you switch to classic view in Control Panel. Simple things like disk defragmenter now has no graphic display on it’s fragmentation. It feels as though MS has swept this stuff under the rug so the focus on productivity instead of maintaining the OS, which is fine but I imagine for “Power Users” this is annoying.

I digress, what I wanted to mention in this post was an issue I’ve had with Vista’s User Account Control (UAC) and running Eclipse. Each time I started Eclipse, Vista would prompt me with a “Open File - Security Warning”, informing me the program I am executing has an Unknown Publisher. Despite unchecking the ‘Always ask me about running this file’ option, it would repeatedly do so.

(eclipsec.exe pictured)

After some Googling I thought I’d found an answer. Check file properties, at the bottom of the General tab, click the Unblock button. No joy. Once I’d clicked OK, if I returned to the properties screen the file was blocked again. It turns out the problem was downloading Eclipse using IE7 and NTFS’ ’streams’.

I tried the suggestion of running Sysinteral’s streams and deleting the stream information but I encountered an ‘Access is denied’ message as well. Copying eclipse.exe from my system drive to my USB flash drive and then copying it back again sorted the issue.

My preferred tool for constructing queries against MySQL databases is the command line client. One niggle that has plagued me since I started using the client was the inability to retrieve warnings that sometimes occur on queries.

This morning I stumbled across the following in the MySQL manual:

mysql> SET GLOBAL query_cache_size = 40000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1282
Message: Query cache failed to set size 39936; new query
         cache size is 0

SHOW WARNINGS reports those pesky messages I could never retrieve!

By default MySQL is not case sensitive when performing comparisons against string columns unless you explicitly declare them as BINARY in the table schema. However, I was caught out when performing a query similar to the following (prepared statement):

SELECT
  label, composer, workTitle, worksId
FROM
  labels L INNER JOIN
  composers C ON (C.id = L.composerId) INNER JOIN
  works W ON (W.id = C.worksId)
WHERE
  CONCAT(
    label,' ',composer,' ',workTitle,' ',wordsId
  ) AS search LIKE '%?%'
LIMIT 20;

Basically it’s a bit of hack to perform a search on all search terms (for single term searches). However, when I ran this query with data I knew existed in the table, my lowercase term returned no results. So I exactly matched the term, and got rows back.

Comparisons on calculated criteria using string functions would appear to be case sensitive. Simply lowering the case of both the search field and my LIKE keyword(s) fixed the issue.

SELECT
  label, composer, workTitle, worksId
FROM
  labels L INNER JOIN
  composers C ON (C.id = L.composerId) INNER JOIN
  works W ON (W.id = C.worksId)
WHERE
  CONCAT(
    LOWER(label),' ',LOWER(composer),' ',LOWER(workTitle),' ',wordsId
  ) AS search LIKE '%?%'
LIMIT 20;

I had an opportunity to play with Bash script on Friday. My task was to write a small deployment script to grab our server class configuration settings from subversion and rsync them to the appropriate machines.

This was easy enough, a couple of commands to subversion, rsync and some glue and I’d be done. However, an hour into writing it I wish I’d used PHP or used my time to learn how to do it in Perl.

For one, a hash of arrays doesn’t sit well in Bash script. I wanted to define a list of servers for each class. In PHP the code would have been as simple as:

<?php

$servers = array(
  'web' => array('server1', 'server2', 'server3'),
  'db'  => array('server4', 'server5', 'server6')
);

?>

Fortunately I was able to work around this with separate arrays for each class. What I couldn’t get around was the pain I had to endure to pass an array as an argument to a function.

Passing an array involves loading the space-separated elements of the array into a variable with command substitution.

Taken from Chapter 33 of the Advanced Bash-Scripting Guide.

printarray () {
  local passed_array
  passed_array=( `echo "$1"` )
  echo "${passed_array[@]}"
}

original_array=( element1 element2 element3 element4 element5 )
argument=`echo ${original_array[@]}` # command substitution
printarray "$argument"

This is just clunky and showed me that for anything more than basic conditional logic I’m better off investing some time in learning Perl.

At every company I’ve worked for and many open source projects I see MyISAM tables with INT(11) columns. Why?

MySQL’s INT columns are limited to 2^32 unsigned (4,294,967,295) which if you count the length is 10 digits long. Therefore INT(11) is impossible - although it worth noting the length on integer columns is only the display width.

If you’re looking to reduce the size of your tables/rows use the appropriate type of integer column.

</rant>