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>

It’s been almost 5 years since I last upgraded my PC. How times change, I never would have thought I’d be considering a laptop to replace a desktop machine any time soon but that’s exactly what I’m going to do.

When I’m not in the process of upgrading I pay little attention to PC hardware so I was surprised to see one terabyte disks available (for only £230!) and even solid state disks (SSD) are now financially viable.

I’m seriously considering upgrading to SSD in Q4, with no moving parts noise and heat is greatly reduced, battery life is improved and they’re more robust than conventional hard drives (being dropped isn’t a problem). The only downsides are price, write performance and lifespan.

Ironically when it comes to deframentation SSDs are deliberately fragmented to prolong disk life. Each cell has a limited number of reads/writes (approximately 1 million) so data is deliberately spread to maximise the lifespan. Therefore defragmentation is discouraged!

I’m waiting for Sony to release their SZ5 notebook which is based on Intel’s Santa Rosa platform (marketed as Centrino Duo for consumers and Centrino Pro for enterprise).

Just a week after deciding it’s time to upgrade, my main PC develops a weird problem where the monitor will go to sleep while I’m using it, even when listening to music or watching a movie. The audio can still be heard when the monitor is in sleep mode.

First thing I did was to set all power management to ‘Always On’ and not to power down the monitor. Didn’t work. So I switched out the graphics card with a spare one I had. Didn’t work. I’ve monitored my PSU voltages, no spikes.

So while I wait patiently for the SZ5 to hit the streets I’m working off an old laptop my Dad has kindly lent me, which I use to remote desktop to my Windows machine.