MySQL でパーティションを設定したテーブルの PRIMARY KEY を変更しようとした時の話
とあるテーブルで複合 PRIMARY KEY を使用していたけれど,事情により変更する必要が出てきた. 例えば,PRIMARY KEY が GraphId (AUTO INCREMENT) と UserId の複合キーであったけれど,GraphId (AUTO INCREMENT) のみにしたくなったわけである. 元々このテーブルはアクセス頻度が高いので,パーティションも区切ってあった. パーティションに用いたカラムは UserId であり,そのため仕方なく PRIMARY KEY に追加したのだけれど,おかげで GraphId が UNIQUE にならず,大変不便であった.
取り敢えず普段使いの Heidi SQL で
ALTER TABLE
MyTable
DROP PRIMARY KEY, ADD PRIMARY KEY (GraphId
);
とすると,1503 A primary key must include all columns in the table's partitioning function で失敗する. (ちなみに innoDB です.)
仕方なく,SQL文を直打ちして,以下のように対応したところ PRIMARY KEY を変更できた.
まずはパーティショニングを解除する.REMOVE PARTITIONING の代わりに DROP してしまうと大変な悲劇が起こる.
ALTER TABLE TrendGraphTable REMOVE PARTITIONING;
あまり深く考えずに以下のSQL文を打つと,1041 out of memory エラーが発生した.ちなみにテーブルのサイズは 123MB,32万件.
ALTER TABLE
MyTable
DROP PRIMARY KEY, ADD PRIMARY KEY (GraphId
);そんなときは
ALTER TABLE
MyTable
DROP PRIMARY KEY, ADD PRIMARY KEY (GraphId
), ALGORITHM = COPY;GraphId を AUTO INCREMENT に再設定する.