H2 SQL Syntax Error `expected "., COMMENT, ("` using Flyway w/ Spring Boot Test

Question

I am using Spring Boot 2 with Data JPA and Flyway on a Postgres database. Everything works fine in production. Now I'm trying to write tests that will run on an H2 Embedded Database for testing purposes. But, the tests encounter a SQL syntax error. But, I don't understand what's wrong with the syntax:

CREATE TABLE mysite_user (
    id int8 NOT NULL,
    thirdparty_user_id varchar(255) NULL,
    email varchar(255) NULL,
    first_name varchar(255) NULL,
    PRIMARY KEY (id)
);
CREATE INDEX mysite_user_thirdparty_user_id_idx ON mysite_user USING btree (thirdparty_user_id) ;

This is the error:

Migration V1__Initial.sql failed
--------------------------------
SQL State  : 42001
Error Code : 42001
Message    : Syntax error in SQL statement "CREATE INDEX MYSITE_USER_THIRDPARTY_USER_ID_IDX ON MYSITE_USER USING[*] BTREE (THIRDPARTY_USER_ID) "; expected "., COMMENT, ("; SQL statement:
CREATE INDEX mysite_user_thirdparty_user_id_idx ON mysite_user USING btree (thirdparty_user_id) [42001-199]
Location   : db/migration/V1__Initial.sql (/Users/me/Development/mysite-website/target/classes/db/migration/V1__Initial.sql)
Line       : 20
Statement  : CREATE INDEX mysite_user_thirdparty_user_id_idx ON mysite_user USING btree (thirdparty_user_id)

What am I doing wrong?

@a_horse_with_no_name Is there a way to specify different SQL for different databases in Flyway? — Adam, Jul 08 '19 at 21:54
I don't know - I only use Liquibase which can easily do that with its (DBMS independent) XML format — , Jul 09 '19 at 05:25

score 1 · Answer 1 · answered Jul 09 '19 at 08:17

Arguably, what you're doing wrong is using different databases between prod and test. As you've seen, your tests end up having to be coded around the differences between the databases, as well as potentially overlooking subtle edge cases which work in one db and not the other.

If you want to go down this route it is possible - see Best way for "database specific" sql scripts with Flyway

H2 SQL Syntax Error `expected "., COMMENT, ("` using Flyway w/ Spring Boot Test

1 Answers1