Data Migrations resulting 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.
This is the auto generated "AutomaticSchemaChange.patch" file for a custom data migration:
And this is the user generated SQL for the custom data migration:
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 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.