ActionTemplateVersion can make a step template un-editable

[markryd]

Every time you update a step template we write the old version to ActionTemplateVersion. For some reason that isn't clear to me that table has a uniqueness constraint on the Name/Version pair.

So if I have a step called "My Step" and rename it to "Obsolete", ActionTemplate will show Name: "Obsolete", Version: 1 and there will be a record in ActionTemplateVersion with Name: "My Step", Version: 0.

I then go and create a new step called "My Step". That works fine because there isn't another ActionTemplate with that name. Then I go to edit the step and it fails. Because when we go to save the update we try to create a record in ActionTemplateVersion with Name: "My Step", Version: 0 and can't because of the uniqueness constraint.

Reported: https://help.octopusdeploy.com/discussions/problems/64560-can-not-update-rename-newly-created-step-template-in-406

Workaround

1. Use a different name
2. If you can't change the name because saving triggers this error then export the step template and then import it with a different name.

[pawelpabich]

The error message can be improved here but this is done on purpose.

We used to keep only the latest version of a given Step Template (aka Action Template). This caused us a lot of problems when steps were upgraded from an old version of a template to the latest version of the template. The upgrade processes didn't have enough information to update steps correctly. To solve this problem we introduced versioning to step templates. This means that they became immutable and each change to a template would create a new version of it. This also means that a step that is based on a specific version of a step template is guaranteed to be isolated from any changes made to the latest version of the template.

One of the side effects of this approach is that we can potentially end up with multiple versions of different templates sharing the same name.

There are at least two problems that this might cause:

• There are quite a few places in the UI where we display the name of the template and having two or more different templates that share the same name might confuse users. There is no other concepts that allows for that.
• There is quite a bit of code out there that assumes that ActionTemplate.Name is unique. Without a unique constraint on ActionTemplateVersion.Name/Version that assumption is not true in certain situations.

My view is that this is a reasonable trade off and customers should not face this problem often. That being said we could improve validation message so when this happens the customer can solve this problem on their own. We could also check during AT creation/update whether there are ATV with the same name. In this way we can notify the user about this problem earlier.

[danielfischeroctopus]

Potentially another source: https://secure.helpscout.net/conversation/712911629/35439?folderId=845288

[johnsimons]

@pawelpabich Your comment says that this is by design, but I was wondering is we were to keep the existing constraint, but also add LatestActionTemplateId to it, it would fix the problem. At least in my testing, this works. Or do you foresee more issues?

So my fix would be:

ALTER TABLE dbo.ActionTemplateVersion
    DROP CONSTRAINT UQ_ActionTemplateVersionUniqueNameVersionPerSpace
GO
ALTER TABLE dbo.ActionTemplateVersion ADD CONSTRAINT
    UQ_ActionTemplateVersionUniqueNameVersionPerSpace UNIQUE NONCLUSTERED 
    (
    Name,
    Version,
    LatestActionTemplateId,
    SpaceId
    )
GO

Currently the constraint is:

UQ_ActionTemplateVersionUniqueNameVersionPerSpace UNIQUE NONCLUSTERED 
    (
    Name,
    Version,
    SpaceId
    )

[pawelpabich]

This will make sure you will not get this error but it allows you to have 2 or more templates with the same name. It's all about trade-offs.

• There are quite a few places in the UI where we display the name of the template and having two or more different templates that share the same name might confuse users. There is no other concepts that allows for that.
• There is quite a bit of code out there that assumes that ActionTemplate.Name is unique. Without a unique constraint on ActionTemplateVersion.Name/Version that assumption is not true in certain situations.

I still believe we should do this:

We could also check during AT creation/update whether there are ATV with the same name. In this way we can notify the user about this problem earlier.

[johnsimons]

@pawelpabich but the table [ActionTemplate] will not have that issue, this would only affect the history table (ActionTemplateVersion), or is the history table also problematic?

[pawelpabich]

or is the history table also problematic?

Yes, we use this table to display data in the UI.

[alexrolleyoctopus]

Another report

[andrewsav-bt]

This is anecdotal, but could be relevant. I had two slack chat sessions with OD guys, to fix the issue with my OD installation that manifested in OP. Each time I was given some custom SQL commands to run to "fix" the data. It cleared up the problem for time being.

Now when I'm trying to upgrade OD to the latest version the Database Migration fails, and upgrade fails as well. This note is not to discuss my upgrade problem, I'll deal with it separately, it's just to point out that design decisions to leave it as it is can potentially cause problems like mine, which is usually difficult to troubleshoot. I wish we could do without custom sql fixes that could potentially prevent database upgrade down the line.

Just something to consider when deciding how to tackle this one.

[pawelpabich]

Another report

[jjanuszkiewicz]

Hello Octopus Deploy team, the reporter of Another report here. Can at least the error message be improved, as currently there is no way for a user to understand what's going on when he hits this bug? Also, is there any plan to fix it at all? It's strange that existence of some old version of one step template blocks an unrelated step template from being saved.

[NickJosevski]

This customer hit a related issue after performing a migration, they ended up with 2 version 0 entries for the same step template. After repairing that data, they were in a position to hit this error, because the entry in the [ActionTemplate] table was now at version 0. Having access to their database I gave them repair code that should get them unstuck.

https://secure.helpscout.net/conversation/980964766/51554?folderId=3252735

[FinnianDempsey]

Another report of this issue.