«
»

MySQL 5.1: Partitioning not quite there yet

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.



Google
 
Search the Web Search only jcole.us

This entry was posted on Sunday, March 12th, 2006 at 20:00:32 and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “MySQL 5.1: Partitioning not quite there yet”

  1. jim Says:
    March 13th, 2006 at 02:30:41

    this was bug #17169, and is fixed in 5.1.8. (it was a simple bug — just some checks on the memory allocation that were backwards.)

  2. Jeremy Cole Says:
    March 13th, 2006 at 09:13:29

    I know — but that doesn’t change the fact that Partitioning is essentially useless in 5.1.6 and 5.1.7. :)

    Eagerly awaiting 5.1.8…

  3. Roland Bouman Says:
    March 13th, 2006 at 15:53:16

    Hi Jeremy,

    I just read you won the iPod for being a big help hunting for MySQL 5.1 bugs. Congratulations!

    Roland

  4. jcole’s weblog: Jeremy Cole’s take on life. » Blog Archive » MySQL 5.1: Partitioning getting closer Says:
    May 29th, 2006 at 13:26:58

    [...] Back in March, I wrote about Partitioning in MySQL 5.1 not being usable, and since a few releases have gone out, and a few months have passed, I am re-visiting the issues I saw back then. [...]

  5. M. Aydın Bahadır Yazıları » Blog Arşivi » Mysql Kaynakları Says:
    February 9th, 2008 at 17:14:18

    [...] 5.1’s New XML Functions, What’s New with MySQL 5.1.x, MySQL 5.1: Partitioning Not Quite There Yet, MySQL 5.1 and Some of Its New Features, Blog Entry on MySQL 5.1, Senior Product Manager Discusses [...]

Leave a Reply