Moving a column from one table to another

MGibson

I have the latest SQL Source control and DML components and am trying to move a column with data from one table to another. I have tried doing this using the following steps.
1. Add new column to table 1 and commit change.
2. Create a migration script to populate the new column in table1 from the old column in table2.
3. Drop the old column from table2.
I can check this all in fine.
The DLM automation process is fine after step 1, but fails on step 2.

Running DLM automation after committing the migration script gives

Error: Comparison of 'Scripts.state' and 'vsdevbuildsvr1.VETSPACE_SNAPSHOT_INTEGRATION' failed: A duplicate definition
was found for the trigger '[dbo].[tr_Category_B_D]'. Ensure that case sensitivity options are set correctly and all
object creation scripts are valid.If the problem persists, contact Redgate support.

So currently I cannot populate the data and drop the old column.
I will have to populate the data in a post script, and drop the old column in a later iteration.
Does anyone else have issues moving columns with DLM automation?

Thanks

Mike U

There's a specific bug in SQL Source Control here around migration script dependencies on tables that have triggers attached to them. The dev team have responded with more detail in the support ticket you've already submitted for this, but unfortunately that response got lost because the member of the support team who was dealing with it is currently on holiday - I'm very sorry about that, and I'll get someone else to take over the ticket.

So there's not a general issue with moving columns using migration scripts, but there is a problem with using migration scripts to make changes to tables with triggers attached.

MGibson

Hi Mike
Do you know when a fix will be issued for this?
We are currently running 5.8.3.6733 of SQL Source Control.

MGibson

Hi Mike
Just to confirm, the Category table that is producing the error has a before and after trigger.
The migration script does not attempt to change data in the Category table.

Mike U

No, but it does depend on the Category table, which means the Category table needs to be in the Compare block that runs before the migration (see https://documentation.red-gate.com/display/SOC5/How+migration+scripts+work). The bug is that when DLM tries to build up the automatic schema change before the migration script, it ends up duplicating the trigger. So the workaround we supplied on the support ticket was to mark the automatic schema change as already deployed to the target database, by following the steps here.

I'm afraid I can't give you any timelines on when the trigger dependency bug will be fixed.

MGibson

Any support ticket we have on this issue was raised by a developer who is now on holiday. Please can I have a link to the support ticket you are referring to.

Mike U

I'll ask support to get in touch with you.

MGibson

Can I confirm the workaround? You want us to mark the migration script that is causing the error as deployed by running the sql you have provided against the target database. This begs the question, what is the point of adding the migration script in the first place if it is not going to be run. The fact that I have created the migration script means there is a data change I want to run to move data from the old to the new column, After running the migration script I want to delete the old columns, in the same build iteration as adding and populating the new column. Please confirm what we have to do here?
If we cannot run the migration script, we are going to have to populate the new column in a post deployment script, and then remove the old column in a later iteration, which is far from ideal and leads to us deploying code that is sub-optimal to work around a defect in your software.
If the workaround works as I have described, can I strongly suggest this bug is treated as high priority, This one defect has caused us huge problem in our development process, and was first raised to you months ago. We have been going back and forward trying to work out what the problem was, and trying different solutions.

MGibson

Hi Mike
The workaround did not work. I added the sql as you said in out pre-deployment and got the same error as before about the duplicate trigger.
Please advise.

Mike U

Hi,

Sorry, I wasn't clear enough. The migration that you need to mark as already deployed is the automatic schema change part that comes before the migration script you want to run - it's incorrectly trying to add a trigger that has already been deployed to your target database. I've been more specific with the names and ids of the relevant migration in the support ticket response to your colleague - I'll get you added to that ticket.

Mike

MGibson

After communicating with support, the work around does not work for us, as this problem exists for many of our migration scripts and for any new migration that we create.
Therefore we currently cannot create any new migration scripts without this defect being fixed.
What that means for us in the case of moving a non-nullable column to a different table as an example;
1. We add the new column as nullable and check this in.
2. We populate the data in the new column from the old one in a post deployment script which runs after all the other processes have completed in our CI.
3. We raise a JIRA for the next release cycle to remove the old column and make the new column non-nullable, which will involve another post deployment fix to ensure there are no null values.
This means we are shipping a half way change to production and this will become very complex for major database refactoring work, which we are about to embark on for some parts of our system.
I cannot emphasis enough how important this defect fix is to us.
If possible I would like to discuss this with someone at Redgate over the phone.

Thanks

Mike U

Hi,

You can request a phone call through the support ticket, which I think you've now been added to.

We have investigated further on that support ticket, and narrowed the issue down to an interaction between having the trigger created and deployed in an earlier commit (i.e. not a migrations commit) and having the Ignore Fill Factor and Ignore Filegroups options enabled. If you can work without those options enabled, then the migrations pre-state should work correctly.

This is quite an environment-specific issue, so it would be easier to continue the support discussion via the support ticket where we can be more specific about the details. Hopefully you have been sent a link to that ticket, but if not please let me know.

Thanks,
Mike