Error with existing table from previous changelog apply

General Discussion
1

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?

2

I don’t really understand your scenario. Please provide your changelog files, and the commands that you ran.

3

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]

4

I’m still not sure I understand the steps, but I think this is what you did:

  1. Ran Liquibase “update” command, which executed the two changesets, and created two tables:
  • Changeset: createTable_Company & Table: Company
  • Changeset: createTable_YearData & Table: YearData
  1. 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.

5

That is very helpful, thank you for your insight.

1 Like