here is the scenario where liquibase is failing to manage its DATABASECHANGELOG table in multiple
database environments with same schema name…
we have 3 databases in snowflake
each having the same schema name i.e TEAM_TEAMNAME_XYZ_SCHEMA (for eg).
we are using a service account to run update command against each of these databases.
the service account has role assigned to them as per the database.
what we found out was that liquibase on issuance of the ‘update’ command does the following thing behind the scene:
show /* JDBC:DatabaseMetaData.getTables() */ tables like ‘DATABASECHANGELOG’ in account
which in short is weird for the reason that , in case it finds the ‘DATABASECHANGELOG’ in one of the
databases where it is already present and not currently present in the target database, it starts writing in the target database to the table (i.e DATABASECHANGELOG) ,without creating it and finally throws the error.
"SQL compilation error: Object ‘CERT.TEAM_TEAMNAME_XYZ_SCHEMA.DATABASECHANGELOG’ does not exist or not authorized.
SELECT MD5SUM FROM TEAM_TEAMNAME_XYZ_SCHEMA.DATABASECHANGELOG WHERE MD5SUM IS NOT NULL "
NOTE: in this case,the changes have been successfully applied to the DEV databases, since DEV being the first environment liquibase runs against with no exising changelog and changelock tables, liquibase ends up applying changes after creating them.
but as soon liquibase is pointed toward the CERT environment for the purpose of build promotion, it fails., as behind the scenes it finds out the table DATABASECHANGELOG is already present in DEV database, and without even creating it ,it starts complaining about authorization. However it successfully creates the ‘DATABASECHANGELOGLOCK’ in the target database.
STEPS to replicate: for snowflake database:
- create 3 diff databases in snowflake
- create same schema names in all of them
- use the same service account to write liquibase chagnes to them.