I'm struggling to make Spring Boot Liquibase identify the changes that are already in the database
Liquibase was added to an existent database following these steps:
- execute a generated-changelog (to create the databasechangelog table based on existent tables)
- execute a changelog-sync (where resources/db/changelog/db.changelog-baseline.xml described below was generated)
Below you can find all details about Spring Boot configuration and data databasechangelog table data.
The latest block shows the exception (due liquibase.exception.DatabaseException: ERROR: relation "company" already exists) I'm facing trying to start the application.
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.1</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.liquibase</groupId>
<artifactId>liquibase-core</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
application.yml
spring:
datasource:
url: jdbc:postgresql://localhost:5432/test_db
username: myapp
password: pass12345
jpa:
show-sql: true
properties:
hibernate:
dialect: org.hibernate.dialect.PostgreSQLDialect
ddl-auto: none
format_sql: true
liquibase:
change-log: classpath:db/db.changelog-root.xml
LiquibaseLabApplication.java
package com.example.liquibaselab;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class LiquibaseLabApplication
{
public static void main(String[] args) {
SpringApplication.run(LiquibaseLabApplication.class, args);
}
}
databasechangelog
| id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase | contexts | labels | deployment_id |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1656504084409-1 | liquibase (generated) | db.changelog-baseline.xml | 2022-06-29 12:01:26.427877 | 1 | EXECUTED | 8:782adbb55688283f6690c848df3ff379 | createTable tableName=company | NULL | 4.10.0 | NULL | NULL | 6504086279.0 | |
| 1656504084409-2 | liquibase (generated) | db.changelog-baseline.xml | 2022-06-29 12:01:26.435501 | 2 | EXECUTED | 8:da536519174a61bb82d743a69184a533 | createTable tableName=manager | NULL | 4.10.0 | NULL | NULL | 6504086279.0 | |
| 1656504084409-3 | liquibase (generated) | db.changelog-baseline.xml | 2022-06-29 12:01:26.441231 | 3 | EXECUTED | 8:436718701e5c6337cd9478f0827ac301 | createTable tableName=employee | NULL | 4.10.0 | NULL | NULL | 6504086279.0 | |
| 1656504084409-4 | liquibase (generated) | db.changelog-baseline.xml | 2022-06-29 12:01:26.446085 | 4 | EXECUTED | 8:13218bc62fcaba1f6ec7e8e9a6a663cb | addForeignKeyConstraint baseTableName=employee, constraintName=fk_manager_company_id, referencedTableName=manager | NULL | 4.10.0 | NULL | NULL | 6504086279.0 | |
| 1656504084409-5 | liquibase (generated) | db.changelog-baseline.xml | 2022-06-29 12:01:26.451618 | 5 | EXECUTED | 8:e7d140adc1a13fca6864b12c7a7ed708 | addForeignKeyConstraint baseTableName=manager, constraintName=fk_manager_company_id, referencedTableName=company | NULL | 4.10.0 | NULL | NULL | 6504086279.0 |
resources/db/db.changelog-root.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
<includeAll path="db/changelog/"/>
</databaseChangeLog>
resources/db/changelog/db.changelog-baseline.xml
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.6.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd">
<changeSet author="liquibase (generated)" id="1656504084409-1">
<createTable tableName="company">
<column autoIncrement="true" name="id" type="INTEGER">
<constraints nullable="false" primaryKey="true" primaryKeyName="company_pkey"/>
</column>
<column name="name" type="VARCHAR">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="liquibase (generated)" id="1656504084409-2">
<createTable tableName="manager">
<column autoIncrement="true" name="id" type="INTEGER">
<constraints nullable="false" primaryKey="true" primaryKeyName="manager_pkey"/>
</column>
<column name="full_name" type="VARCHAR">
<constraints nullable="false"/>
</column>
<column name="company_id" type="INTEGER"/>
</createTable>
</changeSet>
<changeSet author="liquibase (generated)" id="1656504084409-3">
<createTable tableName="employee">
<column autoIncrement="true" name="id" type="INTEGER">
<constraints nullable="false" primaryKey="true" primaryKeyName="employee_pkey"/>
</column>
<column name="full_name" type="VARCHAR">
<constraints nullable="false"/>
</column>
<column name="manager_id" type="INTEGER"/>
</createTable>
</changeSet>
<changeSet author="liquibase (generated)" id="1656504084409-4">
<addForeignKeyConstraint baseColumnNames="manager_id" baseTableName="employee"
constraintName="fk_manager_company_id" deferrable="false" initiallyDeferred="false"
onDelete="NO ACTION" onUpdate="NO ACTION" referencedColumnNames="id"
referencedTableName="manager" validate="true"/>
</changeSet>
<changeSet author="liquibase (generated)" id="1656504084409-5">
<addForeignKeyConstraint baseColumnNames="company_id" baseTableName="manager"
constraintName="fk_manager_company_id" deferrable="false" initiallyDeferred="false"
onDelete="NO ACTION" onUpdate="NO ACTION" referencedColumnNames="id"
referencedTableName="company" validate="true"/>
</changeSet>
</databaseChangeLog>
Exception
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.7.1)
2022-06-29 15:24:40.860 INFO 14000 --- [ main] o.m.l.LiquibaseLabApplication : Starting LiquibaseLabApplication using Java 13.0.1 on dfr01-ID8B25ST7 with PID 14000 (C:\UBS\Dev\code\liquibase-lab-main\liquibase-lab-main\target\classes started by lucasmor in C:\UBS\Dev\code\liquibase-lab-main\liquibase-lab-main)
2022-06-29 15:24:40.864 INFO 14000 --- [ main] o.m.l.LiquibaseLabApplication : No active profile set, falling back to 1 default profile: "default"
2022-06-29 15:24:41.792 INFO 14000 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2022-06-29 15:24:41.814 INFO 14000 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 10 ms. Found 0 JPA repository interfaces.
2022-06-29 15:24:42.749 INFO 14000 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http)
2022-06-29 15:24:42.763 INFO 14000 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2022-06-29 15:24:42.763 INFO 14000 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.64]
2022-06-29 15:24:42.988 INFO 14000 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2022-06-29 15:24:42.988 INFO 14000 --- [ main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 2004 ms
2022-06-29 15:24:43.334 INFO 14000 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-06-29 15:24:43.436 INFO 14000 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2022-06-29 15:24:43.639 INFO 14000 --- [ main] liquibase.database : Set default schema name to test_app
2022-06-29 15:24:43.874 INFO 14000 --- [ main] liquibase.lockservice : Successfully acquired change log lock
2022-06-29 15:24:44.082 INFO 14000 --- [ main] liquibase.changelog : Reading resource: db/changelog/db.changelog-baseline.xml
2022-06-29 15:24:44.610 INFO 14000 --- [ main] liquibase.changelog : Reading from test_app.databasechangelog
Running Changeset: db/changelog/db.changelog-baseline.xml::1656504084409-1::liquibase (generated)
2022-06-29 15:24:44.835 INFO 14000 --- [ main] liquibase.lockservice : Successfully released change log lock
2022-06-29 15:24:44.837 WARN 14000 --- [ main] ConfigServletWebServerApplicationContext : 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.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/db.changelog-baseline.xml::1656504084409-1::liquibase (generated):
Reason: liquibase.exception.DatabaseException: ERROR: relation "company" already exists [Failed SQL: (0) CREATE TABLE test_app.company (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, name VARCHAR NOT NULL, CONSTRAINT company_pkey PRIMARY KEY (id))]
2022-06-29 15:24:44.837 INFO 14000 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2022-06-29 15:24:44.847 INFO 14000 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
2022-06-29 15:24:44.852 INFO 14000 --- [ main] o.apache.catalina.core.StandardService : Stopping service [Tomcat]
2022-06-29 15:24:44.867 INFO 14000 --- [ main] ConditionEvaluationReportLoggingListener :
Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2022-06-29 15:24:44.896 ERROR 14000 --- [ main] o.s.boot.SpringApplication : 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.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/db.changelog-baseline.xml::1656504084409-1::liquibase (generated):
Reason: liquibase.exception.DatabaseException: ERROR: relation "company" already exists [Failed SQL: (0) CREATE TABLE test_app.company (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, name VARCHAR NOT NULL, CONSTRAINT company_pkey PRIMARY KEY (id))]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1804) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154) ~[spring-context-5.3.21.jar:5.3.21]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:908) ~[spring-context-5.3.21.jar:5.3.21]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.21.jar:5.3.21]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147) ~[spring-boot-2.7.1.jar:2.7.1]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:734) ~[spring-boot-2.7.1.jar:2.7.1]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:408) ~[spring-boot-2.7.1.jar:2.7.1]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:308) ~[spring-boot-2.7.1.jar:2.7.1]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1306) ~[spring-boot-2.7.1.jar:2.7.1]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1295) ~[spring-boot-2.7.1.jar:2.7.1]
at org.test.liquibaselab.LiquibaseLabApplication.main(LiquibaseLabApplication.java:10) ~[classes/:na]
Caused by: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/db.changelog-baseline.xml::1656504084409-1::liquibase (generated):
Reason: liquibase.exception.DatabaseException: ERROR: relation "company" already exists [Failed SQL: (0) CREATE TABLE test_app.company (id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL, name VARCHAR NOT NULL, CONSTRAINT company_pkey PRIMARY KEY (id))]
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:126) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Liquibase.lambda$null$0(Liquibase.java:265) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Scope.lambda$child$0(Scope.java:180) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Scope.child(Scope.java:189) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Scope.child(Scope.java:179) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Scope.child(Scope.java:158) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Scope.child(Scope.java:243) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Liquibase.lambda$update$1(Liquibase.java:264) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Scope.lambda$child$0(Scope.java:180) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Scope.child(Scope.java:189) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Scope.child(Scope.java:179) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Scope.child(Scope.java:158) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Liquibase.runInScope(Liquibase.java:2405) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Liquibase.update(Liquibase.java:211) ~[liquibase-core-4.9.1.jar:na]
at liquibase.Liquibase.update(Liquibase.java:197) ~[liquibase-core-4.9.1.jar:na]
at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:314) ~[liquibase-core-4.9.1.jar:na]
at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:269) ~[liquibase-core-4.9.1.jar:na]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1863) ~[spring-beans-5.3.21.jar:5.3.21]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1800) ~[spring-beans-5.3.21.jar:5.3.21]
... 18 common frames omitted
It turned out the filename path was the issue:
My changelog file that was generated by changelog-sync was put at: resources/db/changelog/db.changelog-baseline.xml, but as you can see in the databasechangelog table below it was just showing the file name (filename column) with the full path to it.
Updating the table like below solved the issue and Spring Boot started and verified Liquibase logs successfully.