Spring Boot测试中使用Liquibase失败。

huangapple go评论143阅读模式
英文:

Spring boot testing with liquibase fails

问题

我一直在尝试解决我的问题,但一直没有成功。

无论如何,我有一堆集成测试(在一个非标准的目录testRegression中,与标准测试目录平行)。

这些集成测试使用内存中的h2数据库。在生产环境和测试环境中,我使用liquibase来模拟模式演进。

我的属性(在application-testRegession.properties中)如下所示:

spring.liquibase.enabled=true
spring.liquibase.user=sa
spring.liquibase.password=
spring.liquibase.change-log=classpath:/liquibase/changelog-master.xml

spring.datasource.url=jdbc:p6spy:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS nmc\\;CREATE SCHEMA IF NOT EXISTS mkt\\;CREATE SCHEMA IF NOT EXISTS cdb\\;CREATE SCHEMA IF NOT EXISTS pg_temp
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.username=sa
spring.datasource.password=

我一直得到的错误是:

2020-07-21 15:57:34.173 INFO  [liquibase.lockservice.StandardLockService] [Test worker:13]: 成功获取变更日志锁定
2020-07-21 15:57:34.303 INFO  [liquibase.changelog.StandardChangeLogHistoryService] [Test worker:13]: 创建数据库历史表,名称为:PUBLIC.DATABASECHANGELOG
2020-07-21 15:57:34.305 INFO  [liquibase.executor.jvm.JdbcExecutor] [Test worker:13]: CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))
2020-07-21 15:57:34.307 INFO  [liquibase.lockservice.StandardLockService] [Test worker:13]: 成功释放变更日志锁定
2020-07-21 15:57:34.309 WARN  [org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext] [Test worker:13]: 上下文初始化期间遇到异常 - 取消刷新尝试:org.springframework.beans.factory.BeanCreationException: 在类路径资源[org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]中定义的名为“liquibase”的bean创建错误:调用init方法失败;嵌套异常是liquibase.exception.DatabaseException: 表“DATABASECHANGELOG”已经存在;SQL语句:
CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10)) [42101-197] [Failed SQL: (42101) CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]
2020-07-21 15:57:34.309 INFO  [com.zaxxer.hikari.HikariDataSource] [Test worker:13]: HikariPool-3 - 启动关闭...
2020-07-21 15:57:34.324 INFO  [com.zaxxer.hikari.HikariDataSource] [Test worker:13]: HikariPool-3 - 关闭完成。
2020-07-21 15:57:34.326 INFO  [org.apache.catalina.core.StandardService] [Test worker:13]: 停止服务[Tomcat]
2020-07-21 15:57:34.342 INFO  [org.springframework.boot.autoconfigure.logging.ConditionEvaluationReportLoggingListener] [Test worker:13]: 

启动应用程序出错。要显示条件报告,请使用“debug”启动您的应用程序。
2020-07-21 15:57:34.345 ERROR [org.springframework.boot.SpringApplication] [Test worker:13]: 应用程序运行失败
org.springframework.beans.factory.BeanCreationException: 在类路径资源[org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]中定义的名为“liquibase”的bean创建错误:调用init方法失败;嵌套异常是liquibase.exception.DatabaseException: 表“DATABASECHANGELOG”已经存在;SQL语句:

那么,我该如何解决这个问题呢?我基本的理解是每个测试类都会创建自己的ApplicationContext。为此,它会创建并加载一个liquibase bean。
然而,这个问题只在42个测试中的2个测试中出现。

我真的很想弄清楚这个问题,了解到底发生了什么。
有人能解释一下我的问题吗?

另外
单独运行所有测试都正常,但作为一组运行时失败。

更新 1
相关属性如下:

spring.main.allow-bean-definition-overriding=true
spring.datasource.url=jdbc:p6spy:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS nmc\\;CREATE SCHEMA IF NOT EXISTS mkt\\;CREATE SCHEMA IF NOT EXISTS cdb\\;CREATE SCHEMA IF NOT EXISTS pg_temp
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.hikari.connectionTimeout=10000
spring.datasource.hikari.idleTimeout=60000
spring.datasource.hikari.maxLifetime=180000
spring.datasource.hikari.maximumPoolSize=50
spring.h2.console.enabled=true
spring.h

<details>
<summary>英文:</summary>

I have been trying for quite some time to figure out a solution for my problem, to no avail.

Anyway, i have a bunch of integration tests (in a nonstandard directory testRegression parallel to the standard test directory).

These integration tests use an h2 in memory database. In production as well as for testing i am using liquibase to simulate the schema evolution.

My properties (in application-testRegession.properties) look as follows:

spring.liquibase.enabled=true
spring.liquibase.user=sa
spring.liquibase.password=
spring.liquibase.change-log=classpath:/liquibase/changelog-master.xml

spring.datasource.url=jdbc:p6spy:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS nmc\;CREATE SCHEMA IF NOT EXISTS mkt\;CREATE SCHEMA IF NOT EXISTS cdb\;CREATE SCHEMA IF NOT EXISTS pg_temp
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.username=sa
spring.datasource.password=


The error i consistenly keep getting is:

2020-07-21 15:57:34.173 INFO [liquibase.lockservice.StandardLockService] [Test worker:13]: Successfully acquired change log lock
2020-07-21 15:57:34.303 INFO [liquibase.changelog.StandardChangeLogHistoryService] [Test worker:13]: Creating database history table with name: PUBLIC.DATABASECHANGELOG
2020-07-21 15:57:34.305 INFO [liquibase.executor.jvm.JdbcExecutor] [Test worker:13]: CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))
2020-07-21 15:57:34.307 INFO [liquibase.lockservice.StandardLockService] [Test worker:13]: Successfully released change log lock
2020-07-21 15:57:34.309 WARN [org.springframework.boot.web.servlet.context.AnnotationConfigServletWebServerApplicationContext] [Test worker:13]: Exception encountered during context initialization - cancelling refresh attempt: 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.DatabaseException: Table "DATABASECHANGELOG" already exists; SQL statement:
CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10)) [42101-197] [Failed SQL: (42101) CREATE TABLE PUBLIC.DATABASECHANGELOG (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]
2020-07-21 15:57:34.309 INFO [com.zaxxer.hikari.HikariDataSource] [Test worker:13]: HikariPool-3 - Shutdown initiated...
2020-07-21 15:57:34.324 INFO [com.zaxxer.hikari.HikariDataSource] [Test worker:13]: HikariPool-3 - Shutdown completed.
2020-07-21 15:57:34.326 INFO [org.apache.catalina.core.StandardService] [Test worker:13]: Stopping service [Tomcat]
2020-07-21 15:57:34.342 INFO [org.springframework.boot.autoconfigure.logging.ConditionEvaluationReportLoggingListener] [Test worker:13]:

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-07-21 15:57:34.345 ERROR [org.springframework.boot.SpringApplication] [Test worker:13]: Application run failed
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.DatabaseException: Table "DATABASECHANGELOG" already exists; SQL statement:


So how can i get around this issue? My basic understanding is that each test class creates its own ApplicationContext. For that it creates and loads a liquibase bean into it.
However, this problem occurs only for 2 out of 42 tests.

I would really like to get to the bottom of this and understand whats going on.
Can anyone shed light on my problem?

**ADDITIONALLY**
The test all run fine individually, but when run as a group they fail.

**UPDATE 1**
The relevant properties are as follows:

spring.main.allow-bean-definition-overriding=true
spring.datasource.url=jdbc:p6spy:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=FALSE;INIT=CREATE SCHEMA IF NOT EXISTS nmc\;CREATE SCHEMA IF NOT EXISTS mkt\;CREATE SCHEMA IF NOT EXISTS cdb\;CREATE SCHEMA IF NOT EXISTS pg_temp
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.hikari.connectionTimeout=10000
spring.datasource.hikari.idleTimeout=60000
spring.datasource.hikari.maxLifetime=180000
spring.datasource.hikari.maximumPoolSize=50
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console


My configuration is:

@Configuration
@ComponentScan(
basePackages = {
"com.aareal.nmc"
},
excludeFilters = {
@ComponentScan.Filter(type = FilterType.ASSIGNABLE_TYPE, value = CommandLineRunner.class)
})
@EnableTransactionManagement
@Profile("testRegression")
@SpringBootApplication(exclude = SecurityAutoConfiguration.class)
@EnableConfigurationProperties(LiquibaseProperties.class)
public class RegressionTestConfig {


My two tests are annotated as:

@RunWith(SpringRunner.class)
@SpringBootTest(
classes = {
RegressionTestConfig.class
},
//webEnvironment = SpringBootTest.WebEnvironment.DEFINED_PORT)
webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)


Thanks

</details>


# 答案1
**得分**: 9

我遇到了相同的问题,看起来是由于对数据库表名的大小写敏感检查引起的。也就是说,表格创建为'DATABASECHANGELOG',但Liquibase在检查时要求表名为'databasechangelog'。

解决方法(至少适用于H2数据库)是在数据库URL中指定大小写不敏感的标识符。例如:

jdbc:h2:mem:~/mydb;CASE_INSENSITIVE_IDENTIFIERS=TRUE

**解释:** Spring测试过程会启动一个或多个Spring容器的实例来运行测试。如果它认为两个测试的配置完全相同,它将重复使用一个实例,否则将启动一个新实例。这些实例是共享的,以避免需要为每个测试启动一个全新的Springboot应用程序。但问题是这些实例可能共享一些资源,比如数据库和网络端口。因此,尝试同时启动多个实例可能会导致错误。在这种情况下,测试套件正在使用相同的数据库启动两个实例,但第二个实例由于大小写敏感问题而尝试重新运行整个Liquibase设置,因此它无法看到表已经被创建。

<details>
<summary>英文:</summary>

I had the same issue, and it seems to have been caused by case sensitive checking of the database table name. That is, the table was created as &#39;DATABASECHANGELOG&#39;, but Liquibase was checking for the existence of &#39;databasechangelog&#39;.

The fix (at least for an H2 database) is to specify case insensitive identifiers in the database URL. For example:

    jdbc:h2:mem:~/mydb;CASE_INSENSITIVE_IDENTIFIERS=TRUE

**Explanation:** The Spring test process fires up one or more instances of the Spring container to run tests on. If it thinks the config is exactly the same for two tests it will re-use an instance, otherwise it will start a new one. The instances are shared to avoid needing to start a whole new Springboot application for each test. But, the problem is that the instances may share some resources, such as databases and network ports. Therefore errors might occur from trying to start multiple instances at the same time. In this case, the test suite is starting two instances using the same database, but the second one is trying to re-run the whole Liquibase setup because the case sensitive issue means it doesn&#39;t see the table has already been created.

</details>



# 答案2
**得分**: 2

对于我的特殊情况(仅用于内部测试,而非生产),我有以下内容:

&lt;root&gt;src&lt;br/&gt;
|-- main&lt;br/&gt;
|-- test&lt;br/&gt;
|-- testRegression

**解决方法**

1. 决定使用的liquibase版本(我选择了4.0.0,这是目前最新的版本)
2. 创建一个文件 "src/testRegression/java/liquibase/changelog/StandardChangeLogHistoryService.java"
3. 打开原始的liquibase文件 "StandardChangeLogHistoryService.java"(我的文件位于~//.gradle/caches/modules-2/files-2.1/org.liquibase/liquibase-core/4.0.0/23a5317eb5005b4765cd85e6f3a2cc4bb55c0daa/liquibase-core-4.0.0-sources.jar,我复制并解压了它)
   并将其内容一对一地复制到步骤2中新创建的文件中。
4. 通过更改代码(大约在第396行附近)添加一个捕获块,从

          if (SqlGeneratorFactory.getInstance().supports(sql, database)) {
             executor.execute(sql);
             getDatabase().commit();
          } else {
到

       if (SqlGeneratorFactory.getInstance().supports(sql, database)) {
        try {
          executor.execute(sql);
          getDatabase().commit();
        } catch (DatabaseException excptn) {
          Scope.getCurrentScope()
              .getLog(getClass())
              .warning(
                  "Table &#39;"
                      + getDatabase()
                          .escapeTableName(
                              getLiquibaseCatalogName(),
                              getLiquibaseSchemaName(),
                              getDatabaseChangeLogTableName())
                      + "&#39; already exists.");
        }
      } else {

这只是一个解决方法,因为ChangeLogTable表的创建可能会失败,有合法的原因。但是,我认为它已经存在不应该导致重大故障。

我目前的观点是,这是应该在官方liquibase代码库中解决/修复的问题。

以下帖子对我有帮助:
https://github.com/liquibase/liquibase-cache/issues/1

<details>
<summary>英文:</summary>

For my special case (that is for internal testing only, not production) what i have the following:

&lt;root&gt;src&lt;br/&gt;
|-- main&lt;br/&gt;
|-- test&lt;br/&gt;
|-- testRegression


**Workaround**

1. Decide on the version of liquibase to use (i chose 4.0.0, which is the most recent at this point)
2. Create a file &quot;src/testRegression/java/liquibase/changelog/StandardChangeLogHistoryService.java&quot;
3. Open the original liquibase file &quot;StandardChangeLogHistoryService.java&quot; (mine is in ~//.gradle/caches/modules-2/files-2.1/org.liquibase/liquibase-core/4.0.0/23a5317eb5005b4765cd85e6f3a2cc4bb55c0daa/liquibase-core-4.0.0-sources.jar which i copied and unzipped) 
and copy its contents 1:1 into the newly created file in 2.
4. Add a catch block by changing code (around line 396)  from

          if (SqlGeneratorFactory.getInstance().supports(sql, database)) {
             executor.execute(sql);
             getDatabase().commit();
          } else {
to

       if (SqlGeneratorFactory.getInstance().supports(sql, database)) {
        try {
          executor.execute(sql);
          getDatabase().commit();
        } catch (DatabaseException excptn) {
          Scope.getCurrentScope()
              .getLog(getClass())
              .warning(
                  &quot;Table &#39;&quot;
                      + getDatabase()
                          .escapeTableName(
                              getLiquibaseCatalogName(),
                              getLiquibaseSchemaName(),
                              getDatabaseChangeLogTableName())
                      + &quot;&#39; already exists.&quot;);
        }
      } else {

This is simply a workaround since there could be legitimate reasons for the ChangeLogTable table creation to fail. However, it already existing should not be cause for a major failure in my opinion.

My current view is that this is something which ought to be addressed/fixed in the official liquibase code base.

The following post(s) were helpful:
https://github.com/liquibase/liquibase-cache/issues/1


</details>



huangapple
  • 本文由 发表于 2020年7月22日 22:13:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/63036299.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定