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:
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.
| 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 |
Updating the table like below solved the issue and Spring Boot started and verified Liquibase logs successfully.
| id | author | filename | dateexecuted | orderexecuted | exectype | md5sum | description | comments | tag | liquibase | contexts | labels | deployment_id |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1656504084409-1 | liquibase (generated) | db/changelog/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/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/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/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/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 |