Migration Timeouts

My migrations are timing out:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Mindscape.LightSpeed.Generator.Extraction.DdlUpdater.ExecuteSql(String sql)
   at Mindscape.LightSpeed.Generator.Extraction.DdlUpdater.AddColumn(TableName tableName, Field field)
   at Mindscape.LightSpeed.Migrations.Migration.AddColumn(String tableName, String owner, String columnName, ModelDataType type, Boolean nullable)

How can I increase the timeout value in the migration's "Up" method?

I should add that I'm running the migrations in code (some lines removed for brevity):

        var lightspeedConfiguration = Mindscape.LightSpeed.Configuration.LightSpeedConfigurationSection.Read();

        if (lightspeedConfiguration != null)
        {
            var migrationContext = lightspeedConfiguration.Contexts["migrations"];
            if (migrationContext != null)
            {
                SqlConnectionStringBuilder migrationConnectionStringBuilder = new SqlConnectionStringBuilder(migrationContext.ConnectionString);

                var source = new AssemblyMigrationLoader(typeof(]removed].Migrations.Initialise).Assembly);
                try
                {
                    Migrator.Migrate(source,
                        (ProviderType)
                            Enum.Parse(typeof(ProviderType),
                                lightspeedConfiguration.DefaultContext.DataProvider),
                        migrationConnectionStringBuilder.ConnectionString, null);
                }
                catch (Exception ex)
                {
                }
            }
        }

And the migration's Up() is simply:

   public override void Up()
    {
        this.AddColumn("User", "core", "IsSubscribed", ModelDataType.Boolean, false);
    }

I've tried adding a commandTimeout value of 600 to the lightSpeedContext (in web.config), but it had no impact whatsoever. It's as if Lightspeed isn't utilising the timeout value - it appears to be fixed at 30 seconds (which obviously isn't going to be enough for significant migrations).

The migrations are running in an Azure environment, and I'm running the latest nightly build of Lightspeed 5.

This rings a bell. We have the following lines in our code setting up the LightSpeedContext<> object:

// Parse out the default command timeout as LightSpeed doesn't apply it automatically
var connectionString = new MySqlConnectionStringBuilder(Context.ConnectionString);
Context.CommandTimeout = TimeSpan.FromSeconds(connectionString.DefaultCommandTimeout);

This is from our web code, not migrations, but you might be able to adapt it somehow. From memory the connection timeout works OK on its own.

I don't recall whether that was a MySQL provider specific issue or not sorry.

Check that the connection string being passed through from

SqlConnectionStringBuilder migrationConnectionStringBuilder = new SqlConnectionStringBuilder(migrationContext.ConnectionString);

still contains the command timeout setting. The connection string that is used when creating the migrator is passed as the connection string initialising the ADO.NET provider so if the setting is being passed in then it should be being applied.

Jeremy

Thanks N4L. Sadly, the CommandTimeout is read only on the migration context.

@Jeremy:

The timeout value is correct (600) - I've tried specifying it on the connection string and the lightSpeedContext (below).

In my search for a solution, I'd read about a bug that would see the CommandTimout not being passed down to the command, but I can't find the article now. Is it not possible to add the ability to specify the CommandTimeout on the migration itself, instead of it being a get only property?

Basically, when migrating to Azure, commands have 30 seconds to complete or it times-out, regardless of the timeout value specified here:

  <lightSpeedContexts>
    <add name="migrations" connectionStringName="Migrator" dataProvider="SqlServer2012" commandTimeout="600" />
  </lightSpeedContexts>

Any ideas Jeremy? This has now become a show stopping issue for us.

Thanks

Ive got a branch which we are looking to merge shortly which will add a Timeout property to the Migrator which will ultimately be used for the CommandTimeout value on the underlying ADO.NET command instances used to execute the migration calls, so this should hopefully cover what you need.

The CommandTimeout property on LightSpeedContext is not used with migrations.

Jeremy

Great!

I look forward to the updated nightly.

Thanks

Do you provide release notes for nightly builds, so that I can tell when this has been pushed?

Thanks

We have merged in this update today and so it will be available in the next nightly build for you. The update adds a Timeout property on the Migrator class which is the number of seconds which will be passed down to the IDBCommand instance under the hood.

Jeremy

Fantsatic! Thanks Jeremy. I'll give this a test first thing tomorrow.

Leigh

I've grabbed the nightly (dated 5th Feb), but I can't see a new Timeout property on the Migrator. Am I looking in the wrong place?

var migrator = Migrator.CreateMigrator(
                                source,
                                (ProviderType)Enum.Parse(typeof(ProviderType), lightspeedConfiguration.DefaultContext.DataProvider),
                                migrationConnectionStringBuilder.ConnectionString,
                                null,
                                false);

But there's no Timeout property on the migrator object.

Its available in the nightlies from the 6th of Feb onwards.

Jeremy

Sorry Jeremy - I think the difference in time zones (I'm in the UK) meant I grabbed it prematurely.

When inspecting the Migrator object in the object browser, I can see the Timeout property but it's protected, so it's not available in my code (above).

I was assuming I'd simply set it as such: migrator.Timeout = 600;

How should I be setting the Timeout value?

Yes sorry, this should have been marked as public, Ive added a fix for this now, Im just waiting for this to complete its test run to be able to merge it in so this should make it in for the next nightly build (20150211). Sorry for the confusion here.

Jeremy

Thanks Jeremy - Really appreciate your help with getting this implemented.

And just to follow up on this... The new property appears to have fixed the Azure migration issues we were experiencing :)