Upgrade to version 13.3.0 fails if default constraint on "umbracoContentVersion.versionDate" does not exist

[ScottDabroskiWSS]

Which Umbraco version are you using? (Please write the exact version, example: 10.1.0)

13.2.0

Bug summary

Attempting to upgrade to Umbraco version higher than 13.3.0, but the database upgrade fails. This was traced down to the absence of a default constraint on the umbracoContentVersion.versionDate column. The upgrade logic is attempting to rename this constraint, but doesn't check if the constraint exists first.

Specifics

The following logic is called inside the V_13_3_0/AlignUpgradedDatabase.cs class, specifically the AlignContentVersionTable function. If the query does not return an existing constraint name, it still attempts to rename it, which throws an error. This can be resolved by updating the statement if (currentConstraintName == expectedConstraintName) to also check if the currentConstraintName is not set to a value, indicating that the constraint does not exist and therefore cannot be renamed.

        // Renames the default constraint for the column,
        // apparently the content version table used to be prefixed with cms and not umbraco
        // We don't have a fluid way to rename the default constraint so we have to use raw SQL
        // This should be okay though since we are only running this migration on SQL Server
        Sql<ISqlContext> constraintNameQuery = Database.SqlContext.Sql(@$"
SELECT obj_Constraint.NAME AS 'constraintName'
    FROM   sys.objects obj_table
        JOIN sys.objects obj_Constraint
            ON obj_table.object_id = obj_Constraint.parent_object_id
        JOIN sys.sysconstraints constraints
             ON constraints.constid = obj_Constraint.object_id
        JOIN sys.columns columns
             ON columns.object_id = obj_table.object_id
            AND columns.column_id = constraints.colid
    WHERE obj_table.NAME = '{tableName}'
	AND columns.NAME = '{newColumnName}'
	AND obj_Constraint.type = 'D'
");
        var currentConstraintName = Database.ExecuteScalar<string>(constraintNameQuery);


        // only rename the constraint if necessary
        if (currentConstraintName == expectedConstraintName)
        {
            return;
        }

        Sql<ISqlContext> renameConstraintQuery = Database.SqlContext.Sql(
            $"EXEC sp_rename N'{currentConstraintName}', N'{expectedConstraintName}', N'OBJECT'");
        Database.Execute(renameConstraintQuery);

Steps to reproduce

1. Start with an Umbraco server on a version before 13.3.0.
2. Ensure that no constraint exists in the database on the umbracoContentVersion.versionDate column.
3. Attempt to upgrade the Umbraco server to verstion 13.3.0.

Expected result / actual result

Expected outcome: Successfully upgrade the database to version 13.3.0+.
Actual outcome: Database upgrade fails.

[github-actions]

Hi there @ScottDabroskiWSS!

Firstly, a big thank you for raising this issue. Every piece of feedback we receive helps us to make Umbraco better.

We really appreciate your patience while we wait for our team to have a look at this but we wanted to let you know that we see this and share with you the plan for what comes next.

• We'll assess whether this issue relates to something that has already been fixed in a later version of the release that it has been raised for.
• If it's a bug, is it related to a release that we are actively supporting or is it related to a release that's in the end-of-life or security-only phase?
• We'll replicate the issue to ensure that the problem is as described.
• We'll decide whether the behavior is an issue or if the behavior is intended.

We wish we could work with everyone directly and assess your issue immediately but we're in the fortunate position of having lots of contributions to work with and only a few humans who are able to do it. We are making progress though and in the meantime, we will keep you in the loop and let you know when we have any questions.

Thanks, from your friendly Umbraco GitHub bot 🤖 🙂

[ScottDabroskiWSS]

As a workaround, I was able to add the missing constraint using the following script, after which the database upgraded successfully.

ALTER TABLE [DatabaseNameGoesHere].[dbo].[umbracoContentVersion]
ADD CONSTRAINT DF_umbracoContentVersion_VersionDate
DEFAULT getdate() FOR VersionDate; 

[AndyButland]

Fixed in #18063 and planned for the 13.7 release.

We've added an update to the migration that will check for the case where the constraint doesn't exist, and, if it's not found, create it rather than attempting to rename it.