[Enhancement]: Stop SQL script execution on error about dnn.platform HOT 8 OPEN

Could we add some opt-in attributes to the script component to run scripts fail-fast and/or in a transaction?

from dnn.platform.

@bdukes that would be awesome

from dnn.platform.

Historically the expectation is that SQL Server scripts should be re-runnable, changing this behavior to stop-on-error would be a major breaking change that would impact installation, upgrade, and extension installation/upgrade.

Although I understand the desire here, I'm not sure that the downstream impacts would be worth the change. Just my $0.02 though.

from dnn.platform.

I don't see how this would this prevent a script from being re-runnable. In fact I believe it would make it more so, because instead of running statements out of order this would stop execution and allow whatever problem there was to be resolved before continuing.

I do agree this would be a breaking change, and should probably be held until a major release.

from dnn.platform.

@DanielBolef this wouldn't prevent it from being re-runnable, but it would change the expectation of what is completed or what is not yet completed.

The "rollback" solution for a failure is often to run everything again anyway, so maybe it isn't a big deal, but if we stop abruptly at the first error it could result in many issues with incompatible scripts without manual intervention.

My concern is mostly with upgrade or other issues that right, or wrong, there could be errors. if the scripts stop someone has to manually edit and re-run if we change the behavior, where as the current behavior allows for everything to execute and you can debug. This is a larger issue on upgrades due to possible data issues or otherwise and if certain later scripts don't run the site might not be functional.

from dnn.platform.

@mitchelsellers would your experience tell you that continuing to run dependent scripts after an error is less likely to leave the site in an unrecoverable state than stopping after an unexpected error? I can see scenarios either way.

Personally, I would prefer that the process stop after an error so that it can be investigated, rather than continuing through with the strong possibility that underlying issues are present but not addressed.

from dnn.platform.

@bdukes Yes, I would say the current behavior has a better change of leaving you able to recover, compared to others. A few examples, one third-party module updated some permissions that didn't exist so the script failed, but they also later in the script updated the table structures for all of their data. Since those scripts ran successfully, all of the data was available and the site was not impacted. It was possible to review the failure fix the script and move on. If that script stopped, the site would not have been able to see any content, and in certain circumstances wouldn't even have come online.

We also see this with DNN upgrades, especially on sites with "questionable" pasts where again its 99.9% ok, but that one script messes up. If the site fails to load it is a lot of manual work to re-run via SSMS due to the usage of {objectQualifier} and {databaseOwner}

I normally prefer fail-early as well, it would be ideal if there was a transaction scope that would rollback in the case of a failure.

from dnn.platform.

@bdukes that would be my sugggestion as well, keeping the existing behaviour, if the flag is not set and being able to stop on error, if the flag is set.

in general, it is good practise not to rely on a previous execution but add a check into table modifying commands, e.g by using a WHERE statement or an IF.

from dnn.platform.