Search code examples
mysqldatabase-partitioning

Add partitions to existing table based on current date

I'd like to partition an existing table into 3 partitions. At the time of update of our solution, a powershell script will connect to the MySQL server and execute a script file.

I tried following query for adding the partitions:

Alter Table `mytable`  
  PARTITION BY RANGE (TO_DAYS(`TimeStart`)) 
(
    PARTITION start                 VALUES LESS THAN (0),
    PARTITION "from"+(curdate()+0)  VALUES LESS THAN (curdate()+1),
    PARTITION future                VALUES LESS THAN MAXVALUE
);

column timestart (datetime(3) NOT NULL) is part of the primary key.

The partition in the middle requires specific values to make it compatible with maintenance queries run from a Windows service at some interval.

The query fails for two different reasons:

  1. Creation of the name of the partition: I'd like to get e.g. "from20180220" when the script was executed today. The error messsage is

    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 '"from"+(curdate()+0) VALUES LESS THAN (curdate()+1), PARTITION future ' at line 5

  2. Creation of the partition value. When the script is run today (Feb 19), I'd like to have an equivalent to VALUES LESS THAN (TO_DAYS('2018-2-20').

    Error Code: 1064. Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near '), PARTITION future VALUES LESS THAN MAXVALUE )' at line 5

I tried TO_DAYS(curdate())+1 also. Actually, I did not expect MySQL to have closures...

How can those errors be solved?

Solution

  • You're attempting to use MySQL data-manipulation-language functions like CURDATE() and TO_DAYS() in your data definition code. You Can't Do That™.

    You need to write some sort of program to write out a little file containing your Alter Table command, then run that file in MySQL.