非パーティショニングテーブルを後から、パーティショニングする際にかならずハマるのが”オートインクリメント列を含む”テーブルに関する、パーティショニングの時です その回避策について、以下にまとめました。ご参考になればと思います。
例えば以下のようなテーブルの、updated_at をレンジパーティションキーとして、変更したいなんていう場合に
現在のテーブル定義
[code lang=sql]
CREATE TABLE test_ptable (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
updated_at datetime DEFAULT NULL COMMENT 'UTC-DATE',
domain varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8
;
[/code]
こんな変更を加えると・・・・
[code lang=sql] alter table test_ptable PARTITION BY RANGE COLUMNS(updated_at) ( PARTITION p20140901 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB, PARTITION p20141001 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB, PARTITION p20141101 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB ) ; [/code]
こんな感じで怒られて作ることができません
[code lang=text] ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function [/code]
つまり、パーティショニングには、プライマリキーが含まれていないといけないってことなのですが、例えば、そのプライマリーキーが、上記の表のようにオートインクリメントだったケースが多いのではないかと思います。
どうすればいいのか？・・
プライマリーキーを一旦DROPしちゃって作りなおそう・・
はい、残念、エラーになります
[code lang=sql] ALTER TABLE test_ptable DROP PRIMARY KEY; [/code]
じゃあ、オートインクリメントは諦めて、全部作りなおそう・・ってすでに稼働中のサービスだった場合なんか、そんなに簡単にできないですよね・・
ってことで。どうやればいいのか？
回避策
ユニーク索引を作成する
ひとまずユニーク索引を作成します。複合ユニーク索引を作成するわけですが、その際に、レンジに含めたいカラムと、プライマリーキーを加えてください
[code lang=text] create unique index ix01_test_ptable on test_ptable (id,updated_at) ; [/code]
プライマリーキーをDROPする
既存のプライマリーキーを先ほどと同様の手順でDROPします
[code lang=text]
ALTER TABLE test_ptable DROP PRIMARY KEY;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
[/code]
どうでしょうか？こんどは、DROPできたかと思います
再度プライマリーキーを作成する
新規作成し直すプライマリーキーは、updated_at を含めます
[code lang=text]
ALTER TABLE test_ptable ADD PRIMARY KEY (id,updated_at) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
[/code]
索引の削除
別に削除してもしなくてもいいのですが、同様のカラム構成で、プライマリキーと索引があるというのも気持ち悪いので、索引のほうは消してしまいましょう
- 現在の状態
[code lang=sql]
CREATE TABLE test_ptable (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'UTC-DATE',
domain varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com',
PRIMARY KEY (id,updated_at),
UNIQUE KEY ix01_test_ptable (id,updated_at)
) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8
[/code]
- 索引をDROPします
[code lang=sql] drop index ix01_test_ptable on test_ptable ; [/code]
- 削除後の結果
[code lang=sql]
CREATE TABLE test_ptable (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'UTC-DATE',
domain varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com',
PRIMARY KEY (id,updated_at)
) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8 ;
[/code]
レンジパーティショニング化を再度チャレンジ
こんどはうまくいきましたね。
[code lang=sql]
alter table test_ptable
PARTITION BY RANGE COLUMNS(updated_at)
(
PARTITION p20140901 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB,
PARTITION p20141001 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
PARTITION p20141101 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB
) ;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
[/code]
レンジパーティショニング化後のテーブル定義
こんな感じに変わったかと思います。
[code lang=text]
CREATE TABLE test_ptable (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'UTC-DATE',
domain varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com',
PRIMARY KEY (id,updated_at)
) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8
/!50500 PARTITION BY RANGE COLUMNS(updated_at)
(PARTITION p20140901 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB,
PARTITION p20141001 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
PARTITION p20141101 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB) /
[/code]
如何でしょうか？お試しください。
作業前は予めテーブルはバックアップとっておいてくださいね
