Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Code first custom SQL migration timeout exception

Tags:

sql-server-2012

entity-framework-migrations

entity-framework-6

full-text-indexing

I am trying to create FULL TEXT index using Entity Framework Migration by executing custom Sql.

My migration class looks like this:

public partial class DocumentContentFullTextIndex : DbMigration
{
    public override void Up()
    {
        AlterColumn("dbo.Attachments", "ContentType", c => c.String(maxLength: 260));

        Sql("CREATE FULLTEXT CATALOG FullTextIndexes AS DEFAULT;", true);

        Sql(@"CREATE FULLTEXT INDEX ON [Attachments](
Content
    TYPE COLUMN ContentType
    Language 'ENGLISH'
)
KEY INDEX [PK_dbo.Attachments]
ON FullTextIndexes;", true);
    }

    public override void Down()
    {
        AlterColumn("dbo.Attachments", "ContentType", c => c.String(maxLength: null));

        Sql("DROP FULLTEXT INDEX ON [Attachments]");
        Sql("DROP FULLTEXT CATALOG FullTextIndexes");
    }
}

When I run it from MSSQL management studio everything is perfect and SQL did exactly what I am expected from it.

But when running from migration project second Sql request fires exception

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Full stack trace with -Verbose flag:

Update-Database -ConnectionStringName DatabaseContext -Verbose
Using StartUp project 'Lx2'.
Using NuGet project 'Database.Model'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'Lx2' (DataSource: ., Provider: System.Data.SqlClient, Origin: Explicit).
Applying explicit migrations: [201406050348083_AttachmentsContentFullTextIndex].
Applying explicit migration: 201406050348083_AttachmentsContentFullTextIndex.
ALTER TABLE [dbo].[Attachments] ALTER COLUMN [ContentType] [nvarchar](260) NULL
CREATE FULLTEXT CATALOG FullTextIndexes AS DEFAULT;
CREATE FULLTEXT INDEX ON [Attachments](
Content
    TYPE COLUMN ContentType
    Language 'ENGLISH'
)
KEY INDEX [PK_dbo.Attachments]
ON FullTextIndexes;
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 System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.<ExecuteStatements>b__2e()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)
   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
   at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.<Update>b__b()
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run()
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force)
   at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0()
   at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
ClientConnectionId:3d298f0a-e2dc-4976-8587-c69d03b23c6b
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

I tried to copy all SQL from Verbose output exactly 'as is' and execute it directly in Management Studio and it work exactly as expected with no errors.

Any ideas how this can be fixed?

Strange thing also that if I put ContentType max length changing code

AlterColumn("dbo.Attachments", "ContentType", c => c.String(maxLength: 260));

in separate migration file everything also works fine.

UPDATE:

After shivakumar advice I tried to increase connection timeout (up to 5 minutes) in migration configuration and this increased time before I received "Timeout exception" but problem is still there.

like image 731
Nick Avatar asked Jun 05 '14 04:06

Nick

People also ask

What does timeout expired mean in a migration?

I have a migration to run on a quite big database. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How to set the time limit for Migration?

You can set the timeout for migration only by setting the timeout on the context before calling the Migrations method: You can do it also in the constructor of your database context class. This works but it applies to everything, not just the migration.

What is code first migration in Entity Framework?

Code First Migrations. Code First Migrations is the recommended way to evolve your application's database schema if you are using the Code First workflow. Migrations provide a set of tools that allow: The following walkthrough will provide an overview Code First Migrations in Entity Framework.

How do I enable custom operations in my migrations?

In our migrations, we want to enable writing the following code: The easiest way to implement a custom operation is to define an extension method that calls MigrationBuilder.Sql (). Here is an example that generates the appropriate Transact-SQL. Use the EXEC function when a statement must be the first or only one in a SQL batch.

2 Answers

Use Configuration.cs file to set custom time out:

internal sealed class Configuration : DbMigrationsConfiguration<ApplicationDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        ContextKey = "YourDbContext";

        // New timeout in seconds
        this.CommandTimeout = 60 * 5; 
    }
}
like image 195
Hovhannes Hakobyan Avatar answered Oct 27 '22 08:10

Hovhannes Hakobyan

Your request processing may be taking more time than default command timeout of entity framework(30 sec). You can increase command timeout if necessary as in thread below.

Entity Framework Timeouts

like image 33
shivakumar Avatar answered Oct 27 '22 09:10

shivakumar
Sign in to Comment

Related questions

How to fix reversed arabic characters mixed with english in SQL server
Case-insensitive primary key of type nvarchar where ß != ss
How to execute a 32 bit SSIS package in a 64bit package?
Row By Row ( Without Cursor or Loops)
How can I set identity_insert on a tablename passed as a variable
How to create a setup.exe file using InstallShield LE for a Visual Studio 2012 Windows Forms Application with SQL Server 2012 Local Database
How to make in T-SQL LIKE number or empty string inside square brackets?
Can I use SqlDependency with multiple listeners / load balance
Passing multiple values to a parameter of a function in SQL
Spatial results tab saying:One or more spatial objects were too large to display
LocalDB instance specified does not exist
Convert Image Type Data to XML/Plain Text in SQL Server
SQL - any performance difference using constant values vs parameters?
TABLOCKX not working
SQL - Returning Cumulative Monthly Column Values
Commit Transaction take too long?
Where does a Remote SSIS package actually run
SQL Server - Creating a Plan Guide without using an alias
MIN or MAX Value for Date Ranges - Determining lowest price for a given date range based on Product ID, Price, and Date Ranges
Why the SQL Server ignore the empty space at the end automatically?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!