the weekly whinge

So there’s a few places on the web asking how to do this; while it’s easy to do

SELECT x'1fb5';

if you have the hex string stored in a field you can’t do that.

The answer is to use CONV()

ie

SELECT CONV(myhexfield, 16, 10);

It’s that simple.

Tags: mysql.

From this upgrade note for MySQL 5.1

As of MySQL 5.1.15, InnoDB rolls back only the last statement on a transaction timeout.

Oh, what a fantastic design decision.

I love that there’s no explanation, no reasoning. Just “we’ve fucked over transactions”.

It’s almost like they decided that MySQL was getting far too close to being a usable enterprise solution so they did this to make sure it stayed where they know it really should be – website backend city.

Edit: MAD points out that the old behaviour can be turned back on with an option, but most people new to a database will install it with the default options and really, implementing this option at all just displays complete disregard for the ACID ideals

Tags: mysql.

Edit: this is fixed in 5.1.30, so I’m guessing it was fixed a while back – I’m working on a legacy system using 5.0.15.

 

So MySQL thinks that

SELECT -COALESCE(null, 0);

should return -0

(Whatever -0 is)…

Clearly I’ve simplified my code and there are ways around this, but… WTF?

 

For what it’s worth,

SELECT -COALESCE(null, 0) + 0;

results in the expected “0” value.

 

I was disappointed that

SELECT -COALESCE(null, -0);

doesn’t work though – if you’re going to believe that -0 is a valid number, you should be able to get +0 out of it.

Tags: -0, -zero, coalesce, mysql, negative zero, null.

So the web is full of people asking about this symptom and I hit it myself. Seemingly randomly (but consistently) IE will complain about nonsecure items in the page, with this warning message:

 

The error message from IE7

The usual response to this is that you’re loading images (or other media) from a non-SSL connection (hence “non-secure” items); there are also some mentions of IE not being happy about IFRAMEs without SRC= attributes (the browser assigns “about:blank” as a default SRC, which – being non-secure – flips the error); however none of these things applied in my case.

 

I discovered that if I turned off javascript the error disappeared, which suggested my script was problematic, but I still couldn’t find anything in the scripts which was loading any non-secure items.

 

In the end after much messing around it turns out that the offending script was setting object.style.background to a relative path, eg 

myObj.style.background=”url(/images/mybackground.gif)”;

Even though this will correctly resolve to an https:// address (assuming the root of the document is https://) IE can’t figure this out and moans.

 

Simply adding the full path to the background solved my problem.

 

This doesn’t apply to CSS files setting backgrounds – you can happily have relative paths in these and all will be well. 

 

Of course I then wondered why I hadn’t found that on the web and figured you might be looking for it too – so here it is 🙂

Tags: error, fix, internet explorer, nonsecure items, warning.

So Google Streetview has come to the UK, with street-level pictures of Southampton, London, Bristol, Cambridge, Oxford, Norwich, Coventry, Birmingham, Nottingham, Derby, Manchester, Liverpool, Sheffield, Leeds, York, Newcastle, Glasgow, Edinburgh, Dundee, Aberdeen, Swansea, Cardiff, Belfast, Lisburn and… Scunthorpe.

 

WTF? Does Google UK’s mapping director live in Scunthorpe or something? I mean, I’ve nothing against the town but it’s not exactly the most attractive or well-known, is it?

Tags: google.

Who at Mysql AB decided that SHOW TRIGGERS LIKE ‘…’, unlike the SHOW TABLES LIKE ‘…’ command on which it is modelled, should not display triggers whose names match the ‘…’ string but instead display triggers which relate to tables whose names match ‘…’

??

Sometimes I wonder about these people, I really do.

Tags: mysql.

Say you want to step through a bunch of records in order using a MySQL cursor. That’s normally not a problem: you create a stored procedure so:
CREATE PROCEDURE doit()
BEGIN
DECLARE whichi INT(11) DEFAULT 0;
DECLARE mycur CURSOR FOR SELECT IF(i=1, f1, f2) AS whichi ORDER BY whichi;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cursorExpired=true;
open mycur;
curnext: loop
FETCH mycur INTO whichi;
IF cursorExpired THEN
LEAVE curnext;
END IF;
select whichi;
END LOOP curnext;
close mycur;
END;


Obviously that’s an incredibly inefficient way to list records, but you get the idea.

The point of the query is that the SELECT must be ordered by the result of IF(i=1, f1, f2); however calling doit() will not output what you expect (unless, of course, you’ve come across this problem before, in which case you’ll be expecting it :))

What you get is no ordering of the data at all – the ORDER BY part of the statement is effectively ignored. Why? Because we’ve called the field in the SELECT query the same as the variable we declared above. So you end up with (essentially)

SELECT IF(i=1, f1, f2) AS whichi ORDER BY 0;

I guess you can imagine how long it took me to figure that one out.

Tags: mysql.

I’m not a great fan of making use of non-ANSI SQL: not only does it make it harder for new coders to pick up but it means any chance of changing databases becomes completely financially unviable.

The MySQL USING() clause is the worst type of example: not only is its requirements for brackets confusing and unnatural, it provides no advantage except to the incredibly lazy – being purely syntactic sugar.

For those who don’t know, it replaces

FROM a INNER JOIN b ON a.fieldname=b.fieldname

with

FROM a INNER JOIN b USING(fieldname)

Up until today I’ve been mildly irritated by it, until I came across a bizarre piece of behaviour. Given two tables, “pubs” and “beers”, with a joining field “brewery” and both with named fields “name”, running

SELECT strength FROM beers INNER JOIN pubs USING(brewery) WHERE name="the white horse"

utterly fails to warn me that “name” is ambiguous and returns no results (unless there’s a beer called “the white horse”, of course). On the other hand,

SELECT strength FROM beers INNER JOIN pubs ON beers.brewery=pubs.brewery WHERE name="the white horse"

immediately produces the “Column ‘name’ in where clause is ambiguous” error you expect.

Do I need to go into detail why this makes USING() harmful?

This is admittedly fixed in more recent versions (bugs.mysql.com) since October last year but how many installations out there (like mine) will still exhibit the bug? I’d guess at a fair few.

Tags: bug, join, mysql, using.

So a classic web developer trick is to use the onchange event of a dropdown to change another field in the form. Yes, there are accessibility issues with this (keyboard+screenreader users could trigger the onchange event while scrolling through the options) but FF3 and WebKit both seem to have realised this and if you change the value keyboard-wise they only trigger onchange when you blur. Of course IE7 is behind the curve on this (surprise) but with luck IE8 might catch up.

Anyway, Chrome seems to have a problem with setting focus() to another object from within the onchange event – while the focus works fine and the new field is focussed correctly, it fails to update the existing selectbox to the new value.

There is a workaround (of course) and it’s fairly straightforward: replace

linkedField.focus();

with

setTimeout(function (){linkedField.focus()}, 5);

and all is fine.

Safari doesn’t exhibit this so I don’t know if it’s something google has done or if it’s just Safari’s more recent webkit version. Let’s hope that it’s fixed before the beta period ends!

.

I couldn’t find a solution to this for the first few web searches I ran, so hopefully this will provide answers to anyone also searching for the same problem!

I run Cyrus because it seems like the best solution for a) supporting folders containing subfolders and emails, b) providing email accounts to many users without needing system logins and c) NTLM support.

Due to reasons I won’t go into now I had to restrict the imap process only to run on one of the server’s IPs; however I also need to access it from localhost for some of the server processes.

Now the recommended way to run the cyrus daemon on multiple specific IPs appears to be to tell the master process to run two different daemons, one on each IP.

So I had in cyrus.conf

imap              cmd="imapd -U 30" listen="myhostname:imap" prefork=1 maxchild=100

imap-lh           cmd="imapd -U 30" listen="localhost:imap" prefork=1 maxchild=100

The problem was, as soon as I enabled this second line, cyrus went into complete meltdown – connecting on one of the IPs just hung and eventually timed out (and it seemed to be randomly chosen at restart whether it’s the local or public IP which fails).

I finally found a posting in the cyrus mailing list (lists.andrew.cmu.edu), which has had no responses (typical) and explains it: there’s a bug in the cyrus code which figures out the lockfile name – for some reason (probably to stop people using invalid characters) it just stops at the first alphanumeric character so both daemons were using the same lock file.

So the “random” element was just whichever one connected second: renaming the second daemon to “imaplh” solves the problem.

Hope this helps someone!

Tags: cyrus, freeze, hang, hosting, imap, mail, two IPs.