使用Liquibase时EventRepository与H2测试库集成报错：表不存在

我正在进行EventRepository与H2数据库的集成测试，使用Liquibase管理数据库变更。尝试从测试H2数据库获取数据时，出现了表不存在的错误；但不使用Liquibase，直接将SQL脚本放在src/test/resources下时一切正常。

相关配置与代码 #

application-test.yaml #

spring:
  h2:
    console:
      enabled: true
  datasource:
    url: r2dbc:h2:mem:///testdb;DB_CLOSE_DELAY=-1;
    username: sa
    password: password
    driver-class-name: org.h2.Driver
  liquibase:
    url: jdbc:h2:mem:///testdb
    user: sa
    password: password
    change-log: classpath:db.changelog/db.changelog-master.yaml
    enabled: true

EventRepository.java #

public interface EventRepository extends R2dbcRepository<Event, Long> {}

EventRepositoryTest.java #

@DataR2dbcTest
@ActiveProfiles("test")
class EventRepositoryTest {

  @Autowired
  private EventRepository eventRepository;

  @Test
  @DisplayName("when find all when receive expected event")
  void findAll(){
    Flux<Event> events = eventRepository.findAll();
    events.doOnNext(e -> System.out.println("events received " + e)).blockLast();
  }
}

db.changelog-master.yaml #

databaseChangeLog:
  - include:
      file: db.changelog/db.changelog-1.0.sql

db.changelog-1.0.sql #

--liquibase formatted sql

--changeset deniskaydunov:1
CREATE TABLE IF NOT EXISTS public.event
(
    seq SERIAL PRIMARY KEY,
    type VARCHAR (32) NOT NULL,
    id VARCHAR (32) NOT NULL,
    received TIMESTAMP NOT NULL
);

--changeset deniskaydunov:2 contextFilter:test
INSERT INTO public.event (type, id, received, data) VALUES
('IRS', '123', '2018-02-05 11:59:19');

错误日志 #

2022-11-01 17:53:29.004  INFO 13320 --- \[           main\] c.a.a.e.a.r.EventRepositoryTest          : Starting EventRepositoryTest using Java 19 on LXIBY1300 with PID 13320 (started by denis.kaydunov in C:\\Users\\denis.kaydunov\\IdeaProjects\\axetrade\\event-api)

2022-11-01 17:53:29.005  INFO 13320 --- \[           main\] c.a.a.e.a.r.EventRepositoryTest          : The following 1 profile is active: "test"

2022-11-01 17:53:29.259  INFO 13320 --- \[           main\] .s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode

2022-11-01 17:53:29.260  INFO 13320 --- \[           main\] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.

2022-11-01 17:53:29.427  INFO 13320 --- \[           main\] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 161 ms. Found 1 R2DBC repository interfaces.

2022-11-01 17:53:30.353  INFO 13320 --- \[           main\] liquibase.database                       : Set default schema name to PUBLIC

2022-11-01 17:53:30.566  INFO 13320 --- \[           main\] liquibase.lockservice                    : Successfully acquired change log lock

2022-11-01 17:53:30.625  INFO 13320 --- \[           main\] liquibase.changelog                      : Creating database history table with name: PUBLIC.DATABASECHANGELOG

2022-11-01 17:53:30.629  INFO 13320 --- \[           main\] liquibase.changelog                      : Reading from PUBLIC.DATABASECHANGELOG

Running Changeset: db.changelog/db.changelog-1.0.sql::1::deniskaydunov

2022-11-01 17:53:30.676  INFO 13320 --- \[           main\] liquibase.changelog                      : Custom SQL executed

2022-11-01 17:53:30.677  INFO 13320 --- \[           main\] liquibase.changelog                      : ChangeSet db.changelog/db.changelog-1.0.sql::1::deniskaydunov ran successfully in 13ms

Running Changeset: db.changelog/db.changelog-1.0.sql::2::deniskaydunov

2022-11-01 17:53:30.713  INFO 13320 --- \[           main\] liquibase.changelog                      : Custom SQL executed

2022-11-01 17:53:30.714  INFO 13320 --- \[           main\] liquibase.changelog                      : ChangeSet db.changelog/db.changelog-1.0.sql::2::deniskaydunov ran successfully in 9ms

2022-11-01 17:53:30.719  INFO 13320 --- \[           main\] liquibase.lockservice                    : Successfully released change log lock

2022-11-01 17:53:30.799  INFO 13320 --- \[           main\] c.a.a.e.a.r.EventRepositoryTest          : Started EventRepositoryTest in 2.202 seconds (JVM running for 3.193)

2022-11-01 17:53:31.305 DEBUG 13320 --- \[           main\] o.s.r2dbc.core.DefaultDatabaseClient     : Executing SQL statement \[SELECT event.* FROM event\]

org.springframework.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar \[SELECT event.* FROM event\]; nested exception is io.r2dbc.spi.R2dbcBadGrammarException: \[42104\] \[42S04\] Table "EVENT" not found (this database is empty); SQL statement:

SELECT event.* FROM event \[42104-214\]

核心问题是Liquibase用JDBC连接创建的表，和R2DBC使用的是两个独立的内存H2实例。H2内存数据库的URL格式在JDBC和R2DBC中存在差异，导致两者指向不同的库，Liquibase创建的表自然无法被R2DBC访问到。

具体修复步骤：

1. 统一数据库URL格式
   修改application-test.yaml中的配置，让JDBC和R2DBC的URL指向同一个内存数据库实例：

   spring:
     datasource:
       url: r2dbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
     liquibase:
       url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
   

   注意去掉R2DBC URL中的///，保持和JDBC URL的mem:testdb格式一致。

2. 显式指定默认Schema
   在R2DBC配置中添加默认Schema，确保查询时能找到PUBLIC下的表：

   spring:
     datasource:
       schema: PUBLIC
   

3. 修复SQL字段错误
   插入语句中包含未定义的data字段，需修改db.changelog-1.0.sql的插入逻辑：

   INSERT INTO public.event (type, id, received) VALUES
   ('IRS', '123', '2018-02-05 11:59:19');
   

4. 确保Liquibase优先执行
   在测试类上添加@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)，禁用Spring自动替换测试数据库，保证Liquibase的变更脚本在R2DBC连接初始化前执行完毕。

内容的提问来源于stack exchange，提问作者WarDen