Spring Data JPA / Flyway Escaping Double Quotes-CodePudding

I have a spring boot project using Spring Data JPA, Flyway and H2 that is attempting to process a migration script that contains the following SQL statement:

ALTER ROLE current_user SET search_path TO "$user", public, my_schema;

When Flyway attempts to run this migration, I get the following error:

---------------------------------
SQL State  : 42001
Error Code : 42001
Message    : Syntax error in SQL statement "ALTER ROLE[*] CURRENT_USER SET SEARCH_PATH TO ""$user"", PUBLIC, MY_SCHEMA"; expected "TABLE, USER, INDEX, SCHEMA, SEQUENCE, VIEW"; SQL statement:
ALTER ROLE current_user SET search_path TO "$user", public, my_schema [42001-200]

It looks like somewhere in the process, the double quotes around "$user" got escaped with an additional set of quotes.

How do I fix this?

CodePudding user response：

ALTER ROLE is no valid h2 Command. (and that is also, how the error message sounds like.) I think you mean rather GRANT ROLE!(?)
If we need to escape ", refer to quoted names:

"anything"
| U&"anything" [ UESCAPE 'anything' ]
Case of characters in quoted names is preserved as is. Such names can contain spaces. The maximum name length is 256 characters. Two double quotes can be used to create a single double quote inside an identifier. With default settings identifiers in H2 are case sensitive.

so "$user" should be no problem.