Code first 自定义 SQL 迁移超时异常 [英] Code first custom SQL migration timeout exception

查看：19
本文介绍了Code first 自定义 SQL 迁移超时异常的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试通过执行自定义 Sql 使用实体框架迁移创建全文索引.

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

我的迁移类如下所示:

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");
    }
}

当我从 MSSQL 管理工作室运行它时，一切都很完美，SQL 完全符合我的预期.

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

但是当从迁移项目运行时，第二个 Sql 请求会触发异常

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.

带有 -Verbose 标志的完整堆栈跟踪:

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.

我尝试从 Verbose 输出中完全按原样"复制所有 SQL 并直接在 Management Studio 中执行它，它完全按预期工作，没有错误.

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?

奇怪的是，如果我把 ContentType 最大长度更改代码

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.

在 shivakumar 建议之后，我尝试增加迁移配置中的连接超时(最多 5 分钟)，这增加了之前的时间我收到超时异常"但问题仍然存在.

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.

推荐答案

使用 Configuration.cs 文件设置自定义超时:

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; 
    }
}

这篇关于Code first 自定义 SQL 迁移超时异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆