Getting FlywaySqlException:Error restoring current schema to its original setting

General
,
#1

Hi,
I’m trying to migrate an app from Postgres to cocroachdb.
While trying to do that I’m getting the attached error:
org.flywaydb.core.internal.dbsupport.FlywaySqlException:Error restoring current schema to its original setting
SQL State : 42601
Error Code : 0
Message : ERROR: at or near “$”: syntax error
Detail: source SQL:
SET search_path = $user,public
^
Hint: try \h SET SESSION
at org.flywaydb.core.internal.dbsupport.DbSupport.restoreCurrentSchema(DbSupport.java:134)

Please advise how to solve this issue.

Tech info:
Database: PostgreSQL 9.5.0. Driver: PostgreSQL JDBC Driver 42.2.12 Pool: postgresql
Connection URL: jdbc:postgresql://:26257/?ssl=true&sslmode=require&sslrootcert=<…>&sslcert=<…>&sslkey=<…>"
Server version: CockroachDB CCL v20.2.5 (x86_64-unknown-linux-gnu, built 2021/02/16 12:52:58, go1.13.14) (same version as client)

Thanks,
Hai

#2

Hi! Thanks for the report. We are tracking the issue with $user in search_path here: sql: SHOW search_path does not properly quote $user · Issue #61730 · cockroachdb/cockroach · GitHub

In the meantime, before that’s fixed, I think you should be able to run SET search_path = public in a SQL shell before running Flyway, and that should fix it.