FlywayでDBスキーマのマイグレーションをしてみた

flyway-logo

Background

突然引き継ぐことになった案件では、フレームワークにデータベースマイグレートの仕組みがなく、 ORM も使っていなかったので、後付でデータベースマイグレーションを用意してあげる必要があった。

Java で書かれた Flyway を使うと、いくつかのルールを覚え、ベタの SQL を書くだけで、運用で発生する基本的なマイグレートを実現出来たので、使い方をメモ。

環境

  • Ubuntu 10.04
  • MySQL 5.1
  • Flyway 2.2.1
  • Java(JDK7)

Flyway は Java のコードから呼び出せたり、 Ant/Maven/Gradle といったツールと連携出来たりと jvm 系エコシステムと親和性が高い。今回は、非 Java システムということと、僕自身 Java には疎いので、コマンドライン版の Flyway を利用。

Installation

Install Java

flyway は Java で書かれているので Java をインストール。

http://www.oracle.com/technetwork/java/javase/downloads/index.html

環境変数 JAVA_HOME が設定されていれば $JAVA_HOME/bin/java、設定されていなければ PATH の通ったディレクトリにある java が利用される。
今回は JDK 7 を利用した。

Install flyway

ダウンロードサイト http://flywaydb.org/getstarted/download.html から Command-line Tool をダウンロード

$ wget http://repo1.maven.org/maven2/com/googlecode/flyway/flyway-commandline/2.2.1/flyway-commandline-2.2.1.tar.gz
$ tar zxfv flyway-commandline-2.2.1.tar.gz
$ cd flyway-2.2.1/

Install JDBC

Java から MySQL と通信するために JDBC ドライバーをインストール

http://dev.mysql.com/usingmysql/java/

ダウンロードした jar ファイル(mysql-connector-java-5.1.27-bin.jar)を $FLYWAY_HOME/jars ディレクトリに置く。

Configure Flyway

設定ファイル $FLYWAY_HOME/conf/flyway.properties を編集し、データベースの接続情報を設定。

flyway.url=jdbc:mysql://host_name/schema_name
flyway.user=DB user
flyway.password=DB password

Connect to Database

flyway のログレベルをデバッグ(-X)にしてデータベースに接続してみる。

$ ./flyway -X info
Flyway (Command-line Tool) v.2.2.1
DEBUG: Adding location to classpath: /home/jsmith/dev/flyway-2.2.1/bin/../jars/h2-1.3.170.jar
DEBUG: Adding location to classpath: /home/jsmith/dev/flyway-2.2.1/bin/../jars/mysql-connector-java-5.1.27-bin.jar
DEBUG: Database: MySQL 5.1
DEBUG: DDL Transactions Supported: false
...
+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| No migrations found                                                         |
+----------------+----------------------------+---------------------+---------+

データベース設定の接続情報を間違っていると、トレースバックが表示される。。

$ ./flyway -X info
Flyway (Command-line Tool) v.2.2.1

DEBUG: Adding location to classpath: /home/jsmith/dev/flyway-2.2.1/bin/../jars/h2-1.3.170.jar
DEBUG: Adding location to classpath: /home/jsmith/dev/flyway-2.2.1/bin/../jars/mysql-connector-java-5.1.27-bin.jar
ERROR: Unexpected error
com.googlecode.flyway.core.api.FlywayException: Unable to obtain Jdbc connection from DataSource
...

運用ワークフロー

Flyway を使ったデータベースマイグレーションのフローは、オフィシャルドキュメントのページ “How Flyway Works” に書かれている。

http://flywaydb.org/getstarted/howFlywayWorks.html

具体的な手順は以下。

1.マイグレート情報の初期化

init コマンドを実行すると、データベースにマイグレート情報を管理する専用のテーブル(schema_version)が作られ、初期化が完了する。

$ ./flyway -initVersion=0 init
Flyway (Command-line Tool) v.2.2.1

Creating Metadata table: `flyway`.`schema_version`
Schema initialized with version: 0

MySQL で確認すると

mysql> desc schema_version;
+----------------+---------------+------+-----+-------------------+-------+
| Field          | Type          | Null | Key | Default           | Extra |
+----------------+---------------+------+-----+-------------------+-------+
| version_rank   | int(11)       | NO   | MUL | NULL              |       |
| installed_rank | int(11)       | NO   | MUL | NULL              |       |
| version        | varchar(50)   | NO   | PRI | NULL              |       |
| description    | varchar(200)  | NO   |     | NULL              |       |
| type           | varchar(20)   | NO   |     | NULL              |       |
| script         | varchar(1000) | NO   |     | NULL              |       |
| checksum       | int(11)       | YES  |     | NULL              |       |
| installed_by   | varchar(100)  | NO   |     | NULL              |       |
| installed_on   | timestamp     | NO   |     | CURRENT_TIMESTAMP |       |
| execution_time | int(11)       | NO   |     | NULL              |       |
| success        | tinyint(1)    | NO   | MUL | NULL              |       |
+----------------+---------------+------+-----+-------------------+-------+
11 rows in set (0.01 sec)

mysql> select * from schema_version \G
*************************** 1. row ***************************
  version_rank: 1
installed_rank: 1
       version: 0
   description: << Flyway Init >>
          type: INIT
        script: << Flyway Init >>
      checksum: NULL
  installed_by: root
  installed_on: 2013-11-09 19:11:10
execution_time: 0
       success: 1
1 row in set (0.00 sec)

2.マイグレート SQL を作成

sql ディレクトリにマイグレート用の SQL を追加する。
マイグレート SQL の処理順は開発者が明示的にナンバリングして制御する。
ファイル名は “Vバージョン番号__description.sql” という命名規則に従う。

$ cat sql/V1__Create_person_table.sql
create table PERSON (
  ID int not null,
  NAME varchar(100) not null
);

$ ./flyway info
Flyway (Command-line Tool) v.2.2.1

+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 0              | << Flyway Init >>          | 2013-11-09 19:11:10 | Success |
| 1              | Create person table        |                     | Pending |
+----------------+----------------------------+---------------------+---------+

ファイルを追加しただけでは Pending 状態。

3.マイグレートの実行

migrate コマンドでデータベースに取り込む。

$ ./flyway migrate
Flyway (Command-line Tool) v.2.2.1

Current version of schema `flyway`: 0
Migrating schema `flyway` to version 1
Successfully applied 1 migration to schema `flyway` (execution time 00:00.054s).

$ ./flyway info
Flyway (Command-line Tool) v.2.2.1

+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 0              | << Flyway Init >>          | 2013-11-09 19:20:27 | Success |
| 1              | Create person table        | 2013-11-09 19:20:34 | Success |
+----------------+----------------------------+---------------------+---------+

4.マイグレート実際時のリカバリ

マイグレートはナンバリング順に実行される。トランザクションはファイル単位。失敗するまで順次実行される。
マイグレートが成功すると、SQL ファイルは Success 状態に、失敗すると Failed 状態になる。

正常なSQL(Add people OK)とシンタックスエラーのSQL(Add people NG)を追加

$ ./flyway info
Flyway (Command-line Tool) v.2.2.1

+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 0              | << Flyway Init >>          | 2013-11-09 19:20:27 | Success |
| 1              | Create person table        | 2013-11-09 19:20:34 | Success |
| 2              | Add people OK              |                     | Pending |
| 3              | Add people NG              |                     | Pending |
+----------------+----------------------------+---------------------+---------+

これら SQL ファイルでマイグレートを実行

$ ./flyway migrate
Flyway (Command-line Tool) v.2.2.1

Current version of schema `flyway`: 1
Migrating schema `flyway` to version 2
Migrating schema `flyway` to version 3
ERROR: com.googlecode.flyway.core.api.FlywayException: Migration of schema `flyway` to version 3 failed! Please restore backups and roll back database and code!
ERROR: Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
ERROR: Occured in sun.reflect.NativeConstructorAccessorImpl.newInstance0() at line -2
$ ./flyway info
Flyway (Command-line Tool) v.2.2.1

+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 0              | << Flyway Init >>          | 2013-11-09 19:20:27 | Success |
| 1              | Create person table        | 2013-11-09 19:20:34 | Success |
| 2              | Add people OK              | 2013-11-09 19:25:51 | Success |
| 3              | Add people NG              | 2013-11-09 19:25:51 | Failed  |
+----------------+----------------------------+---------------------+---------+

Version 3 の実行に失敗し、State が Failed となっている。
Failed 状態のスクリプトを再実行するには

  • $ ./flyway repair で状態を Pending に戻し
  • マイグレート用 SQL を修正する

Failed 状態のままマイグレートファイルだけを修正して再実行しても、 repair を促されるだけでマイグレートできない。

$ ./flyway migrate
Flyway (Command-line Tool) v.2.2.1

Current version of schema `flyway`: 3
ERROR: com.googlecode.flyway.core.api.FlywayException: Migration of schema `flyway` to version 3 failed! Please restore backups and roll back database and code!
ERROR: Caused by: com.googlecode.flyway.core.api.FlywayException: Migration of schema `flyway` to version 3 failed! Please restore backups and roll back database and code!
ERROR: Occured in com.googlecode.flyway.core.command.DbMigrate.migrate() at line 199

repair して State を Pending にした後で migrate を再実行する。

$ ./flyway repair
Flyway (Command-line Tool) v.2.2.1

Metadata table `flyway`.`schema_version` successfully repaired (execution time 00:00.001s).
Manual cleanup of the remaining effects the failed migration may still be required.
$ ./flyway info
Flyway (Command-line Tool) v.2.2.1

+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 0              | << Flyway Init >>          | 2013-11-09 19:20:27 | Success |
| 1              | Create person table        | 2013-11-09 19:20:34 | Success |
| 2              | Add people OK              | 2013-11-09 19:25:51 | Success |
| 3              | Add people NG              |                     | Pending |
+----------------+----------------------------+---------------------+---------+
$ ./flyway migrate
Flyway (Command-line Tool) v.2.2.1

Current version of schema `flyway`: 2
Migrating schema `flyway` to version 3
Successfully applied 1 migration to schema `flyway` (execution time 00:00.055s).
$ ./flyway info
Flyway (Command-line Tool) v.2.2.1

+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 0              | << Flyway Init >>          | 2013-11-09 19:20:27 | Success |
| 1              | Create person table        | 2013-11-09 19:20:34 | Success |
| 2              | Add people OK              | 2013-11-09 19:25:51 | Success |
| 3              | Add people NG              | 2013-11-09 19:28:32 | Success |
+----------------+----------------------------+---------------------+---------+

無事取り込まれた。

参照 : http://flywaydb.org/documentation/faq.html#repair

以上が flyway を使った一連のワークフロー。

フォルダ階層

フォルダ階層は以下

$ tree
.
|-- bin
|   |-- flyway-commandline-2.2.1.jar
|   `-- flyway-core-2.2.1.jar
|-- conf
|   `-- flyway.properties
|-- flyway
|-- flyway.cmd
|-- jars
|   |-- mysql-connector-java-5.1.27-bin.jar
|   `-- put-your-jdbc-driver-and-java-migration-jars-here.txt
`-- sql
    |-- V1__Create_person_table.sql
    `-- put-your-sql-migrations-here.txt

初期設定が終わったあとは SQL フォルダにガンガンマイグレートファイルを突っ込んでいけば OK

オプションの指定

オプションは設定ファイル(conf/flyway.properties)と実行時 flyway -key=value の2通りで指定できる。
コマンドラインと設定ファイルで指定が異なる場合、コマンドラインのものが採用される。

マイグレーションファイルの命名規則

マイグレーションファイルの命名規則は次の URL を参照

http://flywaydb.org/documentation/migration/sql.html

  • prefix (Configurable, default: V)
  • version (Dots or underscores separate the parts, you can use as many parts as you like)
  • separator (Two underscores)
  • description (Underscores or spaces separate the words)
  • suffix (Configurable, default: .sql)

SqlMigrationNaming

バージョンがかぶっていない限り、番号が飛んでいても構わない。

  • V1__test.sql # version => 1
  • V1.1__test.sql # version => 1.1
  • V1.1.2__test.sql # version => 1.1.2
  • V1_1_3__test.sql # version => 1.1.3

などはすべて有効

また、description には日本語も使用可能。

V1.3__日本語の概要.sql を追加して確認

$ ./flyway  info
Flyway (Command-line Tool) v.2.2.1

+----------------+---------------------------+---------------------+---------+
| Version        | Description               | Installed on        | State   |
+----------------+---------------------------+---------------------+---------+
| 0              | << Flyway Init >>         | 2013-11-08 17:33:18 | Success |
| 1.1            | Create person table       | 2013-11-08 17:33:40 | Success |
| 1.2            | Create person table       | 2013-11-08 17:33:40 | Success |
| 1.3            | 日本語の概要                    |                     | Pending |
+----------------+---------------------------+---------------------+---------+

見ての通り、マルチバイト文字を含めると桁ズレしてしまう。

運用で役に立つかもしれない TIP

デバッグモード

-Xオプションでデバッグレベルのログも出力される。余計に出力されて困ることはほとんどないだろうから、基本的に -X で実行するのがよい。

$ ./flyway -X migrate
Flyway (Command-line Tool) v.2.2.1

DEBUG: Adding location to classpath: /home/jsmith/dev/flyway-2.2.1/bin/../jars/h2-1.3.170.jar
DEBUG: Adding location to classpath: /home/jsmith/dev/flyway-2.2.1/bin/../jars/mysql-connector-java-5.1.27-bin.jar
DEBUG: Database: MySQL 5.1
DEBUG: DDL Transactions Supported: false
DEBUG: Schema: schema_name
DEBUG: Schema `schema_name` already exists. Skipping schema creation.
DEBUG: No upgrade to the Flyway 2.0 format necessary for metadata table `schema_name`.`schema_version`
DEBUG: No metadata table upgrade to the Flyway 2.0.2 format necessary
DEBUG: No metadata table upgrade to the Flyway 2.1 format necessary
DEBUG: Database: MySQL 5.1
DEBUG: Locking table `schema_name`.`schema_version`...
DEBUG: Lock acquired for table `schema_name`.`schema_version`
DEBUG: Spring Jdbc available: false
DEBUG: Scanning for filesystem resources at '/home/jsmith/dev/flyway-2.2.1/bin/../sql' (Prefix: 'V', Suffix: '.sql')
DEBUG: Scanning for resources in path: /home/jsmith/dev/flyway-2.2.1/bin/../sql (/home/jsmith/dev/flyway-2.2.1/bin/../sql)
DEBUG: Filtering out resource: /home/jsmith/dev/flyway-2.2.1/bin/../sql/put-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
...

バージョンを戻す

一直線にシーケンスにマイグレートスクリプトが適用されるだけ。逆戻りは出来ない。

参照 : http://flywaydb.org/documentation/faq.html#downgrade

あとから追加したマイグレートファイルを実行

バージョン 1と2のマイグレートファイルを実行すると、最新のバージョンは 2。次回 migrate 実行時にはバージョン番号が 2より大きいファイルのみが実行される。

この状態でバージョン 1.1 のファイルを追加すると State が Ignored として扱われる。

$ ./flyway info
Flyway (Command-line Tool) v.2.2.1

+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 0              | << Flyway Init >>          | 2013-11-08 17:33:18 | Success |
| 1.0            | Create person table        | 2013-11-08 17:33:40 | Success |
| 1.1            | hotfix                     |                     | Ignored |
| 2.0            | Create dept table          | 2013-11-08 18:40:03 | Success |
+----------------+----------------------------+---------------------+---------+

Ignored のファイルも実行したいときは outOfOrder=true を指定して実行する。

データベースの再構築

新規案件の開発時などには初期化SQLを用意して、データベースを都度再構築することが有る。
そういう時は $ flyway clean で一度全テーブルをクリアし、 $ flyway migrate で初期化SQLをすべて実行すればよい。

特定のバージョンまで実行

migrate 時には前回実行したバージョンより大きいすべてのファイルが実行される。
特定のバージョンまでしか実行したくない場合は target オプションを利用する。

$ ./flyway -target=11
...
$ ./flyway info
+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 0              | << Flyway Init >>          | 2013-11-09 17:21:35 | Success |
| 1              | a                          | 2013-11-09 17:21:36 | Success |
| 4              | b                          | 2013-11-09 17:37:53 | Success |
| 5              | c                          | 2013-11-09 17:37:53 | Success |
| 11             | d                          | 2013-11-09 17:45:32 | Success |
| 12             | e                          |                     | >Target |
+----------------+----------------------------+---------------------+---------+

Version 12 は Target より大きいため実行されない。State が >Target となっている。

Production でデプロイ時にこのオプションを使うのは事故のもと。
デプロイ用のコードベースには本番で実行して良いマイグレートファイルだけ突っ込み、常に最新のバージョンまで実行すべし。

init処理について

$ flyway init をやらずいきなり $ flyway migrateとしても、flyway 用テーブルの作成とマイグレーションスクリプトの実行は行われる。
init 処理をスキップすると schema_version テーブルには init 処理の履歴な残らない。
$ ./flyway -initVersion=0 init のように initVersion を明示的に 0 にしないとデフォルトではバージョン 1 が初期化処理に割り振られ、混乱のもとになるので、特別な初期化バージョンを指定,しない限り、 $ flyway init はやらないほうが良い。

初期化バージョンのオフセットを指定

もともとマイグレーションファイルを用意していたけど、データベースの引っ越しやら何らやで、特定のバージョン以降のマイグレーションファイルを適用したいことが発生するかもしれない。
そういう時は、運用を間違えているので、新しいデータベースに対してまたマイグレーションファイルを1から用意すべし。

何らかの理由により特定のバージョン以降から適用して運用するはめになった場合は、新しいデータベースに対して initVersion を指定して flyway を初期化する。初期化バージョンより古いマイグレーションファイルは、ステータスが PreInit として表示され、 migrate 時に実行されない。outOfOrder=true を指定しても実行されない。

$ ./flyway info
Flyway (Command-line Tool) v.2.2.1

+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 1              | V1                         |                     | Pending |
| 3              | V3                         |                     | Pending |
| 5              | V5                         |                     | Pending |
+----------------+----------------------------+---------------------+---------+
$ ./flyway -initVersion=2 init
Flyway (Command-line Tool) v.2.2.1

Creating Metadata table: `flyway2`.`schema_version`
Schema initialized with version: 2
$ ./flyway info
Flyway (Command-line Tool) v.2.2.1

+----------------+----------------------------+---------------------+---------+
| Version        | Description                | Installed on        | State   |
+----------------+----------------------------+---------------------+---------+
| 1              | V1                         |                     | PreInit |
| 2              | << Flyway Init >>          | 2013-11-09 19:40:16 | Success |
| 3              | V3                         |                     | Pending |
| 5              | V5                         |                     | Pending |
+----------------+----------------------------+---------------------+---------+

比較

Flyway と類似した機能を有する Liquibase との比較は、StackOverflow を参照
http://stackoverflow.com/questions/8418814/db-migration-tool-liquibase-or-flyway

オフィシャルサイトのトップページのセクション Feature Comparison には9種類のマイグレートツールの比較表がある。
主要機能な各種データベースへの対応状況が一目でわかる。(Pick the right tool for the job)

PHP のシステムなので、同じ PHP つながりということで phinx というのを試そうと思ったけど、PHP のバージョンが古すぎて別途インストールする必要があったのでやめた。

Resources

Tagged with: , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
  • RT @__apf__: How to write a research paper: a guide for software engineers & practitioners. docs.google.com/presentation/d… /cc @inwyrd 1 week ago
  • RT @HayatoChiba: 昔、自然と対話しながら数学に打ち込んだら何かを悟れるのではと思いたち、専門書1つだけ持ってパワースポットで名高い奈良の山奥に1週間籠ったことがある。しかし泊まった民宿にドカベンが全巻揃っていたため、水島新司と対話しただけで1週間過ぎた。 それ… 3 weeks ago
  • RT @googlecloud: Ever wonder what underwater fiber optic internet cables look like? Look no further than this deep dive w/ @NatAndLo: https… 3 weeks ago
  • @ijin UTC+01:00 な時間帯で生活しています、、、 6 months ago
  • RT @mattcutts: Google's world-class Site Reliability Engineering team wrote a new book: amazon.com/Site-Reliabili… It's about managing produc… 9 months ago
%d bloggers like this: