The time has come! The Perth Linux User Group is holding a Quiz Night tonight (doors open 7pm, Quiz starts 7:30pm), with questions very much based upon Linux & Open Source. It’s at The Moon & Six Pence in Murray Street. We’ve got a few prizes, and it’s free (through we’ll probably make you feel guilty if you’re membership isn’t 100% up to date ($10/yr)!!). Come share a drink and a laugh.
MySQL Indexes and maximum lengths
MySQL has several index types; the default and therfore probably most common is the BTree. There’s a limit in MySQL (at least as of this writing when the “current” GA is 5.5.9) is 767 bytes – across all the columns being indexed. Of course, varchar columns can now be bigger than 255 chars, so this limit is probably more easily reached these days.
In my case, I had a table with one column of a URL’s path, and a URL’s Query String, both of which can be larger than the old 255 chars. I also have an index that cover these two plus a few other columsn – normalised protocol, normalised domain, and TCP port.
In trying to move to longer columns (1K) I had to modify my index to restrict the number of characters from these varchar columns to ensure I remained under the 767 limit – I couldn’t just change one of these columns from 255 to 1024.
I throught I’d try and simple change first – instead of increasing the oclumn, just put the restriction on the current columns as they stand – so limit the index to 255 chars (while the column still IS 255). Turns out that in at least 5.5.9, since the specified size is the current column size, it ignores this.
Lets make that clearer; you need to specify a SMALLER length for the indexed columns in order for it to stick. Once that’s done, you can then alter table to increae the column lengths.
MySQL CREATE TABLE as doesn’t take triggers with it
MySQL has a nice feature that you can make a new table exectly like an old table (as far as table column structure and indexes go):
create table FOO like BAR;
However, as I just rediscovered, any triggers on the table aren’t taken across with it. D’oh. Which reminds me, mysqldump has a specific -R flag to backup/dump routines; worth having that on too (I did).
Rule for the day: check your triggers on tables before moving/renaming.
Gingerbread fixed my WiFi
Its been a few weeks now since Android 2.3, a.k.a. Gingerbread, was delivered to my Google Nexus One mobile phone. I had been experiencing terrible problems with the WiFi reconnecting to known networks, and randomly dropping off networks it was connected to. This was most frustrating; dropping back to 3G data is expensive, slow, and sometimes becuase of this, I disabled it which meant no data. All while sitting within 10 foot of my WiFi AP.
It appears that this is no longer a problem, ever since the Gingerbread update was installed. Yay! Thanks for the fix, Google Mobile team.
Optus thinks Perth is in East Timor
Today my smart phone (Google Nexus One, Android 2.3 Gigerbread), which uses the mobile network for reciving its time information, suddenly went an hour ahead. I looked at the TZ settings, and it reported I was at +0900, East Timor. Perth is normally in AWST +0800. I confirmed it several times by switching off “Automatic” (Use network-provided values), setting my TZ to Perth, and then turning Automatic back on; each time the phone would report it had gone to “+0900, East Timor”.
I can only imagine that the local cell tower is handing out the wrong time zone information (Melville, 6153 area). This was fine yesterday. Let’s see how long this takes for Optus, the carrier I am with, to realise. Either that, or ther’s been a strong cell site thats just done nearly 3000 kilometres, or we’ve had some major techtonic plate movement.