HI,
My understanding is that futureRollbackSQL does not actually make updates in the database, and this is the functionality that I am seeing when I run a futureRollbackSQL against a database that does not yet have a databasechangelog table. However if I run it against a database that already has a Liquibase change run against it at a previous date, so the databasechangelog table already exists with data on it, I get an error telling me that my user does not have update permissions. The error is correct in that the user I am allowed to use for liquibase is not allowed to write anything to the database, but the problem is that I want to generate a script that can be run with a user that has full access to the database.
The UpdateSQL part runs just fine and I get a script that can be run, but I have to submit a rollback script as well or they will not run my change script.
What makes this even stranger is that it generates the error halfway through the middle of the rollback generation and not at the end of the particular changeset.
Here is an extract showing the error message:
----------------------Start of file
[exec] – Rolling Back ChangeSet: ./changelog/ovaflo.changelog.CHM01199162.xml::ME FORM - DATA - NEW TAB - CHM01199162::DAVID::(Checksum: 3:6019853962771adb94c8ab719db2c24f)
[exec] UPDATE [dbo].[ofp_Tab] SET [Disabled] = ‘Y’ WHERE Application = ‘of_MF_ASPNETRedirect.asp?pagename=/MF.Proj.MotorAssessmentForm.Web/MLForm.aspx’
[exec] GO
[exec] DELETE FROM [dbo].[DATABASECHANGELOG] WHERE ID=‘ME FORM - DATA - NEW TAB - CHM01199162’ AND AUTHOR=‘DAVID’ AND FILENAME=’./changelog/ovaflo.changelog.CHM01199162.xml’
[exec] GO
[exec] – Rolling Back ChangeSet: ./changelog/ovaflo.changelog.CHM01199162.xml::ME FORM - SP - DC_SP_IsFormComplete - CHM01199162::DAVID::(Checksum: 3:e8497cf2a5deb1cf4d4c8c9dfa7d79b5)
[exec] ALTER PROCEDURE dbo.DC_SP_IsFormComplete (
[exec] @userRef int,
[exec] @claimNo varchar(50),
-----------------------------Cut out large procedure
-----------------------------Return halfway through procedure
[exec] IF @checkAllSections = 1
[exec] BEGIN
[exec] – GET THE “ASSESSMENTFORMCOMPLETE” FIELDREF
[exec] SELECT
[exec] @fLiquibase Update Failed: Error executing SQL UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 0, [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL WHERE [ID] = 1: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.
[exec] SEVERE 8/11/11 11:54 AM:liquibase: Error executing SQL UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 0, [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL WHERE [ID] = 1: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.
[exec] liquibase.exception.LockException: liquibase.exception.DatabaseException: Error executing SQL UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 0, [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL WHERE [ID] = 1: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.
[exec] at liquibase.lockservice.LockService.releaseLock(LockService.java:152)
[exec] at liquibase.Liquibase.futureRollbackSQL(Liquibase.java:456)
[exec] at liquibase.integration.commandline.Main.doMigration(Main.java:723)
[exec] at liquibase.integration.commandline.Main.main(Main.java:115)
[exec] Caused by: liquibase.exception.DatabaseException: Error executing SQL UPDATE [dbo].[DATABASECHANGELOGLOCK] SET [LOCKED] = 0, [LOCKEDBY] = NULL, [LOCKGRANTED] = NULL WHERE [ID] = 1: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.
[exec] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
[exec] at liquibase.executor.jvm.JdbcExecutor.update(JdbcExecutor.java:230)
[exec] at liquibase.executor.jvm.JdbcExecutor.update(JdbcExecutor.java:207)
[exec] at liquibase.lockservice.LockService.releaseLock(LockService.java:138)
[exec] at liquibase.Liquibase.futureRollbackSQL(Liquibase.java:457)
[exec] … 2 more
[exec] Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: UPDATE permission denied on object ‘DATABASECHANGELOGLOCK’, database ‘Ovaflo’, owner ‘dbo’.
[exec] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
[exec] at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:246)
[exec] at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)
[exec] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1491)
[exec] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)
[exec] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:677)
[exec] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
[exec] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
[exec] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
[exec] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
[exec] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:634)
[exec] at liquibase.executor.jvm.JdbcExecutor$1UpdateStatementCallback.doInStatement(JdbcExecutor.java:222)
[exec] at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
[exec] … 6 more
[exec] For more information, use the --logLevel flag)
BUILD FAILED - 0 non-fatal error(s), 28 warning(s)
C:\Users\jrussell.MUFEP\Desktop\MF Work\Liquibase\DATABASE\PROD\Ovaflo\prod.ovaflo.build(36,16):
External Program Failed: C:\Users\jrussell.MUFEP\Desktop\MF Work\Liquibase\DATABASE\tools\ikvm\bin\ikvm.exe (return code was -1)
Total time: 13.9 seconds.
----------------------------------------End of file
If I illegally change the username to one with rights to write to the database, the rollback file generates perfectly, however I don’t see any evidence of it having actually changed anything in the DB.
I am using Liquibase 2.0.1