listUnrunChangeSets throws JDBC error from WaitForLock method

[tom2011]

I was trying to run Liquibase 4.1.1.programmatically. However, it is throwing the following exception on an existing database that has the databasechangeloglock table.

INFO: Create Database Lock Table
Exception in thread “main” liquibase.exception.LockException: liquibase.exception.JDBCException: Error executing SQL CREATE TABLE [DATABASECHANGELOGLOCK] ([ID] INT NOT NULL, [LOCKED] BIT NOT NULL, [LOCKGRANTED] DATETIME, [LOCKEDBY] VARCHAR(255), CONSTRAINT [PK_DATABASECHANGELOGLOCK] PRIMARY KEY (ID))
at liquibase.lock.LockHandler.waitForLock(LockHandler.java:176)
at liquibase.Liquibase.listUnrunChangeSets(Liquibase.java:518)
Caused by: liquibase.exception.JDBCException: Error executing SQL CREATE TABLE [DATABASECHANGELOGLOCK] ([ID] INT NOT NULL, [LOCKED] BIT NOT NULL, [LOCKGRANTED] DATETIME, [LOCKEDBY] VARCHAR(255), CONSTRAINT [PK_DATABASECHANGELOGLOCK] PRIMARY KEY (ID))
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:55)
at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:86)
at liquibase.database.AbstractDatabase.checkDatabaseChangeLogLockTable(AbstractDatabase.java:755)
at liquibase.lock.LockHandler.waitForLock(LockHandler.java:145)
… 3 more

[ronak]

@tom2011 ,

Looks like your db is in a locked state as far as liquibase is concerned. This happens when maybe liquibase operation or the target db gets cut off or shut down improperly.

Clearing liquibase databasechangelog locks can be done by running:
liquibase releaseLocks

[tom2011]

I deleted the record in the databasechangelog table but still the same error.

[ronak]

Hi @tom2011 ,

Sorry you are still having issues. Upon looking at this closer, it seems the user you are running as may not have access to create the locktable.

To better diagnose, would you please provide:

• Exact liquibase command you are using (ex. liquibase --logLevel=debug update)
• Complete log (not just the snippet)
• Context of the run, does the Databasechangelog* tables exist before you start? Is this a db target/system that gets recreated multiple times?
• have you tried running liquibase updateSQL? Does that run successfully?

[tom2011]

I was calling listUnrunChangeSets in programmatically in Java. Running liquibase maven plugin works fine. So it is not permission issue.

[ronak]

Would you please answer the other questions?

@ronak

Context of the run, does the Databasechangelog* tables exist before you start? Is this a db target/system that gets recreated multiple times?

You may be right it is not a permission issue but, I am not able to see that based on what you have provided. Are you using the same user / password credentials programmatically as well as in the liquibase maven?

@ronak

Exact liquibase command you are using (ex. liquibase --logLevel=debug update)

Or rather the exact maven liquibase goal you are running that works?

[tom2011]

This works
mvn liquibase:updateSQL -Psqlserver-dev

	<profile>
		<id>sqlserver-dev</id>
		<build>
			<plugins>
				<plugin>
					<groupId>org.liquibase</groupId>
					<artifactId>liquibase-maven-plugin</artifactId>
					<configuration>
						<verbose>false</verbose>
						<changeLogFile>my_changelog_sqlserver.xml</changeLogFile>
						<driver>net.sourceforge.jtds.jdbc.Driver</driver>
						<url>jdbc:jtds:sqlserver://xxx:1433/yyy;PrepareSQL=3;user=aaa;password=bbb</url>
						<migrationSqlOutputFile>target/liquibase/SQLServerDev-${project.artifactId}-${project.version}.sql</migrationSqlOutputFile>
						<promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
						<defaultSchemaName>my_schema</defaultSchemaName>
					</configuration>
				</plugin>
			</plugins>
		</build>
	</profile>

This does not. There is no way to set schema in jtds URL so I have a default schema set to the database account

public void testExecuteLiquidBaseScripts(String folderPath) throws SQLException, ClassNotFoundException, LiquibaseException {
    Class.forName("net.sourceforge.jtds.jdbc.Driver");
    java.sql.Connection connection = DriverManager.getConnection("jdbc:jtds:sqlserver://xxx:1433/yyy;PrepareSQL=3;user=aaa;password=bbb");
    //connection.setSchema("my_schema");

    try {
        Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(connection);
        FileOpener a = new liquibase.FileSystemFileOpener(folderPath);

        Liquibase liquibase = new Liquibase("my_changelog_sqlserver.xml", a, database);
        StringWriter writer = new StringWriter();

        //liquibase.update(mull,writer);
        //liquibase.update(mull,writer);
        List<ChangeSet> unrunChangeSets = liquibase.listUnrunChangeSets(null);
        for (ChangeSet changeSet : unrunChangeSets) {
            writer.append(changeSet.toString());
        }
        System.out.println(writer.toString());


        } finally {
        if (connection != null) {
            connection.rollback();
            connection.close();
        }
    }
}

[ronak]

Hey @tom2011 ,

You could set the schema in your liquibase changeset. All objects can be set with schemaName.object. If schema differs from environments, you can use property substitution. Hope that helps.

Ronak

[tom2011]

Hi @ronak

It is not with my changesets. It is related to an internal liquibase table. Maven plugin works with the same changeset xml. The JAVA method is giving errors. Please have your dev team to review.

Thanks

[ronak]

@NathanVoxland could you help out @tom2011? Thanks.

[MikeOlivas]

What database are you running this against? Does the same thing happen if you use the vendor’s based jdbc driver vs the open source driver?
In maven you are calling updateSQL and in the java method you are using listUnrunChangeSets.

[tom2011]

I am using SQL Server 2017
It is the same if I use mssql-driver

What is the equivalent call in Java for updateSQL if not using listUnrunChangeSets?

[ronak]

Hey @tom2011 ,

From @NathanVoxland

there is liquibase.Liquibase#update() with the arguments that take a Writer which is what updateSQL calls.

We are actively working on the “command framework” which will be an improved and more obvious way to call things like UpdateSql

[tom2011]

Hi @ronak
I tried update() method call but it’s showing all changesets including the ones had executed. Therefore, I was looking into the other method.

[nvoxland]

From your original post, I’m not able to tell what the actual error you’re seeing is. The stacktrace is just saying “error calling create table” but it’s not listing what that actual error was. Is there more of the stacktrace?

In your maven setup, you are setting defaultSchemaName = my_schema. Calling database.setDefaultSchemaName("my_schema") is the corresponding call in the java code. Probably best to call that right after creating the Database instance.

The defaultSchemaName will make it so the SQL generated by liquibase includes objects fully qualified to that schema.

Given that you’re also seeing unrun changes in update, I’m guessing that without the defaultSchemaName being set it is checking for the databasechangelog and databasechangeloglock tables in whatever schema is your connection’s default and not finding either. But, if you do setDefaultSchemaName("my_schema") it will do what you expect.

Does that help?
Nathan

[tom2011]

Hi @nvoxland

Thank you for the suggestion. It is now not throwing errors or showing all changesets after calling

        database.setDefaultSchemaName("my_schema");

Thanks

[ronak]

Glad to hear you got this working @tom2011 , this should be helpful to others with the same use case. Thanks for digging in, and thanks for answering @NathanVoxland