U4-9544 - Health check to detect if the database has any missing indexes or constraints

Created by Shannon Deminick 20 Feb 2017, 03:57:59 Updated by Sebastiaan Janssen 16 Aug 2018, 12:13:00

Tags: PR

Relates to: U4-11580

This would be useful to detect if the database hasn't been upgraded correctly

1 Attachments

Comments

Søren Gregersen 29 Mar 2018, 10:50:56

AFAIK all the information about indexes are defined on db-entity classes, in migrations and so on.

@Shandem how would this be possible to make? Is there a single source for this information, or should the check be custom for each version?


Shannon Deminick 03 Apr 2018, 01:34:11

We have code that does a comparison of current indexes and schema items vs what is installed. There's a class called DatabaseSchemaResult which is created by the DatabaseContext.ValidateDatabaseSchema. This returns all valid things on the database along with all invalid things (contained in the Errors property)


Dan Patching 06 Jun 2018, 19:48:01

@Shandem I made a health check to call the method you mentioned, but it returns 48 errors (all missing constraints) on a fresh install with no content. So I looked a bit deeper, and it looks like the only place some of the reported missing constraints are referenced, is on the DTO's. If I look at a reported valid index, it's in several places, including a sql upgrade script.

So should I remove the indexes causing errors from the DTO's as part of this PR?


Dan Patching 06 Jun 2018, 21:20:25

https://github.com/umbraco/Umbraco-CMS/pull/2674


Ronald Barendse 15 Aug 2018, 15:11:10

@Shandem Any feedback to what should be done with the missing constrains on a fresh install?


Shannon Deminick 16 Aug 2018, 03:31:14

I'll need to investigate what is going on here and test out this new functionality so i can understand what is being reported. I've added an internal task for us to review.


Sebastiaan Janssen 16 Aug 2018, 06:52:27

Missing constraints are not ignored in this health check and a fresh install doesn't report missing constraints.

When you start messing with constraints you get errors reported as expected.


Ronald Barendse 16 Aug 2018, 12:07:56

The check also writes a warning to the log every time it's executed, even when no errors are found: https://github.com/umbraco/Umbraco-CMS/blob/ffd0adb0f33fe5c7b869f054ac4716dbf47ccaaa/src/Umbraco.Web/HealthCheck/Checks/DataIntegrity/DatabaseSchemaValidationHealthCheck.cs#L42.

Maybe the summary can be shown in the result (although it's only in English): https://github.com/umbraco/Umbraco-CMS/blob/7ee510ed386495120666a78c61497f58ff05de8f/src/Umbraco.Core/Persistence/Migrations/Initial/DatabaseSchemaResult.cs#L170. This could also be used to only write a single warning to the log, so it's not flooded with short, undescriptive messages (I got messages like Unknown: Id, not very usefull).

Oh and all custom tables (like added by Umbraco Forms, etc.) are marked as an error! It should probably filter out additional tables and only report missing tables, fields, constrains/indexes and fields with the wrong db type.


Sebastiaan Janssen 16 Aug 2018, 12:13:00

Yep, created http://issues.umbraco.org/issue/U4-11580 for that last one.

With regards to the summary, that's a great idea, care to make a PR for that? :-)


Priority: Normal

Type: Bug

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted: Pull request

Affected versions:

Due in version: 7.12.0

Sprint:

Story Points:

Cycle: