search

chucknorris / roundhouse

RoundhousE is a Database Migration Utility for .NET using sql files and versioning based on source control
http://projectroundhouse.org
916 stars 249 forks source link

Strange problem #436

Closed strandloper closed 2 years ago

strandloper commented 2 years ago

This may not be an issue in RoundhousE at all but I have been poking at it for days and have not found the cause for what is happening. If anyone has seen something similar or has any suggestions I'd appreciate it.

Starting with a database that is at a particular major release (21.04 i.e April 2021) of our application we should be able to use RH to upgrade to any point but for some reason if I skip the next release (21.06) and try to use Roundhouse to apply the scripted changes for both 21.06 and 21.09 in one operation it fails. However if I first apply 21.06 in one RH run then 21.09 in another it works fine.

There does not appear to be an error in any script as I created a Powershell script to loop through them in the same order shown by a RH dry run, executing each with sqlcmd.exe and no error occurred. Similarly I laboriously added one new script at a time, running RH after each and they all applied without error. In both cases spot checks showed that the scripted changes were made.

Even more strangely the first script that fails is one that checks two tables (Products and Arch_Products) for existence of a new column. If not found they are added. Then within the same script an attempt is made to alter an insert/update trigger that inserts from Inserted to Arch_Products, adding the new column to the trigger. This is where it fails, saying that the column can not be found. I find it hard to understand how the columns don't exist, considering they should have been created in the same script, but in different batches (i.e. separated by GO statements).

If I resolve that error by commenting out the trigger alter, it just results in another failure with a later script also reporting a newly added column to be missing. When I resolve each in turn by commenting out script code until the RH run completes successfully and go back to check if changes were applied it appears that none of the changes in the newly run scripts were actually applied, or if they were, they were rolled back. But even enabling the debug switch doesn't result in the RH log giving any indication of a problem having occurred.

Apologies for the long ramble, but I am really struggling to find an explanation for what is happening.

erikbra commented 2 years ago

It's very difficult to mean anything sensible for me without any more details, but the first thing I think of, is the transactional support. Are you running RoundhousE with transactions? There might be some issues there, that are different when you are just running one and one script via Powershell, where each script will run in its own transaction, and will be committed before the next one is run.

(see https://github.com/chucknorris/roundhouse/wiki/ConfigurationOptions#switches for details on command-line switches)

strandloper commented 2 years ago

Thanks for looking over my sad tale. To answer your question, we are not running with transactional support.

What I have discovered since I posted was that there are three scripts that run fairly early on that are conditionally using SET NOEXEC ON to skip running of some portions then SET NOEXEC OFF before the scripts end. They are rather complex scripts and I'm leaning toward the thought that in some cases one or more of these scripts is early terminating without raising an error level that will stop the run but before the batch containing the SET NOEXEC OFF is reached. My theory is that this may then cause many of the other scripts to run while NOEXEC is one, effectively doing nothing. I am looking into the possibility of rewriting these scripts so that NOEXEC is not used at all and seeing if that makes a difference.

erikbra commented 2 years ago

It seems to me that the issues you are having are not related to RoundhousE per se. If you discover later that they are actually related to RoundhousE anyway, please feel free to open a new issue, providing a bit more details, and maybe a smaller example that demonstrates the issue in question.

Good luck solving your database script issues!

strandloper commented 2 years ago

I'm not sure I agree that this is not a RoundhousE problem, however without being able to pinpoint an error I realise finding the cause is near impossible, so closing the issue is fine.

Should anyone read this issue thread in future it may be helpful to know that my previous suspicion was correct; it has something to do with NOEXEC. After rewriting the few scripts that used NOEXEC in a different way the entire run now completes without failure and the scripted changes are applied.