how to make mysql database schema to be compatible with h2 database

Question

I am currently using mysql as my database and use flyway to manage database schema. All my unit tests are running against mysql and they are running really slow with adding more unit tests. Now I want to change the database from mysql to h2 memory database in unit tests. Below is my setting for h2 db connection:

#Datasource
spring.datasource.url=jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE;DATABASE_TO_UPPER=true
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.default-transaction-isolation-level=1

When I run flywayMigrate, I got some sql errors. Below is one example, this sql is used to create a table on mysql but failed to run on h2.

CREATE TABLE `file_storage` (
  `id` BIGINT(64) NOT NULL AUTO_INCREMENT,
  `file_name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
DEFAULT CHARACTER SET = utf8;

below is the error I got from h2. I don't know what wrong with my sql. Is there a way for h2 to accept mysql database schema?

Execution failed for task ':dbschema:flywayMigrate'.
> Error occurred while executing flywayMigrate

  Migration V2016_02_26_12_59__create_file_storage.sql failed
  -----------------------------------------------------------
  SQL State  : 42000
  Error Code : 42000
  Message    : Syntax error in SQL statement "CREATE TABLE ""FILE_STORAGE"" (
    ""ID"" BIGINT(64) NOT NULL AUTO_INCREMENT,
    ""FILE_NAME"" VARCHAR(45) NULL,
    PRIMARY KEY (""ID""))
  DEFAULT CHARACTER[*] SET = UTF8 "; SQL statement:
  CREATE TABLE `file_storage` (
    `id` BIGINT(64) NOT NULL AUTO_INCREMENT,
    `file_name` VARCHAR(45) NULL,
    PRIMARY KEY (`id`))
  DEFAULT CHARACTER SET = utf8 [42000-190]
  Location   : db/migration/V2016_02_26_12_59__create_file_storage.sql (/Users/yzzhao/dev/cooltoo/cooltoo_backend/dbschema/build/resources/main/db/migration/V2016_02_26_12_59__create_file_storage.sql)
  Line       : 1
  Statement  : CREATE TABLE `file_storage` (
    `id` BIGINT(64) NOT NULL AUTO_INCREMENT,
    `file_name` VARCHAR(45) NULL,
    PRIMARY KEY (`id`))
  DEFAULT CHARACTER SET = utf8

  Syntax error in SQL statement "CREATE TABLE ""FILE_STORAGE"" (
    ""ID"" BIGINT(64) NOT NULL AUTO_INCREMENT,
    ""FILE_NAME"" VARCHAR(45) NULL,
    PRIMARY KEY (""ID""))
  DEFAULT CHARACTER[*] SET = UTF8 "; SQL statement:
  CREATE TABLE `file_storage` (
    `id` BIGINT(64) NOT NULL AUTO_INCREMENT,
    `file_name` VARCHAR(45) NULL,
    PRIMARY KEY (`id`))
  DEFAULT CHARACTER SET = utf8 [42000-190]

EDIT

I have hundreds of sql scripts which is running fine in mysql. So I don't want to change anything in these scripts. Is there a way to allow h2 accepts mysql script?

Answer 1

According to this description, you may try to use your H2 database in MySQL Compatibility Mode, by setting it in the connection string as MODE=MySQL. Here is exactly what is said about it:

To use the MySQL mode, use the database URL jdbc:h2:~/test;MODE=MySQL or the SQL statement SET MODE MySQL.

• When inserting data, if a column is defined to be NOT NULL and NULL is inserted, then a 0 (or empty string, or the current timestamp for timestamp columns) value is used. Usually, this operation is not allowed and an exception is thrown.

• Creating indexes in the CREATE TABLE statement is allowed using INDEX(..) or KEY(..). Example: create table test(id int primary key, name varchar(255), key idx_name(name));

• Meta data calls return identifiers in lower case.

• When converting a floating point number to an integer, the fractional digits are not truncated, but the value is rounded.

• Concatenating NULL with another value results in the other value.

Text comparison in MySQL is case insensitive by default, while in H2 it is case sensitive (as in most other databases). H2 does support case insensitive text comparison, but it needs to be set separately, using SET IGNORECASE TRUE. This affects comparison using =, LIKE, REGEXP.

Answer 2

Your issue can be seen with your example

CREATE TABLE `file_storage`
(
   'id` BIGINT(64) NOT NULL AUTO_INCREMENT, 
   `file_name` VARCHAR(45) NULL, 
   PRIMARY KEY (`id`)
)
DEFAULT CHARACTER SET = utf8;

The last line "DEFAULT CHARACTER SET = utf8" is setting a mySQL table option. H2 does not have such an option at either the table or schema level as it operates using Unicode at all times.

If you have a lot of SQL DDL statements that have been written over the years for MySQL you are likely to see a lot of such issues.