You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
When generating a SQL file for Microsoft SQL Server using Ant, the update statement to lock the database is done prior to the USE statement. When running the SQL script in SQL Server Management Studio, if the DBA has a different database selected then this update statement will fail as the DATABASECHANGELOGLOCK table may not exist in the selected database.
The DATABASECHANGELOGLOCK table does exist in the database, which is why liquibase is not generating the SQL for creating the table
Steps To Reproduce
When generating a SQL script file for Microsoft SQL Server using Ant the update statement to lock the database is done prior to the USE statement.
This is what is generated by liquibase:
-- Lock Database
UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'server', LOCKGRANTED = GETDATE() WHERE ID = 1 AND LOCKED = 0
GO
USE myDatabase;
GO
-- myDatabase does in fact contain the DATABASECHANGELOGLOCK table that should be updated
Expected/Desired Behavior
The update statement to lock should occur after the USE statement so the expected output of generating the SQL file should look like the following:
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log:
-- Ran at: 2/13/24, 2:57 PM
-- Liquibase version: 4.25.1
-- *********************************************************************
USE myDatabase;
GO
-- Lock Database
UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'server', LOCKGRANTED = GETDATE() WHERE ID = 1 AND LOCKED = 0
GO
Liquibase Version
4.25-1
Database Vendor & Version
Microsoft SQL Sever 2019
Liquibase Integration
ant
Liquibase Extensions
No response
OS and/or Infrastructure Type/Provider
Windows
Additional Context
No response
Are you willing to submit a PR?
I'm willing to submit a PR (Thank you!)
The text was updated successfully, but these errors were encountered:
Hi @jjamieson-va, thank you for reporting this issue.
I have been able to reproduce it with Liquibase version 4.26.0, both with and without Ant. In my case the changesets still ran with no issues, but I agree that the USE statement should be at the beginning of the script.
I'll be leaving this ticket open for the community to propose a fix. Our development team will be available to provide guidance to whoever submits a PR.
Thank you Tatiana, in most cases the generated script does run without issue. Where we see an error is when using SQL Server Management Studio (SSMS). If the user logged in to SSMS attempts to run the script and they have a database selected in SSMS (e.g. master instead of myDatabase as in the example code above) that does not have the DATABASECHANGELOGLOCK table in it, then the first statement of setting locked=1 fails. The rest of the script does run successfully, but it does create a scenario where the locking is not being applied correctly prior to running the rest of the script.
Search first
Description
When generating a SQL file for Microsoft SQL Server using Ant, the update statement to lock the database is done prior to the USE statement. When running the SQL script in SQL Server Management Studio, if the DBA has a different database selected then this update statement will fail as the DATABASECHANGELOGLOCK table may not exist in the selected database.
The DATABASECHANGELOGLOCK table does exist in the database, which is why liquibase is not generating the SQL for creating the table
Steps To Reproduce
When generating a SQL script file for Microsoft SQL Server using Ant the update statement to lock the database is done prior to the USE statement.
Ant Task:
This is what is generated by liquibase:
-- Lock Database
UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'server', LOCKGRANTED = GETDATE() WHERE ID = 1 AND LOCKED = 0
GO
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log:
-- Ran at: 2/13/24, 2:57 PM
-- Liquibase version: 4.25.1
-- *********************************************************************
USE myDatabase;
GO
-- myDatabase does in fact contain the DATABASECHANGELOGLOCK table that should be updated
Expected/Desired Behavior
The update statement to lock should occur after the USE statement so the expected output of generating the SQL file should look like the following:
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log:
-- Ran at: 2/13/24, 2:57 PM
-- Liquibase version: 4.25.1
-- *********************************************************************
USE myDatabase;
GO
-- Lock Database
UPDATE DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'server', LOCKGRANTED = GETDATE() WHERE ID = 1 AND LOCKED = 0
GO
Liquibase Version
4.25-1
Database Vendor & Version
Microsoft SQL Sever 2019
Liquibase Integration
ant
Liquibase Extensions
No response
OS and/or Infrastructure Type/Provider
Windows
Additional Context
No response
Are you willing to submit a PR?
The text was updated successfully, but these errors were encountered: