Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database with a date column having datatype as date. I am using mysql 5.6

The table is expected to have 10k rows each day. I want to create partition on the table on year range and then sub partitions on months of that particular year.

I have tried is,

ALTER TABLE nav_master
    PARTITION BY RANGE( YEAR(nav_date) )
    SUBPARTITION BY HASH( MONTH(nav_date) )
    SUBPARTITIONS 12 (
        PARTITION january VALUES LESS THAN (2),
        PARTITION february VALUES LESS THAN (3),
        PARTITION march VALUES LESS THAN (4),
        PARTITION april VALUES LESS THAN (5),
        PARTITION may VALUES LESS THAN (6),
        PARTITION june VALUES LESS THAN (7),
        PARTITION july VALUES LESS THAN (8),
        PARTITION august VALUES LESS THAN (9),
        PARTITION september VALUES LESS THAN (10),
        PARTITION october VALUES LESS THAN (11),
        PARTITION november VALUES LESS THAN (12),
        PARTITION december VALUES LESS THAN (13)
    );

I am able to execute the above query on empty table. But when I insert or when I execute the above query in same table with some data I get the below error.

#1526 - Table has no partition for value 2014

Not able to understand what the exact error is.

Also let me know if sub partitions help in better optimizing the tables or I should just go with partitions?

Update: I was indeed able to create the partition. The issue now is that there is a NULL partition created. When I had ensured there is no null dates and also all the date conditions were handled while making partitions.

Question is how can I see what data has gone to NULL partition? So that I can get an idea on what the problem is.

I am getting a null partition name using this query.

SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'nav_master' limit 0,180

Using this query to get all rows in partition, question is what to pass as partition name for null?

SELECT count(*) FROM nav_master PARTITION(??);
share|improve this question
add comment

1 Answer 1

From the docco here, the syntax is as follows:

CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE

for creating a table with a partition.

But, to add a partition, you have to

ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));

So, it appears to be a syntax issue - RTF(ine)Manual :-)

As for your second question - only you can answer that - depending on your data volumes and storage configuration.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.