Issue
I have the following SQL script that is failing during flyway migration:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON inserttablename(customer_id);
An SQL syntax error is thrown, giving the below output:
13:23:47 java.lang.IllegalStateException: Failed to load
ApplicationContext
13:23:47 Caused by:
org.springframework.beans.factory.BeanCreationException:
13:23:47 Error creating bean with name 'flywayInitializer' defined in
class path resource[org/springframework/boot/autoconfigure/flyway/FlywayAutoConfigurati on$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException:
13:23:47 Migration V5__242141241.sql failed
13:23:47 --------------------------------------
13:23:47 SQL State : 42001
13:23:47 Error Code : 42001
13:23:47 Message : Syntax error in SQL statement "CREATE INDEX
CONCURRENTLY IF[*] NOT EXISTS IDX_NAME ON INSERTTABLENAME(CUSTOMER_ID)";
expected "., ON"; SQL statement:
13:23:47 CREATE INDEX CONCURRENTLY IF NOT EXISTS
idx_name ON tablename(customer_id) [42001-200]
13:23:47 Location : db/migration/V5__242141241.sql
13:23:47 Line : 1
13:23:47 Statement : CREATE INDEX CONCURRENTLY IF NOT EXISTS
idx_name ON inserttablename(customer_id)
13:23:47
13:23:47 Caused by:
org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException:
13:23:47
13:23:47 Migration V5__242141241.sql failed
13:23:47 SQL State : 42001
13:23:47 Error Code : 42001
13:23:47 Message : Syntax error in SQL statement "CREATE INDEX
CONCURRENTLY IF[*] NOT
EXISTS IDX_NAME ON INSERTTABLENAME(CUSTOMER_ID)"; expected "., ON"; SQL
statement:
13:23:47 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON
inserttablename(customer_id) [42001-200]
Executing the same query in pgadmin's query tool works fine with the index being created. Also the same type of migration, with the same SQL query undergoing flyway migration for a different table, was successful previously - even while using the same versions of flyway and h2 in the two different services.
Any inputs would be appreciated
Solution
looks like the integration tests were using h2 in-mem which was causing the pipeline issue. Disabled flyway in the tests and migration worked fine on app startup
Answered By - higz555
Answer Checked By - Clifford M. (JavaFixing Volunteer)
