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
– Adam, Commented Jul 8, 2019 at 21:54
I don't know - I only use Liquibase which can easily do that with its (DBMS independent) XML format — user330315
– user330315, Commented Jul 9, 2019 at 5:25

Julia Hayward · Accepted Answer · 2019-07-09 08:17:31Z

1

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

answered Jul 9, 2019 at 8:17

Julia Hayward

2,1851 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related