Data Migrations resulting in "A duplicate definition was found for the column"

StefanZ

I have found that checking in data migration scripts can often result in "A duplicate definition was found for the column".
We are very strict in ensuring that we don't use migration scripts for anything other than modifying data. We use seperate check ins for schema changes.

However, it looks like migration scripts will do a snapshot of the schema and it often complains about a duplicate column. It is really annoying, and we have decided to stop using migration scripts altogether now, which is a shame.

Example:

This is the auto generated "AutomaticSchemaChange.patch" file for a custom data migration:
https://justpaste.it/1a5n3

And this is the user generated SQL for the custom data migration:
https://justpaste.it/1a5n4

I can guarantee that the table schema has not changed at all before or after the migration script check in whatsoever. It is definitely 100% correct in the local database that was used to create the migration script and that was used to check into the original build nuget package.

When the release happens to the target environment, we receive the following error in our VSTS release:

2017-08-16T23:35:32.0048878Z FINISHED WITH ERROR: Updating database
2017-08-16T23:35:32.0048878Z
2017-08-16T23:35:32.0048878Z
2017-08-16T23:35:32.0048878Z
2017-08-16T23:35:32.0048878Z Error: Comparison of 'Scripts.state' and
2017-08-16T23:35:32.0048878Z 'xxxxxxxxxx.database.windows.net.sqldevcho' failed: A duplicate definition was
2017-08-16T23:35:32.0048878Z found for the column NotificationLevelId. Ensure that case sensitivity options
2017-08-16T23:35:32.0211925Z are set correctly and all object creation scripts are valid. If the problem
2017-08-16T23:35:32.0211925Z persists, contact Redgate support.
2017-08-16T23:35:32.0211925Z Updating database failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare
2017-08-16T23:35:32.0211925Z Running SQLCompare failed with error: SQLCompare failed with exit code 126: see output above for more information, or for generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare


It seems that when Redgate tries to apply the data migration, it does a diff check between the table schemas in the first link ("AutomatciSchemaChange.patch") and for some reason is perhaps trying to create the NotificationLevelId column or Foreign Key.

JonathanD

I've just run into this same issue and we were on the last trial of using Migration Scripts. Prior to this we tried using the scripts actually attached to the schema changes and had unexpected behavior; now with the blank script we ran into this problem. I can understand this to a point however, it makes it almost impossible for us to create custom scripts and store them in source control.

Sergio R

Hi,

This is usually caused by a bug in SQL Source Control, you should be able to workaround this by changing the column order in the target table so that it matches the column order of the table script in Source Control.

Thank you,

StefanZ

Hi, Thanks Sergio. Good to know there is a workaround, or at worse, we delete the contents of the .patch file to stop the comparison.

However, if it is a known bug with SQL Source Control, are Redgate looking to fix that?

Kind regards,

Sergio R

Hi Stefan,

It's in the backlog but at the moment I don't know when it will be fixed.

Thank you,

Luc

Any news on this? I have the same error and can't seem to get around it. Any help would be great!

Sergio R

Hi,

I am afraid it's not fixed yet.
If the workaround is not working for you it might be that you are encountering a different issue, if you have a valid support and upgrades contract, please log a support ticket at https://productsupport.red-gate.com/hc/en-us so that we can look in more detail at the issue you are having.

Kind Regards,

AlbertWong

Hello, I am trying to run a migration script which errors with a similar message. What is the workaround?

Exception syncing database PPD. Exception was SQLCompare.exe terminated with the exit code 79: Databases not identical.
The source and target databases were supposed to be identical, but were different.
SQLCompare.exe produced this error output:
Error: Comparison of 'Scripts.h5ufk3wg.nuc' and 'hcodev.ad.maximusdev.com,50002.PPD' failed: A duplicate definition was found for the column '[dbo].[PlanChildPlan].PlanTypeID'. Ensure that case sensitivity options are set correctly and all object creation scripts are valid.If the problem persists, contact Redgate support.
Comparison of 'Scripts.h5ufk3wg.nuc' and 'hcodev.ad.maximusdev.com,50002.PPD' failed: A duplicate definition was found for the column '[dbo].[PlanChildPlan].PlanTypeID'.
Ensure that case sensitivity options are set correctly and all object creation scripts are valid.If the problem persists, contact Redgate support.