maria-discuss team mailing list archive

[Roland Ziegler <ziegler@xxxxxxxxxx>]

Hi all,

I'd like to set up partitioning for the "time series" use case as in 
https://mariadb.com/kb/en/partition-maintenance/.

However, I'd also like to control data directories. And I want the "max" 
or "future" partition as a catch all fallback.

So I start with (minimalist example):

CREATE TABLE partitioned (
  timestamp INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (timestamp)
)
ENGINE=InnoDB
PARTITION BY RANGE (timestamp)
(
  PARTITION pstart VALUES LESS THAN (1) DATA DIRECTORY 
'G:/MariaDB/partitions/start',
  PARTITION p1 VALUES LESS THAN (1577833200) DATA DIRECTORY 
'G:/MariaDB/partitions/1',
  PARTITION pmax VALUES LESS THAN MAXVALUE DATA DIRECTORY 
'G:/MariaDB/partitions/max'
);

Works as expected.

Now I want to add a new partition, splitting the "pmax" partition:

ALTER TABLE partitioned
  REORGANIZE PARTITION pmax INTO (
    PARTITION p2 VALUES LESS THAN (1609455600) DATA DIRECTORY 
'G:/MariaDB/partitions/2',
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

This only kind of succeeds, as the DATA DIRECTORY clause is ignored, 
with a warning. So the result is useless. The "pmax" partition is now in 
the default data directory and the "p2" partition is in 
G:\MariaDB\partitions\max.

Specifying both data directories:

ALTER TABLE partitioned
  REORGANIZE PARTITION pmax INTO (
    PARTITION p2 VALUES LESS THAN (1609455600) DATA DIRECTORY 
'G:/MariaDB/partitions/2',
    PARTITION pmax VALUES LESS THAN MAXVALUE DATA DIRECTORY 
'G:/MariaDB/partitions/max'
);

gives two warnings, the "pmax" stays in its place now, but the "p2" 
partition is still created in that same directory.

ADD PARTITION doesn't work either, as it collides with MAXVALUE condition.

Any way around this?

MariaDB version 10.4.12

Thank you.