[v1.9.3] Not detecting existing DATABASECHANGELOG table in MS SQLServer database

General Discussion
1

Hi all,

I’m running into a situation where liquibase is not detecting that a certain MS SQL Server db already has the DATABASECHANGELOG and LOCK tables it in, and as a result is trying to create them and then run ALL of the migrations again.

I think it may have something to do with the user account. When I look at the database in SQLServer Management Studio it shows all the tables prefixed with the username. This is the same username that I am using to establish the connection to the database, so it shouldn’t be an issue. But may still be.

Any help would be appreciated.

Thank you,
Evan

2

I had the same problem, but I only fixed it for 2.0RC1. LiquiBase is looking to databasechangelog in the meta data instead of DATABASECHANGELOG. My solution was to override MSSQLDatabaseSnapshotGenerator#convertTableNameToDatabaseTableName and not to lower case the table name.

    public class MSSQLDatabaseSnapshotGenerator extends liquibase.snapshot.core.MSSQLDatabaseSnapshotGenerator {

      @Override
      public int getPriority(Database database) {
        return database instanceof MSSQLDatabase ? PRIORITY_DATABASE + 1 : PRIORITY_DATABASE;
      }
     
      @Override
      protected String convertTableNameToDatabaseTableName(String tableName) {
          return tableName;
      }
     
    }

When you place this class in liquibase.ext.snapshot it will be loaded in 2.0.

3

Thanks for the reply.  I got a little further with this problem.  It seems that the database I was connecting to had a “schema” with a different name than the database or catalogname.  I guess in SQL Server 2005 and later the notion of a “schema” became just a namespace within a database. So the hierarchy is like this:  A Db server has many catalogs which have many schemas.

The solution was to set the defaultSchemaName on the Database object.  The funny thing is that it turned out to be relatively easy to get the schema in the current database:

“SELECT DISTINCT SCHEMA_NAME(schema_id) FROM sys.tables”

So I basically took the return from that query and set the defaultSchemaName to that value.  It is possible that this query might return multiple results if you have multiple schemas in your db/catalog, but that is never the case for our product.

Evan