Set search_path with H2 and Liquibase-CodePudding

I have an issue with my project.

I'm using liquibase ( v4.8.0 ) and on the changelog-master.xml I include a SQL file taht contains that code :

CREATE SCHEMA IF NOT EXISTS public;
SET search_path TO public;

When I run my app with PostgreSQL it works fine, but when I run my tests that are using a H2 database ( h2 v2.1.210 ) it fails with that error :

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set db/schema.sql::raw::includeAll: Syntax error in SQL statement "SET [*]search_path TO public;"; expected "@, AUTOCOMMIT, EXCLUSIVE, IGNORECASE, PASSWORD, SALT, MODE, DATABASE, COLLATION, CLUSTER, DATABASE_EVENT_LISTENER, ALLOW_LITERALS, DEFAULT_TABLE_TYPE, SCHEMA, CATALOG, SCHEMA_SEARCH_PATH, JAVA_OBJECT_SERIALIZER, IGNORE_CATALOGS, SESSION, TRANSACTION, TIME, NON_KEYWORDS, DEFAULT_NULL_ORDERING, LOG"; SQL statement: SET search_path TO public; [42001-210] [Failed SQL: (42001) CREATE SCHEMA IF NOT EXISTS public;

I don't understand why it's failing and also I didn't find any help on the web. Thank you in advance for your help :)

CodePudding user response：

The native syntax for H2 is SET SCHEMA_SEARCH_PATH schemaName, …. This syntax is available unconditionally.

When PostgreSQL compatibility mode is enabled, H2 additionally provides SET SEARCH_PATH command with specialized implementation, it is different from SET SCHEMA_SEARCH_PATH, because it adds pg_catalog first if it wasn't specified and it supports $user. To enable this compatibility mode you need to append ;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE;DEFAULT_NULL_ORDERING=HIGH to JDBC connection URL. It is enough to add ;MODE=PostgreSQL to make SET SEARCH_PATH available, but some other deviations between H2 and PostgreSQL need to be enabled separately, you may need them too. See documentation of compatibility modes for mode details: https://h2database.com/html/features.html#compatibility

This information is actual for the new versions of H2. Old unsupported versions have different options.