こちらを読んだ時のメモ。

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.1 MySQL のパーティショニングの概要

もともとは以下のようなエラーがなぜ発生するかを調べていた。

% ALTER TABLE t ADD PARTITION (PARTITION p20220531 VALUES LESS THAN ('2022-06-01 00:00:00')); (1697, "VALUES value for partition 'p20220531' must have type INT")

パーティションとは

• 本来SQLはデータの格納方法には感知しない言語
• しかしパーティションはデータの格納方法を明示的に指示するための機能

パーティショニング関数

データをどこに格納するかは「パーティショニング関数」が判断する。

• RANGEによるルールや、やHASHによってデータの格納先が決定される
• パーティショニング関数は、INTかNULLを返すようにすればSQLで定義できる

INTかNULLを返す

基本的には日付によるパーティショニングはできない。TO_DAYS 関数などで INT にしているのはこのため。 件のエラーメッセージもこの基本事項によるものだった。

パーティションのメリット

• パーティショニング関数と検索のWHERE句がかみあってると、ディスクを読む範囲を絞れる
• データの削除が楽
  • 日付でパーティションされた時系列データで、古いデータを落とす時などに便利
• データを読み書きするディスクが分かれるので、シークが並列化できる

エラーについて

概要はここまでで、ここからは件のエラーについて。

(1697, "VALUES value for partition 'p20220531' must have type INT")

このエラーが発生したときは RANGE パーティションが設定されていた。

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.2.1 RANGE パーティショニング

余計な部分を省いた例を示すと、このように、joined カラムの年数をパーティショニング関数とするようなテーブルだった。

CREATE TABLE members (
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

Range と Range Column

原因は、Range Column パーティショニング用の DDL を、Range パーティショニングのテーブルに適用していたから。

CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
) 

名前は似ているが、適用すべきDDLは全く違う。

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.2.1 RANGE パーティショニング
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.2.3.1 RANGE COLUMNS パーティショニング

RANGE COLUMNS パーティショニングは RANGE パーティショニングに似ていますが、複数のカラム値に基づく範囲を使用してパーティションを定義できます。また、整数型以外の型のカラムを使用して範囲を定義できます。

RANGE COLUMNS は COLUMNS パーティショニングなので、パーティショニング関数として YEAR(joined) といった式を定義することができない。

この部分

PARTITIONED BY RANGE COLUMNS(column_list) (

あくまで指定するのはカラム名であり、どれくらいの粒度でパーティション分割するかは VALUES LESS THAN 構文のところで指示することになる。

この部分

PARTITION partition_name VALUES LESS THAN (value_list)][,

つまり

適用すべきDDLをこのように修正する必要があった。

% ALTER TABLE t ADD PARTITION (PARTITION p20220531 VALUES LESS THAN (TO_DAYS('2022-06-01 00:00:00')));

もっと言えば、時系列データに素直に RANGE パーティショニングを使うのは現在は悪い例とされているようなので、パーティショニング方式を見直すほうがよいのかもしれない。

• DATE PartitionにTO_DAYS()を使うのはやめよう - Qiita

しかし結局

実際はパーティショニングには MAXVALUE が使われていたため、DDLはちょっとした変更では解決しなかった。

% ALTER TABLE t ADD PARTITION (PARTITION p20220531 VALUES LESS THAN (TO_DAYS('2022-06-01 00:00:00'))); MAXVALUE can only be used in last partition definition

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.3 パーティション管理
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 19.3.1 RANGE および LIST パーティションの管理

↓によるとパーティションは後ろにしか追加することができない。

範囲によってパーティション化されたテーブルで ADD PARTITION を使用するときは、パーティションリストの上端にのみ新しいパーティションを追加できます。この方法で新しいパーティションを既存のパーティションの間または前に追加しようとすると、次のようにエラーになります。

↓によるとパーティションを前方に追加したいときは REORGANIZE PARTITION を使う必要がある。

データを失うことなくテーブルのパーティショニングを変更する場合は、代わりに ALTER TABLE ... REORGANIZE PARTITION を使用してください。REORGANIZE PARTITION については、後続の説明またはセクション13.1.7「ALTER TABLE 構文」を参照してください。