First attempts of range partition in mysql

Question

so I'm trying partitions for the first time in mysql any help will be much appreciated. What I want the partition to do is get before less than cur date for partition 1, the next week in partition 2 and max value for partition 3.

create TABLE diagnostics_daily_device_summary (
ID bigint(20) NOT NULL AUTO_INCREMENT,
timestamp datetime,

-- there are other fields included but for the sake of the example I won't bother throwing them in

PRIMARY KEY (ID, timestamp)
        )
        PARTITION BY RANGE (yearweek(timestamp))(
        PARTITION p1 VALUES LESS THAN (yearweek(curdate())),
        PARTITION p2 VALUES LESS THAN (yearweek(date_add(curdate(), interval 7 day))),
    PARTITION p3 VALUES LESS THAN maxvalue
);

I need to maintain the last 3 weeks of data that's why I'm breaking it down into yearweek format.

When I run the query the error message I'm getting is this

Error Code: 1064. Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near '), PARTITION p201547 VALUES LESS THAN (yearweek(date_add(curdate(), interval 7 d' at line 70

I'm not too sure how to fix it.

Additionally I want to add a new week every week and drop the last week, I got this line of code from an online source

USE demotimeparts;
DROP PROCEDURE IF EXISTS rotateTimePartition;
DELIMITER //
CREATE PROCEDURE rotateTimePartition (newPartValue DATETIME)
BEGIN
        DECLARE keepStmt VARCHAR(2000) default @stms;
        DECLARE partitionToDrop VARCHAR(64);

    SELECT 201501
    INTO partitionToDrop
        -- FROM SCHEMA_NAME.partitions
        WHERE table_schema='SCHEMA_NAME'
        AND tabe_name='diagnostics_daily_device_summary'
        AND partition_ordinary_position=1;
    SET @stmt = CONCAT ('ALTER TABLE diagnostics_daily_device_summary DROP PARTITION' ,     partitionToDrop);
    PREPARE pStmt FROM @stmt;
    EXECUTE pStmt;
    DEALLOCATE PREPARE pStmt;

    SET @stmt = CONCAT ('ALTER TABLE diagnostics_daily_device_summary ADD PARTITION (PARTITION Y',
                        DATE_FORMAT(newPartValue, '%Y%m%d'),
                        ' VALUES LESS THAN (TO_DAYS(\'', 
                         DATE_FORMAT(newPartValue, '%Y-%m-%d'),
                         '\')))');

    PREPARE pStmt FROM @stmt;
    EXECUTE pStmt;
    DEALLOCATE PREPARE pStmt;

    SET @stmt = keepStmt;
END //
DELIMITER;

Like I said any help will be greatly appreciated! thank you again.

Rick James Rick James · Accepted Answer · 2015-11-27T20:20:39

The "timezone" 'error' has to do with not being sure which week a particular time is in. If the timezone changes, some data could be in the wrong partition, thereby making SELECTs give the wrong answer.

Also, CURDATE() would be evaluated at the time of the CREATE TABLE. I suspect this in not what you wanted.

Since you do want to drop old data, etc, I suggest you simply go with days or weeks and use constant dates (using TO_DAYS()). Look at http://mysql.rjweb.org/doc.php/partitionmaint for details on how to do the rotation and tips on other issues.

First attempts of range partition in mysql

1 Answers