(One reason why) MySQL sucks

Posted by Luke
on Friday, September 14

[Update: I should really re-title this “(One reason why) MySQL used to suck” because as Scott Becker points out in the comments, as of MySQL 5.0.3, the maximum VARCHAR in MySQL is a respectable 65,535 bytes. So consider me appropriately chastened. I’ve updated the post below.]

I saw this recently in some code we were working on:

1
2
3

create_table :foobar do |t|
  t.text :url
end

Huh? Using a TEXT column to store a URL? That’s not good:

If any field with “TEXT” or “BLOB” in its type name becomes part of your SELECT query and the query is using temporary tables in its execution plan, the temporary table cannot be created in memory. Instead a temporary table on disk has to be used, regardless of MySQLs settings for tmp_table_size and max_heap_table_size.

Oh yeah, that’s right: MySQL (prior to version 5.0.3) limits VARCHAR fields to 255 bytes. Certainly long enough for most URLs, but somebody was just being careful.

Philip Greenspun pointed out a long time ago that one of the major indicators of database quality is how much data it lets you store in a VARCHAR. MySQL (prior to 5.0.3) is on the pathetically small side compared to other databases:

  • PostgreSQL: approximately 1 GB
  • MySQL (5.0.3+): 65,535 bytes
  • DB2: 32,672 bytes
  • Informix Universal Server: 32,000 bytes
  • Microsoft SQL Server: 8000 bytes
  • Oracle: 4000 bytes
  • MySQL (prior to 5.0.3): 255 bytes

Supposedly this is going to be fixed in a future version. Someday. Sigh. (And it was! Hurray!)

Remote MySQL GUI with SSH

Posted by Luke
on Tuesday, August 28

Back in my PHP/MySQL days I used to be quite the MySQL console jockey. I used it for all kinds of stuff. Then I got a new job, moved to DB2 and thankfully forgot as much as I could about MySQL. Now I’m doing Rails and working with MySQL again. But these days I use CocoaMySQL for nosing around the database on my local machines.

On remote servers, I was still using the console, but I recently found this trick which allows you to open up CocoaMySQL on a remote database using an SSH tunnel. The database doesn’t have to be configured to accept connections from outside of localhost.

Here’s how it works.

First, create an SSH tunnel.

ssh -L 8888:example.com:3306 user@example.com

Here I’m connecting the free port 8888 on my local machine to 3306 (the MySQL port) on the remote server, logging in as user.

Then configure CocoaMySQL to use the tunnel. Set the host to 127.0.0.1 and the port to 8888. The user, database, and password will be that of your remote server.

(There’s a section in the config screen to use an SSH tunnel, which I think is supposed to create the tunnel automatically, but I wasn’t able to get that to work.)

I’ve found this tip useful in my work. Hopefully you will too!

Interesting links

Posted by Luke
on Friday, August 10
  • Kristian Köhntopp writes about common MySQL performance problems with Rails. He shows some ignorance of Rails, but most of the issues he raises are important. Every database has its gotchas so at some level, database abstractions like ActiveRecord fall over. Fortunately, as long as you’re aware of the issues Kristian raises, you can work around most of them. Calling attention to Rails’ default of using large varchars and select * by default is especially important.
  • Patrick Reagan’s caches_constants plugin looks like a nice implementation of the common Java pattern of type-safe enumerations backed by the database. That means you can use constants in your code and foreign keys in your database to refer to a set of objects. With Patrick’s plug in, these objects are only queried for once when your Rails app starts up.
  • I thought the Ruby documentation for Object#instance_variable_set was pretty funny:

    Sets the instance variable names by symbol to object, thereby frustrating the efforts of the class‘s author to attempt to provide proper encapsulation. The variable did not have to exist prior to this call.

  • If you’re wondering why you can’t get Bugzilla working with Apache 2.2, the answer is that they’ve changed the default permissions. Raditha Dissanayake has information on how to fix this.
  • Finding the intersection of two date ranges is annoying, but it has a simple solution if you’re clever about it. Ryan Farley visualizes the problem, but “Dithermaster” realized it’s a lot simpler to find out if two ranges don’t overlap, and negate that. His solution is nicely usable in SQL.
  • During Ostrava on Rails I had the pleasure of meeting (and drinking a pivo or three) with Robert Cigán, developer for Czech Rails development shop Skvělý.CZ. He sent in a link to Skvělý.CZ’s latest application, sMoney.eu an easy to use personal accounting app for EU users. Check it out if you’re in the EU!