update-sql output bloated with excess DATABASECHANGELOGLOCK table CREATE, DELETE and INSERT statements.

[XDelphiGrl]

Environment

Liquibase Version:
Internal Liquibase Build: Core: DAT-8804-2/1016, Pro: DAT-8804-2/288/4ca10b
(Version greater than 4.6.2)

Liquibase Integration & Version:
CLI

Liquibase Extension(s) & Version:
N/A

Database Vendor & Version:
N/A (Can be seen with any database during the update-sql command.)

Operating System Type & Version:
Windows 10
Mac Big Sur
Linux

Description

Executing update-sql with an internal build post-4.6.3 includes repetitive statements related to the creation and updating of the lock state in DATABASECHANGELOGLOCK. My test uses a formatted SQL changelog with three changesets. The CREATE, DELETE and INSERT statements for DATABASECHANGELOGLOCK repeat twenty times during update-sql.

**Example output of update-sql **

Starting Liquibase at 14:27:34 (version [Core: //DAT-8804-2/1016/94950a/2021-12-20 15:37+0000, Pro: DAT-8804-2/288/4ca10b/2021-12-20T15:32:38Z] #1016 built at 2021-12-20 15:37+0000)
Liquibase Version: [Core: //DAT-8804-2/1016/94950a/2021-12-20 15:37+0000, Pro: DAT-8804-2/288/4ca10b/2021-12-20T15:32:38Z]
Liquibase Community [Core: //DAT-8804-2/1016/94950a/2021-12-20 15:37+0000, Pro: DAT-8804-2/288/4ca10b/2021-12-20T15:32:38Z] by Liquibase
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: example-changelog.sql
-- Ran at: 12/20/21, 2:27 PM
-- Against: DBUSER@jdbc:h2:tcp://localhost:9090/mem:dev
-- Liquibase version: [Core: //DAT-8804-2/1016/94950a/2021-12-20 15:37+0000, Pro: DAT-8804-2/288/4ca10b/2021-12-20T15:32:38Z]
-- *********************************************************************

-- Create Database Lock Table
CREATE TABLE PUBLIC.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM PUBLIC.DATABASECHANGELOGLOCK;

INSERT INTO PUBLIC.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, FALSE);

-- Create Database Lock Table
CREATE TABLE PUBLIC.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM PUBLIC.DATABASECHANGELOGLOCK;

INSERT INTO PUBLIC.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, FALSE);

-- Create Database Lock Table
CREATE TABLE PUBLIC.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM PUBLIC.DATABASECHANGELOGLOCK;

INSERT INTO PUBLIC.DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, FALSE);

// repeated a total of 20 times for a changelog with 3 changesets.

Steps To Reproduce

• Install a version of Liquibase greater than v4.6.2.
• Start a database
  -- The H2 database that comes with the Liqubase installer is sufficient
• Execute an update-sql using the attached changelog
  -- Be sure to remove the .txt extension added to make GitHub happy.
  samplechangelog.h2.sql.txt

liquibase --output-file dbcl-in-overdrive.txt update-sql --changelog-file samplechangelog.h2.sql

Actual Behavior

• The update-sql output has multiple CREATE, DELETE and INSERT statements.

Expected/Desired Behavior

• The update-sql output has exactly one CREATE TABLE DATABASECHANGELOGLOCK statement.
• The update-sql output has the correct number of INSERT INTO DATABASECHANGELOGLOCK statements.
  -- Not sure if this is one INSERT at beginning of the output or one INSERT prior to each changeset.
• The update-sql output has the correct number of DELETE FROM DATABASECHANGELOGLOCK statement.
  -- Not sure if this is DELETE at beginning of the output or one DELETE after to each changeset.

Screenshots (if appropriate)

N/A

Additional Context

N/A

[kataggart]

@XDelphiGrl thanks for all the detail here. Seems similar, if not duplicate of, #2295. We are looking at both. Please let me know if you think my analysis of this is incorrect, different, etc. Many thanks again!

[XDelphiGrl]

@XDelphiGrl thanks for all the detail here. Seems similar, if not duplicate of, #2295. We are looking at both. Please let me know if you think my analysis of this is incorrect, different, etc. Many thanks again!

@kataggart, you are correct. Mine is a duplicate. I searched in Issues before I wrote mine but clearly didn't use any useful search terms.