I have a sql formatted file that adds 6 SQL memory optimized tables. I made a mistake on one of the table names on the rollback referencing a duplicate table. It created two of the tables and then failed and now it throws an error about those tables existing. What is the best way to recover from this?
I don’t really understand your scenario. Please provide your changelog files, and the commands that you ran.
I’ll give you an example because there is some data I can’t share. You see where I messed up the rollback and the rollback for the second table references the first table? I ran this and it created the first table and then blew up on the second table. Then I fixed the rollback, and it is complaining about the first table already being there. Sorry, it’s hard to explain but it’s a PEBKAC error and I’m trying to figure out the best practice for fixing it.
–liquibase formatted sql
–changeset change1:createTable_Company runInTransaction:false
CREATE TABLE [dbo].[Company]
(
[Id] INT NOT NULL,
[Name] VARCHAR(100) NOT NULL,
[Address] INT NOT NULL,
[Description] VARCHAR(100) NOT NULL,
CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED ([Id])
)
–rollback DROP TABLE [dbo].[Company]
–changeset change1:createTable_YearData runInTransaction:false
CREATE TABLE [dbo].[YearData]
(
[StartYear] INT NOT NULL,
[EndYear] INT NOT NULL
CONSTRAINT PK_YearData PRIMARY KEY NONCLUSTERED([StartYear], [EndYear], [IsFL])
)
–rollback DROP TABLE [dbo].[Company]
I’m still not sure I understand the steps, but I think this is what you did:
- Ran Liquibase “update” command, which executed the two changesets, and created two tables:
- Changeset: createTable_Company & Table: Company
- Changeset: createTable_YearData & Table: YearData
- Ran Liquibase “rollback” command, which executed the second changeset’s rollback (rollback works in reverse chronological order), dropping table Company, and then failed when attempting to execute the first changeset’s rollback.
- Rollback Changeset: createTable_YearData & Table: Company
- Rollback Changeset: createTable_Company FAILED
That leaves your database in a state where Changeset createTable_Company has been executed, but table Company does not exist.
In this scenario personally I would just remove the databasechangelog table row for Changeset createTable_Company, and manually drop the YearData table to reset the environment. Then correct the error in the createTable_YearData Changeset rollback section.
Hope this is clear.
That is very helpful, thank you for your insight.
1 Like