JdbcSQLSyntaxErrorException Schema not found

Question

My @SpringBootTest is annotated with @Sql({"classpath:create_tables_views.sql"})

and the first line inside create_tables_views.sql is

DROP VIEW IF EXISTS my_schema.my_view;

When I try to run my @SpringBootTest, I get the following exception:

Caught exception while invoking 'beforeTestMethod' callback on TestExecutionListener [org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener@1e0f9063] for test method [void ...] and test instance [...]
org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [create_tables_views.sql]: DROP VIEW IF EXISTS my_schema.my_view; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Schema "MY_SCHEMA" not found;

What am I missing ?

---

UPDATE 1:

Adding some more details here for additional clarity: This is how my class is annotated

@ActiveProfiles("test") // (application-test.yml is currently empty)
@AutoConfigureTestDatabase // (so yes, it's spinning up a brand new H2 instance each time)
@AutoConfigureWebTestClient
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@Sql({"classpath:create_tables_views.sql"})

Also, upon booting, I see this in the log messages:

16:12:08.489 INFO  [main] o.s.j.d.e.EmbeddedDatabaseFactory: Starting embedded database: url='jdbc:h2:mem:9af3a13a-91b3-47ff-a85d-8488d2329a53;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'

Because the initial SQL command is DROP VIEW IF EXISTS my_schema.my_view;, I don't understand why it is throwing an exception if the schema doesn't exist.

---

UPDATE 2:

In my src/main/java folder, I have 2 separate @Configuration files containing @Bean definitions providing DataSource, LocalContainerEntityManagerFactoryBean, and PlatformTransactionManager for 2 different Databases, one @Primary one for Postgres/Postgis and another one for Oracle.

Answer 1

The database cannot decide if the view my_view exists in the schema my_schema, because this schema does not exist.

Command DROP VIEW IF EXISTS first retrieves metadata for the scheme my_schema, checks if the given view exists there, and if exists, then drops it. In your case it fails because this command cannot retrieve metadata from the schema my_schema because this schema does not exist.

May be you expected that it works as follows: "Check if the schema my_schema exists. If it doesn't exist, don't do anything more. If the schema exists, check if the view exists. Only if the view exists, issue a command to drop it." But it is not how it works. If the schema does not exist, an error is thrown.