Flyway + H2 with MySQL Compatibility Mode


[Skinny Framework](http://skinny-framework.org/)で開発していて、そろそろ *model* のテストを書きたいなぁ、というか、書かなきゃ・・・と思ったのがキッカケです。

* 手軽さと本番環境を考慮して [H2 Database](http://www.h2database.com/) を *MySQL Compatibility Mode* で
* テスト用DBのセットアップには既存の [Flyway](http://flywaydb.org/) マイグレーションファイルで

などと、とりあえず方針はすぐに決まったのですが。
動くようになるまで、かなり時間がかかりました・・・

## バージョン情報

* Skinny Framework 2.0.1
* H2 Database 1.4.190
* Flyway 3.2.1


## テストDBの設定

テストなので *in-memory* な設定にします。

**src/main/resources/application.conf**

```
test {
  db {
    default {
      driver="org.h2.Driver"
      url="jdbc:h2:mem:test;MODE=MySQL"
      user="sa"
      password="sa"
      poolInitialSize=2
      poolMaxSize=10
      poolValidationQuery="select 1 as one"
      poolFactoryName="commons-dbcp2"
    }
  }
}
```

## テストクラス用トレイトの準備

テストクラスでは以下のようなトレイトを用意してミックスインしています。

```scala
package model

import org.scalatest.{Suite, BeforeAndAfterAll}
import skinny._

trait TestDBSettings extends BeforeAndAfterAll with DBSettings { this: Suite =>

  override protected def beforeAll(): Unit = dbmigration.DBMigration.migrate()
}
```

コレが正しい方法なのかは分かりません。
テストクラスが増えると何度もDBマイグレーションしようとするので、もっと良い方法があると思うのですが・・・。

## "public" スキーマ見つからない問題

で、この状態でテストを実行すると以下のようなエラーが出てしまいます。

```bash:
$ ./skinny testOnly model.HogeSpec
...

[info] HogeSpec:
[info] Exception encountered when attempting to run a suite with class name: model.HogeSpec *** ABORTED ***
[info]   org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Script failed
[info] -------------
[info] SQL State  : 90079
[info] Error Code : 90079
[info] Message    : スキーマ "public" が見つかりません
[info] Schema "public" not found; SQL statement:
[info] CREATE TABLE "public"."schema_version" (
[info]     "version_rank" INT NOT NULL,
[info]     "installed_rank" INT NOT NULL,
[info]     "version" VARCHAR(50) NOT NULL,
[info]     "description" VARCHAR(200) NOT NULL,
[info]     "type" VARCHAR(20) NOT NULL,
[info]     "script" VARCHAR(1000) NOT NULL,
[info]     "checksum" INT,
[info]     "installed_by" VARCHAR(100) NOT NULL,
[info]     "installed_on" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
[info]     "execution_time" INT NOT NULL,
[info]     "success" BOOLEAN NOT NULL
[info] ) [90079-190]
[info] Line       : 17
[info] Statement  : CREATE TABLE "public"."schema_version" (
[info]     "version_rank" INT NOT NULL,
[info]     "installed_rank" INT NOT NULL,
[info]     "version" VARCHAR(50) NOT NULL,
[info]     "description" VARCHAR(200) NOT NULL,
[info]     "type" VARCHAR(20) NOT NULL,
[info]     "script" VARCHAR(1000) NOT NULL,
[info]     "checksum" INT,
[info]     "installed_by" VARCHAR(100) NOT NULL,
[info]     "installed_on" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
[info]     "execution_time" INT NOT NULL,
[info]     "success" BOOLEAN NOT NULL
[info] )
[info]   at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
[info]   at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.createIfNotExists(MetaDataTableImpl.java:93)
[info]   at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:100)
[info]   at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:158)
[info]   at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156)
[info]   at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
[info]   at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:156)
[info]   at org.flywaydb.core.Flyway$1.execute(Flyway.java:1059)
[info]   at org.flywaydb.core.Flyway$1.execute(Flyway.java:1006)
[info]   at org.flywaydb.core.Flyway.execute(Flyway.java:1418)
[info]   ...
[info]   Cause: org.h2.jdbc.JdbcSQLException: スキーマ "public" が見つかりません
[info] Schema "public" not found; SQL statement:
[info] CREATE TABLE "public"."schema_version" (
[info]     "version_rank" INT NOT NULL,
[info]     "installed_rank" INT NOT NULL,
[info]     "version" VARCHAR(50) NOT NULL,
[info]     "description" VARCHAR(200) NOT NULL,
[info]     "type" VARCHAR(20) NOT NULL,
[info]     "script" VARCHAR(1000) NOT NULL,
[info]     "checksum" INT,
[info]     "installed_by" VARCHAR(100) NOT NULL,
[info]     "installed_on" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
[info]     "execution_time" INT NOT NULL,
[info]     "success" BOOLEAN NOT NULL
[info] ) [90079-190]
[info]   at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
[info]   at org.h2.message.DbException.get(DbException.java:179)
[info]   at org.h2.message.DbException.get(DbException.java:155)
[info]   at org.h2.command.Parser.getSchema(Parser.java:655)
[info]   at org.h2.command.Parser.getSchema(Parser.java:662)
[info]   at org.h2.command.Parser.parseCreateTable(Parser.java:5791)
[info]   at org.h2.command.Parser.parseCreate(Parser.java:4167)
[info]   at org.h2.command.Parser.parsePrepared(Parser.java:349)
[info]   at org.h2.command.Parser.parse(Parser.java:304)
[info]   at org.h2.command.Parser.parse(Parser.java:276)
[info]   ...
```

[[StackOverflow] Schema related problems with Flyway / Spring and H2 embedded database](http://stackoverflow.com/questions/14972408/schema-related-problems-with-flyway-spring-and-h2-embedded-database) によると、H2 が初期処理で生成するデフォルトのスキーマが *PUBLIC* なのに対して Flyway は *public* でアクセスしてしまうという、まさかの大文字小文字問題が原因とのこと。

かなり古い情報だったので期待してませんでしたが、ココに書かれているH2の[Execute SQL on Connection](http://www.h2database.com/html/features.html#execute_sql_on_connection)で *public* スキーマを作るという回避策を試してみました。

**src/test/resources/db/migration/test/init_tests.sql**

```sql:init_tests.sql
CREATE SCHEMA IF NOT EXISTS "public";
```

**src/main/resources/application.conf**

```
test {
  db {
    default {
      driver="org.h2.Driver"
      url="jdbc:h2:mem:test;MODE=MySQL;INIT=RUNSCRIPT FROM 'classpath:db/migration/test/init_tests.sql'"
      user="sa"
      password="sa"
      poolInitialSize=2
      poolMaxSize=10
      poolValidationQuery="select 1 as one"
      poolFactoryName="commons-dbcp2"
    }
  }
}
```

なんと！テストが動くようになりました！！疑ってスイマセン！！！

```bash:
$ ./skinny testOnly model.HogeSpec
...

[info] HogeSpec:
[info] findByMogeIdOrderByHogeIdDesc
[info] - should return expected hoges
[info] - should be empty
[info] Run completed in 1 second, 894 milliseconds.
[info] Total number of tests run: 2
[info] Suites: completed 1, aborted 0
[info] Tests: succeeded 2, failed 0, canceled 0, ignored 0, pending 0
[info] All tests passed.
[success] Total time: 2 s, completed 2015/12/16 20:46:49
```

でも、本当にこんなトリッキーな方法しかないんですかね・・・。

Flywayにスキーマ名を設定できればいいんじゃないかと思って[DBMigration.scala](https://github.com/skinny-framework/skinny-framework/blob/2.0.1/orm/src/main/scala/skinny/dbmigration/DBMigration.scala)を見てみたんですが *locations* 以外は設定できないみたいです。

## SQLステートメントに文法エラーあります問題

場合によっては、前述の回避策を実施すると以下のようなエラーが出力されるようになります。

```bash:
$ ./skinny testOnly model.HogeSpec
...

[info] HogeSpec:
[info] Exception encountered when attempting to run a suite with class name: model.HogeSpec *** ABORTED ***
[info]   org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Migration V1__hoge.sql failed
[info] ------------------------------
[info] SQL State  : 42000
[info] Error Code : 42000
[info] Message    : SQLステートメントに文法エラーがあります "CREATE TABLE ""HOGES"" (
[info]   ""HOGE_ID"" BIGINT NOT NULL AUTO_INCREMENT,
[info]   ""NAME"" VARCHAR(255)  NOT NULL,
[info]   ""EMAIL"" VARCHAR(255)  NOT NULL,
[info]   ""URL"" VARCHAR(255)  DEFAULT NULL,
[info]   ""COMMENT"" TEXT  DEFAULT NULL,
[info]   ""CREATED_AT"" DATETIME DEFAULT NULL,
[info]   ""UPDATED_AT"" DATETIME DEFAULT NULL,
[info]   PRIMARY KEY (""HOGE_ID""),
[info]   UNIQUE KEY ""INDEX_HOGES_ON_EMAIL"" (""EMAIL"")
[info] )  ENGINE=INNODB DEFAULT CHARSET=UTF8 COLLATE[*]=UTF8_GENERAL_CI "
[info] Syntax error in SQL statement "CREATE TABLE ""HOGES"" (
[info]   ""HOGE_ID"" BIGINT NOT NULL AUTO_INCREMENT,
[info]   ""NAME"" VARCHAR(255)  NOT NULL,
[info]   ""EMAIL"" VARCHAR(255)  NOT NULL,
[info]   ""URL"" VARCHAR(255)  DEFAULT NULL,
[info]   ""COMMENT"" TEXT  DEFAULT NULL,
[info]   ""CREATED_AT"" DATETIME DEFAULT NULL,
[info]   ""UPDATED_AT"" DATETIME DEFAULT NULL,
[info]   PRIMARY KEY (""HOGE_ID""),
[info]   UNIQUE KEY ""INDEX_HOGES_ON_EMAIL"" (""EMAIL"")
[info] )  ENGINE=INNODB DEFAULT CHARSET=UTF8 COLLATE[*]=UTF8_GENERAL_CI "; SQL statement:
[info] CREATE TABLE `hoges` (
[info]   `hoge_id` bigint NOT NULL AUTO_INCREMENT,
[info]   `name` varchar(255)  NOT NULL,
[info]   `email` varchar(255)  NOT NULL,
[info]   `url` varchar(255)  DEFAULT NULL,
[info]   `comment` text  DEFAULT NULL,
[info]   `created_at` datetime DEFAULT NULL,
[info]   `updated_at` datetime DEFAULT NULL,
[info]   PRIMARY KEY (`hoge_id`),
[info]   UNIQUE KEY `index_hoges_on_email` (`email`)
[info] )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci [42000-190]
[info] Location   : db/migration/V1__hoge.sql (/Users/niwashun/Workspaces/hogemoge/target/dev/scala-2.11/classes/db/migration/V1__hoge.sql)
[info] Line       : 1
[info] Statement  : CREATE TABLE `hoges` (
[info]   `hoge_id` bigint NOT NULL AUTO_INCREMENT,
[info]   `name` varchar(255)  NOT NULL,
[info]   `email` varchar(255)  NOT NULL,
[info]   `url` varchar(255)  DEFAULT NULL,
[info]   `comment` text  DEFAULT NULL,
[info]   `created_at` datetime DEFAULT NULL,
[info]   `updated_at` datetime DEFAULT NULL,
[info]   PRIMARY KEY (`hoge_id`),
[info]   UNIQUE KEY `index_hoges_on_email` (`email`)
[info] )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
[info]   at org.flywaydb.core.internal.dbsupport.SqlScript.execute(SqlScript.java:117)
[info]   at org.flywaydb.core.internal.resolver.sql.SqlMigrationExecutor.execute(SqlMigrationExecutor.java:71)
[info]   at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:284)
[info]   at org.flywaydb.core.internal.command.DbMigrate$5.doInTransaction(DbMigrate.java:282)
[info]   at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
[info]   at org.flywaydb.core.internal.command.DbMigrate.applyMigration(DbMigrate.java:282)
[info]   at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:46)
[info]   at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:207)
[info]   at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:156)
[info]   at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
...

（壮大すぎて以下省略）
```

このエラーメッセージはマイグレーションファイルの記述量に比例するので、そのボリュームに圧倒されてしまうかもしれません。
私はコレが出た瞬間に心がポキっと折れました。
ですが、安心してください。進捗してますよ！（きっと）

今回のケースでは `ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci` が問題の箇所です。
ここを *mysqldump* Comment Directive (`/*!...*/;`) でコメントアウトして、H2から認識されないようにします。

```sql:V1__hoge.sql
CREATE TABLE `hoges` (
  `hoge_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255)  NOT NULL,
  `email` varchar(255)  NOT NULL,
  `url` varchar(255)  DEFAULT NULL,
  `comment` text  DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`hoge_id`),
  UNIQUE KEY `index_hoges_on_email` (`email`)
) /*! ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci */;
```

*mysqldump* でエクスポートしたDDLは編集することなく使用できるそうなので、マイグレーションファイルはエクスポートしたDDLからコピペした方が良さそうです。

[Flywayのドキュメント](http://flywaydb.org/documentation/database/mysql.html)にも記載されていますが、1行に複数の Comment Directive が含まれるケースには対応していないっぽいので、その点だけ注意です。

>**Limitations**
>
>* [Issue 558](https://github.com/flyway/flyway/issues/558) The parser currently doesn't support multiple comment directives on a single line.
Example: /*!50003 CREATE*/ /*!50017 DEFINER=\`...\`*/ /*!50003 TRIGGER EntityBeforeInsert ...


## 参考

* [[StackOverflow] Schema related problems with Flyway / Spring and H2 embedded database](http://stackoverflow.com/questions/14972408/schema-related-problems-with-flyway-spring-and-h2-embedded-database)
* [[StackOverflow] Using flyway with play in test mode: Schema “public” not found](http://stackoverflow.com/questions/22251656/using-flyway-with-play-in-test-mode-schema-public-not-found)
* [[Skinny Framework] Testing](http://skinny-framework.org/documentation/testing.html)
* [[GitHub Gist] Skinny Framework 1.0 Introduction in Japanese](https://gist.github.com/seratch/7382298#db-マイグレーション)