updateSQL on DB2 trying to delete DATABASECHANGELOGLOCK

General Discussion
1

Hi all,

For the past 4-5 months ive been using Liquibase at my company with minimal issue. Over night something changed and it seems liquibase is now constantly trying to drop the DATABASECHANGELOGLOCK table.

So when I use the diffChangeLog between two empty schemas I correctly get an empty change set (json), but when I try to run updateSQL it gives me the following.

[2021-05-04 16:01:50] FINE [liquibase.lockservice] Created database lock table with name: “SCHEMA_NAME”.DATABASECHANGELOGLOCK
[2021-05-04 16:01:50] FINE [liquibase.executor] Executing with the ‘jdbc’ executor
[2021-05-04 16:01:50] FINE [liquibase.executor] Executing with the ‘jdbc’ executor
[2021-05-04 16:01:50] FINE [liquibase.executor] Executing with the ‘jdbc’ executor
[2021-05-04 16:01:50] FINE [liquibase.executor] Executing with the ‘jdbc’ executor
Unexpected error running Liquibase: Error executing SQL SELECT MIN(locked) AS test FROM “SCHEMA_NAME”.DATABASECHANGELOGLOCK FETCH FIRST ROW ONLY: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=SCHEMA_NAME.DATABASECHANGELOGLOCK, DRIVER=4.27.25
For more information, please use the --logLevel flag
[2021-05-04 16:01:50] SEVERE [liquibase.integration] Unexpected error running Liquibase: Error executing SQL SELECT MIN(locked) AS test FROM “SCHEMA_NAME”.DATABASECHANGELOGLOCK FETCH FIRST ROW ONLY: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=SCHEMA_NAME.DATABASECHANGELOGLOCK, DRIVER=4.27.25
liquibase.exception.LockException: liquibase.exception.DatabaseException: Error executing SQL SELECT MIN(locked) AS test FROM “SCHEMA_NAME”.DATABASECHANGELOGLOCK FETCH FIRST ROW ONLY: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=SCHEMA_NAME.DATABASECHANGELOGLOCK, DRIVER=4.27.25

I am using DB2 Warehouse on Cloud (using DB2 JDBC4 driver) and fairly positive that prior to this updateSQL would just give the Create table statements for the DATABASECHANGELOGLOCK and DATABASECHANGELOG.
Also despite the above “Created database lock table” I can confirm it certainly isn’t creating anything.

I don’t think this is a liquibase issue but I am curious how exactly does liquibase determine if the DATABASECHANGELOGLOCK exists because I suspect someone has added some odd permission on something that is prevent liquibase from seeing this and then thinks it needs to delete it.

Any help would be greatly appreciated,
Daniel C

1 Like
2

I have this also Error on Db2 Databases. Please Fix them.

3

So I spent some time looking at this,

it seems the issue is in the StandardLockService.java - with the code in question causing the issue (part in bold added by me);
(executor.updatesDatabase() && (database instanceof DerbyDatabase) && ((DerbyDatabase) database)
.supportsBooleanDataType() || database.getClass().isAssignableFrom(DB2Database.class) && ((DB2Database) database)
.supportsBooleanDataType() && executor.updatesDatabase())

It looks like the initial intention of the code was to perform a migration from the older SMALLINT databasechangeloglock to the newer BOOLEAN, but it seems an assumption was made that the older table MUST exist for this code to perform correctly.

Now by adding the executor.updatesDatabase() to the above DB2 Check it seems that it ignores the check when executing for updateSQL (since the initial table won’t exist on first migration).

So far I’ve managed to test this on a DB2 On cloud (Not DB2 Warehouse), but it seems promising so far. If all goes well I suppose I can raise it as a proper issue on github + PR it?