MySQL 5.1: Partitioning getting closer

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.

MySQL still doesn’t allow you to partition using a native DATE or DATETIME column. This is a major problem in my opinion, as it’s probably one of the most common uses for partitioning, but is only seen as a “feature request” by MySQL. Hopefully that will get fixed sometime in the near future.

I filed a couple more bugs today, the most serious of which was the inability to repair “crashed” partitions:

mysql> alter table ontime repair partition p_199609;
ERROR 145 (HY000): Table './flightstats/ontime#P#p_199609' is marked as
crashed and should be repaired

Here are the bugs I filed:

Partitioning still has quite a way to go before it’s really usable in my opinion, but it’s certainly improving!

4 thoughts on “MySQL 5.1: Partitioning getting closer”

  2. Regarding Bug #21030 there is a command that does exactly what you want to achieve.
    The command is:
    ALTER TABLE t REORGANIZE PARTITION p_2 INTO
    (PARTITION p_1 VALUES LESS THAN (2),
    PARTITION p_2 VALUES LESS THAN (3));

    The idea is that ADD PARTITION will always add partitions at the end (empty
    partitions for RANGE/LIST partitions) and that REORGANIZE PARTITION is used
    to do all kinds of reorganisations of your partitions such as split, merge, move
    range boundaries and so forth.

    It is naturally possible to simply map the ADD command to the above REORGANIZE
    command but I’m reluctant since the ADD command wouldn’t be expected to
    split a partition by its name.

    I’ll look into the other bugs, #20131 seems like a good idea to support.

    Reply

  4. Hi Mikael,

    Thanks for the detailed reply!

    Ah, good! I didn’t think to use REORGANIZE to do that. I would still have a problems though: The REORGANIZE command is fairly complex to do what is basically adding a new range. What if a new flag was added to ADD to do that for you? E.g.: ALTER TABLE t ADD PARTITION (…) REORGANIZE;

    In any case, it might be a good idea to suggest REORGANIZE in the error message for #20130!

    Reply

What do you think?

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s