MySQL partitioning by range - error in statement?

Question

I try to alter an existing table by adding partitioning, but get SQL errors although it looks like the docu says.

Hopefully somebody can point out my mistake.

The table orders has a field called date_order_start, which is DATE, so it has no time information. This field has an index on it. This index is not unique and not part of another unique index.

I want to partition my table by using this statement:

ALTER TABLE orders
PARTITION BY RANGE (date_order_start) (
    startpoint   VALUES LESS THAN (0),
    from20140701 VALUES LESS THAN ('2014-07-01'),
    from20140801 VALUES LESS THAN ('2014-08-01'),
    from20140901 VALUES LESS THAN ('2014-09-01'),
    future       VALUES LESS THAN MAXVALUE
);

Error....

Before I tried this:

ALTER TABLE orders
PARTITION BY RANGE (TO_DAYS(date_order_start)) (
    startpoint   VALUES LESS THAN (0),
    from20140701 VALUES LESS THAN (TO_DAYS('2014-07-01')),
    from20140801 VALUES LESS THAN (TO_DAYS('2014-08-01')),
    from20140901 VALUES LESS THAN (TO_DAYS('2014-09-01')),
    future       VALUES LESS THAN MAXVALUE
);

But also got an error:

**Error Code: 1064**. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from20140701 VALUES LESS THAN ('2014-07-01'), from20140801 VALUES LESS T' at line 4

Well.... that does not help.

Can anybody spot the error?

Also variation without the startpoint statement didn't work. I thought maybe the (0) was the problem.

I used these pages for information:

http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html http://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations.html

Answer 1

I'm wondering if you're just missing the partition keyword:

ALTER TABLE orders PARTITION BY RANGE (date_order_start) ( PARTITION startpoint VALUES LESS THAN (0), PARTITION from20140701 VALUES LESS THAN ('2014-07-01'), PARTITION from20140801 VALUES LESS THAN ('2014-08-01'), PARTITION from20140901 VALUES LESS THAN ('2014-09-01'), PARTITION future VALUES LESS THAN MAXVALUE );

Also, is the VALUES LESS THAN (0) part really necessary?