Migrations with ModelDataType.GUID on MySQL

Migrations fail if the MySQL connection string is set to "Old Guids=true" (changing the underlying data type to BINARY(16) instead of the far larger CHAR(36) type).

I see that there is a way to replace the ModelDataType.Guid supplied to the AddTable method with a string of "BINARY(16)" but:

1) The same optional string override does not work in ForeignKeyField definitions and AddForeignKeyColumn method calls so all of our dozen migrations queued for deployment into production next month will fail now that we have modified our underlying GUID type representation.

2) This is not very neat when the rest of the LightSpeed library works with either choice of GUID representation transparently and it's a shame to have to manually tweak the migrations created by the LightSpeed designer.

Is there a workaround for this or any chance of a modification in an upcoming nightly build?

Thanks, Chris

PS: Using LightSpeed 5 nightly 20131210

Quick update - Ive added a change to handle this which will be activated via a custom policy being applied in the designer. Im currently working through testing this so it wont be available until next week and Ill post more details about how to toggle this on once Im able to push this into the nightlies for you.

Jeremy

Hi Jeremy,

That sounds like a neat solution. Thanks for the progress update.

Chris

Hi Chris,

Quick update on this.

Ive tested the changes previous mentioned but Ive run into a snag which is not resolvable, but may be sufficient for you. Ive added an option which allows you to specify that the Old Guid format should be used as a MySqlRoundTrippingPolicy option which will cater for the generation of SQL to match what you were expecting. The snag however comes with updating the model based on the database schema. Because the data type is seen as a binary (We will pick it up as a blob) you will see a change detected wherever a Guid is being used despite no real change being present, so this basically means you would need to ignore all Guids if syncing from the database - this may become quite unusable in a reasonable sized model based on Guid identities.

Its fairly common across database providers for Guids to be represented as strings so we have quite a bit of code in the change detection pipeline to cater but its not trivial or very achievable for us to replicate this change for this specific case as the context around when this option should be used is not going to available when the detection of changes is performed.

So given the lack of round-tripping support Im not particularly keen to merge this as its going to lead to potential issues for us later on when someone else tries to use this but doesn't appreciate the limitations. I can give you a copy of the build from this branch if you are interested to test with though and Im open to any suggestions here.

Jeremy

Hi Jeremy,

I've now converted our migrations project to one that only outputs the generated SQL. That SQL is obviously still incorrect but it is not too difficult to fix with some find/replace manual operations. However, the migrations platform is unable to generate the downgrade migrations SQL, instead throwing an exception about removing foreign keys being unsupported*.

Maybe this is related to the assumptions you mention about GUIDs always being represented by a string?

Or are you saying that only the "Update From Source" function is a problem? We have never used that but I agree that an option with the word "RoundTripping" in it could cause plenty of confusion if it only affects operations for a one-way trip!

We're OK with this workaround for the time being so I don't think it's worth us moving to a custom build at this stage but it would still be good to find a neater way to deal with this situation in future.

I was wondering whether it would be practical for you to define a completely different ModelDataType (e.g. BinaryGuid) but maybe that would hit the same problems you mentioned earlier that prevent the change being merged?

* We don't need this urgently but would feel more comfortable knowing we could generate roll-back scripts easily in future.

The full stack trace is below, let me know if you'd rather I open a separate topic for this.

Mindscape.LightSpeed.Generator.Model.GenerationException: This database provider does not support deleting reference columns. 
   at Mindscape.LightSpeed.Generator.Extraction.DdlUpdater.DeleteColumn(TableName tableName, String columnName, Boolean onlyIfForeignKeysCanBeDropped) 
   at Mindscape.LightSpeed.Migrations.Migration.DropColumn(String tableName, String owner, String columnName, Boolean onlyIfCanRemoveForeignKeys) 
   at WarehouseMigrations.CustomGroupAndItems.Down() in c:\Visual Studio Projects\warehouse\Wildscreen Warehouse\WarehouseMigrations\20140226165031_CustomGroupAndItems.cs:line 43 
   at Mindscape.LightSpeed.Migrations.Migrator.Execute(Migration migration, Direction direction) 
   at Mindscape.LightSpeed.Migrations.Migrator.MigrateDown(MigrationVersion currentVersion) 
   at Mindscape.LightSpeed.Migrations.Migrator.Execute() 
   at Mindscape.LightSpeed.Generator.Migrations.IsolatedHost.MigrationExecutor.GetMigrationsSql(String assembly, ProviderType providerType, String startMigration, String endMigration)

Thanks, Chris

Hi Chris,

Ill investigate the custom type approach and let you know if that yields a solution here.

In terms of the migration error, unfortunately that restriction exists because we cannot always generate a valid script to support this as it relies on us knowing the actual constraint FK names in the database to remove them. You will need to manage this manually via ExecuteNativeCommand (e.g. generate the appropriate SQL elsewhere and then apply that via a native command call).

Jeremy