Liquibase Changeset Checksums Validation failures

Liquibase executes a ChangeLog file which contains multiple ChangeSets, each ChangeSet is a DDL or DML operation against the database. When Liquibase encounters a Changeset in the Changelog file during execution, it first computes a MD5 checksome of ChangeSet by combining filepath::id::author parameters of ChangeSet.

this MD5 checksome serves as the unique identifier for the ChangeSet and helps Liquibase determine which ChangeSet has already been executed before.

Liquibase saves these MD5 checksome in the MD5SUM column of the DATABASECHANGELOG table for future reference. It helps Liquibase to make sure if the ChangeSet has been executed before or not.

Liquibase provides calculateChecksum command which recalculate and print the MD5 checksome for the ChangeSet of ChangeLog file. Learn more about Liquibase MD5 calculateChecksum

Execute below ChangeLog

<?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-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1697962298531-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)"/>
			<column name="available" type="CHAR(1)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1697962298531-2">
		<insert catalogName="appledb" tableName="apple"> 
			<column name="apple_name" value="Macintosh" />
			<column name="available" value="Y" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

Liquibase will create a table and insert a record in it.

select * from appleDb.apple;

Liquibase calculates the MD5 Checksum for each ChangeSet and saves in DATABASECHANGELOG table, The same can be verified by executing the command

select * from appleDb.DATABASECHANGELOG;

now update the value="N" in ChangeSet id="1697962298531-2" file as below

<?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-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1697962298531-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)"/>
			<column name="available" type="CHAR(1)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1697962298531-2">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Macintosh" />
			<column name="available" value="N" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

Liquibase will throw error

Unexpected error running Liquibase: Validation Failed:
1 changesets check sum
changelog.mysql.xml::1697962298531-2::wesome was: 9:e8a34b69bdbbae605269cf3338718a1b but is now: 9:2eddcb6b311d4a917828fa7476125f65

The error occurred because, for ChangeSet id="1697962298531-2, the Liquibase calculated the MD5 CheckSum as 9:e8a34b69bdbbae605269cf3338718a1b and saved in DATABASECHANGELOG table. but with the updation of value="N", the new MD5 Checksum is 9:2eddcb6b311d4a917828fa7476125f65, and Liquibase is not able to understand the changes.

The Checksum validation error can be an typo or a valid error because of an update in ChangeSet which should be executed in the database, it can be handled in multiple ways

Manually clear DATABASECHANGELOG table

Liquibase stores all the calculated MD5 checksum in MD5SUM column of DATABASECHANGELOG table, first option is to manually clear the MD5SUM column, so next time Liqubase execute, it will recalculate and populate the MD5SUM column with new values, But it must be done for all environments where the changeset is deployed.

Liquibase clearChecksums command

Liquibase provides clearChecksumcommand which clearn all the MD5 checksome in the DATABASECHANGELOG table, once next database update happens, LIquibase will recalculate all the Checksum from ChangeSet and update the DATABASECHANGELOG table. Learn more about clearChecksumcommand

Add validCheckSum tag with ChangeSet

Liquibase provides validCheckSum tag for ChangeSet, if this tag is present, Liquibase will not calculate the MD5 checksum and skip this ChangeSet assuming this has already been executed before. The validCheckSum tag can have either the old checksum from DATABASECHANGELOG table or the new checksum from the error message

<?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-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1697962298531-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)">
				<constraints unique="true" />
			</column>
			<column name="available" type="CHAR(1)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1697962298531-2">
		<validCheckSum>
			9:2eddcb6b311d4a917828fa7476125f65
		</validCheckSum>
		<insert catalogName="appledb" tableName="apple"> 
			<column name="apple_name" value="Macintosh" />
			<column name="available" value="N" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update

ChangeSet runOnChange attribute

Liquibase provides an runOnChange attribute to run the change set irrespective of modification. Append runOnChange="true" to each ChangeSet that needs to run on modification. Learn more about runOnChange attribute

<?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-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1697962298531-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column name="available" type="CHAR(1)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1697962298531-2" runOnChange="true">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Macintosh" />
			<column name="available" value="Y" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update