Tests fail to run with H2 database on Micronaut project with Flyway migrations

Question

I've got a unit test suite in a Micronaut 2.0.3 project that's supposed to run on an H2 DB. Since I started using Flyway for my DB migrations, I'm getting an error coming from H2 being unable to run the migrations.

The error is

Bean definition [javax.sql.DataSource] could not be loaded: Migration V1__schema.sql failed
-------------------------------
SQL State  : 42001
Error Code : 42001
Message    : Syntax error in SQL statement "CREATE DATABASE[*] IF NOT EXISTS MY_DB"; expected "OR, FORCE, VIEW, ALIAS, SEQUENCE, USER, TRIGGER, ROLE, SCHEMA, CONSTANT, DOMAIN, TYPE, DATATYPE, AGGREGATE, LINKED, MEMORY, CACHED, LOCAL, GLOBAL, TEMP, TEMPORARY, TABLE, SYNONYM, PRIMARY, UNIQUE, HASH, SPATIAL, INDEX"; SQL statement:
CREATE DATABASE if not exists my_db [42001-200]
Location   : db/migration/V1__schema.sql (/somelocation/build/resources/main/db/migration/V1__schema.sql)
Line       : 1
Statement  : CREATE DATABASE if not exists my_db

io.micronaut.context.exceptions.BeanInstantiationException: Bean definition [javax.sql.DataSource] could not be loaded: Migration V1__schema.sql failed
-------------------------------

My application.yml is configured to point at a MariaDB instance in Azure like so:

micronaut:
  application:
    name: myapp
  server:
    context-path: /myapp/api
    cors:
      enabled: true

endpoints:
  all:
    enabled: true
    sensitive: false

datasources:
  default:
    url: jdbc:mariadb://${ENV_DB_HOST_PORT:myurl.mariadb.database.azure.com}:${ENV_DB_PORT:3306}/${ENV_DB_SCHEMA:my_db}?useSSL=${ENV_DB_USE_SSL:true}&useUnicode=true&serverTimezone=Europe/London&autoReconnect=true
    driverClassName: org.mariadb.jdbc.Driver
    username: myusername
    password: mypassword
    dialect: MYSQL
    allow-pool-suspension: false
    connection-test-query: SELECT 1
    connection-timeout: 30000
    max-lifetime: 60000
    maximum-pool-size: 10
    minimum-idle: 1

flyway:
  datasources:
    default:
      enabled: true

But my application-test.yml points at the H2 DB for testing purposes:

datasources:
  default:
    url: jdbc:h2:mem:devDb;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE
    driverClassName: org.h2.Driver
    username: sa
    password: ''

jpa:
  default:
    properties:
      hibernate:
        hbm2ddl:
          auto: update

And my migration file V1__schema.sql looks like the following:

CREATE DATABASE if not exists my_db;
use my_db;

DROP TABLE IF EXISTS `preset`;
CREATE TABLE `preset` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `preset_name` varchar(30) NOT NULL,
    `preset_value` varchar(4000) DEFAULT NULL,
    PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `note`;
CREATE TABLE `note` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `code` varchar(30) NOT NULL,
    `text` varchar(4000) DEFAULT NULL,
    PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

The error is pretty much self explanatory

Message    : Syntax error in SQL statement "CREATE DATABASE[*] IF NOT EXISTS MY_DB";

CREATE DATABASE if not exists my_db;

According to H2 commands, CREATE DATABASE is not a valid command for H2. You probably need the command CREATE SCHEMA if not exists my_db;