pt-online-schema-changeで外部キーを削除する

MySQL tips

サービスを停止せずにデータベースリファクタリングする - Pepabo Tech Portal の作業をやっていたときに知ったことについて書いておく。

結論

pt-online-schema-change (pt-osc)で外部キーを削除するときは

  • そのキー名の先頭に_を付与して--alterに渡す
  • すでに_が2つ付与されていれば、そのキー名先頭の_を2つ削って--alterに渡す
# 外部キー名がfoos_ibfk_1のとき
$ pt-online-schema-change --alter "DROP FOREIGN KEY _foos_ibfk_1" ...

# 外部キー名が_foos_ibfk_1のとき
$ pt-online-schema-change --alter "DROP FOREIGN KEY __foos_ibfk_1" ...

# 外部キー名が__foos_ibfk_1のとき
$ pt-online-schema-change --alter "DROP FOREIGN KEY foos_ibfk_1" ...

なぜか

外部キーを持つテーブルのスキーマを変更するためにpt-oscを使うと、自動で外部キーの名前が変更されるから。

pt-oscはスキーマに変更を加えるテーブル（旧テーブルと呼ぶ）に対して、スキーマを変更済みの新テーブルを新しく作り、トリガーを使って旧テーブルから新テーブルへレコードをコピーする。旧テーブルが外部キーを持つなら、新テーブルも同じ外部キーを持つことになる。一方、MySQLでは、外部キーなどの制約の名前はデータベース内で一意でなければならない1。pt-oscで新テーブルを作るときにそのまま外部キーをコピーしてしまうと、この決まりを守れない。そこで、外部キーを持つテーブルのスキーマをpt-oscで変更しようとすると、外部キーの変更有無に関わらず、外部キー名を変更するようになっている。

pt-oscは外部キー名の先頭にアンダースコアを付与したものを新テーブルの外部キー名とする。この方式で外部キーを持つテーブルのスキーマを繰り返し変更するとアンダースコアが増えていってしまう2。そこで、アンダースコアは2個を上限として、さらにスキーマを変更するとアンダースコアなしに戻るようになっている3。

よって、pt-oscで外部キーを削除するときは、新テーブルを作成したあと--alterに渡されたオプションをもとに外部キーをDROPするので、新テーブルでの外部キー名を指定する必要がある。

参考

  1. https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html の「CONSTRAINT symbol 句が指定されている場合、symbol 値 (使用されている場合) はデータベース内で一意である必要があります」↩

  2. 過去はそのような実装だったようだ↩

  3. https://github.com/percona/percona-toolkit/blob/v3.3.1/bin/pt-online-schema-change#L10759-L10777↩

kymmt