I am executing flyway using maven.
I have a sql(It has DDL create tables - DEPARTMENT , EMPLOYEE as shown below)
I run mvn compile flyway:migrate
Here is the console log.
......
[INFO] Successfully validated 3 migrations (execution time 00:00.097s)
[DEBUG] Schema "PUBLIC" already exists. Skipping schema creation.
[DEBUG] Locking table "PUBLIC"."schema_version"...
[DEBUG] Lock acquired for table "PUBLIC"."schema_version"
[INFO] Current version of schema "PUBLIC": 1
[INFO] Migrating schema "PUBLIC" to version 1.1 - department
[DEBUG] Found statement at line 2: CREATE TABLE Department (
ID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) PRIMARY KEY,
NAME VARCHAR(32) NOT NULL ,
DESCRIPTION VARCHAR(100)
)
[DEBUG] Found statement at line 8: CREATE TABLE EMPLOYEE (
ID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) PRIMARY KEY ,
NAME VARCHAR(100) NOT NULL ,
DEPARTMENTID INTEGER FOREIGN KEY REFERENCES PUBLIC.DEPARTMENT(ID)
)
[DEBUG] Executing SQL: CREATE TABLE Department (
ID INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 1) PRIMARY KEY,
NAME VARCHAR(32) NOT NULL ,
DESCRIPTION VARCHAR(100)
)
The execution hangs after first create table i.e Department table. Then I kill using ctrl+C I see schema_version and Department table alone gets created.
I tried other ways of create table i.e without ID Generation , adding ';' at end , adding GO after each CREATE TABLE but it did not help.
The same create table sql runs successfully using Squirrel SQL client. I am using flyway version 4.0.3 and hsqldb 2.3.4. On debug it is seen that it is waiting for response I/O from DB to complete at this point => boolean hasResults = statement.execute(sql); within the class org.flywaydb.core.internal.dbsupport.JdbcTemplate
UPDATE:
As mentioned by Fredt , this issue is not seen when using hsqldb-2.3.3
This issue is specific to HSQLDB 2.3.4 which has more strict locking. You can set the transaction model to MVCC to avoid the issue. Alternatively use HSQLDB version 2.3.3.