DHL: Clueless?

I recently signed up with Vonage, since it seems pretty cool. They sent me the VoIP adapter via DHL, who picked it up on Thursday, April 6th. It was sent 2nd day delivery, which means it should have been delivered on Monday, April 10th. Did I get it? Nope!

All through yesterday, April 10th, the DHL website claimed:

Est. Delivery Date: 4/10/2006

Today, Tuesday, April 11th, I figured I’d give them a call to see what’s up, and when I should expect to really receive the package, since the site still claims that the estimated delivery date is yesterday, which doesn’t inspire much confidence. The conversation went something like this:

DHL: Thanks for calling DHL, what can I help you with?
Me: I was sent a package which was supposed to be delivered yesterday, but I haven’t received it. I tracked the package on your website, and it still claims an estimated delivery date of yesterday, which can’t be right.
DHL: OK, can you give me the tracking number?
Me: OK, (reads tracking number)…
DHL: Well, we have a lot of packages and not all of them go out every day etc. etc. … your package is here at the sorting facility, it hasn’t gone out today.
Me: Uh, well, it was sent 2nd day delivery, the 2nd day was yesterday. Should I at least expect to receive it today?
DHL: Well, I can’t really tell you that. I don’t know if it will go out today, it’s still here… I don’t know if you’ll get it today.
Me: Don’t you have some sort of service guarantee, or the shipping is free?
DHL: Uh, uh, I don’t know, you’d have to talk to billing about that, I don’t know anything about that…

What the hell? You are DHL. Your only real business is moving other people’s stuff around. How is this considered customer service?

Side Effects

From the weekly beer:30 our group has on Fridays came this great quote, while we were talking about Guy1’s upcoming baby:

Guy1: (Talking about how ovulation testers are wonderful..)
Guy2: Are there any side effects?
Guy1: Uh, you pee on a stick. Side effect: You peed on your hand…
Guy2: Oh…

Gmail on Sony Ericsson W600i

I managed to get Gmail‘s POP3 and SMTP support (via SSL/TLS, which they require) working on my spiffy new Sony Ericsson W600i, but not without a bit of headache.

After setting everything up, using their provided information, the W600i was complaining about not trusting the SSL certificates that Gmail uses, and would refuse to send mail because of it (although it would still receive mail, after asking you to acknowledge a stern warning). In order to get it working I needed to install two certificates on my phone:

In order to get the phone to accept and install them, you have to use OBEX Object Push (aka “Send File…” under the Bluetooth menu in Mac OS X) to send the .cer files to the phone. The “Send File…” client will complain that your the device doesn’t accept the file type you are sending it; choose the “Send Anyway” option, it works fine. If you just transfer the files as regular files, you will see them on the phone, but will have no option (that I could find) to install them.

The phone is rockin’ now!

I’ve provided the above information here, because figuring out which certificates I needed, where to get them, and how to install them is like pulling teeth. I got each tiny bit of information from various different places. Hopefully this page will index well and help others. :)

Oops, BLOB/TEXT overrides tmp_table_size

First, a bit of background information… MySQL has a variable called tmp_table_size which is used whenever a temporary table is needed for many types of operations, but it’s mainly used for ORDER BY and GROUP BY. The tmp_table_size variable (in correlation with the max_heap_table_size variable) decide at what point to convert an in-memory (that is, HEAP storage engine) temporary table to an on-disk (that is, MyISAM) one.

So, if the result of your query will end up greater than the lesser of tmp_table_size or max_heap_table_size, once it reaches that point, it will be automatically converted to a MyISAM table, on-disk (in tmpdir, incidentally).

Here’s a scenario: You have a SELECT query, nothing particularly special. It isn’t any more complex than another query on the same tables, but it performs a bit worse. You put some load on it, and it performs miserably, even putting the server in pain in the process. You check what’s happening… hmm, lots of blocks being written? Why would there be any blocks written, this is a SELECT!

Does the SELECT have a BLOB or TEXT column in it? Are you using a GROUP BY or ORDER BY that can’t use the same index that MySQL is choosing? (Check EXPLAIN for Using temporary or Using filesort.)

In that case, MySQL will need to create a temporary table to do the sort. Since your query contains a BLOB or TEXT column, the temporary table will have to be created as a MyISAM table, as the HEAP storage engine doesn’t support BLOB or TEXT (or even VARCHAR, for that matter, which is changed to CHAR automatically).

This will mean a lot of extra writes to the disk, for no good reason. What can you do about it?

Short term, for 4.1+: Abuse a subquery in the FROM to read the primary key of the table with the BLOB/TEXT and do the sorting that is necessary. In the outer query, select the full row, with BLOB/TEXT and return it, but don’t use ORDER BY.

Long term: Support for BLOB and TEXT need to be added to the HEAP storage engine.

What if you need to ORDER BY or GROUP BY the BLOB or TEXT column? Well, you’re screwed. Think about redesigning your database. :) (Hint: Add a column containing the first up to 255 bytes of the column as a VARCHAR and sort on that instead…)

Good luck!

MySQL 4.1 and 5.0: Prepared Statement Leaks

As it turns out, the memory leaks which I initially blamed on 5.0 also affect 4.1. It’s not stored procedures leaking memory as I initially thought, but server-side prepared statements. The problem was finally worked out by setting useServerPrepStmts=false in the JDBC code, which “fakes” prepared statements on the client side, without any other code changes.

Read MySQL Bug #18300 for more information!