Problem with Sql in Compatible Mode

[lprada]

I have a SQL 2012 Database with compatability mode 2005. The migration want to execute 3 times ( if I set the compatibiity to 2008 the migratiosn aree done twice). The Second time fail beacuse I need to execute a custom SQL that cannot be execute again ( create a table). Waht can I do?

[dradovic]

Can you pease add more details in order for me to reproduce the issue? What migration is being executed more than once? And is it executed in-memory (as part of the validation process) or physically on the database? Or is this a new issue concerning SQL Server 2012?

[lprada]

All Migrations are executed. I dont know if it is memory or not but if you put sql2012 to 2005 compatibility the migrations get executed 3 times.

[dradovic]

How do you know that the migrations are executed 3 times?

[lprada]

I set a BreakPoint in a migration file and It gets call 3 times.

[dradovic]

Well, Mig# first runs the code of a migration in-memory in order to check for validation errors before actually executing the migration on the database (according to the fail early principle). I guess, in your case you have 2 runs to validate for SqlServer2005 and SqlServer2008 + the actual execution = 3. If you are unsure, have a look at the call-stack. In any case, a migration is only executed ONCE on a specific database.

[lprada]

Yes but I have an error because I have a CREATE TABLE as custom SCRIPT and the 2 time the error is that the table already exists.

[dradovic]

What do you mean by "custom script". Are you executing some SQL indepentendly of Mig# as part of the migration?

[lprada]

this is my migration

public void Up(IDatabase db) { switch (db.Context.ProviderMetadata.Name) { case ProviderNames.SqlServer2005: case ProviderNames.SqlServer2008: db.Execute(@"CREATE TABLE WebPublish(LoginEmpresa VARCHAR(20) NOT NULL, PathRaizPublicacion VARCHAR(255) NOT NULL, SqlHost VARCHAR(255) NOT NULL, SqlDbName VARCHAR(255) NOT NULL, SqlUser VARCHAR(255) NOT NULL, SqlPassword VARCHAR(255) NULL, CONSTRAINT PK_WebPublish PRIMARY KEY CLUSTERED ( LoginEmpresa ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARY"); db.Execute(@"ALTER TABLE aud_clas ALTER COLUMN cod_us NVARCHAR(50)"); break; default: throw new InvalidOperationException("Provider not supported"); } }

[dradovic]

Okay, this looks fine. You're using db.Execute which shouldn't be executed on the database more than once. Can you please copy the 3 call-stacks? When does the error occur: at the 2nd or 3rd time you hit the break-point?

[lprada]

ok, give me a few minutes

[lprada]

this is the call stack, but we found a error in our code, now i cannot reproduce the error

First Call

Cardinal.DAU.Applications.EasyDoc.dll!Cardinal.DAU.Applications.EasyDoc.Migration00000000000004.Up(MigSharp.IDatabase db = {MigSharp.Core.Entities.Database}) Line 12 C# MigSharp.dll!MigSharp.Process.BootstrapMigrationStep.GetDatabaseContainingMigrationChanges(MigSharp.MigrationDirection direction = Up, MigSharp.IMigrationContext context = {MigSharp.Process.MigrationContext}) + 0x63 bytes
MigSharp.dll!MigSharp.Process.MigrationStep.Report(MigSharp.IMigrationContext context = {MigSharp.Process.MigrationContext}) + 0x55 bytes
MigSharp.dll!MigSharp.Process.Validator.Validate(System.Collections.Generic.IEnumerable reporters = {System.Linq.Enumerable.WhereSelectEnumerableIterator<MigSharp.Process.ApplicableMigration,MigSharp.Process.MigrationStep>}, out string errors = null, out string warnings = null) + 0x249 bytes
MigSharp.dll!MigSharp.Process.MigrationBatch.Execute() + 0xdc bytes MigSharp.dll!MigSharp.Migrator.MigrateAll(System.Reflection.Assembly assembly = {System.Reflection.RuntimeAssembly}, System.Reflection.Assembly[] additionalAssemblies = {System.Reflection.Assembly[0]}) + 0x8f bytes
Cardinal.DAU.Windows.exe!Cardinal.DAU.Windows.MainForm.BackgroundWorker1_DoWork(object sender = {System.ComponentModel.BackgroundWorker}, System.ComponentModel.DoWorkEventArgs e = {System.ComponentModel.DoWorkEventArgs}) Line 376 + 0x46 bytes C# System.dll!System.ComponentModel.BackgroundWorker.OnDoWork(System.ComponentModel.DoWorkEventArgs e = {System.ComponentModel.DoWorkEventArgs}) + 0x87 bytes
System.dll!System.ComponentModel.BackgroundWorker.WorkerThreadStart(object argument = {Cardinal.DAU.Common.DAUProcess}) + 0x88 bytes
[Native to Managed Transition]
[Managed to Native Transition]
mscorlib.dll!System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(System.RuntimeMethodHandle md = {System.RuntimeMethodHandle}, object[] args = {object[1]}, object server = {System.ComponentModel.BackgroundWorker}, out object[] outArgs = null) + 0x48 bytes
mscorlib.dll!System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(System.Runtime.Remoting.Messaging.IMessage msg = {System.Runtime.Remoting.Messaging.MethodCall}, System.Runtime.Remoting.Messaging.IMessageSink replySink = {System.Runtime.Remoting.Messaging.AsyncResult}) + 0x231 bytes
mscorlib.dll!System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem.DoAsyncCall() + 0x5b bytes
mscorlib.dll!System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem.ThreadPoolCallBack(object o = {System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem}) + 0x47 bytes
mscorlib.dll!System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(object state = {System.Threading.QueueUserWorkItemCallback}) + 0x75 bytes
mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext = {System.Threading.ExecutionContext}, System.Threading.ContextCallback callback = {Method = {System.Reflection.RuntimeMethodInfo}}, object state = {System.Threading.QueueUserWorkItemCallback}, bool preserveSyncCtx = true) + 0x1a2 bytes
mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext = {System.Threading.ExecutionContext}, System.Threading.ContextCallback callback = {Method = {System.Reflection.RuntimeMethodInfo}}, object state = {System.Threading.QueueUserWorkItemCallback}, bool preserveSyncCtx = true) + 0x33 bytes
mscorlib.dll!System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() + 0x95 bytes mscorlib.dll!System.Threading.ThreadPoolWorkQueue.Dispatch() + 0x22e bytes
mscorlib.dll!System.Threading._ThreadPoolWaitCallback.PerformWaitCallback() + 0x18 bytes
[Native to Managed Transition]

Second

Cardinal.DAU.Applications.EasyDoc.dll!Cardinal.DAU.Applications.EasyDoc.Migration00000000000004.Up(MigSharp.IDatabase db = {MigSharp.Core.Entities.Database}) Line 12 C# MigSharp.dll!MigSharp.Process.BootstrapMigrationStep.GetDatabaseContainingMigrationChanges(MigSharp.MigrationDirection direction = Up, MigSharp.IMigrationContext context = {MigSharp.Process.MigrationContext}) + 0x63 bytes
MigSharp.dll!MigSharp.Process.MigrationStep.Report(MigSharp.IMigrationContext context = {MigSharp.Process.MigrationContext}) + 0x55 bytes
MigSharp.dll!MigSharp.Process.Validator.Validate(System.Collections.Generic.IEnumerable reporters = {System.Linq.Enumerable.WhereSelectEnumerableIterator<MigSharp.Process.ApplicableMigration,MigSharp.Process.MigrationStep>}, out string errors = null, out string warnings = null) + 0x249 bytes
MigSharp.dll!MigSharp.Process.MigrationBatch.Execute() + 0xdc bytes MigSharp.dll!MigSharp.Migrator.MigrateAll(System.Reflection.Assembly assembly = {System.Reflection.RuntimeAssembly}, System.Reflection.Assembly[] additionalAssemblies = {System.Reflection.Assembly[0]}) + 0x8f bytes
Cardinal.DAU.Windows.exe!Cardinal.DAU.Windows.MainForm.BackgroundWorker1_DoWork(object sender = {System.ComponentModel.BackgroundWorker}, System.ComponentModel.DoWorkEventArgs e = {System.ComponentModel.DoWorkEventArgs}) Line 376 + 0x46 bytes C# System.dll!System.ComponentModel.BackgroundWorker.OnDoWork(System.ComponentModel.DoWorkEventArgs e = {System.ComponentModel.DoWorkEventArgs}) + 0x87 bytes
System.dll!System.ComponentModel.BackgroundWorker.WorkerThreadStart(object argument = {Cardinal.DAU.Common.DAUProcess}) + 0x88 bytes
[Native to Managed Transition]
[Managed to Native Transition]
mscorlib.dll!System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(System.RuntimeMethodHandle md = {System.RuntimeMethodHandle}, object[] args = {object[1]}, object server = {System.ComponentModel.BackgroundWorker}, out object[] outArgs = null) + 0x48 bytes
mscorlib.dll!System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(System.Runtime.Remoting.Messaging.IMessage msg = {System.Runtime.Remoting.Messaging.MethodCall}, System.Runtime.Remoting.Messaging.IMessageSink replySink = {System.Runtime.Remoting.Messaging.AsyncResult}) + 0x231 bytes
mscorlib.dll!System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem.DoAsyncCall() + 0x5b bytes
mscorlib.dll!System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem.ThreadPoolCallBack(object o = {System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem}) + 0x47 bytes
mscorlib.dll!System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(object state = {System.Threading.QueueUserWorkItemCallback}) + 0x75 bytes
mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext = {System.Threading.ExecutionContext}, System.Threading.ContextCallback callback = {Method = {System.Reflection.RuntimeMethodInfo}}, object state = {System.Threading.QueueUserWorkItemCallback}, bool preserveSyncCtx = true) + 0x1a2 bytes
mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext = {System.Threading.ExecutionContext}, System.Threading.ContextCallback callback = {Method = {System.Reflection.RuntimeMethodInfo}}, object state = {System.Threading.QueueUserWorkItemCallback}, bool preserveSyncCtx = true) + 0x33 bytes
mscorlib.dll!System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() + 0x95 bytes mscorlib.dll!System.Threading.ThreadPoolWorkQueue.Dispatch() + 0x22e bytes
mscorlib.dll!System.Threading._ThreadPoolWaitCallback.PerformWaitCallback() + 0x18 bytes
[Native to Managed Transition]

Third Call

Cardinal.DAU.Applications.EasyDoc.dll!Cardinal.DAU.Applications.EasyDoc.Migration00000000000004.Up(MigSharp.IDatabase db = {MigSharp.Core.Entities.Database}) Line 12 C# MigSharp.dll!MigSharp.Process.BootstrapMigrationStep.GetDatabaseContainingMigrationChanges(MigSharp.MigrationDirection direction = Up, MigSharp.IMigrationContext context = {MigSharp.Process.RuntimeContext}) + 0x63 bytes MigSharp.dll!MigSharp.Process.BootstrapMigrationStep.Execute(System.Data.IDbConnection connection = {System.Data.SqlClient.SqlConnection}, System.Data.IDbTransaction transaction = {System.Data.SqlClient.SqlTransaction}, MigSharp.MigrationDirection direction = Up, MigSharp.IDbCommandExecutor commandExecutor = {MigSharp.Process.ExecutingCommandExecutor}) + 0x93 bytes MigSharp.dll!MigSharp.Process.MigrationStep.Execute(MigSharp.Process.IVersioning versioning = {MigSharp.Process.Versioning}) + 0x2cc bytes
MigSharp.dll!MigSharp.Process.MigrationBatch.ExecuteStep(MigSharp.Process.IMigrationStep step = {MigSharp.Process.MigrationStep}) + 0x72 bytes
MigSharp.dll!MigSharp.Process.MigrationBatch.Execute() + 0x1a1 bytes
MigSharp.dll!MigSharp.Migrator.MigrateAll(System.Reflection.Assembly assembly = {System.Reflection.RuntimeAssembly}, System.Reflection.Assembly[] additionalAssemblies = {System.Reflection.Assembly[0]}) + 0x8f bytes
Cardinal.DAU.Windows.exe!Cardinal.DAU.Windows.MainForm.BackgroundWorker1_DoWork(object sender = {System.ComponentModel.BackgroundWorker}, System.ComponentModel.DoWorkEventArgs e = {System.ComponentModel.DoWorkEventArgs}) Line 376 + 0x46 bytes C# System.dll!System.ComponentModel.BackgroundWorker.OnDoWork(System.ComponentModel.DoWorkEventArgs e = {System.ComponentModel.DoWorkEventArgs}) + 0x87 bytes
System.dll!System.ComponentModel.BackgroundWorker.WorkerThreadStart(object argument = {Cardinal.DAU.Common.DAUProcess}) + 0x88 bytes
[Native to Managed Transition]
[Managed to Native Transition]
mscorlib.dll!System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(System.RuntimeMethodHandle md = {System.RuntimeMethodHandle}, object[] args = {object[1]}, object server = {System.ComponentModel.BackgroundWorker}, out object[] outArgs = null) + 0x48 bytes
mscorlib.dll!System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(System.Runtime.Remoting.Messaging.IMessage msg = {System.Runtime.Remoting.Messaging.MethodCall}, System.Runtime.Remoting.Messaging.IMessageSink replySink = {System.Runtime.Remoting.Messaging.AsyncResult}) + 0x231 bytes
mscorlib.dll!System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem.DoAsyncCall() + 0x5b bytes
mscorlib.dll!System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem.ThreadPoolCallBack(object o = {System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem}) + 0x47 bytes
mscorlib.dll!System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(object state = {System.Threading.QueueUserWorkItemCallback}) + 0x75 bytes
mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext = {System.Threading.ExecutionContext}, System.Threading.ContextCallback callback = {Method = {System.Reflection.RuntimeMethodInfo}}, object state = {System.Threading.QueueUserWorkItemCallback}, bool preserveSyncCtx = true) + 0x1a2 bytes
mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext = {System.Threading.ExecutionContext}, System.Threading.ContextCallback callback = {Method = {System.Reflection.RuntimeMethodInfo}}, object state = {System.Threading.QueueUserWorkItemCallback}, bool preserveSyncCtx = true) + 0x33 bytes
mscorlib.dll!System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() + 0x95 bytes mscorlib.dll!System.Threading.ThreadPoolWorkQueue.Dispatch() + 0x22e bytes
mscorlib.dll!System.Threading._ThreadPoolWaitCallback.PerformWaitCallback() + 0x18 bytes
[Native to Managed Transition]

[dradovic]

Thanks. So from the call-stack it looks good. As I said: 2 validations + 1 execution. So, if you've found an error in your code, should we close this issue?