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;

A useful bookmark for any developer using MySQL: 84 tips on improving your query performance in MySQL.

I’ve spent too many hours this week porting the company database from MySQL 3.23 to 5. This was probably a bit more painful than it needed to be because we’ve not upgraded MySQL versions… ever.

The biggest hurdles were manipulating the SQL dump file (we had to dump and re-import due to MyISAM changes between versions) and leaving MySQL 5.0’s server mode as STRICT (as recommended by the installer).

Manipulation of the dump file (which was 13GBs) really made it stand out how modern programmers don’t necessary think about resources (or don’t have to with memory managed languages). I guess with the ample resources of most desktops for day-to-day operations its easy to see why. However, trying to load a file of this size made every Windows text editor I tried, fall over (either loading or saving).

In the end I chopped the file into 1GB chunks and edited those with gVim. In hindsight I should have split by line number and not by filesize.

Although it was the easy way out (and we should have updated our SQL), changing the mode to mysql323 saved us a LOT of work. Few application changes were required, the main one was for a replication issue. Replication would stop on certain CREATE TABLE statements where a default value was specified on integer auto_increment columns (gotta love legacy application code!).

When running our system on my dev machine with PHP 5.1.4 and MySQL 5 it did feel ’snappy’. I think this was probably more psychological than anything else.

For several months now I’ve thought MySQL 5’s push to a production ready status was more of a political move than technically justified. First I read this and again today when it came time to compile any MySQL support into PHP 5.1.4 on my newly installed Linux box I ran into problems.

We’re in the process of making the move to MySQL 5.0 at work too; however since we’re moving from 3.23.x there’s nothing too clever going on with the SQL. I always check up on the changes made in recent point releases. MySQL 5 scares me because of some the bugs still unpatched (SELECT DISTINCT queries sometimes returned only the last row), we’re now on more point releases for 5.0 than 4.1 (21 vs 19).