1

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?

Mark
  • 4,970
  • 5
  • 42
  • 66

1 Answers1

1

  1. ALTER ROLE is no valid h2 Command. (and that is also, how the error message sounds like.) I think you mean rather GRANT ROLE!(?)

  2. 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.

See also:

xerx593
  • 12,237
  • 5
  • 33
  • 64
  • 1
    Ah I see now. "ALTER ROLE" is postgresql-specific. I was using H2 for tests. I'll have to find a different way to test this. – Mark Dec 22 '21 at 17:22
  • thx for accept! :-) ... [from flyway](https://flywaydb.org/blog/a-simple-way-to-manage-multi-environment-deployments), [spring-boot-specific](https://stackoverflow.com/q/33323837/592355) ;(;(;( – xerx593 Dec 22 '21 at 17:26