As I recently found out, MySQL 5.1 claims to support partitioning, but it doesn’t really work, yet.

I tried loading the FlightStats database (feel free to get your copy on dl.flightstats.us) into a MySQL 5.1 instance to try out partitioning. It seemed fairly simple… create an ontime table, partitioned by month, er, wait. Then I hit this bug:

mysql> CREATE TABLE t (
    ->   d DATE NOT NULL
    -> )
    -> PARTITION BY RANGE(d) (
    ->   PARTITION p0 VALUES LESS THAN ('2005-06-02')
    -> );
ERROR 1064 (42000): VALUES value must be of same type as partition function near
')
)' at line 5

OK, so I worked around that using YEAR(d)*100+MONTH(d) instead, and calling it an INT. I got a few million rows of data loaded. I tried one of the main features that partitioning is useful for, and one that MySQL claims to support: dropping single partitions. Immediately, I hit this bug:

mysql> alter table ontime drop partition p_2000_01;
ERROR 1037 (HY001): Out of memory; restart server and try again (needed 8
bytes)

Ugh. I guess I have to give up and wait for MySQL 5.1.8 before I can try again. Keep an eye out, I’ll update things once I try again.